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.

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

I’ve seen some folks use

REGEXP_EXTRACT(_TABLE_SUFFIX, r'[0-9]+') BETWEEN '{{startYear}}{{startMonth}}{{startDay}}' AND '{{endYear}}{{endMonth}}{{endDay}}'