sep_mrr.R

September 2017 is set ot be a month of low growth for Buffer. We are expected to increase MRR by around 15K in the month of September, which is over 50% less than what was gained in the month of August. This growth will equate to a monthly percentage of less than 1%, which is lower than what we would expect.

In this exploration, I will try to identify factors leading to the decrease in net MRR gained. We will focus on Stripe MRR here, as it makes up the majority of our total MRR amount. It is worth noting that Apple has also experienced lower-than-expected growth (it has only gained a few hundred dollars in September as of September 25). We would still not expect growth this low from the Stripe gateway.

```{r include = FALSE, warning = FALSE, message = FALSE}
library(dplyr); library(ggplot2); library(tidyr); library(lubridate); library(buffer)
```

### Gather data
We'll use the [new Stripe MRR breakdown script](https://github.com/bufferapp/buffer-analysis/blob/master/deliverables/mrr-breakdown.md) to analyze the components that make up MRR. These components are new MRR, churn MRR, upgrades and downgrades. Once we have the data, we can look at how much MRR has grown in September, and compare that to August and July.

This is the overall net gain in MRR for the months of July, August, and September, _as of September 28_.

```{r include = FALSE}
# get july data
july <- read.csv('~/Documents/stripe-mrr-breakdown/july.csv', header = T, stringsAsFactors = F)

# get august data
august <- read.csv('~/Documents/stripe-mrr-breakdown/august.csv', header = T, stringsAsFactors = F)

# get september data
september <- read.csv('~/Documents/stripe-mrr-breakdown/september.csv', header = T, stringsAsFactors = F)
```

```{r include = FALSE}
# merge two dataframes
events <- rbind(july, august, september)

# set date as date
events$date <- as.Date(events$date, format = '%Y-%m-%d')

# get week
events$week <- floor_date(events$date, unit = "week")

# get month
events$month <- floor_date(events$date, unit = "month")

# remove unneeded objects
rm(july); rm(august); rm(september)
```

```{r}
# find net mrr
events %>% 
  group_by(month) %>%
  summarise(mrr_gain = sum(mrr_amount, na.rm = TRUE))
```

Net MRR has increased by around 14.1K in September, compared to 28K in August and 17.6K in July.

### Net MRR by week
Let's look at the amount MRR has increased each week.

```{r echo = FALSE}
events %>%
  filter(week != max(week) & week != min(week)) %>%
  group_by(week) %>%
  summarise(net_mrr = sum(mrr_amount, na.rm = TRUE)) %>%
  ggplot(aes(x = week, y = net_mrr)) +
  geom_bar(stat = 'identity') +
  theme_minimal() +
  labs(x = NULL, y = NULL, title = "MRR Gained by Week")
```

We can see that there were two weeks of unusually low MRR growth at the beginning of September. Let's look at the MRR components now.

In the third week of September, net MRR seems to have recovered to previous levels. Let's view the MRR components, including new, churn, upgrades, and downgrades, and see how they have changed over time.

```{r echo = FALSE}
events %>%
  filter(week != max(week) & week != min(week) & !is.na(event_type)) %>%
  group_by(week, event_type) %>%
  summarise(mrr = sum(mrr_amount, na.rm = TRUE)) %>%
  ggplot(aes(x = week, y = mrr, color = event_type)) +
  geom_line() + 
  geom_point() +
  theme_minimal() +
  labs(x = NULL, y = NULL, title = "MRR Components", color = "Type")
```

We can see that new MRR has remained fairly constant, but decreased in the beginning of September. In the same time period, churn and downgrades both increased. Overall, it looks like churn has increased consistently in the past couple of months, while new MRR has remained fairly constant around 15K per week.

Let's look at the same chart for `pro-monthly` subscriptions.

```{r echo = FALSE}
events %>%
  filter(week != max(week) & week != min(week) & !is.na(event_type) & 
           plan_id == 'pro-monthly' & event_type != 'downgrade') %>%
  group_by(week, event_type) %>%
  summarise(mrr = sum(mrr_amount, na.rm = TRUE)) %>%
  ggplot(aes(x = week, y = mrr, color = event_type)) +
  geom_line() + 
  geom_point() +
  theme_minimal() +
  labs(x = NULL, y = NULL, title = "MRR Components", color = "Type")
```

If we looked only at net gained and net lost, we would see that both are increasing, but the gap between the two shrunk in early September.

