Seiya Posted January 30, 2011 Share Posted January 30, 2011 Hi, We need to report our customer attrition rates i.e. percentage of customers who do not choose to renew their recurring subscriptions in a given period. I'm battling to come up with MySQL queries for the following.. What % of customers on a monthly recurring plan have paid for a 2nd period What % of customers on a monthly recurring plan have paid for a 3rd period What % of customers on a monthly recurring plan have paid for a 4th period Any pseudo code or outline of the logic would be great appreciated from more experienced users. Anyone out there have any idea? 0 Quote Link to comment Share on other sites More sharing options...
Knyteguy Posted January 30, 2011 Share Posted January 30, 2011 It seems like you would need to add another field in MySQL under members. Then create a conditional that upon payment completion to update this field by 1. You could then create a query perhaps based on account creation date & cancelled status to see attrition rate. I don't see this as feasible for existing accounts, unless you would like to add the billing period number manually for every account. 0 Quote Link to comment Share on other sites More sharing options...
othellotech Posted January 30, 2011 Share Posted January 30, 2011 Find the tblhosting records where period=monthly For each of those find all invoiceitems for that hotsing id For each of those find the invoice and check the payment status 0 Quote Link to comment Share on other sites More sharing options...
Juanzo Posted January 13, 2013 Share Posted January 13, 2013 Sorry for bumping this thread, just wanted to let you know I've created a feature request here. 0 Quote Link to comment Share on other sites More sharing options...
WHMCS Chris Posted January 13, 2013 Share Posted January 13, 2013 Juanzo, I really like this request - I've put it on my list of things to look further into. While I cannot guarantee it will be made into the product, I may do another Custom Reporting blog post about it like this one: http://blog.whmcs.com/developers.php?t=66223 0 Quote Link to comment Share on other sites More sharing options...
Juanzo Posted January 13, 2013 Share Posted January 13, 2013 That's great news Chris, thanks! I've read a few articles about this, it seems there is not only 1 way to calculate this but Shopify clarifies this, explaining the importance of the real metrics vs vanity metrics and how to calculate this here. 0 Quote Link to comment Share on other sites More sharing options...
WHMCS Chris Posted January 13, 2013 Share Posted January 13, 2013 Juanzo, I read the article - it's very well done. Thanks again for the reference. Regards, 0 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.