1
$\begingroup$

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

enter image description here

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?
$\endgroup$
  • $\begingroup$ Interesting .. please post the daily data .... or provide the full url $\endgroup$ – IrishStat Jul 17 at 16:37
  • $\begingroup$ 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 $\endgroup$ – xxxvinxxx Jul 17 at 16:42
0
$\begingroup$

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 enter image description here

The EYE and the FORECAST gets confused when examining this assumption driven fit and forecast enter image description here

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

enter image description here

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

$\endgroup$
  • $\begingroup$ 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 $\endgroup$ – xxxvinxxx Jul 18 at 12:23
  • $\begingroup$ 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 ! $\endgroup$ – IrishStat Jul 18 at 12:28
  • $\begingroup$ If you are happy with my answer , please accept it and close the question $\endgroup$ – IrishStat Aug 15 at 8:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.