2 min read

User Counts by Plan

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, 5

We 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()
final_plan users
legacy_awesome 39751
pro 22723
new_business 6923
legacy_business 1891
enterprise 38

Cool. Now let’s determine if a user is active or inactive.

# determine if active
users %>% 
  mutate(type = ifelse(!is.na(updates_last_30_days) & updates_last_30_days >= 1, "active", "inactive")) %>%
  group_by(final_plan, type) %>% 
  summarise(users = n_distinct(user_id)) %>% 
  spread(type, users) %>% 
  arrange(desc(active)) %>% 
  kable()
final_plan active inactive
legacy_awesome 23341 16410
pro 14524 8199
new_business 5590 1333
legacy_business 1549 342
enterprise 36 2

There you have it. I think.