Some query samples to interpret Google Analytics data using Bigquery

As a BI Analyst working in an Online Travelling Agency company, interpreting customer behaviors data into meaningful insights is a Business As Usual task.

Google Analytics is a popular web analytics service tracking website traffic. Therefore, for BI&Reporting team, how to interpret Google Analytics data seems to be an essential skill.

In the technical side, standard SQL can be used in Google BigQuery(cloud-based data warehousing platform) to generate data insights from Google Analytics.

Let’s take some query samples to have a look. Firstly, some dynamic values need to be understood before we write the first query:

  • Dimension: a parameter used for analysis sessions/users/hits, e.g, device.deviceCategory, device.browser, hits.type
  • projectID: ID of the project in BigQuery
  • dataSetName: data set in BigQuery with the information about Google Analytics sessions.
  • startDate: start date for the period analyzed in the following format: 2020-12-01
  • endDate: end date for the period analyzed in the folllowing format: 2020-12-01

So we can write a query sample to calculate the numbers of users, sessions and hits across set dimensions:

SELECT
  {{d​imension}​},​
  EXACT_COUNT_DISTINCT(fullVisitorId) AS users,
  EXACT_COUNT_DISTINCT(fullVisitorId_visitId) AS sessions,
  COUNT(hits.hitNumber) AS hits
FROM (
  SELECT
    fullVisitorId,
    fullVisitorId+'_'+ visitId AS fullVisitorId_visitId,
    hits.hitNumber,
    hits.type,
    device.browser,
    device.deviceCategory
  FROM
    TABLE_DATE_RANGE({{p​rojectID:d​ataSet.tableName}},
    TIMESTAMP('{{startDate}}'), 
    TIMESTAMP('{{endDate default}}')))
GROUP BY
  {{dimension}}

Another sample is to set up a funnel with steps better suited for your business, as what we do for the first query. Here are some dynamic values we need to use:

  • projectID: ID of the project in BigQuery
  • dataSetName: data set in BigQuery with the information about Google Analytics sessions
  • tableName: name of the table with the standard BigQuery export data

Then we can write a query sample as follows:

SELECT
  table_step1.date AS date,
  table_step1.step1 AS count_step1, 
  table_step2.step2 AS count_step2, 
  table_step3.step3 AS count_step3
FROM (
 --calculate transitions to Step 1 (Pageviews), grouped by days
  SELECT
  date,
  COUNT(*) AS step1
  FROM
   TABLE_DATE_RANGE([{{p​rojectID:dataSetName.tableName}​}],
   DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'), 
   DATE_ADD(CURRENT_TIMESTAMP(), 0,'DAY'))
  WHERE hits.eCommerceAction.action_type='2'
  GROUP BY 
    date) AS table_step1
JOIN (
--calculate the number of transitions to Ste​p 2 (Product description view) by days
  SELECT  
  date,
  COUNT(*) AS step2
  FROM 
    TABLE_DATE_RANGE([{{​projectID:dataSetName.tableName}​}],
    DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'), 
    DATE_ADD(CURRENT_TIMESTAMP(), 0,'DAY'))
  WHERE 
    hits.eventInfo.eventCategory CONTAINS 'Click' 
    AND hits.eventInfo.eventAction CONTAINS 'Characteristics of product'
  GROUP BY 
  date) AS table_step2
  ON
    table_step1.date=table_step2.date
JOIN (
--calculate transitions to Step 3 (Adding product to cart) by days
SELECT
  date,
  COUNT(*) AS step3
FROM 
  TABLE_DATE_RANGE([{{​projectID:dataSetName.tableName}​}], 
  DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'), 
  DATE_ADD(CURRENT_TIMESTAMP(), 0,'DAY'))
WHERE 
  hits.eCommerceAction.action_type='3'
GROUP BY 
  date) AS table_step3
ON
  table_step1.date=table_step3.date

There are more things we can do to track website traffic with BigQuery, maybe talk about it in next blog.

If you are interested in or have any problems with BigQuery, feel free to contact me.

Or you can connect with me through my LinkedIn.

Author: Jacqui

Data Science|Business Intelligence

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s