Drawing on our experience developing the database (Figure 1), we identified 4 key methodological principles for harmonizing public HIV and SDOH data. These findings carry direct implications for how future teams should structure similar work.

The final database comprises 242 variables pulled from 8 publicly available data sources (Table 1) supplemented by 14 clinic-specific variables (eg, Ryan White HIV/AIDS Program parts, clinic address, clinic name, and associated HIV Implementation Science Coordination Initiative hub) made available by investigators participating in the parent multisite study. The 104 Ryan White HIV/AIDS Program–associated clinics of interest, the primary unit of analysis, were located in 5 states: Maryland, Illinois, Alabama, Texas, and California (Table 2) [13]. Variables were mapped across various levels of geography: state, county, zip code, and individual clinic [14]. The HIV Implementation Science Coordination Initiative serves as a National Institutes of Health–funded technical support network that enhances the use of implementation science and related qualitative and quantitative methods in National Institutes of Health–funded EHE pilot projects [15].

Of the 242 variables, 238 (98.3%) were continuous variables (eg, percentages and rates), consistent with the nature of the data (eg, social determinants and population statistics), and 4 (1.7%) were categorical variables (eg, geographic coding for HIV-related laws, urban and rural classification, and Safe Drinking Water Act violations). Variables were grouped into four conceptual domains (Tables S1-S3 in Multimedia Appendix 1): (1) environmental variables (eg, population demographics, employment, poverty, housing, education, health care access, and crime), (2) HIV-related variables (eg, demographics of people living with HIV, HIV prevalence and incidence, HIV criminalization laws, and infection routes), (3) implementation outcome variables (eg, HIV mortality rates, preexposure prophylaxis use, and viral suppression), and (4) clinic variables (eg, patient demographics and patient financial information).

The dataset, data dictionary, and SAS code are publicly available at the Yale Center for Methods in Implementation and Prevention Science website [25]. The data dictionary maps each variable to its definition, SAS label, source, data type, and geographic level and is a resource we consider as important as the database itself.

The database build unfolded across 3 phases: source identification (approximately 2 months), database construction (approximately 6 months), and quality control (approximately 6 weeks), requiring approximately 350 total hours across 3 team members (roughly 250 core build hours for data sourcing, quality control, and cleaning plus approximately 100 hours for meetings and project management). What follows is not a chronological project account but a distillation of the 4 decisions that most shaped our experience, with each framed explicitly as a transferable methodological advance. These lessons are grounded in prior literature in public health data integration, which consistently highlights that successful integration depends on interoperable, standard-based systems such as Fast Healthcare Interoperability Resources [26], which facilitate efficient data exchange between health care and public health systems. Federal initiatives such as the Centers for Disease Control and Prevention’s Data Modernization Initiative and the Trusted Exchange Framework and Common Agreement underscore the value of standardized electronic case reporting to enhance the timeliness and completeness of public health data [27,28]. Research on chronic disease surveillance further highlights ongoing fragmentation across data sources and calls for integrated, multi-sectoral data pipelines to reduce silos and improve data quality [29]. Our experience both confirms and extends these findings by providing a concrete, replicable methodology for building such pipelines from publicly available sources.

One of our most consequential decisions was spending 2 months identifying and vetting sources prior to database construction. As we engaged in this process, key data sources and variables emerged through expert consultations and iterative review until no new sources emerged and saturation was reached. Selection criteria prioritized relevance to project goals and data availability. Clinic inclusion was limited to clinics participating in the parent multisite study with available geographic data. The diversity of and open access to public data were critical in supporting database development. The existence of datasets that covered a range of themes and were tagged to geographic levels made pulling and linking datasets by geography simple and feasible. Furthermore, many datasets allowed for direct download, facilitating linkage and checking. Notably, some datasets were already preaggregated for the purpose of supporting analysis of HIV determinants, which limited the number of unique sources to be identified. We recommend this approach strongly, with one caveat: source identification should be treated as a discrete phase. As we lacked a centralized repository of HIV and SDOH datasets, sources were sometimes added ad hoc as expert input was received throughout the database build. This pattern forced us to repeatedly revisit and update things that were already built, which led to complicated version control. Teams should treat source identification as a bounded phase with clear saturation criteria and resist adding sources once construction has begun. To facilitate consistency and continuity, a comprehensive data dictionary was developed early in this phase. This served as a foundational reference for both existing and new team members by outlining each variable’s definition, source, data type, and geographic level. We were mindful of potential variable selection bias and attempted to minimize it by applying clear inclusion criteria aligned with study objectives and documenting all decisions within the data dictionary. During this phase, preliminary metrics were cataloged in an offline Microsoft Excel workbook before being transferred to a cloud-based Microsoft Excel platform to enable collaborative integration. This step proved essential for coordinating work across team members and maintaining a shared understanding of emerging data structures, especially for onboarding new contributors. We regard this documentation step as nonnegotiable.

This is our most direct recommendation. Our database build relied primarily on manual data entry, a method that, while adaptable, resulted in a notable initial error rate of approximately 33%, computed as flagged discrepancies divided by total spot-checked values (roughly 10% of all entered data, sampled as every fifth row per source and geographic level). An “error” was defined as any value that differed from the authoritative source on the date of verification, including discrepancies from data entry mistakes, source updates, or rounding differences. All flagged discrepancies were subsequently corrected, but the rework was substantial.

This experience highlights an important limitation and area for process-related improvement. Future efforts would benefit from the adoption of standard data engineering tools and automation, such as SQL databases; extract, transform, and load pipelines; application programming interfaces, and web scraping libraries such as R’s rvest [30] or Python’s pandas [31]. We adopted automated web scraping later in the build for quality control, which substantially reduced reconciliation effort and would have reduced errors had it been used for initial extraction. While there is a learning curve for these tools, their integration could enhance data accuracy, reduce labor-intensive manual processes, and improve overall efficiency in database construction and maintenance.

We also recommend using a version-controlled, access-managed cloud-based platform (we used Microsoft Excel hosted on Northwestern University’s secure servers). This approach ensured that all team members accessed a single, up-to-date version of the database. Asynchronous communication, including email, enabled the team to divide tasks across different time zones and coordinate effectively but only because version control prevented conflicts that often arise with parallel local editing.

In our build, quality control was only applied in the final phase. We recommend that it be built into the workflow of the entire database construction in future builds. While we relied on 2 complementary approaches that helped us assess the consistency and accuracy of the compiled data (manual spot checks and use of a third-party web scraping tool), errors had accumulated over months of entry. The 33% discrepancy rate we observed reflects not only the error-prone nature of manual data entry, but equally the decision to conduct validation and quality checks only after months of data entry had occurred rather than building them in continuously throughout the database development process.

We recommend integrating verification at each data entry milestone: upon completing each source, each geographic level, and each domain. Other practical considerations for future database builds include the importance of documenting decisions, establishing shared naming conventions and data format standards before entry begins, and incorporating tools not only for quality control but also for initial extraction where possible.

Although several experts were involved in identifying databases, the final build was primarily executed by 1 Master’s student under the guidance of a lead researcher, with 3 team members contributing to data extraction. This lean structure enabled efficient communication and clear accountability. We found that expanding the team would have introduced coordination costs that likely exceeded the time saved.

However, team stability depends on protected time. One direct consequence of resource constraints was that the clinic sample had to be fixed before the parent multisite study was finalized, resulting in a smaller clinic count than the number ultimately enrolled in the main study. Teams should anticipate that timeline pressures create scope constraints and plan accordingly. While there is potential to improve efficiency, the feasibility of this approach suggests that similar builds can be accomplished with modest teams and resources if timelines and availability are managed effectively. We estimate that a team with this structure can complete a comparable build in roughly 250 to 350 hours with 250 core build hours (data sourcing, quality control, and cleaning) plus approximately 100 hours of meetings and project management, although automated tooling from the start could reduce the core build hours substantially.