Calculating Churn Rate

[vpshove]

Has anyone able to get an insights for acknowledging their hosting  churn rate?

I have been appointed to fetch and output daily/month/yearly churn-rate for hosting services.

I do take a look and queries WHMCS's database 'tblhosting', count number of services and count a services that has been terminated. But I am stuck to the fact that i have no idea joining same table.

Basically, Services Churn Rate (daily/monthly/yearly) = Total of terminated services in (any daily/monthly/yearly) / Total of active services in (any daily/monthly/yearly)

I hope this also become a feature to give more insights within WHMCS. I will easily get to know what action should be taken to retain active services.

[Kian]

There's no need to join the same table. Just count by CASE. Try with this one.

SELECT (CONCAT(YEAR(regdate), "-", MONTH(regdate))) AS period, COUNT(CASE WHEN domainstatus = "Terminated" THEN 1 ELSE NULL END) AS terminatedAccounts, COUNT(CASE WHEN domainstatus = 'Active' THEN 1 ELSE NULL END) AS activeAccounts, (COUNT(CASE WHEN domainstatus = "Terminated" THEN 1 ELSE NULL END) / COUNT(CASE WHEN domainstatus = 'Active' THEN 1 ELSE NULL END) * 100) AS churnRate FROM tblhosting GROUP BY YEAR(regdate), MONTH(regdate) DESC

Here's a sample result.

 

Edited July 29, 2020 by Kian

[vpshove]

3 hours ago, Kian said:

There's no need to join the same table. Just count by CASE. Try with this one.

SELECT (CONCAT(YEAR(regdate), "-", MONTH(regdate))) AS period, COUNT(CASE WHEN domainstatus = "Terminated" THEN 1 ELSE NULL END) AS terminatedAccounts, COUNT(CASE WHEN domainstatus = 'Active' THEN 1 ELSE NULL END) AS activeAccounts, (COUNT(CASE WHEN domainstatus = "Terminated" THEN 1 ELSE NULL END) / COUNT(CASE WHEN domainstatus = 'Active' THEN 1 ELSE NULL END) * 100) AS churnRate FROM tblhosting GROUP BY YEAR(regdate), MONTH(regdate) DESC

Here's a sample result.

 

Seems good in which comes close to what i've did previously. However, it comes to a part where -> Product A was subscribed on January 2020, but was terminated on March 2020 do not count in. Which means, if it was terminated on March 2020, it should be counted in March 2020. This got my nerves impulse wrecking

[Kian]

Okay 🤔 One question just to make sure I got it right. I order the following services on Jan:

• Product A
• Product B
• Product C

I terminate them as follows:

• Product A Feb
• Product B Feb
• Product C Mar

Bonus: I order Product D on Feb.

What result are you expecting to see?

 

 

[vpshove]

58 minutes ago, Kian said:

Okay 🤔 One question just to make sure I got it right. I order the following services on Jan:

• Product A
• Product B
• Product C

I terminate them as follows:

• Product A Feb
• Product B Feb
• Product C Mar

Bonus: I order Product D on Feb.

What result are you expecting to see?

Following terminated on (respectively):

• Product A Feb
• Product B Feb
• Product C Mar

But the queries record/count the terminated services on January itself (From the month where it began to Active),

Supposedly, it should record/count the terminated services on respective month (For the month when it was terminated on)

[vpshove]

Here is the case study which I hope may be clearer to picture it out.

[Kian]

Please wait. I decided that the best way to calculate & display churn rate is via WHMCS report. I'm coding one so that you can see that directly from WHMCS (graphs included).

Edit: Okay, I've almost finished the script. Below there's a preview. I need you to confirm me that numbers make sense but first let me explain what every digit value means. Let's take Products Jan 2020 as example:

• -1 previous month variation
• 8.3% churn rate
• 1++ new purchase
• 2-- new terminations
• 23 No. of products (at the end of the month)

 

 

 

 

Edited July 31, 2020 by Kian

[Kian]

