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.

Image for post
Image for post

Originally published at http://jacquiwucom.wordpress.com on December 21, 2020.

Written by

A current Data Analyst in a subsidiary under Webjet, with experience in applying data science techniques to business.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store