cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Complex calculations to predict number of paying customers

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.

2 REPLIES 2
Community Support

Hi @Anonymous

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

Community Support

Hi @Anonymous

Do you want to calculate "forecasted paying customer" by previous years data?

Firstly you have a Data table with user data in it.

I think you may want to calculate "forecasted paying customer" in 2021 by Data in 2020. Is it right?

You will calculate returning% and conversion% by month in 2020 by this data table.

EX. 2020 Jan ,

Here I have a problem, shall we separate the users by Start month?

Ex: UserA Start date = 2020/1/31, so he will be assign to January.

Measure should looks like:

``countx(Filter(all(Data Table), Data Table[StartMonth] = Max(Data Table[StartMonth]))),Data Table[User])``

I am confused about your conversion% .

In your sample 456, start in 2020/01/01, but his end data in 2018/06/01.

Should we distributed assign him to January?

You can show me more details about your calculate logic and the result you want.

And how can we calculate retention ? Is it "Number of paying customers" in an other table?

Your calculate is complex, you can share a sample to me by your OneDrive for Business and show your the result you want.

This may make it easier for me to understand.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.