https://www.growthbook.io/ logo
#announcements
Title
# announcements
n

narrow-psychiatrist-15437

08/17/2022, 10:10 AM
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:
Copy code
-- 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?
f

future-teacher-7046

08/17/2022, 12:03 PM
If you view the queries in GrowthBook, what do you see as the returned rows for that metric?
Oh, actually I think I know what's going on. We use an inner join in our queries right now so nothing is returned unless there is at least 1 conversion. We're planning to change that to a left join so you can still see the user count.
n

narrow-psychiatrist-15437

08/17/2022, 2:13 PM
Ohh, yeah, that could make sense!
I’ll give it some time until we have more conversions
Update: everything works fine now, after we got more conversion on variant
0
!
33 Views