Calculate Summary Metrics From User Level Table

summary_metrics.sql

select
    cohort,
    count(user_id) as cnt_users,
    sum(cnt_sundae_orders) as total_sundae_orders,
    sum(cnt_orders) as total_orders,
    sum(total_order_value) as total_order_value,
    sum(is_user_converted::int) as cnt_converted_users,
    total_sundae_orders / cnt_users as avg_sundae_orders,
    total_orders / cnt_users as avg_orders,
    total_order_value / total_orders as avg_order_value, 
    cnt_converted_users / cnt_users as conversion_rate
from experiments.sundae_wizard_users
group by 1
order by 1  -- This keeps cohorts in the same order every time you run this code
;

╔═════════╦═══════════╦═════════════════════╦══════════════╦═══════════════════╦═════════════════════╦═══════════════════╦════════════╦═════════════════╦═════════════════╗
║ cohort  ║ cnt_users ║ total_sundae_orders ║ total_orders ║ total_order_value ║ cnt_converted_users ║ avg_sundae_orders ║ avg_orders ║ avg_order_value ║ conversion_rate ║
╠═════════╬═══════════╬═════════════════════╬══════════════╬═══════════════════╬═════════════════════╬═══════════════════╬════════════╬═════════════════╬═════════════════╣
║ control ║     4,707 ║               3,671 ║        4,848 ║            42,616 ║               4,566 ║              0.78 ║       1.03 ║            8.79 ║            0.97 ║
╠═════════╬═══════════╬═════════════════════╬══════════════╬═══════════════════╬═════════════════════╬═══════════════════╬════════════╬═════════════════╬═════════════════╣
║ test    ║     4,681 ║               3,885 ║        4,868 ║            43,863 ║               4,353 ║              0.83 ║       1.04 ║            9.01 ║            0.93 ║
╚═════════╩═══════════╩═════════════════════╩══════════════╩═══════════════════╩═════════════════════╩═══════════════════╩════════════╩═════════════════╩═════════════════╝