Report for monthly income for new customers

[EstebanC]

Hello,

 

I would like to know how can I do a report for the monthly income of new customers. I mean, excluding recurring invoices or old customers.

 

Thanks

[brian!]

one option would be to slightly modify the "New Customers" report and add a Totals column to it...

 

<?php

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

$reportdata["title"] = "New Customers";
$reportdata["description"] = "This report shows the total number of new customers, orders and complete orders and compares each of these to the previous year on the graph.";

$reportdata["tableheadings"] = array("Month","New Signups","Orders Placed","Orders Completed","Totals");

for ( $rawmonth = 1; $rawmonth <= 12; $rawmonth++ ) {

   $year2 = $year-1;
   $month = str_pad($rawmonth, 2, 0, STR_PAD_LEFT);

   $newsignups = get_query_val("tblclients","COUNT(*)","datecreated LIKE '$year-$month-%'");
   $totalorders = get_query_val("tblorders","COUNT(*)","date LIKE '$year-$month-%'");
   $completedorders = get_query_val("tblorders","COUNT(*)","date LIKE '$year-$month-%' AND status='Active'");
   $totalamount = get_query_val("tblorders","SUM(amount)","date LIKE '$year-$month-%' AND status='Active'");

   $newsignups2 = get_query_val("tblclients","COUNT(*)","datecreated LIKE '$year2-$month-%'");
   $totalorders2 = get_query_val("tblorders","COUNT(*)","date LIKE '$year2-$month-%'");
   $completedorders2 = get_query_val("tblorders","COUNT(*)","date LIKE '$year2-$month-%' AND status='Active'");

   $reportdata["tablevalues"][] = array($months[$rawmonth].' '.$year,$newsignups,$totalorders,$completedorders,formatCurrency($totalamount));

   if (!$show || $show=="signups") $chartdata['rows'][] = array('c'=>array(array('v'=>$months[$rawmonth]),array('v'=>(int)$newsignups),array('v'=>(int)$newsignups2)));
   if ($show=="orders") $chartdata['rows'][] = array('c'=>array(array('v'=>$months[$rawmonth]),array('v'=>(int)$totalorders),array('v'=>(int)$totalorders2)));
   if ($show=="orderscompleted") $chartdata['rows'][] = array('c'=>array(array('v'=>$months[$rawmonth]),array('v'=>(int)$completedorders),array('v'=>(int)$completedorders2)));

}

$chartdata['cols'][] = array('label'=>'Month','type'=>'string');
$chartdata['cols'][] = array('label'=>$year,'type'=>'number');
$chartdata['cols'][] = array('label'=>$year2,'type'=>'number');

$args = array();
if (!$show || $show=="signups") {
   $args['title'] = 'New Signups';
   $args['colors'] = '#3366CC,#888888';
}
if ($show=="orders") {
   $args['title'] = 'Orders Placed';
   $args['colors'] = '#DC3912,#888888';
}
if ($show=="orderscompleted") {
   $args['title'] = 'Orders Completed';
   $args['colors'] = '#FF9900,#888888';
}
$args['legendpos'] = 'right';

$reportdata["headertext"] = $chart->drawChart('Area', $chartdata, $args, '400px').
   '<p align="center">'.
       '<a href="reports.php'.$requeststr.'&show=signups">New Signups</a>'
       . ' | <a href="reports.php'.$requeststr.'&show=orders">Orders Placed</a>'
       . ' | <a href="reports.php'.$requeststr.'&show=orderscompleted">Orders Completed</a>'
   . '</p>';

$reportdata["yearspagination"] = true;

Edited December 13, 2015 by brian!

[James W]

I signed up to this community just to say thank you to Brian! for giving this code. I know this post was made back in 2015, but I added it to my WHMCS7 New Customers report and it worked flawlessly. This is such an important report I've needed for many years now and was never able to figure out. So thank you, you never know the impact you make on someone even years later... Very appreciative