Load Data from Freshdesk to Redshift in 2 East Steps

Are you looking to load data from Freshdesk to Redshift for deeper analysis? Or are you looking to simply create a backup of this data in 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. 

What is 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. 

What is Amazon Redshift?

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

Find out more on Amazon Redshift Data Warehouse here.

Methods to Load Data from Freshdesk to Redshift

• Method 1: Loading Data from Freshdesk to Redshift Using Custom ETL Scripts
• Method 2: Load Data from Freshdesk to Redshift Using Hevo

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. 

Method 1: Loading Data from Freshdesk to Redshift Using Custom ETL 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" : [ ]
}

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.

Method 2: Load Data from Freshdesk to Redshift Using Hevo

A more elegant, hassle-free alternative to loading data from Freshdesk (Free Data Source) 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

Sign up here for a 14-Day Free Trial!

Advantages of Using Hevo

The Hevo data integration platform lets you move data from Freshdesk (Free Data Source) to Redshift 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 100+ Data Sources (Including 30+ Free Data Sources) 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.

As an alternate option, if you use Google BigQuery, you can also load your data from Freshdesk to Google BigQuery using this guide here.

Conclusion

This article teaches you how to set up Freshdesk to Redshift Data Migration with two methods. It provides in-depth knowledge about the concepts behind every step to help you understand and implement them efficiently.

The first method, however, can be challenging especially for a beginner & this is where Hevo saves the day. Hevo Data, a No-code Data Pipeline, helps you transfer data from a source of your choice in a fully-automated and secure manner without having to write the code repeatedly.

Visit our Website to Explore Hevo

Hevo, with its strong integration with 100+ sources & BI tools, allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiff.

Want to take Hevo for a spin? Sign Up here for the 14-day free trial and experience the feature-rich Hevo suite first hand.

Tell us about your experience of setting up Freshdesk to Redshift Data Transfer! Share your thoughts in the comments section below!