Setting Up Amplitude BigQuery Integration: 2 Easy Methods

Easily move your data from Amplitude to BigQuery to enhance your analytics capabilities. With Hevo’s intuitive pipeline setup, data flows in real-time—check out our 1-minute demo below to see the seamless integration in action!

Analyzing user behavior to increase Sales is one of the essential operations for any digital business. There are many platforms available in the market that allow the organization to get insights on their Marketing Campaigns, website analytics, apps, and user data for Marketing and Sales purposes.

Amplitude is a product analytics platform that helps marketing, sales, and growth teams gain insights on user data and assist in improving the site, strategies, etc. Google BigQuery is a popular and widely used data warehouse that stores and analyzes data. Connecting Amplitude Google BigQuery allows companies to get a unified view of their data.

Amplitude BigQuery Integration enables Business users to analyze Amplitude data with other business data to get an overall view of the business. In this article, you will learn about the steps to set up Amplitude BigQuery Integration using manual and automated methods.

What is Google BigQuery?

Google BigQuery is a Cloud Data Warehouse service provider and a part of Google Cloud Platform. It helps companies store and analyze their business data at a secure Data Warehouse. Google allows users to leverage other Google Cloud Platform features such as engines, APIs, etc on their data directly from the Google BigQuery Data Warehouse.

Google BigQuery can manage terabytes of data using SQL language. Also, it enables companies to analyze their data stored in Data Warehouse using SQL queries. Google BigQuery has a Columnar Storage structure that helps in delivering faster query processing and file compression. 

Key Features of Google BigQuery

Some of the main features of Google BigQuery are listed below:

• Integrations: Google BigQuery supports integration with other Google products, partnered apps, and services for easy data accessibility.
• Encrypted Models: With the help of Google BigQuery ML, users can encrypt their Machine Learning models using Customer-Managed Encryption Keys (CMEK).
• Automatic Backup: Google BigQuery automatically creates backup and recovery options to secure data.
• BI Engine: Google BigQuery offers an in-memory analysis service that allows users to analyze large datasets in the Data Warehouse itself making the sub-query response time faster.

What is Amplitude?

Amplitude is a product analytics tool that analyzes user behavior. It helps companies analyze data for the product, marketing, and growth team and generate insights. Amplitude makes it easier for companies to make smarter data-driven decisions for their Marketing and Sales.

Amplitude allows users to keep track of visitors to your site and mobile apps and monitor them using collaborative models. Also, it provides customer behavioral reports using Relative Time Analysis. Its dynamic and flexible nature allows users to perform Funnel Analysis. Check out how to set up the Amplitude dashboard easily.

Key Features of Amplitude

Some of the main features of Amplitude are listed below:

• Real-time User Analytics: Amplitude helps companies perform real-time data analytics on user data, which provides information such as retention, funnels, revenue analysis, and flexible user segmentation. 
• Cross-Functional Data Access: Amplitude offers easy data access that increases the decision quality because of high-quality data access and increased visibility.
• Predictive Analysis: Amplitude uses Machine Learning to help users forecast outcomes over time and study future trends.

See also: Amplitude to Redshift Connection

Prerequisites

• An active Google BigQuery account.
• An active Amplitude account.

Method 1: Manually Connecting Amplitude to BigQuery

In this method, you need to manually set up and configure Amplitude BigQuery Integration. The following steps for manual Amplitude BigQuery Integration are listed below.

• Step 1: Creating Amplitude BigQuery Project
• Step 2: Enabling the BigQuery Data Transfer Service
• Step 3: Creating Service Account for Amplitude
• Step 4: Setting Up Recurring Data Export

Step 1: Creating Amplitude BigQuery Project

• Open your Google Cloud Console and navigate to the project selector page.
• Next, select the existing project or create a new Google Cloud project for Amplitude BigQuery Integration.
• Now, enable the billing on the project for all the transfers for Amplitude BigQuery Integration.
• Next, enable the Google BigQuery API.

Step 2: Enabling the BigQuery Data Transfer Service

• To enable the Data Transfer Service for Amplitude BigQuery Integration, you must be granted the Owner role.
• From the API library, open the Google BigQuery Data Transfer API.
• Next, select the Amplitude BigQuery project from the drop-down menu. Here, I have named my project “My Project 49591”.
• Click on the “ENABLE” button.

Step 3: Creating a Service Account for Amplitude

• First, enable the IAM API if you haven’t done it for your project already.
  • Now, you have to grant the Service Account Key Admin role (roles/iam.serviceAccountKeyAdmin)

