Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Cohort analysis

Topic Labels: Formulas
2305 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Chris_Forster
5 - Automation Enthusiast
5 - Automation Enthusiast

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.