help needed to create weekly cohorts

sbz87 · 2022-09-20T00:33:34+00:00

There was an error rendering this rich post.

I'm working with a large dataset that contains time-stamped user sign up data. I'd like to use ETL to define weekly cohorts based on when a user signs up. My goal is to use these cohorts as a foundation for beast mode calculations like "growth in weekly sign up cohorts over time", and when joined to tables containing product adoption data, "product adoption by cohort" type things.

I've been searching the archives to find something here and haven't found a solution - but if this is already documented, please point me in the right direction.

Find more posts tagged with

Accepted answers

All comments

GrantSmith

You can use a formula tile to calculate the first day of the week. I’ve done a write up on how to do this previously here https://dojo.domo.com/main/discussion/52687/domo-ideas-exchange-beast-modes-first-last-days-of-the-month-week#latest

jaeW_at_Onyx

@sbz87

Make your cohort_id the start of the week for the sign_up_date.

Before you apply that to a large dataset, you might want to use a RANK_WINDOW function to find the

"first row when a user signed up" (row_number order by date asc partition by user)

"first row when a user signed up with this product_type"

Then filter to row_number = 1

Then apply your cohort_id formula tile.

Now you can JOIN that back to your master transaction table (consider using DatasetViews if the performance is good enough)