Hi guys, We are currently conducting a GB experiment in our production (PROD) environment, utilizing data sourced from a BigQuery (GA4) database. A metric has been defined to calculate the total count of

category_button_click

events triggered via GA4 between March 18th and March 31st. The metric has the custom query

SELECT
  user_id,
  user_pseudo_id AS anonymous_id,
  TIMESTAMP_MICROS(event_timestamp) AS timestamp,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'entryPoint') AS entryPoint,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'url') AS url,
  1 as value
FROM
  `axinan-prod`.`analytics_323303173`.`events_*`,
  UNNEST(event_params) AS value_param
WHERE
  event_name = '{{eventName}}'
  AND EXISTS (
    SELECT 1
    FROM UNNEST(event_params) AS ep2
    WHERE ep2.key = 'entryPoint' AND ep2.value.string_value = 'productTile'
  )
  AND ((_TABLE_SUFFIX BETWEEN '{{date startDateISO "yyyyMMdd"}}' AND '{{date endDateISO "yyyyMMdd"}}') OR
       (_TABLE_SUFFIX BETWEEN 'intraday_{{date startDateISO "yyyyMMdd"}}' AND 'intraday_{{date endDateISO "yyyyMMdd"}}'))
       QUALIFY ROW_NUMBER() OVER (PARTITION BY event_timestamp ORDER BY event_timestamp) = 1

My query filters for events where

eventName

is

category_button_click

. When I execute this query directly in the Google BigQuery console, I obtain a count of 3326, as shown in the screenshot. However, when I attempt to retrieve the same count using a GrowthBook metric, I receive results for 'Count of users',

main_sum

, and

main_sum_squares

instead of the expected count of

category_button_click

events with the parameter

entryPoint

equal to 'productTile'. How can I configure the GrowthBook metric to return the correct event count? Any help will be really appreciated.

Hi Nitin, I would check how the query in GrowthBook differs from the query in BigQuery. If those are the same, I would check to see make sure your population of users being referenced by both queries is the same. Best, Luke

@steep-dog-1694 Do you need more details regarding this?

Hi Nitin, Are your data stored in EU? GrowthBook queries default to US region. If so, you may need to specify the EU region in your query. Thanks, Luke

How can I so that? Can you please refer me to that?

Try using

region

in your

WHERE

clause

Somehow it didn't work, regarding your comment https://growthbookusers.slack.com/archives/C07E4HA06MD/p1743439435853959?thread_ts=1743426639.466229&cid=C07E4HA06MD The generated query i was talking about was

-- PROD Secondary Metrics - category_button_click where the attribute is entryPoint = productTile (count)
WITH
  __rawExperiment AS (
    SELECT
      user_id as user_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
      `axinan-prod`.`analytics_323303173`.`events_*`,
      UNNEST (event_params) AS experiment_id_param,
      UNNEST (event_params) AS variation_id_param
    WHERE
      (
        (_TABLE_SUFFIX BETWEEN '20250318' AND '20250401')
        OR (
          _TABLE_SUFFIX BETWEEN 'intraday_20250318' AND 'intraday_20250401'
        )
      )
      AND event_name = 'experiment_viewed'
      AND experiment_id_param.key = 'experiment_id'
      AND variation_id_param.key = 'variation_id'
      AND user_id is not null
  ),
  __experimentExposures AS (
    -- Viewed Experiment
    SELECT
      e.user_id as user_id,
      cast(e.variation_id as string) as variation,
      CAST(e.timestamp as DATETIME) as timestamp
    FROM
      __rawExperiment e
    WHERE
      e.experiment_id = 'get-quote-button'
      AND e.timestamp >= '2025-03-18 08:14:00'
      AND e.timestamp <= '2025-04-01 01:54:21'
  ),
  __experimentUnits AS (
    -- One row per user
    SELECT
      e.user_id AS user_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.user_id
  ),
  __distinctUsers AS (
    SELECT
      user_id,
      cast('' 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 (PROD Secondary Metrics - category_button_click where the attribute is entryPoint = productTile)
    SELECT
      user_id as user_id,
      m.value as value,
      CAST(m.timestamp as DATETIME) as timestamp
    FROM
      (
        SELECT
          user_id,
          user_pseudo_id AS anonymous_id,
          TIMESTAMP_MICROS(event_timestamp) AS timestamp,
          (
            SELECT
              value.string_value
            FROM
              UNNEST (event_params)
            WHERE
              key = 'entryPoint'
          ) AS entryPoint,
          (
            SELECT
              value.string_value
            FROM
              UNNEST (event_params)
            WHERE
              key = 'url'
          ) AS url,
          1 as value
        FROM
          `axinan-prod`.`analytics_323303173`.`events_*`,
          UNNEST (event_params) AS value_param
        WHERE
          event_name = 'category_button_click'
          AND EXISTS (
            SELECT
              1
            FROM
              UNNEST (event_params) AS ep2
            WHERE
              ep2.key = 'entryPoint'
              AND ep2.value.string_value = 'productTile'
          )
          AND (
            (_TABLE_SUFFIX BETWEEN '20250318' AND '20250401')
            OR (
              _TABLE_SUFFIX BETWEEN 'intraday_20250318' AND 'intraday_20250401'
            )
          )
        QUALIFY
          ROW_NUMBER() OVER (
            PARTITION BY
              event_timestamp
            ORDER BY
              event_timestamp
          ) = 1
      ) m
    WHERE
      m.timestamp >= '2025-03-18 08:14:00'
      AND m.timestamp <= '2025-04-01 01:54:21'
  ),
  __userMetricJoin as (
    SELECT
      d.variation AS variation,
      d.dimension AS dimension,
      d.user_id AS user_id,
      (
        CASE
          WHEN m.timestamp >= d.timestamp
          AND m.timestamp <= '2025-04-01 01:54:21' THEN m.value
          ELSE NULL
        END
      ) as value
    FROM
      __distinctUsers d
      LEFT JOIN __metric m ON (m.user_id = d.user_id)
  ),
  __userMetricAgg as (
    -- Add in the aggregate metric value for each user
    SELECT
      umj.variation AS variation,
      umj.dimension AS dimension,
      umj.user_id,
      SUM(COALESCE(value, 0)) as value
    FROM
      __userMetricJoin umj
    GROUP BY
      umj.variation,
      umj.dimension,
      umj.user_id
  )
  -- One row per variation/dimension with aggregations
SELECT
  m.variation AS variation,
  m.dimension AS dimension,
  COUNT(*) AS users,
  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

Hi Nitin, I would try to find a metric that produces the same result in GrowthBook as in BigQuery. For example, I would start with a simple version of this metric. After matching on the simple version, I would add back in components of the metric, until it doesn't match anymore. That would facilitate deep diving where it is going wrong. Hope this helps, Luke