Hi GrowthBook team, I'm facing an issue where a custom metric I created in GrowthBook returns different results from what I get when querying directly in BigQuery. I'm trying to configure everything inside GrowthBook to match the numbers from BigQuery, which are highly reliable in our case. Context 1. I’ve created a Fact Table in GrowthBook based on a BigQuery query. 2. Then I set up a Ratio metric to calculate the percentage of users with a specific value in the

return

field (

"error"

). 3. However, the value returned by GrowthBook does not match the expected result calculated directly in BigQuery using the same logic. BigQuery Reference Query

sql
WITH fact_table AS (
  SELECT
    e.user_id,
    e.user_pseudo_id AS anonymous_id,
    TIMESTAMP_MICROS(event_timestamp) AS timestamp,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'description') AS description,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'socialNetworkName') AS socialNetworkName,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'return') AS return
  FROM
    `******.analytics_2********2.events_*` e
  WHERE
    event_name = "connection_social_network"
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'socialNetworkName') = "facebook"
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'socialNetworkName') IS NOT NULL
    AND (
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'return') = 'success'
      OR (
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'return') = 'error'
        AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'description') IS NOT NULL
      )
    )
    AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
    AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
)

SELECT
  DATE(timestamp, 'America/Sao_Paulo') AS dia,
  COUNTIF(return = 'error') AS numerador,
  COUNT(*) AS denominador,
  ROUND(COUNTIF(return = 'error') / COUNT(*) * 100, 2) AS percent_error,
  ROUND(COUNTIF(return = 'success') / COUNT(*) * 100, 2) AS percent_success
FROM fact_table
GROUP BY dia
ORDER BY dia DESC;

GrowthBook Setup • Fact Table:

Filter Instagram connections

• Filters: ▪︎

event_name = "connection_social_network"

▪︎

socialNetworkName = "facebook"

▪︎

return = "error"

or

"success"

▪︎

description IS NOT NULL

• Metric Type: Ratio ◦ Numerator: Count of rows where

return = "error"

◦ Denominator: Total count of rows after experiment exposure • Metric Goal: Decrease metric value 👉 Please refer to the attached screenshots for the exact configuration: • Fact Table SQL setup • Metric configuration • SQL preview from GrowthBook Issue Despite replicating the logic between BigQuery and GrowthBook, the metric result shown inside GrowthBook doesn’t match the output from BigQuery. I suspect it might be related to: • Differences in default time zones • Filter logic timing (e.g.,

timestamp >= exposure_timestamp

) • Unseen row filtering nuances What I Need Help verifying: 1. Whether my metric configuration matches the logic from BigQuery correctly. 2. If there are any known nuances with how GrowthBook processes time ranges or filters that could explain the mismatch. 3. Suggestions to ensure alignment between GB metrics and BQ calculations. Thanks in advance! Let me know if you need more information. Best, Pedro

Hi Pedro, I am a data scientist at GrowthBook. I would compare the BigQuery output above to the query generated by GrowthBook. You can find this by clicking the

View Queries

button under the time series you sent me. Best, Luke

Hey, Luke. thanks for help me. The

View Queries

is here:

