CODEWITHSUNDEEP
google-bigquery
Kieran
Kieran

Reputation: 45

Selecting multiple hit-scoped custom dimensions with bigquery

I'm trying to get a list of customers who made at least one purchase on a given day. The bigquery below works when just selecting date and customer_id. However, city always returns null, though I know the data exists.

Do I need to use a different approach when selecting multiple hit-scoped custom dimensions?

SELECT date
,CASE WHEN hits.customdimensions.index = 5 
 THEN hits.customdimensions.value END as customer_id
,CASE WHEN hits.customdimensions.index = 50 
 THEN hits.customdimensions.value END as city
FROM (TABLE_DATE_RANGE([16475821.ga_sessions_],
      TIMESTAMP('2016-09-01'), TIMESTAMP('2016-09-07'))
GROUP BY 1, 2, 3
HAVING sum(case when hits.eventInfo.eventAction = 'purchase' then 1 end) > 0 
       and customer_id is not null

Upvotes: 1

Views: 518

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Don't have any data to test again, but visually assessing - below should work for you 

SELECT 
  date,
  MAX(CASE WHEN hits.customdimensions.index = 5 
           THEN hits.customdimensions.value END) WITHIN RECORD AS customer_id
  MAX(CASE WHEN hits.customdimensions.index = 50 
           THEN hits.customdimensions.value END) WITHIN RECORD AS city
  SUM(CASE WHEN hits.eventInfo.eventAction = 'purchase' 
           THEN 1 END) WITHIN RECORD AS purchases_count
FROM (TABLE_DATE_RANGE([16475821.ga_sessions_],
      TIMESTAMP('2016-09-01'), TIMESTAMP('2016-09-07')))
HAVING purchases_count > 0
AND NOT customer_id IS NULL

Upvotes: 1

Related Questions