jolly-oil-48997
03/19/2025, 8:32 AMcategory_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.steep-dog-1694
03/19/2025, 10:29 PM1
as the value column? This is ideal for counting rows (please see attached screenshot from the Metric
page on GrowthBook's UI).
Thanks,
Lukejolly-oil-48997
03/19/2025, 11:33 PMjolly-oil-48997
03/20/2025, 12:10 AMValue Column
in the metric page? I couldnot find itsteep-dog-1694
03/20/2025, 12:13 AMEdit
button next to Query Settings
jolly-oil-48997
03/20/2025, 12:16 AMsteep-dog-1694
03/20/2025, 12:17 AMsteep-dog-1694
03/20/2025, 12:18 AMjolly-oil-48997
03/20/2025, 12:21 AMsteep-dog-1694
03/20/2025, 12:31 AMuser_id
values, rather than their counts? In your query, ,can you please check that when you sum value = 1
?jolly-oil-48997
03/20/2025, 5:02 AMsteep-dog-1694
03/20/2025, 5:12 AMjolly-oil-48997
03/20/2025, 5:13 AMjolly-oil-48997
03/20/2025, 5:15 AMjolly-oil-48997
03/20/2025, 5:15 AMsteep-dog-1694
03/20/2025, 5:16 AMjolly-oil-48997
03/20/2025, 5:48 AMsteep-dog-1694
03/20/2025, 3:25 PMid
, rather than counting the number of ids. I would try changing your sql:
value_param.value.int_value as value
to
1 as value
jolly-oil-48997
03/25/2025, 7:35 AMmain_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.steep-dog-1694
03/25/2025, 3:52 PMmain_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,
Lukejolly-oil-48997
03/25/2025, 3:55 PMsteep-dog-1694
03/25/2025, 3:58 PMjolly-oil-48997
03/25/2025, 4:07 PMsteep-dog-1694
03/25/2025, 4:08 PMjolly-oil-48997
03/25/2025, 4:09 PMmain_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?steep-dog-1694
03/25/2025, 4:10 PMjolly-oil-48997
03/25/2025, 4:11 PMsteep-dog-1694
03/25/2025, 4:11 PMjolly-oil-48997
03/25/2025, 4:11 PMhelpful-application-7107
03/25/2025, 4:11 PM