How to create segmentation development of customers

[Anonymous]

So, I want to create a table that shows how the customer develops from one segment from one month to another.

Something like

if (iscustomeratleast6months=1,sum(salesamount)>1000) then segment=A

else if (iscustomeratleast6months=0) then Segment=B

else if (isCustomeratleast6months=1,atcollection=1) then Segment=C

and so on..

I should be able to do all these calculations from my tables Transactions and Customers.

So how do I create these segments, and show how the customers develop per month?

I've been googeling quite a lot, but my lack of technical expertise makes it hard to understand how to transfer the examples to my own dataset. Can any of you please help?

CustomerID	2017_01	2017_02	2017_03
12345	A	A	B
12346	C	C	C
12347	C	C	C
12348	A	A	A

Message was edited by: Xili Lin

[sunny_talwar]

Would you be able to share some raw sample data which includes the flags such as iscustomeratleast6months and other fields such as salesamount and CustomerID.

[Anonymous]

Tried to create a sample set. The customertable only shows todays values, so man plan is to make historic data from now on, but for now I've only got the present data. I do however have a lot of salesdata. I'm worried that the sales data might break the report if I use too many calculations. So what would be the smart thing to do?

[sunny_talwar]

How do you create iscustomeratleast6months flag? look at the max salesdate and compare it to Today - 6 months? Also, Sum(SalesAmount) is at the customer level? You need this on the front end of the application?

[Anonymous]

I don't create the iscutomeratleast6monthsflag, it is a set variable from the DWH. The salesamount is at customer level.

My main problem is where to create all the ifs, and how.

[sunny_talwar]

I am not sure I follow... your if statement uses this

if (iscustomeratleast6months=1,sum(salesamount)>1000) then segment=A

else if (iscustomeratleast6months=0) then Segment=B

else if (isCustomeratleast6months=1,atcollection=1) then Segment=C

and so on..

What is iscustomeratleast6months? How do I know if the value is 1 or 0?