Placeholder

Customer Forum

Identifying Customers who have stopped spending with you.

External Posted: 2012-11-02 14:50

 One of the benefits of having your customer transaction data in your CRM system is you can identify customer buying trends and have your sales team engage if appropriate.

To illustrate the point, I have created a report which shows how much a customer has purchased over the past two months by product category and calculates the percentage increase or decrease. 

As you can see from the example above, we can easily track which customers have stopped buying which products.  In this case Westland Business Services appear to have stopped buying 'Services' so your sales team could pick-up the phone and find out if there is a problem.

You could even write a Process Script to automatically create Activities for Sales People to follow-up if you wanted!

This report is built in the following way:

  1. Create a new Invoice Line Item Report
  2. Add the 'Party' Field from the Invoice - This is the customer.
  3. Add the product category.
  4. Create a calculated column to identify sales last month (see formula below).
  5. Create a calculated column to identify sales 2 months ago (see formula below).
  6. Create a calculated column to identify the percentage change (see formula below).
  7. Summarise the Report by Customer (Party Field).
  8. Summarise the report by Product Category.

 

Calculating the formulas

The 'Last Month' & '2 Months Ago' formula take a little SQL knowledge but basically they check to see if the invoice date is in last month or the previous month.  If they are then line item net amount (before tax) is totalled.  If not the value is 0.  The formula then is rounded to 0 decimal places using the ROUND function and then a pound sign is added using the CONCAT function.

The Change formula then calculates the percentage change.

 

 

 

Workbooks Support Posted: Fri, 09.11.2012 - 13:19

This report has now been published as a template report and is available to use within your Workbooks Database.

To create the report, go to:

Start > New > Report > From a template report > Select 'Comparison of sales by product category, by Customer'