Automated Email Report Ingestion
Key Highlights
- Automated ingestion of advertising reports delivered through a shared Microsoft 365 mailbox.
- Used Microsoft Graph with application-level mailbox access for unattended processing.
- Supported both CSV attachments and report-download URLs contained in email messages.
- Created reusable mailbox configuration instead of separate email logic for each advertising provider.
- Parsed report metadata such as reporting date, date range, and timezone before processing data rows.
- Supported changing report schemas and optional fields.
- Preserved records containing blank optional dimensions instead of incorrectly dropping valid metrics.
- Integrated email-delivered data into the existing MongoDB, CSV-generation, and Amazon S3 reporting pipeline.
Project Background
The advertising Data Lake collected information from structured integrations such as REST APIs, BigQuery datasets, and S3 files.
However, some advertising and reporting vendors did not provide the required data through a direct API.
Instead, they generated scheduled reports and delivered them to a dedicated mailbox.
Examples included providers sending:
Scheduled Report
↓
Microsoft 365 Mailbox
↓
CSV Attachment
while others used:
Scheduled Report
↓
Microsoft 365 Mailbox
↓
Download URL
↓
Hosted CSV File
The Data Lake needed to process these sources automatically with the same reliability as normal API integrations.
Manual downloading was not suitable because reporting jobs ran daily and historical data also needed to be processed when required.
The Engineering Challenge
At first glance, retrieving a CSV from an email appears simple.
In production, however, several questions needed to be resolved before the Data Lake could process a single row.
The system had to determine:
- which email contained the required report,
- whether it belonged to the expected reporting period,
- whether the report was attached or linked,
- which attachment should be processed,
- which URL represented the actual CSV,
- whether the file was valid,
- where the data table started,
- what date the report represented,
- and how external fields mapped to internal report fields.
The mailbox therefore could not simply be treated like an API endpoint returning a predictable response.
Multiple Report Delivery Methods
One provider could deliver a CSV directly as an email attachment.
Another could send a message containing a temporary downloadable URL such as an S3-hosted report location.
Building separate ingestion systems for each method would duplicate the same mailbox logic.
For example:
Provider A
Graph Authentication
Email Search
Attachment Download
CSV Parse
Provider B
Graph Authentication
Email Search
Body Read
URL Extraction
CSV Download
CSV Parse
Most of these responsibilities were identical.
The architecture needed to reuse mailbox communication while allowing report-specific delivery behavior.
Email Selection
The reporting mailbox could contain messages for several platforms.
A job therefore could not safely use the most recent message without additional validation.
Each integration required matching rules such as:
- subject pattern,
- reporting mailbox,
- message lookback period,
- attachment requirement,
- and downloadable URL pattern.
This prevented one report job from accidentally processing a message belonging to another provider.
CSV Files Were Not Always Normal Tables
Several exports contained report metadata before the actual table.
A source file could begin with:
Date/Time Generated, 8/9/2026, 8:06 AM
Data Last Updated, 8/9/2026, 7:53 AM
Report Time Zone, America/New_York (Billing)
Date Range, 8/8/2026 – 8/8/2026
Report Fields
Day, Venue Type, Advertiser, Campaign Name, …
A parser assuming the first line contained headers would not process the file correctly.
The metadata also contained useful information that should not simply be discarded.
Empty Optional Values
Advertising reports frequently contained valid rows where some dimensions were empty.
For example:
Campaign Name = Campaign A
Zip Code = “”
Impressions = 125
Total Cost = …
The missing ZIP code did not make the impression data invalid.
If the pipeline skipped the row because ZIP code was empty, the generated report totals could become incorrect.
The ingestion process therefore needed to distinguish between:
missing optional data
and
invalid required data.
Source-Specific Date Formats
Different reports supplied dates in different forms.
Examples included:
01-Jan-26
05/05/2026 4:00:00 AM
2026-05-05 04:00:00
Relying on generic JavaScript date parsing for every source could produce inconsistent results.
Dates needed to be normalized according to the format expected from each provider.
Solution Architecture
A reusable email ingestion layer was introduced instead of implementing mailbox communication directly inside each report runner.
The overall flow became:
Scheduled Email
↓
Microsoft Graph
↓
Message Discovery
↓
Report Delivery Resolver
↙ ↘
Attachment Download URL
↘ ↙
Raw CSV
↓
Metadata Parsing
↓
Schema Validation
↓
Field Normalization
↓
MongoDB
↓
CSV Report Generation
↓
Amazon S3
This kept email-specific work at the beginning of the pipeline while preserving the existing downstream Data Lake architecture.
1. Shared Microsoft Graph Mail Reader
Microsoft Graph was used to access the shared reporting mailbox programmatically.
Application-level access was selected because the processing occurred as a scheduled backend service rather than through an interactive user session.
The reusable mail reader handled infrastructure-level responsibilities such as:
- authentication,
- mailbox connection,
- searching messages,
- reading message content,
- obtaining attachments,
- and exposing message information to report jobs.
The report-specific code did not need to implement Graph authentication independently.
2. Configuration-Driven Mail Processing
Instead of hard-coding one report into the mail reader, individual jobs supplied configuration.
Typical configuration properties included:
subject
lookbackDays
isAttachment
linkRegex
expected file format
For example, an attachment-based integration could indicate:
isAttachment = true
A URL-based integration could use:
isAttachment = false
linkRegex = provider-specific report URL
The same common mail reader could then support multiple advertising sources.
This made future email integrations primarily a configuration and parsing task rather than another Microsoft Graph implementation.
3. Message Discovery
For every scheduled job, the ingestion layer searched only within a relevant time window.
The configured subject criteria were then applied to identify candidate messages.
Conceptually:
Get recent messages
↓
Filter subject
↓
Validate expected message
↓
Resolve report delivery
This was safer than selecting the newest email in the mailbox.
It also allowed individual integrations to define different lookback periods depending on when vendors normally delivered their reports.
4. Attachment-Based Reports
When configuration indicated that the report should be attached, the message attachments were inspected.
The workflow became:
↓
Find Expected Attachment
↓
Retrieve File
↓
Validate CSV
↓
Send to Parser
The mailbox layer handled file retrieval, but validation and source-field interpretation remained part of the integration.
5. URL-Based Reports
Some scheduled emails contained an externally hosted CSV link instead of an attachment.
The message body could also contain unrelated hyperlinks, so using the first detected URL was not safe.
The job configuration therefore included a provider-specific matching pattern.
Processing followed:
Email Body
↓
Extract URLs
↓
Apply linkRegex
↓
Select Report URL
↓
Download CSV
Once downloaded, the report entered the same processing flow as an attachment.
Downstream code therefore did not care how the CSV originally arrived.
6. Metadata-Aware CSV Processing
CSV parsing was divided into two logical sections:
Metadata
+
Report Table
Instead of assuming row one represented column names, the parser located the actual report section.
Metadata such as:
Report Time Zone
Date Range
Data Last Updated
could be extracted independently.
The tabular portion was then passed to the normal CSV parser.
This design allowed the Data Lake to use information contained in the source rather than requiring every value to be supplied externally.
7. Report Date from Source Metadata
For sources that contained their own reporting period, the Data Lake could derive or validate the effective processing date from the file.
For example:
Date Range, 8/8/2026 – 8/8/2026
provided direct evidence that the data represented August 8.
This was particularly valuable for backfills.
Instead of requiring an operator to manually provide a date that might not match the file, the integration could use:
CSV File
↓
Read Date Range
↓
Determine Report Period
↓
Process Data
This reduced the chance of storing correct data under an incorrect report date.
8. Field Normalization
External header names were mapped into the Data Lake’s internal report model.
For example:
“Day” → report_date
“Campaign Name” → campaign_name
“Campaign ID” → campaign_id
“Zip Code” → zip_code
“Total Cost” → total_cost
Keeping mappings outside of the general report-processing logic made source differences easier to maintain.
A provider-specific schema change could generally be addressed in its own configuration or normalization layer without changing other integrations.
9. Optional Column Handling
The parser did not require every historical and daily file to have an identical set of columns.
Fields were classified according to whether they were essential to report generation.
For an optional value:
row[“Zip Code”] = “”
the system preserved the row and stored an empty value.
The row was not discarded when other metrics remained valid.
This was important because deleting a row due to one missing dimension could also remove:
- impressions,
- revenue,
- cost,
- conversions,
- or other valid measures.
10. ZIP-Code Handling
Postal codes required special attention because they are identifiers rather than numeric measurements.
For example:
01234
must conceptually remain different from:
1234
The normalization layer therefore treated ZIP codes as string data.
One operational detail was that applications such as Excel or Google Sheets could still interpret unquoted CSV values numerically and hide the leading zero even when the raw CSV contained it correctly.
This distinction was documented because it was a presentation issue rather than necessarily a Data Lake corruption issue.
11. Source-Specific Date Normalization
Date parsing was handled before data reached the common report layer.
Each integration could recognize its own expected input format and convert the result into a consistent internal timestamp.
This prevented downstream code from needing logic such as:
if Vistar date…
else if Art19 date…
else if another provider date…
Source-specific formatting remained inside the ingestion adapter.
12. MongoDB Batch Processing
After normalization, records were written to MongoDB using batch operations.
The reporting platform used manageable chunks for high-volume integrations instead of performing one insert for every row.
The processing pattern was:
Parse CSV Rows
↓
Normalize Records
↓
Create Batch
↓
MongoDB Bulk Write
↓
Continue
This reduced database round trips and supported larger scheduled reports without changing the basic ingestion architecture.
13. Integration with the Existing Report Runner
A major design goal was preventing email-based sources from becoming a separate reporting subsystem.
After the CSV had been retrieved and normalized, it entered the same Data Lake lifecycle used for other source types.
Normalized Records
↓
Common Report Runner
↓
MongoDB Report Entries
↓
CSV Generation
↓
S3 Upload
This allowed existing behavior for reporting, job tracking, batch processing, and failure handling to be reused.
Technical Implementation
Technology Stack
The email pipeline used:
- Node.js
- TypeScript
- Microsoft Graph API
- Microsoft 365 mailbox
- CSV parsing
- HTTP file retrieval
- MongoDB
- AWS S3
- Scheduled background jobs
- CLI-based historical processing
Reusable Mail Configuration
The common configuration approach supported future integrations without changing Graph infrastructure.
Each job primarily needed to provide:
- how its message should be identified,
- how its file was delivered,
- how the CSV should be interpreted,
- and how source fields mapped to report fields.
This established a repeatable pattern for future providers using the same reporting mailbox.
Results
Automated Previously Manual Sources
Scheduled email reports could be ingested without manually opening messages or downloading files.
Supported Multiple Delivery Methods
Attachment-based and URL-based reports were handled through one ingestion framework.
Reduced Duplicate Integration Code
Microsoft Graph authentication and mailbox logic were implemented once and reused by multiple jobs.
Improved Data Validation
Source metadata could be checked before rows were persisted.
Better Historical Processing
CSV date ranges could be used during backfills instead of depending entirely on operator-provided dates.
Preserved Valid Advertising Data
Rows with missing optional dimensions were retained, protecting report metrics from being lost unnecessarily.
Improved Extensibility
New email-delivered integrations could reuse the same mailbox and reporting infrastructure while supplying their own configuration and source mappings.
Key Takeaways
- Mailbox access should be implemented as shared infrastructure rather than repeated in individual ETL jobs.
- Email attachment and download-link delivery can converge into one common raw-file processing interface.
- Report metadata is useful operational information and should be parsed rather than discarded.
- Optional blank dimensions should not cause otherwise valid advertising records to disappear.
- Source-specific dates and field formats should be normalized before entering common Data Lake processing.
- Once ingestion is complete, email-derived records should follow the same reporting lifecycle as API, BigQuery, and S3 sources.
