Buffer has had many plans over the years. There are almost 250 plans that users can be on. In this analysis we’ll try to find out how many active and inactive users are on each plan.
We’ll gather users’ production plans, their active Stripe plan id, their simplified Stripe plan id, and the date of their last update with the SQL query below.
select
u.id as user_id
, u.billing_plan_name as billing_plan
, s.plan_id
, s.simplified_plan_id
, up.number_of_updates_in_last_30days as updates_last_30_days
from dbt.users as u
inner join dbt.stripe_subscriptions as s
on u.billing_stripe_customer_id = s.customer_id
and s.status = 'active'
and s.successful_charges >= 1
left join dbt.user_update_facts as up
on u.id = up.user_id
group by 1, 2, 3, 4, 5We have around 72 thousand paying customers. We’ll assume that all customers with the “individual” plan on production are on the new-ish Free plan. Now we have to categorize the rest of the plans.
# define enterprise plans
ent_plans <- c("enterprise", "enterprise150", "enterprise200", "enterprise300",
"enterprise400", "enterprise500")
# categorize plans
users <- users %>%
filter(billing_plan != "individual") %>%
mutate(final_plan = ifelse(billing_plan %in% ent_plans, "enterprise",
ifelse(billing_plan == "awesome" | billing_plan == "new_awesome", "legacy_awesome",
ifelse(billing_plan == "pro", "pro",
ifelse((billing_plan == "small" | billing_plan == "business" | billing_plan == "agency") &
grepl("v2", plan_id), "new_business",
ifelse((billing_plan == "small" | billing_plan == "business" | billing_plan == "agency") &
!grepl("v2", plan_id), "legacy_business", "other"))))))Let’s count all of the users on each plan.
# count users on each plan
users %>%
group_by(final_plan) %>%
summarise(users = n_distinct(user_id)) %>%
arrange(desc(users)) %>%
kable()## `summarise()` ungrouping output (override with `.groups` argument)| final_plan | users |
|---|---|
| legacy_awesome | 39751 |
| pro | 22723 |
| new_business | 6923 |
| legacy_business | 1891 |
| enterprise | 38 |