How to predict DAU using Duolingo\u2019s growth model and control the prediction<\/h4>\n1. Introduction<\/h3>\n
Doubtlessly, DAU, WAU, and MAU\u200a\u2014\u200adaily, weekly, and monthly active users<\/a>\u200a\u2014\u200aare critical business metrics. An article \u201cHow Duolingo reignited user growth\u201d<\/a> by Jorge Mazal<\/a>, former CPO of Duolingo, is #1 in the Growth section of Lenny\u2019s Newsletter blog. In this article, Jorge paid special attention to the methodology Duolingo used to model the DAU metric (see another article \u201cMeaningful metrics: how data sharpened the focus of product teams\u201d<\/a> by Erin Gustafson<\/a>). This methodology has multiple strengths, but I\u2019d like to focus on how one can use this approach for DAU forecasting.<\/p>\n The new year is coming soon, so many companies are planning their budgets for the next year these days. Cost estimations often require DAU forecasts. In this article, I\u2019ll show how you can get this prediction using Duolingo\u2019s growth model. I\u2019ll explain why this approach is better compared to standard time-series forecasting methods and how you can adjust the prediction according to your teams\u2019 plans (e.g., marketing, activation, product\u00a0teams).<\/p>\n The article text goes along with the code, and a simulated dataset is attached so the research is fully reproducible. The Jupyter notebook version is available here<\/a>. In the end, I\u2019ll share a DAU \u201ccalculator\u201d designed in Google Spreadsheet format.<\/p>\n I\u2019ll be narrating on behalf of the collective \u201cwe\u201d as if we\u2019re talking together.<\/p>\n A quick recap on how the Duolingo\u2019s growth model<\/a> works. At day d (d = 1, 2,\u00a0\u2026 ) of a user\u2019s lifetime, the user can be in one of the following 7 (mutually-exclusive) states: new, current, reactivated, resurrected, at_risk_wau, at_risk_mau, dormant. The states are defined according to indicators of whether a user was active today, in the last 7 days, or in the last 30 days. The definition summary is given in the table\u00a0below:<\/p>\n Having these states defined (as a set S), we can consider user behavior as a Markov chain<\/a>. Here\u2019s an example of a user\u2019s trajectory: new\u2192 current\u2192 current\u2192 at_risk_wau\u2192…\u2192 at_risk_mau\u2192…\u2192 dormant. Let M be a transition matrix associated with this Markov process: m_{i, j} = P(s_j | s_i) are the probabilities that a user moves to state s_j right after being at state s_i, where s_i, s_j \u2208 S. Such a matrix is inferred from the historical data.<\/p>\n If we assume that user behavior is stationary (independent of time), the matrix M fully describes the states of all users in the future. Suppose that the vector u_0 of length 7 contains the counts of users in certain states on a given day, denoted as day 0. According to the Markov model, on the next day 1, we expect to have the following number of users states\u00a0u_1:<\/p>\n Applying this formula recursively, we derive the number of users in certain states on any arbitrary day t > 0 in the\u00a0future.<\/p>\n Besides the initial distribution u_0, we need to provide the number of new users that will appear in the product each day in the future. We\u2019ll address this problem as a general time-series forecasting.<\/p>\n Now, having u_t calculated, we can determine DAU values on day\u00a0t:<\/p>\n DAU_t = #New_t + #Current_t + #Reactivated_t + #Resurrected_t<\/p>\n Additionally, we can easily calculate WAU and MAU\u00a0metrics:<\/p>\n WAU_t = DAU_t + #AtRiskWau_t, Finally, here\u2019s the algorithm outline:<\/p>\n This section is devoted to technical aspects of the implementation. If you\u2019re interested in studying the model properties rather than code, you may skip this section and go to the Section\u00a04<\/a>.<\/p>\n We use a simulated dataset based on historical data of a SaaS app. The data is stored in the dau_data.csv.gz<\/a> file and contains three columns: user_id, date, and registration_date. Each record indicates a day when a user was active. The dataset includes activity indicators for 51480 users from 2020-11-01 to 2023-10-31. Additionally, data from October 2020 is included to calculate user states properly, as the at_risk_mau and dormant states require data from one month\u00a0prior.<\/p>\n This is how the DAU time-series looks\u00a0like.<\/p>\n Suppose that today is 2023\u201310\u201331 and we want to predict the DAU metric for the next 2024 year. We define a couple of global constants PREDICTION_START and PREDICTION_END which encompass the prediction period.<\/p>\n Let\u2019s start from the new users prediction. We use the prophet<\/a> library as one of the easiest ways to forecast time-series data. The new_users Series contains such data. We extract it from the original df dataset selecting the rows where the registration date is equal to the\u00a0date.<\/p>\n prophet requires a time-series as a DataFrame containing two columns ds and y, so we reformat the new_users Series to the new_users_prophet DataFrame. Another thing we need to prepare is to create the future variable containing certain days for prediction: from prediction_start to prediction_end. This logic is implemented in the predict_new_users function. The plot below illustrates predictions for both past and future\u00a0periods.<\/p>\n The new_users_pred Series stores the predicted users\u00a0amount.<\/p>\n In practice, the most calculations are reasonable to execute as SQL queries to a database where the data is stored. Hereafter, we will simulate such querying using the duckdb<\/a>\u00a0library.<\/p>\n We want to assign one of the 7 states to each day of a user\u2019s lifetime within the app. According to the definition, for each day, we need to consider at least the past 30 days. This is where SQL window functions come in. However, since the df data contains only records of active days<\/em>, we need to explicitly extend them and include the days when a user was not active. In other words, instead of this list of\u00a0records:<\/p>\n we\u2019d like to get a list like\u00a0this:<\/p>\n For readability purposes we split the following SQL query into multiple subqueries.<\/p>\n The query results are kept in the states DataFrame:<\/p>\n Having obtained these states, we can calculate state transition frequencies. In the Section 4.3<\/a> we\u2019ll study how the prediction depends on a period in which transitions are considered, so it\u2019s reasonable to pre-aggregate this data on daily basis. The resulting transitions DataFrame contains date, state_from, state_to, and cnt\u00a0columns.<\/p>\n Now, we can calculate the transition matrix M. We implement the get_transition_matrix function, which accepts the transitions DataFrame and a pair of dates that encompass the transitions period to be considered.<\/p>\n As a baseline, let\u2019s calculate the transition matrix for the whole year from 2022-11-01 to 2023-10-31.<\/p>\n The sum of each row of any transition matrix equals 1 since it represents the probabilities of moving from one state to any other\u00a0state.<\/p>\n An initial state is retrieved from the states DataFrame by the get_state0 function and the corresponding SQL query. The only argument of the function is the date for which we want to get the initial state. We assign the result to the state0 variable.<\/p>\n The predict_dau function below accepts all the previous variables required for the DAU prediction and makes this prediction for a date range defined by the start_date and end_date arguments.<\/p>\n This is how the DAU prediction dau_pred looks like for the PREDICTION_START – PREDICTION_END period. Besides the expected dau, wau, and mau columns, the output contains the number of users in each state for each prediction date.<\/p>\n Finally, we calculate the ground-truth values of DAU, WAU, and MAU (along with the user state counts), keep them in the dau_true DataFrame, and plot the predicted and true values altogether.<\/p>\n We\u2019ve obtained the prediction but so far it\u2019s not clear whether it\u2019s fair or not. In the next section, we\u2019ll evaluate the\u00a0model.<\/p>\n First of all, let\u2019s check whether we really need to build a complex model to predict DAU. Wouldn\u2019t it be better to predict DAU as a general time-series using the mentioned prophet library? The function predict_dau_prophet below implements this. We try to use some tweaks available in the library in order to make the prediction more accurate. In particular:<\/p>\n The fact that the code turns out to be quite sophisticated indicates that one can\u2019t simply apply prophet to the DAU time-series.<\/p>\n Hereafter we test a prediction for multiple predicting horizons: 3, 6, and 12 months. As a result, we get 3 test\u00a0sets:<\/p>\n For each test set we calculate the MAPE<\/a> loss function.<\/p>\n The MAPE error turns out to be high: 18%\u200a\u2014\u200a35%. The fact that the shortest horizon has the highest error means that the model is tuned for the long-term predictions. This is another inconvenience of such an approach: we have to tune the model for each prediction horizon. Anyway, this is our baseline. In the next section we\u2019ll compare it with more advanced\u00a0models.<\/p>\n In this section we evaluate the model implemented in the Section 3.6<\/a>. So far we set the transition period as 1 year before the prediction start. We\u2019ll study how the prediction depends on the transition period in the Section 4.3<\/a>. As for the new users, we run the model using two options: the real values and the predicted ones. Similarly, we fix the same 3 prediction horizons and test the model on\u00a0them.<\/p>\n The make_predicion helper function below implements the described options. It accepts prediction_start, prediction_end arguments defining the prediction period for a given horizon, new_users_mode which can be either true or predict, and transition_period. The options of the latter argument will be explained further.<\/p>\n In total, we have 6 prediction scenarios: 2 options for new users and 3 prediction horizons. The diagram below illustrates the results. The charts on the left relate to the new_users_mode = ‘predict’ option, while the right ones relate to the new_users_mode = ‘true’\u00a0option.<\/p>\n And here are the MAPE values summarizing the prediction quality:<\/p>\n We notice multiple\u00a0things.<\/p>\n Now, let\u2019s decompose the prediction error and see which states contribure the most. By error we mean here dau_pred – dau_true values, by relative error – ( dau_pred – dau_true) \/ dau_true – see left and right diagrams below correspondingly. In order to focus on this aspect, we’ll narrow the configuration to the 3-months prediction horizon and the new_users_mode=’true’ option.<\/p>\n From the left chart we notice that the error is basically contributed by the current state. It’s not surprising since this state contributes to DAU the most. The error for the reactivated, and resurrected states is quite low. Another interesting thing is that this error is mostly negative for the current state and mostly positive for the resurrected state. The former might be explained by the fact that the new users who appeared in the prediction period are more engaged that the users from the past. The latter indicates that the resurrected users in reality contribute to DAU less than the transition matrix expects, so the dormant\u2192 resurrected conversion rate is overestimated.<\/p>\n As for the relative error, it makes sense to analyze it for the current state only. This is because the daily amount of the reactivated and resurrected states are low so the relative error is high and noisy. The relative error for the current state varies between -25% and 4% which is quite high. And since we’ve fixed the new users prediction, this error is explained by the transition matrix inaccuracy only. In particular, the current\u2192 current conversion rate is roughly 0.8 which is high and, as a result, it contributes to the error a lot. So if we want to improve the prediction we need to consider tuning this conversion rate foremost.<\/p>\n In the previous section we kept the transitions period fixed: 1 year before a prediction start. Now we\u2019re going to study how long this period should be to get more accurate prediction. We consider the same prediction horizons of 3, 6, and 12 months. In order to mitigate the noise from the new users prediction, we use the real values of the new users amount: new_users_mode=’true’.<\/p>\n Here comes varying of the transition_period argument. Its values are masked with the last_<N>d pattern where N stands for the number of days in a transitions period. For each prediction horizon we calculate 12 different transition periods of 1, 2,\u00a0…, 12 months. Then we calculate the MAPE error for each of the options and plot the\u00a0results.<\/p>\n It turns out that the optimal transitions period depends on the prediction horizon. Shorter horizons require shorter transitions periods: the minimal MAPE error is achieved at 1, 4, and 8 transition periods for the 3, 6, and 12 months correspondingly. Apparently, this is because the longer horizons contain some seasonal effects that could be captured only by the longer transitions periods. Also, it seems that for the longer prediction horizons the MAPE curve is U-shaped meaning that too long and too short transitions periods are both not good for the prediction. We\u2019ll develop this idea in the next\u00a0section.<\/p>\n Nevertheless, fixing a single transition matrix for predicting the whole year ahead doesn\u2019t seem to be a good idea: such a model would be too rigid. Usually, user behavior varies depending on a season. For example, users who appear after Christmas might have some shifts in behavior. Another typical situation is when users change their behavior in summer. In this section, we\u2019ll try to take into account these seasonal\u00a0effects.<\/p>\n So we want to predict DAU for 1 year ahead starting from November 2022. Instead of using a single transition matrix M_base which is calculated for the last 8 months before the prediction start, according to the previous subsection results (and labeled as the last_240d option below), we’ll consider a mixture of this matrix and a seasonal one M_seasonal. The latter is calculated on monthly basis lagging 1 year behind. For example, to predict DAU for November 2022 we define M_seasonal as the transition matrix for November 2021. Then we shift the prediction horizon to December 2022 and calculate M_seasonal for December 2021,\u00a0etc.<\/p>\n In order to mix M_base and M_seasonal we define the following two\u00a0options.<\/p>\n According to the MAPE errors, seasonal_0.3 configuration provides the best results. Interestingly, smoothing approach has appeared to be even worse than the last_240d. From the diagram above we see that all three models start to underestimate the DAU values in July 2023, especially the smoothing model. It seems that the new users who started appearing in July 2023 are more engaged than the users from 2022. Probably, the app was improved sufficiently or the marketing team did a great job. As a result, the smoothing model that much relies on the outdated transitions data from July 2022 – October 2022 fails more than the other\u00a0models.<\/p>\n To sum things up, let\u2019s make a final prediction for the 2024 year. We use the seasonal_0.3 configuration and the predicted values for new\u00a0users.<\/p>\n In the Section 4<\/a> we studied the model performance from the prediction accuracy perspective. Now let\u2019s discuss the model from the practical point of\u00a0view.<\/p>\n Besides poor accuracy, predicting DAU as a time-series (see the Section 4.1<\/a>) makes this approach very stiff. Essentially, it makes a prediction in such a manner so it would fit historical<\/em> data best. In practice, when making plans for a next year we usually have some certain expectations about the future. For\u00a0example,<\/p>\n Our model can take into account such expectations. For the examples above we can adjust the new users prediction, the new\u2192 current and the current\u2192 current conversion rates respectively. As a result, we can get a prediction that doesn’t match with the historical data but nevertheless would be more realistic. This model’s property is not just flexible – it’s interpretable. You can easily discuss all these adjustments with the stakeholders, and they can understand how the prediction works.<\/p>\n Another advantage of the model is that it doesn\u2019t require predicting whether a certain user will be active on a certain day. Sometimes binary classifiers are used for this purpose. The downside of this approach is that we need to apply such a classifier to each user including all the dormant users and each day from a prediction horizon. This is a tremedous computational cost. In contrast, the Markov model requires only the initial amount of states ( state0). Moreover, such classiffiers are often black-box models: they are poorly interpretable and hard to\u00a0adjust.<\/p>\n The Markov model also has some limitations. As we already have seen, it\u2019s sensitive to the new users prediction. It\u2019s easy to totally ruin the prediction by a wrong new users amount. Another problem is that the Markov model is memoryless meaning that it doesn\u2019t take into account the user\u2019s history. For example, it doesn\u2019t distinguish whether a current user is a newbie, experienced, or reactivated\/ resurrected one. The retention rate of these user types should be certainly different. Also, as we discussed earlier, the user behavior might be of different nature depending on the season, marketing sources, countries, etc. So far our model is not able to capture these differences. However, this might be a subject for further research: we could extend the model by fitting more transition matrices for different user segments.<\/p>\n Finally, as we promised in the introduction, we provide a DAU spreadsheet calculator<\/a>. In the Prediction sheet you’ll need to fill the initial states distribution row (marked with blue) and the new users prediction column (marked with purple). In the Conversions sheet you can adjust the transition matrix values. Remember that the sum of each row of the matrix should be equal to\u00a01.<\/p>\n That\u2019s all for now. I hope that this article was useful for you. In case of any questions or suggestions, feel free to ask in the comments below or contact me directly on LinkedIn<\/a>.<\/p>\n All the images in the post are generated by the\u00a0author.<\/p>\n Modeling DAU with Markov Chain<\/a> was originally published in Towards Data Science<\/a> on Medium, where people are continuing the conversation by highlighting and responding to this story.<\/p>\n<\/div>\n2. Methodology<\/h3>\n
![\"\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2AtAPSb4Syw4hFteuao0T7PA.png?ssl=1\")
<\/figure>\n![\"\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2ACv3xYnXplDXsDIno6MPrFw.png?ssl=1\")
<\/figure>\n
MAU_t = DAU_t + #AtRiskWau_t + #AtRiskMau_t.<\/p>\n\n
3. Implementation<\/h3>\n
3.1 Dataset<\/h4>\n
import pandas as pd
df = pd.read_csv('dau_data.csv.gz', compression='gzip')
df['date'] = pd.to_datetime(df['date'])
df['registration_date'] = pd.to_datetime(df['registration_date'])
print(f'Shape: {df.shape}')
print(f'Total users: {df['user_id'].nunique()}')
print(f'Data range: [{df['date'].min()}, {df['date'].max()}]')
df.head()<\/pre>\nShape: (667236, 3)
Total users: 51480
Data range: [2020-10-01 00:00:00, 2023-10-31 00:00:00]<\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2AboptDIHcT3KVLhNdbNNnQw.png?ssl=1\")
<\/figure>\ndf.groupby('date').size()
.plot(title='DAU, historical')<\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2AB88Nd_1uVTA3SYy4Xh2xgg.png?ssl=1\")
<\/figure>\nPREDICTION_START = '2023-11-01'
PREDICTION_END = '2024-12-31'<\/pre>\n3.2 Predicting new users\u00a0amount<\/h4>\n
new_users = df[df['date'] == df['registration_date']]
.groupby('date').size()
new_users.head()<\/pre>\ndate
2020-10-01 4
2020-10-02 4
2020-10-03 3
2020-10-04 4
2020-10-05 8
dtype: int64<\/pre>\nimport logging
import matplotlib.pyplot as plt
from prophet import Prophet
# suppress prophet logs
logging.getLogger('prophet').setLevel(logging.WARNING)
logging.getLogger('cmdstanpy').disabled=True
def predict_new_users(prediction_start, prediction_end, new_users_train, show_plot=True):
\"\"\"
Forecasts a time-seires for new users
Parameters
----------
prediction_start : str
Date in YYYY-MM-DD format.
prediction_end : str
Date in YYYY-MM-DD format.
new_users_train : pandas.Series
Historical data for the time-series preceding the prediction period.
show_plot : boolean, default=True
If True, a chart with the train and predicted time-series values is displayed.
Returns
-------
pandas.Series
Series containing the predicted values.
\"\"\"
m = Prophet()
new_users_train = new_users_train
.loc[new_users_train.index < prediction_start]
new_users_prophet = pd.DataFrame({
'ds': new_users_train.index,
'y': new_users_train.values
})
m.fit(new_users_prophet)
periods = len(pd.date_range(prediction_start, prediction_end))
future = m.make_future_dataframe(periods=periods)
new_users_pred = m.predict(future)
if show_plot:
m.plot(new_users_pred)
plt.title('New users prediction');
new_users_pred = new_users_pred
.assign(yhat=lambda _df: _df['yhat'].astype(int))
.rename(columns={'ds': 'date', 'yhat': 'count'})
.set_index('date')
.clip(lower=0)
['count']
return new_users_pred<\/pre>\nnew_users_pred = predict_new_users(PREDICTION_START, PREDICTION_END, new_users)<\/pre>\n
![\"\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2APrxCj-BJmr7rgc7VyNkIlw.png?ssl=1\")
<\/figure>\nnew_users_pred.tail(5)<\/pre>\n
date
2024-12-27 52
2024-12-28 56
2024-12-29 71
2024-12-30 79
2024-12-31 74
Name: count, dtype: int64<\/pre>\n3.3 Getting the\u00a0states<\/h4>\n
user_id date registration_date
1234567 2023-01-01 2023-01-01
1234567 2023-01-03 2023-01-01<\/pre>\nuser_id date is_active registration_date
1234567 2023-01-01 TRUE 2023-01-01
1234567 2023-01-02 FALSE 2023-01-01
1234567 2023-01-03 TRUE 2023-01-01
1234567 2023-01-04 FALSE 2023-01-01
1234567 2023-01-05 FALSE 2023-01-01
... ... ... ...
1234567 2023-10-31 FALSE 2023-01-01<\/pre>\n\n
import duckdb
DATASET_START = '2020-11-01'
DATASET_END = '2023-10-31'
OBSERVATION_START = '2020-10-01'
query = f\"\"\"
WITH
full_range AS (
SELECT
user_id, UNNEST(generate_series(greatest(registration_date, '{OBSERVATION_START}'), date '{DATASET_END}', INTERVAL 1 DAY))::date AS date
FROM (
SELECT DISTINCT user_id, registration_date FROM df
)
),
dau_full AS (
SELECT
fr.user_id,
fr.date,
df.date IS NOT NULL AS is_active,
registration_date
FROM full_range AS fr
LEFT JOIN df USING(user_id, date)
),
states AS (
SELECT
user_id,
date,
is_active,
first_value(registration_date IGNORE NULLS) OVER (PARTITION BY user_id ORDER BY date) AS registration_date,
SUM(is_active::int) OVER (PARTITION BY user_id ORDER BY date ROWS BETWEEN 6 PRECEDING and 1 PRECEDING) AS active_days_back_6d,
SUM(is_active::int) OVER (PARTITION BY user_id ORDER BY date ROWS BETWEEN 29 PRECEDING and 1 PRECEDING) AS active_days_back_29d,
CASE
WHEN date = registration_date THEN 'new'
WHEN is_active = TRUE AND active_days_back_6d BETWEEN 1 and 6 THEN 'current'
WHEN is_active = TRUE AND active_days_back_6d = 0 AND IFNULL(active_days_back_29d, 0) > 0 THEN 'reactivated'
WHEN is_active = TRUE AND active_days_back_6d = 0 AND IFNULL(active_days_back_29d, 0) = 0 THEN 'resurrected'
WHEN is_active = FALSE AND active_days_back_6d > 0 THEN 'at_risk_wau'
WHEN is_active = FALSE AND active_days_back_6d = 0 AND ifnull(active_days_back_29d, 0) > 0 THEN 'at_risk_mau'
ELSE 'dormant'
END AS state
FROM dau_full
)
SELECT user_id, date, state FROM states
WHERE date BETWEEN '{DATASET_START}' AND '{DATASET_END}'
ORDER BY user_id, date
\"\"\"
states = duckdb.sql(query).df()<\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2ACKrex2_FcRmfmFMOtT-_yw.png?ssl=1\")
<\/figure>\n3.4 Calculating the transition matrix<\/h4>\n
M = get_transition_matrix(transitions, '2022-11-01', '2023-10-31')
M<\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2ACEK1ieeHcLT4-0XtXUAiHg.png?ssl=1\")
<\/figure>\n3.5 Getting the initial state\u00a0counts<\/h4>\n
def get_state0(date):
query = f\"\"\"
SELECT state, count(*) AS cnt
FROM states
WHERE date = '{date}'
GROUP BY state
\"\"\"
state0 = duckdb.sql(query).df()
state0 = state0.set_index('state').reindex(states_order)['cnt']
return state0<\/pre>\nstate0 = get_state0(DATASET_END)
state0<\/pre>\nstate
new 20
current 475
reactivated 15
resurrected 19
at_risk_wau 404
at_risk_mau 1024
dormant 49523
Name: cnt, dtype: int64<\/pre>\n3.6 Predicting DAU<\/h4>\n
def predict_dau(M, state0, start_date, end_date, new_users):
\"\"\"
Predicts DAU over a given date range.
Parameters
----------
M : pandas.DataFrame
Transition matrix representing user state changes.
state0 : pandas.Series
counts of initial state of users.
start_date : str
Start date of the prediction period in 'YYYY-MM-DD' format.
end_date : str
End date of the prediction period in 'YYYY-MM-DD' format.
new_users : int or pandas.Series
The expected amount of new users for each day between `start_date` and `end_date`.
If a Series, it should have dates as the index.
If an int, the same number is used for each day.
Returns
-------
pandas.DataFrame
DataFrame containing the predicted DAU, WAU, and MAU for each day in the date range,
with columns for different user states and tot.
\"\"\"
dates = pd.date_range(start_date, end_date)
dates.name = 'date'
dau_pred = []
new_dau = state0.copy()
for date in dates:
new_dau = (M.transpose() @ new_dau).astype(int)
if isinstance(new_users, int):
new_users_today = new_users
else:
new_users_today = new_users.astype(int).loc[date]
new_dau.loc['new'] = new_users_today
dau_pred.append(new_dau.tolist())
dau_pred = pd.DataFrame(dau_pred, index=dates, columns=states_order)
dau_pred['dau'] = dau_pred['new'] + dau_pred['current'] + dau_pred['reactivated'] + dau_pred['resurrected']
dau_pred['wau'] = dau_pred['dau'] + dau_pred['at_risk_wau']
dau_pred['mau'] = dau_pred['dau'] + dau_pred['at_risk_wau'] + dau_pred['at_risk_mau']
return dau_pred<\/pre>\ndau_pred = predict_dau(M, state0, PREDICTION_START, PREDICTION_END, new_users_pred)
dau_pred<\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2AonivsmmuFBTDt0qZhlrQXA.png?ssl=1\")
<\/figure>\nquery = f\"\"\"
SELECT date, state, COUNT(*) AS cnt
FROM states
GROUP BY date, state
ORDER BY date, state;
\"\"\"
dau_true = duckdb.sql(query).df()
dau_true['date'] = pd.to_datetime(dau_true['date'])
dau_true = dau_true.pivot(index='date', columns='state', values='cnt')
dau_true['dau'] = dau_true['new'] + dau_true['current'] + dau_true['reactivated'] + dau_true['resurrected']
dau_true['wau'] = dau_true['dau'] + dau_true['at_risk_wau']
dau_true['mau'] = dau_true['dau'] + dau_true['at_risk_wau'] + dau_true['at_risk_mau']<\/pre>\ndau_true.head()<\/pre>\n
![\"\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2AToJZNPI8Bd4oxJm5tMts5w.png?ssl=1\")
<\/figure>\npd.concat([dau_true['dau'], dau_pred['dau']])
.plot(title='DAU, historical & predicted');
plt.axvline(PREDICTION_START, color='k', linestyle='--');<\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2Ab9opg5Z4MkcNvx0RvWJEjA.png?ssl=1\")
<\/figure>\n4. Model evaluation<\/h3>\n
4.1 Baseline\u00a0model<\/h4>\n
\n
def predict_dau_prophet(prediction_start, prediction_end, dau_true, show_plot=True):
# assigning peak days for the new year
holidays = pd.DataFrame({
'holiday': 'january_spike',
'ds': pd.date_range('2022-01-01', '2022-01-31', freq='D').tolist() +
pd.date_range('2023-01-01', '2023-01-31', freq='D').tolist(),
'lower_window': 0,
'upper_window': 40
})
m = Prophet(growth='logistic', holidays=holidays)
m.add_seasonality(name='monthly', period=30.5, fourier_order=3)
m.add_seasonality(name='yearly', period=365, fourier_order=3)
train = dau_true.loc[(dau_true.index < prediction_start) & (dau_true.index >= '2021-08-01')]
train_prophet = pd.DataFrame({'ds': train.index, 'y': train.values})
# removining outliers
train_prophet.loc[train_prophet['ds'].between('2022-06-07', '2022-06-09'), 'y'] = None
train_prophet['new_year_peak'] = (train_prophet['ds'] >= '2022-01-01') &
(train_prophet['ds'] <= '2022-02-14')
m.add_regressor('new_year_peak')
# setting logistic upper and lower bounds
train_prophet['cap'] = dau_true.max() * 1.1
train_prophet['floor'] = 0
m.fit(train_prophet)
periods = len(pd.date_range(prediction_start, prediction_end))
future = m.make_future_dataframe(periods=periods)
future['new_year_peak'] = (future['ds'] >= '2022-01-01') & (future['ds'] <= '2022-02-14')
future['cap'] = dau_true.max() * 1.1
future['floor'] = 0
pred = m.predict(future)
if show_plot:
m.plot(pred);
# converting the predictions to an appropriate format
pred = pred
.assign(yhat=lambda _df: _df['yhat'].astype(int))
.rename(columns={'ds': 'date', 'yhat': 'count'})
.set_index('date')
.clip(lower=0)
['count']
.loc[lambda s: (s.index >= prediction_start) & (s.index <= prediction_end)]
return pred<\/pre>\n\n
from sklearn.metrics import mean_absolute_percentage_error
mapes = []
prediction_end = '2023-10-31'
prediction_horizon = [3, 6, 12]
for offset in prediction_horizon:
prediction_start = pd.to_datetime(prediction_end) - pd.DateOffset(months=offset - 1)
prediction_start = prediction_start.replace(day=1)
prediction_end = '2023-10-31'
pred = predict_dau_prophet(prediction_start, prediction_end, dau_true['dau'], show_plot=False)
mape = mean_absolute_percentage_error(dau_true['dau'].reindex(pred.index), pred)
mapes.append(mape)
mapes = pd.DataFrame({'horizon': prediction_horizon, 'MAPE': mapes})
mapes<\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2AIDKv5izzXgCIIEO2BNRO6Q.png?ssl=1\")
<\/figure>\n4.2 General evaluation<\/h4>\n
import re
def make_prediction(prediction_start, prediction_end, new_users_mode='predict', transition_period='last_30d'):
prediction_start_minus_1d = pd.to_datetime(prediction_start) - pd.Timedelta('1d')
state0 = get_state0(prediction_start_minus_1d)
if new_users_mode == 'predict':
new_users_pred = predict_new_users(prediction_start, prediction_end, new_users, show_plot=False)
elif new_users_mode == 'true':
new_users_pred = new_users.copy()
if transition_period.startswith('last_'):
shift = int(re.search(r'last_(d+)d', transition_period).group(1))
transitions_start = pd.to_datetime(prediction_start) - pd.Timedelta(shift, 'd')
M = get_transition_matrix(transitions, transitions_start, prediction_start_minus_1d)
dau_pred = predict_dau(M, state0, prediction_start, prediction_end, new_users_pred)
else:
transitions_start = pd.to_datetime(prediction_start) - pd.Timedelta(240, 'd')
M_base = get_transition_matrix(transitions, transitions_start, prediction_start_minus_1d)
dau_pred = pd.DataFrame()
month_starts = pd.date_range(prediction_start, prediction_end, freq='1MS')
N = len(month_starts)
for i, prediction_month_start in enumerate(month_starts):
prediction_month_end = pd.offsets.MonthEnd().rollforward(prediction_month_start)
transitions_month_start = prediction_month_start - pd.Timedelta('365D')
transitions_month_end = prediction_month_end - pd.Timedelta('365D')
M_seasonal = get_transition_matrix(transitions, transitions_month_start, transitions_month_end)
if transition_period == 'smoothing':
i = min(i, 12)
M = M_seasonal * i \/ (N - 1) + (1 - i \/ (N - 1)) * M_base
elif transition_period.startswith('seasonal_'):
seasonal_coef = float(re.search(r'seasonal_(0.d+)', transition_period).group(1))
M = seasonal_coef * M_seasonal + (1 - seasonal_coef) * M_base
dau_tmp = predict_dau(M, state0, prediction_month_start, prediction_month_end, new_users_pred)
dau_pred = pd.concat([dau_pred, dau_tmp])
state0 = dau_tmp.loc[prediction_month_end][states_order]
return dau_pred
def prediction_details(dau_true, dau_pred, show_plot=True, ax=None):
y_true = dau_true.reindex(dau_pred.index)['dau']
y_pred = dau_pred['dau']
mape = mean_absolute_percentage_error(y_true, y_pred)
if show_plot:
prediction_start = str(y_true.index.min().date())
prediction_end = str(y_true.index.max().date())
if ax is None:
y_true.plot(label='DAU true')
y_pred.plot(label='DAU pred')
plt.title(f'DAU prediction, {prediction_start} - {prediction_end}')
plt.legend()
else:
y_true.plot(label='DAU true', ax=ax)
y_pred.plot(label='DAU pred', ax=ax)
ax.set_title(f'DAU prediction, {prediction_start} - {prediction_end}')
ax.legend()
return mape<\/pre>\nfig, axs = plt.subplots(3, 2, figsize=(15, 6))
mapes = []
prediction_end = '2023-10-31'
prediction_horizon = [3, 6, 12]
for i, offset in enumerate(prediction_horizon):
prediction_start = pd.to_datetime(prediction_end) - pd.DateOffset(months=offset - 1)
prediction_start = prediction_start.replace(day=1)
args = {
'prediction_start': prediction_start,
'prediction_end': prediction_end,
'transition_period': 'last_365d'
}
for j, new_users_mode in enumerate(['predict', 'true']):
args['new_users_mode'] = new_users_mode
dau_pred = make_prediction(**args)
mape = prediction_details(dau_true, dau_pred, ax=axs[i, j])
mapes.append([offset, new_users_mode, mape])
mapes = pd.DataFrame(mapes, columns=['horizon', 'new_users', 'MAPE'])
plt.tight_layout()<\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2A2tlgRVrGc_aDjRNdYii_9g.png?ssl=1\")
<\/figure>\nmapes.pivot(index='horizon', columns='new_users', values='MAPE')<\/pre>\n
![\"\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2AGX0FfctDtEPiQvBey-IPaQ.png?ssl=1\")
<\/figure>\n\n
dau_component_cols = ['new', 'current', 'reactivated', 'resurrected']
dau_pred = make_prediction('2023-08-01', '2023-10-31', new_users_mode='true', transition_period='last_365d')
figure, (ax1, ax2) = plt.subplots(1, 2, figsize=(12, 4))
dau_pred[dau_component_cols]
.subtract(dau_true[dau_component_cols])
.reindex(dau_pred.index)
.plot(title='Prediction error by state', ax=ax1)
dau_pred[['current']]
.subtract(dau_true[['current']])
.div(dau_true[['current']])
.reindex(dau_pred.index)
.plot(title='Relative prediction error (current state)', ax=ax2);<\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2AjGx4NxaaMzY8cW27xCuehg.png?ssl=1\")
<\/figure>\n4.3 Transitions period\u00a0impact<\/h4>\n
result = []
for prediction_offset in prediction_horizon:
prediction_start = pd.to_datetime(prediction_end) - pd.DateOffset(months=prediction_offset - 1)
prediction_start = prediction_start.replace(day=1)
for transition_offset in range(1, 13):
dau_pred = make_prediction(
prediction_start, prediction_end, new_users_mode='true',
transition_period=f'last_{transition_offset*30}d'
)
mape = prediction_details(dau_true, dau_pred, show_plot=False)
result.append([prediction_offset, transition_offset, mape])
result = pd.DataFrame(result, columns=['prediction_period', 'transition_period', 'mape'])
result.pivot(index='transition_period', columns='prediction_period', values='mape')
.plot(title='MAPE by prediction and transition period');<\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2AqeC8RXnYtfF74NpBIUfUOQ.png?ssl=1\")
<\/figure>\n4.4 Obsolence and seasonality<\/h4>\n
\n
result = pd.DataFrame()
for transition_period in ['last_240d', 'seasonal_0.3', 'smoothing']:
result[transition_period] = make_prediction(
'2022-11-01', '2023-10-31',
'true',
transition_period
)['dau']
result['true'] = dau_true['dau']
result['true'] = result['true'].astype(int)
result.plot(title='DAU prediction by different transition matrices');<\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2AZrRv71gr3_36ssoFlWhE3g.png?ssl=1\")
<\/figure>\nmape = pd.DataFrame()
for col in result.columns:
if col != 'true':
mape.loc[col, 'mape'] = mean_absolute_percentage_error(result['true'], result[col])
mape<\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2A_cwTRpOtAlKpqDMr75IlOw.png?ssl=1\")
<\/figure>\n4.5 Final\u00a0solution<\/h4>\n
dau_pred = make_prediction(
PREDICTION_START, PREDICTION_END,
new_users_mode='predict',
transition_period='seasonal_0.3'
)
dau_true['dau'].plot(label='true')
dau_pred['dau'].plot(label='seasonal_0.3')
plt.title('DAU, historical & predicted')
plt.axvline(PREDICTION_START, color='k', linestyle='--')
plt.legend();<\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2ABZLm5RMB7E7qN_3UWlJZJw.png?ssl=1\")
<\/figure>\n5. Discussion<\/h3>\n
\n
![\"\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/0%2AnH670_3JC8_4uul1.png?ssl=1\")
<\/figure>\n<\/p>\n
\n