Database Feasibility Assessment and Attrition Funnel

In plain language

Before building a study, researchers first check whether a claims or health records database actually contains enough of the right patients to answer their question — this is called a feasibility assessment. If the database looks promising, they next apply every eligibility rule one by one, recording how many patients survive each step, until only the qualifying group remains; that shrinking list of counts is the attrition funnel. The funnel matters because it makes every decision about who gets into a study visible, so readers can judge whether the final group is representative and where patients were lost. Running this process early can also save months of work by flagging a doomed study before expensive data are purchased or programmed.

A database feasibility assessment answers a binary, fit-for-purpose question before any comparative analysis is written: does this data source contain enough of the right patients, with enough observable person-time, exposure detail, covariate capture, and ascertainable outcomes to estimate the target estimand with acceptable precision and credibility? The attrition funnel (also called a participant-flow or cohort-decrement diagram) is the auditable artifact that operationalizes that answer: a stepwise table that starts from the full source population and reports, for each eligibility, exposure, washout, and follow-up criterion in the order it is applied, how many patients (and how much person-time) are retained and dropped, ending at the analyzable N. It is the RWE analogue of the CONSORT flow diagram, and structured RWE reporting templates (STaRT-RWE, HARPER) require it precisely because the sequence and magnitude of losses is where most silent bias and most fatal feasibility problems live.

Core conceptual distinction

. Feasibility and attrition are two phases of the same fit-for-purpose discipline, and they must not be collapsed. Feasibility is a go/no-go decision made on aggregate counts and metadata — often before licensing a database — and is about relevance (does the source capture the population, exposure, and outcome?) and reliability (are those captured well enough to trust?). Attrition is the patient-level realization of the protocol's eligibility logic, and its job is transparency, not decision: it makes every inclusion/exclusion rule and its cost visible so reviewers can judge selection bias and generalizability. The critical, non-obvious point is that attrition is order-dependent and the order must mirror the protocol's causal logic, not convenience. Applying "≥1 outcome-free baseline year" before "first qualifying exposure" versus after changes both the denominator and which patients are excluded; applying continuous-enrollment requirements that extend after time zero builds future information into eligibility and manufactures immortal time. The funnel is therefore not bookkeeping — it is a specification of the study population, and a misordered funnel is a misspecified study. A feasibility assessment estimates the expected bottom of the funnel; the attrition table reports the actual one, and a large gap between them is itself a finding (usually unobserved person-time or stricter-than-anticipated capture).

Pros, cons, and trade-offs

. - vs jumping straight to cohort construction with no documented feasibility step: A formal feasibility assessment kills doomed studies cheaply (before a six-figure data license or six months of programming) and forces pre-specification of the expected analyzable N, event count, and exposure prevalence — numbers that anchor power and that regulators and HTA bodies expect to see justified. Cost: it adds an upfront aggregate-counting phase and tempts teams to "peek" at the outcome distribution, which can bias design choices. Prefer the formal step for any consequential comparative, safety, or decision-grade analysis; a quick informal scan suffices only for hypothesis-generating descriptive work. - vs an undocumented or post-hoc attrition narrative ("we excluded some patients"): A pre-specified, ordered funnel table is reproducible, lets reviewers re-derive each step, and exposes selection bias by showing where the cohort collapses. Cost: more programming and diagnostic counts. Always prefer the explicit funnel — STaRT-RWE, HARPER, RECORD, and FDA/EMA RWE guidance all treat it as mandatory. - vs a single overall "N excluded" number: The stepwise funnel localizes the loss (e.g., 70% lost at the continuous-enrollment step signals a data-completeness problem, not a rare disease), which a single number hides. Cost: requires committing to and defending a specific ordering. Prefer the stepwise funnel; reserve a collapsed count only for an abstract-level summary.

When to use

. Always, before finalizing any RWE protocol that will support a comparative, safety, utilization, cost, or regulatory/HTA decision: run a feasibility assessment to set go/no-go and to pre-specify expected N, person-time, exposure prevalence, and event counts; then implement the ordered attrition funnel as a required protocol/SAP deliverable and report it in the manuscript. Use it as the first diagnostic when a planned analyzable N comes in far below expectation, when comparing two candidate databases, and whenever a reviewer or regulator needs to audit how the study population was built.

When NOT to use — and when it is actively misleading or dangerous

. - As a substitute for design. A pristine funnel does not make a biased design valid; it documents selection, it does not remove confounding by indication, immortal time, or outcome misclassification. Treating "we showed the funnel" as evidence of validity is the dangerous failure. - When the ordering is chosen to maximize N rather than to encode the causal logic. Reordering steps to retain more patients (e.g., applying outcome-free baseline last so it discards fewer) silently changes the estimand and the eligible population; an order-optimized funnel is actively misleading. - When continuous-enrollment or "had a follow-up visit" criteria reach past time zero. Requiring future observability or a future event to qualify for entry builds immortal time and survivor bias directly into eligibility — the funnel will look clean while the design is fatally biased. - When feasibility counts are read as effect estimates. Aggregate cell counts run during feasibility are not adjusted, are subject to small-cell suppression, and must never be promoted to results; using them to pick the database that gives the "best" preliminary signal is outcome-dependent design. - When low yield is rationalized away instead of investigated. If 80% of patients are lost at one step, the right response is to diagnose the data source (capture gap? code-list error? plan type?), not to relax the criterion until enough remain.

Data-source operational depth

. - Administrative claims (FFS vs MA vs commercial): The funnel's most consequential early step is almost always continuous enrollment / observable person-time — absence of a claim means "not observed," not "did not occur," so washout and outcome-free requirements are only valid over enrolled spans. Failure mode: Medicare Advantage person-time lacks fee-for-service claims because care is capitated, so MA enrollees look event- and exposure-free; including MA-only spans inflates the eligible N and biases rates downward. Workaround: restrict to enrollees with the relevant benefit (Parts A/B/D or commercial medical+pharmacy) and exclude MA-only person-time, and report it as an explicit funnel step. Other failure modes: claims adjudication lag and reversals near the data cut create artificial right-censoring (impose a runout/maturity buffer); same-day duplicate and bundled/capitated claims distort counts; plan switching breaks enrollment continuity. - EHR: Capture is encounter-driven, so "no record of X" conflates a healthy patient, a patient who got care out-of-network, and a patient who simply did not visit. The funnel must include an explicit "minimum observability / in-network activity" step, and loss to follow-up must be treated as potentially informative (sicker patients leave the system). External-care leakage and missing structured fields mean exposure/outcome capture is systematically incomplete; where possible, validate against linked claims and quantify capture by site and calendar time. - Registry: Often complete and adjudicated for the index disease and key outcomes but typically weak for full exposure history and for events occurring outside the registry's remit. The feasibility step must check enrollment criteria, completeness, and the registry's catchment; the funnel should make linkage eligibility (who can be linked to claims/EHR for follow-up) an explicit decrement, since the linkable subset is a selected population. - Linked claims–EHR–vital records: The richest substrate (severity + completeness + reliable mortality) but linkage itself is a funnel step with selection: only the linkable subset enters, and order/fill/service date discrepancies between sources must be reconciled before time-zero assignment. A common, dangerous error is differential competing risks by exposure in elderly claims populations — if one arm is older/sicker, death (a competing event) differentially truncates follow-up; the funnel and the downstream estimand must account for this rather than treating death as ordinary censoring.

Worked claims example

Question: feasibility and cohort assembly for incident heart failure among new users of a study oral antihyperglycemic in a commercial + Medicare FFS claims database (2016–2023). The pre-specified, ordered funnel: (1) Source population with ≥1 pharmacy claim for the study drug class: N = 412,000. (2) First fill of the study drug (index date) on/after 2016-07-01 and with ≥365 days of database history available before index (left-truncation guard): retain 318,400; drop 93,600 (early-period fills with insufficient lookback). (3) Continuous medical + pharmacy enrollment for the full 365-day baseline through index, FFS-observable only — exclude Medicare Advantage-only person-time so "no prior fill" and "no prior HF" are genuinely observed: retain 171,200; drop 147,200 (this large step is the feasibility signal — most loss is unobservable baseline, chiefly MA-only spans, not true ineligibility). (4) New-user washout: no fill of the study drug or its comparator class in the 365-day baseline (defines incident use): retain 138,900; drop 32,300 (prevalent users). (5) Outcome-free baseline: no validated HF diagnosis in the 365-day lookback (the outcome algorithm = ≥1 inpatient or ≥2 outpatient HF `dx` codes ≥30 days apart): retain 124,500; drop 14,400 (prevalent HF). (6) Age ≥18 and indication confirmed (≥2 type-2 diabetes `dx` in baseline): retain 121,700. Analyzable cohort N = 121,700, with an expected event count (from the feasibility incidence-rate scan) of ~4,100 incident HF events over a median 2.1 years of follow-up — comfortably powered. Follow-up runs from index to first validated HF event, censoring at disenrollment, death (a competing event in this elderly-enriched cohort), end of data minus a 90-day claims-runout buffer, and — for an as-treated analysis — last `days_supply` end plus a grace period. Note how step (3) dominates the attrition: had MA-only person-time been left in, N would have looked far larger but rates of both exposure gaps and outcomes would have been spuriously low.

Worked example

Scenario

A research team wants to study new users of a type-2 diabetes drug in a commercial claims database covering 2018 to 2023. Before programming anything, they run a feasibility check and then apply their eligibility rules one at a time to build an attrition funnel. The goal is to know the final analyzable group size and to see exactly where patients are lost along the way.

Dataset

Attrition funnel: patients surviving each eligibility criterion in order, from the full database to the analyzable cohort.

Steps

• Step 1 is the starting point: every person who ever had a record in the database during the study years — 850,000 people.

• Step 2 filters to the 112,000 people who had at least one pharmacy fill for the study drug; everyone else is irrelevant to this question.

• Step 3 keeps only the 84,000 whose first fill falls late enough in the database that a full year of history exists before it; the 28,000 dropped here started the drug too early for a valid baseline to be constructed.

• Step 4 requires unbroken insurance coverage across that entire prior year so that a missing claim genuinely means no event, not just a gap in observation; 33,000 people are dropped here, which is the single largest loss and signals a real data-completeness challenge worth investigating.

• Step 5 removes the 10,000 people who had a fill of this drug class within the prior year, keeping only true new starters.

• Step 6 removes the 1,500 people under 18 or with a childhood-onset diabetes code, leaving the adult type-2 population.

• The final analyzable cohort is 39,500 patients — about 4.6% of the full database and 35% of those who ever filled the drug.

Result

The analyzable cohort is 39,500 patients (steps: 850,000 → 112,000 → 84,000 → 51,000 → 41,000 → 39,500). The largest single loss — 33,000 patients at the continuous enrollment step — is the most important feasibility signal: it tells the team that a substantial portion of drug users cannot be validly studied in this database because their baseline period is not fully observable. Running this funnel before purchasing additional data years or writing analysis code means the team can make a go/no-go decision and justify their expected sample size to reviewers and regulators.

Runnable example

import pandas as pd

WASHOUT = pd.Timedelta(days=365)   # baseline / new-user lookback
HF_CODES = {"I50", "I110", "I130"} # validated HF dx prefixes (illustrative)

def attrition_funnel(rx, enroll, dx, study_start="2016-07-01"):
    steps = []
    def record(label, ids):
        steps.append({"step": label, "n": len(ids)})
        return ids

    # (1) Source: anyone with a study-drug-class fill
    study = rx[rx["drug_class"] == "STUDY"].sort_values(["person_id", "fill_date"])
    ids = record("1. Has >=1 study-drug fill", set(study["person_id"]))

    # (2) First fill = index; on/after study_start with >=365d history available before it
    idx = study.groupby("person_id")["fill_date"].first().rename("index_date")
    idx = idx[idx >= pd.Timestamp(study_start)]
    ids = record("2. Index on/after study start", set(idx.index) & ids)

    # (3) Continuous, FFS-observable enrollment across full baseline through index (exclude MA-only)
    e = enroll.merge(idx, left_on="person_id", right_index=True)
    covers = e[(~e["ma_only"]) &
               (e["enroll_start"] <= e["index_date"] - WASHOUT) &
               (e["enroll_end"]   >= e["index_date"])]
    ids = record("3. Continuous FFS-observable baseline (no MA-only)", set(covers["person_id"]) & ids)

    # (4) New-user washout: no STUDY or COMPARATOR fill in the 365d before index
    idxm = idx.reset_index()
    prior = rx.merge(idxm, on="person_id")
    prior_ids = set(prior[(prior["drug_class"].isin(["STUDY", "COMPARATOR"])) &
                          (prior["fill_date"] <  prior["index_date"]) &
                          (prior["fill_date"] >= prior["index_date"] - WASHOUT)]["person_id"])
    ids = record("4. New user (drug-free washout)", ids - prior_ids)

    # (5) Outcome-free baseline: no validated HF (>=1 IP or >=2 OP HF dx >=30d apart) in lookback
    hf = dx[dx["dx_code"].str[:3].isin({c[:3] for c in HF_CODES})].merge(idxm, on="person_id")
    hf = hf[(hf["dx_date"] < hf["index_date"]) & (hf["dx_date"] >= hf["index_date"] - WASHOUT)]
    ip = set(hf[hf["care_setting"] == "IP"]["person_id"])
    op = hf[hf["care_setting"] == "OP"].sort_values(["person_id", "dx_date"])
    op_span = op.groupby("person_id")["dx_date"].agg(["min", "max", "count"])
    op_valid = set(op_span[(op_span["count"] >= 2) &
                           ((op_span["max"] - op_span["min"]) >= pd.Timedelta(days=30))].index)
    ids = record("5. Outcome-free baseline (no prevalent HF)", ids - ip - op_valid)

    funnel = pd.DataFrame(steps)
    funnel["dropped"] = funnel["n"].shift(1) - funnel["n"]
    cohort = idx.loc[idx.index.isin(ids)].reset_index().rename(columns={"index_date": "index_date"})
    cohort["baseline_start"] = cohort["index_date"] - WASHOUT
    return funnel, cohort

library(data.table)
WASHOUT <- 365L
hf_prefix <- c("I50", "I11", "I13")

attrition_funnel <- function(rx, enroll, dx, study_start = as.Date("2016-07-01")) {
  setDT(rx); setDT(enroll); setDT(dx)
  steps <- list(); rec <- function(label, ids) { steps[[length(steps)+1L]] <<- list(step=label, n=length(ids)); ids }

  study <- rx[drug_class == "STUDY"][order(person_id, fill_date)]
  ids <- rec("1. Has >=1 study-drug fill", unique(study$person_id))

  idx <- study[, .(index_date = fill_date[1L]), by = person_id][index_date >= study_start]
  ids <- rec("2. Index on/after study start", intersect(idx$person_id, ids))

  e <- merge(enroll, idx, by = "person_id")
  covers <- e[!ma_only & enroll_start <= index_date - WASHOUT & enroll_end >= index_date, unique(person_id)]
  ids <- rec("3. Continuous FFS-observable baseline (no MA-only)", intersect(covers, ids))

  pr <- merge(rx, idx, by = "person_id")
  prior_ids <- unique(pr[drug_class %chin% c("STUDY","COMPARATOR") &
                         fill_date < index_date & fill_date >= index_date - WASHOUT, person_id])
  ids <- rec("4. New user (drug-free washout)", setdiff(ids, prior_ids))

  hf <- merge(dx[substr(dx_code,1,3) %chin% hf_prefix], idx, by = "person_id")
  hf <- hf[dx_date < index_date & dx_date >= index_date - WASHOUT]
  ip <- unique(hf[care_setting == "IP", person_id])
  op <- hf[care_setting == "OP"][order(person_id, dx_date)]
  opv <- op[, .(n = .N, span = as.integer(max(dx_date) - min(dx_date))), by = person_id][n >= 2 & span >= 30, person_id]
  ids <- rec("5. Outcome-free baseline (no prevalent HF)", setdiff(ids, union(ip, opv)))

  funnel <- rbindlist(steps)
  funnel[, dropped := shift(n) - n]
  cohort <- idx[person_id %chin% ids][, baseline_start := index_date - WASHOUT]
  list(funnel = funnel, cohort = cohort)
}