• Now, go to the Create Service Account page in the Google Console.
• Then, select the project for Amplitude BigQuery Integration.
• Now, select the E-Mail address of the service account for which you want to create the key.
• Then, click on the “Add Key” drop-down menu and select the “Create new key” option.
• From the “Key type” option, click on the “JSON” format and then click on the “Create” button.

• It will download the service account key file for Amplitude BigQuery Integration.
• The format for the account service key is given below.

{
  "type": "service_account",
  "project_id": "project-id",
  "private_key_id": "key-id",
  "private_key": "-----BEGIN PRIVATE KEY-----nprivate-keyn-----END PRIVATE KEY-----n",
  "client_email": "service-account-email",
  "client_id": "client-id",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://accounts.google.com/o/oauth2/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/service-account-email"
}

Step 4: Setting Up Recurring Data Export 

• To set up recurring data export for Amplitude BigQuery, you should have the following roles enabled:
  • BigQuery User
  • BigQuery Data Editor
  • A custom role that has the following permissions enabled:
    • bigquery.transfers.get
    • bigquery.transfers.update
• Now, upload the Amplitude BigQuery service account key file to Amplitude.
• Log in to your Amplitude account.

• Now, go to the “Data Destination” option.
• Here, click on the “Google BigQuery” option under the “Add More Destinations” section.

• This will open up the “Getting Started” page for Amplitude BigQuery Integration.
• Now, from the “Export Data to BigQuery” section, choose the data that you want to export from Amplitude to BigQuery.

• Review all the schemas for the Event table and Merge IDs table. Then click on the “Next” button.
• This will open the “Set Up Export” page.
• From the “Google Cloud Credentials For Amplitude” section, provide details for the Google BigQuery dataset that will receive the Amplitude data on export. 
• Also, upload the service account key file.

• Then, click on the “Next” button. Now it will perform the test upload for Amplitude BigQuery. Once the upload is successful, click on the “Finish” button.

Limitations of the Manual Method

• It consumes time that can be used to analyze the user data.
• It requires many permissions to grant and APIs to enable. 
• With this method, it is overwhelming to monitor the data transfer.

Method 2: Using Hevo for Amplitude BigQuery Integration

Using Hevo Data, you can connect Amplitude to BigQuery in the following 2 steps:

• Step 1: Configuring Amplitude Analytics as a Source
• Step 2: Configuring Google BigQuery as a Destination

Step 1: Configuring Amplitude Analytics as a Source

• Now, log in to your Amplitude account.
• Navigate to the “Settings” from the left navigation menu.

• In the “Org Settings” click on the “Projects” option.

• Here, select the project whose data you want to export to Google BigQuery.
• Copy the “API Key” and “Secret key” from the project details.

• Log in to your Hevo account.
• Now, select the “Amplitude Analytics” source from the “Select Source Type” page.
• It will open “Configure your Amplitude Analytics Source” and provide the required details for Amplitude BigQuery Integration.

• Click on the “TEST & CONTINUE” to proceed and set up the destination.

Step 2: Configuring Google BigQuery as a Destination

• Add Google BigQuery as a destination from the “Add Destination” page.
• Here, set up a new Data Warehouse or select the existing one by providing details of Google BigQuery Data Warehouse.

• Then, click on the “SAVE DESTINATION” button.

That’s it! You have successfully integrated Amplitude BigQuery using Hevo using just two methods. Let’s look at some salient features of Hevo:

• Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
• Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
• Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
• Schema Management: Hevo can automatically detect the schema of the incoming data and map it to the destination schema.
• Live Monitoring: Advanced monitoring gives you a one-stop view of all the activities that occur within pipelines.
• Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Explore our guide on Amplitude and HubSpot integration to optimize your customer insights and streamline your marketing processes.

Conclusion

In this article, you learned about Amplitude, Google BigQuery, and the two methods to perform Amplitude BigQuery Integration. The first method is a manual one, where you need to set up and configure all the requirements and eventually consumes time. The other method is automated, easy, fast, and reliable. Connecting Amplitude BigQuery provides more data accessibility for Data Analytics. Companies store valuable data from multiple data sources in Google BigQuery.

The manual process of transferring data from Amplitude to destination is a tedious task. Hevo Data is a No-code Data Pipeline that can help you transfer data from Amplitude Analytics for free or any data source to desired Google BigQuery. It fully automates the process of loading and transforming data from 150+ data sources to a destination of your choice without writing a single line of code.

Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite firsthand.
Share your experience of learning about Amplitude BigQuery Integration in the comments section below!

FAQs