Skip to content

Instantly share code, notes, and snippets.

@jonhilgart22
Last active October 11, 2018 17:26
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jonhilgart22/3c451b6ad9969170f01b1384f5006a72 to your computer and use it in GitHub Desktop.
Save jonhilgart22/3c451b6ad9969170f01b1384f5006a72 to your computer and use it in GitHub Desktop.
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