AACT database guide for clinical trial analysts

AACT stands for Aggregate Analysis of ClinicalTrials.gov. It is maintained by the Clinical Trials Transformation Initiative (CTTI), a public private partnership that works on improving the quality and efficiency of clinical trials.

The idea is simple. ClinicalTrials.gov is built for looking up one study at a time, while AACT restructures the same records into a PostgreSQL database so you can ask questions across thousands of studies at once.

The database is refreshed daily from ClinicalTrials.gov, so a newly registered trial or an updated status usually appears in AACT within a day.

CTTI offers several ways in: a hosted cloud database you connect to with your own credentials, downloadable database snapshots you restore locally, and flat files for people who prefer to load the data into a spreadsheet tool or a statistics package.

AACT contains only what sponsors registered and reported on ClinicalTrials.gov. It does not include publications, and it does not include trials registered only in other registries.

Every study in AACT is identified by its NCT number, written as NCT followed by eight digits. Almost every table carries this identifier, and it is the key you join on.

The design is one row per study in the central table and many rows per study in the surrounding tables. A trial with three conditions has three condition rows, a trial with forty sites has forty facility rows.

These are the tables most analysts use first, described in general terms:

• studies: one row per trial with the core descriptors, such as titles, study type, phase, overall status, key dates and enrolment
• conditions: the conditions or diseases each study lists, as entered by the sponsor
• interventions: the drugs, devices, procedures, behavioral or other interventions each study tests
• design_outcomes: the primary and secondary outcome measures as planned in the registration
• outcome_measurements: the reported values for outcomes, for studies that posted results
• sponsors: the lead sponsor and collaborators, with a sponsor class such as industry or other
• facilities: the sites where the study runs, with location details
• calculated_values: fields CTTI derives to make analysis easier, such as counts of sites and helper flags

Other tables cover eligibility criteria, design details, arm groups, countries, baseline characteristics, adverse events and outcome analyses. The official AACT documentation and data dictionary describe every table and column, and it is worth keeping open while you write queries.

You do not need complex SQL to get value out of AACT. Most landscape questions follow a few patterns.

Count by phase and status. Join conditions to studies, filter on a condition term, then group by phase and overall status to see how many trials are recruiting, completed or terminated.

Find who is active. Join sponsors to studies for one indication, restrict to industry sponsors and recent start dates, and rank sponsors by the number of trials.

Map geography. Join facilities to studies and count trials or sites by country to see where recruitment happens for a disease.

Review endpoints. Pull the primary outcome measures from design_outcomes for all phase 3 trials in an indication and read how others defined success, including the time frame.

Check results reporting. Filter completed studies by their completion date and check whether results were posted, which is a common question in transparency research.

Each of these is a join on the NCT number plus a filter and a count. The hard part is not the SQL, it is getting the filters right.

AACT reflects what sponsors typed into ClinicalTrials.gov. That makes it complete for registered studies but uneven in how things are described.

• Condition terms are free text. The same disease appears under several spellings, abbreviations and broader or narrower terms, so a single exact match misses trials. Use the MeSH based browse tables or build a list of synonyms.
• Intervention names vary. Code names, generic names and brand names refer to the same compound, and combinations are listed in different ways.
• One row per study is not the default. Joining several one to many tables at once multiplies rows, so counts of trials quietly turn into counts of combinations. Count distinct NCT numbers.
• Enrolment can be anticipated or actual. The type changes as a trial progresses, so mixing them overstates or understates the real size of a landscape.
• Dates can be partial or estimated. Start and completion dates may be planned rather than actual, and some carry only a month and year.
• Results exist for a subset. Many studies never post results, so outcome tables describe only reporting trials, which is not representative of all trials.
• Outcome values are heterogeneous. Units, time frames and analysis populations differ between trials, so numbers from outcome_measurements are rarely comparable without careful reading.
• The schema can change. When ClinicalTrials.gov changes its data model, AACT tables and columns can change too, so saved queries need occasional maintenance.
• Registry is not literature. A trial without posted results may have a peer reviewed paper, and AACT does not link to it.

A few habits prevent most errors in AACT based work.

• Write the question down first, in the shape of population, intervention, comparator and outcome. Our guide to the PICOS framework shows how.
• Build and save your condition and intervention term lists, and record why each term is included.
• Count distinct NCT numbers at every step, and check a handful of records by hand on ClinicalTrials.gov.
• Record the date of your data pull, because statuses and enrolment change daily.
• Keep registered and published evidence separate in your report, and say which one each number comes from.
• Read the record before quoting an endpoint or a result, especially where units or time frames differ.

AACT is the right tool when you need a custom dataset, a reproducible pipeline or a statistical analysis across thousands of trials. Nothing replaces it for that.

Many daily evidence questions are smaller. A medical affairs lead wants to know which phase 3 trials test a class of drugs in an indication, what they used as primary endpoints and which ones were terminated. A feasibility analyst wants the active sponsors and countries for a rare disease.

For these questions, writing joins, cleaning synonyms and checking records by hand takes longer than reading the answer. ClinicalEvidence searches registered trials and published studies together, writes an answer only from retrieved passages and attaches an NCT number or PMID to every sentence.

• Semantic search finds trials described with different words, so you are not limited to exact condition terms.
• The clinical trial table lists phase, population, interventions, endpoints, sponsor, status, enrolment, dates and locations for every matching study, sortable and filterable.
• Every citation is verified against the index, and an answer without verified sources is not shown.
• The export of clinical trial data gives you CSV, XLSX or DOCX with a reference list, so you can still run your own analysis afterwards.
• Publications sit next to registry records, which AACT on its own does not offer.

Many analysts use both: a cited answer to scope the question in minutes, then AACT when the project needs a full reproducible dataset. If you search the registry by hand, our guide on how to search ClinicalTrials.gov covers the interface itself.

• AACT is a PostgreSQL copy of ClinicalTrials.gov from CTTI, refreshed daily.
• The NCT number links every table, and most tables hold many rows per study.
• Free text conditions, mixed enrolment types and partial results are the main sources of error.
• It covers registered trials only, not publications or other registries.
• For quick, cited landscape answers, a verified AI answer layer is faster, and AACT stays the tool for custom datasets.