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
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 ...
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:
- 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?
- Do I need to apply any sort of transformation before fitting the model (ie BoxCox)? if so, why does it help?
- 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?
- How do I understand whether I have to use a logistic growth instead of linear?