https://www.growthbook.io/ logo
#ask-questions
Title
# ask-questions
c

clever-rose-53406

08/23/2023, 11:14 AM
Hey, I just wanted to sense check our
Experiment Assignment Queries
with someone. We are using GA as our data source and wanted to include our intraday data into the experiment reporting, so we expanded the query to this. Any feedback would be appreciated.
Copy code
SELECT
  user_pseudo_id as anonymous_id,
  TIMESTAMP_MICROS(event_timestamp) as timestamp,
  experiment_id_param.value.string_value AS experiment_id,
  variation_id_param.value.int_value AS variation_id,
  geo.country as country,
  traffic_source.source as source,
  traffic_source.medium as medium,
  device.category as device,
  device.web_info.browser as browser,
  device.operating_system as os
FROM
  analytics_181650743.`events_*`,
  UNNEST(event_params) AS experiment_id_param,
  UNNEST(event_params) AS variation_id_param
WHERE
  (
    (_TABLE_SUFFIX BETWEEN '{{startYear}}{{startMonth}}{{startDay}}' AND '{{endYear}}{{endMonth}}{{endDay}}') 
    OR 
    (DATE(CURRENT_DATE()) BETWEEN DATE('{{startYear}}-{{startMonth}}-{{startDay}}') AND DATE('{{endYear}}-{{endMonth}}-{{endDay}}') AND REGEXP_CONTAINS(_TABLE_SUFFIX, '^intraday'))
  )
  AND event_name = 'experiment_joined'  
  AND experiment_id_param.key = 'experiment_id'
  AND variation_id_param.key = 'variation_id'
  AND user_pseudo_id is not null
f

fresh-football-47124

08/23/2023, 4:43 PM
I’ve seen some folks use
Copy code
REGEXP_EXTRACT(_TABLE_SUFFIX, r'[0-9]+') BETWEEN '{{startYear}}{{startMonth}}{{startDay}}' AND '{{endYear}}{{endMonth}}{{endDay}}'
👍 1
16 Views