Customer segmentation over time

[srl01]

I'm working with a sales transaction dataset with unique customer IDs for each customer, and want to graph customer segments overtime (defined various ways, but to start off I want to split into 2 segments - HighFrequencyCustomers, who have move than 6 transactions in the previous 12 months, and then all the rest).

TransactionsLast12Months = calculate(distinctcount(SalesRawData[TransactionCode]),filter(SalesRawData,SalesRawData[customerCode]<=max(SalesRawData[customerCode])),filter(all('Calendar'[Date]),'Calendar'[Date]>max('Calendar'[Date])-365 && 'Calendar'[Date]<=max('Calendar'[Date])))

IsHighFrequencyCustomer = [TransactionsLast12Months]>6

So far so good.

But how do I use the IsHighFrequencyCustomer as a filter or axis to be able to show the change over time in the mix between these two customer groups?

Would greatly appreciate guidance.

[Greg_Deckler]

So, you will need to employ the disconnected table trick. So, create a separate table that solely consists of the possible values of your measure. 

Then you will need a measure for each possible value, in your case it may be the same (the one from your post). Then you need a final measure like this:

Measures to Show = 
IF(
     HASONEVALUE(Employees[Employee]),
     SWITCH(
          VALUES(Attendance[Attendance]),
          "Attended",[Attended],
          "Not Attended",[NotAttended]
	 ),
	 MAX([Date])
)

In this example, Employees[Employee] is a column in the visual. The Attendance[Attendance] table/column is the disconnected table and there are two rows in it, "Attended" and "Not Attended". [Attended] and [NotAttended] are my two measures. 

[sumit4732]

Hi @srl01,

Below function should help, it will create a flag for frequent and other customers which can work as axis given you have customerID count in values.

test = var transCount =Calculate(distinctcount(SalesRawData[TransactionCode]),datesbetween(cal[date],max('Calendar'[Date])-365,max('Calendar'[Date]))) return
if(transCount>6,"FrequentVisitors","Others")

Please share sample data in case you face any problem in this

Thanks

-Sumit

[srl01]

@sumit4732Thank you! However I'm not sure what you proposed is functionally any different to what I had before (other than combining into a single measure definition).

The output is the same as what I was seeing previously.

Whereas I would want to see 

EndOfMonth	CustomerSegment	Count of CustomerID
January 2017	FrequentVisitors	200
January 2017	Others	50
February 2017	FrequentVisitors	180
February 2017	Others	70
... etc

 ...which I can then manipulate into area charts or whatever to show the trend visually between segments.

Data is very simple - SalesRawData has date, transaction code, item number, sale price, customerCode; then there is CustomerMaster which has customerCode, gender, date of birth etc.

Thank you!

[v-jiascu-msft · Accepted solution]

Hi @srl01,

You can try to summarize the data first. Then the other steps can be based on the summarized table.

1. Create a date table if there isn't one.

Datetable =
CALENDAR ( DATE ( 2010, 1, 1 ), DATE ( 2017, 12, 31 ) )

2. Establish a relationship between Date table and SalesRawData table.

3. Create a summary table.

SummaryTable =
SUMMARIZE (
    SalesRawData,
    'SalesRawData'[customerCode],
    'Date'[Date].[Year],
    'Date'[Date].[Month],
    "TransactionsLast12Months", CALCULATE (
        DISTINCTCOUNT ( SalesRawData[TransactionCode] ),
        DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -1, YEAR )
    )
)

4. Add a new column to the summary table. 

CustomerSegment =
IF ( [TransactionsLast12Months] > 6, "FrequentVisitors", "Others" )

5. Create a visual.

 

Best Regards!

Dale