Membership Length

JBerr

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

ColemenWilson

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.

JBerr

thank you

ColemenWilson

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.

ColemenWilson

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

MarkSnodgrass

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.

JBerr

Hi Colemen and Mark,

Here is some sample data:

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

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.

JBerr

thank you