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

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.

Data

You can access the data here via this google sheet.

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

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)) %>%

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 ...

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?

## bumped to the homepage by Community♦9 hours ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• Interesting .. please post the daily data .... or provide the full url – IrishStat Jul 17 at 16:37
• If you read inside the first code block, you can access the daily data directly into an R dataframe via googlesheet. I added the direct link to it if you want to ingest it via any other framework – xxxvinxxx Jul 17 at 16:42

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?

• Interesting! In fact, starting around a month before that day, major marketing campaigns were activated hence the traffic became much more consistent. But how to overcome that problem of linearity? Should I consider alternative logistic growth? @IrishStat – xxxvinxxx Jul 18 at 12:23
• The detection of the level shift suggests that a real reason be found as memory is insufficient to accomodate the structural (intercept change) . What you might do now or perhaps what you should have done in the beginning is to entertain the idea that "the marketing campaign" be introduced as a predictor series . Your question about linearity confuses me and perhaps we need a session . Logistic growth would be about 14 steps backward in my opinion . Maybe 15 ! – IrishStat Jul 18 at 12:28
• If you are happy with my answer , please accept it and close the question – IrishStat Aug 15 at 8:01