Segmenting customer by purchase behavior

[Anonymous]

Hello, 

I am hoping someone could help.

I want to segment my customer by : buyer who bought once, every 2 month, every 3 month, etc..

For that i have a table with unique ID and date of their first order, i have another table with all the purchase history for a year with the same ID, Transaction ID, date of the order and the product they bought.

I tried to use a custom column in the first table but i have a hard time to be able to use a DAX formula that can join my two tables...

If someone has a better idea, that would save my day !! 

Thank you in advance 

Best regards 

[v-lili6-msft]

hi, @Anonymous 

What is the logic of "every 2 month, every 3 month, etc.." ?

If one customer is every 2 month and every 3 month, which group should be segmented into.

Sample data and expected output would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Lin

[Anonymous]

Hello, 

Sorry for my late answer. 

My purpose is to group customer based on their purchase behavior Like 

Customer A,B,C buy every 2 month

Customer D,E,F buy every 3 month 

Customer  G,H,I buy every 4 month 

Customer  J,K,L bought only once etc...

That i can use to understand my customer retention.

Is it more clear ? 

Thank you in advance 

Best regards 

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

Assuming that if Customer A buy something on 1,3,4,6,10 month, what group should be for him? every 2 month, every 3 month, every 4 month?

So sample data and expected output would help tremendously. 

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 

Best Regards,
Lin

[Anonymous]

Hello Lili, 

Indeed, i didnt think about that, thank you for your answer, please find attached the table i am working with,

Purchase_date	Type of order	Transaction ID	Customer Id	Amount	Customer status	region	First purchase date	Last purchase date
01/01/2018	sales order	EU00000	D00000	50	Active	EU	01/02/2017	01/01/2018

 i have a date table  and unique ID table with Customer ID and first purchase date.

Now i only want to set 5 max different segment and your question made me think that maybe segmenting by month will not work i will still have some customer without segment. 

Maybe an average of the frequency based on their purchase behavior will be more appropriate.

As : 

Customer A,B buy every one to two month 

Customer C, D buy every 3 to 4 month etc.. 

Then the kind of customer you are talking about can go in the first or second segment. Maybe having a dynamic column like this 'Frequency'  added to the unique ID table :

Customer Id	First purchase date	Frequency
D0000	01/02/2017	Every 2 to 3 month

Thank you for your support.

Best regards 

Ilhame

[v-lili6-msft]

HI, @Anonymous 

Could you please tell me if you still have other problem?

if not, please close the topic and mark the relevant post as an answer. Thank you 

Best Regards,

Lin