Category
Google Cloud Platform
Published on
April 26, 2020
‣
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 |