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

clever-hair-5481

12/14/2023, 6:07 PM
Hey folks 👋 I tried launching an experiment today using metrics that I’ve used in previous tests. However, today I’m seeing
relation "identifies" does not exist
errors for all queries. Do you know why this would be occurring and how to fix?
f

fresh-football-47124

12/15/2023, 12:17 AM
can you share the full query?
c

clever-hair-5481

12/15/2023, 12:43 AM
Copy code
-- % of Users Finalized Selection Step [Step 2] (7d) (binomial)
WITH
  __identities0 as (
    SELECT
      anonymous_id,
      user_id
    FROM
      (
        SELECT
          user_id,
          anonymous_id
        FROM
          identifies
      ) i
    GROUP BY
      anonymous_id,
      user_id
  ),
  __rawExperiment AS (
    SELECT
      anonymous_id,
      received_at as timestamp,
      experiment_key as experiment_id,
      variation_id,
      context_campaign_source as source,
      context_campaign_medium as medium,
      (
        CASE
          WHEN context_user_agent LIKE '%Mobile%' THEN 'Mobile'
          ELSE 'Tablet/Desktop'
        END
      ) as device,
      (
        CASE
          WHEN context_user_agent LIKE '% Firefox%' THEN 'Firefox'
          WHEN context_user_agent LIKE '% OPR%' THEN 'Opera'
          WHEN context_user_agent LIKE '% Edg%' THEN ' Edge'
          WHEN context_user_agent LIKE '% Chrome%' THEN 'Chrome'
          WHEN context_user_agent LIKE '% Safari%' THEN 'Safari'
          ELSE 'Other'
        END
      ) as browser
    FROM
      groover_frontend_prod.growthbook_experiment_viewed
    WHERE
      anonymous_id is not null
  ),
  __experimentExposures AS (
    -- Viewed Experiment
    SELECT
      e.anonymous_id as anonymous_id,
      cast(e.variation_id as varchar) as variation,
      e.timestamp as timestamp
    FROM
      __rawExperiment e
    WHERE
      e.experiment_id = 'ratings-social-proof'
      AND e.timestamp >= '2023-12-14 17:58:44'
      AND e.timestamp <= '2023-12-15 00:09:29'
  ),
  __experimentUnits AS (
    -- One row per user
    SELECT
      e.anonymous_id AS anonymous_id,
      (
        CASE
          WHEN count(distinct e.variation) > 1 THEN '__multiple__'
          ELSE max(e.variation)
        END
      ) AS variation,
      MIN(e.timestamp) AS first_exposure_timestamp
    FROM
      __experimentExposures e
    GROUP BY
      e.anonymous_id
  ),
  __distinctUsers AS (
    SELECT
      anonymous_id,
      cast('All' as varchar) AS dimension,
      variation,
      first_exposure_timestamp AS timestamp,
      date_trunc('day', first_exposure_timestamp) AS first_exposure_date
    FROM
      __experimentUnits
  ),
  __metric as ( -- Metric (% of Users Finalized Selection Step [Step 2] (7d))
    SELECT
      i.anonymous_id as anonymous_id,
      1 as value,
      m.timestamp as timestamp
    FROM
      (
        SELECT DISTINCT
          user_id,
          received_at as timestamp
        FROM
          groover_frontend_prod.campaign_step_2_curators_selection_validated
      ) m
      JOIN __identities0 i ON (i.user_id = m.user_id)
    WHERE
      m.timestamp >= '2023-12-14 17:58:44'
      AND m.timestamp <= '2023-12-22 00:09:29'
  ),
  __userMetricJoin as (
    SELECT
      d.variation AS variation,
      d.dimension AS dimension,
      d.anonymous_id AS anonymous_id,
      (
        CASE
          WHEN m.timestamp >= d.timestamp
          AND m.timestamp <= d.timestamp + INTERVAL '168 hours' THEN m.value
          ELSE NULL
        END
      ) as value
    FROM
      __distinctUsers d
      LEFT JOIN __metric m ON (m.anonymous_id = d.anonymous_id)
  ),
  __userMetricAgg as (
    -- Add in the aggregate metric value for each user
    SELECT
      variation,
      dimension,
      anonymous_id,
      MAX(COALESCE(value, 0)) as value
    FROM
      __userMetricJoin
    GROUP BY
      variation,
      dimension,
      anonymous_id
  )
  -- One row per variation/dimension with aggregations
SELECT
  m.variation AS variation,
  m.dimension AS dimension,
  COUNT(*) AS users,
  'mean' as statistic_type,
  'binomial' as main_metric_type,
  SUM(COALESCE(m.value, 0)) AS main_sum,
  SUM(POWER(COALESCE(m.value, 0), 2)) AS main_sum_squares
FROM
  __userMetricAgg m
GROUP BY
  m.variation,
  m.dimension
here’s one of them 🙂
h

helpful-application-7107

12/15/2023, 1:44 AM
Is this the first test where you're launching an experiment using the
anonymous_id
identifier type? It looks like your metrics are based on
user_id
and your experiment assignment query is based on the
anonymous_id
identifier. So then we have to use a JOIN to get these two identifier types to match up. The issue is that the query we are using for that is
Copy code
SELECT
          user_id,
          anonymous_id
        FROM
          identifies
When it looks like
identifies
isn't in your datasource or something. So you'll need to go to your Data Source and edit your
Join Tables
to point to the right lookup table. I can maybe help you with that but it might be easier for you to do if you know your datasource and where the
identifies
table lives. Are you using Segment?
c

clever-hair-5481

12/15/2023, 12:31 PM
Thanks! Is it possible to update (or restart) the test so that it’s based off of user_id instead?
@helpful-application-7107 would be great for us to know this before the weekend 🤞
h

helpful-application-7107

12/15/2023, 3:12 PM
In your experiment's analysis settings you should be able to just change the Experiment Assignment Table if indeed the feature is set up to randomize by user_id
c

clever-hair-5481

12/15/2023, 3:24 PM
It’s not really clear to me where how to edit it to point to
user_id
. I thought
id
was already pointing to
user_id
, but I also don’t see a place where I can change it
h

helpful-application-7107

12/15/2023, 3:35 PM
It's in your Experiment Analysis settings. If
id
is right in your feature flag and maps to
user_id
in your datasource, then I think it just got set incorrectly in your experiment (not your feature).
image.png,image.png
c

clever-hair-5481

12/15/2023, 4:07 PM
that worked, thanks!!
4 Views