Done. Here's the preview (click to enlarge). There should be everything.

 

Here is the code but since this community doesn't allow me to edit posts after X hours, you can also find it on Github. I'm still making small changes. I don't like color scheme 😑

<?php

/**
 * Churn Rate
 *
 * @writtenby Kian
 *
 */

use WHMCS\Carbon;
use WHMCS\Database\Capsule;

if (!defined("WHMCS")) {
    die("This file cannot be accessed directly");
}

$dateFilter = Carbon::create($year, $month, 1);
$startOfMonth = $dateFilter->startOfMonth()->toDateTimeString();
$endOfMonth = $dateFilter->endOfMonth()->toDateTimeString();

$reportdata["title"] = 'Churn Rate for ' . $year;
$reportdata["description"] = "Rate at which customers stop doing business with you.";
$reportdata["yearspagination"] = true;
$reportdata["tableheadings"] = array(
    'Date',
    'Products',
    '<strong class="text-success"><i class="far fa-plus-square"></i></strong>',
    '<strong class="text-danger"><i class="far fa-minus-square"></i></strong>',
    '<strong><i class="fas fa-percentage"></i></strong>',
    'Domains',
    '<strong class="text-success"><i class="far fa-plus-square"></i></strong>',
    '<strong class="text-danger"><i class="far fa-minus-square"></i></strong>',
    '<strong><i class="fas fa-percentage"></i></strong>',
    'Overall',
    '<strong class="text-success"><i class="far fa-plus-square"></i></strong>',
    '<strong class="text-danger"><i class="far fa-minus-square"></i></strong>',
    '<strong><i class="fas fa-percentage"></i></strong>',
);

$reportvalues = array();

// Products/Services
$groupBy = Capsule::raw('date_format(`regdate`, "%Y-%c")');
$reportvalues['productsNew'] = Capsule::table('tblhosting')->whereYear('regdate', '=', $year)->where('domainstatus', 'Active')->groupBy($groupBy)->orderBy('regdate')->pluck(Capsule::raw('count(id) as total'), Capsule::raw('date_format(`regdate`, "%Y-%c") as month'));
$groupBy = Capsule::raw('date_format(`nextduedate`, "%Y-%c")');
$reportvalues['productsTerminated'] = Capsule::table('tblhosting')->whereYear('nextduedate', '=', $year)->where('nextduedate', '<=', $dateFilter->format('Y-m-d'))->whereNotIn('billingcycle', ['One Time', 'Completed'])->groupBy($groupBy)->orderBy('nextduedate')->pluck(Capsule::raw('count(id) as total'), Capsule::raw('date_format(`nextduedate`, "%Y-%c") as month'));
$activeProducts = Capsule::table('tblhosting')->where('domainstatus', 'Active')->pluck(Capsule::raw('count(id) as total'))[0];

// Domains
$groupBy = Capsule::raw('date_format(`registrationdate`, "%Y-%c")');
$reportvalues['domainsNew'] = Capsule::table('tbldomains')->where('status', 'Active')->whereYear('registrationdate', '=', $year)->groupBy($groupBy)->orderBy('registrationdate')->pluck(Capsule::raw('count(id) as total'), Capsule::raw('date_format(`registrationdate`, "%Y-%c") as month'));
$groupBy = Capsule::raw('date_format(`nextduedate`, "%Y-%c")');
$reportvalues['domainsTerminated'] = Capsule::table('tbldomains')->whereYear('nextduedate', '=', $year)->where('nextduedate', '<=', $dateFilter->format('Y-m-d'))->groupBy($groupBy)->orderBy('nextduedate')->pluck(Capsule::raw('count(id) as total'), Capsule::raw('date_format(`nextduedate`, "%Y-%c") as month'));
$activeDomains = Capsule::table('tbldomains')->where('status', 'Active')->pluck(Capsule::raw('count(id) as total'))[0];

for ($tmonth = 1; $tmonth <= 12; $tmonth++)
{
    if (date('Y') == $year AND $tmonth > str_replace('0', '', $month)): continue; endif;

    $date = Carbon::create($year, $tmonth, 1);
    $dateMonthYear = $date->format('M Y');
    $dateMonth = $date->format('M');
    $key = $year . '-' . $tmonth;

    // Products
    $activeProducts = $activeProducts + $reportvalues['productsNew'][$key];
    $productStart[$tmonth] = $activeProducts;
    $reportvalues['productsCumulative'][$key] = $activeProducts;
    $productsNew = isset($reportvalues['productsNew'][$key]) ? $reportvalues['productsNew'][$key] : '0';
    $productsTerminated = isset($reportvalues['productsTerminated'][$key]) ? $reportvalues['productsTerminated'][$key] : '0';
    $productsCumulative = isset($reportvalues['productsCumulative'][$key]) ? $reportvalues['productsCumulative'][$key] : '0';
    $productVariation = $productsNew - $productsTerminated;
    $productChurnRate = number_format(($productsTerminated / $productStart[($tmonth == '1' ? '1' : $tmonth - 1)]) * 100, 1, '.', '') + 0;

    // Domains
    $activeDomains = $activeDomains + $reportvalues['domainsNew'][$key];
    $domainStart[$tmonth] = $activeDomains;
    $reportvalues['domainsCumulative'][$key] = $activeDomains;
    $domainsNew = isset($reportvalues['domainsNew'][$key]) ? $reportvalues['domainsNew'][$key] : '0';
    $domainsTerminated = isset($reportvalues['domainsTerminated'][$key]) ? $reportvalues['domainsTerminated'][$key] : '0';
    $domainsCumulative = isset($reportvalues['domainsCumulative'][$key]) ? $reportvalues['domainsCumulative'][$key] : '0';
    $domainVariation = $domainsNew - $domainsTerminated;
    $domainChurnRate = number_format(($domainsTerminated / $domainStart[($tmonth == '1' ? '1' : $tmonth - 1)]) * 100, 1, '.', '') + 0;

    // Overall
    $activeOverall = $activeProducts + $activeDomains;
    $overallStart[$tmonth] = $activeOverall;
    $reportvalues['overallCumulative'][$key] = $activeOverall;
    $overallNew = $productsNew + $domainsNew;
    $overallTerminated = $productsTerminated + $domainsTerminated;
    $overallCumulative = $productsCumulative + $domainsCumulative;
    $overallVariation = $productVariation + $domainVariation;
    $overallChurnRate = $productChurnRate + $domainChurnRate;

    $reportdata['tablevalues'][] = array(
        $dateMonthYear,
        formatCell(array('col' => 'products=', 'variation' => $productVariation, 'start' => $productStart[($tmonth == '1' ? '1' : $tmonth - 1)], 'end' => $productStart[($tmonth == '1' ? '1' : $tmonth - 1)] + $productVariation)),
        formatCell(array('col' => 'products+', 'increase' => $productsNew)),
        formatCell(array('col' => 'products-', 'decrease' => $productsTerminated)),
        formatCell(array('col' => 'products%', 'churnRate' => $productChurnRate)),
        formatCell(array('col' => 'domains=', 'variation' => $domainVariation, 'start' => $domainStart[($tmonth == '1' ? '1' : $tmonth - 1)], 'end' => $domainStart[($tmonth == '1' ? '1' : $tmonth - 1)] + $domainVariation)),
        formatCell(array('col' => 'domains+', 'increase' => $domainsNew)),
        formatCell(array('col' => 'domains-', 'decrease' => $domainsTerminated)),
        formatCell(array('col' => 'domains%', 'churnRate' => $domainChurnRate)),
        formatCell(array('col' => 'overall=', 'variation' => $overallVariation, 'start' => $overallStart[($tmonth == '1' ? '1' : $tmonth - 1)], 'end' => $overallStart[($tmonth == '1' ? '1' : $tmonth - 1)] + $overallVariation)),
        formatCell(array('col' => 'overall+', 'increase' => $overallNew)),
        formatCell(array('col' => 'overall-', 'decrease' => $overallTerminated)),
        formatCell(array('col' => 'overall%', 'churnRate' => $overallChurnRate)),
    );

    $chartdata['rows'][] = array(
        'c'=>array(
            array('v' => $dateMonth),
            array('v' => (int)$productStart[($tmonth == '1' ? '1' : $tmonth - 1)] + $productVariation),
            array('v' => (int)$domainStart[($tmonth == '1' ? '1' : $tmonth - 1)] + $domainVariation),
            array('v' => (int)$overallStart[($tmonth == '1' ? '1' : $tmonth - 1)] + $overallVariation),
        )
    );
}

function formatCell($data)
{
    /**
     * @param       string      $col            Column type
     * @param       string      $variation      Monthly change
     * @param       string      $increase       New purchases
     * @param       string      $decrease       New terminations
     * @param       string      $start          No. of customers (at the start of the period)
     * @param       string      $end            No. of customers (at the end of the period)
     * @param       string      $churnRate      Churn Rate
     * @return      string                      Formatted HTML cell
     */
    $data['variation'] = ($data['variation'] ? $data['variation'] : '0');
    $data['increase'] = ($data['increase'] ? $data['increase'] : '0');
    $data['decrease'] = ($data['decrease'] ? $data['decrease'] : '0');
    $data['start'] = ($data['start'] ? $data['start'] : '0');
    $data['end'] = ($data['end'] ? $data['end'] : '0');
    $data['churnRate'] = ($data['churnRate'] ? $data['churnRate'] : false);

    if (in_array($data['col'], array('products+', 'domains+', 'overall+')))
    {
        if ($data['increase'])
        {
            return '<span>' . $data['increase'] . '</span>';
        }
        else
        {
            return '-';
        }
    }
    elseif (in_array($data['col'], array('products-', 'domains-', 'overall-')))
    {
        if ($data['decrease'])
        {
            return '<span>' . $data['decrease'] . '</span>';
        }
        else
        {
            return '-';
        }
    }
    elseif (in_array($data['col'], array('products=', 'domains=', 'overall=')))
    {
        if ($data['variation'] > '0')
        {
            $variation = '<small class="pull-right" style="opacity:0.8;">' . abs($data['variation']) . '<i class="fad fa-angle-double-up fa-fw text-success"></i></span>';
        }
        elseif ($data['variation'] < '0')
        {
            $variation = '<small class="pull-right" style="opacity:0.8;">' . abs($data['variation']) . '<i class="fad fa-angle-double-down fa-fw text-danger"></i></span>';
        }

        if ($data['start'] != $data['end'])
        {
            return $data['start'] . ' <i class="fas fa-angle-right fa-fw"></i> ' . $data['end'] . $variation;
        }
        else
        {
            return $data['start'];
        }
    }
    elseif (in_array($data['col'], array('products%', 'domains%', 'overall%')))
    {
        if ($data['churnRate'] > 0)
        {
            return '<span class="label label-danger">' . $data['churnRate'] . '%</span>';
        }
        else
        {
            return '-';
        }
    }
}

$chartdata['cols'][] = array('label'=>'Day','type'=>'string');
$chartdata['cols'][] = array('label'=>'Products','type'=>'number');
$chartdata['cols'][] = array('label'=>'Domains','type'=>'number');
$chartdata['cols'][] = array('label'=>'Overall','type'=>'number');

$args = array();
$args['legendpos'] = 'right';

$reportdata["headertext"] = $chart->drawChart('Area',$chartdata,$args,'400px');

 

[vpshove]

You're really a gearhead.

After taking a look and using this to view on my end.
I would like to know for the total [At the end of the month]
Such that as in the picture above,

On January, 23 total at the end of the month
On Feb, 24 at the end of the month,

- There is no plus minus on Feb, since 1 new and 1 terminate, how does it being 24. Maybe there is a reason for it which I was left out.
- The same goes for March as well however.

Next one would be,
- I think the active service for Jan, isn't supposed to be 1, or could be more (which has been terminated)
- I'm guessing that this has filtered the actual number of active service on Jan. I am thinking of (actual number of service activated) should be representing it there? Instead of (Current status of the service) that particular month.

Psst. Correct me if I am wrong. 😄

[vpshove]

Update:

I do notice that,  the amount of product do not start with small number when this hosting company just started, where the report generated was showing a large number (thousands) of active product.

For example, Started on March 2012 (8 years back where this hosting was started) has only few customer use our services maybe like less than hundreds but the report showing thousands of active services instead.

 

Another case is that, in 2020, the reports surely will generate until this August.

However, for the past year, it stops calculating churn on august as well

 

Edited August 2, 2020 by leeyondking

[Kian]

I noticed more than one problem. The core of the issue is that I'm starting the year with a wrong calculation. I'll fix it as soon as I can.

Edited August 2, 2020 by Kian

[Kian]

It was It was harder than I thought 🥶 I don't want to annoy you with details so let's get straight to the point. Can you confirm me that the following numbers are correct? Anyway you can also test it on your WHMCS. I updated the script on Github.

Edited August 2, 2020 by Kian

[vpshove]

11 hours ago, Kian said:

It was It was harder than I thought 🥶 I don't want to annoy you with details so let's get straight to the point. Can you confirm me that the following numbers are correct? Anyway you can also test it on your WHMCS. I updated the script on Github.

Yes the number in my case was correct for the year when it was started, (2012), March up until August, but then there is no terminated value anymore after august 2012.

This leads to only an increment of products without termination after august just like i mentioned above just to recap.

I can only confirm the value for when it is started is correct until august. I am looking at the code too and tested on WHMCS.

[Kian]

9 hours ago, leeyondking said:

I can only confirm the value for when it is started is correct until august.

Got it. There was an unnecessary filter by date <= current date on "Terminated" products/domains. I updated the script. It should work now.

Edit: I realized now that I was also counting "Free" products/services. I'm going to change the script so that they're not part of the report.

Edited August 3, 2020 by Kian

[vpshove]

On my end, using the latest code you've updated.

I changed the following line (49)

$products['active']['currentYear'] = Capsule::table('tblhosting')->whereYear('regdate', '=', $year)->where('domainstatus', 'Active')->whereNotIn('billingcycle', ['One Time', 'Completed', 'Free Account'])->groupBy($groupBy)->pluck(Capsule::raw('count(id) as total'), Capsule::raw('date_format(`regdate`, "%c") as month'));

TO

$products['active']['currentYear'] = Capsule::table('tblhosting')->whereYear('regdate', '=', $year)->whereNotIn('billingcycle', ['One Time', 'Completed', 'Free Account'])->groupBy($groupBy)->pluck(Capsule::raw('count(id) as total'), Capsule::raw('date_format(`regdate`, "%c") as month'));

Without "->where('domainstatus', 'Active')". From here I was able to get the churn rate percentage already for all row.

Reason for why i remove it, it is because we want to also know how many actual number of Service hosted (Even it is not active anymore). Then perform some kind of plus minus. 

Extra situation/use case we're trying to achieve:

At the end of the day, say that the latest one is August 2020 in Churn_Rate Report, I should be able to tally the latest number of Products with the total Active Products/Services in WHMCS->Clients->Products/Services->Filter Status to Active. 

Hopefully this is clear

[vpshove]

Do you mind explain on the column for getting the number of termination?

It was quite an interesting number figures from time to time after the updates from your output.

 

Here I wanna share some research i've gotten recently from how some determine churn rate.

Quote

let's say that your client is paying you $100/month. They have paid on July 1st so your MRR increased by $100 in July. When their account is terminated on 2nd July (e.g. manually or immediate cancellation), the service churn is not on 2nd July but on 31st July. Your MRR is reduced from 1st August and the actual **subscription churn** happens on 31st July because they have paid the entire month. If you'd want to review cancellation requests, there is a separate report on the Other category

 

Edited August 4, 2020 by leeyondking

