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 helping Marketing, Sales, and growth teams to gain insights on user data and assist in improving the site, strategies, etc. Google BigQuery is a popular and widely used Data Warehouse for storing and analyzing 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.
Prerequisites
- An active Amplitude account.
- An active Google BigQuery account.
What is Google BigQuery?
Image Source
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.
To learn more about Google BigQuery, click here.
What is Amplitude?
Image Source
Amplitude is a Product Analytics tool for analyzing 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, 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.
Key Features of Amplitude
Some of the main features of Amplitude are listed below:
- Real-time User Analytics: Amplitude help companies perform real-time Data Analytics on the user data that 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 use Machine Learning to help users forecast outcomes over time and study future trends.
To learn more about Amplitude, click here.
Setting Up Amplitude BigQuery Integration
Now that you have read about Amplitude and Google BigQuery. In this section, you will learn about the steps to set up Amplitude BigQuery Integration for data transfer from Amplitude to BigQuery. The following methods for Amplitude BigQuery Integration are listed below:
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
- Open your Google Cloud Console and navigate to the project selector page here.
- 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 here.
Step 2: Enabling the BigQuery Data Transfer Service
- To enable the Data Transfer Service for Amplitude BigQuery Integration, you must be granted with Owner role.
- From the API library, open the Google BigQuery Data Transfer API here.
- Next, select the Amplitude BigQuery project from the drop-down menu.
- Click on the “ENABLE” button.
Step 3: Creating Service Account for Amplitude
- First, enable the IAM API here, if you haven’t done it already for your project.
- Now, you need the following permissions to manage service accounts. You have to grant the Service Account Key Admin role (roles/iam.serviceAccountKeyAdmin).
- Now, go to the Create service account page here, 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 here.
- Now, go to the “Data Destination” option.
- Here, click on the “Google BigQuery” option under the “Add More Destinations” section.
Image Source
- 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.
Image Source
- 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.
Image Source
- 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
There are a few limitations to method 1, listed below:
- 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
Image Source
Hevo is a No-code Data Pipeline. It supports pre-built data integrations from 100+ data sources, including Amplitude which is one of Hevo’s free sources. Hevo offers a fully managed solution for your Data Migration process. It will automate your data flow in minutes without writing any line of code.
Get Started with Hevo for Free
Its fault-tolerant architecture makes sure that your data is secure and consistent. Hevo provides you with a truly efficient and fully-automated solution to manage data in real-time and always have analysis-ready data at your data warehouse.
Using Hevo Data, you can connect Amplitude to BigQuery in the following 2 steps:
Step 1: Configuring Amplitude Analytics as a Source
- Now, log in to your Amplitude account here.
- Navigate to the “Settings” from the left navigation menu.
Image Source
- In the “Org Settings” click on the “Projects” option.
Image Source
- Here, select the project whose data you want to export to Google BigQuery.
- Copy the “API Key” and “Secret key” from the project details.
Image Source
- Log in to your Hevo account here.
- Click on the “PIPELINES” option from the “Asset Palette”.
- Click on the “+ Create” button from the “Pipelines List View“.
- Now, select the “Amplitude Analytics” source from the “Select Source Type” page.
- It will open the “Configure your Amplitude Analytics Source” and here provide the required details for Amplitude BigQuery Integration.
Image Source
- 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.
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 maps it to the destination schema.
- Live Monitoring: Advanced monitoring gives you a one-stop view to watch 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.
Sign up here for a 14-Day Free Trial!
Conclusion
In this article, you learnt about Amplitude, Google BigQuery, and the 2 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.
Visit our Website to Explore Hevo
Companies store valuable data from multiple data sources into Google BigQuery. The manual process to transfer 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 to load and transform data from 100+ 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 first hand.
Share your experience of learning about Amplitude BigQuery Integration in the comments section below!
Aditya has a keen interest in data science and is passionate about data, software architecture, and writing technical content. He has experience writing around 100 articles on data science.