How to stream Google Analytics data to Bigquery

How to stream Google Analytics data to Bigquery

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:

  1. Create a new project in Google Apps Script by going to https://script.google.com and clicking on the “+” icon.
  2. In the script editor, click on the “Resources” menu and select “Advanced Google Services”.
  3. Enable the “BigQuery API” by turning the switch to “ON”.
  4. Click on the “Google Cloud Platform Console” link to open the Cloud Console.
  5. In the Cloud Console, create a new project or select an existing project.
  6. In the Cloud Console, go to the “BigQuery” page and create a new dataset.
  7. In the Apps Script editor, go to the “File” menu and select “Project properties”.
  8. In the “Project properties” window, click on the “Script properties” tab.
  9. Add a new script property with the name “bq_project_id” and the value set to your BigQuery project ID.
  10. Add a new script property with the name “bq_dataset_id” and the value set to your BigQuery dataset ID.
  11. 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);
}