CODEWITHSUNDEEP
sqlgoogle-analyticsgoogle-bigquery
Spikey
Spikey

Reputation: 435

BigQuery Google Analytics sessionsWithEvent metric

I'm having trouble creating a BigQuery query that will allow for me to fetch the Google Analytics ga:sessionsWithEvent metric.

This is what I tried:

SELECT
  EXACT_COUNT_DISTINCT(concat(fullvisitorid, string(visitid))) AS distinctVisitIds
FROM
  (TABLE_DATE_RANGE([xxxxxxxx.ga_sessions_], TIMESTAMP('2016-11-30'), TIMESTAMP('2016-12-26')))
WHERE
  hits.type='EVENT'

The logic in the query above seems sound - get all the rows that have a hit.type of 'EVENT' and sum up the exact count of distinct fullVisitorId/VisitId results - aka. the number of unique sessions with an event.

But the numbers I get from here are close but higher than what I get using query explorer

Thank you.

EDIT: Addressing comment below to use wider date range with date filter

With date range +-5 days, this makes the query

SELECT
  EXACT_COUNT_DISTINCT(concat(fullvisitorid, string(visitid))) AS distinctVisitIds
FROM
  (TABLE_DATE_RANGE([xxxxxxxx.ga_sessions_], TIMESTAMP('2016-11-25'), TIMESTAMP('2016-12-31')))
WHERE
  hits.type='EVENT'
  AND ('20161130'<=date AND date<='20161226')

Unfortunately I still get the same number

Upvotes: 2

Views: 350

Answers (1)

Pentium10
Pentium10

Reputation: 208042

Don't rely on the table dates, usually even on later days you can have metrics from previous days. Instead use a larger date range on from and exact date range on columns.

AFAIK also the data explorer does approximations.

Upvotes: 0

Related Questions