Ask an analyst which table holds weekly new-to-brand prescriptions by territory, and how the metric is calculated, and the answer is often “ask Sam.” This template is a place to write down what Sam knows, before Sam moves on.
It is an Excel workbook built for pharma commercial analytics, with worked examples from the kind of data most brand teams use.
What’s in the template
| Sheet | One row per | What it records |
|---|---|---|
| Tables | Table or file | Purpose, grain, owner, steward, source, refresh, sensitivity, caveats |
| Columns | Column | Business name, description, type, allowed values, example, sensitivity, derivation |
| Metrics | KPI | Business definition, exact calculation, unit, grain, lookback, source tables, filters, owner, version, effective date, caveats, where it is used |
| Glossary | Business term | Agreed definition, synonyms, related terms, owner |
| Change log | Change | What changed and why, the new version, when it takes effect, who approved it, which reports are affected |
The examples use an invented patient-level prescription table and a complete definition of new-to-brand prescriptions (NBRx), including a version history that shows why the definition changed. Drop-down lists keep values such as sensitivity and refresh frequency consistent.
How to use it
- Start with the scorecard. List the KPIs on the leadership scorecard and the tables they come from. Document those first.
- Write for a new analyst. A definition is good enough when someone who has never seen the table could calculate the metric correctly from it.
- Name the grain. For every table, write down what one row represents. It is the single most useful fact about a table, and it is missing from most documentation.
- Give everything an owner. The owner decides what a definition is and approves changes to it.
- Never change a definition silently. When a metric changes, add a row to the change log with a new version and an effective date, so historical comparisons still make sense.
- Flag sensitive data. Mark any column that holds personal or patient-level data, even if it is de-identified, and follow your company’s privacy policies.
When a spreadsheet stops being enough
A spreadsheet is a good way to start, and for a single data set and a small team it can be enough. It starts to struggle when the number of tables grows, when the data changes often, or when people need to see which reports depend on a field before they change it. A dictionary that falls out of date is worse than none, because people trust it.
Clarity keeps the dictionary, the KPI definitions and the lineage from source to report in one governed place, and updates them as part of normal work rather than as a separate documentation project.
For the thinking behind the metrics sheet, read our guide to KPI definitions for pharma commercial analytics, or see the glossary entries for data dictionary, business glossary and KPI drift.