Assessing Revenue Risk Due to Facebook API Changes

In this analysis we’ll explore the number of paying customers that have connected Facebook Profiles, Pages, and Groups to assess the potential impact of Facebook’s recently-announced API changes.

We will gather all paying customers below, count the number of profiles, groups, and pages they have, and calculate the sum of their MRR values for the most recent month. We will also examine the number of updates that customers have created for each type of profile.

Data Collection

We’ll query Redshift directly to get the data we need.

select
  u.id as user_id
  , u.name
  , u.email
  , u.billing_plan_name as plan
  , u.billing_stripe_customer_id as customer_id
  , mrr.subscription_id
  , mrr.plan_id
  , mrr.mrr_amount
  , count(distinct case when p.service = 'facebook' 
                        and p.service_type = 'profile' 
                        and p.is_deleted = false 
                        and p.is_disabled = false then p.id else null end) as facebook_profiles
  , count(distinct case when p.service = 'facebook' 
                        and p.service_type = 'group' 
                        and p.is_deleted = false 
                        and p.is_disabled = false then p.id else null end) as facebook_groups
  , count(distinct case when p.service = 'facebook' 
                        and p.service_type = 'page' 
                        and p.is_deleted = false 
                        and p.is_disabled = false then p.id else null end) as facebook_pages
  , count(distinct case when p.service != 'facebook' 
                        and p.is_deleted = false 
                        and p.is_disabled = false then p.id else null end) as other_profiles
from dbt.users as u
left join dbt.profiles as p on p.user_id = u.id
left join dbt.stripe_mrr as mrr on mrr.customer_id = u.billing_stripe_customer_id
where u.billing_plan_name != 'individual'
and mrr.date >= (current_date - 3)
group by 1, 2, 3, 4, 5, 6, 7, 8

There are around 73 thousand paying Stripe customers in this dataset. We’re now ready to answer a few key questions.

How many paid customers have only FB profiles?

By “exclusive” I assume that we want to know how many customers have Profiles and no other profile types. Let’s count how many users there are that fit this criteria.

# find users that exclusively have Profiles or Groups
users %>% 
  mutate(profiles_only = facebook_profiles >= 1 &
           other_profiles == 0 & facebook_pages == 0 & facebook_groups == 0) %>% 
  group_by(profiles_only) %>% 
  summarise(users = n_distinct(user_id), total_mrr = sum(mrr_amount, na.rm = T)) %>%
  mutate(percent = users / sum(users))

## # A tibble: 2 x 4
##   profiles_only users total_mrr percent
##   <lgl>         <int>     <dbl>   <dbl>
## 1 F             73235   1346584 0.995  
## 2 T               356      4165 0.00484

There are only 356 paying Stripe customers that have only FB profiles connected. They represent around 4 thousand dollars of MRR. There are only five Business customers that represent $771/mo in this segment.

How many paid customers have at least one Facebook Profile or Group?

This one is pretty straightforward.

# find users that  have Profiles or Groups
users %>% 
  mutate(has_profile = facebook_profiles >= 1) %>% 
  group_by(has_profile) %>% 
  summarise(users = n_distinct(user_id), total_mrr = sum(mrr_amount, na.rm = T)) %>%
  mutate(percent = users / sum(users))

## # A tibble: 2 x 4
##   has_profile users total_mrr percent
##   <lgl>       <int>     <dbl>   <dbl>
## 1 F           52495   1016615   0.713
## 2 T           21096    334134   0.287

There are around 21 thousand paying Stripe customers with at least one Facebook Profile. They represent around $334K in MRR. There are 1438 Business customers in this segment.

How many paid customers meaningfully use FB Profiles and/or Groups?

We’ll have to answer these types of questions by querying the updates table to see how many updates have been created for Facebook profiles by our paying customers. We’ll look at a few different scenarios. First we’ll count the number of updates sent to each type of profile in the past 60 days.

select
  u.id as user_id
  , count(distinct case when p.service = 'facebook' 
                        and p.service_type = 'profile' 
                        and p.is_deleted = false 
                        and p.is_disabled = false then up.id else null end) as facebook_profile_updates
  , count(distinct case when p.service = 'facebook' 
                        and p.service_type = 'group' 
                        and p.is_deleted = false 
                        and p.is_disabled = false then up.id else null end) as facebook_group_updates
  , count(distinct case when p.service = 'facebook' 
                        and p.service_type = 'page' 
                        and p.is_deleted = false 
                        and p.is_disabled = false then up.id else null end) as facebook_page_updates
  , count(distinct case when p.service != 'facebook' 
                        and p.is_deleted = false 
                        and p.is_disabled = false then up.id else null end) as other_updates
from dbt.users as u
left join dbt.profiles as p on p.user_id = u.id
left join dbt.updates as up
  on up.profile_id = p.id
  and up.was_sent_with_buffer
  and up.created_at > (current_date - 30)
where u.billing_plan_name != 'individual'
group by 1

Now we have the update counts for all paying Stripe customers for each type of profile.

How many paid customers are active on at least one Facebook profile?

We’ll count the number of paying customers that have created at least one post for a Facebook Profile in the past 60 days.

# count users with at least one post to a profile or group
updates %>% 
  mutate(use_profiles = !is.na(facebook_profile_updates) & facebook_profile_updates >= 1) %>% 
  group_by(use_profiles) %>% 
  summarise(users = n_distinct(user_id), total_mrr = sum(mrr_amount, na.rm = T)) %>%
  mutate(percent = users / sum(users))

## # A tibble: 2 x 4
##   use_profiles users total_mrr percent
##   <lgl>        <int>     <dbl>   <dbl>
## 1 F            64076   1201939   0.871
## 2 T             9515    148810   0.129

Around 9.5 thousand paying customers have created a post for a Facebook profile in the past 60 days. They represent around $149K in MRR. There are 585 Business customers in this segment that represent around $51K in MRR.

Percentage of Updates

How many paying users send 10%, 25%, 50%, 75% and 100% of their updates to Facebook groups and profiles in the past 60 days? To answer this question we’ll need to calculate the percentage of updates sent to Facebook profiles and groups.

# calculate total number of updates
user_updates <- updates %>% 
  mutate(total_updates = facebook_profile_updates + facebook_group_updates + 
           facebook_page_updates + other_updates) %>% 
  mutate(percent_to_profiles = facebook_profile_updates / total_updates)

# select buckets
cuts <- c(-Inf, 0, 0.1, 0.25, 0.50, 0.75, 0.99, 1)

# bucket profile and group counts
user_updates <- user_updates %>% 
  mutate(percent_bucket = cut(percent_to_profiles, breaks = cuts, 
                              include.highest = TRUE, right = TRUE))

# count number of users
user_updates %>% 
  group_by(plan_type, percent_bucket) %>% 
  summarise(users = n_distinct(user_id), mrr = sum(mrr_amount, ignore.na = TRUE))

## # A tibble: 16 x 4
## # Groups:   plan_type [?]
##    plan_type percent_bucket users    mrr
##    <chr>     <fct>          <int>  <dbl>
##  1 awesome   (-Inf,0]       39832 468995
##  2 awesome   (0,0.1]         3066  38155
##  3 awesome   (0.1,0.25]      3475  35621
##  4 awesome   (0.25,0.5]      1626  15970
##  5 awesome   (0.5,0.75]       245   2402
##  6 awesome   (0.75,0.99]      136   1311
##  7 awesome   (0.99,1]         382   3733
##  8 awesome   <NA>           17578 167575
##  9 business  (-Inf,0]        5765 498565
## 10 business  (0,0.1]          377  35341
## 11 business  (0.1,0.25]       144  10875
## 12 business  (0.25,0.5]        43   3641
## 13 business  (0.5,0.75]        10    609
## 14 business  (0.75,0.99]        5    341
## 15 business  (0.99,1]           6    823
## 16 business  <NA>             901  66807

Effect on Monthly Active Users

To get an idea on how our monthly active user count might be affect, we can do a similar analysis to the one above and count how mnay of our MAUs created X% of their updates in the past month for FB profiles. Let’s begin by collecting the data we need.

