CODEWITHSUNDEEP
excelpivot-tableretention
franzi
franzi

Reputation: 27

Cohort in Excel with aggregated monthly data

I'm trying to make a cohort in Excel Pivot with a dataset having:

aggregated number of monthly sign ups (month by month), aggregated number user of completed next step, number of months between sign up and the next action taken.

What I can't figure out when i do the pivot to have the cohort, is what to put into the value field in the pivot? Normally I would take the Customer IDs as value, but since I only have the data on aggregated monthly level I'm not sure if i put the number of sign ups or the number of next step completed?

Also how do I get the sum of each cohort so i can calculated the retention rate?

I hope this make sense.

Signup month Action completed month Months between sign up and action completed signups conversion to Action completed

Jan-17  Sep-18  20  95  71
Jan-17  Jan-18  12  95  77
Jan-17  Jun-18  17  96  72
Jan-17  Jan-18  12  92  78
Jan-17  Dec-18  23  91  78
Jan-17  Jul-18  18  100 73
Jan-17  Oct-18  21  92  79
Jan-17  Feb-18  13  95  70
Jan-17  Jan-18  12  91  79
Jan-17  May-18  16  93  71
Jan-17  Jun-18  17  95  72

Upvotes: 0

Views: 119

Answers (1)

Jenn
Jenn

Reputation: 647

Is this what you are looking to achieve?

enter image description here

REVISION #1

This layout shows the total number of signups, by the month in which the signup occurred, distributed by the number of months btwn the signup and action completed. The action completed month may be omitted and will still achieve the same result; it is there FYI only.

enter image description here

REVISION #2

This is an example of the average months between the signup and action. Is this what you are looking for?

enter image description here

enter image description here

Upvotes: 0

Related Questions