CODEWITHSUNDEEP
sqlgoogle-analyticsgoogle-bigquery
Pascal Reder
Pascal Reder

Reputation: 25

Sessions per Hour and Minute: SQL - BigQuery / GA360

I want to measure the impact of TV-Advertising on website sessions and transactions. Therefore, I need all (new) sessions per hour and minute to compare these with the TV airing time. I thought about selecting the hits.hitnumber and filter it by all values that equal 1 (=first hit). However, I get too little sessions, around one third of what GA displays. Any help is appreciated!

SELECT
date,
hits.hour, 
hits.minute,
hits.hitNumber,
totals.transactions,
hits.item.productName,
FROM [XXXXXXXX.ga_sessions_20180221] 
WHERE hits.hitNumber =1
GROUP BY date, hits.hour, hits.minute, hits.hitNumber ,totals.transactions, hits.item.productName

Upvotes: 1

Views: 1086

Answers (1)

Pol Ferrando
Pol Ferrando

Reputation: 673

There may be several sessions with the same values of the fields you are grouping by, so you should add session identifiers (in BQ, this is the combination of fullVisitorId and visitId) at least in the GROUP BY clause:

SELECT
  fullVisitorId,
  visitId,
  date,
  hits.hour, 
  hits.minute,
  hits.hitNumber,
  totals.transactions,
  hits.item.productName,
FROM [XXXXXXXX.ga_sessions_20180221] 
WHERE hits.hitNumber =1
GROUP BY 
  fullVisitorId, visitId, date, hits.hour, hits.minute, hits.hitNumber,
  totals.transactions, hits.item.productName

Upvotes: 2

Related Questions