Database Feasibility Assessment and Attrition Funnel

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

Decision diagram

flowchart TD
  S["(1) Source population<br/>>=1 study-drug fill<br/>N = 412,000"]
  S -->|"-93,600: index before study start<br/>or <365d history"| A["(2) Index on/after study start<br/>N = 318,400"]
  A -->|"-147,200: not FFS-observable<br/>(mostly MA-only baseline)"| B["(3) Continuous FFS-observable baseline<br/>N = 171,200"]
  B -->|"-32,300: prevalent users"| C["(4) New user, drug-free washout<br/>N = 138,900"]
  C -->|"-14,400: prevalent HF"| D["(5) Outcome-free baseline<br/>N = 124,500"]
  D -->|"-2,800: age <18 or indication unconfirmed"| E["(6) Analyzable cohort<br/>N = 121,700<br/>~4,100 expected HF events"]

flowchart TD
  Q["Candidate data source for the estimand"] --> P{"Population captured<br/>with the indication?"}
  P -->|No| STOP1["Infeasible: wrong population"]
  P -->|Yes| X{"Exposure ascertainable<br/>NDC/fill or order/admin?"}
  X -->|No| STOP2["Infeasible: exposure not captured"]
  X -->|Yes| O{"Outcome ascertainable<br/>validated algorithm exists?"}
  O -->|No| STOP3["Infeasible / needs linkage or validation"]
  O -->|Yes| T{"Enough observable person-time<br/>and expected events for power?"}
  T -->|No| STOP4["Underpowered: consider linkage,<br/>pooling, or different source"]
  T -->|Yes| GO["Feasible: proceed to ordered<br/>attrition funnel and protocol"]

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

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.

Trade-offs

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)
}

%let washout = 365;
%let start   = '01JUL2016'd;

proc sql; create table funnel (step char(60), n num); quit;
%macro count(lbl, ds);
  proc sql; insert into funnel
    select "&lbl", count(distinct person_id) from &ds; quit;
%mend;

/* (1) Source: any study-drug fill */
proc sql; create table s1 as select distinct person_id from work.rx where drug_class='STUDY'; quit;
%count(1. Has >=1 study-drug fill, s1)

/* (2) First study fill = index; on/after study start */
proc sql;
  create table idx as
  select person_id, min(fill_date) as index_date format=date9.
  from work.rx where drug_class='STUDY' group by person_id
  having calculated index_date >= &start;
quit;
%count(2. Index on/after study start, idx)

/* (3) Continuous FFS-observable baseline through index (exclude MA-only spans) */
proc sql;
  create table s3 as
  select i.person_id, i.index_date from idx i
  where exists (select 1 from work.enroll e
                where e.person_id=i.person_id and e.ma_only=0
                  and e.enroll_start <= i.index_date - &washout
                  and e.enroll_end   >= i.index_date);
quit;
%count(3. Continuous FFS-observable baseline (no MA-only), s3)

/* (4) New-user washout: no STUDY/COMPARATOR fill in the 365d before index */
proc sql;
  create table s4 as
  select c.* from s3 c
  where not exists (select 1 from work.rx p
                    where p.person_id=c.person_id
                      and p.drug_class in ('STUDY','COMPARATOR')
                      and p.fill_date <  c.index_date
                      and p.fill_date >= c.index_date - &washout);
quit;
%count(4. New user (drug-free washout), s4)

/* (5) Outcome-free baseline: >=1 IP or >=2 OP HF dx >=30d apart in lookback */
proc sql;
  create table prev_hf as
  select c.person_id from s4 c
  where exists (select 1 from work.dx d where d.person_id=c.person_id
                  and d.care_setting='IP' and substr(d.dx_code,1,3) in ('I50','I11','I13')
                  and d.dx_date < c.index_date and d.dx_date >= c.index_date - &washout)
     or (select count(*) from work.dx d where d.person_id=c.person_id
                  and d.care_setting='OP' and substr(d.dx_code,1,3) in ('I50','I11','I13')
                  and d.dx_date < c.index_date and d.dx_date >= c.index_date - &washout) >= 2;
  create table cohort as
  select c.person_id, c.index_date, c.index_date - &washout as baseline_start format=date9.
  from s4 c where c.person_id not in (select person_id from prev_hf);
quit;
%count(5. Outcome-free baseline (no prevalent HF), cohort)

/* Funnel with per-step drops */
data funnel; set funnel; dropped = lag(n) - n; run;

Citations

1. [1]Wang SV, Pinheiro S, Hua W, et al. STaRT-RWE: structured template for planning and reporting on the implementation of real world evidence studies. BMJ. 2021;372:m4856.
2. [2]Wang SV, Pottegård A, Crown W, et al. HARmonized Protocol Template to Enhance Reproducibility of hypothesis evaluating real-world evidence studies on treatment effects: A good practices report of a joint ISPE/ISPOR task force. Pharmacoepidemiology and Drug Safety. 2022.

1. [3]Benchimol EI, Smeeth L, Guttmann A, et al. The REporting of studies Conducted using Observational Routinely-collected health Data (RECORD) statement. PLoS Medicine. 2015;12(10):e1001885.

1. [4]Berger ML, Sox H, Willke RJ, et al. Good practices for real-world data studies of treatment and/or comparative effectiveness: Recommendations from the joint ISPOR-ISPE Special Task Force on real-world evidence in health care decision making. Pharmacoepidemiology and Drug Safety. 2017;26(9):1033-1039.