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

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

Edited by Kian

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.


×
×
  • 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