This post will help you with syncing your Freshdesk to BigQuery. By doing this, you will perform advanced analytics on a system designed for this kind of data payloads, like Google BigQuery. Alternatively, you can simplify the process of syncing data from Freshdesk to BigQuery by using RudderStack, where RudderStack will handle the whole process. 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 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.
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 track all ongoing tickets and manage all the support-related communication across all channels. You can also produce various helpdesk reports to understand your team’s performance better, gauge your customers’ level of satisfaction, and gain important insight into 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 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.
Sign Up For Free And Start Sending Data
Test out our event stream, ELT, and reverse-ETL pipelines. Use our HTTP source to send data in less than 5 minutes, or install one of our 12 SDKs in your website or app.