I spent two days on this, ending up with too complex models that file slowed down to a point of being unusable. So I hope someone here can point me towards simpler solutions.
I have user data like this (example):
| user_id | start | end | trial_end | returning |
| 123 | 01/01/2018 | | 15/01/2018 | 0 |
| 456 | 01/01/2018 | 01/06/2018 | | 1 |
And I receive predictions from a collegue for number of sign ups per month next year. They include both new customers with trial and returning customers who will directly become paying.
sample:
| Month Next Year | signup |
| January | 1000 |
| February | 500 |
| March | 2000 |
Based on these, I want to forecast number of paying customers per month next year. The formula would be in two parts:
Some definitions:
returning%: percentage of users that with returning = 1
conversion%: percentage of new users who will become paying after the trial (trial_end but no end date)
signup_previous : number of signups previous month
here i will need to calculate conversion% and returning% based on the first data table, based on historical data.
So that'll either be a table: month, conversion% and returning%
or a measure that can dynamically calculate the relevant % based on the month to be forecasted, by using the percentages for the same month in the previous year.
new customers (per month):
signup*returning%
+ signup(1-returning%)*0.5*conversion% //half of the users to be converted in that month becomes paying that month
+signup_previous*(1-returning%)*0.5*conversion% //other half becomes paying customers next month, because of trial
retention: the number of paying users to be retained from the previous month, based on their customer age (in months), each age has a retention percentage, based on the historical data. So it would give, per age, % of the customers were retained to the next month. This is the most tricky part for me. I created a calculated table that looks like below, but that slowed down the file, so I'm looking for alternative solutions:
Per month, per age, number of paying customers, created using the first table with user_id and dates:
| Year Month | Age (months) | Number of paying customers |
| 201701 | 0 | 5000 |
| 201702 | 1 | 4000 |
Forecasted paying customers = new customers + retention
Forecasted paying customers would be a measure that gives the total of forecast per month next year.
The end table would look like this (sample), and users then can export it to use.
| Month Next Year | Forecasted paying customers |
| January 2021 | 2000 |
| February 2021 | 1000 |
I know it's complex, but I appreciate any direction, tips etc. I'm not expecting someone to come up with a full solution.
