Freshdesk to Redshift: Steps to Load Data Instantly

on Tutorial • February 14th, 2020 • Write for Hevo

Are you looking to load Freshdesk data to Redshift for deeper analysis? Or are you looking to simply create a backup of this data on your warehouse? Whatever be the use case, deciding to move data from Freshdesk to Redshift is a step in the right direction. This blog highlights the broad approaches and steps that one would need to take to reliably load data from Freshdesk to Redshift. 

Introduction to Freshdesk 

Freshdesk is a cloud-based customer support platform owned by Freshworks. It integrates support platforms such as emails, live chat, phone and social media platforms like Twitter and Facebook. Freshworks allows you to keep track of all ongoing tickets and manage all support-related communications across all platforms. Freshdesk generates reports that allow you to understand your team’s performance and gauge the customers’ satisfaction level.

Freshdesk offers well-defined and rich REST (Representation State Transfer) API. Using Freshdesk’s REST API, data on Freshdesk tickets, customer support, team’s performance, etc. can be extracted and loaded onto Redshift for deeper analysis. 

Introduction to Amazon Redshift

AWS Redshift is a data warehouse owned and maintained by amazon web services (AWS) and forms a large part of the AWS cloud computing platform. It is built using MPP (massively parallel processing) architecture. Its ability to handle analytical workloads on a large volume of data sets stored in the column-oriented DBMS principles makes it different from Amazon’s other hosted database offerings. Redshift makes it possible to query megabytes of structured and non-structured data using SQL. You can save the results back to your S3 data lake using formats like Apache Parquet. This allows you to further analyze from other analytical services like Amazon Athena, Amazon EMR, and Amazon SageMaker. You can read more Redshift here.

Methods to Load Data from Freshdesk to Redshift

This can be done in two ways:

Method 1: Using Custom ETL Scripts

This would need you to invest in the engineering team’s bandwidth to build a custom solution. The process involves the following steps broadly. Getting data out using Freshdesk API, preparing Freshdesk data and finally loading data into Redshift.

Method 2: Use a fully-managed Data Pipeline Platform like Hevo Data

You will need to use engineering resources to write the scripts to get data from Salesforce’s API to S3 and then to Redshift. You will also need to maintain the infrastructure for this and monitor the scripts on an ongoing basis.

This article will provide an overview of both the above approaches. This will allow you to analyze the pros and cons of all approaches and select the best method as per your use case. 

Loading Data from Freshdesk to Redshift Using Custom Scripts

Step 1: Getting data from Freshdesk

The REST API provided by Freshdesk allows you to get data on agents, tickets, companies and any other information from their back-end. Most of the API calls are simple, for example, you could call GET /api/v2/tickets to list all tickets.  Optional filters such as company ID, and updated date could be used to limit retrieved data. The include parameter could also be used to fetch fields that are not sent by default.

Freshdesk sample data

The information is returned in JSON format.  Each JSON object may contain more than one attribute which should be parsed before loading the data in your data warehouse. Below is an example of the API call response made to return all tickets.

{
 "cc_emails" : ["user@cc.com"],
 "fwd_emails" : [ ],
 "reply_cc_emails" : ["user@cc.com"],
 "email_config_id" : null,
 "fr_escalated" : false,
 "group_id" : null,
 "priority" : 1,
 "requester_id" : 1,
 "responder_id" : null,
 "source" : 2,
 "spam" : false,
 "status" : 2,
 "subject" : "",
 "company_id" : 1,
 "id" : 20,
 "type" : null,
 "to_emails" : null,
 "product_id" : null,
 "created_at" : "2015-08-24T11:56:51Z",
 "updated_at" : "2015-08-24T11:59:05Z",
 "due_by" : "2015-08-27T11:30:00Z",
 "fr_due_by" : "2015-08-25T11:30:00Z",
 "is_escalated" : false,
 "description_text" : "Not given.",
 "description" : "<div>Not given.</div>",
 "custom_fields" : {
 "category" : "Primary"
 },
 "tags" : [ ],
 "requester": {
 "email": "test@test.com",
 "id": 1,
 "mobile": null,
 "name": "Rachel",
 "phone": null
 },
 "attachments" : [ ]
}

Source:https://developers.freshdesk.com/api/

Step 2: Freshdesk Data Preparation

You should create a data schema to store the retrieved data. Freshdesk documentation provides the data types to use, for example, INTEGER, FLOAT, DATETIME, etc.

Some of the retrieved data may not be “flat” – they maybe list. Therefore, to capture unpredictable cardinality in each of the records, additional tables may need to be created.

Step 3: Loading Data to Redshift

When you have high volumes of data to be stored, you should load data into Amazon S3 and load into Redshift using the copy command. Often times when dealing with low volumes of data, you may think of loading the data using the INSERT statement. This will load the data row by row and slow the process because Redshift isn’t optimized to load data in this way.

Freshdesk to Redshift Using Custom Code: Limitations and challenges

  1. Accessing Freshdesk Data in Real-time:  At this stage, you have successfully created a program that loads data into the data warehouse. The challenge of loading new or updated data is not solved yet. You could decide to replicate data in real-time, each time a new or updated record is created. This process will be slow and resource-intensive. You will need to write additional code and build cron jobs to run this in a continuous loop to get new and updated data as it appears in the Freshdesk.
  2. Infrastructure Maintainance: Always remember that any code that is written should be maintained because Freshdesk may modify its API or a datatype that your script doesn’t recognize may be sent by the API.

Simpler Alternative to Load Data from Freshdesk to Redshift

A more elegant, hassle-free alternative to loading data from Freshdesk to Redshift would be to use a Data Integration Platform like Hevo (14-day free trial) that works out of the box. Being a no-code platform, Hevo can overcome all the limitations mentioned above and seamlessly and securely more Freshdesk data to Redshift in just two steps:

  1. Authenticate and Connect Freshdesk Data Source
  2. Configure the Redshift Data warehouse where you need to move the data

Advantages of Using Hevo:

The Hevo data integration platform lets you move data from Google Analytics to BigQuery seamlessly. Here are some other advantages:

  1. No Data Loss – Hevo’s fault-tolerant architecture ensures that data is reliably moved from Freshdesk to Redshift without data loss. 
  2. 100’s of Out of the Box Integrations – In addition to Freshdesk, Hevo can bring data from Databases, Cloud Applications, SDKs and so on into Redshift in just a few clicks. This will ensure that you always have a reliable partner to cater to your growing data needs.
  3. Minimal Setup – Since Hevo is a fully managed, setting up the platform would need minimal effort and bandwidth from your end.
  4. Automatic schema detection and mapping – Hevo automatically scans the schema of incoming Freshdesk data. If any changes are detected, it handles this seamlessly by incorporating this change on Redshift.
  5. Exceptional Support –  Hevo provides 24×7 support to ensure that you always have Technical support for Hevo is provided on a 24/7 basis over both email and Slack.

Effective data analysis would require you to have reliable, up-to-date data in your warehouse. Hevo ensures that your Freshdesk data is consistently delivered in your Redshift Data Warehouse. 

Sign up for a 14-day free trial here. 

No-code Data Pipeline for Redshift