Trials: pharma trials | Pharma Observatory

A clinical trial registered on ClinicalTrials.gov, with its registry record, sites, enrollment history, results and the sponsors that run it.

Id: A ClinicalTrials.gov NCT id such as NCT01234567 (NCT followed by eight digits). Every trials dataset takes this same id in its trial_id column. Matching is exact.

Commands

Trials commands
Command	Reads
`pharma trials list`	Trials
pharma trials get <id>	One trial and the datasets reachable from it
`pharma trials get <id> sponsor-trials`	Sponsor trials
`pharma trials get <id> trial-adverse-events`	Trial adverse events
`pharma trials get <id> trial-changes`	Trial changes
`pharma trials get <id> trial-delivery`	Trial delivery
`pharma trials get <id> trial-demographics`	Trial demographics
`pharma trials get <id> trial-drugs`	Trial drugs
`pharma trials get <id> trial-enrollment-history`	Trial enrollment history
`pharma trials get <id> trial-enrollment-rate`	Trial enrollment rate
`pharma trials get <id> trial-extracted-endpoints`	Trial extracted endpoints
`pharma trials get <id> trial-outcome-measures`	Trial outcome measures
`pharma trials get <id> trial-sites`	Trial sites

Every command takes the paging, sorting and output flags described on the CLI page; the CLI prints the source credit with every result.

Pharma Observatory CLI

Trials

pharma trials list

The trials object type's primary dataset: one row per ClinicalTrials.gov trial, with its registry record, eligibility fields, lead sponsor and the id lists that scope a list by condition, drug or sponsor organization.

One row per: ClinicalTrials.gov trial
Key columns: trial_id

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

Columns 50

Trials columns
Column	Type	Unit	Description
trial_id	text		The trial's ClinicalTrials.gov registry id (NCT, National Clinical Trial, number).
brief_title	text		The trial's brief title.
official_title	text		The trial's official title.
study_type	text		INTERVENTIONAL, OBSERVATIONAL or EXPANDED_ACCESS.
overall_status	text		The registry's overall status code (RECRUITING, COMPLETED, TERMINATED and so on).
overall_status_summary	text		A coarse status label derived from the overall status (Recruiting, Completed, Discontinued and so on).
phase	text		The registry's phase code (PHASE1, PHASE2, PHASE2/PHASE3, NA and so on). Null when the registry gives no phase (observational and expanded-access trials); phase_label carries the display label for those.
phase_label	text		The trial phase as a label (Phase 2, Phase 3, Observational, Not applicable).
phase_by_status	text		The phase code and the status summary joined with a colon (PHASE2:Completed).
enrollment	integer	participants	Enrollment as registered: a planned target or an achieved count (see enrollment_type).
enrollment_type	text		ACTUAL (a count of enrolled participants) or ESTIMATED (a planned target).
enrollment_is_plausible	boolean		False when the registered enrollment is a placeholder value rather than a count.
enrollment_flag_reason	text		Why the registered enrollment was marked a placeholder (null when it was not).
start_date	date		Trial start date.
start_year	integer	calendar year	Year of the trial start.
primary_completion_date	date		Primary completion date.
primary_completion_date_type	text		ACTUAL (a date that has occurred) or ESTIMATED (a planned date). The registry's older ANTICIPATED is published as ESTIMATED.
completion_date	date		Completion date.
completion_date_type	text		ACTUAL (a date that has occurred) or ESTIMATED (a planned date). The registry's older ANTICIPATED is published as ESTIMATED.
study_first_posted_date	date		Date the study was first posted to the registry.
last_update_posted_date	date		Date the registry record was last updated.
why_stopped	text		The sponsor's stated reason a trial was stopped early, as filed.
number_of_arms	integer		Number of study arms.
were_results_reported	boolean		Whether results are posted to the registry.
gender	text		Eligible sex: ALL, FEMALE or MALE.
minimum_age	text		Minimum eligible age, as filed (for example 18 Years).
maximum_age	text		Maximum eligible age, as filed (for example 65 Years).
healthy_volunteers	boolean		Whether healthy volunteers are accepted.
eligibility_criteria	text		The inclusion and exclusion criteria text, as filed.
lead_sponsor_name	text		The lead sponsor name as filed.
lead_sponsor_org_id	text		Legal-entity level: the organization record the lead sponsor resolved to (null when unresolved).
lead_sponsor_company_id	text		Company level: the company the lead sponsor belongs to (null when unresolved).
lead_sponsor_company_name	text		Display name of the lead sponsor's company.
lead_sponsor_group_id	text		Group level: the lead sponsor's current ultimate parent company; a root company's group is itself.
lead_sponsor_group_name	text		Display name of the lead sponsor's group.
collaborators	text[]		Collaborating sponsor names, as filed.
participating_sponsors	text[]		Names of the lead sponsor and the collaborators, as filed.
conditions	text[]		Conditions studied, as registered.
condition_mesh	text[]		Conditions studied, as Medical Subject Headings (MeSH) terms the registry assigns.
interventions	text[]		Interventions, as registered.
intervention_mesh	text[]		Interventions as Medical Subject Headings (MeSH) terms the registry assigns.
intervention_types	text[]		Intervention types (DRUG, BIOLOGICAL, DEVICE and so on).
number_of_facilities	integer		Number of study sites the registry record states.
facility_count	integer		Number of study sites listed on the record.
has_us_facility	boolean		Whether any listed site is in the United States.
facility_country_count	integer		Number of countries with a listed site.
facility_countries	text[]		Countries with a listed site.
condition_ids	text[]		MeSH condition ids the trial is linked to, direct and through the MeSH tree. Filter on one id to match the trials that carry it.
drug_ids	text[]		Drug ids the trial's interventions resolved to. Filter on one id to match the trials that carry it.
sponsor_org_ids	text[]		Organization record ids of the sponsors on the trial, lead and collaborator. Filter on one id to match the trials that carry it.

