https://www.growthbook.io/ logo
Join Slack
Powered by
We recently relaunched an experiment, but after tw...
# ask-questions
e
We recently relaunched an experiment, but after two or three days, we still haven't received any data on the GB dashboard. We verified in GA4 that the experiment is active. Could someone help us identify what we might be missing? Our other experiments are functioning correctly.
b
Hi AJ, thanks for reaching out. Are you also using BigQuery?
e
yes @brief-honey-45610 other experiment works fine
r
Could you send me a screenshot of the SQL query that's being used to the query your database? Look for the Experiment Assignment Query section in the "Metrics and Data --> Data Sources --> Click on relevant data source".
Doing the above will help us determine if the SQL GrowthBook is running in order to query the database is correct.
Could you also send me a screenshot or code snippet of the GrowthBook-related code, particularly for the trackingCallback? If the ​`trackingCallback`​ is not firing correctly or if there is an issue with the way events are being sent to GA4, this could result in data not appearing in GrowthBook. It's important to ensure that the ​`trackingCallback`​ method is implemented correctly and that the ​`gtag`​ function is available when the callback is executed.
Are you using any custom dimensions?
e
I'll send you a dm
Copy code
-- Purchase (User conversion rate) (binomial)
WITH
  __rawExperiment AS (
    SELECT
      user_pseudo_id as anonymous_id,
      TIMESTAMP_MICROS(event_timestamp) as timestamp,
      experiment_id_param.value.string_value AS experiment_id,
      variation_id_param.value.int_value AS variation_id,
      geo.country as country,
      traffic_source.source as source,
      traffic_source.medium as medium,
      device.category as device,
      device.web_info.browser as browser,
      device.operating_system as os
    FROM
      `involuted-reach-399721`.`analytics_380888564`.`events_*`,
      UNNEST (event_params) AS experiment_id_param,
      UNNEST (event_params) AS variation_id_param
    WHERE
      (
        (_TABLE_SUFFIX BETWEEN '20231225' AND '20240112')
        OR (
          _TABLE_SUFFIX BETWEEN 'intraday_20231225' AND 'intraday_20240112'
        )
      )
      AND event_name = 'experiment_viewed'
      AND experiment_id_param.key = 'experiment_id'
      AND variation_id_param.key = 'variation_id'
      AND user_pseudo_id is not null
  ),
  __experimentExposures AS (
    -- Viewed Experiment
    SELECT
      e.anonymous_id as anonymous_id,
      cast(e.variation_id as string) as variation,
      CAST(e.timestamp as DATETIME) as timestamp
    FROM
      __rawExperiment e
    WHERE
      e.experiment_id = 'add-free-shipping-announcement-bar-relaunch-2'
      AND e.timestamp >= '2023-12-25 15:15:00'
      AND e.timestamp <= '2024-01-12 15:40:14'
  ),
  __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 string) AS dimension,
      variation,
      first_exposure_timestamp AS timestamp,
      date_trunc(first_exposure_timestamp, DAY) AS first_exposure_date
    FROM
      __experimentUnits
  ),
  __metric as ( -- Metric (Purchase (User conversion rate))
    SELECT
      anonymous_id as anonymous_id,
      1 as value,
      CAST(m.timestamp as DATETIME) as timestamp
    FROM
      (
        SELECT
          user_pseudo_id as anonymous_id,
          TIMESTAMP_MICROS(event_timestamp) as timestamp
        FROM
          `involuted-reach-399721.analytics_380888564.events_*`
        WHERE
          (
            (_TABLE_SUFFIX BETWEEN '20231225' AND '20240115')
            OR (
              _TABLE_SUFFIX BETWEEN 'intraday_20231225' AND 'intraday_20240115'
            )
          )
          AND event_name = 'purchase'
      ) m
    WHERE
      m.timestamp >= '2023-12-25 15:15:00'
      AND m.timestamp <= '2024-01-15 15:40:14'
  ),
  __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 <= DATETIME_ADD(d.timestamp, INTERVAL 72 HOUR) 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
