HI Team, I need a quick help with the one of the ...
# experimentation
j
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
Copy code
-- 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
Copy code
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.
s
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
j
@steep-dog-1694 let me check this and get back to you,
Where can i see this
Value Column
in the metric page? I couldnot find it
s
Please click the
Edit
button next to
Query Settings
j
Is the highlighted field you are talking about?
s
No - it is above the Query
Is your metric a Count metric?
j
Here's the screencast , yeah it's a count metric
s
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
?
j
By growthbook version, you mean the sdk version or growthbook UI?
s
UI
j
Where can i see the UI version?
We are using the free version as of now and have not upgraded yet.
Is it available in the upgraded version?
s
yes
j
Is there a way to resolve this issue with the current free version?
s
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:
Copy code
value_param.value.int_value as value
to
Copy code
1 as value
j
@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.
Copy code
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.
s
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:
Copy code
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
j
I am hoping to run a query that returns only user counts,
s
I'm sorry, I deleted the extra two lines in the my message above, please try the query now
j
How can i run the query in the GrowthBook? That entire query is auto generated and I don't see any option to edit.
s
I'm sorry, you can't run the query standalone in GrowthBook.
j
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?
s
correct
j
Any workaround to get only Count of the users?
s
not that I know of.
j
I thought there should be some way to get only count of the users.
h
User counts can be found on the health tab.