pharma trials get <id> sponsor-trials

Which trials a sponsor runs, one row per trial and sponsor, so group-by phase and year answers the sponsor-pipeline question.

One row per: trial and sponsor as filed (lead sponsor or collaborator)
Key columns: trial_id, sponsor_role, sponsor_name

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

Columns 22

Sponsor trials columns
Column	Type	Unit	Description
trial_id	text		The trial's ClinicalTrials.gov registry id (NCT, National Clinical Trial, number).
sponsor_role	text		lead or collaborator.
sponsor_name	text		The sponsor name as filed.
sponsor_org_id	text		Legal-entity level: the organization record the sponsor resolved to (null when unresolved).
sponsor_company_id	text		Company level: the company the sponsor belongs to (null when unresolved).
sponsor_company_name	text		Display name of the sponsor's company.
sponsor_group_id	text		Group level: the sponsor's current ultimate parent company; a root company's group is itself.
sponsor_group_name	text		Display name of the sponsor's group.
sponsor_agency_class	text		The funder class the registry declared for the sponsor (INDUSTRY, NIH, FED, OTHER_GOV, NETWORK, OTHER, INDIV, AMBIG, UNKNOWN). UNKNOWN is the registry's own code for a class it does not state.
sponsor_resolution_method	text		How the sponsor name was resolved to an organization record.
trial_title	text		The trial's brief title.
study_type	text		INTERVENTIONAL or OBSERVATIONAL.
phase	text		The registry's phase code (PHASE1, PHASE2, PHASE2/PHASE3, NA and so on). Null when the registry gives no phase (observational and expanded-access trials); phase_label carries the display label for those.
phase_label	text		The trial phase as a display label (Phase 2, Phase 3, Observational, Not applicable, Expanded access, Unknown). The same trial's phase column holds the registry code.
overall_status	text		The registry's overall status.
start_date	date		Trial start date.
start_year	integer	calendar year	Year of the trial start.
primary_completion_date	date		Primary completion date.
enrollment	integer	participants	Enrollment as registered.
were_results_reported	boolean		Whether results are posted.
conditions	text[]		Conditions studied, as registered.
interventions	text[]		Interventions, as registered.

Trial adverse events

pharma trials get <id> trial-adverse-events

The adverse events a trial posted, serious and other, summed per organ system and event term.

One row per: trial, event type, organ system and event term
Key columns: trial_id, event_type, organ_system, adverse_event_term

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

Columns 6

