Creating a report to show inactive customers

If you have a lot of customers set up on your account, you might want to streamline them by taking out non-active customers. This could improve performance. It could also be used for marketing, perhaps creating a campaign for customers who have not been active in a while.

To create a report showing your active customers – see Creating a dashboard 2 – jobs per customer

To create a report showing inactive customers, read on for step by step instructions.

1. Define the time frame for an inactive customer and then download jobs over that period

The first step is to determine over what period you mean by inactive. i.e, those customers who have not done a job within the last year.

Once you have defined the relevant timeframe, run the completed jobs report for the period say 1st January to the current date.

For more information on generating a completed jobs report – see View all completed jobs 

Once the report has run, click the Excel icon or Google Sheets icon to download this information to Microsoft Excel or Google Sheets.

2. Download your list of customers

Run your Customer list report and download it to Microsoft Excel or Google Sheets.

For more information on how to download your customer list, see Customer details report.

3. Extract your inactive customers using Microsoft Excel or Google Sheets

Import your data into two tabs in Microsoft Excel or Google Sheets.

Completed jobs tab

Note: The customer column has been highlighted in the above screenshot. This is just to show which column we will use later.

Inactive customers

To highlight inactive customers

  1. Add a new column to the left of your imported customers data
  2. Use a VLOOKUP formula to find the customer name in the jobs report for each customer in your customer report. In this case, the formula is VLOOKUP(B7, ‘All jobs’, !F:F,1,false)
  3. Copy this filter and paste for each row 
  4. Add a filter to your able by selecting the range of data and then clicking the filter button

You should now have a list of customers and #N/As in the new column.  

The #N/As are where a customer could not be found in the jobs tab. These are your inactive customers as they had no job over the relevant time period.  You can use the filter on the first column to select just those rows that contain #N/A – your inactive customers.

Note: The above screenshots are from Google Sheets. The process is the same in Microsoft Excel.