Skip to content

Instantly share code, notes, and snippets.

@foundinblank
Last active November 14, 2022 16:54
Show Gist options
  • Save foundinblank/259c2d0d6e66b513c8ecf0694877ee1f to your computer and use it in GitHub Desktop.
Save foundinblank/259c2d0d6e66b513c8ecf0694877ee1f to your computer and use it in GitHub Desktop.
Create User Level Table
create table experiments.sundae_wizard_users as
-- Get all users exposed to the experiment
with exposed_users as (
select
experiment_name,
user_id,
feature_flag_evaluation,
evaluated_at
from analytics.fct_experiment_assignments
where experiment_name = 'sundae_wizard'
and evaluated_at between '2022-07-01' and '2022-07-15'
),
-- If an user id shows up multiple times, it's because they saw more than one variant
multiple_variants as (
select
user_id,
count(*) as variants
from exposed_users
group by 1
having variants > 1
),
-- Remove users who saw multiple variants
exposed_users_cleaned as (
select exposed_users.*
from exposed_users
left join multiple_variants
on exposed_users.user_id = multiple_variants.user_id
where multiple_variants.user_id is null
),
-- Build your users table, and exclude spam users
users as (
select
exposed_users_cleaned.user_id,
iff(exposed_users_cleaned.feature_flag_evaluation, 'test', 'control') as cohort,
exposed_users_cleaned.evaluated_at as assigned_at,
dim_users.created_at,
dim_users.city_name,
dim_users.signup_marketing_channel,
dim_users.first_order_at
from analytics.dim_users
inner join exposed_users
on dim_users.user_id = exposed_users.user_id
where dim_users.is_spam = false
),
-- Last, join order metrics for all orders during the experiment period (excluding any orders prior to exp. assignment)
final as (
select
users.*,
count(iff(fct_orders.order_type = 'sundae', fct_orders.order_id, null)) as cnt_sundae_orders,
count(fct_orders.order_id) as cnt_orders,
coalesce(sum(fct_orders.order_value), 0) as total_order_value,
cnt_orders > 0 as is_user_converted
from users
left join analytics.fct_orders
on users.user_id = fct_orders.user_id
and fct_orders.order_placed_at between '2022-07-01' and '2022-07-15'
and fct_orders.order_placed_at > users.assigned_at
group by 1, 2, 3, 4, 5, 6, 7
)
select * from final;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment