How to build a pharma company R&D profile from public data: a repeatable method

We wrote this alongside our Eli Lilly R&D profile so that you can repeat the analysis for another company. It sets out the questions we asked, what each one needs, and the mistakes we caught. It was written on 8 November 2026, using ClinicalTrials.gov data as of 7 November, and the data is updated daily.

An R&D profile covers one company’s pipeline, trial activity, FDA record, publications and peers. It doesn’t cover revenue, R&D spending, deal terms, prescriptions, approvals outside the US or undisclosed preclinical work. None of that is in these sources.

You can do this with ClinLit AI, or directly on the public sources it uses: ClinicalTrials.gov (through AACT), Drugs@FDA and the Orange Book, PubMed, and FAERS.

The Lilly profile took 25 questions. Each took from a few seconds to under three minutes. One was refused, one failed, one ran out of its budget and one gave a wrong list. Those four taught us the most.

Step 1: decide which names count as the company

This step decides everything after it. Sponsors appear under many names, and no public table links them to a parent company. List every name first:

Which lead sponsor names in aact.sponsors (agency_class INDUSTRY) and which sponsor values in fda.applications appear to belong to [COMPANY] or a company with ‘[SHORT NAME]’ in the name? Give the trial or application count for each, and flag any that look like a different organisation.

Then add the companies it has bought. A search for “Lilly” can’t find Loxo Oncology (17 trials) or Dermira (10). Look for acquisitions in the company’s press releases or SEC filings. ClinLit AI declined a question about corporate deals as outside its scope, so do this part on the web. Some registry names state the ownership themselves: “POINT Biopharma, a wholly owned subsidiary of Eli Lilly and Company”.

What went wrong when we didn’t check:

Trap	What happened
A different company with a similar name	Morphic Medical (13 trials) isn’t Morphic Therapeutic, which Lilly bought
Two companies with the same name	Merck KGaA is separate from Merck & Co., and an open question merged them
A name that changes between sources	Drugs@FDA lists Ozempic and Wegovy under “NOVO”, so a search for “Novo Nordisk” found 4 novel approvals instead of 8
A division that has left	“Pfizer’s Upjohn has merged with Mylan to form Viatris Inc.” holds 226 trials that shouldn’t count for Pfizer
A foundation	Lilly Endowment and Lilly Foundation aren’t the drug company

Write the result as an explicit list of exact names, and paste it into every question. Then the answer doesn’t depend on what the assistant decides that day.

Decide how to treat history too. We used today’s ownership, so a company’s trials from before its acquisition count for the buyer. That suits a pipeline view. For a track record, you may want the opposite.

Step 2: get the size and shape

Begin every question with your definition:

Define [COMPANY] trials as studies in aact whose lead sponsor (sponsors.lead_or_collaborator = ‘lead’) has one of exactly these names: [NAMES]. Active means overall_status in (‘RECRUITING’,‘NOT_YET_RECRUITING’,‘ACTIVE_NOT_RECRUITING’,‘ENROLLING_BY_INVITATION’).

Then ask:

How many [COMPANY] trials are there in all? How many are active? Break the active ones down by phase (Early Phase 1 and Phase 1 together, Phase 1/2, Phase 2, Phase 2/3, Phase 3, and no phase). Then give trial starts by year for the last ten years, saying how many of this year’s starts have an ESTIMATED rather than ACTUAL start date.

Follow-ups in the same conversation reuse the definition. A conversation keeps its last 10 messages, so paste it again in a new one.

Check the estimated dates. Part of the latest year is plans, not trials that have started.

Step 3: see where the research is going

MeSH tree codes give comparable disease areas across companies, using the trial’s own terms:

