CODEWITHSUNDEEP
google-analyticsgoogle-bigquery
techscolasticus
techscolasticus

Reputation: 97

Calculating sessions, users, and revenue by hit scoped custom dimension in BigQuery

Thanks for taking a look!

My goal is to get number of sessions, users, transactions, and revenue by device to certain subdomains within my site whose names are stored in a custom dimension. Below is the desired output and site_name is stored in customdimension 4.

desired output

The query below is from this answer using GA's public dataset. I've modified it by replacing pageviews with sessions, added the desired columns, and tried to isolate pageviews that are an entrance for the site_name because I thought that's the accurate method.

SELECT  
   (SELECT hcd.value FROM h.customdimensions AS hcd WHERE hcd.index = 4 ) AS site_name,
   device.deviceCategory AS device,
   SUM(totals.visits) as sessions,
   COUNT(DISTINCT fullVisitorId) as users,
   SUM(totals.transactions) as transactions,
   SUM(totals.transactionREvenue) as revenue
FROM  `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
      UNNEST(hits) AS h

WHERE _table_suffix between '20170801' and '20170801' 
AND hits.isEntrance IS TRUE
AND totals.visits = 1

GROUP BY site_name, device

When I add AND hits.isEntrance IS TRUE, I get the error Cannot access field isEntrance... Since I've unnested the hits, I'm surprised by this error. If the query is going to be accurate without this line, please let me know.

Unfortunately, I can't compare the results of the query to GA's interface because it is quite a costly query on my real dataset.

Upvotes: 0

Views: 930

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

When I add AND hits.isEntrance IS TRUE, I get the error Cannot access field isEntrance... Since I've unnested the hits, I'm surprised by this error

You should reference that unnested (UNNEST(hits) AS h) instance - which is h in this case - so use AND h.isEntrance IS TRUE instead as in example below 

#standardSQL
SELECT  
   (SELECT hcd.value FROM h.customdimensions AS hcd WHERE hcd.index = 4 ) AS site_name,
   device.deviceCategory AS device,
   SUM(totals.visits) as sessions,
   COUNT(DISTINCT fullVisitorId) as users,
   SUM(totals.transactions) as transactions,
   SUM(totals.transactionREvenue) as revenue
FROM  `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
      UNNEST(hits) AS h

WHERE _table_suffix between '20170801' and '20170801' 
AND h.isEntrance IS TRUE
AND totals.visits = 1

GROUP BY site_name, device

Upvotes: 2

Related Questions