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

bored-forest-47521

06/03/2022, 7:59 PM
Hello! I'm trying to import some existing experiment data into GrowthBook. I have both a
user_id
and
user_pseudo_id
identifier set up. It looks like the import is failing because the query that's auto-generated by GrowthBook to pull in experiment data has subbed in the
user_pseudo_id
name in the wrong spot. See thread for more details.
Copy code
-- Past Experiments

WITH __exposures0 as (

  SELECT

    cast('user_id' as string) as exposure_query,

    experiment_id,

    variation_id,

    date_trunc(CAST(timestamp as DATETIME), DAY) as date,

    count(distinct user_id) as users

  FROM

    (

      SELECT

        user_id as user_id,

        assigned_at as timestamp,

        experiment_id as experiment_id,

        variant_id as variation_id

      FROM

        REDACTED.cohort_abtests

      WHERE

        test_on = 'REDACTED'

    ) e0

  WHERE

    CAST(timestamp as DATETIME) > DATETIME("2021-06-03 19:54:18")

  GROUP BY

    experiment_id,

    variation_id,

    date_trunc(CAST(timestamp as DATETIME), DAY)

),

__exposures1 as (

  SELECT

    cast('tbl_l3yux92v' as string) as exposure_query,

    experiment_id,

    variation_id,

    date_trunc(CAST(timestamp as DATETIME), DAY) as date,

    count(distinct user_pseudo_id) as users

  FROM

    (

      SELECT

        user_pseudo_id as user_id,

        assigned_at as timestamp,

        experiment_id as experiment_id,

        variant_id as variation_id

      FROM

        REDACTED.cohort_abtests

      WHERE

        test_on = 'REDACTED'

    ) e1

  WHERE

    CAST(timestamp as DATETIME) > DATETIME("2021-06-03 19:54:18")

  GROUP BY

    experiment_id,

    variation_id,

    date_trunc(CAST(timestamp as DATETIME), DAY)

),

__experiments as (

  SELECT

    *

  FROM

    __exposures0

  UNION ALL

  SELECT

    *

  FROM

    __exposures1

),

__userThresholds as (

  SELECT

    exposure_query,

    experiment_id,

    variation_id,

    -- It's common for a small number of tracking events to continue coming in

    -- long after an experiment ends, so limit to days with enough traffic

    max(users) * 0.05 as threshold

  FROM

    __experiments

  WHERE

    -- Skip days where a variation got 5 or fewer visitors since it's probably not real traffic

    users > 5

  GROUP BY

    exposure_query,

    experiment_id,

    variation_id

),

__variations as (

  SELECT

    d.exposure_query,

    d.experiment_id,

    d.variation_id,

    MIN(d.date) as start_date,

    MAX(d.date) as end_date,

    SUM(d.users) as users

  FROM

    __experiments d

    JOIN __userThresholds u ON (

      d.exposure_query = u.exposure_query

      AND d.experiment_id = u.experiment_id

      AND d.variation_id = u.variation_id

    )

  WHERE

    d.users > u.threshold

  GROUP BY

    d.exposure_query,

    d.experiment_id,

    d.variation_id

)

SELECT

  *

FROM

  __variations

WHERE

  -- Experiment was started recently

  date_diff(DATETIME("2022-06-03 19:54:18"), start_date, DAY) < 14

  OR -- OR it ran for long enough and had enough users

  (

    date_diff(end_date, start_date, DAY) >= 14

    AND users > 100

    AND -- Skip experiments at start of date range since it's likely missing data

    date_diff(start_date, DATETIME("2021-06-03 19:54:18"), DAY) > 2

  )

ORDER BY

  experiment_id ASC,

  variation_id ASC
The
exposures1
CTE has
count(distinct user_pseudo_id) as users
when it should be
count(distinct user_id) as users
f

fresh-football-47124

06/03/2022, 10:43 PM
Hi Jane
was this import from the 'add experiments' or was it from the importing past experiments?
b

bored-forest-47521

06/06/2022, 2:12 PM
It was from importing past experiments
I also tried the import via
add experiments
and ran into the same query issue
Good news: I figured out the issue with the experiment imports (it was a case of misunderstanding how to set up the identifier query).
Though oddly not all of our experiments were detected by GrowthBook. 6 are missing, which is half.
Ah - has to do with user threshold
f

fresh-football-47124

06/08/2022, 2:46 PM
ok, cool - ya, we let you adjust the look back time in the settings
but I assume you found that
b

bored-forest-47521

06/08/2022, 2:48 PM
I didn't but thanks for pointing that out! Adjusting a setting brought in the rest.
f

fresh-football-47124

06/08/2022, 3:07 PM
Anything you think we should clarify in the app or documentation?
b

bored-forest-47521

06/08/2022, 3:32 PM
In general, I think it would be helpful to have process-oriented docs like here are the steps, 1. 2. 3. to create a new experiment. Here are the steps to import old experiments, with all the associated settings (which live in a different place than the Import Experiments or Add Experiments buttons).
👍 1
The current docs are great for understanding what GrowthBook does but it's harder to piece together how to do stuff without learning by making mistakes (like what's going on with our A/A test)
7 Views