Report Of the Week - Customer Retention Report

Report Of the Week - Customer Retention Report

This week we will see how to create a Customer Retention report based on your CRM data. This lets you know how better you are engaging with your existing customers and make them continue buying products or services.  


 

Here we will use Zoho CRM data as an example. This can also be achieved in Salesforce CRM, Microsoft Dynamics CRM, and HubSpot CRM by slightly modifying the queries.  

To create the customer retention report, you need to create the following Query Tables. 

  • Customer Start Date
  • Invoice and Recurring Month
  • Customer Count by Month

Customer Start Date 

This Query Table obtains information on the date of subscription for each customer.

SELECT

"Customer ID",

STR_TO_DATE( min ( "Date of Subscription" ),  '%Y-%m-%d' )  "Date" /* fetches the start date from subscription created time */

FROM    "Invoice" 

GROUP BY   "Customer ID" 



Invoice and Recurring Month 

This Query Table obtains information on the month and year of the customer's subscription, along with Invoice details.

SELECT

INV. "Invoice ID" "Invoice ID" ,

INV. "Date of Subscription" "Date" ,

INV. "Customer ID" "Customer ID" ,

INV. "Invoice Owner" "Invoice Owner" ,

STR_TO_DATE(CUS. "Date" ,  '%Y-%m-%d' ) 'Min Date' ,

date_format(CUS. "Date" , '%Y%m' )  "Month and Year" ,  /*Extracts the year and month */

period_diff(date_format(INV. "Date of Subscription" ,  '%Y%m' ), date_format(CUS. "Date" , ' %Y%m' )) ' Month'    /*Finds the duration in months */

FROM    "Customer Start Date" CUS

INNER JOIN   "Invoice" INV  ON INV. "Customer ID" = CUS. "Customer ID"   



Customer Count by Month 

This Query Table obtains the month-wise Customer Count. 

SELECT

date_format( "Date" ,  '%Y%m' ) "Month and Year" ,

count ( "Customer ID" )  "Customer Count"

FROM    "Customer Start Date"  

GROUP BY    "Month and Year" 



Join Tables 

Create a lookup relationship between the Invoice and Recurring Month table and the Customer Count by Month using the Month & Year column.

Create Aggregate Formula

Create an aggregate formula as Recurring % in the Invoice and Recurring Month table using the below formula. 

count("Invoice and Recurring Month"."Invoice ID")*100/avg("Customer Count by Month"."Customer Count")

Create Pivot

Now create a new Pivot View over the Customer Count by Month Query Table by following the below steps.
  1. Click New icon ans select New Pivot View.
  2. Drag and drop the columns into the selves as given below:
    • Column - Month with Actual (Dimension) from Invoice and Recurring Month table.
    • Row - Drop columns as follow.
      • Min Date with Month&Year  from Invoice and Recurring Month table.
      • Customer Count with Actual (Dimension) from Customer Count by Month table.
    • Data - Recurring % with Actual from Invoice and Recurring Month   table.


  3. In the Filters tab add Month from Invoice and Recurring Month table and Exclude Items 0 from the report.


  4. Use the Click Here to Generate Pivot button to create the Pivot View. The report will be generated.
  5. Hide the Sub-totals and Grand total.
  6. Click the cell and select Conditional Formatting . Set the Conditions to highlight the Customer Retention rate.


Your Customer Retention Report is created.