analytics

Google Analytics dataset demystified | BigQuery ga_sessions

Omid

The first you need to know, I am referring to go google analytics 360. one click and the data is downloaded automatically to BigQuery. Notice this ga_session table is sharded, be sure to understand the differences between a sharded table vs partitioned table.

Documentation and Sample data can be seen here, below is basic examples:

SELECT *

FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170101`

gs session query date range variable suffix

SELECT
  *
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _table_suffix BETWEEN '20160801'
  AND '20170801'

ga session date range which is dynamic (last 2 weeks):

SELECT
  *
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _table_suffix BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 15 DAY))
  AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

The schema of the ga_session table is rather complex as it contains many complex data structures for the hits. the hits records contain all the custom dimensions user define in google analytics. A visual representation of ga_session table is in this link.

Analyzing the ga_session and extraction records and custom dimensions should like along the line:

SELECT
trafficSource.campaignCode AS Campaign_Code,
(SELECT distinct value FROM h.customDimensions where index=4) as cd_4

FROM
  `MyProject.MyDataSEt.ga_sessions_*` t, t.hits h

Notice there may be some CD’s on the user level information (notice CD2 and CD6):

SELECT
visitId AS  visitId,
hits.hitNumber  AS  hits_hitNumber,
hits.time   AS  hits_time,
hits.page.pagePath  AS  hits_page_pagePath,
-- hit scope custom dimensions
(SELECT value from hits.customDimensions where index=2) AS CD2,
-- user and session level custom dimensions
(SELECT value from sessions.customDimensions where index=6) AS CD6
FROM `MyProject.1111.ga_sessions_*` AS sessions, UNNEST(hits) as hits

I highly recommend the following article, as it is an excellent standard sql BigQuery cookbook by Johan van de Werken with steps by step explanations and examples.

——————————————————————————————————————————

I put a lot of thoughts into these blogs, so I could share the information in a clear and useful way. If you have any comments, thoughts, questions, or you need someone to consult with, feel free to contact me:

https://www.linkedin.com/in/omid-vahdaty/

Leave a Reply