How to load data from Freshdesk to Google BigQuery

Blendo Team

This post will help you with syncing your Freshdesk to BigQuery. By doing this you will be able to perform advanced analytics on a system that is designed for this kind of data payloads, like Google BigQueryAlternatively, you can simplify the process of syncing data from Freshdesk to BigQuery by using Blendo, where the whole process will be handled by Blendo and you can focus on what matters, the analysis of your customer support data.

Access your data on Freshdesk

The first step in loading your Freshdesk data to any kind of data warehouse solution is to access them and start extracting it.

Freshdesk offers a rich and well-defined API that belongs to the Representational State Transfer (REST) category. Using it you can perform RESTful operations such as reading, modifying, adding and deleting of your helpdesk data, thus allowing you to programmatically interact with your account.

Among the 18 provided resources, you can find information about Tickets and Conversations, Agents, Companies, Surveys and Satisfaction Ratings and many more.

You will need more time to read this post than integrating Freshdesk to BigQuery.

Effortlessly Sync All Your Freshdesk Data to BigQuery

In addition to the above, the things that you have to keep in mind when dealing with the Freshdesk API, are:

  1. Rate limits. Depending on the chosen plan and API version that is being used, Freshdesk allows an amount of API calls per hour.
  2. Authentication. You authenticate on Freshdesk using an API key.
  3. Paging and dealing with big amount of data. Platforms like Freshdesk that are dealing with clickstream data tend to generate a lot of data, like events on your web properties.

About Freshdesk

Freshdesk is a SaaS customer support platform released by Freshworks that integrates traditional support channels such as email, phone, and LiveChat with social channels like Facebook or Twitter.

While using Freshdesk as your ticketing platform you can easily keep track of all ongoing tickets as well as manage all the support-related communication across all channels. You can also produce various helpdesk reports in order to better understand your team’s performance, gauge your customers’ level of satisfaction and gain important insight regarding possible improvements.

Transform and prepare your Freshdesk Data for Google BigQuery

After you have accessed data on Freshdesk, you will have to transform it based on two main factors,

  1. The limitations of the database that is going to be used
  2. The type of analysis that you plan to perform

Each system has specific limitations on the data types and data structures that it supports. If for example you want to push data into Google BigQuery, then you can send nested data like JSON directly.

Of course, when you are dealing with tabular data stores, like Microsoft SQL Server, this is not an option. Instead, you will have to flatten out your data, just as in the case of JSON, before loading into the database.

Also, you have to choose the right data types. Again, depending on the system that you will send data to and the data types that the API exposes to you, you will have to make the right choices. These choices are important because they can limit the expressivity of your queries and limit your analysts on what they can do directly out of the database. Freshdesk has a very limited set of available data types which means that your work to do these mappings is much easier and straightforward, but nonetheless equally important with any other case of a data source.

Due to the rich and complex data model that Freshdesk follows, some of the provided resources might have to be flattened out and be pushed in more that one table.

Load from Freshdesk to Google BigQuery

If you want to load Freshdesk data to BigQuery, you have to use one of the following supported data sources.

  1. Google Cloud Storage
  2. Sent data directly to BigQuery with a POST request
  3. Google Cloud Datastore Backup
  4. Streaming insert
  5. App Engine log files
  6. Cloud Storage logs

From the above list of sources, 5 and 6 are not applicable in our case.

For Google Cloud Storage, you first must load data to it, there are a few options on how to do this, for example, you can use the console directly as it is described here and do not forget to follow the best practices.

Another option is to post data through the JSON API, as we see again APIs play an important role in both the extraction but also the loading of data in our data warehouse. In its simplest case, it’s just a matter of one HTTP POST request using a tool like CURL or Postman.

After you have loaded data inside Google Cloud Storage, you have to create a Load Job for BigQuery to actually load any data into it, this Job should point to the source data in Cloud Storage that have to be imported, this happens by providing source URIs that point to the appropriate objects.

The best way to load data from Freshdesk to Google BigQuery and possible alternatives

So far we just scraped the surface of what can be done with Google BigQuery and how loading of data to it can be done. The way to proceed relies heavily on data you want to load, from which service they are coming from and the requirements of your use case.

Things can get even more complicated if you want to integrate data coming from different sources. A possible alternative, instead of writing, hosting and maintaining a flexible data infrastructure, is to use a product like Blendo that can handle this kind of problems automatically for you.

Blendo integrates with multiple sources or services like databases, CRM, email campaigns, analytics and more.  Quickly and safely move all your data by Freshdesk to BigQuery and start generating insights from your data.

Help your customer support and executive team take ownership of the insights that live in your Freshdesk customer support platform.

Blendo is the easiest way to automate powerful data integrations.

Try Blendo free for 14 days. No credit card required.