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

     

     

sumit4732's avatar
sumit4732
Advocate II
8 years ago

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'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