Maintenance Plan Penetration % Explained:
Track Customer Membership Growth in Excel

How to Calculate Maintenance Plan Penetration % in Excel

1. Create the Month Field from the Service Date

• Start with the service date or event date in your dataset.
• Use the EOMONTH function to convert each service date into the corresponding month-end date.
• This gives you a consistent monthly grouping field that can be used for the rest of the analysis.
• Once the month field is ready, format it as a date so each record is tied to the correct reporting month.

2. Build a Unique List of Reporting Months

• Create a separate summary area for your KPI output.
• Use the UNIQUE function on the month field you just created.
• This produces one row for each reporting month in the dataset.
• Format the list clearly and align it so it is easy to use as the base of your monthly KPI table.

3. Pull the Customer List for Each Month

• For each month in your summary table, use the FILTER function to pull all customer IDs tied to that month.
• This step isolates the customers who had activity in that specific month.
• Since the raw dataset is job-level data, the same customer may appear more than once.
• That means you cannot just count rows, because duplicate customer records would overstate the customer total.

4. Remove Duplicate Customers

• Wrap the filtered customer list inside the UNIQUE function.
• This trims the monthly customer list down to distinct customers only.
• The result is a clean monthly customer set with duplicates removed.
• This is the key step that makes the monthly penetration calculation accurate.

5. Count the Total Unique Customers

• Use COUNTA on the unique monthly customer list.
• This gives you the total number of distinct customers served in that month.
• Copy the calculation down for the rest of the months so each month has its own unique customer count.

6. Pull the Monthly Customer List for Maintenance Plan Members

• Repeat the same process, but this time add a second condition.
• Use FILTER to return customer IDs where:
  • the month matches the reporting month
  • the maintenance plan flag equals 1
• This narrows the customer list to only those who had a maintenance plan in that month.

7. Remove Duplicate Maintenance Plan Customers

• Wrap that filtered maintenance-plan customer list in UNIQUE.
• This removes repeat customers from the maintenance-plan list just like before.
• The result is the distinct number of maintenance-plan customers for each month.

8. Count the Unique Maintenance Plan Customers

• Use COUNTA on the unique maintenance-plan customer list.
• This gives you the number of distinct customers with a maintenance plan for that month.
• Copy the logic down through the monthly summary table.

9. Calculate the Monthly Penetration Percentage

• Divide the monthly maintenance-plan customer count by the monthly total unique customer count.
• Format the result as a percentage.
• This produces the final monthly Maintenance Plan Penetration %.

10. Create a Combo Chart to Visualize the KPI

• Highlight the monthly summary table.
• Insert a chart using Recommended Charts and switch to a Combo Chart if needed.
• Keep the customer counts on the primary axis.
• Place the penetration percentage line on the secondary axis so it is not compressed against the larger count values.
• This makes the counts and percentage trend readable in the same visual.

11. Clean Up the Chart Formatting

• Add a clear title to the chart.
• Adjust the number formatting so the customer-count columns do not show unnecessary decimals.
• If you want labels, add them selectively instead of labeling every point.
• A good approach is to label only the highest and lowest points so the chart stays readable.

12. Extend the Same Process to Other Views

• The same method can be reused for other breakdowns.
• Instead of grouping by month, you can swap in other fields such as:
  • acquisition channel
  • technician
  • system type
• The structure stays the same. Only the filter criteria change.