Hi, team! We’re running an experiment where the Growthbook analysis does not find users allocated to
variant_id = 0
. Looking at the raw data from BigQuery, the variants (
0
and
1
) are equally distributed (see screenshot), but variant
0
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:
-- 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
)
Are you able to spot what’s wrong here?
