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 https://www.cloud9charts.com/rest-api -- 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 https://parse.com/docs/rest/guide#queries).

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.