Visualizing Data and Driving Actions from REST APIs using Knowi

APIs are everywhere these days. While most SaaS products now offer API access, interacting with APIs, gathering insights and driving actions, typically requires custom implementations to manage and maintain.

This post walks through how to query and drive actions from API’s with Knowi, using a real world use case with Zendesk APIs to alert our customer success team.

Knowi is a new kind of analytics platform designed for modern data architectures, including:

• Calling API endpoints.
• Prep and transform the results.
• Multi-source Joins to combine results with other API calls and/or NoSQL/SQL databases with/without keys.
• Visualizing and triggering actions on the data.
• Integrating machine learning for forecasting, anomaly detection, predictive analytics.

Use Case

Our customer success team needed alerts on tickets under certain conditions. For example, new tickets that have not been responded to for over 3 hours.

At a high level, this boils down to:

• Retrieve Ticket data from Zendesk
• Join with another REST call in Zendesk to retrieve Submitter data
• Transform/Manipulate the data (using Cloud9QL, a SQL like syntax to filter the results)
• Setup a Trigger Notification into Slack with a list of tickets that require attention

Connecting to Zendesk API

First step is setting up a REST datasource in Knowi, by specifying a base URL (https://<yourdomain>.zendesk.com/api/v2), along with the authentication method. Zendesk supports basic user/password auth, so we’ll use that. Knowi will pass along the authentication as part of the API calls into subsequent requests.

Calling Endpoints

Retrieving Ticket Data

The API endpoint is

GET /tickets.json

The API returns tickets in a nested JSON array, with associated details for each ticket.

{
 "tickets": [
 {
 "id": 35436,
 "subject": "Help I need somebody!",
 …
 },
 {
 "id": 20057623,
 "subject": "Not just anybody!",
 …
 },
 …
 ]
}

To query this in Knowi:

• Specify the end-point to query. In this case, it’s /tickets
• Pass along URL params sort_by=created_at&sort_order=desc&include=comment_count to get the most recent tickets first and include the comment count in the ticket.
• Use Cloud9QL to manipulate the results. For example, to get the total tickets by date from the results, the syntax would be:

select expand(tickets);
select count(*) as count, date(created_at) as week
group by date(created_at)

This expands the nested array to calculate total tickets by date. This can be immediately visualized and added into a dashboard (for sharing and embedding).

To get the new tickets that have not been responded to:

1. Retrieve Ticket data from Zendesk

Using the following Cloud9QL to get the relevant fields from the nested tickets array.

select expand(tickets);
select id as ticket_id, assignee_id,created_at,description,status,submitter_id,subject,type,recipient,requester_id,comment_count
order by ticket_id desc;

2. Join with another REST call — Retrieve Submitter info

Knowi supports Joins with other API end points (or disparate databases). Since the tickets only contain submitter id, get the submitted name/email from a second end point (/users) to join it with the first set of results.

3. Post-Join Data Transformation

The last step of the data processing is to filter the results to what we are looking for. Specifically, we are looking for tickets in open status, and not created by internal users. In addition, we are only looking to receive alerts during business hours, Pacific Time.

4. Action: Trigger notifications into Slack

With this data, now we are ready to drive an action. Knowi provides Triggers to drive calls into other API’s, emails or Slack. In this case, we’ll send a slack notification to our Customer Success team with the list of tickets that require attention attached.

Summary

We looked at connecting to an API and driving insights and actions from it. Try it on your own APIs. See our REST API Playground to point to your own. Enjoy!

Resources:

REST API Explorer

Knowi REST-API Documentation