Forum Discussion

srl01's avatar
srl01
Helper II
8 years ago
Solved

Customer segmentation over time

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 se...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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

     

     

srl01's avatar
srl01
Helper II
8 years ago

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's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

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