Count formula - Cohort analysis

[Creekleo]

Hello community,

I'm working on a cohort analysis that goes like this :

I want to calculate the retention rate of customers: in this case, I want to count the numbers of people that purchased in july 2021 ( unique Ids ) and then see if theses people came back to purchase again in august and septembre. ( i tried to explain in the file below )

this is just a simple example. my data set is huge ( over million row ). I just want to test a formula that will work.

thanks all.

New Microsoft Excel Worksheet.xlsx
	B	C	D	E	F	G	H	I	J	K
3	ID Customer	Date of operation	Month
4	IC686621	01/07/2021	7				Cohort	July - Start Month	August	Septembre
5	IC686621	01/07/2021	7				Jul-21	Count unique ID's of people that purchased in july	Count IDS of the july population that purchased again in august	Count IDS of the july population that purchased again in Septembre
6	IC686621	01/07/2021	7
7	IC686621	01/07/2021	7
8	IC686621	01/07/2021	7
9	IC686621	01/07/2021	7
10	IC686621	01/07/2021	7
11	IC686621	01/07/2021	7
12	IC686621	01/07/2021	7
13	IC686621	01/07/2021	7
14	IC686621	01/07/2021	7
15	IC686621	01/07/2021	7
16	IC7890	01/07/2021	7
17	IC7890	01/07/2021	7
18	IC7890	01/07/2021	7
19	IC7890	01/07/2021	7
20	IC7890	01/07/2021	7
21	IC7890	01/07/2021	7
22	IC7890	01/07/2021	7
23	IC7890	01/07/2021	7
24	IC7890	01/07/2021	7
25	IC7890	01/07/2021	7
26	IC68000	01/07/2021	7
27	IC68000	01/07/2021	7
28	IC68000	01/07/2021	7
29	IC68000	01/07/2021	7
30	IC68000	01/07/2021	7
31	IC68000	01/07/2021	7
32	IC68000	01/07/2021	7
33	IC68000	01/07/2021	7
34	IC68000	01/07/2021	7
35	IC68000	01/07/2021	7
36	IC68000	01/07/2021	7
37	IC68000	01/07/2021	7
38	IC68000	01/07/2021	7
39	IC68000	01/07/2021	7
40	IC68000	01/07/2021	7
41	IC68000	01/07/2021	7
42	IC68000	01/07/2021	7
43	IC68000	01/07/2021	7
44	IC68000	01/07/2021	7
45	IC68000	01/07/2021	7
46	IC68000	01/07/2021	7
47	IC68000	01/07/2021	7
48	IC68000	01/07/2021	7
49	IC68000	01/07/2021	7
50	IC68000	01/07/2021	7
51	IC68000	01/07/2021	7
52	IC68000	01/07/2021	7
53	IC686621	05/08/2021	8
54	IC686621	05/08/2021	8
55	IC686621	05/08/2021	8
56	IC686621	05/08/2021	8
57	IC686621	05/08/2021	8
58	IC686621	05/08/2021	8
59	IC686621	05/08/2021	8
60	IC686621	05/08/2021	8
61	IC686621	05/08/2021	8
62	IC686621	05/08/2021	8
63	IC686621	05/08/2021	8
64	IC686621	05/08/2021	8
65	IC68000	05/09/2021	9
66	IC68000	05/09/2021	9
67	IC68000	05/09/2021	9
68	IC68000	05/09/2021	9
69	IC68000	05/09/2021	9
70	IC68000	05/09/2021	9
71	IC68000	05/09/2021	9
72	IC68000	05/09/2021	9
73	IC68000	05/09/2021	9
74
75
76
Sheet1

Cell Formulas
Range	Formula
D4:D73	D4	=MONTH(C4)

 

[alansidman]

a power query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date of operation", type date}}),
    #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([Date of operation]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Month", {"ID Customer", "Month"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
in
    #"Grouped Rows"

 

[Creekleo]

a power query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date of operation", type date}}),
    #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([Date of operation]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Month", {"ID Customer", "Month"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
in
    #"Grouped Rows"

Thank you for your response mr. Alan.
I don't know how to apply this methode in power query. is it a vba code ? thank you

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[Creekleo]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Thank you . well explained.