Create User Level Table

create_user_level_table.sql

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;