```{r echo = FALSE}
events %>%
  filter(week != max(week) & week != min(week) & !is.na(event_type)) %>%
  mutate(event_type = ifelse(event_type == 'churn' | event_type == 'downgrade', 
                             'net_lost', 'net_gained')) %>%
  mutate(mrr_amount = ifelse(event_type == 'net_lost', mrr_amount * -1, mrr_amount)) %>%
  group_by(week, event_type) %>%
  summarise(mrr = sum(mrr_amount, na.rm = TRUE)) %>%
  ggplot(aes(x = week, y = mrr, color = event_type)) +
  geom_line() + 
  geom_point() +
  theme_minimal() +
  labs(x = NULL, y = NULL, title = "MRR Components", color = "Type")
```

We can see that the gap between `net_gained` and `net_lost` shrunk by quite a bit. We can also look at the movement by day to get more granular details, but that might be less helpful for us.

```{r echo = FALSE}
events %>%
  filter(date != max(date) & date != min(date) & !is.na(event_type)) %>%
  mutate(event_type = ifelse(event_type == 'churn' | event_type == 'downgrade', 
                             'net_lost', 'net_gained')) %>%
  mutate(mrr_amount = ifelse(event_type == 'net_lost', mrr_amount * -1, mrr_amount)) %>%
  group_by(date, event_type) %>%
  summarise(mrr = sum(mrr_amount, na.rm = TRUE)) %>%
  ggplot(aes(x = date, y = mrr, color = event_type)) +
  geom_line() + 
  geom_point() +
  theme_minimal() +
  labs(x = NULL, y = NULL, title = "MRR Components", color = "Type")
```

Alright, that isn't helpful. Let's move on and look at overall net MRR by day instead.

```{r echo = FALSE}
events %>%
  filter(date != max(date)) %>%
  group_by(date) %>%
  summarise(mrr = sum(mrr_amount, na.rm = TRUE)) %>%
  ggplot(aes(x = date, y = mrr)) +
  geom_line() + 
  geom_point() +
  theme_minimal() +
  stat_smooth(method = 'loess') +
  labs(x = NULL, y = NULL, title = "MRR Components", color = "Type")
```

Overall, the trend seems stable, but net MRR decreased in the beginning of September. We should try to segment this to see if we can discover what exactly is causing this dip.

### MRR growth by plan interval
We can get a little more granular and see how MRR has grown for monthly and yearly plans.

```{r echo = FALSE}
# plot net MRR for monthly and yearly plans
events %>%
  filter(week != max(week) & week != min(week) & !is.na(plan_interval)) %>%
  group_by(week, plan_interval) %>%
  summarise(net_mrr = sum(mrr_amount, na.rm = TRUE)) %>%
  ggplot(aes(x = week, y = net_mrr, color = plan_interval)) +
  geom_line() +
  geom_point() +
  theme_minimal() +
  labs(x = NULL, y = NULL, title = "MRR Gained by Week", color = "Interval")
```

Net MRR has decreased for both monthly and yearly plans in the first couple weeks of September, but the decrease was more pronounced for monthly plans. We can expect more volatility with monthly plans, so this makes sense to me.

We can now look at net MRR by plan type, but we will need to pull in more data.

### MRR by plan
We'll pull in data from the `stripe_subscription_values` table. This table is used to calculate MRR each day.

```{r include = FALSE}
# connect to redshift
con <- redshift_connect()
```

```{sql connection = con, output.var = mrr}
select 
  date(mrr.date) as mrr_date
  , mrr.subscription_id
  , s.customer as customer_id
  , s.plan_id
  , date(s.start) as sub_start_date 
  , u.user_id
  , date(u.created_at) as created_at
  , mrr.mrr_amount
from looker_scratch.LR$MCSE2DGONUY4DUDQAJJVH_stripe_subscription_values as mrr
left join stripe._subscriptions as s
  on mrr.subscription_id = s.id
left join users as u
  on u.billing_stripe_id = s.customer
where date(mrr.date) >= '2017-08-01'
```

We will also need to pull in simplified plan names.

```{r}
# get plans
plans <- readRDS('~/Documents/stripe-mrr-breakdown/plan_names.rds')

# join simplified plan name
mrr <- mrr %>%
  left_join(plans, by = 'plan_id')
```

Now we can plot MRR by plan. This can also be done in Looker. :) 

```{r echo = FALSE}
mrr %>%
  filter(simplified_plan_id == 'awesome' | simplified_plan_id == 'business') %>%
  group_by(mrr_date, simplified_plan_id) %>%
  summarise(mrr = sum(mrr_amount)) %>%
  ggplot(aes(x = mrr_date, y = mrr, color = simplified_plan_id)) +
  geom_line() +
  theme_minimal() +
  labs(x = NULL, y = NULL, title = "Daily MRR Values", color = NULL)
```

