Sunday, December 20, 2015

Roundup of Latest Features

Cloud9 Charts Product Updates
We've got lots of new goodies to help you go from data to insights in a snap from your NoSQL, SQL, files and other sources.

Complete Analytics platform for Unstructured and Stuctured Data
Data in multiple sources? Learn how to effectively leverage your "polyglot" architecture to join data across multiple database systems using our Multi-Datasource Join capability.

Connect to any REST API, query it using a SQL-like syntax on multi-structured data and visualize it.

Apply predictions on any of your data. We'll back-test the data against a number of prediction models automatically to get the best fit.

Native integrations to derive insights instantly: DataStax, ElasticSearch, Spark.

If you are a MarkLogic user, our XQuery generator is here by popular demand.

Also check out query generators for MongoDB, DataStax, SQL, Spark and others.

Cohort analytics can be useful to determine churn and behaviour across user groups, but can also be notoriously difficult to do. Learn how we simplify it to build your cohort analysis easily.

Choose from over 20 visualization types, including gauges, grid heatmaps, enhanced geo options and several more.


Friday, December 11, 2015

RESTful services, Part 2 - Mixpanel

In the last blog entry in this series, we introduced our integration to connect to any REST API's querying using a SQL-like syntax and generating custom visualizations from it. The example used a live Parse account and we've since had some requests for the same for Mixpanel. 

This outlines connecting and querying Mixpanel data using our REST API interface to build custom visualizations from it. Whereas Parse API  is simple to connect, query and track, Mixpanel API's are not particularly easy to work with. Partly this is due to the way in which authentication is handled, where any parameters to the API must be signed using MD5 in alphabetical order. 

The high level steps are:
  1. Determine your API Key and API secret. This can be found under the Accounts --> Projects section of your Mixpanel account. 
  2. Determine what you'd like to query from Mixpanel from their documentation:
  3. Build the URL, and generate a signature with the parameters.
  4. Query and post process the nested data using Cloud9QL.

Let's take a live Mixpanel demo account to make it easier to follow along. This example determines the count of events over the past day. The API documentation for this can be found here:

1. Visit Enter as the host. 
(If you an existing user, this is also accessible within the REST datasource section within your account) 

2. Leave the Headers section empty. For end point, set to events/top.

3. Now comes the tricky part. To build the URL, we need to build a MD5 hash for the alphabetically ordered parameters. 

Let's build up the URL with basic params first: api_key=a8d3995557ce4cea1ae92ec03314a620&type=unique

Determine the epoch expiry date for the request. For 1st, Dec  2016, this would be 1480550400 ( Adding this to the URL, we get: api_key=a8d3995557ce4cea1ae92ec03314a620&type=unique&expire=1480550400

Next comes the sig field, which is the MD5 hash for the request:
  • Build a string with the url parameters in ascending form concatenated together without the & separator: api_key=a8d3995557ce4cea1ae92ec03314a620expire=1480550400type=unique
  • Concatenate the Mixpanel API  secret to it:  
  • api_key=a8d3995557ce4cea1ae92ec03314a620expire=1480550400type=unique6a2532b187735ea4d531f4b7dfc7a7d2
  • MD5 this string ( to generate a hash. This will be the sig field for the request. MD5 Result: befa480e75440ef4b8141f2664ba6753
  • Putting it all together, the resulting URL parameter looks like this: api_key=a8d3995557ce4cea1ae92ec03314a620&type=unique&expire=1480550400&sig=befa480e75440ef4b8141f2664ba6753
4. Add the above into the URL params field at

5. Now we are ready to query Mixpanel to fetch the results. The return data looks something like this:

{"events": [{"amount": 2,
             "event": u"funnel",
             "percent_change": -0.35635745999582824},
            {"amount": 75,
             "event": u"pages",
             "percent_change": -0.20209602478821687},
            {"amount": 2, "event": u"projects", "percent_change": 1.0}],
 "type": u"unique"}

To unwind the events nested array above, add the following Cloud9QL into the the Cloud9QL Query: 

select expand(events);

Click on Show me to visualize the results. 

Tuesday, December 8, 2015

Analyzing data from Parse & other RESTful services

Cloud9 Charts provides connectivity to third party REST enabled services to be able to query, transform, visualize, analyze and track that data. 

In the following example, we'll connect to Parse to query and derive visualizations from it.

To see it in action visit -- this is pre-configured with a live Parse URL, authentication headers and the end point to query from. 

If you are a parse user, you can plug in your own end point and authentication parameters. (To determine your Parse auth key, see

Parse returns data in an embedded JSON form with results at the top level JSON element. For example:

  "results": [
      "createdAt": "2015-12-09T02:12:05.185Z",
      "customer": "Costco",
      "objectId": "SF5hWccArl",
      "opened": 98240,
      "sent": 604585,
      "updatedAt": "2015-12-09T02:12:05.185Z",
      "week": "2015-04-13"
      "createdAt": "2015-12-09T02:12:05.190Z",
      "customer": "eHarmony",
      "objectId": "QuVeGh6hkA",
      "opened": 81738,
      "sent": 557202,
      "updatedAt": "2015-12-09T02:12:05.190Z",
      "week": "2015-04-13"

Let's say that we are looking for the sent count by customer, by week across this dataset to see the weekly trends by customer. For this, we'll need the following:
  • Unwind top level results.
  • Perform aggregations on the sent count by week by customer.
So how do we make this happen? Using Cloud9QL

The above can be done using the following Cloud9QL:

select expand(results);
select customer,date(week) as week, sent group by customer, week

The first statement unwinds the  array from the top level results object. The second calculates the weekly totals for each customer. 

Click on 'Show Me' to instantly visualize the data. 

To learn more about tracking query results, multi-datasource joins and variety of use cases, see our documentation.

Saturday, December 5, 2015

MarkLogic XQuery Generator

Due to popular demand from our MarkLogic users, we are pleased to unveil XQuery generation capabilities. This allows MarkLogic users to interactively discover and auto-generate XQuery using a point and click interface and build interactive, embeddable visualizations and dashboards.

  • Unlock insights from multi-structured MarkLogic data instantly
  • Discover collections
  • Auto-generate XQuery using a point and click interface
  • Native integration (withour ODBC) that maintains the multi-structured view of the data 
  • Eliminates complex ETL processing and shoehorning the data into relational form for BI
  • Instant visualizations, dashboards and share/embed easily
See for yourself at This is pre-configured with a live MarkLogic database that you can interact and build dynamic queries from the data.

Additional documentation: