From: Mark Wells Date: Thu, 13 Nov 2014 01:17:01 +0000 (-0800) Subject: customer churn report, #30132 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=f423d4c76d2bef990dc9abeb74256cab9968dd31 customer churn report, #30132 --- diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index 3a4a1695d..934287a15 100644 --- a/FS/FS/Report/Table.pm +++ b/FS/FS/Report/Table.pm @@ -664,7 +664,7 @@ sub cust_bill_pkg_discount { } -##### churn report ##### +##### package churn report ##### =item active_pkg: The number of packages that were active at the start of the period. The end date of the period is ignored. Options: @@ -756,7 +756,91 @@ sub pkg_where { @where; } -##### end of churn report stuff ##### +##### end of package churn report stuff ##### + +##### customer churn report ##### + +=item active_cust: The number of customers who had any active recurring +packages at the start of the period. The end date is ignored, agentnum is +mandatory, and no other parameters are accepted. + +=item started_cust: The number of customers who had no active packages at +the start of the period, but had active packages at the end. Like +active_cust, agentnum is mandatory and no other parameters are accepted. + +=item suspended_cust: The number of customers who had active packages at +the start of the period, and at the end had no active packages but some +suspended packages. Note that this does not necessarily mean that their +packages were suspended during the period. + +=item resumed_cust: The inverse of suspended_cust: the number of customers +who had suspended packages and no active packages at the start of the +period, and active packages at the end. + +=item cancelled_cust: The number of customers who had active packages +at the start of the period, and only cancelled packages at the end. + +=cut + +sub active_cust { + my $self = shift; + $self->churn_cust(@_)->{active}; +} +sub started_cust { + my $self = shift; + $self->churn_cust(@_)->{started}; +} +sub suspended_cust { + my $self = shift; + $self->churn_cust(@_)->{suspended}; +} +sub resumed_cust { + my $self = shift; + $self->churn_cust(@_)->{resumed}; +} +sub cancelled_cust { + my $self = shift; + $self->churn_cust(@_)->{cancelled}; +} + +sub churn_cust { + my $self = shift; + my ( $speriod ) = @_; + + # run one query for each interval + return $self->{_interval}{$speriod} ||= $self->calculate_churn_cust(@_); +} + +sub calculate_churn_cust { + my $self = shift; + my ($speriod, $eperiod, $agentnum, %opt) = @_; + + my $churn_sql = FS::cust_main::Status->churn_sql($speriod, $eperiod); + my $where = ''; + $where = " WHERE cust_main.agentnum = $agentnum " if $agentnum; + my $cust_sql = + "SELECT churn.* ". + "FROM cust_main JOIN ($churn_sql) AS churn USING (custnum)". + $where; + + # query to count the ones with certain status combinations + my $total_sql = " + SELECT SUM((s_active > 0)::int) as active, + SUM((s_active = 0 and e_active > 0)::int) as started, + SUM((s_active > 0 and e_active = 0 and e_suspended > 0)::int) + as suspended, + SUM((s_active = 0 and s_suspended > 0 and e_active > 0)::int) + as resumed, + SUM((s_active > 0 and e_active = 0 and e_suspended = 0)::int) + as cancelled + FROM ($cust_sql) AS x + "; + + my $sth = dbh->prepare($total_sql); + $sth->execute or die "failed to execute churn query: " . $sth->errstr; + + $self->{_interval}{$speriod} = $sth->fetchrow_hashref; +} sub in_time_period_and_agent { my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4); diff --git a/FS/FS/cust_main/Status.pm b/FS/FS/cust_main/Status.pm index e5803e0db..8f908a1d3 100644 --- a/FS/FS/cust_main/Status.pm +++ b/FS/FS/cust_main/Status.pm @@ -106,6 +106,42 @@ sub cancelled_sql { =back +=head1 CLASS METHODS + +=over 4 + +=item churn_sql START, END + +Returns an SQL statement for the customer churn status query. The columns +returned are the custnum and the number of active, suspended, and cancelled +packages (excluding one-time packages) at the start date ("s_active", +"s_suspended", and "s_cancelled") and the end date ("e_active", etc.). + +=cut + +# not sure this belongs here...FS::cust_main::Packages? + +sub churn_sql { + my $self = shift; + my ($speriod, $eperiod) = @_; + + my $s_sql = FS::h_cust_pkg->status_as_of_sql($speriod); + my $e_sql = FS::h_cust_pkg->status_as_of_sql($eperiod); + + my @select = ( + 'custnum', + 'COALESCE(SUM(s.is_active::int),0) as s_active', + 'COALESCE(SUM(s.is_suspended::int),0) as s_suspended', + 'COALESCE(SUM(s.is_cancelled::int),0) as s_cancelled', + 'COALESCE(SUM(e.is_active::int),0) as e_active', + 'COALESCE(SUM(e.is_suspended::int),0) as e_suspended', + 'COALESCE(SUM(e.is_cancelled::int),0) as e_cancelled', + ); + my $from = "($s_sql) AS s FULL JOIN ($e_sql) AS e USING (custnum)"; + + return "SELECT ".join(',', @select)." FROM $from GROUP BY custnum"; +} + =head1 BUGS =head1 SEE ALSO diff --git a/FS/FS/h_cust_pkg.pm b/FS/FS/h_cust_pkg.pm index 0c3db107f..423b44250 100644 --- a/FS/FS/h_cust_pkg.pm +++ b/FS/FS/h_cust_pkg.pm @@ -177,9 +177,57 @@ sub churn_fromwhere_sql { return ($from, @where); } +=head1 as_of_sql DATE + +Returns a qsearch hash for the instantaneous state of the cust_pkg table +on DATE. + +Currently accepts no restrictions; use it in a subquery if you want to +limit or sort the output. (Restricting within the query is problematic.) + +=cut + +sub as_of_sql { + my $class = shift; + my $date = shift; + "SELECT DISTINCT ON (pkgnum) * + FROM h_cust_pkg + WHERE history_date < $date + AND history_action IN('insert', 'replace_new') + ORDER BY pkgnum ASC, history_date DESC" +} + +=item status_query DATE + +Returns a statement for determining the status of packages on a particular +past date. + +=cut + +sub status_as_of_sql { + my $class = shift; + my $date = shift; + + my @select = ( + 'h_cust_pkg.*', + FS::cust_pkg->active_sql() . ' AS is_active', + FS::cust_pkg->suspended_sql() . ' AS is_suspended', + FS::cust_pkg->cancelled_sql() . ' AS is_cancelled', + ); + # foo_sql queries reference 'cust_pkg' in field names + foreach(@select) { + s/\bcust_pkg\b/h_cust_pkg/g; + } + + return "SELECT DISTINCT ON(pkgnum) ".join(',', @select). + " FROM h_cust_pkg". + " WHERE history_date < $date AND history_action IN('insert','replace_new')". + " ORDER BY pkgnum ASC, history_date DESC"; +} + =head1 BUGS -churn_fromwhere_sql fails on MySQL. +churn_fromwhere_sql and as_of_sql fail on MySQL. =head1 SEE ALSO diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index 61cdd746f..04c64905d 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -114,6 +114,7 @@ $report_customers{'List customers'} = [ \%report_customers_lists, 'List customer if $curuser->access_right('List all customers'); $report_customers{'Zip code distribution'} = [ $fsurl. 'search/report_cust_main-zip.html', 'Zip codes by number of customers' ]; $report_customers{'Customer signup report'} = [ $fsurl. 'graph/report_cust_signup.html', 'New customer signups by date' ]; +$report_customers{'Customer churn report'} = [ $fsurl.'graph/report_cust_churn.html', 'New customers, suspensions, and cancellations summary' ]; $report_customers{'Signup date report'} = [ $fsurl. 'graph/report_signupdate.html', 'Signup date report (by date of signup)' ]; $report_customers{'Advanced customer reports'} = [ $fsurl. 'search/report_cust_main.html', 'by status, signup date, agent, etc.' ] if $curuser->access_right('Advanced customer search'); diff --git a/httemplate/graph/cust_churn.html b/httemplate/graph/cust_churn.html new file mode 100644 index 000000000..07193c776 --- /dev/null +++ b/httemplate/graph/cust_churn.html @@ -0,0 +1,70 @@ +<& elements/monthly.html, + 'title' => $agentname. 'Customer Churn', + 'items' => \@items, + 'labels' => \@labels, + 'graph_labels' => \@labels, + 'colors' => \@colors, + 'links' => \@links, + #'params' => \@params, + 'agentnum' => $agentnum, + 'sprintf' => ( $normalize ? '%0.1f%%' : '%u'), + 'normalize' => ( $normalize ? 0 : undef ), + 'disable_money' => 1, + 'remove_empty' => 0, + 'nototal' => 1, + 'no_graph' => [ 1, 0, 0, 0, 0 ], # don't graph 'active' +&> +<%init> + +my $curuser = $FS::CurrentUser::CurrentUser; +die "access denied" + unless $curuser->access_right('List customers'); + +my( $agentnum, $agent ) = ('', ''); +if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $agentnum = $1; + $agent = qsearchs('agent', { 'agentnum' => $agentnum } ); +} else { + die "agentnum required"; # UI prevents this +} + +my $agentname = $agent ? $agent->agent.' ' : ''; + +my @base_items = qw(active_cust + started_cust + suspended_cust + resumed_cust + cancelled_cust ); + +my %base_labels = ( + active_cust => 'Active customers', + started_cust => 'New', + suspended_cust => 'Suspended', + resumed_cust => 'Resumed', + cancelled_cust => 'Cancelled', +); + +my %base_colors = ( + active_cust => '000000', #black + started_cust => '00cc00', #green + suspended_cust => 'ff9900', #yellow + resumed_cust => '4444ff', #light blue for some reason + cancelled_cust => 'cc0000', #red +); + +my %base_links; +foreach my $status (qw(active started suspended resumed cancelled)) { + $base_links{$status.'_cust'} = + "${p}search/cust_main_churn.html?agentnum=$agentnum;status=$status;"; +} + +# indirection in case at some point we need to add breakdown options +my (@items, @labels, @colors, @links, @params); +@items = @base_items; +@labels = @base_labels{@base_items}; +@colors = @base_colors{@base_items}; +@links = @base_links{@base_items}; + +my $normalize = $cgi->param('normalize'); + + diff --git a/httemplate/graph/report_cust_churn.html b/httemplate/graph/report_cust_churn.html new file mode 100644 index 000000000..3f942777f --- /dev/null +++ b/httemplate/graph/report_cust_churn.html @@ -0,0 +1,32 @@ +<% include('/elements/header.html', 'Customer Churn Summary' ) %> + +
+ + + +<& /elements/tr-select-from_to.html &> + +<& /elements/tr-select-agent.html, + 'curr_value' => scalar($cgi->param('agentnum')), + 'label' => 'For agent: ', + 'disable_empty' => 1, +&> + +<& /elements/tr-checkbox.html, + 'field' => 'normalize', + 'value' => 1, + 'label' => 'Show percentages' +&> + +
+ +
+
+ +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List customers'); + + diff --git a/httemplate/search/cust_main_churn.html b/httemplate/search/cust_main_churn.html new file mode 100755 index 000000000..59f92cb6f --- /dev/null +++ b/httemplate/search/cust_main_churn.html @@ -0,0 +1,120 @@ +<& elements/search.html, + 'title' => $title, + 'name' => 'customers', + 'query' => $query, + 'count_query' => $count_query, + 'header' => [ emt('#'), + FS::UI::Web::cust_header( + $cgi->param('cust_fields') + ), + { label => "Packages on $start_date", colspan => 3 }, + '', + '', + { label => "Packages on $end_date", colspan => 3 }, + '', + '', + ], + 'header2' => [ '', + map({ '' } (FS::UI::Web::cust_header())), + 'Active', + 'Suspended', + 'Cancelled', + 'Active', + 'Suspended', + 'Cancelled', + ], + 'fields' => [ + 'custnum', + \&FS::UI::Web::cust_fields, + 's_active', + 's_suspended', + 's_cancelled', + 'e_active', + 'e_suspended', + 'e_cancelled', + ], + 'color' => [ + '', + FS::UI::Web::cust_colors(), + # package colors here + '00CC00', + 'FF9900', + 'FF0000', + + '00CC00', + 'FF9900', + 'FF0000', + ], + 'style' => [ '', + FS::UI::Web::cust_styles(), + '' ], + 'align' => 'r'. FS::UI::Web::cust_aligns(). 'rrrrrr', + 'links' => [ + '', + ( map { $_ ne 'Cust. Status' ? $clink : '' } + FS::UI::Web::cust_header( + $cgi->param('cust_fields') + ) + ), + '', + ], +&> +<%init> + +my $curuser = $FS::CurrentUser::CurrentUser; + +die "access denied" + unless $curuser->access_right('List customers'); + +my($speriod, $eperiod) = FS::UI::Web::parse_beginning_ending($cgi); +my $start_date = time2str('%b %o, %Y', $speriod); +my $end_date = time2str('%b %o, %Y', $eperiod); + +my $agentnum; +if ($cgi->param('agentnum') =~ /^(\d+)$/) { + $agentnum = $1; +} + +# can't use this directly as it doesn't have any cust_main fields. +my $churn = FS::cust_main::Status->churn_sql($speriod, $eperiod); + +my $query = { + 'table' => 'cust_main', + 'select' => 'cust_main.*, churn.*', + 'addl_from' => " JOIN ($churn) AS churn USING (custnum) ", +}; +my $count_query = "SELECT COUNT(*) FROM cust_main JOIN ($churn) AS churn USING (custnum)"; +my @where; + +my $status = $cgi->param('status'); +my $title; +if ( $status eq 'active' ) { + $title = "Customers active on $start_date"; + push @where, "s_active > 0"; +} elsif ( $status eq 'started' ) { + $title = "Customers starting service, $start_date - $end_date"; + push @where, "s_active = 0 and e_active > 0"; +} elsif ( $status eq 'suspended' ) { + $title = "Customers suspended, $start_date - $end_date"; + push @where, "s_active > 0 and e_active = 0 and e_suspended > 0"; +} elsif ( $status eq 'resumed' ) { + $title = "Customers resuming service, $start_date - $end_date"; + push @where, "s_active = 0 and s_suspended > 0 and e_active > 0"; +} elsif ( $status eq 'cancelled' ) { + $title = "Customers cancelled, $start_date - $end_date"; + push @where, "s_active > 0 and e_active = 0 and e_suspended = 0"; +} + +if ($agentnum) { + push @where, "agentnum = $agentnum"; +} + +if ( @where ) { + my $where = " WHERE ". join(' AND ', @where); + $query->{extra_sql} = $where; + $count_query .= $where; +} + +my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; + +