select
  u.id as user_id
  , count(distinct case when p.service = 'facebook' 
                        and p.service_type = 'profile' 
                        and p.is_deleted = false 
                        and p.is_disabled = false then up.id else null end) as facebook_profile_updates
  , count(distinct case when p.service = 'facebook' 
                        and p.service_type = 'group' 
                        and p.is_deleted = false 
                        and p.is_disabled = false then up.id else null end) as facebook_group_updates
  , count(distinct case when p.service = 'facebook' 
                        and p.service_type = 'page' 
                        and p.is_deleted = false 
                        and p.is_disabled = false then up.id else null end) as facebook_page_updates
  , count(distinct case when p.service != 'facebook' 
                        and p.is_deleted = false 
                        and p.is_disabled = false then up.id else null end) as other_updates
from dbt.users as u
left join dbt.profiles as p on p.user_id = u.id
inner join dbt.updates as up
  on up.profile_id = p.id
  and up.was_sent_with_buffer
  and up.created_at >= (current_date - 31)
group by 1

Awesome, we have around 290 thousand MAUs to analyze. We’ll use the same method that we used previously to calculate the percentage of updates that were sent to Facebook profiles.

# calculate total number of updates
mau_updates <- mau_updates %>% 
  mutate(total_updates = facebook_profile_updates + facebook_group_updates + 
           facebook_page_updates + other_updates) %>% 
  filter(total_updates > 0) %>% 
  mutate(percent_to_profiles = facebook_profile_updates / total_updates)

# select buckets
cuts <- c(-Inf, 0, 0.1, 0.25, 0.50, 0.75, 0.99, 1)

# bucket profile and group counts
mau_updates <- mau_updates %>% 
  mutate(percent_bucket = cut(percent_to_profiles, breaks = cuts, 
                              include.highest = TRUE, right = TRUE))

# count number of users
mau_updates %>% 
  group_by(percent_bucket) %>% 
  summarise(users = n_distinct(user_id)) %>% 
  mutate(percent = round(users / sum(users) * 100, 3))

## # A tibble: 7 x 3
##   percent_bucket  users percent
##   <fct>           <int>   <dbl>
## 1 (-Inf,0]       227579  77.8  
## 2 (0,0.1]          4076   1.39 
## 3 (0.1,0.25]       6132   2.10 
## 4 (0.25,0.5]      14870   5.09 
## 5 (0.5,0.75]       2874   0.983
## 6 (0.75,0.99]      1354   0.463
## 7 (0.99,1]        35463  12.1

Great!

It may be worth noting that MAUs could have only sent one single update, and that could have been created for a Facebook profile.

Of Recent Upgrades, How Many Used FB Profiles?

First let’s grab recently converted users.

select
  s.id
  , date(s.first_paid_invoice_created_at) as first_invoice_date
  , s.plan_id
  , s.successful_charges
  , u.id as user_id
  , date(u.created_at) as signup_date
  , count(distinct case when p.service = 'facebook' and p.service_type = 'profile' 
            then up.id else null end) as fb_profile_updates
from dbt.stripe_subscriptions as s
inner join dbt.users as u on u.billing_stripe_customer_id = s.customer_id
left join dbt.profiles as p on p.user_id = u.id
left join dbt.updates as up on up.profile_id = p.id
where s.first_paid_invoice_created_at >= (current_date - 60) 
and (up.was_sent_with_buffer or up.was_sent_with_buffer is null)
and (up.created_at < s.first_paid_invoice_created_at or up.created_at is null)
group by 1, 2, 3, 4, 5, 6

Now, let’s see how many of these recent 2040 upgrades created updates for FB profiles.

upgrades %>% 
  group_by(fb_profile_updates > 0) %>% 
  summarise(users = n_distinct(user_id)) %>% 
  mutate(percent = users / sum(users))

## # A tibble: 2 x 3
##   `fb_profile_updates > 0` users percent
##   <lgl>                    <int>   <dbl>
## 1 F                        13313   0.873
## 2 T                         1942   0.127

Around 13% of users that upgraded in the past two months created at least one post for a FB profile.