Database Construction

How the Email Database Was Built

Creating the email database [hyperlink to Calendar Tool] from the original public email corpus has involved intensive work. The 445,298 pages of emails were originally released in 97 PDFs in 2016 by Michigan on a state government website. These PDFs ranged from 1 page to 20,241 pages. The PDFs were named for the department providing the emails— for example, Department of Environmental Quality, Treasury, etc— and thus provided a rough division by content. Occasionally, the PDF sequence of emails was roughly chronological, but overall, they were not organized by sender, content, or time period. Some of these PDFs were not text searchable, and a few of them were scanned paper documents. This made them difficult to manage, to view as individual emails or threads, to search, or view chronologically.  

The data has required many stages of data extraction and cleaning, including data conversion to plaintext; metadata extraction; separation of text into emails, threads and attachments; and de-duplication. We briefly summarize these processes here, both as a record of the steps taken in producing this database, and in the hope that our process may assist other researchers or communities interested in similar digital preservation or archival creation efforts. Our Python code and documentation is available on Github. 

Processing to date for the email corpus has included the rendering of multiple file types for each page, linked with persistent digital identifiers, and metadata extraction through regular expressions and other data mining techniques. Metadata for each page in the corpus is stored in a JSON file. To render the corpus searchable and create an organized database by date, sender, etc., the dataset was split into individual PDF pages. Each single-page PDF was renamed with object identifiers for the original PDF document, pagination, and bookmark level (a rough proxy separating email thread or attachment). Each page was rendered as a .JPG file, processed through Optical Character Recognition (OCR) after binary erosion to improve OCR quality, and converted into .TXT files. 

Textfiles permitted reconstruction and extraction of email metadata (sender(s), direct and cc receiver(s), subject, date/time, and attachments) through an iterative process of writing regular expressions in Python to match each of several email client presentations. Email dates and times were standardized as UNIX timestamps. Each email was assigned a unique ID and the resulting dataset was built as a JSON file. 

Duplicates were identified by grouping emails with the same sender and with timestamps within 100 seconds of one another (to account for differences in time reporting styles across email clients). For emails appearing multiple times, “canonical” duplicates were selected based on a simple algorithm that prioritized iterations with better quality metadata and text rendering. This processing step allows users to choose to limit their email searches to unique emails if they wish. (The option to see every email iteration can also be helpful to observe how the same email may appear in different email threads with different interlocutors.) A separate de-duplication process, similar to the process for duplicate emails, identified duplicates at the level of the “bookmark” (in most cases, a proxy for an email thread and attachments, as we explain below). We also conducted a spell check process using both a general and a project-specific dictionary we generated to count the average number of errors per page in each PDF, to identify potential OCR problems (often rotated and/or scanned pages).

Early evaluation of the large PDF files showed the large PDFs were subdivided into PDF “bookmarks” that roughly proxied division by email chain and/or attachment (with a few exceptions, in which large bookmarks appear to contain a full FOIA response with multiple emails). That is, one “bookmark” could include both an email thread and its attachments as subsequent pages, or an email thread and its relevant attachment could be split into distinct, consecutive bookmarks. Our identifying system thus categorizes pages by their parent PDF and their bookmark number, which can be mapped in the URLs for each PDF returned in our database. 

This bookmark division allows us to subdivide the corpus into bookmarks with and without email metadata. Until now, we have primarily focused on bookmarks containing email metadata to reconstruct the communication network. In future stages, we plan to facilitate additional ways to search the email attachments included in the PDFs that are not already included as part of the “bookmark” of a parent email. We also plan to add documents occasionally nested within a hyperlinked structure in “born-digital” PDFs, another layer of attachments (e.g., Word documents) that are not represented within the total number of PDF pages of the corpus.   

This process of metadata extraction has been performed on a third of the total email corpus (150,507 text file pages of 445,298 total pages, or 12,258 bookmarks out of 26,623 total bookmarks), by focusing on bookmarks containing metadata within the PDFs deemed to be highest priority based on content. This includes PDFs from the following departments:

• Department of Environmental Quality (DEQ), 26 files 
• Department of Treasury, 2 files
• Executive Office, 3 files 
• Michigan Department of Health and Human Services (DHHS), 4 files

Using our text extraction method for this data subset provides estimates of around 40,615 emails total, or 23,643 unique emails (with duplicates removed) sent and received by at least 1200 participants in the communication network. This leaves 104,829 pages of “non-email” documents in the dataset subsample we have focused on to date. 

These pages are primarily attachments belonging to emails in the database, but which have been split into a separate “bookmark” structure. We have conducted preliminary assessments of the non-email bookmarks in this subset, and evaluated ideal methods to pair as many attachment bookmarks as possible to their accompanying email. We are in preliminary phases of designing a database structure that would organize and allow searches within these non-email documents.

