WORKSHEET #3: Defining the Size and Purpose of Roles
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.
This Worksheet is a continuation from Worksheet #2: Segmenting Customers by Size.
Step 6:
Record the amount of purchases each customer makes from his Primary, Secondary, Tertiary and Other suppliers in columns 6 – 13.
| 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 |
| Col. 6 | Col. 7 | Col. 8 | Col. 9 | Col. 10 | Col. 11 |
| Name of Primary | Primary Amount | Reason for Primary | Name of Secondary | Secondary Amount | Reason for Secondary |
| Col. 12 | Col. 13 | Col. 14 | Col. 15 | Col. 16 | Col. 17 |
| Name of Tertiary | Tertiary Amount | Reason for Tertiary | Name of Other | Other Amount | Reason for Other |
Step 7:
For each customer and role in the customer relationship, determine the amount the customer purchases from the supplier in each role. If the customer gives you the proportions or percentages of his needs that he buys from each supplier, convert these into units or dollars. Fill these results into columns 6, 10, 13 and 16 of the table above.
Step 8:
For each customer and role in the customer relationship, record the reason the customer uses the supplier for each role. For this step, and for several others throughout the set of Worksheets, we have found it helpful to use an Access or Excel database format. To use these databases effectively, we developed abbreviations for customer types, competitors, and for the various benefit reasons. By using these abbreviations, we are able quickly to record and analyze the data from customer surveys. Often, these code packets bear similarities to one another. The link below provides you with examples of coding packets for the benefits in two industries, Commercial Printing and Less Than Truckload Trucking. We have organized these Benefit Code Packets by the Customer Buying Hierarchy (i.e. Function, Reliability, Convenience and Price). You might use them as models for your own database work. You may find it helpful to use the same or similar coding and coding approach in Worksheet #7: Wins and Failures in the Marketplace.
Benefit Code Packets: Commercial Printing Industry or Less Than Truckload Trucking
Step 9:
If possible, rationalize and consolidate reasons for each Secondary, Tertiary and Other role to a limit of five different reasons.
Step 10:
Sum all the sales volume in the total sample of all customers for each Secondary, Tertiary, and Other role.
Step 11:
Divide the sales volume for each role reason in a particular role (that is, Secondary, Tertiary or Other) by the total sales volume for the role in the sample. This calculation provides the data for Analysis 9.