Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
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_idstartendtrial_endreturning
12301/01/2018 15/01/20180
45601/01/201801/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 Yearsignup
January1000
February500
March2000

 

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 MonthAge (months)Number of paying customers 
20170105000
20170214000

 

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 YearForecasted paying customers
January 20212000
February 20211000

 

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
v-rzhou-msft
Community Support
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

v-rzhou-msft
Community Support
Community Support

Hi @Anonymous 

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

From your statement, I think your calculate logic is as below.

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. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.