green-engine-21191
04/25/2023, 11:50 AMrelation "staging_react_web.experiment_viewed" does not exist
Full SQL query is added to the thread of this message.
I am currently not sending any events yet to segment. Is there any naming convention I should follow for the events name?-- Test New Checkout UI Component (binomial)
WITH
__rawExperiment as (
SELECT
user_id,
received_at as timestamp,
experiment_id,
variation_id,
context_campaign_source as source,
context_campaign_medium as medium,
(
CASE
WHEN context_user_agent LIKE '%Mobile%' THEN 'Mobile'
ELSE 'Tablet/Desktop'
END
) as device,
(
CASE
WHEN context_user_agent LIKE '% Firefox%' THEN 'Firefox'
WHEN context_user_agent LIKE '% OPR%' THEN 'Opera'
WHEN context_user_agent LIKE '% Edg%' THEN ' Edge'
WHEN context_user_agent LIKE '% Chrome%' THEN 'Chrome'
WHEN context_user_agent LIKE '% Safari%' THEN 'Safari'
ELSE 'Other'
END
) as browser
FROM
staging_react_web.experiment_viewed
WHERE
user_id is not null
),
__experiment as ( -- Viewed Experiment
SELECT
e.user_id as user_id,
cast(e.variation_id as varchar) as variation,
e.timestamp as timestamp,
e.timestamp as conversion_start,
e.timestamp + INTERVAL '72 hours' as conversion_end
FROM
__rawExperiment e
WHERE
e.experiment_id = 'web-checkout-new-address'
AND e.timestamp >= '2023-04-19 11:07:00'
),
__metric as ( -- Metric (Test New Checkout UI Component)
SELECT
user_id as user_id,
1 as value,
m.timestamp as timestamp,
m.timestamp as conversion_start,
m.timestamp as conversion_end
FROM
(
SELECT
user_id,
anonymous_id,
received_at as timestamp
FROM
staging_react_web.test_new_checkout_ui_component
) m
WHERE
m.timestamp >= '2023-04-19 11:07:00'
),
__distinctUsers as (
-- One row per user
SELECT
e.user_id as user_id,
cast('All' as varchar) 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,
MAX(1) 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,
'binomial' 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
)
fresh-football-47124
green-engine-21191
04/25/2023, 8:09 PMfresh-football-47124
const gb = new GrowthBook({
apiHost: "<https://cdn.growthbook.io>",
clientKey: "sdk-abc123",
trackingCallback: (experiment, result) => {
// Example using Segment
analytics.track("Experiment Viewed", {
experimentId: experiment.key,
variationId: result.key,
});
},
});