How to Measure Average Jobs per Customer in Excel (Boost Repeat Revenue)

Average Jobs per Customer

1. Create a month-end column from the service date

Start by adding a helper column that converts each service date into the end of its month.

• Use the EOMONTH function
• Reference the event date or service date column
• Set it to return the end of the same month
• Fill the formula down for the full dataset

This gives you a consistent monthly grouping field for the rest of the analysis.

2. Build a unique list of months

Once the month-end column is ready, create a summary area for your monthly KPI view.

• Add a header for Month
• Use the UNIQUE function on the month-end helper column
• Make sure you reference the table field or table range correctly, not the full worksheet column
• Confirm the list returns one value per month

This becomes the monthly timeline for the KPI.

3. Add summary columns for customers and jobs

Next to the month list, create the structure for the calculations.

Add columns for:

• Active Customers
• Total Jobs
• Average Jobs per Customer
• Target

This sets up the table that will feed the chart.

4. Filter the customer list by month

To count active customers correctly, first pull the customers associated with each month.

• Use the FILTER function
• Filter the customer ID column based on the selected month in the summary table
• This returns all customers who had a job in that month

At this stage, the list may still contain duplicate customer IDs if a customer had multiple jobs.

5. Remove duplicate customers

Because the KPI needs unique active customers, the filtered list must be cleaned up.

• Wrap the filtered customer list with the UNIQUE function
• This removes repeat customer IDs within the same month
• The result is a true active customer list for that month

This ensures customers are only counted once per month, even if they had multiple jobs.

6. Count the number of active customers

After isolating the unique monthly customer list, count how many customers are in it.

• Use the COUNTA function
• Count the unique filtered customer IDs
• Copy the calculation down for all months

This gives you the monthly active customer count.

7. Count the total number of jobs

Now calculate how many jobs were completed in each month.

• Use the COUNTIF function
• Count how many rows in the month-end column match each month in the summary table
• Copy the result down through the full month list

Because each row represents one completed job, this gives you the total jobs for each month.

8. Calculate average jobs per customer

With both monthly counts in place, calculate the KPI itself.

• Divide total jobs by active customers
• Format the result with two decimals if needed
• Label the column as Average Jobs per Customer

This creates the monthly trend for the KPI.

9. Add a target line

To benchmark the KPI visually, add a target column.

• Enter a target value in the first row
• Repeat it down the full summary table by referencing the cell above or copying it down
• Keep the target consistent across all months

This gives you a clean comparison line for the chart.

10. Build the combo chart

Select the summary data and create a chart that shows both activity and the KPI trend.

• Highlight Month, Active Customers, Total Jobs, Average Jobs per Customer, and Target
• Go to Insert and open Recommended Charts
• Choose a Combo Chart
• Set Active Customers and Total Jobs as Clustered Column
• Set Average Jobs per Customer and Target as Line
• Place the KPI lines on the Secondary Axis

This creates a chart that combines volume metrics with the KPI trend line.

11. Format the KPI and target lines

After the chart is created, refine the line formatting so the KPI is easier to read.

• Click the line series and adjust the thickness
• Change the line style if needed, such as using a thinner or dotted line for the target
• Update the chart title to match the KPI name

This helps reduce clutter and makes the target easier to distinguish.

12. Add selective data labels

Instead of labeling everything, add labels only where they add value.

• Click the chart and add data labels only to the KPI line if needed
• To label a single point, click the line once, then click the specific point again
• Add labels only to important highs or lows
• Optionally format the label background and bold the text

This keeps the chart readable without making it noisy.

13. Clean up the axis formatting

Finish by simplifying the number display on the KPI axis.

• Double click the secondary axis
• Open the number settings
• Reduce the decimals to one place if that improves readability

This gives the chart a cleaner final look and makes the KPI easier to scan.