It's quite striking to see the difference in Awesome and Business MRR since August 1. While Business MRR has dipped a bit in recent days, Awesome MRR has been consistently declining since the middle of August.

We can get a little more granular and look at the plan _interval_ as well. Perhaps monthly and yearly plans are growing at different rates.

```{sql connection = con, output.var = stripe_plans}
select * from stripe._plans
```

```{r}
# get plan interval
intervals <- stripe_plans %>%
  select(id, interval)

# join plan interval
mrr <- mrr %>%
  left_join(intervals, by = c("plan_id" = "id"))
```

```{r echo = FALSE, message = FALSE, warning = FALSE}
library(gridExtra)

awesome_monthly <- mrr %>%
  filter(simplified_plan_id == 'awesome' & interval == 'month') %>%
  group_by(mrr_date, simplified_plan_id) %>%
  summarise(mrr = sum(mrr_amount)) %>%
  ggplot(aes(x = mrr_date, y = mrr)) +
  geom_line() +
  theme_minimal() +
  labs(x = NULL, y = NULL, title = "Awesome Monthly")

business_monthly <- mrr %>%
  filter(simplified_plan_id == 'business' & interval == 'month') %>%
  group_by(mrr_date, simplified_plan_id) %>%
  summarise(mrr = sum(mrr_amount)) %>%
  ggplot(aes(x = mrr_date, y = mrr)) +
  geom_line() +
  theme_minimal() +
  labs(x = NULL, y = NULL, title = "Business Monthly")

awesome_yearly <- mrr %>%
  filter(simplified_plan_id == 'awesome' & interval == 'year') %>%
  group_by(mrr_date, simplified_plan_id) %>%
  summarise(mrr = sum(mrr_amount)) %>%
  ggplot(aes(x = mrr_date, y = mrr)) +
  geom_line() +
  theme_minimal() +
  labs(x = NULL, y = NULL, title = "Awesome Yearly")

business_yearly <- mrr %>%
  filter(simplified_plan_id == 'business' & interval == 'year') %>%
  group_by(mrr_date, simplified_plan_id) %>%
  summarise(mrr = sum(mrr_amount)) %>%
  ggplot(aes(x = mrr_date, y = mrr)) +
  geom_line() +
  theme_minimal() +
  labs(x = NULL, y = NULL, title = "Business Yearly")

grid.arrange(awesome_monthly, business_monthly, awesome_yearly, business_yearly, nrow = 2)
```

The decrease in monthly Awesome MRR and the slowdown in monthly Business MRR seem to be the most noticeable characteristics of these plots.

We can take a different approach and look at MRR that comes from new users and compare it to MRR that comes from existing users.

### New vs Existing MRR
We can label users that signed up in the 30 days prior to MRR being recorded as "new".

```{r}
# label new users
mrr <- mrr %>%
  mutate(is_new = (as.numeric(mrr_date - created_at) <= 30))
```

Let's see how much MRR comes from new vs existing customers.

```{r echo = FALSE}
new <- mrr %>%
  filter(is_new) %>%
  group_by(mrr_date) %>%
  summarise(mrr = sum(mrr_amount)) %>%
  ggplot(aes(x = mrr_date, y = mrr)) +
  geom_line() +
  theme_minimal() +
  labs(x = NULL, y = NULL, title = "New MRR")

old <- mrr %>%
  filter(!is_new) %>%
  group_by(mrr_date) %>%
  summarise(mrr = sum(mrr_amount)) %>%
  ggplot(aes(x = mrr_date, y = mrr)) +
  geom_line() +
  theme_minimal() +
  labs(x = NULL, y = NULL, title = "Existing MRR")

grid.arrange(new, old, nrow = 1)
```

It looks like MRR from new customers has been steadily declining, but, if my thinking is correct, we would expect that with a subscription based business. Existing customers should make up a larger and larger percentage of the customer base over time, unless churn is out of control.

MRR from existing customers is much, much greater than MRR from new customers. This has taken a significant hit in September, for whatever reason. Let's focus on existing users for now.

