CleverTap To MySQL: 2 Easy Methods

CleverTap is the only platform that uses AI and ML to personalize customer experience using real-time behavioral and pattern data. These precise predictive models result in time-relevant engagement opportunities based on each user’s exact requirements. The platform also segments the users based on various parameters, so no manual intervention is required. If you are using CleverTap to manage your customer communications and want to transfer your CleverTap data to a destination database such as MySQL, you are in the right place.

In this post, you will be introduced to two ways to migrate data from CleverTap to MySQL.

Introduction to CleverTap

CleverTap is a platform that seamlessly collects various events and data regarding the users that visit your website or application. The platform provides various plugins that can be integrated to get the consolidated data of user trends and habits on the website or the application, to capitalize on the retention of the user by personalizing customer communication.

Introduction to MySQL

MySQL is available freely and an open-source tool based on a relational database management system (RDMS) that uses the structured query language (SQL). SQL is a language that is used to manipulate and store the data into a database. It is a popular tool that is used widely in various applications for storage and retrieval of data.

Prerequisites

Before understanding the two methods of data transfer from CleverTap to MySQL, let us go through the initial requirements:

1. MySQL Server needs to be installed and a DB created with an appropriate name. You can follow the tutorial here to set it up.
2. Python has to be installed and set up in the system as you will use Python to manually shift data from Clevertap to MySQL. Installation steps can be found here.
3. You need to have an account set up in CleverTap connected to one of your services, from which the data will be retrieved. More information related to CleverTap can be found here.

Methods to Connect CleverTap to MySQL

Here are the methods you can use to connect CleverTap and MySQL in a seamless fashion:

• Method 1: Using Custom Python Scripts to Connect CleverTap to MySQL
• Method 2: Using Hevo to Connect CleverTap to MySQL

Method 1: Using Custom Python Scripts to Connect CleverTap to MySQL

Once the prerequisites are met, the following steps have to be done to complete the data transfer from CleverTap to MySQL database.

1. Connect to CleverTap API.
2. Read the data from the CleverTap platform.
3. Convert the JSON response to the Python dictionary.
4. Push the data values to MySQL corresponding table DB.

The following code performs the above-mentioned 4 steps in Python.

import requests
import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
import json

headers = {
    'X-CleverTap-Account-Id': 'ACCOUNT_ID',
    'X-CleverTap-Passcode': 'PASSCODE',
    'Content-Type': 'application/json',
}

params = (
    ('email', jack@gmail.com'),
)

response = requests.get('https://api.clevertap.com/1/profile.json', headers=headers, params=params)

clevertapdata = json.loads(response)

try:
    connection = mysql.connector.connect(host='yourhostname',
                                         database='dbname',
                                         user='username',
                                         password='dbpassword')
    mySql_insert_query = """INSERT INTO ClevertapUser (Email, LastScore, Highscore, firstseen) 
                           VALUES 
                           (%s,%s,%s,%s) """

    cursor = connection.cursor()
    cursor.execute(mySql_insert_query, clevertapdata["record.Email","record.profileData.Last Score","record.profileData.High Score","events.App Launched.first_seen"])
    connection.commit()
    print(cursor.rowcount, "Record inserted successfully into ClevertapUser table")
    cursor.close()

except mysql.connector.Error as error:
    print("Failed to insert record into ClevertapUser table {}".format(error))

finally:
    if (connection.is_connected()):
        connection.close()
        print("MySQL connection is closed")

The above code successfully loads CleverTap data to MySQL DB, you can choose what columns and what data you require, or you can get the whole data itself by creating the corresponding columns in the DB.

An example of the response data from the CleverTap API is as given below.

The above table is an example of a MySQL DB which contains data received from the CleverTap API account. The table can have any number of columns pertaining to data that is required from the CleverTap account. Accordingly, the data table can be changed to reflect whatever is required.

Limitations of using Custom Python Scripts to Connect CleverTap to MySQL

1. Effort intensive: Using custom code to move data from CleverTap to MySQL database requires you to learn and combine many different technologies. Given the learning curve involved, your data projects’ timelines can be affected.
2. Not real-time: The process mentioned above does not help you bring data in real time. You would have to develop a cron job and write extra code to bring data in real time.
3. No data transformation: At times, you would encounter use cases where you need to transform data to perform efficient analytics. The mentioned approach does not cover that.
4. Constant monitoring & maintenance: If there are some changes in the API at CleverTap’s end, it will result in irretrievable data loss. Hence, this approach requires constant monitoring and maintenance of the systems involved.

Method 2: Using Hevo to Connect CleverTap to MySQL

Step 1: Connect your CleverTap account to Hevo’s platform. Hevo has an in-built CleverTap integration that connects to your account within few clicks. Just provide the Pipeline Name, Account ID, Passcode, Region, and Events.

Step 2: Select MySQL as your destination and start moving data by providing the details like the Destination Name, Database Host, Database Port, Database User, Database Password, and Database Name.

That is all! You can now sit back and watch your data move seamlessly from CleverTap to MySQL.

Benefits of using Hevo

These are a few other advantages: 

1. Simplicity: Hevo is an extremely intuitive and simple to use platform. With Hevo, you can start pushing data from CleverTap to MySQL database in just a few minutes.
2. Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the pipelines you set up. You need to edit the event object’s properties received in the transform method as a parameter to carry out the transformation. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use.
3. Connectors: Hevo supports 150+ integrations to SaaS platforms such as CleverTap, files, databases, analytics, and BI tools. It supports various destinations including Salesforce CRM, Google BigQuery, Amazon Redshift, Firebolt, Snowflake Data Warehouses; Amazon S3 Data Lakes; and MySQL, MongoDB, TokuDB, DynamoDB, PostgreSQL databases to name a few.  
4. Real-time Data: The real-time streaming design structure of Hevo ensures that you move data from CleverTap to MySQL database immediately, without any delay. In this way, meaningful analysis in real-time is made possible.
5. Reliable Data Load: The fault-tolerant architecture of Hevo ensures that your data is loaded in a consistent and reliable fashion without any loss of data.
6. Scalability: Any scale of data can be handled by Hevo. Additionally, data from multiple sources can be loaded to MySQL using Hevo. Both these features of Hevo can help you scale your data infrastructure as per your data needs. 

Conclusion

This blog talks about the two methods you can use to connect CleverTap to MySQL seamlessly: using Python Scripts and with a third-party tool, Hevo.

Hevo takes care of automatically mapping all your data to relevant tables in the MySQL table, giving you access to analysis-ready CleverTap data in real time for free.

Learn how to move data from CleverTap to PostgreSQL to optimize your data processes. Our guide provides step-by-step instructions for a smooth transition.

Extracting complex data from a diverse set of data sources can be a challenging task and this is where Hevo saves the day! 

Try Hevo’s free 14-day trial to give Hevo a try. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

What are your thoughts on the methods described in this article? What is your preferred way to move data from CleverTap to MySQL? Let us know in the comments below.

FAQ