I have been given a table with retention rates starting from day 1 to day 364. I have been given a current DAU count of 600k with a degradation rate of 2% per week(I am assuming current DAU means DAU on Day 1. The other information includes a count of daily acquired new users which is 10k.
The problem is to forecast/predict users on day 365.
What would be the best way to approach this problem?
I don't know whether my approach is correct but below is what I am thinking.
Step 1: Starting from a count of 600k on day one add 10 k users each day. On day 8 deduct 2% from day 7 count and perform the same operation till day 364.
Step 2: For the retention rates. Say day 1 - 100%, 2-60%, 3-40%, 4-30%...day 364-1%. Calculate DAU using values in Step 1 and run a cumlative sum using the retention rates. Example:
Day 1 - 600,000 * 100 %
Day 2 - 600,000 * 60% + 610,000 * 100%
Day 3 - 600,000 * 40% + 610,000 * 60% + 620,000 * 100%
and so on
Once I have the DAU from Day 1 to Day 364 use Time series forecasting and predict the DAU for Day 365.
Thank you!
Adheip
Let :
A1 = day
B1 = RR
C1 = no of users
A2 = 0
B2 = 100%
C2 = 600000
E1 = 0
F1 = 1
G1 = 2
H1 = 3
I1 = 4
J1 = 5
K1 = 6
L1 = 7
M1 = 8
N1 = 9
O1 = 10
P1 = 11
Q1 = 12
R1 = 13
with
D2 =SUM(E2:R2)
drag until D16,
C3 =C2+10000
drag until C16, and
E2 =IF(E$1>$A2,0,INDEX($C:$C,MATCH(E$1,$A:$A,0)))*INDEX($B:$B,MATCH(E$1,$A:$A,0))
drag until R16.
Hope that helps. (: