echoing-park-17810
10/17/2023, 2:16 PMswift-helmet-3648
10/17/2023, 4:14 PMechoing-park-17810
10/17/2023, 4:38 PMswift-helmet-3648
10/17/2023, 9:17 PMuser_id
or anonymous_id
(the identifiers used by metrics)thankful-oil-10632
10/18/2023, 1:58 AMhelpful-application-7107
10/18/2023, 4:25 PManonymous_id
and user_id
as identifier types, but there is only one column in your metric return anonymous_id
, so something is strange in your setup here
2. You have multiple exposures. This means that for whatever identifier type you are using for the experiment (this is defined by the Experiment Assignment Table you chose for your experiment), there are some id
values that have multiple variants. This is another sign that something is wrong.thankful-oil-10632
10/18/2023, 4:31 PM-- Count of begin_checkout (count)
WITH
__rawExperiment AS (
SELECT
user_pseudo_id as anonymous_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
`cedar-electronics-prismfly`.`analytics_253839784`.`events_*`,
UNNEST (event_params) AS experiment_id_param,
UNNEST (event_params) AS variation_id_param
WHERE
(
(_TABLE_SUFFIX BETWEEN '20231012' AND '20231018')
OR (
_TABLE_SUFFIX BETWEEN 'intraday_20231012' AND 'intraday_20231018'
)
)
AND event_name = 'experiment_viewed'
AND experiment_id_param.key = 'experiment_id'
AND variation_id_param.key = 'variation_id'
AND user_pseudo_id is not null
),
__experimentExposures AS (
-- Viewed Experiment
SELECT
e.anonymous_id as anonymous_id,
cast(e.variation_id as string) as variation,
CAST(e.timestamp as DATETIME) as timestamp
FROM
__rawExperiment e
WHERE
e.experiment_id = 'delivery-timer-product-page'
AND e.timestamp >= '2023-10-12 15:33:00'
AND e.timestamp <= '2023-10-18 14:15:28'
),
__experimentUnits AS (
-- One row per user
SELECT
e.anonymous_id AS anonymous_id,
cast('All' as string) AS dimension,
MIN(e.timestamp) AS first_exposure_timestamp,
(
CASE
WHEN count(distinct e.variation) > 1 THEN '__multiple__'
ELSE max(e.variation)
END
) AS variation
FROM
__experimentExposures e
GROUP BY
e.anonymous_id
),
__distinctUsers AS (
SELECT
anonymous_id,
dimension,
variation,
first_exposure_timestamp AS timestamp,
date_trunc(first_exposure_timestamp, DAY) AS first_exposure_date
FROM
__experimentUnits
),
__metric as ( -- Metric (Count of begin_checkout)
SELECT
anonymous_id as anonymous_id,
m.value as value,
CAST(m.timestamp as DATETIME) as timestamp
FROM
(
SELECT
user_pseudo_id as anonymous_id,
TIMESTAMP_MICROS(event_timestamp) as timestamp,
1 as value
FROM
`cedar-electronics-prismfly.analytics_253839784.events_*`
WHERE
(
(_TABLE_SUFFIX BETWEEN '20231012' AND '20231018')
OR (
_TABLE_SUFFIX BETWEEN 'intraday_20231012' AND 'intraday_20231018'
)
)
AND event_name = 'begin_checkout'
) m
WHERE
m.timestamp >= '2023-10-12 15:33:00'
AND m.timestamp <= '2023-10-18 14:15:28'
),
__userMetricJoin as (
SELECT
d.variation AS variation,
d.dimension AS dimension,
d.anonymous_id AS anonymous_id,
(
CASE
WHEN m.timestamp >= d.timestamp THEN m.value
ELSE NULL
END
) as value
FROM
__distinctUsers d
LEFT JOIN __metric m ON (m.anonymous_id = d.anonymous_id)
),
__userMetricAgg as (
-- Add in the aggregate metric value for each user
SELECT
variation,
dimension,
anonymous_id,
SUM(COALESCE(value, 0)) as value
FROM
__userMetricJoin
GROUP BY
variation,
dimension,
anonymous_id
)
-- One row per variation/dimension with aggregations
SELECT
m.variation AS variation,
m.dimension AS dimension,
COUNT(*) AS users,
'mean' as statistic_type,
'count' as main_metric_type,
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
helpful-application-7107
10/18/2023, 4:39 PMuser_pseudo_id
in the tracking callback. What's happening is that there are multiple different variation_id
values for the same user_pseudo_id
.thankful-oil-10632
10/20/2023, 1:33 PMuser_pseudo_id
in the tracking callback."_helpful-application-7107
10/20/2023, 2:15 PMthankful-oil-10632
10/20/2023, 2:17 PMvariation_id
,
count(DISTINCT user_pseudo_id) as Total_users
,
count(
DISTINCT CONCAT(
user_pseudo_id,
"-",
(
SELECT
value.int_value
FROM
unnest (event_params)
WHERE
key="ga_session_id"
)
)
) as Sessions
,
COUNTIF(event_name='add_to_cart') as Add_to_cart
FROM
cedar-electronics-prismfly.analytics_253839784.events_*
,
unnest(event_params) as experiment_id_param
,
unnest(event_params) as variation_id_param
WHERE
event_name='experiment_viewed'
AND experiment_id_param.key = 'experiment_id'
AND experiment_id_param.value.string_value = 'aa-test-cobra'
AND variation_id_param.key = 'variation_id'
Group by 1helpful-application-7107
10/20/2023, 3:55 PMBut i am not able to understand why the metrics are not showing up the values in bigquery as wellBecause you are filtering out event names in this query:
SELECT
variation_id_param.value.int_value AS `variation_id`,
count(DISTINCT user_pseudo_id) as `Total_users`,
count(
DISTINCT CONCAT(
user_pseudo_id,
"-",
(
SELECT
value.int_value
FROM
unnest (event_params)
WHERE
key="ga_session_id"
)
)
) as `Sessions`,
------- THIS LINE WILL DO NOTHING BECAUSE YOU ALREADY HAVE event_name='experiment_viewed' in the WHERE CLAUSE ------
COUNTIF(event_name='add_to_cart') as `Add_to_cart`
FROM
`cedar-electronics-prismfly.analytics_253839784.events_*`,
unnest(event_params) as `experiment_id_param`,
unnest(event_params) as `variation_id_param`
WHERE
event_name='experiment_viewed'
AND experiment_id_param.key = 'experiment_id'
AND experiment_id_param.value.string_value = 'aa-test-cobra'
AND variation_id_param.key = 'variation_id'
Group by 1
user_pseudo_id
that have multiple variations. Maybe you should consider restarting the test with a new experiment
key, so that you can see if this happens again. Check. to ensure you are always setting the user_psuedo_id
in the SDK before making any checks for feature values in the app.
WITH variations AS (
SELECT
COUNT(DISTINCT variation_id_param.value.int_value) as n_variations
user_psuedo_id
FROM
`cedar-electronics-prismfly.analytics_253839784.events_*`,
unnest(event_params) as `experiment_id_param`,
unnest(event_params) as `variation_id_param`
WHERE
event_name='experiment_viewed'
AND experiment_id_param.key = 'experiment_id'
AND experiment_id_param.value.string_value = 'aa-test-cobra'
AND variation_id_param.key = 'variation_id'
GROUP BY user_pseudo_id
)
SELECT
n_variations,
COUNT(*)
FROM variations
GROUP BY 1
thankful-oil-10632
10/20/2023, 8:29 PMcedar-electronics-prismfly
.analytics_253839784
.events_*
,
UNNEST(event_params) AS experiment_id_param,
UNNEST(event_params) AS variation_id_param
WHERE
((_TABLE_SUFFIX BETWEEN '{{date startDateISO "yyyyMMdd"}}' AND '{{date endDateISO "yyyyMMdd"}}') OR
(_TABLE_SUFFIX BETWEEN 'intraday_{{date startDateISO "yyyyMMdd"}}' AND 'intraday_{{date endDateISO "yyyyMMdd"}}'))
AND event_name = 'experiment_viewed'
AND experiment_id_param.key = 'experiment_id'
AND variation_id_param.key = 'variation_id'
AND user_pseudo_id is not nullhelpful-application-7107
10/23/2023, 2:44 PMDoes that mean only 8 user_pseudo_ids are able to see the variation 2 ?No, this means that there are 8 users that have seen 2 variations.
please let me know if the below experiment query is right ?The query looks fine. If it's an issue with one project and the queries are the same, I would assume that the problem is how they are implementing the SDK in their code.
echoing-park-17810
10/23/2023, 3:29 PMhelpful-application-7107
10/23/2023, 4:06 PMuser_pseudo_id
values that both have (1) an experiment exposure and (2) an add_to_cart event. That isn't necessarily a problem, because maybe no users in your experiment are adding things to cart, but I don't think that's what's going on. I think it's more likely that you are not persisting the user_psuedo_id across pages, so that when users go to add_to_cart
they're using a different user_pseudo_id
.
You are doing getUUID
right in the gb
instance, which means you aren't persisting it.
Therefore, when you send the experiment exposure and the add_to_cart event to your BigQuery, they're happening with totally random user ids, so we cannot match them.thankful-oil-10632
10/26/2023, 5:18 PMhelpful-application-7107
10/26/2023, 6:48 PMthankful-oil-10632
10/26/2023, 6:50 PMhelpful-application-7107
10/26/2023, 6:51 PMechoing-park-17810
11/03/2023, 2:24 PMhelpful-application-7107
11/03/2023, 4:11 PMechoing-park-17810
11/07/2023, 2:43 PM