```{r echo = FALSE}
monthly <- mrr %>%
  filter(!is_new & interval == 'month') %>%
  group_by(mrr_date) %>%
  summarise(mrr = sum(mrr_amount)) %>%
  ggplot(aes(x = mrr_date, y = mrr)) +
  geom_line() +
  theme_minimal() +
  labs(x = NULL, y = NULL, title = "Existing Monthly MRR")

yearly <- mrr %>%
  filter(!is_new & interval == 'year') %>%
  group_by(mrr_date) %>%
  summarise(mrr = sum(mrr_amount)) %>%
  ggplot(aes(x = mrr_date, y = mrr)) +
  geom_line() +
  theme_minimal() +
  labs(x = NULL, y = NULL, title = "Existing Yearly MRR")

grid.arrange(monthly, yearly, nrow = 1)
```

Alright, this decrease comes more from existing monthly customers -- let's dig in further and compare Awesome plans to Business plans again.

```{r echo = FALSE}
awesome <- mrr %>%
  filter(is_new == FALSE & interval == 'month' & simplified_plan_id == 'awesome') %>%
  group_by(mrr_date, simplified_plan_id) %>%
  summarise(mrr = sum(mrr_amount)) %>%
  ggplot(aes(x = mrr_date, y = mrr)) +
  geom_line() +
  theme_minimal() +
  labs(x = NULL, y = "MRR", title = "Existing Monthly Awesome MRR")

business <- mrr %>%
  filter(is_new == FALSE & interval == 'month' & simplified_plan_id == 'business') %>%
  group_by(mrr_date, simplified_plan_id) %>%
  summarise(mrr = sum(mrr_amount)) %>%
  ggplot(aes(x = mrr_date, y = mrr)) +
  geom_line() +
  theme_minimal() +
  labs(x = NULL, y = "MRR", title = "Existing Monthly Business MRR")

grid.arrange(awesome, business, nrow = 1)
```

Interesting, it looks like there may have been a steep decrease in monthly Awesome MRR from existing users in recent days.

### Trial lengths 
Buffer for Business trials began as 7-day trials. On **September 23, 2015** we lengthened them to 30 days, which created a period of 23 days in which we had no trial finishing. This led to a decrease in MRR in 2015. 

Business trials shortened to from 30 days to 14 days on **September 9, 2016**. This had the effect of creating 16 days in which both 30-day and 14-day trials were converting at the same time. 

Could these changes to trial lengths have had an impact on MRR in 2017? We know that most trials are started shortly after users signed up -- we can use that fact to identify users that may have been affected by those changes.

One thing we can do is look at MRR from subscriptions that are 1, 2, 3, or more years old.

```{r}
# calculate year of subscriptions
mrr <- mrr %>%
  mutate(age_in_years = floor(as.numeric(mrr_date - sub_start_date) / 365)) %>%
  mutate(age_in_years = ifelse(age_in_years < 0, 0, age_in_years))
```

```{r echo = FALSE}
mrr %>%
  group_by(mrr_date, age_in_years) %>%
  summarise(mrr = sum(mrr_amount)) %>%
  ggplot(aes(x = mrr_date, y = mrr, color = as.factor(age_in_years))) +
  geom_line() +
  theme_minimal() +
  labs(x = NULL, y = NULL, title = "Daily MRR Values", color = "Age in Years")
```

It's hard to see changes here. Let's look at subscriptions that are less than 1 year old.

```{r echo = FALSE}
mrr %>%
  filter(age_in_years == 0 & (simplified_plan_id == 'awesome' | simplified_plan_id == 'business')) %>%
  group_by(mrr_date, simplified_plan_id) %>%
  summarise(mrr = sum(mrr_amount)) %>%
  ggplot(aes(x = mrr_date, y = mrr, color = simplified_plan_id)) +
  geom_line() +
  theme_minimal() +
  labs(x = NULL, y = NULL, title = "Daily MRR Values", color = "Age in Years")
```

Now let's look at subscriptions that are 1 to 2 years old.

```{r}
one_year <- mrr %>%
  filter(age_in_years == 1) %>%
  group_by(mrr_date) %>%
  summarise(mrr = sum(mrr_amount)) %>%
  ggplot(aes(x = mrr_date, y = mrr)) +
  geom_line() +
  theme_minimal() +
  labs(x = NULL, y = NULL, title = "One Year Old Subscriptions")

two_years <- mrr %>%
  filter(age_in_years == 2) %>%
  group_by(mrr_date) %>%
  summarise(mrr = sum(mrr_amount)) %>%
  ggplot(aes(x = mrr_date, y = mrr)) +
  geom_line() +
  theme_minimal() +
  labs(x = NULL, y = NULL, title = "Two Year Old Subscriptions")

grid.arrange(one_year, two_years, nrow = 1)
```

It looks like MRR from subscriptions 1 to 2 years old is solid.

```{r include = FALSE}
detach("package:lubridate", unload=TRUE)
```