A later stage of data cleaning, still ongoing as of 2026, involved a second round of OCR processing (reflecting advancements in the technology since 2018) and the creation of an internal interface for identifying and correcting OCR errors in email metadata, completing metadata from incomplete records, and vetting new OCR suggestions. In an earlier stage, team members identified errors in sender and receiver names and matched names of over 1000 participants to job titles and workplaces, facilitating search functions and the cleaning process. 

Why is this processing necessary? What about other email archives?

The most well-known other email preservation project is the Enron email corpus. More recent projects have included the Stanford epadd project and the Email Processing Capstone project with the Illinois State Archives. Both are excellent projects and offer valuable insights. The crucial difference for our project is the input format of the emails. The emails in our corpus were released as PDF pages (many printed out and re-scanned), not as direct outputs from email clients (i.e., in .pst or .eml format). Flat PDF pages require complex processing, including image enhancement, recognition of text (OCR), metadata extraction, duplicate suppression, and extensive data cleaning. In contrast, emails directly outputted from an email client come already sorted into header metadata categories and separated into discrete email bodies and attachments. To our knowledge, no other project has established public-facing workflows for converting PDF email outputs into a searchable digital archive. As FOIA responses are frequently returned in PDF format (in part to aid with redactions), our methods should assist many other projects faced with similar data problems.

Computer scientists often rely on relatively clean, pre-processed datasets for their work— or else assume that their dataset is clean. For instance, the Enron corpus was instrumental to the development of multiple language models and tools for processing email, and is still widely used as a model communication network by researchers. This corpus (which, again, was directly extracted from email clients) was preprocessed by the private firm hired by government investigators, and further cleaned by multiple teams working in collaboration. In contrast, projects working with found archives like this one have little to no control over the initial presentation of data. As long as this is the case, we will benefit from developing and retaining knowledge of the steps for basic data cleaning. 

The “e-discovery” industry does provide some of the bulk-PDF processing and data extraction work that we have done independently. We decided that creating our own digital infrastructure will better ensure long-term access and data preservation for the following reasons:

First, our goals entail higher data cleaning, transparency and accuracy standards. Our work to date has allowed us to produce internal error estimates and revise until we reach our own quality standards. In contrast, E-discovery aims to quickly pull out and organize key topics and findings from the data for the purpose of evidence gathering. The quality claims made by private vendors offering these services are not auditable and may not be meeting their own stated claims. Moreover, our need to reassemble email metadata structure is distinct from the orientation of E-discovery services to specific search terms. The latter would be satisfied by knowing whether a certain name appears on a given page, which is a much lower bar than needing to know the sender of a given email.

Second, our aim is to create a complete record of the email corpus (minus duplicated records). E-discovery’s aims differ in that the process is designed around iterative sampling and related processes to return sufficient relevant evidence to build a legal case. In fact, firms do not necessarily want to generate a fully accessible corpus, as it could give unwanted advantage to the opposing team during discovery. 

Third, we are building a front-end application whose goals are attuned primarily toward community users, whereas E-discovery products tend to require specialized knowledge from a small group of people trained in the specific interface. The proprietary nature of these tools would give us relatively little ability to edit or design the end product or user interface.

Most importantly, creating our own digital infrastructure will better ensure long-term access and data preservation, and prevent us from relying on product subscriptions in an extremely dynamic environment of mergers and acquisitions. We have no guarantee that existing E-discovery services will still be available over the long term. We have much greater control over the end product’s appropriateness, usability and longevity by creating the tools ourselves, based on packages we have already developed.

We hope the processes we have developed can help other researchers contemplating work with other large PDF archives, especially archives of emails, which are fairly common in FOIA responses seeking communications. Much of the current discussion and knowledge production around FOIA and email develops best practices for an organization voluntarily seeking to improve document accessibility, and selecting an ideal storage format for delivery of the FOIA response. We encourage such discussions and hope that best practices around formats for FOIA delivery become widespread or are further regulated. However, such discussions generally assume goodwill on the part of the organizations receiving FOIA requests. And yet the nature of FOIA requests often is to seek greater transparency than a public organization had wished to provide (hence the need for further data requests). Rules for public records requests also vary by state (with Michigan scoring especially low for data transparency). To that end, organizations may be incentivized to provide FOIA responses that are still only minimally accessible or useful, or in the “easiest” format to produce. As major political events occur, it is increasingly likely that relevant archival material will be “born digital” (i.e. produced through digital means, and often existing only in digital space). We need processes for capturing, storing and processing these materials in their actual format, not in their ideal format.