Hi.
I figured out how to create a cohort analysis in a pivot table to show how many customers left each month, based on which month they started.
The table looks something like this:
[TABLE="class: outer_border, width: 512"]
<tbody>[TR]
[TD]Grand Total [TABLE="width: 500"]
<tbody>[TR]
[TD="class: outer_border"][/TD]
[TD="class: outer_border"]A[/TD]
[TD="class: outer_border"]B[/TD]
[TD="class: outer_border"]C[/TD]
[TD="class: outer_border"]D[/TD]
[TD="class: outer_border"]E[/TD]
[TD="class: outer_border"]F[/TD]
[/TR]
[TR="class: outer_border"]
[TD]1[/TD]
[TD][/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR="class: outer_border"]
[TD]2[/TD]
[TD]May[/TD]
[TD]26[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]144[/TD]
[/TR]
[TR="class: outer_border"]
[TD]3[/TD]
[TD]June[/TD]
[TD][/TD]
[TD]105[/TD]
[TD]12[/TD]
[TD]14[/TD]
[TD]256[/TD]
[/TR]
[TR="class: outer_border"]
[TD]4[/TD]
[TD]July[/TD]
[TD][/TD]
[TD][/TD]
[TD]251[/TD]
[TD]62[/TD]
[TD]389[/TD]
[/TR]
[TR="class: outer_border"]
[TD]5[/TD]
[TD]August[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]183[/TD]
[TD]249[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
F2 is how many new people shopped in May. B2 is from the people who shopped in may the first time, how many dropped out in may. C2 is from the people who shopped in may the first time, how many dropped out in June...and so on.
C3 is from the people who shopped in june the first time, how many dropped out in june. D3 is from the people who shopped in june the first time, how many dropped out in July...and so on.
My question is, how can I make that the values in columns B:E should be how many people were left in that month (which is f2-b2) Cell b2 would be 118 (144-26). That number as a percentage would be my rate of retention. (now it just shows me a number of people who left)
If it helps, my pivot table is set up like this:
Filters: City
Rows: Month of First Order
Columns: Month of Last Order
Values: Distinct count of Email Address
Can you help me with this?
I figured out how to create a cohort analysis in a pivot table to show how many customers left each month, based on which month they started.
The table looks something like this:
[TABLE="class: outer_border, width: 512"]
<tbody>[TR]
[TD]Grand Total [TABLE="width: 500"]
<tbody>[TR]
[TD="class: outer_border"][/TD]
[TD="class: outer_border"]A[/TD]
[TD="class: outer_border"]B[/TD]
[TD="class: outer_border"]C[/TD]
[TD="class: outer_border"]D[/TD]
[TD="class: outer_border"]E[/TD]
[TD="class: outer_border"]F[/TD]
[/TR]
[TR="class: outer_border"]
[TD]1[/TD]
[TD][/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR="class: outer_border"]
[TD]2[/TD]
[TD]May[/TD]
[TD]26[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]144[/TD]
[/TR]
[TR="class: outer_border"]
[TD]3[/TD]
[TD]June[/TD]
[TD][/TD]
[TD]105[/TD]
[TD]12[/TD]
[TD]14[/TD]
[TD]256[/TD]
[/TR]
[TR="class: outer_border"]
[TD]4[/TD]
[TD]July[/TD]
[TD][/TD]
[TD][/TD]
[TD]251[/TD]
[TD]62[/TD]
[TD]389[/TD]
[/TR]
[TR="class: outer_border"]
[TD]5[/TD]
[TD]August[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]183[/TD]
[TD]249[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
F2 is how many new people shopped in May. B2 is from the people who shopped in may the first time, how many dropped out in may. C2 is from the people who shopped in may the first time, how many dropped out in June...and so on.
C3 is from the people who shopped in june the first time, how many dropped out in june. D3 is from the people who shopped in june the first time, how many dropped out in July...and so on.
My question is, how can I make that the values in columns B:E should be how many people were left in that month (which is f2-b2) Cell b2 would be 118 (144-26). That number as a percentage would be my rate of retention. (now it just shows me a number of people who left)
If it helps, my pivot table is set up like this:
Filters: City
Rows: Month of First Order
Columns: Month of Last Order
Values: Distinct count of Email Address
Can you help me with this?
