How many users will be affected by the Free plan limits?

Posted on June 30, 2017

Introduction

Over the past few weeks, simplifying the free plan has become a theme and key component of realizing our product vision. This Paper has a lot of good context and discussion around what that involves.

There are four major components to this change:

  • Reducing the profile limit to 2
  • Reducing the number of updates people can send to 5 per day per profile
  • Removing the ‘Share Now’ option
  • Removing custom scheduling

The goal of this analysis is to estimate how many active users would be affected by each component. :)

Methodology

I believe we can gather the user_id’s of users affected by each change with a bit of SQL and Looker magic. Once we have the IDs of users affected by each change, we can join the lists into a single list and count the number of changes that would affect each. Then we will be able to calculate some summary statistics to see how many would be affected by only 1 change, 2 changes, 3 changes, or each change.

Data collection

Let’s start by gathering the user_id’s of active Individual users that have more than 2 profiles connected. Active is defined as having sent at least one update through Buffer in the past 30 days.

with user_facts as (
  select
    up.user_id
    , max(up.created_at) as last_update_created_at
    , count(distinct p.profile_id) as number_of_profiles
  from transformed_updates as up
  inner join users
    on users.user_id = up.user_id
  left join profiles as p
    on p.user_id = users.user_id
  where up.status != 'service'
  and users.billing_plan = 'individual'
  and p.is_disabled = false
  and p.is_deleted = false
  group by up.user_id
)
select
  user_id
  , last_update_created_at
  , number_of_profiles
from user_facts
where last_update_created_at >= (current_date - 30)
and number_of_profiles > 2

Now let’s gather the users that have shared over 5 updates in a single day to a single profile in the past 90 days. We’ll start by collecting the user_id, profile_id, and number of updates sent for each day in the past 90 days for users on the Individual plan with the SQL query below.

select
  up.user_id
  , up.profile_id
  , date(up.sent_at) as update_date
  , count(distinct up.id) as update_count
from transformed_updates as up
inner join users
  on users.user_id = up.user_id
where up.status != 'service'
and date(up.sent_at) > (current_date - 90)
and users.billing_plan = 'individual'
group by 1, 2, 3

We just need to do a bit of transformation to find the users that meet our criteria.

# Add indicator if user sent 5 or more updates in a single day
users_five_updates <- users_five_updates %>%
  mutate(sent_5_updates = ifelse(update_count >= 5, 1, 0),
         over_limit = ifelse(update_count > 5, 1, 0))

# Group by user
users_five_updates <- users_five_updates %>%
  group_by(user_id) %>%
  summarise(days = n_distinct(update_date),
            total_updates = sum(update_count),
            days_with_5_updates = sum(sent_5_updates),
            days_over_limit = sum(over_limit)) %>%
  mutate(hit_limit = (days_with_5_updates >= 1),
         over_limit = (days_over_limit >= 1)) %>%
  filter(over_limit == TRUE)

Cool! Now let’s find the number of free users that have used the share now option in the past 30 days.

select
  up.user_id
  , count(distinct up.id) as update_count
from transformed_updates as up
inner join users
  on users.user_id = up.user_id
where up.status != 'service'
and date(up.sent_at) > (current_date - 30)
and users.billing_plan = 'individual'
and up.was_shared_immediately = TRUE
group by up.user_id

Great! Now let’s find the users that have used a custom schedule feature in the past 30 days.

select
  a.user_id
  , count(distinct a.id) as count
from actions_taken as a
inner join users
  on users.user_id = a.user_id
where date(a.date) > (current_date - 30)
and users.billing_plan = 'individual'
and (a.full_scope = 'dashboard updates shared composer schedule'
  or a.full_scope = 'extension composer multiple-composers updates shared schedule'
  or a.full_scope = 'dashboard updates shared composer schedule pinned'
  or a.full_scope = 'dashboard queue changed_update change_custom_scheduled_time'
  or a.full_scope = 'dashboard updates shared composer schedule rebuffer'
  or a.full_scope = 'dashboard updates shared composer schedule groups pinned'
  )
group by a.user_id

Now we have all of the active users that would be directly affected by these changes. Now we need to join these four separate data frames and count the number of items they will be affected by.

Data tidying

We need to add indicators to each dataframe to show what change the users are affected by. We can also drop columns that are no longer needed.

# Drop columns
users_two_profiles <- users_two_profiles %>% select(user_id)
users_five_updates <- users_five_updates %>% select(user_id)
users_shared_now <- users_shared_now %>% select(user_id)
users_custom_schedule <- users_custom_schedule %>% select(user_id)

# Add indicators
users_two_profiles$profile_limit <- TRUE
users_shared_now$share_now <- TRUE
users_five_updates$update_limit <- TRUE
users_custom_schedule$custom_schedule <- TRUE

Now let’s join all of our dataframes into one. :)

# Join tables
users <- users_two_profiles %>%
  full_join(users_five_updates, by = 'user_id') %>%
  full_join(users_shared_now, by = 'user_id') %>%
  full_join(users_custom_schedule, by = 'user_id')

There are over 170 thousand users in total. This is more than half of the active Free user population!

Now we’re ready for some summary stats.

Data visualization

Let’s visualize the distribution of the number of items each user is affected by.

Alright, around 94K users would only be affected by one of the changes. This is about 54% of the population. Around 55K users would be affected by two changes (32%), around 21K users would be affected by 3 changes (12%), and around 2 thousand users would be affected by all 4 changes.

But which change accounts for the most users affected? Let’s start by only looking at those users only affected by a single change.

# Filter users
single <- users %>%
  filter(changes_affecting == 1)

There are 94 thousand of these users. Let’s count how many would be affected by the 2 profile limit.

single %>%
  filter(profile_limit == TRUE) %>%
  count
## # A tibble: 1 x 1
##       n
##   <int>
## 1 32404

Around 32 thousand users. This is around 34% of the users. Let’s count users affected by the update limit.

single %>%
  filter(update_limit == TRUE) %>%
  count
## # A tibble: 1 x 1
##       n
##   <int>
## 1 11733

Around 12 thousand users would be affected by the update limit. This is around 12% of users. How about the Share Now feature?

single %>%
  filter(share_now == TRUE) %>%
  count
## # A tibble: 1 x 1
##       n
##   <int>
## 1 26537

Around 26 thousand users (around 28%). How about the custom schedule?

single %>%
  filter(custom_schedule == TRUE) %>%
  count
## # A tibble: 1 x 1
##       n
##   <int>
## 1 23342

Around 23 thousand users, or 25%.

Conclusions

More than half of the active user population on the free plan would be affected by at least one of these proposed changes. Around 94K users would only be affected by one of the changes, which represents about 54% of the population. Around 55K users would be affected by two changes (32%), around 21K users would be affected by 3 changes (12%), and around 2 thousand users would be affected by all 4 changes.

These are large numbers. Experimentation and communication feel very essential.