https://www.growthbook.io/ logo
#ask-questions
Title
# ask-questions
h

helpful-carpenter-3208

03/07/2022, 6:17 AM
Hi, I met a issue. when I try to import existing experiment from our data source. it’s not using the query I edit in the data source. it’s keep using the default experiment_viewed table. is it a bug?
f

future-teacher-7046

03/07/2022, 6:18 AM
If you go to your datasource settings, can you share what the queries are there?
h

helpful-carpenter-3208

03/07/2022, 6:20 AM
I trigger the rerun and it works to use my setting. LoL. But I got another error
Copy code
-- Past Experiments
WITH __experiments as (
  SELECT
    user_id,
    user_id as anonymous_id,
    enroll_time as timestamp,
    abtest_name as experiment_id,
    enrolled as variation_id,
    device_id
  FROM
    magic.mv_abtest_users
),
__experimentDates as (
  SELECT
    experiment_id,
    variation_id,
    dateTrunc('day', timestamp) as date,
    count(distinct anonymous_id) as users
  FROM
    __experiments
  WHERE
    timestamp > toDateTime('2021-03-07 06:17:57')
  GROUP BY
    1,
    2,
    3
),
__userThresholds as (
  SELECT
    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
    __experimentDates
  WHERE
    -- Skip days where a variation got 5 or fewer visitors since it's probably not real traffic
    users > 5
  GROUP BY
    experiment_id,
    variation_id
),
__variations as (
  SELECT
    d.experiment_id,
    d.variation_id,
    MIN(d.date) as start_date,
    MAX(d.date) as end_date,
    SUM(d.users) as users
  FROM
    __experimentDates d
    JOIN __userThresholds u ON (
      d.experiment_id = u.experiment_id
      AND d.variation_id = u.variation_id
    )
  WHERE
    d.users > u.threshold
  GROUP BY
    d.experiment_id,
    d.variation_id
)
SELECT
  *
FROM
  __variations
WHERE
  -- Experiment was started recently
  dateDiff(
    'day',
    start_date,
    toDateTime('2022-03-07 06:17:57')
  ) < 6
  OR -- OR it ran for long enough and had enough users
  (
    dateDiff('day', start_date, end_date) >= 6
    AND users > 100
    AND -- Skip experiments at start of date range since it's likely missing data
    dateDiff(
      'day',
      toDateTime('2021-03-07 06:17:57'),
      start_date
    ) > 2
  )
ORDER BY
  experiment_id ASC,
  variation_id ASC
click to minimize
500: Code: 215, e.displayText() = DB::Exception: Column `experiment_id` is not under aggregate function and
Copy code
SELECT
  user_id,
  user_id as anonymous_id,
  enroll_time as timestamp,
  abtest_name as experiment_id,
  enrolled as variation_id,
  device_id
FROM
   magic.mv_abtest_users
this is our setting
f

future-teacher-7046

03/07/2022, 6:23 AM
What type of datasource is it?
Clickhouse?
h

helpful-carpenter-3208

03/07/2022, 6:27 AM
yes
f

future-teacher-7046

03/07/2022, 6:36 AM
I wonder if the
GROUP BY 1,2,3
is causing it to break. Can you try switching that to use actual column names and running on clickhouse directly to see if it works?
h

helpful-carpenter-3208

03/07/2022, 6:37 AM
yes, change to actual name works.
so how can I modify this query… since it’s generated by growthbook itself
I just want to import existing experiment
I guess it should be a easy fix. and wondering how can I bypass this error for now?
f

future-teacher-7046

03/07/2022, 1:57 PM
The group by issue is fixed now on the latest Docker build.
1
16 Views