Skip to main content

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

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.

Reply


Terms & Conditions