Suggest a feature
×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: Chris Beardsley
Added: Jan 30, 2019 6:59 AM
Views: 32
Tags: no tags
  1. /* Standard SQL */
  2. SELECT DISTINCT
  3.   user_id,
  4.   session_date,
  5.   /* Case when logic to 'clean up' the data into 0 or 1 values */
  6.   CASE
  7.     WHEN global_consent_status IS NULL THEN '0'
  8.     WHEN global_consent_status='false' THEN '0'
  9.   ELSE global_consent_status
  10.   END AS global_consent_status  
  11. /* Selecting from the table we've just made */
  12. FROM (
  13.   SELECT
  14.     /* Unnesting the custom dimension part of the GA export and returning only values in slot 5 (which we know from the GA interface is User ID) */
  15.     (SELECT MAX(IF(INDEX = 5,VALUE,NULL)) FROM UNNEST(customDimensions)) AS user_id,
  16.     DATE AS session_date,
  17.     /* Unnesting the custom dimension field and returning only values in slot 22 (which we know from the GA interface is the Global Consent Status) */
  18.     (SELECT MAX(IF(INDEX = 22,VALUE,NULL)) FROM UNNEST(customDimensions)) AS global_consent_status
  19.   FROM
  20.   /* Selecting from the GA export tables where the data is coming from */
  21.     `hbo-nordic-bigquery-1322.134556033.ga_sessions_*`
  22.   /* Date range of the query */
  23.   WHERE _TABLE_SUFFIX BETWEEN '20181201' AND '20181212')
  24. WHERE
  25.   /* Only return rows with distinct user_id values */
  26.   user_id IS NOT NULL AND user_id != ''
  27. ORDER BY user_id