CODEWITHSUNDEEP
google-analyticsgoogle-bigquery
amysong
amysong

Reputation: 143

Sessions by hits.page.pagePath in GA bigquery tables

I am new to bigquery, so sorry if this is a noob question! I am interested in breaking out sessions by page path or title. I understand one session can contain multiple paths/titles so the sum would be greater than total sessions. Essentially, I want to create a 'session id' and do a count distinct of sessionids where path like a or b.

It might actually be helpful to start at the very beginning and manually calculate total sessions. I tried to concatenate visit id and full visitor id to create a unique visit id, but apparently that is quite different from sessions. Can someone help enlighten me? Thanks!

I am working with our GA site data. Schema is the standard in GA exports.

DATA SAMPLE

Let's use an example out of the sample BigQuery (London Helmet) data:

There are 63 sessions in this day: 

SELECT count(*) FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]

How many of those sessions are where hits.page.pagePath like /vests% or /helmets%? How many were vests only vs helmets only? Thanks!

Upvotes: 3

Views: 3916

Answers (2)

Patrice
Patrice

Reputation: 4692

Way 1, easier but you need to repeat on each field

Obviously you can do something like this : 

SELECT count(*) FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910] WHERE hits.page.pagePath like '/helmets%'

And then have multiple queries for your own substrings (one with '/vests%', one with 'helmets%', etc).

Way 2, works fine, but not with repeated fields

If you want ONE query that'll just group by on the first part of the string, you can do something like that :

Select a, Count(*) FROM (SELECT FIRST(SPLIT(hits.page.pagePath, '/')) as a  FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910] ) group by a

When I do this, it returns me the following the 63 sessions, with a total count of 63 :).

Way 3, using a FLATTEN on the table to get each hit individually

Since the "hits" field is repeatable, you would need a FLATTEN in your query : 

Select a, Count(*) FROM (SELECT FIRST(SPLIT(hits.page.pagePath, '/')) as a  FROM FLATTEN ([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910] , hits)) group by a

The reason why you need to FLATTEN here is that the "hits" field is repeatable. If you don't flatten, it won't look into ALL the "hits" in your response. Adding "FLATTEN" will make you work off a sub-table where each hit is in its own row, so you can query on all of them.

If you want it by sessions instead of hits, (it'll be both), do something like :

Select b, a Count(*) FROM (SELECT FIRST(SPLIT(hits.page.pagePath, '/')) as a, visitID as b, FROM FLATTEN ([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910] , hits)) group by b, a

Upvotes: 2

Mosha Pasumansky
Mosha Pasumansky

Reputation: 13994

Here is an example of how to calculate whether there were only helmets, or only vests or both helmets and vests or neither:

SELECT 
  visitID,
  has_helmets AND has_vests AS both_helmets_and_vests,
  has_helmets AND NOT has_vests AS helmets_only,
  NOT has_helmets AND has_vests AS vests_only,
  NOT has_helmets AND NOT has_vests AS neither_helmets_nor_vests
FROM (
  SELECT
    visitId,
    SOME(hits.page.pagePath like '/helmets%') WITHIN RECORD AS has_helmets,
    SOME(hits.page.pagePath like '/vests%') WITHIN RECORD AS has_vests,
  FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910] 
)

Upvotes: 5

Related Questions