10 Jan How to Count Google Analytics User Session Using Google Cloud BigQuery
When you’re ready to start working with your data, it’s essential to use tools that work seamlessly together. One of the most significant selling points of Google Analytics 360 is the Google BigQuery integration, a tool that enables you to query and run analytics against your data at the hit level. But before you start working with your data, it is crucial to have a good point of reference that you can build off of as you start to gather data-driven insight about lands on your website, and how users interact with your website.
When Google Analytics 360 and Google BigQuery integration is set up, you can establish this point of reference by looking at the list of session ids, and comparing the number of sessions in BigQuery to the number of Google Analytics user sessions shown in your Google Analytics 360 interface. Let’s walk through how to use BigQuery to count unique Google Analytics user sessions when Google Analytics 360 and Google BigQuery integration is set up.
Step 1: View the data schema
Before writing a SQL statement, look at the data schema to establish which field names give you the result you need:
fullVisitorId– the unique visitor ID (aka client ID). This number shows unique user numbers only.
visitId– An identifier for the session.
totals.visits– The number of sessions. This value is one for sessions with interaction events. The value is null if there are no interaction events in the session.
Tip 1: The number of sessions in BigQuery is always greater than the number of sessions shown in the Google Analytics 360 interface. In this case, the discrepancy is 2% since Google Analytics 360 automatically filters out sessions with no interaction events. Taking that into consideration, you need to include “where totals.visits = 1″ as a statement.
Step 2: Write your SQL statement
Now you can write an SQL statement that uses a combination of different fields and different counting methods. In our example, we’ll count unique visit_ids and total visits. We’ll also use the exact_count formula to count visit_id and some additional fields.
SUM(totals.visits) AS totals_visits,
COUNT(DISTINCT fullvisitorid) AS distinct_fullvisitorid,
COUNT(DISTINCT visitid) AS distinct_visitid,
COUNT(DISTINCT CONCAT(CAST(fullvisitorid AS string),CAST(visitid AS string))) AS distinct_visitid_fullvisitorid,
EXACT_COUNT_DISTINCT(CONCAT(CAST(fullvisitorid AS string),CAST(visitid AS string))) AS exact_distinct_visitid_fullvisitorid,
EXACT_COUNT_DISTINCT (visitid) AS exact_distinct_visitid,
FROM (TABLE_DATE_RANGE([myproject:mydataset.ga_sessions_], TIMESTAMP(‘2017–07–28’), TIMESTAMP(‘2017–07–28’)))
totals.visits = 1
Tip 2: To get the number of unique values, you could use the COUNT(DISTINCT metric_name) method. But it turns out, that formula only gives approximate results. To get a more accurate number, use the EXACT_COUNT_DISTINCT(metric_name) method.
Run the above query against your data and evaluate the results. It’s a good idea to try it with and without the “total.visits where” statement.
Step 3: Compare interfaces
There are two ways to find the number of total sessions, and they should match the value shown in your Google Analytics 360 interface:
a) SUM(totals.visits), which uses the “totals” metric created for you by Google.
b) EXACT_COUNT_DISTINCT(concat(cast(fullvisitorid as string),cast(visitid as string))) which is the method we recommend. This method also gives you a view on session_ids for more in-depth analysis.
Tip 3: Multiple users can have the same visitid, so the number of unique visitids does offer meaningful insight. For a unique representation of a session, you’ll need to use the fullvisitorid+visitid string.
Seamless integration between Google Analytics 360 and Google BigQuery allows you to use BigQuery to count unique sessions so that you can evaluate the number of sessions across both platforms and evaluate any discrepancies. To learn more about Google Analytics 360 and BigQuery, read our blog post on custom attribution modeling in Google Analytics 360 and BigQuery.