Google Analytics (GA) is a popular suite of analytic tools used by many companies to track customer interactions on their digital channels. Although it offers plenty of built-in capabilities for insights discovery, there are times when you want to deep dive and run your own analyses. This post will help you understand the Google Analytics data that is exported to BigQuery and how to extract the key pieces of information that you need.
Understanding the data structure
- BigQuery stores the exported data by date, and each day is stored in its own table. For instance yesterday’s data will be stored in 1300.ga_sessions_20170315, where 1300 is the project id and 20170315 is the date in yyyymmdd format. Data of the current date is stored in an intraday table, e.g. 1300.ga_sessions_intraday_20170315.
- Each table contains all sessions by users, one row per user session. A session is simply a sequence of pages viewed by the user (or in GA terminology, page hits).
For analytical tasks, we want to be able to identify users and sessions.
Identifying unique users
Users can be divided into two categories: logged in and not logged in (guests), of which only the former can be reliably identified. Logged in users can be associated with customers if you set and send these programmatically, either via the userId field or some custom dimensions that you define. Guests can be identified via fullVisitorIds but these are reset if user clear their cookies or use multiple devices. In fact the mapping from userIds and fullVisitorIds are N-to-N, so they can’t be reliably linked.
Take-away message: set, send, and use userId to uniquely identify customers.
Identifying unique sessions
The documentation from GA recommends using fullVisitorId + visitId to get a globally unique session identifier (within your GA data source). But for logged in users, we should actually use userId + visitStartTime to identify sessions of each user, where visitStartTime is the start time of a session. Let me illustrate with a toy example:
visitId | fullVisitorId | userId | visitStartTime
v1 | f1 | u1 | 1000000
v1 | f2 | u1 | 1500000
Here we have one user u1, who is mapped to two different visitor ids in two different sessions. The visitIds happen to be the same in both sessions. So using userId + visitId we would get only one session where in fact there are two. Using userId with visitTime is the right combination as an user can’t have two sessions that start at exactly the same time. If we want to be 100% certain that they are unique, we can use userId + visitId + visitStartTime.
Example reference query
Now that we know how to identify users and sessions, let me give you one reference query that covers the main concepts you need to know to work with this data.
Note that BigQuery is compliant with the SQL 2011 standard and supports complex types like Arrays and Structs. It also supports legacy SQL, but I encourage the use of the Standard SQL dialect, as in the given query below.
select
b.account_number
, concat(cast(visitId as string), '_', cast(visitStartTime as string)) session_id
, hits.type as hit_type
, hits.hitNumber as hit_number
, concat(hits.page.pagePathLevel1,
regexp_replace(hits.page.pagePathLevel2, '\\?.*$', ''))
) as page_level12
, hits.appInfo.screenName
from `project_id.ga_sessions_201702*` s, s.customdimensions as custdim,
s.hits, `project_id.account_numbers` b
where custdim.index = 1 and
custdim.value = cast(b.account_number as string) and
timestamp_diff(timestamp_seconds(visitStartTime), b.first_online, HOUR) < 24
and hits.type in ('APPVIEW', 'PAGE');
The query extracts all pages visited by each user on apps and websites, within their first day online. Here are the main points:
- account_number is used in place of userId for logged in users. This comes from an external data source, for example your customer database table. This is set and send to GA via the first custom dimension, which we retrieve with the condition custdim.index = 1.
- unique session identifier is given by concatenating visitId and visitStartTime, as discussed above.
- hits is an array of struct containing information about each hit / page view. There are several different hit types but here we limit to ‘APPVIEW’ for app interactions and ‘PAGE’ for website interactions
- the sessions table is implicitly joined with its column hits to flatten the table (getting one row per record)
- hits.hit_number gives us the order of page views within a session
- wildcard is used to qualify against the tables (hence dates) we query, here we are looking at data in Feb 2017 only
- hits.page.pagePathLevel{1 to 4} gives the web page, and hits.appInfo.screenName gives the app page
- timestamp_diff, timestamp_seconds are date time functions
References:
- Schema of exported data: https://support.google.com/analytics/topic/3416089?hl=en&ref_topic=2430414
- Standard SQL reference: https://cloud.google.com/bigquery/docs/reference/standard-sql/
- BigQuery cookbook (legacy SQL), https://support.google.com/analytics/answer/4419694
Side note
BigQuery web interface is not yet fully fledged. In fact, I find it quite limiting at first as it does not allow creating tables directly from query and has only one window for writing query. But auto-completion (which works for table names, columns and functions) and pop-up documentation are absolutely two killer features. I like it much better in this respect compared to the boring SQLWorkbench/J client that I’ve been using.
Thoughts and Things 