Copy code
-- Revenue per user (revenue)
WITH
  __rawExperiment AS (
    SELECT
      user_pseudo_id as anonymous_id,
      TIMESTAMP_MICROS(event_timestamp) as timestamp,
      experiment_id_param.value.string_value AS experiment_id,
      variation_id_param.value.int_value AS variation_id,
      geo.country as country,
      traffic_source.source as source,
      traffic_source.medium as medium,
      device.category as device,
      device.web_info.browser as browser,
      device.operating_system as os
    FROM
      `involuted-reach-399721`.`analytics_380888564`.`events_*`,
      UNNEST (event_params) AS experiment_id_param,
      UNNEST (event_params) AS variation_id_param
    WHERE
      (
        (_TABLE_SUFFIX BETWEEN '20231225' AND '20240112')
        OR (
          _TABLE_SUFFIX BETWEEN 'intraday_20231225' AND 'intraday_20240112'
        )
      )
      AND event_name = 'experiment_viewed'
      AND experiment_id_param.key = 'experiment_id'
      AND variation_id_param.key = 'variation_id'
      AND user_pseudo_id is not null
  ),
  __experimentExposures AS (
    -- Viewed Experiment
    SELECT
      e.anonymous_id as anonymous_id,
      cast(e.variation_id as string) as variation,
      CAST(e.timestamp as DATETIME) as timestamp
    FROM
      __rawExperiment e
    WHERE
      e.experiment_id = 'add-free-shipping-announcement-bar-relaunch-2'
      AND e.timestamp >= '2023-12-25 15:15:00'
      AND e.timestamp <= '2024-01-12 15:40:14'
  ),
  __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 string) AS dimension,
      variation,
      first_exposure_timestamp AS timestamp,
      date_trunc(first_exposure_timestamp, DAY) AS first_exposure_date
    FROM
      __experimentUnits
  ),
  __metric as ( -- Metric (Revenue per user)
    SELECT
      anonymous_id as anonymous_id,
      m.value as value,
      CAST(m.timestamp as DATETIME) as timestamp
    FROM
      (
        SELECT
          user_pseudo_id as anonymous_id,
          TIMESTAMP_MICROS(event_timestamp) as timestamp,
          ecommerce.purchase_revenue as value
        FROM
          `involuted-reach-399721`.`analytics_380888564`.`events_*`
        WHERE
          event_name = 'purchase'
          AND (
            (_TABLE_SUFFIX BETWEEN '20231225' AND '20240115')
            OR (
              _TABLE_SUFFIX BETWEEN 'intraday_20231225' AND 'intraday_20240115'
            )
          )
      ) m
    WHERE
      m.timestamp >= '2023-12-25 15:15:00'
      AND m.timestamp <= '2024-01-15 15:40:14'
  ),
  __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 <= DATETIME_ADD(d.timestamp, INTERVAL 72 HOUR) 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,
      SUM(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,
  'count' 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
Copy code
-- Purchase conversion rate (session) (binomial)
WITH
  __rawExperiment AS (
    SELECT
      user_pseudo_id as anonymous_id,
      TIMESTAMP_MICROS(event_timestamp) as timestamp,
      experiment_id_param.value.string_value AS experiment_id,
      variation_id_param.value.int_value AS variation_id,
      geo.country as country,
      traffic_source.source as source,
      traffic_source.medium as medium,
      device.category as device,
      device.web_info.browser as browser,
      device.operating_system as os
    FROM
      `involuted-reach-399721`.`analytics_380888564`.`events_*`,
      UNNEST (event_params) AS experiment_id_param,
      UNNEST (event_params) AS variation_id_param
    WHERE
      (
        (_TABLE_SUFFIX BETWEEN '20231225' AND '20240112')
        OR (
          _TABLE_SUFFIX BETWEEN 'intraday_20231225' AND 'intraday_20240112'
        )
      )
      AND event_name = 'experiment_viewed'
      AND experiment_id_param.key = 'experiment_id'
      AND variation_id_param.key = 'variation_id'
      AND user_pseudo_id is not null
  ),
  __experimentExposures AS (
    -- Viewed Experiment
    SELECT
      e.anonymous_id as anonymous_id,
      cast(e.variation_id as string) as variation,
      CAST(e.timestamp as DATETIME) as timestamp
    FROM
      __rawExperiment e
    WHERE
      e.experiment_id = 'add-free-shipping-announcement-bar-relaunch-2'
      AND e.timestamp >= '2023-12-25 15:15:00'
      AND e.timestamp <= '2024-01-12 15:40:14'
  ),
  __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 string) AS dimension,
      variation,
      first_exposure_timestamp AS timestamp,
      date_trunc(first_exposure_timestamp, DAY) AS first_exposure_date
    FROM
      __experimentUnits
  ),
  __metric as ( -- Metric (Purchase conversion rate (session))
    SELECT
      anonymous_id as anonymous_id,
      1 as value,
      CAST(m.timestamp as DATETIME) as timestamp
    FROM
      (
        SELECT
          user_pseudo_id as anonymous_id,
          TIMESTAMP_MICROS(event_timestamp) as timestamp,
          1 as value
        FROM
          `involuted-reach-399721`.`analytics_380888564`.`events_*`
        WHERE
          event_name = 'purchase'
          AND (
            (_TABLE_SUFFIX BETWEEN '20231225' AND '20240118')
            OR (
              _TABLE_SUFFIX BETWEEN 'intraday_20231225' AND 'intraday_20240118'
            )
          )
      ) m
    WHERE
      m.timestamp >= '2023-12-25 15:15:00'
      AND m.timestamp <= '2024-01-18 15:40:14'
  ),
  __denominator0 as ( -- Metric (session_start)
    SELECT
      anonymous_id as anonymous_id,
      1 as value,
      CAST(m.timestamp as DATETIME) as timestamp
    FROM
      (
        SELECT
          user_pseudo_id as anonymous_id,
          TIMESTAMP_MICROS(event_timestamp) as timestamp
        FROM
          `involuted-reach-399721.analytics_380888564.events_*`
        WHERE
          (
            (_TABLE_SUFFIX BETWEEN '20231225' AND '20240118')
            OR (
              _TABLE_SUFFIX BETWEEN 'intraday_20231225' AND 'intraday_20240118'
            )
          )
          AND event_name = 'session_start'
      ) m
    WHERE
      m.timestamp >= '2023-12-25 15:15:00'
      AND m.timestamp <= '2024-01-18 15:40:14'
  ),
  __denominatorUsers as (
    -- one row per user
    SELECT
      initial.anonymous_id AS anonymous_id,
      MIN(initial.dimension) AS dimension,
      MIN(initial.variation) AS variation,
      MIN(initial.first_exposure_date) AS first_exposure_date,
      MIN(t0.timestamp) AS timestamp
    FROM
      __distinctUsers initial
      JOIN __denominator0 t0 ON (t0.anonymous_id = initial.anonymous_id)
    WHERE
      t0.timestamp >= initial.timestamp
      AND t0.timestamp <= DATETIME_ADD(initial.timestamp, INTERVAL 72 HOUR)
    GROUP BY
      initial.anonymous_id
  ),
  __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 <= DATETIME_ADD(d.timestamp, INTERVAL 72 HOUR) THEN m.value
          ELSE NULL
        END
      ) as value
    FROM
      __denominatorUsers 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
