How to Track Repeat Service Rate % in Excel
for Customer Retention

How to Calculate Repeat Service Rate % in Excel

1. Create the Month Field from the Service Date

• Start by adding a helper column that converts each service date into its month-end date.
• Use the EOMONTH function to generate the end of month for every service record.
• This gives you a clean monthly grouping field that can be used for all the calculations that follow.
• Format the result as a date if Excel displays it as a number.

2. Build a Unique List of Months

• Create a separate list of reporting months using the new month field.
• Use the UNIQUE function to return one value for each month in the dataset.
• If your months are not already in order, use the SORT function with the unique list so they appear chronologically.
• This becomes the monthly structure for your KPI table.

3. Set Up the Reporting Columns

Create a small summary table with these columns:

• Month
• Customers
• Repeat
• Repeat Rate %

This gives you one row per month and a clean layout for the chart later.

4. Count the Number of Unique Customers per Month

• To get the total customer count for each month, first isolate the customer IDs that belong to that month.
• Use the FILTER function to return only the customer IDs tied to the selected month.
• Then wrap that result in the UNIQUE function so duplicate customer IDs are removed.
• Finally, use COUNTA to count how many unique customers appear in that filtered list.
• Fill the formula down for all months.

This ensures you are counting customers, not jobs, invoices, or service visits.

5. Identify Repeat Customers Without Double Counting

• For the repeat column, the goal is to count only customers whose service date is later than their first-ever service date.
• Start by filtering customer IDs for the selected month.
• Then apply a second condition so only customer records with a service date greater than that customer’s earliest service date are included.
• Use FILTER to apply both conditions together.
• Use MINIFS to find the first service date for each customer.
• The logic checks whether the service date is greater than that earliest date, which identifies a repeat customer.
• This approach prevents customers from being counted as repeat on their very first service.
• It also avoids double counting when a customer has multiple records tied to the same service period or job activity.

6. Calculate the Repeat Service Rate

• Once you have monthly customer counts and monthly repeat counts, calculate the repeat rate by dividing repeat customers by total customers.
• Format the results as a percentage using Ctrl + Shift + 5.
• Fill the formula down through the full month list.

This gives you the monthly Repeat Service Rate % trend.

7. Create the Combo Chart

• Select the completed summary table.
• Go to Insert and choose Recommended Charts.
• Switch to Combo Chart so you can assign different chart types to each metric.
• Use a clustered column chart for:
  • Customers
  • Repeat
• Use a line chart for:
  • Repeat Rate %
• Place the percentage line on the secondary axis so it is readable next to the customer counts.

This creates a visual with:

• Blue columns for total customers
• Red columns for repeat customers
• A line for Repeat Service Rate %

8. Adjust the Chart to Focus on the Right Time Period

• The video narrows the chart to a later period instead of showing the full dataset from the very beginning.
• This is helpful because repeat rates can look artificially low in the earliest months when the business is still building its customer base.
• Update each chart series manually so the chart only reflects the months you want to highlight.
• Make sure the customer series, repeat series, and percentage line all point to the same adjusted date range.

9. Clean Up the Final Display

• Copy the chart into position beside the summary table if needed.
• Format the number columns so they show zero decimals.
• Review the axis formatting so the count metrics and percentage metrics are easy to read together.
• Rename the chart title to match the KPI clearly.

10. Final Review

At the end of the build, your dashboard should show:

• A monthly list of reporting periods
• Total unique customers by month
• Repeat customers by month
• Repeat Service Rate % by month
• A combo chart that compares customer volume with repeat behavior over time

This structure makes it much easier to see whether the business is retaining customers and generating repeat service activity month after month.