Distribution of Customers Over Time
We recently received this great question from the Finance team: “I was curious if you might be able to help me figure out our most recent 6 months distribution (# of customers) with Pro, Small, Medium and Enterprise plans.”
We can answer this question with data in the subscription_mrr_values
table. The only tricky part will be categorizing the subscriptions into these categories. First thing’s first, let’s collect the MRR values from the past six months.
We’ll only collect data from Stripe and will assume that the distribution of customers in mobile is similar. In any case, there are far more customers paying via Stripe, so this should give us a good idea of the overall distribution.
select
date(s.date) as report_date
, s.plan_id
, p.simplified_plan_id
, count(distinct s.customer_id) as customers
, sum(s.mrr_amount) as total_mrr
from dbt.stripe_mrr as s
left join dbt.plans as p on s.plan_id = p.id
where s.date >= (current_date - 181)
group by 1, 2, 3
Awesome. Now we need to categorize the plans into these categories: Pro, Small, Medium, Enterprise. Let’s get a sense of all the plan IDs we’re working with here.
# get plan counts
table(subscriptions$plan_id)
##
## 40TeamMembers 50team
## 182 182
## 60TeamMembers_v2 70TeamMemberv1
## 64 127
## 75 TM v1 Monthly 80TeamMembersYearly
## 17 96
## 85 Team Member Custom v1 agency-yearly
## 165 182
## analyze-early-access-10-profiles analyze-early-access-25-profiles
## 86 85
## analyze-early-access-50-profiles business
## 71 182
## business_v2_agency_monthly business_v2_agency_yearly
## 182 182
## business_v2_business_monthly business_v2_business_yearly
## 182 182
## business_v2_small_monthly business_v2_small_yearly
## 182 182
## business-simple business-yearly
## 182 182
## Ent300Prof Enterprise 100 users
## 182 182
## Enterprise 175 monthly Enterprise 200 Yearly2
## 59 182
## Enterprise 30 Members V2 Annual Enterprise 35 Team Members
## 182 182
## Enterprise 400 + 60 Users v2 Enterprise 400 v2 monthly
## 118 182
## Enterprise 50 team members Enterprise 50 team members v1
## 39 59
## Enterprise 500 v2 monthly Enterprise 60 team members v1
## 182 31
## Enterprise 900 social accounts Enterprise-2
## 29 182
## enterprise-500 Enterprise100TeamMembers
## 20 169
## Enterprise150-40team Enterprise200v2
## 182 182
## Enterprise250 enterprise250+50
## 90 169
## enterprise30 enterprise300
## 182 182
## Enterprise300_v2 enterprise30v2
## 129 182
## Enterprise400 enterprise500yearly
## 182 177
## Enterprise90v1 lite-29
## 85 182
## plus-149 plus-349
## 182 182
## premium-annual pro_v1_monthly
## 181 55
## pro_v1_yearly pro-annual
## 55 182
## pro-monthly Respond-12-12
## 182 180
## respond-business-base-monthly respond-channel-monthly
## 182 182
## respond-grow-monthly Respond-Medium-v2
## 182 182
## respond-medium-yearly respond-pro-annual-v1
## 182 182
## respond-pro-base-monthly respond-pro-base-yearly
## 182 182
## respond-pro-monthly Respond-Small-Team-v2
## 182 182
## Respond-Small-Team-v3 respond-standard-v2-yearly
## 182 23
## Respond-starter-annual-1 respond-starter-monthly
## 182 182
## respond-starter-monthly-v2 respond-user-monthly
## 182 182
## respond-user-yearly small_business
## 182 182
## small_business_yearly small-149
## 182 182
## small-29 solo-9
## 182 182
## studio-29 studio-59
## 182 182
Wow, there are a lot. Let’s start with low hanging fruit, Awesome/Pro, Reply, and Analyze plans.
# set new plan field
subscriptions <- subscriptions %>%
mutate(plan_type = ifelse(simplified_plan_id == 'reply', 'Reply',
ifelse(simplified_plan_id == 'awesome', 'Awesome/Pro',
ifelse(simplified_plan_id == 'analyze', 'Analyze', NA))))
Now let’s see how many we’ve categorized.
table(subscriptions$plan_type)
##
## Analyze Awesome/Pro Reply
## 242 474 4571
Ok, now let’s take care of Small Business.
# set small business
subscriptions <- subscriptions %>%
mutate(plan_type = ifelse(is.na(plan_type) & grepl('small', plan_id), 'Small Business', plan_type))
Great. Let’s view the plan IDs we still need to categorize.
# count uncategorized plan IDs
subscriptions %>%
filter(is.na(plan_type)) %>%
count(plan_id) %>%
arrange(desc(n))
## # A tibble: 43 x 2
## plan_id n
## <chr> <int>
## 1 40TeamMembers 182
## 2 50team 182
## 3 agency-yearly 182
## 4 business 182
## 5 business_v2_agency_monthly 182
## 6 business_v2_agency_yearly 182
## 7 business_v2_business_monthly 182
## 8 business_v2_business_yearly 182
## 9 business-simple 182
## 10 business-yearly 182
## # ... with 33 more rows
Great. Let’s take care of medium business. The rest will be enterprise plans!
# list medium business plan names
med_plans <- c('business', 'business-yearly', 'business_v2_business_monthly',
'business_v2_business_yearly')
# categorize medium business and enterprise plans
subscriptions <- subscriptions %>%
mutate(plan_type = ifelse(is.na(plan_type) & plan_id %in% med_plans, 'Medium Business',
ifelse(is.na(plan_type) & !(plan_id %in% med_plans), 'Enterprise', plan_type)))
Let’s view the categorized plans now.
table(subscriptions$plan_type)
##
## Analyze Awesome/Pro Enterprise Medium Business
## 242 474 5464 728
## Reply Small Business
## 4571 728
Perfect!
Visualizing the Distribution of Customers over Time
Let’s start by creating a stacked area chart that shows the distribution of all customers over time.
## Warning in as.POSIXlt.POSIXct(x): unknown timezone 'zone/tz/2018c.1.0/
## zoneinfo/America/Kentucky/Louisville'
It’s difficult to see changes over time here, so let’s plot each plan type individually.
We can see that there hasn’t been much change in the past six months. Awesome/Pro subscriptions make up around 89% of the customer base, Small Business subscriptions make up around 9.5% of the customer base, Medium Business makes up 0.9%, Enterprise makes up 0.3%, Reply 0.2%, and analyze a negligible amount.