Membership Length

JBerr
JBerr Member
in Beast Mode

Hi new user here,

I have a question about getting the membership length of our customers and I am doing this as a beast mode for a card:

  • I need to find the start date of membership
  • I need to find the end date of membership
  • Finally the avg time they have been a member

I used the following case statement to try and find the start and end dates:

  • StartDate - Beast mode calculation
  • CASE WHEN countasUWCsignup= 1
    AND customer type= 'UWC'
    Then Date
  • EndDate - Beast mode calculation
  • CASE WHEN countasUWCsignup= 1
    AND customer type= 'UWC'
    Then Date
    • UWC is the membership type but I am not getting the correct start or end dates

If I do a filter on the card for using Customer type nd countasUWCsignup =1 I get the correct start date.

Thank you

Best Answers

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    Is there a unique ID for each customer? Is there a field that indicates a cancellation? If there is a single date column that would mean that signup and cancellation dates would be on separate rows in which case you would need to do what @MarkSnodgrass pointed out. What you've shared is not enough information to do what you are hoping.

    If I solved your problem, please select "yes" above

  • JBerr
    JBerr Member
    Answer ✓

    thank you

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited October 2023

    Without seeing your data or understanding its fields it is tough to know which fields you ought to use, however the calculation is pretty straightforward once you do:

    CASE WHEN `statusOfCustomer` = 'Active' THEN DATEDIFF(CURDATE(),`enrollmentDate`)
    WHEN `statusOfCustomer` = 'Cancelled' THEN DATEDIFF(`cancelledDate`,`enrollmentDate`) END

    This beastmode would give you the difference in days between those dates to calculate your average time they have been a member.

    If I solved your problem, please select "yes" above

  • ColemenWilson
    ColemenWilson Coach

    If you could provide a few sample rows of data with sensitive information obfuscated the community could likely help you figure it out.

    If I solved your problem, please select "yes" above

  • MarkSnodgrass
    MarkSnodgrass Coach

    If your start and end dates are in the same row, then it is pretty straightforward as @ColemenWilson points out. If your start and end dates are in the same column, but different rows, then you will need to use Magic ETL to get it consolidated so that the start and end dates are in the same row and separate columns.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • JBerr
    JBerr Member

    Hi Colemen and Mark,

    Here is some sample data:

    image.png

    You are either a member on a certain date until you expire and that is pretty much it is setup. Hope this helps.

    Thanks


  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    Is there a unique ID for each customer? Is there a field that indicates a cancellation? If there is a single date column that would mean that signup and cancellation dates would be on separate rows in which case you would need to do what @MarkSnodgrass pointed out. What you've shared is not enough information to do what you are hoping.

    If I solved your problem, please select "yes" above

  • JBerr
    JBerr Member
    Answer ✓

    thank you

Categories