Connecting CleverTap To PostgreSQL: 2 Easy Methods

CleverTap is the only platform that uses AI (Artificial Intelligence) and ML (Machine Learning) 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 requirements. The platform also segments the users based on various parameters, so no manual intervention is required. Are you looking to transfer your data from CleverTap to PostgreSQL for analysis? In this post, you will know various ways to move the data from CleverTap to PostgreSQL.

Introduction to CleverTap

CleverTap is a platform that seamlessly collects various events and data about the users who 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.

To know more about CleverTap, check out CleverTap’s Official Documentation.

Introduction To PostgreSQL

PostgreSQL is a popular object-relational database management system that offers enterprise-grade features with a strong concentration on extensibility. It runs on all major operating systems such as Unix and Windows. It’s open-source, fully ACID-compliant, and fully supports foreign keys, joins, etc. in multiple languages. It’s available in cloud-based deployments by most major cloud providers.

To know more about PostgreSQL, check out their PostgreSQL Official Documentation.

Methods to Connect CleverTap to PostgreSQL

This article delves into both the manual and using Hevo methods in depth. You will also see some of the pros and cons of these approaches and would be able to pick the best method based on your use case. Below are the two methods:

• Method 1: Steps to Load Data from CleverTap to PostgreSQL Table Manually
• Method 2: Steps to Load Data from CleverTap to PostgreSQL using Hevo

Method 1: Steps to Load Data from CleverTap to PostgreSQL Table Manually

Let’s see what you will cover here:

• Prerequisites for Manual Method
• Steps to Create Custom Scripts
• Limitations of Manual Method

Prerequisites for Manual Method

• You need to have a CleverTap account set up and connected to a service of yours from where the data is synced to the CleverTap Account. For CleverTap API (Application Programming Interface) and further documentation, kindly refer here.
• A PostgreSQL database has to be set up in your local system; for example, setting up PostgreSQL can be found here.
• Python has to be set up; on a side note, this script can be programmed using PHP and javascript as well.

Steps To Create Custom Scripts

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

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 PostgreSQL corresponding table database.

The following code performs the above-mentioned four steps in python:

import requestsfrom psycopg2.extras 
import Jsonimport psycopg2
import json

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

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

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

clevertapdata = json.loads(response)

# create a nested list of the records' 
valuesvalues = [list(x.values()) for x in insights]

# get the column names
columns = [list(x.keys()) for x in insights][0]

# value string for the SQL string
values_str = ""

# enumerate over the records' values
for i, record in enumerate(values):

    # declare empty list for values    
    val_list = []      

    # append each value to a new list of values    
    for v, val in enumerate(record):
        if type(val) == str:
            val = str(Json(val)).replace('"', '')
        val_list += [ str(val) ]

    # put parenthesis around each record string
    values_str += "(" + ', '.join( val_list ) + "),n"

# remove the last comma and end SQL with a semicolon
values_str = values_str[:-2] + ";"

# concatenate the SQL string
table_name = "your_tablename"
sql_string = "INSERT INTO %s (%s)n VALUES (%s,%s,%s,%s)" % (    table_name,
    ', '.join(columns),
    values_str)

# Connect to the PostgreSQL database
try:

    # declare a new PostgreSQL connection object
    conn = psycopg2.connect(
        dbname = "",
        user = "",
        host = "",
        password = "", 

       # attempt to connect for 3 seconds then raise exception
        connect_timeout = 3
       )
   
    cur = conn.cursor()
    print ("ncreated cursor object:", cur)

except (Exception, Error) as err:
    print ("npsycopg2 connect error:", err)
    conn = None
    cur = None

# Insert the SQL string to the database
# only attempt to execute SQL if cursor is validif cur != None:

    try:
        cur.execute( sql_string, clevertapdata["record.Email","record.profileData.Last Score","record.profileData.High Score","events.App Launched.first_seen"]) 
       conn.commit()

       print ('nfinished INSERT INTO execution')

    except (Exception, Error) as error:
        print("nexecute_sql() error:", error)
        conn.rollback()

    # close the cursor and connection
    cur.close()
    conn.close()

The above code successfully loads the data from CleverTap to PostgreSQL, 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 database.

Limitations of Manual Method

Limitations of using the manual method are listed below:

1. Effort-Intensive: Using custom code to move data from CleverTap to the PostgreSQL database requires you to learn and bring together 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 standardize time zones to perform efficient analytics. The mentioned approach does not cover that.
4. Constant Monitoring & Maintenance: In case 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: Steps to Load Data from CleverTap to PostgreSQL using Hevo

Hevo Data, a No-code Data Pipeline, helps you directly transfer data from CleverTap and 100+ other data sources to a Database such as PostgreSQL, Data Warehouses, BI tools, or a destination of your choice in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Hevo Data takes care of all your Data Preprocessing needs and lets you focus on key business activities and draw a much more powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. 

Below are the steps to Load Data From CleverTap to PostgreSQL using Hevo: 

• Connect your CleverTap account to Hevo’s platform. Hevo has an in-built CleverTap integration that connects to your account within minutes.

• Select the PostgreSQL database as your destination and start moving your data.

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

Conclusion

PostgreSQL is a great RDBMS that is very versatile and can be used to aggregate structured data and derives useful insights and CleverTap is a great platform to get various user analytics useful to one’s business needs. Depending on the particular use case and data requirement, you may choose to replicate data from CleverTap to PostgreSQL using one of the approaches detailed in this article. You may build a custom code-based data pipeline to transfer data from CleverTap to PostgreSQL.

You can try Hevo today by SigningUp for a Free Trial.

FAQ on CleverTap To PostgreSQL

Let us know about your experience in loading the data from CleverTap to PostgreSQL in the comment section below.