How to Integrate CleverTap With External Business Intelligence Platforms

CleverTap is the world’s No. 1 retention cloud that empowers digital consumer brands to increase customer retention and lifetime value. As a retention platform, we focus on analytics and engagement and offer customers enough flexibility to export their data into external Business Intelligence (BI) platforms such as Google Data Studio, Tableau, or Power BI for automation and analytics.

Possible Use Cases

Create and deliver automated reports and alerts by harnessing the capabilities of BI platforms with your CleverTap data.
Use SQL if the volume of data is too large for Excel or Google Sheets.
Conduct a deeper analysis and apply Machine Learning to your data.
Slice and dice the data, and apply joins for insights by combining profile data with events data.

In this blog post, we will learn how to harness CleverTap’s cloud connectors and APIs for this purpose along with the pros and cons of each approach. We will also share some code snippets that you can use as a template and a bonus tip to get custom alerts to monitor your most important events!

There are generally two options for integrating CleverTap with your BI platform:

1. Using GCP or AWS Cloud Connectors

CleverTap allows you to export your data into the cloud provider of your choice and from there, bring it into your BI system. You can do this via Google Cloud Platform (GCP) or Amazon Web Services (AWS) cloud connectors.

Screenshot showing AWS and GCP connectors for exporting your data.

Export your data into the cloud provider of your choice and from there, bring it into your BI system.

Here we will take the example of the GCP bucket to demonstrate how you can integrate and automate your CleverTap report. You can also harness Amazon’s S3 bucket for this purpose depending on the organization infrastructure available.

CleverTap provides several choices when it comes to exporting. You can export both the profile data as well as the events data (including customizing the event that you want to export). You can customize the period as well as set up recurring data exports using the steps below. More information can be found in our user documentation.

Screenshot showing the create an export dialog within CleverTap

Choose how to export your data from CleverTap.

Once exported to the requisite GCP/S3 bucket, you can use the Bigquery data transfer service to transfer it to the Bigquery SQL and then integrate it with the BI platform of your choice. It helps you to automatically transfer data from Google Cloud Storage to Bigquery where you can apply SQL or connect it with Data Studio as currently there is no direct connection from CleverTap to Bigquery, it has to go through the Google Cloud Storage bucket.
The event data will be stored date-wise in separate CSV files for the recurring exports. You can automate the integration of the files in Google Cloud Storage with the Bigquery using the Bigquery Transfer Service.
Let’s say you are exporting an order placed event to the cloud. This is what your file name for the same event will look like:
Day 1 – 1663664837-1663785000-Order-Placed-20220921-0-0.csv.gz
Day 2 – 1663664837-1663957800-Order-Placed-20220922-0-0.csv.gz
Day 3 – 1663664837-1663957800-Order-Placed-20220923-0-0.csv.gz
As you can see, in the file name above, for Day X, the file name will look like – 1663664837-1663785000-Order-Placed*
So you can use the wildcard characters with the ‘*’ operator to append the data into a specific Bigquery table, meaning irrespective of the value beyond %, append the files in the same table.

Automate the integration of the files in Google Cloud Storage using the Bigquery Transfer Service.
Once the data is imported and visualized in Google Data Studio through the Bigquery connector it automatically updates/refreshes every 15 hours. So for example, if you have an export scheduled at 12:15 AM, and your Bigquery data transfer runs at 12:30 AM every day, you can rest assured you have the appended and updated table visualized in your GDS dashboard.

Pros

This won’t require coding, hence no developer dependency. Even a semi-technical person can set it up after reading the docs.
The “Build once use forever” system means recurring loads will populate your dashboard daily without any manual effort.
You’ll see minimal cloud costs as most use cases would fit in the free tier or under the $1 per month query cost.

Cons

For large volumes of data, be mindful of cloud costs.
The time period and frequency of data exports are predefined; you can customize them only after export.

2. Using the CleverTap API

CleverTap offers various kinds of APIs that can help you programmatically generate data in your desired JSON or CSV format to integrate with the BI platform of your choice. Here we will only focus on APIs required for Business Intelligence, you can read about all the other options we provide in our developer documentation.

Screenshot showing our documentation on using the CleverTap API.

Check out the developer docs for how to get started with the CleverTap API.

Here are some of the top APIs that can be used to fetch data for independent analysis. Refer to our developer documentation for more details and code snippets that you can use:

Events API – The Get Events API lets you download user events from CleverTap. For example, you can use this API to get a list of App Launched or Purchase events.
Event Count API – This endpoint is used to retrieve counts for an event in a specified duration. For example, you can get the total number of Charged events in the past day.
Campaign Report API – The Get Campaign Report API lets you retrieve campaign metrics. To get a campaign report, you specify the ID of the report needed.
Get User Profiles API – The Get User Profiles API lets you download user profiles from CleverTap.
Get Profile Count API – This endpoint enables you to get the total number of profiles that match an event query.

If you are a semi-technical person not comfortable with coding we have also created a python code template where you can just fill in the credentials and names of events you want to get and it will create a folder and CSV file with the required events/profiles in your local system. Feel free to copy the collab notebook and make it your own.

Pros

Query costs are minimal, however fetching huge quantities of data can take a few hours, so be patient and plan ahead.
Compared to cloud export, this option provides more control and flexibility over the outcome, the schema/structure volume as well as the timeline by allowing you to configure some of the elements according to your requirements.

Cons

This option requires developer dependency, or a beginning level knowledge of working with APIs. However, a semi-technical person can use our code as a template to start with.
This option only has semi automation, unlike cloud exports that provide complete automation. This approach is semi-automatic since you will have to manually place the CSV data generated out of your query into your BI system. However, a developer can set up a periodic cron job and integrate it with the backend.

Bonus Tip: Custom Alerts Through Webhooks

For a better integration between CleverTap and other platforms in your tech stack, we provide webhooks. When configured correctly, you can use a webhook to send an event notification to a third-party end-point.

Some common use cases for webhooks include:

Subscribe or unsubscribe a user from marketing communication
Set up Slack alerts for success, failures, or errors to a relevant channel
Send subscription events from your subscription management platform to CleverTap after the subscription is renewed or canceled

As seen in the examples above, webhooks are lightweight, event-driven communication, and may not be the best way to export large amounts of data from CleverTap into a BI platform.

These are some of the ways in which CleverTap provides you with the flexibility to use your data in your BI system for analytics and notifications. And if you have any interesting use cases, we’d love to hear about them. Reach out to us at [email protected]