anonymous_id

user_id

anonymous_id

id

-- Count of begin_checkout (count)
WITH
  __rawExperiment AS (
    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
      `cedar-electronics-prismfly`.`analytics_253839784`.`events_*`,
      UNNEST (event_params) AS experiment_id_param,
      UNNEST (event_params) AS variation_id_param
    WHERE
      (
        (_TABLE_SUFFIX BETWEEN '20231012' AND '20231018')
        OR (
          _TABLE_SUFFIX BETWEEN 'intraday_20231012' AND 'intraday_20231018'
        )
      )
      AND event_name = 'experiment_viewed'
      AND experiment_id_param.key = 'experiment_id'
      AND variation_id_param.key = 'variation_id'
      AND user_pseudo_id is not null
  ),
  __experimentExposures AS (
    -- Viewed Experiment
    SELECT
      e.anonymous_id as anonymous_id,
      cast(e.variation_id as string) as variation,
      CAST(e.timestamp as DATETIME) as timestamp
    FROM
      __rawExperiment e
    WHERE
      e.experiment_id = 'delivery-timer-product-page'
      AND e.timestamp >= '2023-10-12 15:33:00'
      AND e.timestamp <= '2023-10-18 14:15:28'
  ),
  __experimentUnits AS (
    -- One row per user
    SELECT
      e.anonymous_id AS anonymous_id,
      cast('All' as string) AS dimension,
      MIN(e.timestamp) AS first_exposure_timestamp,
      (
        CASE
          WHEN count(distinct e.variation) > 1 THEN '__multiple__'
          ELSE max(e.variation)
        END
      ) AS variation
    FROM
      __experimentExposures e
    GROUP BY
      e.anonymous_id
  ),
  __distinctUsers AS (
    SELECT
      anonymous_id,
      dimension,
      variation,
      first_exposure_timestamp AS timestamp,
      date_trunc(first_exposure_timestamp, DAY) AS first_exposure_date
    FROM
      __experimentUnits
  ),
  __metric as ( -- Metric (Count of begin_checkout)
    SELECT
      anonymous_id as anonymous_id,
      m.value as value,
      CAST(m.timestamp as DATETIME) as timestamp
    FROM
      (
        SELECT
          user_pseudo_id as anonymous_id,
          TIMESTAMP_MICROS(event_timestamp) as timestamp,
          1 as value
        FROM
          `cedar-electronics-prismfly.analytics_253839784.events_*`
        WHERE
          (
            (_TABLE_SUFFIX BETWEEN '20231012' AND '20231018')
            OR (
              _TABLE_SUFFIX BETWEEN 'intraday_20231012' AND 'intraday_20231018'
            )
          )
          AND event_name = 'begin_checkout'
      ) m
    WHERE
      m.timestamp >= '2023-10-12 15:33:00'
      AND m.timestamp <= '2023-10-18 14:15:28'
  ),
  __userMetricJoin as (
    SELECT
      d.variation AS variation,
      d.dimension AS dimension,
      d.anonymous_id AS anonymous_id,
      (
        CASE
          WHEN m.timestamp >= d.timestamp THEN m.value
          ELSE NULL
        END
      ) as value
    FROM
      __distinctUsers d
      LEFT JOIN __metric m ON (m.anonymous_id = d.anonymous_id)
  ),
  __userMetricAgg as (
    -- Add in the aggregate metric value for each user
    SELECT
      variation,
      dimension,
      anonymous_id,
      SUM(COALESCE(value, 0)) as value
    FROM
      __userMetricJoin
    GROUP BY
      variation,
      dimension,
      anonymous_id
  )
  -- One row per variation/dimension with aggregations
SELECT
  m.variation AS variation,
  m.dimension AS dimension,
  COUNT(*) AS users,
  'mean' as statistic_type,
  'count' as main_metric_type,
  SUM(COALESCE(m.value, 0)) AS main_sum,
  SUM(POWER(COALESCE(m.value, 0), 2)) AS main_sum_squares
FROM
  __userMetricAgg m
GROUP BY
  m.variation,
  m.dimension

user_pseudo_id

variation_id

Total_users

Sessions

Add_to_cart

cedar-electronics-prismfly.analytics_253839784.events_*

experiment_id_param

variation_id_param

user_pseudo_id

experiment

user_psuedo_id

WITH variations AS (
SELECT
  COUNT(DISTINCT variation_id_param.value.int_value) as n_variations
  user_psuedo_id
FROM
  `cedar-electronics-prismfly.analytics_253839784.events_*`,
  unnest(event_params) as `experiment_id_param`,
  unnest(event_params) as `variation_id_param`
WHERE
  event_name='experiment_viewed'
    AND experiment_id_param.key = 'experiment_id'
    AND experiment_id_param.value.string_value = 'aa-test-cobra'
    AND variation_id_param.key = 'variation_id'
GROUP BY user_pseudo_id
)

SELECT
 n_variations,
 COUNT(*)
FROM variations
GROUP BY 1

cedar-electronics-prismfly

analytics_253839784

events_*

please let me know if the below experiment query is right ?