How Pro Users Use the Calendar
2018-09-10
In this analysis we’ll examine how Pro/Awesome customers use the Calendar feature. We’ll look at how many Pro customers utlized the feature at any point in their first month on the plan, as well as how frequently they used it. We’ll then explore how usage correlates with key metrics like retention.
Data Collection
We’ll collect all Awesome/Pro customers that started subscriptions from January 1, 2017 to July 1, 2018 and count the number of calendar-specific actions they took in the first 30 days on the plan. We’ll collect the data by running the following SQL query.
The data includes the customer’s subscription ID, plan ID, the date that the subscription began, the date it was canceled (if any), the user’s signup date, the number of calendar-specific actions, the number of days in which calendar-specific actions were taken, and the number of weeks in which calendar-specific actions were taken.
select
s.id as subscription_id
, s.plan_id
, date(s.created_at) as subscription_start
, date(s.ended_at) as subscription_end
, s.customer_id
, u.id as user_id
, u.billing_plan_name as billing_plan
, date(u.created_at) as signup_date
, count(distinct at.id) as cal_actions
, count(distinct date(at.created_at)) as days_used
, count(distinct date_trunc('week', at.created_at)) weeks_used
from dbt.stripe_subscriptions as s
inner join dbt.users as u
on u.billing_stripe_customer_id = s.customer_id
left join dbt.actions_taken as at
on at.user_id = u.id
and at.created_at >= s.created_at
and at.created_at <= dateadd(day, 30, s.created_at)
and at.full_scope in ('dashboard calendar update edited',
'dashboard calendar week clicked add_post',
'dashboard calendar update scheduled_time changed',
'dashboard calendar update deleted',
'dashboard calendar update shared_now')
where s.created_at >= '2017-01-01'
and (s.ended_at is null or s.ended_at <= '2018-07-01')
and s.successful_charges >= 1
group by 1, 2, 3, 4, 5, 6, 7, 8
There are around 84K users to work with. The subscriptions could have started anywhere from January 1, 2017 to July 1, 2018. Now that we’ve collected the data, we’re ready to do some exploratory analysis.
Exploratory Analysis
The first question we’ll try to answer is “what percentage of Pro users utilize the Calendar feature?”. To do this, we’ll need to filter out all non-pro subscriptions. Once we’ve done that, we can plot the percentage of customers that took at least one calendar-specific action
Around 25% of Awesome/Pro users used the Calendar feature at some point during the first 30 days on the plan. Now let’s plot the distribution of the number of calendar actions taken for users that used the calendar at least once.
We can see clearly that the number of actions customers took is distributed according to a Power Law distribution, with most customers taking very few actions and a few customers taking many actions.
Now let’s plot the distribution of the number of days in which calendar actions were taken.
We see a similar pattern here. Many users that used the calendar only took an action on a single day. We can make a similar plot for the number of weeks in which a calendar action was taken.
There are a decent number of customers that used the Calendar in multiple weeks. One other specific question we’ve gotten seeks to find the percentage of Awesome/Pro customers that used the calendar that took at least 3 calendar actions.
Around 80% of Pro users with at least one calendar action have taken at least 3 actions in the Calendar. Now that we have a better idea of how customers are utilizing the Calendar feature, let’s look at how usage correlates with churn.
Comparing Churn Rates
We’ll classify a user as “churned” if he or she canceled the subscription within 90 days of the start date. Let’s plot the churn rates for users that had at least one calendar-specific action.
Users that have used the Calendar feature have lower churn rates that those who don’t, and the difference is statistically significant. Now let’s plot the churn rates of users that used the calendar a given number of weeks.
We can see that there is a clear correlation between the number of weeks in which Calendar actions were taken and the proportion of customers that churned within 90 days. I don’t think that this is enough to imply causation though - calendar usage may be more of an indicator that a customer is active within the app. General activity may be a confounding factor in this case. Still, it’s good to see that there is a correlation!
Calendar Usage Per User
Another question we received seeks to find out how many times the Calendar was used by each pro user in the past 7 days. None of this code is run and it’s fine to ignore it.
select
u.id as user_id
, date(at.created_at) as rebuffer_date
, (datediff(day, date(at.created_at), current_date) - 7) * -1 as day_number
, u.billing_plan_name as billing_plan
, date(u.created_at) as signup_date
, count(distinct at.id) as actions
from dbt.users as u
inner join dbt.actions_taken as at
on at.user_id = u.id
and at.created_at >= (current_date - 7)
and at.full_scope in ('dashboard calendar update edited',
'dashboard calendar week clicked add_post',
'dashboard calendar update scheduled_time changed',
'dashboard calendar update deleted',
'dashboard calendar update shared_now')
where u.billing_plan_name in ('awesome', 'new_awesome', 'pro')
group by 1, 2, 3, 4, 5
There are only around 600 users in this dataset. We’ll need to clean and tidy the data.
# set as integers
cal_users$day_number <- as.integer(cal_users$day_number)
cal_users$actions <- as.integer(cal_users$actions)
# spread data out wide
spread <- cal_users %>%
group_by(user_id, day_number) %>%
summarise(actions = sum(actions, na.rm = TRUE)) %>%
spread(day_number, actions, fill = 0)
Now we write the csv.
write.csv(spread, file = '~/Downloads/calendar_actions.csv', row.names = FALSE)