Count trials started in 2011-2015, 2016-2020 and 2021 onward in each of these areas, taken from the trial’s own MeSH condition terms (browse_conditions with mesh_type = ‘mesh-list’) joined to mesh_terms on downcase_mesh_term and the first three characters of tree_number: C04 neoplasms, C18 nutritional and metabolic diseases, C19 endocrine diseases, C10 nervous system diseases, F03 mental disorders, C20 immune system diseases, C14 cardiovascular diseases. A trial counts once in each area it touches. Also give the number of trials started in each period, and how many have no term in these areas.

Choose the codes that fit the company. Read the “no term” count. For Lilly, 42% of trials from 2021 on fell in none of the seven areas, mostly because they have no MeSH term, so the area counts are lower than the real ones.

Step 4: build the asset list

Trials aren’t a pipeline. A trial lists comparators and background drugs. Lilly’s experimental arms included more than 50 of other companies’ approved drugs, such as pembrolizumab. This took us the most work, so ask three smaller questions rather than one large one. Our single large question ran out of budget and returned errors.

Never-approved drugs:

In active [COMPANY] trials, take interventions of type DRUG, BIOLOGICAL, GENETIC or COMBINATION_PRODUCT in design groups of type EXPERIMENTAL. Link them to moieties in fda.study_ingredients, following the fda guide’s method for which arm gets a drug. Keep moieties with first_approval_date NULL in fda.ingredients. Give one row per moiety whose highest active phase is Phase 2 or Phase 3: name, highest active phase, number of distinct active trials and the main conditions. Give the number of moieties by highest active phase. Say which rows are salt forms of the same drug or false positives.

How to tie a drug to an arm matters. Our first attempt matched the linked drug’s name exactly to the trial’s intervention name, and it dropped retatrutide, one of Lilly’s largest late-stage drugs, while the answer still looked complete. The stored name for most links is the trial’s MeSH term, which often differs from the name the trial lists: only 31% match exactly and 52% ignoring case, because a trial can list a drug as a code. So we added the right method to ClinLit AI’s guide. If you work on the data directly, match the trial’s intervention name and other names through FDA’s substance dictionary to the same moiety, too.

Code names not yet in FDA’s dictionary:

In active [COMPANY] trials, take intervention names in design groups of type EXPERIMENTAL that match [COMPANY CODE PREFIX] followed by digits, and that have no row in fda.study_ingredients for that trial and intervention name. Normalise each by removing spaces, hyphens and suffixes such as IV or SC. Check each normalised code against fda.substance_names using fda.normalize_name, and split them into codes that resolve to a known moiety (say which) and codes that resolve to nothing.

Companies use different prefixes (LY for Lilly, AZD for AstraZeneca, PF- for Pfizer, MK- for Merck & Co., NN for Novo Nordisk).

Approved drugs still in development. Ask for moieties with an FDA application held by the company, and exclude other companies’ drugs.

Then clean the list by hand:

• Merge salt and radiolabelled forms of one drug. Lepodisiran and lepodisiran sodium are one programme.
• Remove false positives. Abiraterone and nab-paclitaxel appeared as “never approved”, because FDA’s dictionary files the marketed form separately.
• Read trial titles for codes. LY3650150 is lebrikizumab, an approved drug.
• Note partnered drugs. A drug in a company’s experimental arm isn’t necessarily its own.

Step 5: list what is due

List active Phase 3 [COMPANY] trials whose primary_completion_date falls between [TODAY] and [ONE YEAR ON]: NCT ID, drug, condition, enrollment, primary completion date and whether that date is ESTIMATED or ACTUAL.

Treat these as sponsor estimates, and say so.

Step 6: read what changed

ClinLit AI keeps every earlier version of each study from July 2024, which ClinicalTrials.gov’s current records don’t show directly.

Using aact_history.study_changes, answer three things for [COMPANY] trials. 1) How many changed overall_status to TERMINATED, WITHDRAWN or SUSPENDED, by phase, and for Phase 2 and Phase 3 list NCT ID, drug, condition and the why_stopped text. 2) For Phase 3 trials, how many changes to primary_completion_date moved it later and how many earlier, and what was the median shift in days each way? 3) Which trials changed lead_sponsor into or out of the [COMPANY] names, excluding changes that are only a rename of the same organisation?