[Kian]

6 hours ago, leeyondking said:

Without "->where('domainstatus', 'Active')".

I'm using this "where" to avoid counting as "Active" products/services with the following status: Suspended, Cancelled, Terminated, Fraud, Pending.

6 hours ago, leeyondking said:

At the end of the day, say that the latest one is August 2020 in Churn_Rate Report, I should be able to tally the latest number of Products with the total Active Products/Services in WHMCS->Clients->Products/Services->Filter Status to Active. 

Nope. WHMCS simply "counts" products grouping them by "domainstatus". For churn rate you need more than that. We need to know WHEN a specific product/service has been terminated. WHMCS doesn't store any "termination date" (at least not one that we can use) that's why I count "Terminated" products based on "nextduedate". If "nextduedate" is < "current date" no one renewed the product/service in question so I count it as "Terminated".

I don't think there's another way to do that. I mean if I count products/services just looking at "Terminated" and "Active" status I end up with products/services and domains from 2017 and 2018 and 2019 and 2020 and 2021 and 2022 (...) all combined. Calculating churn rate would be impossible since every month/year will be the same.

The only downside is that statistics for current month can't be accurate regarding "Terminated" stuff. In the latest version of the script I also added the following tooltip next to current month «Statistics for current month are inaccurate as renewals still have to occur». I hope it's clear.

Edited August 4, 2020 by Kian

[vpshove]

16 hours ago, Kian said:

I'm using this "where" to avoid counting as "Active" products/services with the following status: Suspended, Cancelled, Terminated, Fraud, Pending.

Hi, this is the latest code that I'm using. Here's the result so far.

This is when plus minus, for when only "Active" product vs "Terminate" products, I would ended up getting a large number of negative (Post Deduction). 

 

 

When I remove the active as filter for $products['active']['currentYear'], then the percentage of churn could be shown. 

 

 

[Kian]

I noticed the same on my test WHMCS. I'll let you know when I manage to find the source of the problem.

[Kian]

Edit: nvm. I'm still working on it

Edited August 5, 2020 by Kian

[Kian]

Okay I think I fixed it. I had to use more complex conditions. Moreover I restored the "variation" data that was accidentally removed.

[baymax]

Guys...thank you so much for working on this. I have been doing this the excel way for a few months now. Then I saw this and added it to our collection of reports.

I am just cross checking our numbers for Domains but it doesn't look like it's showing the right count.

I haven't looked at Products/Services yet but that'll be next. It'll be nice too if we can filter by Product Groups.

Anyhow, back to Domains.

I've got a total of 1402 Active domains but it's showing 1411 as of today. I am not sure why that is or if I missed anything.

I should mention I have 2 domains in Transferred Away status and 19 marked as Cancelled but even with that, numbers don't add up...

THANK YOU!

[vpshove]

On 8/6/2020 at 5:21 AM, Kian said:

Okay I think I fixed it. I had to use more complex conditions. Moreover I restored the "variation" data that was accidentally removed.

Guess what, you need to get your free coffee from me,

I did double checking, but is done in phpmyadmin for the number of the total terminate services (based on next due date). It do make sense to me now (which I hardly get to know the reasoning)

However (again),

the number of terminate, are grouped in month and will be totalled (which is what we see now). 

I'll update this to another version, which can be seen in daily mode.

[Kian]

It has been brought to my attention that Diego Fabiano (Altomarketing)  stole this code that is 100% free and open source to make it a commercial module that costs 19$. It is listed on WHMCS Marketplace. At the moment the purchase link goes to a 404 but it is possible that since June 2021 someone bought it so go get your refund if possible.

I underline once again that is code is 100% free and can be found on my Github page.

Geez, I will never understand how some people have the nerve to steal open source code and sell it for real money.

Now I understand why months ago I received strange emails. Some poeple probably bought the script from this genius convinced that I was the seller.

Edited May 9, 2022 by Kian

[Bigol'tastynuggets]

What a D**k I hope nobody buys any of his work in future!