How to load data from Freshdesk to Google BigQuery

Access your data on Freshdesk

The first step in loading your Freshdesk data to any data warehouse solution is accessing them and starting extracting them.

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 your helpdesk data, thus allowing you to interact with your account programmatically.

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

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 being used. Freshdesk allows a number of API calls per hour.

2. Authentication - You authenticate on Freshdesk using an API key.

3. Paging and dealing with big data - Platforms like Freshdesk dealing with clickstream data tend to generate a lot of data, like events on your web properties.

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 you want to push data into Google BigQuery, 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 it into the database.

Also, you have to choose the right data types. Again, depending on the system, 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 equally important with any other data source case.

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 than 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, and 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 we can load data into it. 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. Instead of writing, hosting, and maintaining a flexible data infrastructure, a possible alternative is to use a product like RudderStack to handle this kind of problem you automatically.

RudderStack 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. Don't want to go through the pain of direct integration? RudderStack’s Freshdesk to Google BigQuery integration makes it easy to send data from Freshdesk to Google BigQuery.