# Trials: pharma trials

The pharma trials commands, with each dataset's columns, units and source credit.

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

| 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](https://pharmaobservatory.com/cli)

## Trials

**Command**

```sh
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)

| 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. |

## Sponsor trials

**Command**

```sh
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)

| 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

**Command**

```sh
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)

| 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

**Command**

```sh
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)

| Column | Type | Unit | 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

**Command**

```sh
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)

| 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

**Command**

```sh
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)

| 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

**Command**

```sh
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)

| 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

**Command**

```sh
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)

| 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

**Command**

```sh
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)

| 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

**Command**

```sh
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)

| 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

**Command**

```sh
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)

| Column | Type | Unit | 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

**Command**

```sh
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)

| 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. |