Blog · Territory, capacity and quota planning · Pharmaceuticals
Group pharmaceutical business accounts by approved channel attributes, then compare sales and commercial coverage. This guide works through an account export, spreadsheet formulas, and the checks that keep the groups consistent.
Customer segmentation in the pharmaceutical industry groups business accounts by approved commercial attributes so teams can compare sales and coverage on a consistent basis. Start with channel and account structure, then add the measures needed for a specific business decision. Keep the analysis at the organization level, using supplied account data rather than patient profiles or inferred prescribing behavior.
An account might mean a contracting organization, a billing account, or a delivery site. Those are different units. Write the definition at the top of the workbook, and use the same unit for sales and coverage.
Channel labels also need definitions. FDA's product tracing questions and answers distinguish manufacturers, repackagers, wholesale distributors, and dispensers in the prescription drug supply chain. These distinctions provide useful context, but commercial segment labels should come from your approved account master. A label in a sales report does not establish an account's regulatory status.
For broader organization classifications, the Census Bureau describes NAICS as a system for classifying business establishments. Use a verified code already held in your account records when it helps; do not guess an account's classification from its name.
Keep channel separate from performance. An account remains a hospital account when its sales fall. A sales band can change independently. The general B2B segmentation method covers that distinction; this workflow adds pharmaceutical channel and coverage definitions.
Use the same start and end dates in the account, sales, and activity exports. Sum sales transactions to the chosen account level before adding activity totals. Otherwise, repeating an account's quarterly sales beside every activity will multiply revenue.
For coverage, define a planned activity and the evidence of completion. Count only completed activities matched to the agreed plan. Extra contacts can remain in a separate column; they should not erase missed planned work.
This fictional US supplier uses the following account summary for July 1 through September 30, 2026. Sales are net of the credits and adjustments included in its reporting policy. Activity counts refer to that quarter only.
| Account ID (A) | Approved channel (B) | Net sales (USD) (C) | Planned activities (D) | Completed planned activities (E) | Coverage (F) |
|---|---|---|---|---|---|
| P-01 | Wholesale | 480,000 | 4 | 3 | 75.0% |
| P-02 | Wholesale | 320,000 | 4 | 4 | 100.0% |
| P-03 | Hospital | 240,000 | 6 | 3 | 50.0% |
| P-04 | Hospital | 160,000 | 6 | 6 | 100.0% |
| P-05 | Retail | 120,000 | 3 | 2 | 66.7% |
| P-06 | Retail | 80,000 | 3 | 3 | 100.0% |
| Total | 1,400,000 | 26 | 21 | 80.8% |
In F2, enter the formula below and fill it through F7. Format the result as a percentage with one decimal place. The blank result for no plan means coverage is undefined, rather than zero.
=IF(D2=0,"",E2/D2)
Check the account ID in G2, filled down. Every result must equal one in this account summary.
=COUNTIF($A$2:$A$7,A2)
If the account master has multiple effective dates, select the approved quarter's record before combining it with sales. Customer profiling and segmentation explains the difference between describing an account and assigning its group.
Channel coverage = Completed planned activities in the channel / Planned activities in the channel
Channel sales share = Channel net sales / Net sales across all included accounts
Put Wholesale, Hospital, and Retail in I2:I4. Use the following summary formulas in row 2, then fill down. They work from the account rows above.
In Excel, SUMIF adds values whose corresponding rows meet a criterion, which here is the approved channel. Covirage is not affiliated with Microsoft.
J2 =COUNTIF($B$2:$B$7,I2)
K2 =SUMIF($B$2:$B$7,I2,$C$2:$C$7)
L2 =SUMIF($B$2:$B$7,I2,$D$2:$D$7)
M2 =SUMIF($B$2:$B$7,I2,$E$2:$E$7)
N2 =IF(L2=0,"",M2/L2)
O2 =K2/SUM($C$2:$C$7)
| Channel | Accounts | Net sales (USD) | Planned | Completed | Coverage | Sales share |
|---|---|---|---|---|---|---|
| Wholesale | 2 | 800,000 | 8 | 7 | 87.5% | 57.1% |
| Hospital | 2 | 400,000 | 12 | 9 | 75.0% | 28.6% |
| Retail | 2 | 200,000 | 6 | 5 | 83.3% | 14.3% |
| Total | 6 | 1,400,000 | 26 | 21 | 80.8% | 100.0% |
Percentages are rounded to one decimal place after calculation. Hospital coverage is 9 / 12, or 75.0%. The company rate is 21 / 26, or 80.8%; calculate it from counts, rather than averaging the channel percentages.
The checks are direct. Channel sales must sum to $1,400,000 and tie to the included sales export. Channel account counts must sum to six. Planned and completed counts must tie to their respective activity exports. Investigate unmatched accounts before accepting any of those totals.
These summary checks return zero when the values agree:
=SUM(K2:K4)-SUM(C2:C7)
=SUM(L2:L4)-SUM(D2:D7)
=SUM(M2:M4)-SUM(E2:E7)
The hospital group has the lowest coverage in this example. That is a reason to review its missed planned activities, beginning with P-03. It is not evidence of unmet clinical need or a prediction of sales from another visit.
If one additional planned hospital activity were completed, hospital coverage would become 10 / 12, or 83.3%. Sales would remain $400,000 unless a separate sales transaction changed. Keep that separation visible when discussing the next quarter's coverage plan.
Commercial operations should own the account hierarchy and segment definitions. Finance should approve the sales reconciliation, and account managers should resolve activity exceptions. Use concentration by industry when a few accounts dominate the channel total; coverage percentages alone will not show that exposure.
Bring a sample of your own approved account, net sales, and commercial coverage exports. Covirage maps the columns, and its tools compute and check the group comparisons; the external AI model explains the result with every figure traced to its rows. Create a free Covirage account and set up your business to start with your own file.
See Covirage for pharmaceutical businesses for the industry context.
Use a stable business account ID, an approved channel label, net sales, the reporting period, and any commercial coverage fields you intend to compare. Keep the account hierarchy explicit. If sales are recorded at a parent but coverage is recorded at a site, resolve that difference before combining the files.
They can appear in the same overall report, but retain separate channel labels when their purchasing and commercial coverage differ. A wholesale relationship may represent many downstream locations. Comparing its sales directly with a single hospital account can confuse organizational scale with account performance.
No. Coverage shows whether the agreed commercial activity happened. Sales can also move with order timing, contract changes, inventory purchases, returns, and account scope. Review those factors before attributing a sales change to contact activity, even when the two measures move together.
This commercial method does not require it. Build the analysis from approved business account, sales, and coverage exports. Use organization identifiers rather than patient records, and retain a clear definition of what each account represents. Clinical questions and patient segmentation require a different process.