Trial adverse events columns
Column	Type	Unit	Description
trial_id	text		The trial's ClinicalTrials.gov registry id (NCT, National Clinical Trial, number).
event_type	text		serious or other.
organ_system	text		The organ system class, as filed ((unspecified) where the filing gives none).
adverse_event_term	text		The adverse event term, as filed.
subjects_affected	bigint	participants	Participants affected, summed over the trial's groups.
subjects_at_risk	bigint	participants	Participants at risk, summed over the trial's groups.

Trial changes

pharma trials get <id> trial-changes

The recorded changes to a trial's registry record, one row per change, with the trial's title.

One row per: recorded change to a trial (event, field and time)
Key columns: observed_at, trial_id, event_type, field

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

Columns 10

Trial changes columns
Column	Type	Description
trial_id	text	The trial's ClinicalTrials.gov registry id (NCT, National Clinical Trial, number).
observed_at	timestamp	When the platform observed the change.
event_type	text	The kind of event (first_seen, status_change and so on).
field	text	The record field that changed (null for a first_seen event).
old_value	text	The value before the change.
new_value	text	The value after the change.
trial_title	text	The trial's brief title.
condition_ids	text[]	MeSH (Medical Subject Headings) condition ids the trial is linked to, direct and through the MeSH tree. Filter on one id to scope changes to a condition's trials.
drug_ids	text[]	Drug ids the trial's interventions resolved to. Filter on one id to scope changes to a drug's trials.
sponsor_org_ids	text[]	Organization record ids of the sponsors on the trial, lead and collaborator. Filter on one id to scope changes to a sponsor's trials.

Trial delivery

pharma trials get <id> trial-delivery

Planned against actual enrollment and primary completion for a trial, one row per trial with a recorded plan or result.

One row per: trial
Key columns: trial_id

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

Columns 25

Trial delivery columns
Column	Type	Unit	Description
trial_id	text		The trial's ClinicalTrials.gov registry id (NCT, National Clinical Trial, number).
lead_sponsor_name	text		The lead sponsor name as filed.
overall_status_summary	text		A coarse status label (Recruiting, Completed and so on).
study_type	text		INTERVENTIONAL, OBSERVATIONAL or EXPANDED_ACCESS.
phase	text		The registry's phase code (PHASE1, PHASE2, PHASE2/PHASE3, NA and so on). Null when the registry gives no phase (observational and expanded-access trials); phase_label carries the display label for those.
phase_label	text		The trial phase as a display label (Phase 2, Phase 3, Observational, Not applicable, Expanded access, Unknown). The same trial's phase column holds the registry code.
planned_enrollment	integer	participants	Planned enrollment from the earliest snapshot typed ESTIMATED.
actual_enrollment	integer	participants	Enrollment from the latest observation typed ACTUAL.
enrollment_type	text		Enrollment type on the current record (ACTUAL or ESTIMATED).
enrollment_delta	integer	participants	Actual minus planned enrollment (null without both).
enrollment_attainment_pct	numeric	percent	Actual as a percentage of planned enrollment (null without both).
enrollment_met	boolean		Whether actual enrollment reached the plan (null without both).
enrollment_delta_known	boolean		Whether a planned and an actual enrollment are both recorded.
planned_basis	text		Where the planned enrollment came from: history_earliest_estimated, type_estimated_current or none.
actual_basis	text		How the actual enrollment was taken (from the snapshot history, from the current record, or none).
planned_primary_completion_date	date		Planned primary completion date from the earliest snapshot typed ESTIMATED.
actual_primary_completion_date	date		Primary completion date once typed ACTUAL.
planned_completion_date	date		Planned completion date from the earliest snapshot typed ESTIMATED.
actual_completion_date	date		Completion date once typed ACTUAL.
primary_completion_date_type	text		ACTUAL (a date that has occurred) or ESTIMATED (a planned date). The registry's older ANTICIPATED is published as ESTIMATED.
completion_date_type	text		ACTUAL (a date that has occurred) or ESTIMATED (a planned date). The registry's older ANTICIPATED is published as ESTIMATED.
primary_completion_slip_days	integer	days	Actual minus planned primary completion date; positive is later than planned (null without both).
completion_slip_days	integer	days	Actual minus planned completion date; positive is later than planned (null without both).
completion_slipped	boolean		Whether the primary completion came later than planned (null without both).
has_completion_delta	boolean		Whether a planned and an actual primary completion date are both recorded.

Trial demographics

pharma trials get <id> trial-demographics

