Multi-product customer analysis

[egspen2]

I have a list of customers including the products they bought, amount they paid for each product, and the month they bought that product. I'm trying to put together an analysis that shows (for example) "when a customer buys product 10, they buy these other, related products in the periods after". Basically trying to show a list of customers that bought product 10 and then show all other sales after that product 10 purchase over time. Attached is my current data source, just having trouble trying to figure out the right pivot / table / presentation to accomplish this.

Unable to get the xl2bb add-in to work so here is a snip of a few rows of the data:

 

[coulombevin]

Hi @egspen2 ,

Here's what i would do for start:

Classeur1
	A	B	C	D	E	F	G
1	Product	Name	Amount	End of Month		First bought	Bought after
2	Prod3	Customer10	-27,45	2024-01-22		Prod1	Prod3;Prod2
3	Prod1	Customer10	749,40	2024-05-22		Prod1	Prod2
4	Prod5	Customer2	159,96	2024-05-22		Prod1	Prod3
5	Prod3	Customer2	129,98	2024-05-22		Prod3	Prod1;Prod2
6	Prod5	Customer4	563,15	2024-05-22		Prod3	Prod1
7	Prod3	Customer10	26,97	2024-06-22		Prod3	Prod2
8	Prod5	Customer8	-19,50	2024-06-22		Prod3	Prod1
9	Prod3	Customer2	141,98	2024-06-22		Prod5	Prod3;Prod1
10	Prod1	Customer3	799,60	2024-06-22		Prod5	Prod4
11	Prod2	Customer10	-1,05	2024-07-22
12	Prod2	Customer9	500,10	2024-09-22
13	Prod5	Customer4	-0,34	2024-10-22
14	Prod4	Customer8	-0,30	2024-11-22
15	Prod1	Customer2	-3049,95	2024-12-22
16	Prod2	Customer3	255,00	2025-01-22
17	Prod2	Customer3	495,00	2025-01-22
18	Prod2	Customer9	535,00	2025-01-22
19	Prod2	Customer5	320,00	2025-01-22
20	Prod3	Customer2	535,00	2025-01-22
21	Prod5	Customer7	395,00	2025-01-22
Feuil1

Cell Formulas
Range	Formula
F2:G10	F2	=LET(listProduct,A2:A21, listCustomer,B2:B21, listMonth,D2:D21, tbl,SORT(HSTACK(SEQUENCE(MAX(ROWS(listProduct),ROWS(listCustomer),ROWS(listMonth))),listProduct,listCustomer,listMonth),4,1), getProduct,LAMBDA(tbl,orderId,LET(orderInfo,FILTER(tbl,CHOOSECOLS(tbl,1)=orderId,""), tempTbl,DROP(tbl,MATCH(orderId,CHOOSECOLS(tbl,1),0)), TEXTJOIN(";",TRUE,UNIQUE(CHOOSECOLS(FILTER(tempTbl,(CHOOSECOLS(tempTbl,2)<>INDEX(orderInfo,1,2))*(CHOOSECOLS(tempTbl,3)=INDEX(orderInfo,1,3)),""),2))))), listProductBoughtAfter,SORT(DROP(REDUCE("",SEQUENCE(ROWS(tbl)),LAMBDA(t,r,VSTACK(t,HSTACK(INDEX(tbl,r,2),IFERROR(getProduct(tbl,r),""))))),1),1,1), FILTER(listProductBoughtAfter,CHOOSECOLS(listProductBoughtAfter,2)<>""))
Dynamic array formulas.

As you can see, it output a table with product and other item that were bought after. The function will loop all rows, find the customer order after the one of the row and remove the one with the same product and return a concatenated list of items that are different and were bought after the order.

I'm not quite sure how you want to output this but don't hesitate to reply on what you would like to see next!

Bests regards,

Vincent