-- error_percent_connection_social_network Metric Analysis
WITH
  __factTable AS ( -- Fact Table (Filter Instagram connections)
    SELECT
      anonymous_id as anonymous_id,
      CAST(m.timestamp as DATETIME) as timestamp,
      -- error_percent_connection_social_network
      CASE
        WHEN (
          return
            = 'error'
        ) THEN 1
        ELSE NULL
      END as m0_value,
      -- error_percent_connection_social_network (denominator)
      1 as m0_denominator
    FROM
      (
        SELECT
          e.user_id,
          e.user_pseudo_id AS anonymous_id,
          TIMESTAMP_MICROS(event_timestamp) AS timestamp,
          (
            SELECT
              value.string_value
            FROM
              UNNEST (event_params)
            WHERE
              key = 'description'
          ) AS description,
          (
            SELECT
              value.string_value
            FROM
              UNNEST (event_params)
            WHERE
              key = 'socialNetworkName'
          ) AS socialNetworkName,
          (
            SELECT
              value.string_value
            FROM
              UNNEST (event_params)
            WHERE
              key = 'return'
          ) AS
        return
        FROM
          `*****.analytics_********.events_*` e
        WHERE
          event_name = "connection_social_network"
          AND (
            SELECT
              value.string_value
            FROM
              UNNEST (event_params)
            WHERE
              key = 'socialNetworkName'
          ) = "facebook"
          AND (
            SELECT
              value.string_value
            FROM
              UNNEST (event_params)
            WHERE
              key = 'socialNetworkName'
          ) IS NOT NULL
          AND (
            (
              SELECT
                value.string_value
              FROM
                UNNEST (event_params)
              WHERE
                key = 'return'
            ) = 'success'
            OR (
              (
                SELECT
                  value.string_value
                FROM
                  UNNEST (event_params)
                WHERE
                  key = 'return'
              ) = 'error'
              AND (
                SELECT
                  value.string_value
                FROM
                  UNNEST (event_params)
                WHERE
                  key = 'description'
              ) IS NOT NULL
            )
          )
          AND (
            (_TABLE_SUFFIX BETWEEN '20250325' AND '20250425')
            OR (
              _TABLE_SUFFIX BETWEEN 'intraday_20250325' AND 'intraday_20250425'
            )
          )
      ) m
    WHERE
      m.timestamp >= '2025-03-25 03:00:00'
      AND m.timestamp <= '2025-04-25 02:59:00'
  ),
  __userMetricDaily AS (
    -- Get aggregated metric per user by day
    SELECT
      f.anonymous_id AS anonymous_id,
      date_trunc(timestamp, DAY) AS date,
      COUNT(f.m0_value) AS value,
      COUNT(f.m0_denominator) AS denominator
    FROM
      __factTable f
    GROUP BY
      date_trunc(f.timestamp, DAY),
      f.anonymous_id
  ),
  __userMetricOverall AS (
    SELECT
      anonymous_id,
      SUM(COALESCE(value, 0)) AS value,
      SUM(COALESCE(denominator, 0)) AS denominator
    FROM
      __userMetricDaily
    GROUP BY
      anonymous_id
  ),
  __statisticsDaily AS (
    SELECT
      date,
      MAX(cast('date' as string)) AS data_type,
      'uncapped' AS capped,
      COUNT(*) as units,
      SUM(COALESCE(value, 0)) as main_sum,
      SUM(POWER(COALESCE(value, 0), 2)) as main_sum_squares,
      SUM(COALESCE(denominator, 0)) as denominator_sum,
      SUM(POWER(COALESCE(denominator, 0), 2)) as denominator_sum_squares,
      SUM(COALESCE(denominator, 0) * COALESCE(value, 0)) as main_denominator_sum_product
    FROM
      __userMetricDaily
    GROUP BY
      date
  ),
  __statisticsOverall AS (
    SELECT
      CAST(NULL AS DATE) AS date,
      MAX(cast('overall' as string)) AS data_type,
      'uncapped' AS capped,
      COUNT(*) as units,
      SUM(COALESCE(value, 0)) as main_sum,
      SUM(POWER(COALESCE(value, 0), 2)) as main_sum_squares,
      SUM(COALESCE(denominator, 0)) as denominator_sum,
      SUM(POWER(COALESCE(denominator, 0), 2)) as denominator_sum_squares,
      SUM(COALESCE(denominator, 0) * COALESCE(value, 0)) as main_denominator_sum_product
    FROM
      __userMetricOverall
  )
SELECT
  *
FROM
  __statisticsOverall
UNION ALL
SELECT
  *
FROM
  __statisticsDaily

Hi Pedro, I suggest you compare this query to the query you are running in BigQuery. Best, Luke