Which participant demographics a trial's posted results report, and the sex split and race breakdown where they do, one row per trial.

One row per: trial with a posted baseline demographic breakdown
Key columns: trial_id

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

Columns 8

Trial demographics columns
Column	Type	Unit	Description
trial_id	text		The trial's ClinicalTrials.gov registry id (NCT, National Clinical Trial, number).
has_sex_data	boolean		Whether a sex baseline breakdown was posted.
has_race_data	boolean		Whether a race baseline breakdown was posted.
has_ethnicity_data	boolean		Whether an ethnicity baseline breakdown was posted.
has_age_data	boolean		Whether a categorical age baseline breakdown was posted.
pct_female	numeric	percent	Percent of baseline participants recorded as female (null when sex was not posted).
race_breakdown	text	percent by category	A JSON object of race category to percent of baseline participants, as posted.
n_analyzed	integer	participants	Baseline participants analyzed.

Trial drugs

pharma trials get <id> trial-drugs

The drugs a trial studies, one row per molecule the trial's drugs resolved to and one per drug that reached no molecule, with the names as registered.

One row per: molecule a trial's drugs resolved to, and one per drug with no molecule
Key columns: trial_id, drug_key

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

Columns 7

Trial drugs columns
Column	Type	Unit	Description
trial_id	text		The trial's ClinicalTrials.gov registry id (NCT, National Clinical Trial, number).
drug_key	text		The molecule key, or the drug name as registered prefixed unresolved: for a drug that reached no molecule.
molecule_id	text		The molecule key the drugs resolved to (null for a drug with no molecule).
molecule_name	text		The molecule's display name (null for a drug with no molecule).
registered_as	text[]		The drug names as registered on the trial that led to this row.
matched_from_name_part	boolean		Whether every match to the molecule came from one part of a multi-part drug name.
brand_count	integer	brands	Number of brand names recorded for the molecule.

Trial enrollment history

pharma trials get <id> trial-enrollment-history

How a trial's registered enrollment, status and key dates stood in each registry snapshot held for it, one row per snapshot.

One row per: trial and registry snapshot date
Key columns: trial_id, as_of

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

Columns 10

Trial enrollment history columns
Column	Type	Unit	Description
trial_id	text		The trial's ClinicalTrials.gov registry id (NCT, National Clinical Trial, number).
as_of	date		The registry snapshot date this row describes.
enrollment	integer	participants	Enrollment registered at the snapshot (a planned target or an achieved count, see enrollment_type).
enrollment_type	text		ACTUAL or ESTIMATED at the snapshot.
overall_status	text		The trial's overall status code at the snapshot.
start_date	date		Start date at the snapshot.
primary_completion_date	date		Primary completion date at the snapshot.
completion_date	date		Completion date at the snapshot.
enrollment_is_plausible	boolean		False when the registered enrollment is a placeholder value rather than a count.
enrollment_flag_reason	text		Why the enrollment was marked a placeholder (null when it was not).

Trial enrollment rate

pharma trials get <id> trial-enrollment-rate

How fast a trial enrolled, in participants per month and per site-month, one row per trial.

One row per: trial
Key columns: trial_id

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

Columns 22

Trial enrollment rate columns
Column	Type	Unit	Description
trial_id	text		The trial's ClinicalTrials.gov registry id (NCT, National Clinical Trial, number).
phase	text		The registry's phase code (PHASE1, PHASE2, PHASE2/PHASE3, NA and so on). Null when the registry gives no phase (observational and expanded-access trials); phase_label carries the display label for those.
phase_label	text		The trial phase as a display label (Phase 2, Phase 3, Observational, Not applicable, Expanded access, Unknown). The same trial's phase column holds the registry code.
lead_sponsor_name	text		The lead sponsor name as filed.
overall_status_summary	text		A coarse status label (Completed and so on).
actual_enrollment	integer	participants	Enrollment typed ACTUAL.
enrollment_type	text		ACTUAL or ESTIMATED.
enrollment_is_plausible	boolean		False when the registered enrollment is a placeholder value rather than a count.
active_sites	integer	sites	Number of sites the rate is computed over.
facility_country_count	integer		Number of countries with a listed site.
has_us_facility	boolean		Whether any listed site is in the United States.
start_date	date		Trial start date.
primary_completion_date	date		Primary completion date.
completion_date	date		Completion date.
primary_completion_date_type	text		ACTUAL (a date that has occurred) or ESTIMATED (a planned date). The registry's older ANTICIPATED is published as ESTIMATED.
completion_date_type	text		ACTUAL (a date that has occurred) or ESTIMATED (a planned date). The registry's older ANTICIPATED is published as ESTIMATED.
is_rate_computable	boolean		Whether the inputs support a rate.
rate_flag_reason	text		Why a rate could not be computed (null when it could).
duration_months	numeric	months	Months from start to primary completion.
duration_months_overall	numeric	months	Months from start to completion.
pts_per_month	numeric	participants per month	Participants enrolled per month.
pts_per_site_month	numeric	participants per site per month	Participants enrolled per site per month.

