Cohort analysis

[Chris_Forster]

Hi - I’m looking to create a cohort analysis of my user base.

I need to make the first column a week-long date range for (Sun-Sat). Each successive column would have the number of users who signed up during that week-long period that returned during the following weeks. The second column would be Week 0 (i.e. 100%), the third column Week +1, then Week +2, etc.

How do I do:

1. The date ranges in the first column
2. The successive columns to represent the following weeks?

Thank you all for any help!
Best,
Chris

[Justin_Barrett]

As far as the date ranges go, begin by adding an autonumber field. This will automatically number records beginning at 1, and incrementing from there.

Add a formula field named {Sunday} to calculate the Sunday date for your desired starting week. For example, if you wanted the first week to begin on Sunday, December 1, the {Sunday} field formula would be:

DATEADD(DATETIME_PARSE("DEC 1, 2019", "MMM D, YYYY"), {Autonumber} - 1, "weeks")

Then add a {Saturday} field to find the appropriate Saturday date for each week:

DATEADD(Sunday, 6, "days")

Finally, turn the primary field into a formula that shows both dates:

DATETIME_FORMAT(Sunday, "L") & " - " & DATETIME_FORMAT(Saturday, "L")

Without knowing more about the source and nature of the user data that you’re trying to track, it’s tough to offer suggestions on the extra columns.