Report for terminations / cancellations

[Chris74]

Hi, I was wondering if anyone here has produced a report that details when services were terminated?

 

What I'm trying to do is produce a list within a 30 day period of the number of services that were terminated during that date range and their value - in order to compare that to the new signups report. There seems to be no way in WHMCS of listing services that were terminated in a given period. If I look at the standard list of services, the filters don't offer a "termination date", so it isn't possible.

 

For me it's vital to be able to accurately compare non renewed services each month with new business and I'm yet to find a way in WHMCS to do that - which is very strange for a billing system.

 

I was using overdue invoices as a gauge for this, but it isn't really accurate enough.

 

I have services set to terminate 30 days after their due date if the invoice remains unpaid, so if in May there were for example 20 services terminated, those would be clients that did not renew their service in April. I could then compare the April new signups figures with the amounts from the terminated services to get an idea of how things balance out.

 

I know that the annual income report shows a monthly graph with overall income trends but I'm looking to drill down specifically to particular products.

[brian!]

Hi Chris,

 

i'm trying to think of a way to do this without the need to write a new report or work out the correct SQL query... they'd both be simple to do, but with WHMCS, there is often a quicker way.

 

one possible solution for you would be to tweak the Services report and add a Termination Date option to it...

 

$filterfields = array("id"=>"ID","userid"=>"User ID","clientname"=>"Client Name","orderid"=>"Order ID","packageid"=>"Product ID","server"=>"Server ID","domain"=>"Domain Name","dedicatedip"=>"Dedicated IP","assignedips"=>"Assigned IPs","firstpaymentamount"=>"First Payment Amount","amount"=>"Recurring Amount","billingcycle"=>"Billing Cycle","nextduedate"=>"Next Due Date","paymentmethod"=>"Payment Method","domainstatus"=>"Status","username"=>"Username","password"=>"Password","notes"=>"Notes","subscriptionid"=>"Subscription ID","suspendreason"=>"Suspend Reason","termination_date"=>"Termination Date");

that will add the option to the checkboxes and filter dropdowns... with that, you could then run a filtered search and choose services with termination dates for April 2016, by using the following...

 

 

that should give you a list of services that were terminated in April.

 

I know it's not quite the 30-day date range you were looking for, but if you wanted to add that option to a report, then it's just a SQL query using 'between' that would need adding to the code.

 

similarly for Cancellations, you can use "Status" (Domain Status) and filter on it for "Cancelled"... either by itself or along with the termination date.

[Chris74]

Hi Chris,

 

i'm trying to think of a way to do this without the need to write a new report or work out the correct SQL query... they'd both be simple to do, but with WHMCS, there is often a quicker way.

 

one possible solution for you would be to tweak the Services report and add a Termination Date option to it...

 

$filterfields = array("id"=>"ID","userid"=>"User ID","clientname"=>"Client Name","orderid"=>"Order ID","packageid"=>"Product ID","server"=>"Server ID","domain"=>"Domain Name","dedicatedip"=>"Dedicated IP","assignedips"=>"Assigned IPs","firstpaymentamount"=>"First Payment Amount","amount"=>"Recurring Amount","billingcycle"=>"Billing Cycle","nextduedate"=>"Next Due Date","paymentmethod"=>"Payment Method","domainstatus"=>"Status","username"=>"Username","password"=>"Password","notes"=>"Notes","subscriptionid"=>"Subscription ID","suspendreason"=>"Suspend Reason","termination_date"=>"Termination Date");

that will add the option to the checkboxes and filter dropdowns... with that, you could then run a filtered search and choose services with termination dates for April 2016, by using the following...

 

 

that should give you a list of services that were terminated in April.

 

I know it's not quite the 30-day date range you were looking for, but if you wanted to add that option to a report, then it's just a SQL query using 'between' that would need adding to the code.

 

similarly for Cancellations, you can use "Status" (Domain Status) and filter on it for "Cancelled"... either by itself or along with the termination date.

 

Thanks Brian, I appreciate your reply. I don't see a "Services" report.

 

I guess what I'm looking for is something similar to the "report for new customers" ( I think it's a third party report) which allows you to run the report on specific services and choose a date range. It gives totals of each different product ordered within that date range and the amounts.

 

I'm actually looking for exactly the same thing, but for services that have the status either "terminated" or "cancelled" and the date range would query those selected services for the actual date they were terminated, rather than the date they were set up.

 

With a bit of head scratching, I might be able to modify that one, based on your suggestion to use the search criteria of "termination_date" - so I'll have a look into that.

 

Thanks again for your reply.

[brian!]

Thanks Brian, I appreciate your reply. I don't see a "Services" report.

http://docs.whmcs.com/Reports#Services

it's in the Exports section at the bottom of the Reports page.

 

I guess what I'm looking for is something similar to the "report for new customers" ( I think it's a third party report) which allows you to run the report on specific services and choose a date range. It gives totals of each different product ordered within that date range and the amounts.

I think I can see it in the App Store, but it's commercial and I doubt that i've seen what it looks like - but generally creating new reports is pretty straightforward... it can be the SQL query that takes the time if it's cross-referencing multiple tables.

 

I'm actually looking for exactly the same thing, but for services that have the status either "terminated" or "cancelled" and the date range would query those selected services for the actual date they were terminated, rather than the date they were set up.

 

With a bit of head scratching, I might be able to modify that one, based on your suggestion to use the search criteria of "termination_date" - so I'll have a look into that.

it's probably worth noting that termination_date only works in the services report because it's querying the tblhosting table (where termination_date is one of the fields)... you can't just throw it into any report and expect it to find the field - it will need to use the appropriate query (which services.php is already using).

 

for searching for cancelled or terminated services, you should be able to use the services report... if you find it!

[websavers]

I've created a cancellation requests and churn rate report here that could help! https://github.com/websavers/WHMCS-Report-Cancellations

You can, of course, fork it and adapt to your needs. Or submit a pull request if you've got some useful changes to apply.