Trial extracted endpoints

pharma trials get <id> trial-extracted-endpoints

A language model's reading of a trial's primary-endpoint result from its posted results, one row per trial the extraction covered. The reading is extracted text, separate from the registry record.

One row per: trial with an extracted primary-endpoint reading
Key columns: trial_id

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

Columns 12

Trial extracted endpoints columns
Column	Type	Unit	Description
trial_id	text		The trial's ClinicalTrials.gov registry id (NCT, National Clinical Trial, number).
result_reported	boolean		Whether the extraction found a formal primary-endpoint result.
signal	text		The extracted reading, verbatim: met, missed, mixed, reported_indeterminate or not_reported.
outcome_category	text		The signal grouped as met, missed, mixed or unclear.
has_clean_outcome	boolean		Whether outcome_category is met, missed or mixed.
primary_endpoints	text[]		The primary endpoint descriptions the extraction found.
primary_endpoint_count	integer		Number of primary endpoints extracted.
confidence	numeric	fraction	The extraction's confidence, from 0 to 1.
evidence_quote	text		The text quoted as evidence for the reading.
source_url	text		The registry page the reading was taken from.
model	text		The model that produced the reading.
extracted_at	timestamp		When the extraction ran.

Trial outcome measures

pharma trials get <id> trial-outcome-measures

The outcome measures a trial posted results for, one row per measure.

One row per: posted outcome measure
Key columns: trial_id, outcome_id

Source: ClinicalTrials.gov via AACT, CTTI (CC BY)

Columns 6

Trial outcome measures columns
Column	Type	Description
trial_id	text	The trial's ClinicalTrials.gov registry id (NCT, National Clinical Trial, number).
outcome_id	integer	The outcome measure's registry id.
outcome_type	text	PRIMARY, SECONDARY or OTHER.
title	text	The outcome measure's title.
time_frame	text	The time frame the measure covers.
population	text	The population the measure is reported for.

Trial sites

pharma trials get <id> trial-sites

The study sites a trial lists, one row per listing, with the coordinates the trial page maps.

One row per: site listing on a trial (a repeat listing of the same site on the same trial carries a higher listing_ordinal)
Key columns: trial_id, site_key, listing_ordinal

Source: ClinicalTrials.gov via AACT, CTTI (CC BY); GeoNames (CC BY 4.0); Amazon Location Service (AWS Service Terms)

Columns 15

Trial sites columns
Column	Type	Unit	Description
trial_id	text		The trial's ClinicalTrials.gov registry id (NCT, National Clinical Trial, number).
site_key	text		A site key built from the normalized facility name, city, state and country.
listing_ordinal	integer		1 for the first listing of the site on the trial; higher for a repeat listing of the same site on the same trial.
facility_name	text		The facility name as filed.
city	text		City.
state	text		State or region.
country	text		Country.
postal	text		Postal code.
latitude	double	degrees	Latitude of the site, in degrees.
longitude	double	degrees	Longitude of the site, in degrees.
geo_precision	text		How exact the coordinate is (postal, city and so on).
coord_source	text		Where the coordinate came from: aact, geonames or als.
trial_status	text		The trial's overall status code.
trial_start_year	integer	calendar year	Year the trial started.
trial_title	text		The trial's brief title.

Markdown copy of this page

Commands

Trials

Sponsor trials

Trial adverse events

Trial changes

Trial delivery

Trial demographics

Trial drugs

Trial enrollment history

Trial enrollment rate

Trial extracted endpoints

Trial outcome measures

Trial sites