help needed to create weekly cohorts

sbz87
sbz87
in Magic ETL

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.

Answers

  • GrantSmith
    GrantSmith Coach

    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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

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

    or

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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories