Customer Wallet Share % Explained: Identify Revenue Gaps and Growth Opportunities

How This Customer Wallet Share Analysis Was Built

1. Create the helper columns in the dataset

Start by adding the calculated columns used throughout the analysis.

• Create Our Spend by adding together the revenue columns tied to your company
• Create Competitor Spend by adding together the revenue columns tied to competitors
• Create Total Spend by combining your spend and competitor spend
• Create Year by extracting the year from the snapshot month using the YEAR function
• Create Member Status by converting the plan flag into labels like Member and Non-Member using the IF function

These helper columns make the rest of the analysis much easier.

2. Build the yearly summary table

Set up a separate table to summarize the KPI by year.

• Copy the structure you want to use for the summary section
• Create a list of years using the UNIQUE function, or type the years manually if there are only a few
• Sort the years so they appear in the correct order

This becomes the main table for annual wallet share analysis.

3. Count unique customers by year

To calculate customer counts correctly, you cannot simply count rows because the same customer may appear multiple times.

• Use the FILTER function to return only customers from a selected year
• Use the UNIQUE function on that filtered list so each customer appears once
• Use a counting function to count the unique customer IDs

This gives you the correct number of customers for each year.

4. Summarize annual spend values by year

Once the yearly customer counts are ready, calculate the spend totals.

• Use SUMIFS to total Our Spend by year
• Use SUMIFS to total Competitor Spend by year
• Calculate Total Spend by combining the two
• Set Opportunity equal to the gap between what customers spend overall and what they spend with you

This gives you the annual totals needed for the weighted wallet share view.

5. Calculate weighted wallet share

Use the annual totals to calculate the wallet share at the year level.

• Divide your annual spend by total annual spend
• Format the result as a percentage
• Review the yearly trend to see how much of the customer HVAC budget your company is capturing

This creates the weighted wallet share portion of the KPI.

6. Build the customer segment table

Next, create a second section that breaks the analysis down by segment.

• Build rows that combine Customer Type and Member Status
• Keep the years across the columns
• Add a Name field that combines the segment labels into one text label for charting

This table is used to compare wallet share across customer groups.

7. Count unique customers by segment and year

This part is more detailed because the count depends on three conditions at the same time.

• Use FILTER to return customer IDs that match:
  • the selected year
  • the selected customer type
  • the selected member status
• Use UNIQUE to remove duplicate customer IDs
• Use a counting function to count the filtered unique customers
• Lock the year and header references as needed so the formula can be copied across the table

This produces the customer count for each segment and year.

8. Calculate average annual spend per customer

Do not use a simple average of the raw rows here because customers can appear multiple times.

• Use SUMIFS to total Our Spend for each segment and year
• Divide that result by the segment customer count
• This gives you Average Annual Spend per Customer

This method avoids understating spend when one customer has multiple rows in the data.

9. Calculate average total annual spend per customer

Repeat the same logic for total customer spend.

• Use SUMIFS to total Total Spend for each segment and year
• Divide by the same customer count
• This gives you Average Total Annual Spend per Customer

Now you have both the numerator and denominator needed for segment-level wallet share.

10. Calculate average wallet share and revenue opportunity per customer

With both average spend values in place, calculate the final customer-level metrics.

• Divide Average Annual Spend by Average Total Annual Spend to get Average Wallet Share
• Subtract your average spend from average total spend to calculate Average Revenue Opportunity per Customer
• Multiply the average revenue opportunity by the customer count to get Total Opportunity Gap

This shows both percentage share and dollar opportunity by segment.

11. Audit the totals

Before charting, validate that the calculations tie back correctly.

• Sum the total opportunity values across segments
• Compare that number to the overall total opportunity
• Use this as a quick audit to confirm the formulas are working properly

This helps catch any errors before presenting the KPI.

12. Create the final combo chart

The final visualization compares wallet share and opportunity side by side.

• Highlight the segment Name column
• Hold Ctrl and also highlight:
  • Average Wallet Share
  • Average Revenue Opportunity per Customer
• Go to Insert and choose a Combo Chart
• Set Average Wallet Share as columns
• Set Average Revenue Opportunity per Customer as a line
• Place the line on the Secondary Axis

This makes both metrics visible even though they use very different scales.

13. Format the chart for readability

Clean up the visual so it is easier to interpret.

• Update the chart title
• Remove unnecessary decimals
• Cap the wallet share axis at 1 if needed
• Add data labels to the columns
• Keep the chart sorted based on wallet share if that is the primary KPI being analyzed

This makes the chart easier to read and more useful for decision-making.

14. Interpret the segment opportunities

Use the chart and table together to identify where the biggest growth opportunities exist.

• Look for segments with lower wallet share
• Compare those segments against the line for average revenue opportunity per customer
• Pay special attention to high-value segments where the opportunity per customer is much larger than the rest

This helps identify where cross-sell, upsell, retention, or membership conversion strategies could have the greatest impact.