Membership Length
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
ANDcustomer type
= 'UWC'
ThenDate
- EndDate - Beast mode calculation
- CASE WHEN
countasUWCsignup
= 1
ANDcustomer type
= 'UWC'
ThenDate
- 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
-
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
0 -
thank you
0
Answers
-
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`) ENDThis 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
0 -
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
0 -
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
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
0 -
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
0 -
thank you
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive