Jump to content
leeyondking

Calculating Churn Rate

Recommended Posts

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.

Share this post


Link to post
Share on other sites
Posted (edited)

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.

churn.png.e210ba0fe95a14b622b7e1d115b7df9d.png

 

Edited by Kian

Share this post


Link to post
Share on other sites
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.

churn.png.e210ba0fe95a14b622b7e1d115b7df9d.png

 

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

Share this post


Link to post
Share on other sites

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?

 

 

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites

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)

 

 

churn-rate.thumb.png.22afc3395c6e2d5e65c6f4adef8529f4.png

 

 

Edited by Kian

Share this post


Link to post
Share on other sites

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

whmcs-churn-rate-report.thumb.png.64df2a47f5f86e0caf2e877c1d70be8b.png

 

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');

 

Share this post


Link to post
Share on other sites

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. 😄

Share this post


Link to post
Share on other sites

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.

startonmarch.PNG.e91788c4aa8c0ab4822fc541b454038d.PNG

 

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

exp.PNG.0b0063ad1106d4ffc0d967da3389b375.PNG

 

Edited by leeyondking

Share this post


Link to post
Share on other sites

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 by Kian

Share this post


Link to post
Share on other sites

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.

churn-fixed.png.1c7544d1966c6aacd5e8faa7a10c1c67.png

Edited by Kian

Share this post


Link to post
Share on other sites
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.

churn-fixed.png.1c7544d1966c6aacd5e8faa7a10c1c67.png

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.

Share this post


Link to post
Share on other sites
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 by Kian

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by leeyondking

Share this post


Link to post
Share on other sites
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 by Kian

Share this post


Link to post
Share on other sites
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). 

image.png.2f0335699237449cecb725e0ce35c557.png

 

 

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

image.png.f05d661a2a23b8c422ecc64c25739d32.png

 

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   1 member

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated