As an integral part of the Google Cloud Platform (or GCP), Google BigQuery is being deployed as a comprehensive data warehousing solution for a variety of industry applications. With its seamless integration with Google Analytics (GA), BigQuery (or BQ) enables data analysts and developers to store, integrate, as well as analyse big data through its standard interface and APIs.
Among the widely used analytics tools in the market today, Google Analytics is being deployed to monitor website traffic, derive useful business insights from customer sessions, and much more. As a suitable platform for business intelligence (or BI), GA can be integrated with the BQ tool to transfer the business data and enable deeper and richer analysis.
Through this article, we shall explore the business benefits of Google Analytics to BigQuery Automation and how you can configure this setup. To begin with, let’s understand what is meant by Google Analytics BigQuery automation.
What is Google Analytics to BigQuery Automation?
In simple terms, the Google Analytics to BigQuery Automation process is an automated BigQuery job that eliminates any manual querying tasks. This typically includes tasks like importing Google Analytics data to BigQuery or executing any SQL queries.
This automated process can be used to connect Google Analytics to BigQuery and set up a seamless integration between the two tools. Developers who are familiar with SQL syntax should be able to easily automate the BigQuery jobs using this connection.
In the following section, let’s examine how the Google Analytics to BigQuery Automation configuration can be set up (with the aid of an industry case study).
How to Configure the Google Analytics to BigQuery Automation Process
You can automate the data loading of Google Analytics to BigQuery with the help of any of the following data sources:
Google Cloud Storage
BigQuery supports the data loading from Cloud Storage in various formats including Avro, CSV (default), JSON, and ORC. You can use the Google console directly to perform this loading.
Another option is to post the data directly using JSON APIs. Google Analytics API play a crucial role in both the loading and extraction of data into the BQ data warehouse. For instance, you can execute the HTTP POST request with the CURL or Postman tool.
You can configure the entire automation process with the following few steps using any Query Editor tool:
- Create your query in the editor tool (example, as shown below).
- Create the new schedule query using the “Schedule query” option shown above.
- The next step is to schedule the new query using the “New Scheduled Query” option. Apart from the scheduled query name, ensure that you specify the target table name. Besides these, you can specify the following configuration settings:
- Repeat frequency: when the queries must be repeated (daily, weekly, or monthly) along with the start and end date & time
- Table write preference: where you can specify if the query should either overwrite or append data to the target table.
- Email notifications: where you can select if you wish to be notified on email of the execution of a successful query.
- Finally, click “Schedule” to schedule your query with the above settings.
Next, let’s look at an industry case study for implementing Google Analytics to BigQuery Automation, which was facilitated by Countants:
The client was facing an issue downloading their marketing data (from the Facebook platform) directly into Google BigQuery using the Supermetrics add-on tool for marketing reports. Due to a deprecated database table column, the SQL query used to combine the partitioned tables was not working. As a result, the client was not able to append data to the master database table. As a solution, the client was looking for automation that could perform data appending regularly.
Here are the steps that we executed to fix this client problem:
- Checked the SQL query used for appending data to the master table regularly. We could still append data even though a few table columns were deprecated in the Facebook database tables.
- To connect data to Google Analytics using the Google Analytics API, we built the automation using the GA authorization keys like a service account.
- After the authorization process, we could import the data from Google Analytics.
- Using the Google ETL scheduler tool, we scheduled a Cronjob job (at an interval of every 10 minutes) that could import GA data from the client server into the dashboard.
To perform this step, a Python API script was used to read the ViewID parameters from the spreadsheets and pull the Google Analytics data to BigQuery at an interval of every 10 minutes.
In the next section, we shall look at some of the business benefits of Google Analytics to BigQuery Automation.
Business Benefits of Google Analytics to BigQuery Automation
BQ automation has multiple benefits including using automated BigQuery jobs to directly populate business dashboards (as discussed in the case study).
Let’s look at some more benefits:
Advanced Cohort Analysis
Using Google Analytics, businesses can use Cohort Analysis to estimate customer retention on the basis of their acquisition date. However, using Google Analytics, the cohort analysis report is limited to a maximum of 3 months of customer data.
BigQuery automation can extend the capabilities of cohort analysis using Google Data Studio. BQ jobs can be automatically executed to process the GA data, store them in staging tables, and finally populate the cohort analysis report each month with new and updated data. Here’s an example of a cohort analysis report in Google Data Studio that was enabled by BigQuery automation.
Data Pre-Processing and Cleaning
Automated BigQuery jobs can be useful when it comes to pre-processing and cleaning of Google Analytics data.
For instance, pre-processed data in Google Analytics shows multiple page-wise metrics such as page views, average page time, and bounce rates. When this data is exported to BigQuery, it is no longer aggregated, which can complicate the BigQuery analysis process. However, using BigQuery, you can calculate the same GA metrics and automate the query to update as new data is being exported daily from GA to BQ.
Additionally, BigQuery automation can be used to clean the GA data. For example, a BQ query can be written to eliminate any additional characters on page titles and then automatically run to generate BQ reports with clean page titles.
Integration with other data sources
BigQuery automation allows you to integrate your Google Analytics data with other external data sources. As an example, traditional offline data such as transactions in a brick-and-mortar store can be integrated seamlessly with BigQuery.
Another example of seamless integration is the ability to move BQ data to Google Cloud Storage using Google Apps Script any cloud-based tool.
Finally, BigQuery job automation is useful for data scientists and analysts for updating their data models on the basis of new data. For instance, BQ jobs can be scheduled to create a pipeline that can automatically add new users to a specific user cluster on the basis of their online behavior.
This type of automated user assignment is often used by digital marketers looking to identify and group users based on common interests.
The increasing value of big data analytics for business presents a lot of use cases for BigQuery technology. Through Google Analytics to BigQuery automation, data analysts can save time as well as extract deeper insights from the latest Google Analytics data.
With state-of-the-art analytics technology, Countants is best poised to deliver value to its global customers looking to leverage on BQ automation. Our technical skills in cloud-powered analytics and visualization is driving faster and more accurate decision making for customers looking for a competitive advantage in today’s market.
Do you want to explore the benefits of automated BigQuery analytics for your business data? Do visit our website and leave us a message.