Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Cohort analysis

Topic Labels: Formulas
1193 1
cancel
Showing results for 
Search instead for 
Did you mean: 

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

1 Reply 1

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")

06%20AM

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.