Last active
October 11, 2018 17:09
-
-
Save jonhilgart22/865e11cd33c2e491aa4f772c66ec529d 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
--ltv for users to call | |
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, | |
--find billable minutes for week one | |
coalesce(sum( case when subscription_week = 1 then billable_minutes else null end), 0) as billable_minutes_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 | |
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, | |
round(total_minutes / rev_cum.cumulative_percent_of_year_one_expected_revenue) as total_expected_year_one_billable_minutes | |
from weekly_minutes w | |
-- get week one's expected percent of year one revenue | |
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 = 'all' | |
and rev_cum.active_days_week_one_string = w.days_active_week_one | |
) | |
select | |
* | |
from revenue_summary | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment