How to Count Google Analytics Sessions Using Big Query

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 integration with Google BigQuery, 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. 

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 sessions shown in your Google Analytics 360 interface. Let’s walk through how to count Google Analytics Sessions using BigQuery.

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 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 Sessions Using Big Query

When you’re ready to start working with your data, it’s essential to use tools that…

How to Count Google Analytics Sessions Using Big Query

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 integration with Google BigQuery, 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. 

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 sessions shown in your Google Analytics 360 interface. Let’s walk through how to count Google Analytics Sessions using BigQuery.

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 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

DELVE Earns Designation as a Great Place To Work-Certified™ Company in 2020

Boulder, CO – February 18, 2019 – DELVE  DELVE, a data-first, measurement-first digital advertising and…

Leverage Uplift Modeling To Optimize ROI For Direct Mail Campaigns

Most marketers know that direct mail touts an impressive response rate between 5% and 9%. …

Best Practices After “Average Position” – Google Ads Top Impression Metrics Explained

Google Ads removed average position in September 2019. And whenever there’s a removal of a…

Managing User Data In Google Analytics

In the context of CCPA (California Consumer Privacy Act), which came into effect on January…