Copy code
-- Traffic Query for Health Tab
WITH
  __rawExperiment AS (
    SELECT
      user_pseudo_id as anonymous_id,
      TIMESTAMP_MICROS(event_timestamp) as timestamp,
      experiment_id_param.value.string_value AS experiment_id,
      variation_id_param.value.int_value AS variation_id,
      geo.country as country,
      traffic_source.source as source,
      traffic_source.medium as medium,
      device.category as device,
      device.web_info.browser as browser,
      device.operating_system as os
    FROM
      `involuted-reach-399721`.`analytics_380888564`.`events_*`,
      UNNEST (event_params) AS experiment_id_param,
      UNNEST (event_params) AS variation_id_param
    WHERE
      (
        (_TABLE_SUFFIX BETWEEN '20231225' AND '20240112')
        OR (
          _TABLE_SUFFIX BETWEEN 'intraday_20231225' AND 'intraday_20240112'
        )
      )
      AND event_name = 'experiment_viewed'
      AND experiment_id_param.key = 'experiment_id'
      AND variation_id_param.key = 'variation_id'
      AND user_pseudo_id is not null
  ),
  __experimentExposures AS (
    -- Viewed Experiment
    SELECT
      e.anonymous_id as anonymous_id,
      cast(e.variation_id as string) as variation,
      CAST(e.timestamp as DATETIME) as timestamp
    FROM
      __rawExperiment e
    WHERE
      e.experiment_id = 'add-free-shipping-announcement-bar-relaunch-2'
      AND e.timestamp >= '2023-12-25 15:15:00'
      AND e.timestamp <= '2024-01-12 15:40:14'
  ),
  __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
  ),
  __distinctUnits AS (
    SELECT
      anonymous_id,
      variation,
      format_date("%F", date_trunc(first_exposure_timestamp, DAY)) AS dim_exposure_date
    FROM
      __experimentUnits
  )
  -- One row per variation per dimension slice
  -- dim_exposure_date
  (
    SELECT
      variation AS variation,
      dim_exposure_date AS dimension_value,
      MAX(cast('dim_exposure_date' as string)) AS dimension_name,
      COUNT(*) AS units
    FROM
      __distinctUnits
    GROUP BY
      variation,
      dim_exposure_date
  )
LIMIT
  3000
the trackingcallback is fine as we are receiving data from GA4
image.png
image.png
b
Hi AJ, thank you, I see all the screenshots and SQL queries you sent. This is the public channel though -- did you want to send these via DM instead?
e
sure
sent you the dm along with growthbook SDK code
b
I see it, thank you. I'm juggling multiple support requests this morning and will take a look as soon as I can
e
Thank you!
hey @brief-honey-45610 we just found out that it is a bigquery issue, thanks!
b
Oh awesome! I’m glad you were able to isolate the cause!
24 Views
Open in Slack
PreviousNext
Powered by