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

 

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

     

     

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

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