IDDS Spending

Author

Matt Broerman

library(googlesheets4)
library(dplyr)
library(tibble)
library(ggplot2)
library(stringr)
library(scales)

Data is export from https://www.usaspending.gov/award/CONT_AWD_7200AA18M00010_7200_GS00Q14OADU119_4732

I’ll figure out the API later so this can be updated readily.

Our view is this is very important work that allocated funds must be spent cost-effectively.

Federal Accounts

fed_accts <- read_sheet("14lNw24Sdjg1WdSBNoPfB_VlL5QbA18jZrhjcAdpgYTE")

Let’s look for dates

fed_accts |> 
  select(submission_period, contains("date")) |> 
  glimpse()

Rows: 459
Columns: 9
$ submission_period                      <chr> "FY2023P08", "FY2023P08", "FY20…
$ award_base_action_date                 <dttm> 2018-05-25, 2018-05-25, 2018-0…
$ award_base_action_date_fiscal_year     <dbl> 2018, 2018, 2018, 2018, 2018, 2…
$ award_latest_action_date               <dttm> 2023-11-14, 2023-11-14, 2023-1…
$ award_latest_action_date_fiscal_year   <dbl> 2024, 2024, 2024, 2024, 2024, 2…
$ period_of_performance_start_date       <dttm> 2018-05-25, 2018-05-25, 2018-0…
$ period_of_performance_current_end_date <dttm> 2024-05-21, 2024-05-21, 2024-0…
$ ordering_period_end_date               <lgl> NA, NA, NA, NA, NA, NA, NA, NA,…
$ last_modified_date                     <dttm> 2023-06-28, 2023-06-28, 2023-0…

fed_accts |> 
  count(award_base_action_date)

# A tibble: 1 × 2
  award_base_action_date     n
  <dttm>                 <int>
1 2018-05-25 00:00:00      459

fed_accts |> 
  count(period_of_performance_start_date)

# A tibble: 1 × 2
  period_of_performance_start_date     n
  <dttm>                           <int>
1 2018-05-25 00:00:00                459

We don’t have clear dates here. All the award dates are the same for all rows. But there are submission_period, which are roughly monthly, though there are a few periods denominated in quarters (early)

fed_accts |> 
  filter(str_detect(submission_period, "Q")) |> 
  select(
    owning_agency_name,
    submission_period,
    gross_outlay_amount_FYB_to_period_end,
    transaction_obligated_amount
  )

# A tibble: 10 × 4
   owning_agency_name                   submission_period gross_outlay_amount_…¹
   <chr>                                <chr>                              <dbl>
 1 Department of State                  FY2020Q2                         243283.
 2 Department of State                  FY2020Q2                         119161.
 3 Department of State                  FY2020Q2                         624437.
 4 Department of State                  FY2020Q2                              0 
 5 Department of State                  FY2020Q2                              0 
 6 Department of State                  FY2020Q2                        2049936.
 7 Agency for International Development FY2020Q2                              0 
 8 Department of State                  FY2018Q3                              0 
 9 Department of State                  FY2018Q3                              0 
10 Department of State                  FY2019Q4                              0 
# ℹ abbreviated name: ¹gross_outlay_amount_FYB_to_period_end
# ℹ 1 more variable: transaction_obligated_amount <dbl>

I am assuming here “outlay” means bills in common language. A little extraction and cleanup.

fed_accts |> 
  select(submission_period) |> 
  mutate(year = str_extract(submission_period, "\\d{4}")) |> 
  mutate(month = str_extract(submission_period, "\\d{2}$"))

# A tibble: 459 × 3
   submission_period year  month
   <chr>             <chr> <chr>
 1 FY2023P08         2023  08   
 2 FY2023P08         2023  08   
 3 FY2023P07         2023  07   
 4 FY2023P07         2023  07   
 5 FY2023P07         2023  07   
 6 FY2023P07         2023  07   
 7 FY2023P07         2023  07   
 8 FY2023P07         2023  07   
 9 FY2023P07         2023  07   
10 FY2023P07         2023  07   
# ℹ 449 more rows

fed_dollars <- fed_accts |>
  select(
    outlay = gross_outlay_amount_FYB_to_period_end,
    oblige = transaction_obligated_amount,
    fund = disaster_emergency_fund_name,
    agency = owning_agency_name,
    submission_period
  ) |>
  mutate(
    fund = case_match(
      fund,
      "American Rescue Plan Act of 2021" ~ "ARP 2021",
      "Coronavirus Preparedness and Response Supplemental Appropriations Act, 2020" ~ "CPRSAA 2020"
    )
  ) |>
  mutate(
    agency = case_match(
      agency,
      "Department of State" ~ "DoS",
      "Agency for International Development" ~ "USAID"
    )
  ) |>
  mutate(year = str_extract(submission_period, "\\d{4}")) |>
  mutate(month = str_extract(submission_period, "\\d{2}$")) |>
  mutate(date = as.Date(str_c(year, month, "01", sep = "-"))) |>
  arrange(date)

fed_dollars |> 
  ggplot(aes(date, outlay, color = fund, shape = agency)) +
  geom_point() +
  scale_y_continuous(labels = dollar_format()) +
  theme_minimal() +
  labs(title = "Bills from ICF")

So there seem to be a bunch of constant charges as we expect, and then some that are larger and almost monotonic increasing to year end. They are greater than ~$2.5M. Almost all of these are DoS so maybe the data is off?

fed_dollars |> 
  filter(outlay > 2.5E6) |> 
  ggplot(aes(date, outlay, color = fund, shape = agency)) +
  geom_point() +
  scale_y_continuous(labels = dollar_format()) +
  theme_minimal()

These last bills contribute overwhelmingly to the cumulative costs.

fed_dollars |> 
  summarise(outlay = sum(outlay, na.rm = FALSE), .by = c(fund, agency, date)) |> 
  filter(!is.na(outlay)) |> 
  mutate(outlay = cumsum(outlay), .by = c(fund, agency)) |> 
  ggplot(aes(date, outlay, color = fund, shape = agency)) +
  geom_point() +
  scale_y_continuous(labels = dollar_format()) +
  theme_minimal() +
  labs(title = "Cumulative bills from ICF")

Sub Awards

These are the sub contracts that ICF writes I guess?

sub_awards <- read_sheet("1y04jd7Q1iqJj6uNfVtXlfsG1_JD8d5KG3kt5VbBGZLw")

sub_awards |> 
  glimpse()

Rows: 58
Columns: 118
$ prime_award_unique_key                                     <chr> "CONT_AWD_7…
$ prime_award_piid                                           <chr> "7200AA18M0…
$ prime_award_parent_piid                                    <chr> "GS00Q14OAD…
$ prime_award_amount                                         <dbl> 134954766, …
$ prime_award_disaster_emergency_fund_codes                  <chr> "L: Emergen…
$ `prime_award_outlayed_amount_from_COVID-19_supplementals`  <dbl> 15081463, 1…
$ `prime_award_obligated_amount_from_COVID-19_supplementals` <dbl> 14700000, 1…
$ prime_award_outlayed_amount_from_IIJA_supplemental         <lgl> NA, NA, NA,…
$ prime_award_obligated_amount_from_IIJA_supplemental        <lgl> NA, NA, NA,…
$ prime_award_total_outlayed_amount                          <dbl> 119884614, …
$ prime_award_base_action_date                               <dttm> 2018-05-25…
$ prime_award_base_action_date_fiscal_year                   <dbl> 2018, 2018,…
$ prime_award_latest_action_date                             <dttm> 2023-11-14…
$ prime_award_latest_action_date_fiscal_year                 <dbl> 2024, 2024,…
$ prime_award_period_of_performance_start_date               <dttm> 2018-05-25…
$ prime_award_period_of_performance_current_end_date         <dttm> 2024-05-21…
$ prime_award_period_of_performance_potential_end_date       <dttm> 2024-05-21…
$ prime_award_awarding_agency_code                           <dbl> 72, 72, 72,…
$ prime_award_awarding_agency_name                           <chr> "Agency for…
$ prime_award_awarding_sub_agency_code                       <dbl> 7200, 7200,…
$ prime_award_awarding_sub_agency_name                       <chr> "AGENCY FOR…
$ prime_award_awarding_office_code                           <chr> "7200AA", "…
$ prime_award_awarding_office_name                           <chr> "USAID M/OA…
$ prime_award_funding_agency_code                            <dbl> 72, 72, 72,…
$ prime_award_funding_agency_name                            <chr> "Agency for…
$ prime_award_funding_sub_agency_code                        <dbl> 7200, 7200,…
$ prime_award_funding_sub_agency_name                        <chr> "AGENCY FOR…
$ prime_award_funding_office_code                            <chr> "7200AA", "…
$ prime_award_funding_office_name                            <chr> "USAID M/OA…
$ prime_award_treasury_accounts_funding_this_award           <chr> "072-019-20…
$ prime_award_federal_accounts_funding_this_award            <chr> "019-1031;0…
$ prime_award_object_classes_funding_this_award              <chr> "41.0: Gran…
$ prime_award_program_activities_funding_this_award          <chr> "0001: DIRE…
$ prime_awardee_uei                                          <chr> "QHBLBNKKV4…
$ prime_awardee_duns                                         <lgl> NA, NA, NA,…
$ prime_awardee_name                                         <chr> "ICF INCORP…
$ prime_awardee_dba_name                                     <lgl> NA, NA, NA,…
$ prime_awardee_parent_uei                                   <chr> "K76NME5D2U…
$ prime_awardee_parent_duns                                  <lgl> NA, NA, NA,…
$ prime_awardee_parent_name                                  <chr> "ICF INTERN…
$ prime_awardee_country_code                                 <chr> "USA", "USA…
$ prime_awardee_country_name                                 <chr> "UNITED STA…
$ prime_awardee_address_line_1                               <chr> "1902 RESTO…
$ prime_awardee_city_name                                    <chr> "RESTON", "…
$ prime_awardee_county_fips_code                             <dbl> 51059, 5105…
$ prime_awardee_county_name                                  <chr> "FAIRFAX", …
$ prime_awardee_state_fips_code                              <dbl> 51, 51, 51,…
$ prime_awardee_state_code                                   <chr> "VA", "VA",…
$ prime_awardee_state_name                                   <chr> "VIRGINIA",…
$ prime_awardee_zip_code                                     <dbl> 201905231, …
$ prime_award_summary_recipient_cd_original                  <chr> "VA-11", "V…
$ prime_award_summary_recipient_cd_current                   <chr> "VA-11", "V…
$ prime_awardee_foreign_postal_code                          <lgl> NA, NA, NA,…
$ prime_awardee_business_types                               <chr> NA, NA, "Fo…
$ prime_award_primary_place_of_performance_city_name         <chr> "WASHINGTON…
$ prime_award_primary_place_of_performance_county_fips_code  <dbl> 11001, 1100…
$ prime_award_primary_place_of_performance_county_name       <chr> "DISTRICT O…
$ prime_award_primary_place_of_performance_state_fips_code   <dbl> 11, 11, 11,…
$ prime_award_primary_place_of_performance_state_code        <chr> "DC", "DC",…
$ prime_award_primary_place_of_performance_state_name        <chr> "DISTRICT O…
$ prime_award_primary_place_of_performance_address_zip_code  <dbl> 205230001, …
$ prime_award_summary_place_of_performance_cd_original       <chr> "DC-98", "D…
$ prime_award_summary_place_of_performance_cd_current        <chr> "DC-98", "D…
$ prime_award_primary_place_of_performance_country_code      <chr> "USA", "USA…
$ prime_award_primary_place_of_performance_country_name      <chr> "UNITED STA…
$ prime_award_base_transaction_description                   <chr> "THE STRATE…
$ prime_award_project_title                                  <chr> "IDDS - Inf…
$ prime_award_naics_code                                     <dbl> 541330, 541…
$ prime_award_naics_description                              <chr> "ENGINEERIN…
$ prime_award_national_interest_action_code                  <chr> "NONE", "NO…
$ prime_award_national_interest_action                       <chr> "NONE", "NO…
$ subaward_type                                              <chr> "sub-contra…
$ subaward_fsrs_report_id                                    <chr> "B498B75E12…
$ subaward_fsrs_report_year                                  <dbl> 2020, 2021,…
$ subaward_fsrs_report_month                                 <dbl> 7, 4, 4, 5,…
$ subaward_number                                            <list> "19MSSK005…
$ subaward_amount                                            <dbl> 47088.00, 3…
$ subaward_action_date                                       <dttm> 2020-07-10…
$ subaward_action_date_fiscal_year                           <dbl> 2020, 2021,…
$ subawardee_uei                                             <chr> "XLKVP9LN6T…
$ subawardee_duns                                            <dbl> 117154926, …
$ subawardee_name                                            <chr> "ANSON DANI…
$ subawardee_dba_name                                        <chr> "DANIEL ANS…
$ subawardee_parent_uei                                      <chr> NA, NA, "C8…
$ subawardee_parent_duns                                     <dbl> NA, NA, NA,…
$ subawardee_parent_name                                     <chr> NA, NA, "DA…
$ subawardee_country_code                                    <chr> "USA", "USA…
$ subawardee_country_name                                    <chr> "UNITED STA…
$ subawardee_address_line_1                                  <chr> "433 UVILLA…
$ subawardee_city_name                                       <chr> "SHENANDOAH…
$ subawardee_state_code                                      <list> "WV", "WV"…
$ subawardee_state_name                                      <chr> "WEST VIRGI…
$ subawardee_zip_code                                        <dbl> 254424620, …
$ subaward_recipient_cd_original                             <chr> "WV-02", "W…
$ subaward_recipient_cd_current                              <chr> "WV-02", "W…
$ subawardee_foreign_postal_code                             <dbl> NA, NA, NA,…
$ subawardee_business_types                                  <chr> "FOR-PROFIT…
$ subaward_primary_place_of_performance_city_name            <chr> "SHENANDOAH…
$ subaward_primary_place_of_performance_state_code           <list> "WV", "WV"…
$ subaward_primary_place_of_performance_state_name           <chr> "WEST VIRGI…
$ subaward_primary_place_of_performance_address_zip_code     <dbl> 254424620, …
$ subaward_place_of_performance_cd_original                  <chr> "WV-02", "W…
$ subaward_place_of_performance_cd_current                   <chr> "WV-02", "W…
$ subaward_primary_place_of_performance_country_code         <chr> "USA", "USA…
$ subaward_primary_place_of_performance_country_name         <chr> "UNITED STA…
$ subaward_description                                       <chr> "THE PURPOS…
$ subawardee_highly_compensated_officer_1_name               <chr> NA, NA, NA,…
$ subawardee_highly_compensated_officer_1_amount             <dbl> NA, NA, NA,…
$ subawardee_highly_compensated_officer_2_name               <chr> NA, NA, NA,…
$ subawardee_highly_compensated_officer_2_amount             <dbl> NA, NA, NA,…
$ subawardee_highly_compensated_officer_3_name               <chr> NA, NA, NA,…
$ subawardee_highly_compensated_officer_3_amount             <dbl> NA, NA, NA,…
$ subawardee_highly_compensated_officer_4_name               <chr> NA, NA, NA,…
$ subawardee_highly_compensated_officer_4_amount             <dbl> NA, NA, NA,…
$ subawardee_highly_compensated_officer_5_name               <chr> NA, NA, NA,…
$ subawardee_highly_compensated_officer_5_amount             <dbl> NA, NA, NA,…
$ usaspending_permalink                                      <chr> "https://ww…
$ subaward_fsrs_report_last_modified_date                    <chr> "2020-11-12…

sub_awards |>
  summarise(
    award_mean = mean(subaward_amount, na.rm = TRUE),
    award_std = sd(subaward_amount, na.rm = TRUE),
    orgs = str_c(subawardee_name, collapse = ", "),
    n = n(),
    .by = subaward_description
  ) |> 
  knitr::kable()

subaward_description	award_mean	award_std	orgs	n
THE PURPOSE OF THE IDDS ACTIVITY IS TO OPERATIONALIZE GLOBAL AND U.S. GOVERNMENT INITIATIVES AND STRATEGIES AIMED TO REDUCE GLOBAL HEALTH THREATS POSED BY INFECTIOUS DISEASES, FOCUSING ON STRENGTHENING OF DISEASE DETECTION NETWORKS AND SURVEILLANCE SYSTEMS INCLUDING ONE HEALTH NETWORKS TO ADDRESS ZOONOTIC AND EMERGING DISEASES.	821012.44	1279513.71	ANSON DANIEL, ANSON DANIEL, CEPHEID, AFRICAN SOCIETY FOR LABORATORY MEDICINE, INTEL HR CONSULTING LTD, METABIOTA, INC., METABIOTA, INC., ABT ASSOCIATES INC, ABT ASSOCIATES INC, ABT ASSOCIATES INC, GRYPHON SCIENTIFIC, LLC, GRYPHON SCIENTIFIC, LLC, FAMILY HEALTH INTERNATIONAL, FAMILY HEALTH INTERNATIONAL, FAMILY HEALTH INTERNATIONAL, FAMILY HEALTH INTERNATIONAL, FAMILY HEALTH INTERNATIONAL, FAMILY HEALTH INTERNATIONAL, FAMILY HEALTH INTERNATIONAL, FAMILY HEALTH INTERNATIONAL, SYSTEMONE LLC, SYSTEMONE LLC, SYSTEMONE LLC, SYSTEMONE LLC, PATH, PATH, PATH, PATH, PATH, PATH, PATH	31
PROCUREMENT OF MTB PLUS CHIPS TO PROVIDE STOPAGE FOR TRUEMAT IMPLEMENTATION IN CAMBODIA	31600.00	0.00	CEPHEID, MOLBIO DIAGNOSTICS PRIVATE LIMITED	2
PROVIDE TECHNICAL SUPPORT TO UGANDA BY SUPPORTING PROCUREMENT OF COMMODITIES FOR MICRO-DILUTION BROTH AND WHOLE GENOME SEQUENCING TO ADOPT NEWER MOLECULAR AND PHENOTYPIC DST METHODS.	283497.65	231998.22	CEPHEID, THULASISO (PRIVATE) LIMITED, MEDIPRO DIAGNOSTICS LIMITED, REACH ETHIOPIA	4
PROCUREMENT OF GENEXPERT MODULE DESKTOP FOR A FEASIBILITY STUDY OF THE USE OF STOOL FOR TB DIAGNOSIS.	120913.76	36393.99	CEPHEID, CEPHEID, KNCV TUBERCULOSIS FOUNDATION OF NIGERIA, MOLBIO DIAGNOSTICS PRIVATE LIMITED	4
PROVIDE TECHNICAL ASSISTANCE TO ENHANCE THE CAPACITY OF LAB STAFF, TRAINING AND MENTORSHIP OF UGANDA SUPREANATIONAL REFERENCE LABORATORY CONSULTANTS AND OTHER LAB EXPERTS.	54998.64	NA	KNCV TUBERCULOSIS FOUNDATION OF NIGERIA	1
PROVIDE SUPPORT TO USAID-INFECTIOUS DISEASE DETECTION AND SURVEILLANCE (IDDS)	35329.65	NA	KNCV TUBERCULOSIS FOUNDATION OF NIGERIA	1
PROVIDE SUPPORT FOR USAID-INFECTIOUS DISEASE DETECTION AND SURVEILLANCE (IDDS)	163803.24	NA	AIR FILTER MAINTENANCE SERVICES INTERNATIONAL (PTY) LTD	1
PROCUREMENT OF XPERT MTB ULTRA TEST CARTRIDGES FOR THE FEASIBILITY STUDY OF THE USE OF STOOL FOR TB DIAGNOSIS	95000.00	NA	MOLBIO DIAGNOSTICS PRIVATE LIMITED	1
DEVELOPMENT OF A DIAGNOSTIC NETWORK ASSESSMENT (DNA) TOOL FOR IMPLEMENTATION IN THE MENA REGION TO EVALUATE THE CAPACITY AND FUNCTIONALITY OF COUNTRIESÂ DIAGNOSTIC NETWORKS AND PREPAREDNESS TO RESPOND TO HIGH-RISK EMERGING DISEASE THREATS.	53957.25	NA	ASSOCIATION TUNISIENNE DU DEVELOPPEMENT DE LA CULTURE ELECTRONIQUE	1
THE STRATEGIC OBJECTIVE OF THE IDDS ACTIVITY IS TO STRENGTHEN THE DETECTION OF DISEASES OF PUBLIC HEALTH IMPORTANCE AND IDENTIFICATION OF AMR IN PRIORITY INFECTIOUS DISEASES, AND IMPROVE THE QUALITY OF REAL-TIME SURVEILLANCE SYSTEMS FOR EXISTING AND EMERGING PATHOGENS.
IDDS ACTIVITY WILL DIRECTLY CONTRIBUTE TO THE GOALS AND STRATEGIC OBJECTIVES OF USAID S INFECTIOUS DISEASE OFFICE IN THE GH BUREAU.	529115.56	483647.80	DELOITTE CONSEIL TUNISIE, ABT ASSOCIATES INC, GRYPHON SCIENTIFIC, LLC, FAMILY HEALTH INTERNATIONAL, FAMILY HEALTH INTERNATIONAL, PATH, PATH	7
PROCUREMENT OF TRUENAT MTB-RIF DX	306114.30	NA	TEKMAX COMPANY LIMITED	1
DEVELOP DRUG RESISTANCE SURVEY PROTOCOL AND DATA COLLLECTION TOOLS, A PILOT SENTIMENT SURVEILLANCE, A 5-YEAR TB LAB ROADMAP	73403.00	NA	OSPEDALE SAN RAFFAELE S.R.L.	1
FIELD-TB-ZBW-0 MGT/OPS	330072.16	325443.24	ABT ASSOCIATES INC, GRYPHON SCIENTIFIC, LLC	2
TRANSLATION SERVICE FOR VARIOUS LANGUAGES	100000.00	NA	TRANSPERFECT TRANSLATIONS INTERNATIONAL INC	1

Average price of the GENEXPERT MODULE DESKTOP is an order of magnitude too much. https://amsp.africa/product/genexpert-ii-2-desktop/

TRUENAT MTB-RIF DX costs about $10? https://www.nature.com/articles/s41598-023-32810-2

so 306114.30, if doubled, would be 15k units?

Summary

Little public data on hard numbers available, and possibly with errors. This report conatins all available from USAspending.gov and probably all without a FOIA or asking nicely. But worrying signs.

Excluding quarterly submission periods (so underestimated), this USAID program IDDS seems to have billed $300M to date to the Department of State.
The only information on the bills is reported here, as “subawards”.
That information is very lacking, and where things can be compared to external prices, concerning.

Tentative conclusions

Working with on the ground partners is a goood idea, but using consultancy intermediaries to accomplish that is a bad one. Partners may not know what they need, and intermediary have little incentive to procure that cheaply.
Outputs are not monitored in public rigorously enough.