fast-yacht-85144
04/27/2023, 10:50 AMswift-rose-65038
05/02/2023, 8:25 AMswift-rose-65038
05/02/2023, 8:26 AMdamp-branch-45047
05/03/2023, 10:21 AMtall-addition-93394
05/03/2023, 1:57 PMtall-addition-93394
05/03/2023, 2:21 PMbusy-air-96466
05/03/2023, 5:00 PM-- Counter button clicks (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,
<http://variation_id_param.value.int|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
(
SELECT
*
FROM
``analytics_xxx.events_*``
WHERE
_TABLE_SUFFIX BETWEEN '20230403' AND '20230505'
UNION ALL
SELECT
*
FROM
``analytics_xxx.events_intraday_*``
WHERE
_TABLE_SUFFIX BETWEEN '20230403' AND '20230505'
),
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 variation_id_param.key = 'variation_id'
AND user_id is not null
),
__experiment 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,
CAST(e.timestamp as DATETIME) as conversion_start,
DATETIME_ADD(CAST(e.timestamp as DATETIME), INTERVAL 72 HOUR) as conversion_end
FROM
__rawExperiment e
WHERE
e.experiment_id = 'new-secondary-panel-title'
AND CAST(e.timestamp as DATETIME) >= DATETIME("2023-04-03 17:53:00")
),
__metric as ( -- Metric (Counter button clicks)
SELECT
user_id as user_id,
m.value as value,
CAST(m.timestamp as DATETIME) as timestamp,
CAST(m.timestamp as DATETIME) as conversion_start,
CAST(m.timestamp as DATETIME) as conversion_end
FROM
(
SELECT
user_id,
user_pseudo_id as anonymous_id,
TIMESTAMP_MICROS(event_timestamp) as timestamp,
1 as value
FROM
``analytics_356435236.events_intraday_*``
WHERE
event_name = 'Click counter button'
AND _TABLE_SUFFIX BETWEEN '20230328' AND '20231231'
UNION ALL
SELECT
user_id,
user_pseudo_id as anonymous_id,
TIMESTAMP_MICROS(event_timestamp) as timestamp,
1 as value
FROM
``analytics_356435236.events_*``
WHERE
event_name = 'Click counter button'
AND _TABLE_SUFFIX BETWEEN '20230328' AND '20231231'
) m
WHERE
CAST(m.timestamp as DATETIME) >= DATETIME("2023-04-03 17:53:00")
),
__distinctUsers as (
-- One row per user
SELECT
e.user_id as user_id,
cast('All' as string) as dimension,
(
CASE
WHEN count(distinct e.variation) > 1 THEN '__multiple__'
ELSE max(e.variation)
END
) as variation,
MIN(e.conversion_start) as conversion_start,
MIN(e.conversion_end) as conversion_end
FROM
__experiment e
GROUP BY
e.user_id
),
__userMetric as (
-- Add in the aggregate metric value for each user
SELECT
d.variation,
d.dimension,
d.user_id,
SUM(m.value) as value
FROM
__distinctUsers d
JOIN __metric m ON (m.user_id = d.user_id)
WHERE
m.timestamp >= d.conversion_start
AND m.timestamp <= d.conversion_end
GROUP BY
d.variation,
d.dimension,
d.user_id
),
__stats AS (
-- One row per variation/dimension with aggregations
SELECT
m.variation,
m.dimension,
COUNT(*) AS count,
SUM(COALESCE(m.value, 0)) AS main_sum,
SUM(POWER(COALESCE(m.value, 0), 2)) AS main_sum_squares
FROM
__userMetric m
GROUP BY
m.variation,
m.dimension
),
__overallUsers as (
-- Number of users in each variation/dimension
SELECT
variation,
dimension,
COUNT(*) as users
FROM
__distinctUsers
GROUP BY
variation,
dimension
)
SELECT
u.variation,
u.dimension,
u.users AS users,
'mean' as statistic_type,
'count' as main_metric_type,
COALESCE(s.main_sum, 0) AS main_sum,
COALESCE(s.main_sum_squares, 0) AS main_sum_squares
FROM
__overallUsers u
LEFT JOIN __stats s ON (
u.variation = s.variation
AND u.dimension = s.dimension
)
Here’s a screenshot of the SELECT in BigQuery, and a screenshot of the experiment in GrowthBook. Is there anything else you can suggest for me to try?purple-winter-48671
05/03/2023, 10:01 PMtall-addition-93394
05/04/2023, 12:27 PMbulky-monkey-20562
05/04/2023, 2:30 PMsteep-dawn-35762
05/04/2023, 2:43 PMquaint-window-98285
05/04/2023, 4:11 PMaatest_1
, aatest_2
, and so forth. is this expected behavior?steep-dawn-35762
05/04/2023, 5:04 PMmany-account-13676
05/04/2023, 7:53 PMicy-market-57765
05/04/2023, 11:02 PMsquare-leather-98692
05/05/2023, 10:00 AMglamorous-raincoat-43541
05/05/2023, 6:17 PMbrave-train-899
05/08/2023, 9:41 AMtall-addition-93394
05/08/2023, 10:32 AMadventurous-jelly-25184
05/08/2023, 11:10 AMv1.7.0
to v2.1.0
. Should we just update the docker file with new version or is there anything do like migrations for MondoDB and all?brash-fireman-45530
05/08/2023, 11:23 AMmodern-nightfall-33300
05/09/2023, 11:52 AMdamp-mechanic-13144
05/10/2023, 3:29 AMuser.organization.plan == 'pro' and <anything else…>
billions-memory-82613
05/10/2023, 6:23 AMshy-memory-49932
05/10/2023, 12:35 PMbusy-air-96466
05/10/2023, 4:00 PMdry-eye-65136
05/10/2023, 4:06 PMbillions-memory-82613
05/10/2023, 11:17 PMsome-planet-44104
05/11/2023, 7:57 AMsome-planet-44104
05/11/2023, 9:23 AM