Track dropped customers

Create a new report to identify key customers who are no longer doing business with your company.  By identifying these customers as early as possible, the sales department can initiate contacts to determine why the customers are apparently switching their purchases elsewhere, and preferably before they are locked in to the products or services of a new supplier.

To create a report to track dropped customers:

  1. Determine which customers create sufficient sales volume to be worthy of detailed tracking; this can be accomplished by exporting a sales by customer report to Excel, sorting it by dollar volume, and retaining for further analysis the top 20% of all customers that typically comprise 80% of all sales volume.
  2. For this reduced set of customers, access the customer orders database and create a report that shows the exact dates on which the last three orders were placed by each customer.  A quick examination of this information will reveal the average frequency with which each customer places orders.  Then load the average ordering frequency (in days) into an unused field in the customer master file.
  3. Create a custom report that adds the average ordering frequency in days to the last date on which an order was placed, and determines which customers have not ordered within their standard ordering interval.  The report should be sorted so that those customers with the longest ordering delays beyond their normal ordering intervals appear at the top of the report.

Also, consider enhancing the report by showing annual purchasing volume for each customer in the report.  If a report shows a customer with a combination of large sales volume and a significant ordering delay, then the sales manager should immediately contact that customer to see if there is a problem.

Finally, be sure to conduct an annual update of the average ordering frequency calculation, and load any changes into the customer master file.  This procedure maintains the accuracy of the report.

In short, a report that reveals any customers who may be in the process of switching their business elsewhere is an excellent addition to the standard corporate reporting package.