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?

If you view the queries in GrowthBook, what do you see as the returned rows for that metric?

Ohh, yeah, that could make sense!