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.

SELECT
date,
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’)))
WHERE
totals.visits = 1
GROUP BY
date

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. 


Ready to take your ads to the next level?  
DELVE is your strategic partner for site-side analytics, campaign management, and advanced marketing science. As experts in the Google Marketing Platform and Google Cloud Platform, DELVE drives client growth through a data-driven mindset that converts digital inefficiency into hard ROI.
SEE EXAMPLES of our experience and reviews from our clients.
Contact us to learn more about how we help our clients get advertising right

DELVE Experts
delve.experts@delvepartners.com


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…

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.

SELECT
date,
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’)))
WHERE
totals.visits = 1
GROUP BY
date

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. 


Ready to take your ads to the next level?  
DELVE is your strategic partner for site-side analytics, campaign management, and advanced marketing science. As experts in the Google Marketing Platform and Google Cloud Platform, DELVE drives client growth through a data-driven mindset that converts digital inefficiency into hard ROI.
SEE EXAMPLES of our experience and reviews from our clients.
Contact us to learn more about how we help our clients get advertising right

Related Posts

Best Practices for Amazon DSP Ads: Webinar

As consumer behavior continues to evolve due to the changing environment surrounding COVID-19, top brands…

Google Ads Budget Pacing in Paid Search Engine Marketing

The average internet user conducts between 3-4 Google searches per day. Given that there are…

What is Amazon DSP and How to Reach Amazon Target Audiences

Data is a core component of any successful marketing strategy. Making first-party data assets the…

Gaming Industry Operatore Stride Gaming Increased New Customer Revenue by 50% at a 12% Lower CPA

Understanding user behavior is essential for any company, but it’s even more significant for the…

Amazon DSP Webinar for Retailers is on Demand!

X