Google Analytics is one of the most commonly used services for tracking individual customer session data, website traffic, purchase information, and other important metrics. The large amount of data produced by Google Analytics can help in the extraction of useful as well as actionable business insights.
Despite its usefulness, Google Analytics does not come accompanied by an ideal format for its produced data analysis. This has led to the growing need for moving data to the Data warehouse from the application, i.e., moving the Google Analytics data to BigQuery.
BigQuery is basically a prominent data warehouse from Google. Datasets with extremely large sizes can be queried in a SQL-like manner very quickly with BigQuery. As a result of Google’s vast infrastructure and proprietary technology, like the Dremel query engine, query processing is made fast and efficient.
In this article, you will be learning everything about how to stream Google Analytics data to BigQuery. To learn how to stream data from Google Analytics to BigQuery by using Google Apps Script, follow the below-mentioned steps for a hassle-free streaming experience.
Google Analytics 4 is replacing universal analytics and as per Google, you can only find GA4 data for a maximum of 14 months. If you want to retain data longer than that, you can always use the awesome Google Bigquery service.
You can stream data from Google Analytics to BigQuery using Google Apps Script by following these steps:
Create a new project in Google Apps Script by going to https://script.google.com and clicking on the “+” icon.
In the script editor, click on the “Resources” menu and select “Advanced Google Services”.
Enable the “BigQuery API” by turning the switch to “ON”.
Click on the “Google Cloud Platform Console” link to open the Cloud Console.
In the Cloud Console, create a new project or select an existing project.
In the Cloud Console, go to the “BigQuery” page and create a new dataset.
In the Apps Script editor, go to the “File” menu and select “Project properties”.
In the “Project properties” window, click on the “Script properties” tab.
Add a new script property with the name “bq_project_id” and the value set to your BigQuery project ID.
Add a new script property with the name “bq_dataset_id” and the value set to your BigQuery dataset ID.
In the Apps Script editor, copy and paste the following code:
function streamDataToBigQuery() {
// Replace with your table ID
var tableId = 'ga_sessions_20221231';
// Replace with your view ID
var viewId = 'ga:12345678';
// Replace with your start and end dates
var startDate = '2022-12-01';
var endDate = '2022-12-31';
// Construct the request payload
var payload = JSON.stringify({
'reportRequests': [ { 'viewId': viewId, 'dateRanges': [ { 'startDate': startDate, 'endDate': endDate } ],
'metrics': [ { 'expression': 'ga:sessions' } ],
'dimensions': [ { 'name': 'ga:country' }, { 'name': 'ga:deviceCategory' } ]
}
]
});
// Send the request and get the response
var response = Analytics.Reports.batchGet(payload);
var rows = response.reports[0].data.rows;
// Create the data for the BigQuery insert request
var data = [];
for (var i = 0; i < rows.length; i++) {
var row = rows[i];
var values = [];
values.push(row.dimensions[0]);
values.push(row.dimensions[1]);
values.push(row.metrics[0].values[0]);
data.push({
insertId: 'row-' + i,
json: {
country: row.dimensions[0],
device: row.dimensions[1],
sessions: row.metrics[0].values[0]
}
});
}
// Set up the insert request options
var options = {
method: 'POST',
contentType: 'application/json',
payload: JSON.stringify({ rows: data })
};
// Construct the insert request URL
var url = 'https://www.googleapis.com/bigquery/v2/projects/' + PropertiesService.getScriptProperties().getProperty('bq_project_id') + '/datasets/' + PropertiesService.getScriptProperties().getProperty('bq_dataset_id') + '/tables/' + tableId + '/insertAll';
// Send the insert request
UrlFetchApp.fetch(url, options);
}
