#1 Gaining an Analytics Edge Using Federal Spending Open Data

Blog #1 Getting Started…

Premise of this Blog Series: Publicly available “open data” sources and open-source technology analytics can provide information to reduce uncertainty when making critical business investment decisions.

Purpose: Demonstrate to executives how easy it is to access and analyze free, open-data sources and motivate them to expect more insight and context from their team when making crucial go/no-go investment decisions on bids and new offerings.

Scope: US Federal Contracting Market.


Deciding whether to commit key talent, time, and money to a new business opportunity is one of the most energizing and challenging decisions that any general manager (profit center manager) must make.

  • Is the opportunity consistent with our business strategy?
  • Do we have the operational capability to capture and deliver the work without jeopardizing our other priorities?
  • Can we build a product or service that customers will select over other options they have to address their needs? What are those options?
  • What are the opportunity costs of redirecting our “A-team”?
  • Do we have an ‘edge’ vs our competitors in the eyes of the prospective customer(s)? Do we know their source selection criteria and how they have decided previous purchases?

Do We Know What We Don’t Know

Executives want to gain an edge vs. their competitors, but how?

To deal with the uncertainties in pursuing various types of business growth initiatives, many large companies use standard processes and templates to guide the information gathering and analytics of growth investments. It is common to find sales qualification criteria for deals and stage-gating mechanism for product/service development. The intent is to maximize the skills of the team and minimize the effects of bad luck. The objective is to reduce as much uncertainty as possible before committing more resources.

At every step along the way, the executive in charge must ask whether the team has gathered enough information to make a reliable return on investment bet within an acceptable range of uncertainty.

After an initial decision to proceed with qualifying and characterizing the opportunity, deciding whether to continue to invest can be harder than the initial green light to begin. The team is now emotionally invested; suppliers engaged; potential solutions and prototypes developed; prospective customer visits are in process; other earlier opportunities have passed, and so on. Costs and stress grow as your team continues along the path of a new business opportunity toward a sale, acquisition, or launch.

It is easy for judgment to get clouded with the enthusiasm of the team, so rational and disciplined go/no-go processes and decision criteria are key.

Open Data Options

If you are serving the Federal Government market, one of the first steps should include using free, open data sources to provide more context in crafting the initial strategy for the market and opportunity, understanding the prospective customer’s preferences, assessing competitor strengths, and confirming the scale of the opportunity.

In my work, advising clients on business strategy and emerging technology, I frequently look for open data sources to provide context on the current state and assess the addressable market’s size and nature. For my clients that serve government, there are a growing number of freely available, government-run open data sources that provide timely and valuable information to help frame analysis. Free US Census Bureau and local government open data sources can be valuable for commercial market assessments.

If you are competing in the Federal Government contracting market, you have several open data choices. My favorite starting point is to use USAspending.gov to assemble context.

Starting with an Example…

I will walk you through an example that demonstrates how easy it is to access this valuable information and extract insights that will help you make better-informed business investment decisions. In subsequent blog posts, I will show other analyses using this same data, automated techniques to streamline the process using open-source tools such as the free Python technology ecosystem, and ways to combine the USAspending.gov data with additional data sources to provide an even richer view of the market.


Your company has technologies and experience helping insurance companies detect medical fraud claims. One of your best salespeople just had lunch with a former client who says that she heard that the Center for Medicare and Medicaid Services (CMS) has been getting lots of extra scrutiny from their Congressional oversight committees about fraudulent claims activity reports.

Also, the GAO and HHS IG have published publicly-available reports about it and proposed new legislation will increase requirements and funding to prevent and detect fraud. Your sales leader recommends authorizing spending some money to explore the opportunity further. Your firm has never done business with the broader Health and Human Services Department (HHS) nor CMS.

Having read an article about open data sources that are available on Federal spending, what expectations should you have for your sales leader to use free information sources to understand the current market before committing too many more resources?

The first step in building a macro-level context is identifying and reading GAO and HHS IG reports along with Congressional Hearing testimony on the subject. Here are some examples:

Now that you have a sense of the ‘big picture’, the next critical step is to assess the current market and spending on these types of solutions and services.

How do you do that?

(1) Visit https://www.usaspending.gov/#/

(2) Visit https://www.usaspending.gov/#/search (Main/Award Search/Advanced Search)

(3) Select the last complete Government Fiscal Year (10/1–9/30) — “FY 2019”

(4) Select Agency/Funding Agency and enter “CMS” (Centers for Medicare and Medicaid Services will pop up and choose that)

(5) Select the “Submit Search” button (located on the left top and left bottom of the options selections pane)

(6) Select the Contracts tab and Prime Awards (those are the defaults)

(7) Select the DOWNLOAD button in the upper right corner. A pop-up Download Data window opens. Double-check the “Active Filters” represent what you want to download — FY19 and CMS. Choose ‘Transaction’ in the pop-up menu to get detailed obligation transactions. The choose “Everything”.

(8) You should now see a “We’re preparing your download” window.

(9) After a few minutes, your browser may ask you if you want to save the file. Select that or look in your Downloads folder to find the file(s).

(10) Look in your downloads folder for a file named something like “PrimeTransactionsAndSubawards_2020–09–02_H14M17S13451239.zip”

(11) Unzip the file (on a Mac, double click the file), and a folder will appear.

(12) Open the folder and look for a file that begins with “Contracts_PrimeTransactions_” and ends with “.csv” and open it in Excel or Google Sheets. You may want to move or save the file in a folder you create for this analysis in your Documents folder.

(13) Put the spreadsheet in Data Filter Views mode (for Google Sheets, for Excel) so the columns have a pulldown menu to select subsets of the data or sort the column. Here is an example of GFY19 download from the step above — Google Sheets Example here

(14) Using the Google Sheets or Excel “Find” (Command-F on a Mac), you can enter the word “fraud” and see what appears. The FY19 file shows 30 references to that term.

The term appears in the award_description and major_program fields of the spreadsheet. That gives us a good start on records and contracts to review.

An additional approach is to do a Google search on “medicare fraud contract award”. Some links from press coverage and press releases:

Having those contractor names enables you to look at prime obligations they received that may NOT have the word fraud in the award_description field or major_program field but are the contracts where this or related work is performed. That work might be with other Agencies in need of similar expertise.

(15) Using the Google Sheets Filter and selection feature on the column major_program (column FA) and entering the word fraud; this reveals distinct programs with “fraud” in their title. They are:


It may take some work to look for synonyms and or related terms that are specific to the function or domain (e.g., ‘program integrity’). You will want to do similar searches for those terms too.

The recipient_name (column AU) and recipient_parent_name (AY) fields tell you who the prime contractors are. The field type_of_contract shows that some are “FIRM FIXED PRICE” and others are “COST PLUS AWARD FEE” and “TIME AND MATERIALS”.

The product_or_service_code and product_or_service_code_description fields explain what the government considers to be the primary type of work (see this guide for how the Federal Government designates the major type of work — https://www.acquisition.gov/psc-manual, https://www.acquisition.gov/content/psccategoryalignmentapril2020xlsx

If the product_or_service_code starts with a ‘D’ it represents IT and Telecommunications Services and ‘R’ for Professional Services. If it begins with a ‘70’ it means a technology buy (likely software or hardware). Any of the obligation records may have a combination of these services, but this designator will convey the prime contractor’s dominant specialty for this work.

The field commercial_item_acquisition_procedures indicates that some of these were considered “COMMERCIAL ITEM” during procurement. That is important to our fictional company since it mainly does work with commercial insurance companies. It would want its solution considered as a commercial product during the selection process. Also, the field cost_accounting_standards_clause indicates whether the company has to have a CAS-compliant accounting system to bid on the program (something that would be true for a COST PLUS contract).

The fed_biz_opps field will indicate whether the opportunity was advertised in the in this system — https://beta.sam.gov/ prior to award. If so, you will be able to search for the original award and RFI/RFP information in the archives (formerly known as ‘Fed Biz Opps’).

The field parent_award_type will indicate whether the item was purchased off a Federal Supply Schedule (FSS) contract or a GWAC or IDC. The parent_award_single_or_multiple field is useful also to gauge the competitive challenges after award.

The award_type might indicate DELIVERY ORDER or DEFINITIVE CONTRACT, for instance.

The type_of_set_aside field is critical to view since if it is for a set_aside, your firm may not be eligible to bid a prime and instead need to subcontract with a firm that meets the criteria.

The number_of_offers_received field is useful since it can indicate the competitive intensity for the deal on the previous competition. Something you are likely to see in a future bid.

For a specific line item, you can look at the field usaspending_permalink and further inspect the details of the contract or delivery order. Here is an example https://www.usaspending.gov/#/award/CONT_AWD_HHSM500201200009G_7530_GS15F0059M_4730/

Another example: https://www.usaspending.gov/#/award/CONT_AWD_HHSM500201400196C_7530_-NONE-_-NONE-/

The award_id_piid, parent_award_id_piid, and parent_award_agency_name fields are useful for researching the start and expiration of contracts. For instance a search on www.fpds.gov (this will migrate to beta.sam.gov in mid-October 2020) for award_id_piid = “HHSM500201200009G” yields this page.

The following fields are extremely valuable to assess the market’s potential size and the relative size of the incumbents:

  • *** federal_action_obligation (these are the actual dollars that were obligated to the contractor under the contract on the action_date)
  • total_dollars_obligated (cumulative obligations)
  • base_and_exercised_options_value
  • current_total_value_of_award
  • base_and_all_options_value
  • potential_total_value_of_award
  • action_date
  • action_date_fiscal_year
  • period_of_performance_start_date
  • period_of_performance_current_end_date
  • period_of_performance_potential_end_date

If we use the Filter feature on the award_description field and enter the term “fraud” we see a much bigger list of obligations to primes under program names such as “DARK WEB MONITORING PILOT”, “UNIFIED PROGRAM INTEGRITY CONTRACT (UPIC)”, and “MEDICARE DRUG INTEGRITY CONTRACTORS (MEDIC) — MULTIPLE AWARD IDIQ”.

Another approach is to use the Google Search of “medicare fraud contract award” and search the recipient_name and recipient_parent_name fields to see the scope and extent of work each of those firms is doing at CMS. You can expand that to search for all of the work that the contractor is doing across HHS and the entire Federal market for publicly reported contracts using the USAspending.gov Advanced Search feature we used to start this analysis process.

Pivot Table Views — Start Analytics Process…

One of the best ways to look at this data is by using the Excel and Google Sheets Pivot Table feature. Many people are intimidated by this tool, but it is one of the most powerful features of modern spreadsheets. With just a few clicks, one can organize the data in ways that make it easy to see the big picture and also the finer details.

I’ve added a rudimentary Pivot Table tab to this spreadsheet here.

Next Blog Post

The manual process described above works fine for quick analysis like this to start assessing a narrow portion of the Federal market. As your analytical appetite grows, there are much easier automated ways to work with the data and perform research, such as a market-share analysis and trending across GFY. Often looking at multi-year trends is key to building analytics that increases your edge. A full Government Fiscal year (GFY) of USAspending.gov is about 10 GB which is much more than a spreadsheet can handle so other tools are needed that I will demonstrate in future posts.

In my next blog post, I will show you how to automate this process and analyze it using Python and Google Sheets. Link to the next blog in this series.

About the author:

Leif Ulstrup @lulstrup is the founder of Primehook Technology and an experienced general manager with a proven track record, adapting emerging technology and management innovations to achieve significant business and mission results. His experience spans from the management of large enterprise profit centers to consulting to senior executives faced with critical business and mission transformation challenges.

Strategy, emerging technology, innovation, and management advisor https://www.primehookllc.com/about-us.html, https://www.american.edu/kogod/faculty/ulstrup.cfm

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store