narrow-psychiatrist-1543708/17/2022, 10:10 AM
. Looking at the raw data from BigQuery, the variants (
variant_id = 0
) are equally distributed (see screenshot), but variant
users do not show up in the experiment results. I’ve also verified that the there are no `userId`s exposed to both variants. Here’s the query from the analysis:
Are you able to spot what’s wrong here?
-- Activation (binomial) WITH __rawExperiment as ( SELECT user_id, received_at as timestamp, experiment_id, variation_id FROM `rudderstack_data.experiment` ), __experiment as ( -- Viewed Experiment SELECT e.user_id as user_id, cast(e.variation_id as string) as variation, CAST(e.timestamp as DATETIME) as conversion_start, DATETIME_ADD(CAST(e.timestamp as DATETIME), INTERVAL 336 HOUR) as conversion_end FROM __rawExperiment e WHERE e.experiment_id = 'hello-sanity_template' AND CAST(e.timestamp as DATETIME) >= DATETIME("2022-08-15 19:00:00") ), __metric as ( -- Metric (Activation) SELECT user_id as user_id, 1 as value, CAST(m.timestamp as DATETIME) as conversion_start, CAST(m.timestamp as DATETIME) as conversion_end FROM ( SELECT user_id, CAST(activation_date AS TIMESTAMP) AS timestamp FROM `dbt_production.dim_user` WHERE is_activated = TRUE ) m WHERE CAST(m.timestamp as DATETIME) >= DATETIME("2022-08-15 19:00:00") ), __distinctUsers as ( -- One row per user/dimension SELECT e.user_id, cast('All' as string) as dimension, ( CASE WHEN count(distinct e.variation) > 1 THEN '__multiple__' ELSE max(e.variation) END ) as variation, MIN(e.conversion_start) as conversion_start, MIN(e.conversion_end) as conversion_end FROM __experiment e GROUP BY e.user_id ), __userMetric as ( -- Add in the aggregate metric value for each user SELECT d.variation, d.dimension, 1 as value FROM __distinctUsers d JOIN __metric m ON (m.user_id = d.user_id) WHERE m.conversion_start >= d.conversion_start AND m.conversion_start <= d.conversion_end GROUP BY variation, dimension, d.user_id ), __overallUsers as ( -- Number of users in each variation SELECT variation, dimension, COUNT(*) as users FROM __distinctUsers GROUP BY variation, dimension ), __stats as ( -- Sum all user metrics together to get a total per variation/dimension SELECT variation, dimension, COUNT(*) as count, AVG(value) as mean, STDDEV(value) as stddev FROM __userMetric GROUP BY variation, dimension ) SELECT s.variation, s.dimension, s.count, s.mean, s.stddev, u.users FROM __stats s JOIN __overallUsers u ON ( s.variation = u.variation AND s.dimension = u.dimension )
narrow-psychiatrist-1543708/17/2022, 2:13 PM