Output: At the end of this step, you will have identified a few offices that are buying a large amount of one of your products or service (your anchor customer) and those that have other offices around them that are also buying a reasonable amount of your products or services (your early growth customers).
Step 2: We also want to look at the value of the contracts coming out – not just the volume. Click anywhere in your pivot table and push [Ctrl+A] to select the pivot table.
Step 3: With the entire pivot table selected, copy it, then skip three rows below your current pivot table. In the far left cell, hitCtrl+V to paste the duplicate table there. When you click into this pivot table you should see the same controls screen.
Step 4: In the values box, remove”unique_transactionid” and put in”dollarsobligated.” Change it to “Average.”
*Tip: To change from “Count” to “Average,”click the small “i” of the down arrow button next to “Dollars Obligated.”
Step 5: Select all of the numbers in the body of the pivot table (except the grand total column). Go to “Conditional Formatting” and “Color Scale” to create your heat map again.
Step 6: Using both pivot tables, identify two to three clusters of offices that look promising (lots of green). This is a bit of an art, but a couple of things to think about when selecting criteria:
1. The best cluster is a cluster where there is at least one good anchor client. E.g. an office that is buying a high volume of your stuff and where you can sell multiple services or products.
2. Second best is where there is a strong anchor customer and you can sell the same service or product to other offices inside the same sub-agency.
3. Third best is if there is a strong anchor customer and you can sell the same service or product to multiple offices inside the same agency.
Note: It may be tempting to select a cluster of offices that have an eye-popping average value but we recommend prioritizing the number of contracts over the value. Instead use the value as a way to validate that there is nothing weird happening, e.g. that the average contracts size is tiny (making it difficult to build a sustainable business) or huge (making it difficult to win your first work)