cancel
Showing results for
Did you mean:
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Grouped customer based on purchase pattern

Hi @nzr,

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
Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team

## Re: Grouped customer based on purchase pattern

Hi @nzr,

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
Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

## Re: Grouped customer based on purchase pattern

Thank you, it works perfectly!