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, 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?

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

2 Replies

  • 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!