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/