none
RFM Ranking of customers or how to create equally sized quintiles, sorted by second variable? RRS feed

  • Question

  • I have a database of customers. Customer id, frequency and recency. Table looks like this. Frequency and Recency are calculated columns.

    The code I already have looks like below. The problem is that this splits the quintiles based on the sum of frequency. So the first quintile only has a few customer ids in it and the bottom one a lot. What I like to achieve is quintiles with equal numbers of customers but ranked by frequency. (it's also for quartiles but will fix that).

    Quartile (Frequencies) 2 = 
    VAR Frequency =
        SUM ( 'shopifyCustomers'[Frequency] )
    VAR CustomerList =
        GROUPBY (
            ALL ( 'shopifyCustomers' ),
            'shopifyCustomers'[Customer Id],
            "Val", SUMX ( CURRENTGROUP (), [Frequency] )
        )
    VAR Percentile50 =
        MEDIANX ( CustomerList, [Val] )
    VAR Percentile75 =
        MEDIANX ( FILTER ( CustomerList, [Val] >= Percentile50 ), [Val] )
    VAR Percentile25 =
        MEDIANX ( FILTER ( CustomerList, [Val] < Percentile50 ), [Val] )
    RETURN
        IF (
            Frequency >= Percentile75,
            "Q4",
            IF ( Frequency >= Percentile50, "Q3", IF ( Frequency >= Percentile25, "Q2", "Q1" ) )
        )

    Monday, April 29, 2019 9:42 PM