Grouped customer based on purchase pattern

[Anonymous]

Hi. I want to group customers based on their purchase interval. For example, there's column customer_id, purchase_date, item_id, total_price. I want to group it like:

Daily customers: Customer A, D

Weekly Customers: Customer Y, V, Z, L

Monthly customers: Customer C, J, K

Once in 2 months: Customer O, P, R T, G

Once in 6 months: Customer F

Yearly: Customer S, W

does anyone know how to make this happen?

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Suppose the customer table is named as 'Test1'. Please new a date dimention table as below. And establish a one to many relationship between two tables based on date column.

date table = ADDCOLUMNS(CALENDAR(DATE(2018,1,1),DATE(2018,12,31)),"weekNo",WEEKNUM([Date],2))

Then, please add a calculated column in 'Test1' to check whether current customer purchased daily, weekly or monthly.

check =
VAR countdaily =
    CALCULATE (
        DISTINCTCOUNT ( 'date table'[Date] ),
        FILTER ( 'Test1', 'Test1'[customer_id] = EARLIER ( Test1[customer_id] ) ),
        USERELATIONSHIP ( 'date table'[Date], Test1[purchase_date] )
    )
VAR countweekly =
    CALCULATE (
        DISTINCTCOUNT ( 'date table'[weekNo] ),
        FILTER ( 'Test1', 'Test1'[customer_id] = EARLIER ( Test1[customer_id] ) ),
        USERELATIONSHIP ( 'date table'[Date], Test1[purchase_date] )
    )
VAR countmonthly =
    CALCULATE (
        DISTINCTCOUNT ( 'date table'[Date].[Month] ),
        FILTER ( 'Test1', 'Test1'[customer_id] = EARLIER ( Test1[customer_id] ) ),
        USERELATIONSHIP ( 'date table'[Date], Test1[purchase_date] )
    )
VAR countyearly =
    CALCULATE (
        DISTINCTCOUNT ( 'date table'[Date].[Year] ),
        FILTER ( 'Test1', 'Test1'[customer_id] = EARLIER ( Test1[customer_id] ) ),
        USERELATIONSHIP ( 'date table'[Date], Test1[purchase_date] )
    )
VAR checkeveryNmonth =
    DATEDIFF (
        CALCULATE (
            MAX ( Test1[purchase_date] ),
            FILTER (
                Test1,
                Test1[customer_id] = EARLIER ( Test1[customer_id] )
                    && Test1[purchase_date] < EARLIER ( Test1[purchase_date] )
            )
        ),
        [purchase_date],
        MONTH
    )
RETURN
    IF (
        countdaily = DISTINCTCOUNT ( 'date table'[Date] ),
        "Daily",
        IF (
            countweekly = DISTINCTCOUNT ( 'date table'[weekNo] ),
            "Weekly",
            IF (
                countmonthly = DISTINCTCOUNT ( 'date table'[Date].[Month] ),
                "Monthly",
                IF (
                    countyearly = DISTINCTCOUNT ( 'date table'[Date].[Year] ),
                    "Yearly",
                    IF (
                        checkeveryNmonth = 2,
                        "Once in 2 months",
                        IF ( checkeveryNmonth = 6, "Once in 6 months", "N/A" )
                    )
                )
            )
        )
    )

Best regards,

Yuliana Gu

[Anonymous]

Thank you, it works perfectly!