WORKSHEET #2: Segmenting Customers by Size
Note:
This worksheet draws on the results of customer data gathered using Analysis 2
and Analysis 66
. Please review these analyses for further information and context for the steps in this worksheet.
Step 1:
Obtain a comprehensive list of all customers in the market. Every customer in the market, whether or not you currently serve the customer, should be included in the list. It is important that each of these customers is the final buying location, the location that picks the supplier and the amount to be purchased from the supplier. You will not need a list if your customers are consumers.
Step 2:
Take a random sample of 100 of those customers in the market. This sample should be completely random and drawn from the list of all customers. If a customer on the list is a Standardized purchaser, keep the customer in your sample only if the customer is the actual buying location. Adjust your list until you have a total of 100 actual buying locations.
Step 3:
Determine the annual or monthly purchases that each of these customers makes from all suppliers in the marketplace. It is likely you will need the sales force and marketing staff to make this estimate.
Step 4:
Rank each customer in your sample of 100 from the largest to the smallest in total purchases from all suppliers.
Step 5:
You are now ready to complete the following table:
| Col. 1 | Col. 2 | Col. 3 | Col. 4 | Col. 5 |
| Cust. ID | Cust. Name | Total Purchases from All Suppliers | Cumulative Purchases in the Industry | Cumulative % of Purchases in the Industry |
| 1 | ABC | |||
| 2 | DEF |
Fill in this table as follows:
In Column 1, place the Customer's ID number, from 1 to 100. The Customer ID number does not imply the size of the customer. The Customer ID number can help you identify the the order in which the customer was interviewed, how many customers have been interviewed, the source of the information, etc.
In Column 2, list the customer's name.
In Column 3, list the customer's total purchases from all suppliers in the marketplace. You should sort this column so that, beginning with the largest, Column 3 will descend from the purchases of the largest customer down to the purchases of the smallest.
In Column 4, cumulate the purchases in the industry sample. For example, the row for the customer in Row 3 will show, in Column 4, the sum of the sales of the first, second and third largest customers in the market.
In Column 5, calculate the cumulative percentage of all purchases of this and all larger customers in the sample as a percentage of the total purchases in the sample. For example, if the industry sample had total purchases of 200 and the customer in row 1 had purchased 20, then the customer in row 1 would have 10% in Column 5. The customer in the third row would show, in Column 5, the combined sales for the first three customers as a cumulative percentage of all the sales in the sample, which is the total in Column 4 divided by the total sales of the sample.
Draw boundary lines where the cumulative volume shown in Column 5 is equal, or close to 50% of the market, 80% of the market and 95% of the market. These are your initial volume breaks for the minimum sizes of Very Large, Large and Medium customers.
Check that the volume breaks make sense to you. Ask whether the customers in each group are treated roughly the same by suppliers in the marketplace. If not, you may choose to change your volume breaks, either up or down, as appropriate.
Finalize the boundary lines that you have identified in total purchases from all suppliers in Column 3, as the break points for the minimum sizes of Very Large, Large and Medium customers. For example, if the customer who falls at 50% in Column 5 buys $500M a year from all suppliers, then the minimum sized Very Large customer purchases $500M a year. Any customer who purchases $500M a year or more from all suppliers would be considered a Very Large customer. Below $500M a year in purchases, until you get to the break point for the purchases of the customer at the 80th percentile in Column 5, would define the size range for the Large customers.
Calculate the percentage of number of customers that fall within each size category. Do this by counting the number of customers in the category in Column 1, dividing that number by 100, and converting the fraction to a percentage.
Examine the customers in each size category to determine whether those customers would have fallen in the same size category three to five years ago. Pick a date, such as three years ago, and do the same analyses as you have done in the previous steps, using the same 100 customers. Did each customer fall into the same size category, Very Large, Large, Medium and Small? If a customer has changed categories, especially if the customer has changed two categories, say from Very Large to Medium or from Small to Large, you may want to spend more analytical time to find out what made those customers either more successful or less successful than the market as a whole.
Note: The results from this step will be used in several of the following worksheets.