Building a Content Database from Email Archives
Raw email data contains inconsistencies (mixed-case addresses, subdomain variations, gateway artifacts) that must be standardized before analysis.
Why Raw Archives Mislead
An email archive records messages as they arrive: full headers, complete bodies, and addresses in the formats used by senders. That fidelity is useful for storage, but it creates problems for analysis. The same sender may appear with mixed capitalization, while related addresses may use different subdomains or gateway-generated forms. If these representations are counted separately, an analysis can overcount senders, split related organizations, and misrepresent communication patterns.
| Raw representation | Analysis problem | Effect of normalization |
|---|---|---|
| Mixed-case address forms | One sender can be counted as multiple senders | Address representation is standardized |
| Subdomain variations | Related institutional addresses can appear as separate domains | Domain is reduced to an analysis-relevant level |
| Gateway artifacts | A sender identity can be fragmented across address forms | Matching gmane addresses can be replaced |
The Database Transformation
gmodel.py treats the archive as a data pipeline rather than as a collection that can be analyzed immediately. It reads raw email from content.sqlite, applies cleaning and normalization rules, and writes a new result to index.sqlite. The output is both normalized and compressed. The compression is reported as roughly tenfold, but the more important benefit is analytical accuracy: equivalent sender and domain representations are brought into a more consistent form before analysis.
Tracing One Archive Record
Follow a message record through the gmodel.py pipeline.
Read: The message is read from the raw content.sqlite database in the form in which it was collected.
Clean: The program applies address normalization, including lowercase conversion and any applicable gmane replacement.
Normalize: The domain is handled according to its top-level domain and any active mapping rules.
Write: The transformed record is written into the rebuilt index.sqlite database.
The record moves from bulky, inconsistent raw storage to a normalized and compressed analysis database.
Address Normalization Rules
The first address transformation is lowercase conversion. This makes differently capitalized forms comparable. For example, John.Doe@EXAMPLE.COM and john.doe@example.com represent the same normalized spelling after lowercase conversion. The local part is not replaced merely because it contains punctuation or a name; the stated normalization begins with standardizing case.
gmane replacement is conditional, not automatic. A gmane address is replaced only when a matching real address exists. Without that match, the rule does not provide a basis for inventing a replacement identity.
Applying Address Rules
Determine the normalization steps for two generated raw address records.
Mixed case: For John.Doe@EXAMPLE.COM, convert the address to lowercase, producing john.doe@example.com.
Gateway artifact with a match: For a gmane address, apply replacement only if the archive contains a matching real address.
Gateway artifact without a match: If no matching real address exists, do not claim that a replacement has been identified.
Lowercase conversion is general; gmane replacement depends on a matching real address.
Domain Truncation Rules
Domain normalization is asymmetric because different top-level domains use different truncation depths. Common TLDs such as .com, .org, .edu, and .net are truncated to two levels. Other TLDs are truncated to three levels so that institutional identity is preserved. The purpose is analytical: for a university address, the institution may matter more than the specific subdepartment or mail server.
Choosing the Truncation Depth
Apply the domain rules to two generated domain forms.
Identify the TLD: First determine whether the domain ends in one of the common TLDs .com, .org, .edu, or .net.
Use two levels for common TLDs: A domain ending in .edu, such as si.umich.edu, is reduced to two levels: umich.edu.
Use three levels otherwise: For a domain with another TLD, retain three levels rather than applying the two-level rule.
The truncation depth depends on the TLD: two levels for the listed common TLDs and three levels for others.
Operational Feedback
gmodel.py provides progress information while it works. It reports the number of unique senders loaded, the number of active mapping rules, and a progress line every 250 messages. In the documented run, it loaded 1588 unique sender addresses and 28 mapping rules. The progress output also showed normalized lowercase addresses and normalized domains, allowing you to check both that processing is continuing and that the transformations appear to be active.
Use the progress output as a verification tool, not merely as a status display. A large archive can take significant time to process, especially when the mail data is nearly a gigabyte. Seeing lines at regular message intervals helps distinguish active processing from a program that has stopped responding.
Analyzing raw addresses without lowercase normalization.
The same person can be counted more than once.
Fix:
Apply lowercase conversion before comparing address identities.Applying the two-level domain rule to every TLD.
The rules are asymmetric, and other TLDs use three levels to preserve institutional identity.
Fix:
Identify the TLD before selecting two-level or three-level truncation.Replacing every gmane address automatically.
Replacement requires a matching real address.
Fix:
Use gmane replacement only when the required match is present.Assuming the existing output database is incrementally preserved.
Each run deletes and rebuilds the output file.
Fix:
Treat each run as a fresh rebuild using the current parameters and mapping tables.
Apply the Rules
For each case, predict the transformation before checking the rules: a mixed-case address, the source domain si.umich.edu, a domain with a TLD other than .com, .org, .edu, or .net, and a gmane address with no matching real address. State whether lowercase conversion, two-level truncation, three-level truncation, or gmane replacement applies.
Hints
- Lowercase conversion is part of email normalization.
- The .edu example si.umich.edu becomes umich.edu.
- Common listed TLDs use two levels; other TLDs use three.
- Gmane replacement requires a matching real address.
What do you think happens?
A message has the address John.Doe@EXAMPLE.COM. What normalized address should you expect after lowercase conversion?
Reveal answer
Answer: john.doe@example.com
The stated address normalization includes lowercase conversion. The local part remains john.doe, and the domain remains example.com.
What do you think happens?
A domain is si.umich.edu. Which normalized domain follows the documented common-TLD rule?
Reveal answer
Answer: umich.edu
The .edu TLD is in the common-TLD group, which is truncated to two levels. The source uses si.umich.edu and umich.edu to illustrate this institutional normalization.
Pipeline Summary
- Raw email archives preserve bulky and inconsistent source representations, so they should not be treated as analysis-ready.
- gmodel.py reads content.sqlite, applies address and domain rules, and writes a normalized, compressed result to index.sqlite.
- Email normalization includes lowercase conversion and conditional replacement of gmane addresses when a matching real address exists.
- Domains with .com, .org, .edu, or .net are truncated to two levels; other TLDs are truncated to three levels.
- The output is rebuilt on each run, while progress and mapping information help verify and refine the transformation.
Key Takeaways
- Cleaning is necessary because raw email archives contain mixed-case addresses, subdomain variations, and gateway artifacts.
- gmodel.py transforms raw content.sqlite data into normalized records in index.sqlite.
- Lowercase conversion, conditional gmane replacement, and TLD-dependent domain truncation support consistent identity analysis.
- The pipeline improves analytical validity, while its compression is a secondary benefit.
- Progress output and rebuildable results make it possible to inspect and refine the cleaning process.