HI Team, I need a quick help with the one of the metrics that I had created that uses the GA4 events data as a data source coming from BigQuery. I have a metric that should get me the total no. of rows for

category_button_click

GA event where the its attribute is

entryPoint

= that has value as

productTile

But currently it gives me

main_sum_square

in the experiment. Ideally I should get the total count of the

category_button_click

event where the event param is

productTile

The combined query is

-- DEV 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-dev`.`analytics_320528630`.`events_*`,
      UNNEST (event_params) AS experiment_id_param,
      UNNEST (event_params) AS variation_id_param
    WHERE
      (
        (_TABLE_SUFFIX BETWEEN '20250313' AND '20250319')
        OR (
          _TABLE_SUFFIX BETWEEN 'intraday_20250313' AND 'intraday_20250319'
        )
      )
      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-13 00:00:00'
      AND e.timestamp <= '2025-03-19 04:26:17'
  ),
  __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 (DEV 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,
          value_param.value.int_value as value
        FROM
          `axinan-dev.analytics_320528630.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 EXISTS (
            SELECT
              1
            FROM
              UNNEST (event_params) AS ep3
            WHERE
              ep3.key = 'url'
              AND ep3.value.string_value LIKE '%staging%'
          )
          AND (
            (_TABLE_SUFFIX BETWEEN '20250313' AND '20250319')
            OR (
              _TABLE_SUFFIX BETWEEN 'intraday_20250313' AND 'intraday_20250319'
            )
          )
      ) m
    WHERE
      m.timestamp >= '2025-03-13 00:00:00'
      AND m.timestamp <= '2025-03-19 04:26:17'
  ),
  __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-03-19 04:26:17' 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

and the query that i had created to extract the data is

SELECT
 user_id,
  user_pseudo_id AS anonymous_id,
  TIMESTAMP_MICROS(event_timestamp) AS timestamp,
  value_param.value.int_value as value
FROM
  `axinan-dev.analytics_320528630.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 EXISTS (
    SELECT 1
    FROM UNNEST(event_params) AS ep3
    WHERE ep3.key = 'url' AND ep3.value.string_value LIKE '%staging%'
  )
  AND ((_TABLE_SUFFIX BETWEEN '{{date startDateISO "yyyyMMdd"}}' AND '{{date endDateISO "yyyyMMdd"}}') OR
       (_TABLE_SUFFIX BETWEEN 'intraday_{{date startDateISO "yyyyMMdd"}}' AND 'intraday_{{date endDateISO "yyyyMMdd"}}'))

Help will be really appreciated.

Hi Nitin, I am a Data Scientist at GrowthBook. For your metric, are you using

1

as the value column? This is ideal for counting rows (please see attached screenshot from the

Metric

page on GrowthBook's UI). Thanks, Luke

@steep-dog-1694 let me check this and get back to you,

Please click the

Edit

button next to

Query Settings

Is the highlighted field you are talking about?

No - it is above the Query

Here's the screencast , yeah it's a count metric

Thanks - I'm not sure if there is supposed to be audio, but I can't hear it. What version of GrowthBook are you using? We have updated our metric fields a bit. However, based upon your large sums and sums of squares, it looks like you may be summing the

user_id

values, rather than their counts? In your query, ,can you please check that when you sum

value = 1

?

By growthbook version, you mean the sdk version or growthbook UI?

UI

Where can i see the UI version?

yes

Is there a way to resolve this issue with the current free version?

Given how large your sums and sums of squares are, it looks like you are summing the values for the

id

, rather than counting the number of ids. I would try changing your sql:

value_param.value.int_value as value

to

1 as value

@steep-dog-1694 I need one more help in this. Do you know how we can only get the count of users and not

main_sum

and

main_sum_squares

in the dashboard.

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

how can this query be updated to get only

COUNT(*) AS users

and not others. As shown the highlighted values in the below screenshot. i need only COUNT of users but the combined query gives everything.

Hi Nitin, Are you hoping to run a query that returns only user counts, or are you hoping that the

main_sum

and

main_sum_squares

results aren't in the dashboard? If the former, please run this:

SELECT
  m.variation AS variation,
  m.dimension AS dimension,
  COUNT(*) AS users

If the latter, we cannot suppress results from showing in the dashboard. Hope this helps, Luke

I am hoping to run a query that returns only user counts,

I'm sorry, I deleted the extra two lines in the my message above, please try the query now

How can i run the query in the GrowthBook? That entire query is auto generated and I don't see any option to edit.

I'm sorry, you can't run the query standalone in GrowthBook.

So we will always receive

main_sum

and

main_sum_squares

as part of the result, and it cannot not be modified as it is required to generate the variants' dashboard, right?

correct

Any workaround to get only Count of the users?

not that I know of.

I thought there should be some way to get only count of the users.

User counts can be found on the health tab.