Tuesday, April 10, 2018

4 Simple Steps to Create Reports and Drive Actions from REST APIs

Real-time Customer Support Team Performance Reporting Using Zendesk APIs

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 join results with other API calls and/or NoSQL/SQL databases
  • 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.

Knowi REST API Query

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).

Knowi RESt API data visualization

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,
order by ticket_id desc;
Knowi REST API data transformation

2. Join with another REST call in Zendesk to retrieve Submitter info

Knowi supports Joins with other API endpoints (or disparate databases). Since the tickets only contain submitter id, get the submitted name/email from a second endpoint (/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..
Knowi REST API joins

4. Action: Setup a Trigger notification 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.

Triggering actions from REST APIs with Knowi

Knowi Slack Alert Configuration


We pulled support tickets from Zendesk through the Zendesk Ticket API. We visualized tickets by date. We added Submitter information to create a blended dataset that we could use for alerting. We created an alert to send a list of open tickets that exceeded our response threshold of 3 hours to our customer support Slack channel.

Try it on your own APIs. See our REST API Playground to point to your own.