Read later dates as the signal. An earlier date is often a stopped trial, or an estimate replaced by the real date.

Step 7: the FDA record

Use the sponsor names from Step 1 in their Drugs@FDA spelling:

Using is_novel as the proxy for novel approvals, list [COMPANY]’s novel approvals with first_approval_date since 2016-01-01. Then, for currently marketed NDAs, give the latest patent expiry and latest exclusivity expiry per application from fda.patents and fda.exclusivities. Then say how many currently marketed BLAs there are and whether fda.patents covers them.

Three limits apply:

• The proxy counts every original BLA, including biosimilars.
• The sponsor is the current holder, so acquisitions inflate the count.
• The Orange Book has no biologics. A company reliant on biologics will look unexposed to patent expiry when you simply can’t see it.

Step 8: compare with peers

Build the same definition for each peer and put them in one question, so the method is identical. Choose peers that make sense for the company: for a specialist, compare within its disease area.

Compare shape rather than rank: Phase 3 share, area mix, trial size and sites, China and US footprint. Be careful with termination rates, because sponsors record stopped trials differently.

Step 9: publications, protocols, safety and the latest news

• Publications. Ask how many trials have a linked PubMed article, using the databank accessions and study references. Restrict to Phase 3 trials that finished long enough ago to have published.
• Protocols. Ask about one trial at a time, ending “Cite the protocol pages.” Amendments show what a registry entry can’t.
• Safety. State that FAERS has no denominator or causality assessment. Say “reported”, never “risk” or “rate”, and don’t compare report counts between drugs.
• The latest results. Check PubMed first, then the company’s press releases, and name what is a company announcement and not peer reviewed. Ask about one or two drugs at a time: our question on four drugs failed.

Step 10: check everything

Each of these came from checking one answer against its source:

What we checked	What we found
Page numbers against the PDF	Two of the donanemab citations were one page too low
A number against the protocol	Significance level given as 0.017. Page 120 says 0.016
A column label against the same run’s other answers	A column called “all trials started 2021+” was really trials in six areas: 284, not 506
The drug list against what we knew	Retatrutide was missing, because of an exact match on names
A first attempt at the open question	It gave Novo Nordisk 4 approvals and merged Merck KGaA into Merck & Co.

Then cross-check the headline counts in your own SQL if you can. Ours matched exactly once the definitions were the same.

Keep the date on the result. Every number here is from a particular day. Say which, and re-run the questions after each refresh.

Trying it on a second company

To test the method we pasted the same questions for Novo Nordisk, changing only the names (name ~* 'Novo Nordisk'). ClinLit AI returned 960 trials, 101 active, 47 in active Phase 3 and 10 due within a year, the same as in our peer table, and listed the trials including NCT06118281 (ARTEMIS, ziltivekimab) and NCT05669755 (REDEFINE 3, CagriSema). The answer took 16 seconds.

Reproduce this with ClinLit AI: the second company

We asked:

Define Novo Nordisk trials as studies in aact whose lead sponsor (sponsors.lead_or_collaborator = ‘lead’, agency_class = ‘INDUSTRY’) has a name matching name ~* ‘Novo Nordisk’. Active means overall_status in (‘RECRUITING’,‘NOT_YET_RECRUITING’,‘ACTIVE_NOT_RECRUITING’,‘ENROLLING_BY_INVITATION’). How many Novo Nordisk trials are there in all? How many are active? Break the active ones down by phase (Early Phase 1 and Phase 1 together, Phase 1/2, Phase 2, Phase 2/3, Phase 3, and no phase). Then list the active Phase 3 trials with primary_completion_date between 2026-11-08 and 2027-11-08, largest enrollment first, at most 15 rows.

Ask ClinLit AI this question →