dahamsta Posted February 5, 2015 Share Posted February 5, 2015 Is there a way of working out the annual revenue for a client group? I acquired a group of customers last year and I'd like to pay a finders fee to the former owner, but I have no idea of the value of the group of customers. In hindsight, I realise I should have made him an affiliate and attributed all of his former customers to him, but hindsight is 20/20. 0 Quote Link to comment Share on other sites More sharing options...
WHMCS Support Manager WHMCS John Posted February 6, 2015 WHMCS Support Manager Share Posted February 6, 2015 Hi, This isn't a feature of WHMCS as standard, but I imagine it would be possible to query this kind of data from the database. You could perhaps adapt one of the existing income reports to only include invoices from clients belonging to a certain client group? Documentation: http://docs.whmcs.com/Reports 0 Quote Link to comment Share on other sites More sharing options...
blakeh Posted February 14, 2015 Share Posted February 14, 2015 I just did this same calculation manually. Exported clients csv, active only exported services csv, active only loaded both into excel did vlookup formula to add group id to service line. Changed billing cycle from words (Annually, Monthly, etc..) to numeric (1, 12, etc..) added column for maths on billing amount times cycle sorted by group id subtotaled by group id did manual counting on client group screen because of no csv dump of client groups/ids, manually labeled in spreadsheet. Kind of a pain, took me about 10 min to finish, I realize not everyone wants to go to this length to mess with. 0 Quote Link to comment Share on other sites More sharing options...
dahamsta Posted June 23, 2015 Author Share Posted June 23, 2015 Finally got time to do this today: SELECT SUM(amountin) FROM tblaccounts WHERE userid IN (SELECT id FROM tblclients WHERE groupid = 5); And if you'd like it between two dates: SELECT SUM(amountin) FROM tblaccounts WHERE userid IN (SELECT id FROM tblclients WHERE groupid = 5) AND date BETWEEN '2013-09-01' AND '2014-09-01'; 0 Quote Link to comment Share on other sites More sharing options...
brian! Posted June 23, 2015 Share Posted June 23, 2015 on that last SQL query, shouldn't the second date be 2014-08-31 ? if there happens to be a transaction on 2014-09-01, and you run this query it will be included... but if you then wanted to run the same query for the following year (2014-09-01 - 2015-09-01), it would be included again. 0 Quote Link to comment Share on other sites More sharing options...
dahamsta Posted June 23, 2015 Author Share Posted June 23, 2015 It should, but I was in a hurry so I copied and pasted. 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.