Clinical pipeline analysis by indication and phase is the request that lands on commercial operations every quarter, usually phrased as a simple question and never simple to answer. A business unit head wants to know what the CNS pipeline looked like over the last two years, split by phase, for a particular country. Forty minutes later somebody is still reconciling sponsor names in a spreadsheet.
The analysis itself is not hard. Assembling the dataset is, and the reason is almost always the same: the export step.
The questions business unit heads actually ask
In our experience it is four, in roughly this order:
- Is this therapeutic area growing or shrinking, and at which phase?
- Which sponsors are most active in it, and are they the ones we already know?
- How does our region compare to the others?
- What changed since the last time we looked?
Notice that none of these needs individual study detail. They need counts, grouped and compared over time. That matters, because it means the dataset you need is smaller than it first appears.
The dataset you need
Five fields, per study:
| Field | Why it is needed | Usual source |
|---|---|---|
| Sponsor | Grouping and the "who is active" question | Trial registry, needs name resolution |
| Indication | The primary grouping dimension | Trial registry condition field |
| Phase | The second grouping dimension | Trial registry |
| Country or region | Territory comparison | Trial registry site locations |
| Start date | Everything time-based | Trial registry |
The field that causes the trouble is sponsor. A single company appears under multiple legal entities, acquired subsidiaries and inconsistent spellings, and if you do not resolve those, your "most active sponsors" chart is wrong in a way that is embarrassing rather than merely imprecise.
This is the single most valuable thing a sales intelligence layer does for this analysis, and it is invisible until you try to do it by hand.
Building it from Discover facets
The workflow below is built around facet counts rather than a spreadsheet pivot, which for a quarterly review turns out to be faster than it sounds:
- Filter the sponsor universe by therapeutic area, highest phase and modality. The facet counts give you the headline numbers for questions one and three directly, without any export at all. For many quarterly reviews this is enough.
- Read the counts per facet combination. Changing one filter at a time and recording the count is how most people build the time series. It is manual, it takes about twenty minutes for a typical therapeutic area, and it is quarterly rather than weekly.
- Use tracked account lists for the sponsor-level view. The accounts you track carry their own trial detail, so question two is answered from the account side rather than the study side.
- Pull through the API for anything larger. If you have someone comfortable with an API, the data is available that way today and that is the route teams doing this monthly tend to take.
Be realistic about which of these your team will actually do. A quarterly twenty-minute facet pass that happens is worth more than a monthly automated pipeline that nobody builds.
The four charts worth maintaining
Resist the urge to build a dashboard. Four charts answer the four questions.
Trials started per year, by phase. A stacked column, three or four years. Answers the growth question and shows whether growth is early or late phase, which matters enormously for a CRO's capacity planning.
Top twenty sponsors by active studies. A horizontal bar. The value here is not the ranking, it is the names on the list you do not recognise. That is your prospecting gap, visible in one chart.
Regional split. Studies by country or region, as a share. Answers the territory question and usually starts the most productive conversation, because the gap between where the pipeline is and where your commercial coverage is tends to be larger than anyone expects.
Change since last quarter. The same numbers as last time with a delta column. Unglamorous and the one people actually read.
See Salesmotion on a real account
Book a 15-minute demo and see how your team saves hours on account research.
Refreshing it quarterly
Quarterly is the right cadence for this analysis. Trial registrations do not move fast enough to justify monthly, and annual is too slow to catch a shift in a therapeutic area.
Two habits make the refresh cheap:
Write down the filter combinations you used. The most common reason a refresh takes as long as the original is that nobody recorded how the first one was built.
Keep the previous quarter's numbers in the same sheet. The delta column is the part the business unit head reads, and it only exists if you did not overwrite last quarter's file.
Frequently asked questions
How do I get the data out? Read facet counts in the interface for the headline numbers, work from tracked account lists for the sponsor view, or pull through the API for anything larger. Teams doing this monthly tend to take the API route.
Does this replace a research database? No. If you need epidemiology, market sizing or analyst commentary alongside the trial counts, a research platform does that and we do not. This is the commercial view of the same underlying registry data, aimed at the selling motion.
How accurate are the sponsor groupings? Better than raw registry data, because company records are resolved across entities, and not perfect. Check the top twenty by hand the first time you build the chart. After that the corrections carry forward.
Can I do this by US state? Yes, with a one-time pass to resolve state from the account records. Our guide to territory planning by state sets out how to build and maintain that map.
If you build this analysis every quarter, the fastest way to judge whether the facet route saves you time is to run one therapeutic area you already know the answer for. Compare with a curated pipeline database or see how it works for CRO business development.
