Query Google Analytics 360 raw logs

Setup

Google Analytics 360 allows access to raw logs stored on BigQuery. Google provides a public sample BigQuery dataset for GA 360, that can be queried either directly in the BigQuery interface, or via a client. For this tutorial series, we will use Jupyter Notebooks.

Step 1: enable connection to the BigQuery API from your notebook. In your GCP project, follow these steps to create a service account with access to BigQuery, and download a JSON file containing credentials.

Step 2: install the google-cloud-bigquery library with pip: pip install google-cloud-bigquery

Initiate connection

# Import libraries
import pandas as pd
from google.cloud import bigquery

# Launch BigQuery client from the JSON credentials file
bq_client = bigquery.Client.from_service_account_json('../bigquery_creds.json')

# Helper function for queries to be returned as a Dataframe
def bquery(sql):
    return bq_client.query(sql).to_dataframe()

Query

# Query the public Google Analytics 360 dataset
sql = """
SELECT *
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE _TABLE_SUFFIX = '20170801'
LIMIT 10
"""
bquery(sql)

	visitorId	visitNumber	visitId	visitStartTime	date	totals	trafficSource	device	geoNetwork	customDimensions	hits	fullVisitorId	userId	clientId	channelGrouping	socialEngagementType
0	NaN	1	1501583974	1501583974	20170801	{'visits': 1, 'hits': 1, 'pageviews': 1, 'time...	{'referralPath': None, 'campaign': '(not set)'...	{'browser': 'Chrome', 'browserVersion': 'not a...	{'continent': 'Americas', 'subContinent': 'Car...	[]	[{'hitNumber': 1, 'time': 0, 'hour': 3, 'minut...	2248281639583218707	None	None	Organic Search	Not Socially Engaged
1	NaN	1	1501616585	1501616585	20170801	{'visits': 1, 'hits': 1, 'pageviews': 1, 'time...	{'referralPath': None, 'campaign': '(not set)'...	{'browser': 'Chrome', 'browserVersion': 'not a...	{'continent': 'Americas', 'subContinent': 'Nor...	[{'index': 4, 'value': 'North America'}]	[{'hitNumber': 1, 'time': 0, 'hour': 12, 'minu...	8647436381089107732	None	None	Organic Search	Not Socially Engaged
2	NaN	1	1501583344	1501583344	20170801	{'visits': 1, 'hits': 1, 'pageviews': 1, 'time...	{'referralPath': None, 'campaign': '(not set)'...	{'browser': 'Chrome', 'browserVersion': 'not a...	{'continent': 'Asia', 'subContinent': 'Souther...	[{'index': 4, 'value': 'APAC'}]	[{'hitNumber': 1, 'time': 0, 'hour': 3, 'minut...	2055839700856389632	None	None	Organic Search	Not Socially Engaged
3	NaN	1	1501573386	1501573386	20170801	{'visits': 1, 'hits': 1, 'pageviews': 1, 'time...	{'referralPath': None, 'campaign': '(not set)'...	{'browser': 'Chrome', 'browserVersion': 'not a...	{'continent': 'Europe', 'subContinent': 'Weste...	[{'index': 4, 'value': 'EMEA'}]	[{'hitNumber': 1, 'time': 0, 'hour': 0, 'minut...	0750846065342433129	None	None	Direct	Not Socially Engaged
4	NaN	8	1501651467	1501651467	20170801	{'visits': 1, 'hits': 1, 'pageviews': 1, 'time...	{'referralPath': None, 'campaign': '(not set)'...	{'browser': 'Chrome', 'browserVersion': 'not a...	{'continent': 'Americas', 'subContinent': 'Nor...	[{'index': 4, 'value': 'North America'}]	[{'hitNumber': 1, 'time': 0, 'hour': 22, 'minu...	0573427169410921198	None	None	Organic Search	Not Socially Engaged
5	NaN	1	1501611552	1501611552	20170801	{'visits': 1, 'hits': 1, 'pageviews': 1, 'time...	{'referralPath': '/imgres', 'campaign': '(not ...	{'browser': 'Chrome', 'browserVersion': 'not a...	{'continent': 'Asia', 'subContinent': 'Southea...	[]	[{'hitNumber': 1, 'time': 0, 'hour': 11, 'minu...	8313021323030224050	None	None	Referral	Not Socially Engaged
6	NaN	2	1501600400	1501600400	20170801	{'visits': 1, 'hits': 1, 'pageviews': 1, 'time...	{'referralPath': None, 'campaign': '(not set)'...	{'browser': 'Chrome', 'browserVersion': 'not a...	{'continent': 'Americas', 'subContinent': 'Nor...	[{'index': 4, 'value': 'North America'}]	[{'hitNumber': 1, 'time': 0, 'hour': 8, 'minut...	9161549067325106850	None	None	Organic Search	Not Socially Engaged
7	NaN	1	1501640178	1501640178	20170801	{'visits': 1, 'hits': 1, 'pageviews': 1, 'time...	{'referralPath': None, 'campaign': '(not set)'...	{'browser': 'Chrome', 'browserVersion': 'not a...	{'continent': 'Asia', 'subContinent': 'Southea...	[{'index': 4, 'value': 'APAC'}]	[{'hitNumber': 1, 'time': 0, 'hour': 19, 'minu...	7712738124831804349	None	None	Organic Search	Not Socially Engaged
8	NaN	3	1501585492	1501585492	20170801	{'visits': 1, 'hits': 1, 'pageviews': 1, 'time...	{'referralPath': '/', 'campaign': '(not set)',...	{'browser': 'Chrome', 'browserVersion': 'not a...	{'continent': 'Americas', 'subContinent': 'Nor...	[{'index': 4, 'value': 'North America'}]	[{'hitNumber': 1, 'time': 0, 'hour': 4, 'minut...	6644155147493409979	None	None	Referral	Not Socially Engaged
9	NaN	1	1501635646	1501635646	20170801	{'visits': 1, 'hits': 1, 'pageviews': 1, 'time...	{'referralPath': None, 'campaign': '(not set)'...	{'browser': 'Chrome', 'browserVersion': 'not a...	{'continent': 'Americas', 'subContinent': 'Nor...	[{'index': 4, 'value': 'North America'}]	[{'hitNumber': 1, 'time': 0, 'hour': 18, 'minu...	2485028951030553624	None	None	Organic Search	Not Socially Engaged