Freshdesk to BigQuery: 2 Easy Methods to Move Data

Does your team use Freshdesk for customer service and are looking to move this data to BigQuery to power advanced analytics? Transferring your data from Freshdesk to BigQuery will enable you to take advantage of the latter’s advanced analytical capabilities to analyze your customer support data. 

This blog discusses two methods that will help you achieve the connection from Freshdesk to BigQuery. This will enable you to evaluate the methods and choose the one which best suits your needs. Before we dive in, let us understand these applications briefly. 

Introduction to Freshdesk

Freshdesk is a software-as-a-service (SAAS) cloud-based helpdesk and customer service platform that helps you easily manage support-related communication across all channels. Freshdesk also creates reports that can help you gain a better understanding of customers and team performance. 

Freshdesk data can be accessed through Freshdesk’s REST API and loaded into the BigQuery data warehouse for further analysis.

Here are a few salient features of Freshdesk:

• Collaboration: Freshdesk allows you to share ownership of tickets with other teams without losing visibility into the progress being made on a specific issue. You can also link related tickets together to keep track of widespread issues and deliver consistent responses.
• Ticketing: Freshdesk’s ticketing feature lets you categorize, prioritize, and assign tickets so you don’t lose track of them. It also allows you to track and manage incoming support tickets from various channels with one inbox. It also lets you ensure that multiple agents don’t wind up working on the same ticket by mistake.
• Field Service: Freshdesk allows you to create service tasks for the tickets that need a field team response. It also allows you to track its status to completion. You can even track time spent working in the field with the mobile app and log in your billable hours.

Introduction to Google BigQuery

BigQuery is a cloud-based data warehouse that provides fast and scalable analysis of Big Data with SQL code through Google Cloud Storage. It is capable of analyzing terabytes of data in seconds. BigQuery is based on Dremel, a technology that has been used by Google for over a decade now. Google’s ease of use and implementation has made it one of the famous data warehouse options available today. Here are a few salient features of BigQuery:

• Natural Language Processing: With Data QnA, you can access the data insights they need through NLP while maintaining security and governance controls. Based on Analyza, Data QnA allows you to analyze petabytes of data through Google BigQuery. It can be embedded where users work, places like spreadsheets, chatbots, BI platforms like Looker, or custom-built User Interfaces.
• Built-in AI and ML Integrations: Google BigQuery integrates with TensorFlow and Vertex AI that allows you to train and execute powerful models on structured data in no time flat. All it takes is SQL.
• Serverless: Google BigQuery’s serverless Data Warehouse carries out resource provisioning behind the scenes which allows you to focus on analysis and data instead of worrying about securing, upgrading, or managing the infrastructure.

Read more about Google BigQuery here.

Methods to connect Freshdesk to BigQuery

Multiple methods can be used to connect Freshdesk to BigQuery and load data easily:

• Method 1: Using Freshdesk APIs to connect Freshdesk to BigQuery
• Method 2: Using Hevo Data to connect Freshdesk to BigQuery

Method 1: Using Freshdesk APIs to connect Freshdesk to BigQuery

This process of connecting Freshdesk to BigQuery encompasses the following 3 steps:

• Step 1: Extracting Data from Freshdesk
• Step 2: Preparing the Data Extracted from Freshdesk
• Step 3: Loading Data into Google BigQuery

Step 1: Extracting Data from Freshdesk

Freshdesk data can be extracted by making calls to its REST API. For example, data on all tickets can be obtained using GET /api/v2/tickets. The data returned will be in JSON format. Below is a sample of data from Freshdesk:

{

  "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" : [ ]

}

Additional information on the Freshdesk API can be found here.

Step 2: Preparing the Data Extracted from Freshdesk

You may have to create a schema for your data tables before you load them. You also have to make sure that the data types in Google BigQuery match the attributes and data types from Freshdesk. Freshdesk provides support for many of the popular data types. Additional information on the types and their corresponding Freshdesk attribute names can be found here.

Information on the data types supported by BigQuery can be found on this page.

Step 3: Loading Data into Google BigQuery

The data can be uploaded directly from the JSON file to a BigQuery table using the BigQuery GUI. Alternatively, you can follow these steps:

• Load the data into GCS with gsutil.
• Write code through the BigQuery Command Line Interface (bq) to create a table to store your data and specify the schema. This can be done with the bq load command.
• Load the data into your table.

Additional information on loading JSON data through the bq Command Line Interface can be found here.

Limitations of Migrating Data Using Custom Code

• Maintenance: This method may result in inaccurate data whenever the Freshdesk API is down or if you have any issues connecting to it.  
• Hard to Perform Data Transformations: It is impossible to perform fast data transformations like standardizing dates, times, etc or currency conversions under this method
• Data Availability Limitations: You have to write a lot of extra to code or configure cron jobs to enable basic real-time functionality with this method
• Labor Intensive and Time Consuming: This method requires you to write a lot of custom code. This is very time-consuming and could become a problem when there are tight deadlines to meet. 

Method 2: Using Hevo Data to connect Freshdesk to BigQuery

Steps to use Hevo Data:

Hevo Data focuses on two simple steps to move data from Freshdesk to BigQuery to get you started:

Click on +Create to create a new Pipeline

Configure Source: Connect Hevo Data with Freshdesk by simply providing the API key, domain identifier, and pipeline name.

Integrate Data: Load data from Freshdesk to BigQuery by providing your BigQuery database credentials such as your authorized Google BigQuery account, along with a name for your database, dataset id, GCS bucket, sanitize table/column names, destination, and project Id.

Advantages of using Hevo Data Platform:

• Real-Time Data Export: Hevo with its strong integration with 100+ sources, allows you to transfer data quickly & efficiently. This ensures efficient utilization of bandwidth on both ends.
• Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
• Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
• Minimal Learning: Hevo with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
• Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
• Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.

Conclusion

This blog talks about the two methods you can use to establish a connection from Freshdesk to BigQuery in a seamless fashion: using Freshdesk APIs and a third-party tool, Hevo Data.

Extracting complex data from a diverse set of data sources can be a challenging task and this is where Hevo saves the day! 

Visit our Website to Explore Hevo

Hevo offers a faster way to move data from Databases or SaaS applications like Google Analytics 4 into your Data Warehouse to be visualized in a BI tool such as Google Data Studio. Hevo is fully automated and hence does not require you to code. 

FAQ

How do I transfer data to BigQuery?

• Go to the BigQuery Console.
• In the navigation panel, select your dataset.
• Click “Create Table.”
• In the “Source” section, select your data source (e.g., Google Cloud Storage, local file, etc.).
• Configure the schema and other settings.
• Click “Create Table.”

How do you load data into BigQuery?

To load CSV files perform the following steps:

• Specify the schema.
• Use the Web UI, bq command, or API to load the CSV file.

How to migrate UA data to BigQuery?

• Use Google Analytics 360 to export data to Google Cloud Storage.
• If you have raw data exports, save them to Google Cloud Storage.

Want to try Hevo and load your data from Freshdesk for free to any destination of your choice? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. Have a look at our unbeatable pricing, which will help you choose the right plan for you.

What are your thoughts on moving data from Freshdesk to BigQuery? Let us know in the comments.