Based on monthly transactions, categorize each customer for each month into one of the following groups: New: A customer who has never made a purchase in previous months. Active: A customer who made a purchase this month and in the previous month. Inactive: A customer who did not make any purchase this month. Returning: A customer who made a purchase this month but did not make a purchase in the previous month.
📌 Challenge Details and Links
Challenge Number: 165
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Custom Grouping! Part 13 with Power Query
Power Query solution 1 for Custom Grouping! Part 13, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Group(Source, {"Month"}, {{"H", each [Customer]}}),
LG = Table.Combine(List.Skip(List.Generate(()=> [x = 0, In = {}],
each [x]Power Query solution 2 for Custom Grouping! Part 13, proposed by Alexis Olson:
let
Source = Table.TransformColumnTypes(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{{"Month", Int64.Type}, {"Customer", type text}, {"Quantity", Int64.Type}}
),
Months = Table.Skip(Table.Distinct(Table.SelectColumns(Source, {"Month"}))),
Customers = List.Distinct(Source[Customer]),
CrossJoin = Table.ExpandListColumn(
Table.AddColumn(Months, "Customer", each Customers),
"Customer"
),
Merge = Table.NestedJoin(
CrossJoin,
{"Customer"},
Source,
{"Customer"},
"Source",
JoinKind.LeftOuter
),
AddCols = Table.AddColumn(
Merge,
"Cols",
each [
L = [Source][Month],
Match = List.Contains(L, [Month]),
New = [Month] = List.Min(L),
Active = List.ContainsAll(L, {[Month], [Month] - 1}),
Inactive = not Match and List.Min(L) <= [Month],
Returning = Match and New = Active
]
),
Cols = {"New", "Active", "Inactive", "Returning"},
ExpandCols = Table.ExpandRecordColumn(AddCols, "Cols", Cols),
f = (T, col) => Text.Combine(Table.SelectRows(T, each Table.Column(_, col))[Customer], ", "),
GroupRows = Table.Group(
ExpandCols,
{"Month"},
List.Transform(Cols, (col) => {col, each f(_, col)})
)
in
GroupRowsPower Query solution 3 for Custom Grouping! Part 13, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content][[Month], [Customer]],
B = Table.AddColumn(
A,
"Clas",
each
let
a = List.Max(
Table.SelectRows(A, (x) => x[Customer] = [Customer] and x[Month] < [Month])[Month],
0
),
b = if [Month] - a = 1 then "Active" else if a = 0 then "New" else "Returning"
in
b
),
C = Table.Pivot(B, List.Distinct(B[Clas]), "Clas", "Customer", each Text.Combine(_, ", ")),
D = Table.AddColumn(
C,
"Inactive",
each
let
a = List.Distinct(Table.SelectRows(A, (y) => y[Month] < [Month])[Customer]),
b = Text.Split(Text.Combine(List.Skip(Record.ToList(_)), ", "), ", "),
c = Text.Combine(List.Difference(a, b), ", ")
in
c
),
E = Table.SelectColumns(D, {"Month", "New", "Active", "Inactive", "Returning"}),
F = Table.SelectRows(E, each ([Month] <> 1))
in
FPower Query solution 4 for Custom Grouping! Part 13, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, {"Month"}, {"V", each [Customer]}),
Recs = List.Generate(
() => [N = 0, V = Group[V]{0}, A = V],
each [N] < Table.RowCount(Group),
each [
N = [N] + 1,
V = Group[V]{N},
A = List.Distinct([A] & V),
Month = Group[Month]{N},
New = List.Difference(V, [A]),
Active = List.Intersect({V, [V]}),
Inactive = List.Difference(A, V),
Returning = List.Difference(V, New & Active)
],
each [[Month], [New], [Active], [Inactive], [Returning]]?
),
Res = Table.TransformColumns(
Table.Skip(Table.FromRecords(Recs), 1),
{"Month", each _},
each Text.Combine(_, ", ")
)
in
ResPower Query solution 5 for Custom Grouping! Part 13, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = List.Difference,
C = (x)=> Text.Combine(x, ", "),
D = Table.Group(A, "Month", {"x", each [Customer]}),
E = List.Accumulate(List.RemoveLastN(D[Month]), {}, (s,c) => let a = List.Distinct(List.Combine(List.FirstN(D[x],c))), b = D[x]{c}, d = D[x]{c-1}
in s & {c+1, C(B(b, a)), C(List.Intersect({d, b})), C(B(a, b)), C(List.RemoveItems(List.RemoveItems(b , d), B(b, a)))}),
F = Table.FromRows(List.Split(E,5), {"Month", "New", "Active", "Inactive", "Returning"})
in FPower Query solution 6 for Custom Grouping! Part 13, proposed by Glyn Willis:
let
Months = List.Buffer(List.Distinct(CT[Month])),
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
CT = Table.TransformColumnTypes(
Source,
{{"Month", Int64.Type}, {"Customer", type text}, {"Quantity", Int64.Type}}
),
Rec = Table.FromRecords(
List.Combine(
Table.Group(
CT,
{"Customer"},
{
{
"A",
each [
tbl = Table.Buffer(_),
item = List.First(tbl[Customer]),
result = List.Transform(
Months,
(r) =>
[
fP = List.Min(allM),
allM = List.Buffer(tbl[Month]),
Month = r,
N = if fP <> 1 and r = fP then item else null,
A =
if r
> fP
and List.Count(List.PositionOfAny(allM, {r, r - 1}, Occurrence.All))
= 2
then
item
else
null,
I = if r > fP and List.PositionOf(allM, r) = - 1 then item else null,
R =
if r
<> fP and List.PositionOf(allM, r)
<> - 1 and List.PositionOf(allM, r - 1)
= - 1
then
item
else
null
][[Month], [N], [A], [I], [R]]
)
][result],
type table
}
}
)[A]
)
),
Group = Table.Group(
Rec,
{"Month"},
{
{"New", each Text.Combine([N], ", "), type nullable text},
{"Active", each Text.Combine([A], ", "), type nullable text},
{"Inacticve", each Text.Combine([I], ", "), type nullable text},
{"Returning", each Text.Combine([R], ", "), type nullable text}
}
)
in
GroupPower Query solution 7 for Custom Grouping! Part 13, proposed by Vida Vaitkunaite:
let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = Table.RenameColumns(Table.FromList(List.Distinct(A[Month]), Splitter.SplitByNothing()), {"Column1", "Month"}),
C = Table.AddColumn(B, "Custom", each let
a = Table.RemoveColumns(A, "Quantity"),
b = Table.SelectRows(a, (x)=> x[Month]<=[Month]),
c = Table.Group(b, {"Customer"}, {{"Count", each Table.RowCount(_)}}),
d = Table.RemoveColumns(Table.RenameColumns(Table.SelectRows(a, (x)=> x[Month]=[Month]), {"Customer", "Cust"}), {"Month"}),
e = Table.Join(c, {"Customer"}, d, {"Cust"},1),
f = Table.RemoveColumns(Table.RenameColumns(Table.SelectRows(a, (x)=> x[Month]=[Month]-1), {"Customer", "Prev"}),{"Month"}),
g = Table.Join(e, {"Customer"}, f, {"Prev"},1),
h = Table.AddColumn(g, "Result", (x)=> if x[Count]=1 and x[Cust]<> null then "New" else if x[Count]>1 and x[Cust] <> null and x[Prev] <> null then "ACTIVE" else if x[Count]>=1 and x[Cust] = null then "Inactive" else "Returning"),
i = Table.Group(h, {"Result"}, {{"All", each Text.Combine(_[Customer], ", ")}})
in i),
D = Table.ExpandTableColumn(C, "Custom", {"Result", "All"}, {"Result", "All"}),
E = Table.SelectRows(Table.Pivot(D, List.Distinct(D[Result]), "Result", "All"), each ([Month] <> 1))
in
ESolving the challenge of Custom Grouping! Part 13 with Excel
Excel solution 1 for Custom Grouping! Part 13, proposed by Bo Rydobon 🇹🇭:
=LET(m,
B3:B11,
c,
C3:C11,
n,
XLOOKUP(
c,
c,
m
)=m,
y,
ISNA(
XMATCH(
c&m-1,
c&m
)
),
VSTACK({"Month",
"New",
"ACTIVE",
"Inactive",
"Returning"},DROP(GROUPBY(m,
HSTACK(REPT(
c,
n
),
REPT(
c,
1-y
),
MAP(m,
LAMBDA(i,
ARRAYTOTEXT(UNIQUE(FILTER(c,
(mExcel solution 2 for Custom Grouping! Part 13, proposed by Kris Jaganah:
=LET(a,
B3:B11,
b,
C3:C11,
c,
D3:D11,
d,
MAP(a,
b,
LAMBDA(x,
y,
MAX(FILTER(a,
(b=y)*(a1
),
i
),
1,
3,
2,
5,
4
)) Excel solution 3 for Custom Grouping! Part 13, proposed by JvdV -:
=LET(
m,
B3:B11,
c,
C3:C11,
y,
UNIQUE(
m
),
x,
TOROW(
UNIQUE(
c
)
),
f,
COUNTIFS,
z,
TOCOL(
DROP(
y&"|"&x&"|"&IFS(
f(
m,
"<="&y,
c,
x
),
IF(
f(
m,
"<"&y,
c,
x
)=0,
"New",
IF(
f(
m,
y,
c,
x
),
IF(
f(
m,
y-1,
c,
x
),
"Active",
"Returning"
),
"Inactive"
)
)
),
1
),
2
),
r,
REGEXEXTRACT,
CHOOSECOLS(
PIVOTBY(
r(
z,
"w+"
),
r(
z,
"w+$"
),
r(
z,
"|Kw+"
),
ARRAYTOTEXT,
,
0,
,
0
),
1,
4,
2,
3,
5
)
)Excel solution 4 for Custom Grouping! Part 13, proposed by Gabriel Pugliese:
=LET(m,
AB3:AB11,
c,
AC3:AC11,
q,
AD3:AD11,mct,
TOCOL(
UNIQUE(
m
)&TOROW(
UNIQUE(
c
)
)
),mm,
--LEFT(
mct
),
cc,
RIGHT(
mct
),qq,
XLOOKUP(
mct,
m&c,
q,
0
),base,
HSTACK(
mct,
mm,
cc,
qq
),fdate,
MAP(INDEX(
base,
,
3
),
LAMBDA(x,
MIN(FILTER(INDEX(
base,
,
2
),
(INDEX(
base,
,
4
)>0)*(INDEX(
base,
,
3
)=x))))),base2,
SORT(
HSTACK(
base,
fdate
),
{3,
2},
{1,
1}
),status,
MAP(SEQUENCE(
ROWS(
base2
)
),
LAMBDA(i,
IFS(INDEX(
base2,
i,
2
)=INDEX(
base2,
i,
5
),
"new",INDEX(
base2,
i,
2
)INDEX(
base2,
i,
5
))*(INDEX(
base2,
i,
4
)=0),
"inactive",(INDEX(
base2,
i-1,
4
)=0)*(INDEX(
base2,
i,
4
)>0),
"returning",(INDEX(
base2,
i-1,
4
)>0)*(INDEX(
base2,
i,
4
)>0),
"active"))),
base3,
HSTACK(
base2,
status
),base4,
FILTER(base3,
(INDEX(
base3,
,
6
)>"")*(INDEX(
base3,
,
2
)>1)),hh,
SORTBY(
TOROW(
UNIQUE(
INDEX(
base4,
,
6
)
)
),
{2,
3,
4,
1}
),rr,
REDUCE(hh,
UNIQUE(
INDEX(
base4,
,
2
)
),
LAMBDA(a,
v,
VSTACK(a,
MAP(hh,
LAMBDA(x,
ARRAYTOTEXT(FILTER(INDEX(
base4,
,
3
),
(INDEX(
base4,
,
6
)=x)*(INDEX(
base4,
,
2
)=v),
""))))))),HSTACK(
VSTACK(
"Month",
UNIQUE(
INDEX(
base4,
,
2
)
)
),
rr
)) Excel solution 5 for Custom Grouping! Part 13, proposed by Pieter de B.:
=LET(
b,
B3:B11,
c,
C3:C11,
n,
IF(
XLOOKUP(
c,
c,
b
)=b,
c,
""
),
a,
IF(
ISNA(
XMATCH(
c&b-1,
c&b
)
),
"",
c
),
i,
IF(
XMATCH(
b,
b
)=ROW(
b
)-2,
MAP(
b,
LAMBDA(
m,
LET(
y,
FILTER(
c,
b<=m,
""
),
ARRAYTOTEXT(
UNIQUE(
FILTER(
y,
ISNA(
XMATCH(
y,
FILTER(
c,
b=m
)
)
),
""
)
)
)
)
)
),
""
),
r,
IF(
LEN(
n
)+LEN(
a
),
"",
c
),
VSTACK(
{"Month",
"New",
"Active",
"Inactive",
"Returning"},
DROP(
GROUPBY(
b,
HSTACK(
n,
a,
i,
r
),
LAMBDA(
x,
TEXTJOIN(
", ",
,
x
)
),
,
0
),
1
)
)
)Solving the challenge of Custom Grouping! Part 13 with Python
Python solution 1 for Custom Grouping! Part 13, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "CH-165 Customer Grouping.xlsx"
input = pd.read_excel(path, usecols="B:D", skiprows=1, nrows=10)
test = pd.read_excel(path, usecols="F:J", skiprows=1, nrows=4).rename(columns=lambda x: x.split('.')[0])
grid = input.set_index(['Month', 'Customer']).unstack(fill_value=0).stack().reset_index()
def get_status(row, first_date):
if row['Month'] == first_date:
return "New"
if row['Quantity'] == 0:
return "" if row['Month'] < first_date else "Inactive"
return "Returning" if row['Quantity_lag'] == 0 else "ACTIVE"
grid['Quantity_lag'] = grid.groupby('Customer')['Quantity'].shift(1).fillna(0)
grid['FirstDate'] = grid.groupby('Customer')['Month'].transform(lambda x: x[grid['Quantity'] > 0].min())
grid['status'] = grid.apply(lambda row: get_status(row, row['FirstDate']), axis=1)
grid = grid[(grid['Month'] > 1) & (grid['status'] != "")]
grid = grid.groupby(['Month', 'status'])['Customer'].apply(', '.join).unstack().reset_index()
grid = grid[['Month', 'New', 'ACTIVE', 'Inactive', 'Returning']]
grid.columns.name = None
print(grid.equals(test)) # TrueSolving the challenge of Custom Grouping! Part 13 with R
R solution 1 for Custom Grouping! Part 13, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path <- "files/CH-165 Customer Grouping.xlsx"
input <- read_excel(path, range = "B2:D11")
test <- read_excel(path, range = "F2:J6")
grid <- expand.grid(unique(input$Month), unique(input$Customer)) %>%
left_join(input, by = c("Var1" = "Month", "Var2" = "Customer")) %>%
rename(Month = Var1, Customer = Var2) %>%
replace_na(list(Quantity = 0)) %>%
arrange(Customer, Month) %>%
mutate(FirstDate = min(Month[Quantity > 0]),
status = case_when(
Month == FirstDate ~ "New",
Month < FirstDate & Quantity == 0 ~ "",
Month != FirstDate & Quantity == 0 ~ "Inactive",
Month != FirstDate & Quantity > 0 & lag(Quantity) == 0 ~ "Returning",
Month != FirstDate & Quantity > 0 & lag(Quantity) > 0 ~ "ACTIVE",
TRUE ~ ""
), .by = Customer) %>%
filter(Month > 1, status != "") %>%
select(Month, Customer, status) %>%
summarise(Customer = str_c(Customer, collapse = ", "), .by = c(Month, status)) %>%
pivot_wider(names_from = status, values_from = Customer) %>%
relocate(New, .after = Month)
all.equal(grid, test, check.attributes = FALSE)
#> [1] TRUE
Solving the challenge of Custom Grouping! Part 13 with Google Sheets
Google Sheets solution 1 for Custom Grouping! Part 13, proposed by Peter Krkos:
PowerQuery solution:
https://docs.google.com/spreadsheets/d/1zR5IZLz8OT76vhaPEHfsPrw8-RDKnLyyqS49IJjdhFk/edit?pli=1&gid=1592914483#gid=1592914483