Last active
October 11, 2018 17:26
-
-
Save jonhilgart22/3c451b6ad9969170f01b1384f5006a72 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
with most_recent_subscription as ( | |
select | |
user_id, | |
max(created_at) as created_at | |
from subscriptions s | |
group by 1 | |
), | |
weekly_minutes as ( | |
select | |
date_trunc('month', subscription_started_at) as sub_start_month, | |
a.user_id, | |
a.summarized_reporting_cleaned_source, | |
case when a.is_qualified is True then 'yes' else 'no' end as is_qualified_flag, | |
active_days_week_one || '-days' as days_active_week_one, | |
-- we round the subscription week so that at most we are +/- three days out of sync | |
max(round(f_time_interval('week', subscription_started_at, getdate()))) as most_recent_subscription_week, | |
-- find the total billable minutesper user | |
coalesce(sum(billable_minutes), 0) as total_minutes, | |
sum(case when lock_date >= getdate() - '30 days'::interval then billable_minutes else null end) as last_month_demand | |
from user_stats_by_subscription_day u | |
join analytics.acquisition_credit_card_source_tracking a | |
on a.user_id = u.user_id | |
join most_recent_subscription m | |
on m.user_id = u.user_id | |
and m.created_at = u.subscription_started_at | |
where | |
subscription_started_at + '1 week'::interval <= getdate() -- at least a week old | |
group by 1,2,3,4,5 | |
), | |
revenue_summary as ( | |
select | |
w.user_id, | |
w.total_minutes, | |
w.last_month_demand, | |
round(total_minutes / rev_cum.cumulative_percent_of_year_one_expected_revenue) as total_expected_year_one_billable_minutes | |
from weekly_minutes w | |
-- Calculte the expected percent of year one revenue based upon cumulative revenue curves | |
-- Calculation: Total revenue observed / cumulative percent of year one revenue expected for this user cohort | |
join analytics.weekly_revenue_stats_by_tenure_week rev_cum | |
on rev_cum.payment_tenure_week = w.most_recent_subscription_week | |
-- cleaned source can be all or the acquisition source | |
and rev_cum.cleaned_source = w.summarized_reporting_cleaned_source | |
--week_one_qualified_bucket can be yes, no, or all | |
and rev_cum.week_one_qualified_bucket = w.is_qualified_flag | |
and rev_cum.active_days_week_one_string = w.days_active_week_one | |
) | |
select | |
user_id | |
from revenue_summary | |
where | |
-- This select ~10% of users & ~74% of demand over the past month | |
-- Agent > 5 weeks account for ~65% of supply as of 10/10/18 | |
total_expected_year_one_billable_minutes > 600 | |
or total_minutes > 600 | |
group by 1 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment