IRS Form 990 Data

How to query public data sets using BigQuery

BigQuery is a fully managed data warehouse and analytics platform. Public datasets are available for you to analyze using SQL queries. You can access BigQuery public data sets using the web UI the command-line tool, or by making calls to the BigQuery REST API using a variety of client libraries such as Java, .NET, or Python.

To get started using a BigQuery public dataset, create or select a project. The first terabyte of data processed per month is free, so you can start querying public datasets without enabling billing. If you intend to go beyond the free tier, you should also enable billing.

  1. Sign in to your Google account.

    If you don't already have one, sign up for a new account.

  2. Select or create a Cloud Platform project.

    Go to the Manage resources page

  3. Enable billing for your project.

    Enable billing

  4. BigQuery is automatically enabled in new projects. To activate BigQuery in a pre-existing project, Enable the BigQuery API.

    Enable the API

Dataset overview

Form 990 is used by the United States Internal Revenue Service to gather financial information about nonprofit/exempt organizations. This BigQuery dataset can be used to perform research and analysis of organizations that have electronically filed Forms 990, 990-EZ and 990-PF.

For a complete description of data variables available in this dataset, see the IRS’s extract documentation.

You can start exploring this data in the BigQuery console:

Go to IRS 990 Dataset

Sample query

Here is a SQL query you can run on this data in BigQuery.

This sample uses BigQuery’s support for standard SQL. Use the #standardSQL tag to let BigQuery know you want to use standard SQL. For more information about the #standardSQL prefix, see Setting a query prefix.

List organizations that filed exempt status in 2015

This query combines IRS-990 filings and EIN data to list organizations that filed exempt status in 2015. For each organization, results include the number of employees, total revenue of the organization, and total compensation of all officers. Results are ordered by total revenue.

Web UI

Open the following query in the Web UI

#standardSQL
SELECT
  irsein.name AS name,
  irsein.state AS state,
  irsein.city AS city,
  irs990.totrevenue AS revenue,
  irs990.noemplyeesw3cnt AS employees,
  irs990.noindiv100kcnt AS employees_over_100k,
  irs990.compnsatncurrofcr AS officers_comp
FROM
  `bigquery-public-data.irs_990.irs_990_ein` AS irsein
JOIN
  `bigquery-public-data.irs_990.irs_990_2015` AS irs990
USING (ein)
ORDER BY
  revenue DESC

Command-line

bq query --use_legacy_sql=false '
SELECT
  irsein.name AS name,
  irsein.state AS state,
  irsein.city AS city,
  irs990.totrevenue AS revenue,
  irs990.noemplyeesw3cnt AS employees,
  irs990.noindiv100kcnt AS employees_over_100k,
  irs990.compnsatncurrofcr AS officers_comp
FROM
    `bigquery-public-data.irs_990.irs_990_ein` AS irsein
JOIN
    `bigquery-public-data.irs_990.irs_990_2015` AS irs990
USING (ein)
ORDER BY
  revenue DESC'

The top ten results are shown here:

+----------------------------------------------------------------+-------+---------------+-------------+-----------+---------------------+---------------+
|                              name                              | state |     city      |   revenue   | employees | employees_over_100k | officers_comp |
+----------------------------------------------------------------+-------+---------------+-------------+-----------+---------------------+---------------+
| KAISER FOUNDATION HEALTH PLAN INC                              | OR    | PORTLAND      | 45409123226 |     22656 |                7065 |      68314072 |
| KAISER FOUNDATION HOSPITALS                                    | OR    | PORTLAND      | 20796549014 |     66570 |               25007 |             0 |
| PARTNERS HEALTHCARE SYSTEM INC                                 | MA    | BOSTON        | 11091388129 |     63919 |                9629 |      66230068 |
| UPMC                                                           | PA    | PITTSBURGH    | 10098163008 |     59159 |                4536 |      90900288 |
| UAW RETIREE MEDICAL BENEFITS TR                                | MI    | DETROIT       |  9890722789 |       112 |                  45 |       3541179 |
| THRIVENT FINANCIAL FOR LUTHERANS                               | MN    | MINNEAPOLIS   |  9475129863 |      7766 |                1723 |      19276753 |
| THRIVENT FINANCIAL FOR LUTHERANS                               | MN    | MINNEAPOLIS   |  9021585970 |      7929 |                1636 |      17474648 |
| DIGNITY HEALTH                                                 | CA    | SAN FRANCISCO |  8655129029 |     49928 |               11282 |      43758982 |
| CLEVELAND CLINIC FOUNDATION                                    | OH    | CLEVELAND     |  7523260077 |     51023 |                5193 |      52336784 |
| PRESIDENT AND FELLOWS OF HARVARD COLLEGE                       | MA    | CAMBRIDGE     |  6740015230 |     34359 |                3401 |       8478834 |
+----------------------------------------------------------------+-------+---------------+-------------+-----------+---------------------+---------------+

About the data

Dataset Source: U.S. Internal Revenue Service

Category: Finance, Regulatory

Use: This dataset is publicly available for anyone to use under the following terms provided by the Dataset Source - http://www.data.gov/privacy-policy#data_policy - and is provided "AS IS" without any warranty, express or implied, from Google. Google disclaims all liability for any damages, direct or indirect, resulting from the use of the dataset.

Update Frequency: Annual

View in BigQuery: Go to IRS 990 dataset

Send feedback about...