Forecasting recurring orders for an online subscription business using Facebook Prophet and R

Question

I am analyzing data from a subscription model, in which a customer must pay a recurring price at a regular interval (30 days) for access to the product.

EDIT -> Direct link to daily data: https://docs.google.com/spreadsheets/d/1rgFKQsXIn9VmKtpv06cVPytCoPynpVva3fOVKqevD3s/edit#gid=0

Data

You can access the data here via this google sheet.

library(tidyverse)
library(lubridate)
library(forecast)

df <- read.csv("https://docs.google.com/spreadsheets/d/e/2PACX-1vTrf4SbDZPwIe_xDHsHLywkxBtm1ZD6AOz4YQJmNNTDwpMuol0um3xmLGpJkY2ImNDtfKwKhoeXOlF-/pub?gid=0&single=true&output=csv")

head(df)
          ds  order_type y
1 2018-12-04 acquisition 1
2 2018-12-09 acquisition 1
3 2018-12-16 acquisition 1
4 2018-12-18 acquisition 1
5 2018-12-19 acquisition 1
6 2018-12-20 acquisition 1

After aggregating the data on the YYYY-MM level, one can observe the following:

df %>%
  mutate(month = format(as.Date(ds), "%Y-%m")) %>%
  group_by(month,order_type) %>%
  summarise(y = sum(y)) %>%
  spread(order_type, y)

month   acquisition recurring_orders
2018-12      9        0
2019-01      42       6
2019-02      98       34
2019-03      644      130
2019-04      588      554
2019-05      324      775
2019-06      335      709
2019-07      184      467

• Data for July is incomplete. The last date of data collection is 2019-07-17.

Recurring orders are obviously 0 in December - the month of the launch - and then they will be reduced of a certain percentage representing the churn of the userbase; while they will grow as well in accordance with the acquisition of the previous months.

Users will churn for different reasons:

• "Natural churn" of users dropping out of the subscription
• Orders not processed for payment-related reasons - ie insufficient funds
• Users "pausing" the subscription - for example users skipping a delivering postponing it to the next month ...

Task:

I want to perform a forecast for recurring orders for the month of July 2019 aka for the remaining 14 days:

last_day = as.Date('2019-07-17')
remaining_days <- as.numeric(days_in_month(last_day) - mday(last_day))

Recurring orders will be affected by multiple factors - 2 forces (churn and acquisition) pushing in two different directions; combinations of seasonalities, platform-related (ie. payments) issues and so on.

I chose to use Prophet, an algorithm recently published by Facebook, accounting for weekly and monthly seasonalities, and simple linear growth.

m <- prophet(weekly.seasonality=T,
             daily.seasonality=F,
             yearly.seasonality = F)

m <- add_seasonality(m, name='monthly', period=30.5, fourier.order=5)
m <- fit.prophet(m, df_r)

future <- make_future_dataframe(m, periods = remaining_days)
forecast <- predict(m, future)
prophet_plot_components(m, forecast)

# generate basic forecast
future <- make_future_dataframe(m, periods = remaining_days)
forecast <- predict(m, future)

plot(m, forecast, xlabel = "", ylabel = "orders")

I can look now at the predictions:

forecast %>%
  select(ds, yhat) %>%
  mutate(month = format(ds, "%Y-%m")) %>%
  group_by(month) %>%
  summarise(orders_pre = sum(yhat)) -> pred

df_r %>%
  mutate(month = format(ds, "%Y-%m")) %>%
  group_by(month) %>%
  summarise(actual_orders = sum(y)) -> act

act %>%
  left_join(pred) %>%
  mutate(predicted_orders = round(orders_pre,0)) %>%
  select(-orders_pre) %>%
  mutate(prediction_error = predicted_orders - actual_orders) %>%
  mutate(perc_mismatch = round(prediction_error/actual_orders,4)*100)


# A tibble: 7 x 5
  month   actual_orders predicted_orders prediction_error perc_mismatch
  <chr>           <int>            <dbl>            <dbl>         <dbl>
1 2019-01             6              -15              -21       -350   
2 2019-02            34               44               10         29.4 
3 2019-03           130              279              149        115.  
4 2019-04           554              475              -79        -14.3 
5 2019-05           775              655             -120        -15.5 
6 2019-06           709              736               27          3.81
7 2019-07           467              866              399         85.4 

It seems to generate a very good prediction for the month of June, but it doesn't perform very well in the previous months.

I have some questions:

1. Prophet seems to be good at capturing the seasonality and the changes of trend. But why I get negative predictions at the starting point? Does it make sense to use a time series model like this to estimate data (recurring orders) heavily dependent on the past observations? Is there any other family of models I should look into?
2. Do I need to apply any sort of transformation before fitting the model (ie BoxCox)? if so, why does it help?
3. To evaluate the model, I simply take the difference (absolute and %) between the actual and the predicted orders in the past. What are the solutions embedded in prophet in terms of measures of model performance?
4. How do I understand whether I have to use a logistic growth instead of linear?

Answer 1

It is important to know thy assumptions . As you quoted "I chose to use Prophet, an algorithm recently published by Facebook, accounting for weekly and monthly seasonalities, and simple linear growth" . Simple linear growth assumptions can have serious consequences as in this case.

The problem with this if there is a simple level shift in your data this leads to a false conclusion about growth. Your data , some 354 transactions over a 226 day period suggests that there was a simple level shift around 3/3/2019 or day 90 . This break point or deterministic intervention (level/step shift) was easily found with tools following http://docplayer.net/12080848-Outliers-level-shifts-and-variance-changes-in-time-series.html which are a curious omission in Prophet's current solution.

It is easily confirmed by a simple plot

The EYE and the FORECAST gets confused when examining this assumption driven fit and forecast

Your complaint about poor accuracies might be related to the utilization of a deterministic trend line instead of a level shift indicator to explain/reflect "growth". Discerning between these two potentially critical components should be part of any analytical engine and would fall under the class of a "must have" rather than a"nice to have feature".

Here are the 'Growth Driven Forecasts" which are troubling to you ( and me ! ) ... all a consequence of "simple linear growth" .

Please see some of my recent reflections on Prophet's assumptions here Is Prophet from Facebook any different from a linear regression?