Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Grouped customer based on purchase pattern

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,...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

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

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's avatar
    Anonymous
    Not applicable
    7 years ago

    Thank you, it works perfectly!