Transform DMARC XML to Time Series with SQL

Why DMARC XML Reports Are Underused in Email Deliverability Monitoring

You’re checking deliverability by tracking bounces and open rates—but what if your most detailed data source is sitting unused in XML files?

DMARC reports aren’t just logs of failed authentication. They’re a continuous stream of real-time insights about spoofing attempts, sender policy mismatches, and domain reputation shifts across your email ecosystem. Yet most teams treat them as archival clutter, not as raw material for proactive monitoring.

With SQL, you can extract the signals buried in DMARC XML—normalize inconsistent fields, timestamp each report, and aggregate counts over time. The result? A time-series dataset that turns static reports into actionable dashboards. This is how you shift from reactive alerts to predictive visibility.

Key takeaways

DMARC XML reports contain structured, time-stamped data on authentication failures and spoofing attempts that can be transformed into time-series metrics.
Using SQL to parse, normalize, and aggregate DMARC data enables trend analysis and early warning detection across multiple domains and senders.
Raw DMARC reports are underused because teams lack the tools to turn them into actionable, visual dashboards without custom scripting.

What You Get When You Parse DMARC XML with SQL

You turn raw, daily DMARC XML reports—sent by Gmail, Yahoo, and Microsoft—into actionable time-series data. With SQL, you extract pass/fail rates by SPF, DKIM, and authentication status, track sudden spoofing spikes, and identify anomalies in sender IPs. This lets you see deliverability trends, detect unauthorized senders, and prove your email security posture over time.

What’s Inside a DMARC Report

Each day, major email providers send you an XML file summarizing how your domains performed. These reports include the source IP address of every message, whether SPF passed, if DKIM was valid, and if the messages were authenticated. You’ll also see the volume of failures and which domains or subdomains were targeted. If you’re not parsing these files, you’re missing the full picture of who’s sending as your brand.

DMARC is enforced by receivers, and while most reports are aggregated, you still gain visibility into the types of messages being blocked, especially spoofing attempts. The format is standardized, defined in RFC 7489, which ensures consistency. But raw XML isn’t queryable—it’s a document, not data. That’s where SQL comes in.

How SQL Turns Reports Into Time-Series Insights

Once you’ve loaded the XML into a database—say, PostgreSQL or BigQuery—you can use SQL to extract and normalize the data. You can group it by date, domain, IP address, or authentication method. From there, you build rolling daily statistics: the percentage of messages with valid SPF, DKIM, or both. A sharp decline means trouble—maybe your SPF record changed or a third-party sender went rogue.

Let’s say you spot a sudden spike in DKIM failures from a specific IP. SQL lets you correlate that with a change in your sending infrastructure. Or, if you see unauthorized domains sending emails that claim to be from you, you can identify spoofing attempts before attackers exploit them. These insights feed directly into your deliverability dashboard, not just as metrics, but as triggers for action.

Most security teams process DMARC data manually or with clunky tools. But with SQL, you automate trend detection, track compliance over time, and set up alerts. The result isn’t just data—it’s a defense system.

For teams managing large volumes of email, turning DMARC into time-series data is a standard practice. You can validate your reports with tools like MXToolbox, but you need a way to track trends. That’s where parsing with SQL adds real value beyond static reports.

How to Extract and Normalize DMARC XML into a SQL-Friendly Format

You can transform DMARC XML reports into time series data for email deliverability dashboards by parsing the XML content using a scripting language like Python, converting it to structured JSON or CSV, then loading it into a SQL database—such as PostgreSQL or BigQuery—with standardized fields like date, source IP, policy, and alignment. This enables accurate, aggregated analysis over time. The key is proper normalization to ensure IP addresses and domains are cleaned and consistently formatted.

Parse DMARC XML using a lightweight script — Use Python, Node.js, or an ETL tool like Airbyte to read raw DMARC XML files. These reports come from email receivers and follow the DMARC specification (RFC 7208). Parsing ensures you extract per-aggregate data, including how many messages were sent, aligned, and rejected per source IP and domain.
Convert to structured JSON or CSV — Normalize each report's record section into a flat, consistent structure. Extract fields like org_name, date, source_ip, policy, disposition, and alignment. Use a schema that matches the eventual SQL table design, avoiding nested arrays or unstructured fields.
Normalize IP and domain fields — Before loading, standardize source IP addresses into netblocks (e.g., 192.168.1.0/24) and domain names to lowercase, removing subdomains where appropriate. This ensures accurate grouping across reports and prevents false separation in time-series aggregations.
Load into a SQL-friendly database — Use tools like psycopg2 (for PostgreSQL), bq load (for BigQuery), or sqlite3 (for local testing) to insert records. Ensure the table schema includes indexed columns for date, source_ip, and domain—critical for fast filtering and charting.
Validate and clean during ingestion — Add a step to filter invalid or malformed records. For example, reject reports with incomplete or malformed timestamps. Use your email deliverability tool’s verification capabilities to cross-check domains against recent delivery health, such as with MailTester’s real-time email checker for suspected bounces or dead addresses.

Why Normalization Matters for Accurate Insights

Without consistent IP and domain formatting, reports may count the same sender as multiple sources or misrepresent policy compliance. For instance, mail.example.com and mail.EXAMPLE.COM should map to one domain. Misaligned data leads to incorrect conclusions—like falsely flagging a large portion of your email traffic as failed alignment.

Once ingested, you can query across daily aggregates: “How many messages failed alignment by source IP on June 10?” or “Which domains show a spike in rejection rates after policy change?” These queries power visual dashboards in tools like Metabase or Looker, showing trends that directly tie to sender reputation and deliverability health.

Use Case: Correlating DMARC with Delivery Failures

When you see a rise in disposition=reject or alignment=fail events, cross-reference the source_ip with your own sending infrastructure. If an outbound IP appears in multiple failed reports, it may indicate misconfigured authentication or compromised systems. Use MailTester’s integrations with platforms like SendGrid or Klaviyo to audit sender reputation in real time, improving your ability to act before blocklists trigger.

Build a Time Series Dataset from DMARC Reports Using SQL Aggregations

You can transform raw DMARC XML reports into a time series by grouping daily authentication results by domain, then calculating pass rates and failure trends using SQL. This lets you detect anomalies, visualize delivery health over time, and correlate spikes with configuration changes. Tools like inbox placement testing rely on similar data patterns to assess real-world deliverability.

Process: From XML to Time Series

Parse and extract daily authentication results. Use a SQL function or ETL tool to extract report metadata—specifically, org_name (domain), report_date, and auth_results from the DMARC XML. Focus on the spf and dkim pass/fail outcomes. This step turns structured XML into tabular data you can query.
Aggregate by domain and day. Group the extracted data by org_name and report_date. For each day, compute total messages, SPF passes, DKIM passes, and total failures. This creates one row per domain per day—your base for time series analysis.
Calculate daily pass ratios. In your query, create a computed column: pass_rate = (spf_pass + dkim_pass) / total_messages. Use COALESCE to avoid division by zero. This gives you a clean daily metric of email authentication success, normalized across volume.
Flag anomalies using window functions. Apply LAG() to compare today’s failure rate to yesterday’s. Use ABS(lag_rate - current_rate) > 0.2 to flag sudden spikes. This helps isolate misconfigurations or phishing attempts in real time without manual inspection.
Apply 7-day and 30-day moving averages. Use AVG(pass_rate) OVER (ORDER BY report_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) to smooth weekly trends. For longer-term visibility, repeat with 29 preceding rows. These averages reduce noise from daily fluctuations and reveal actual trends.

Why It Matters for Deliverability

DMARC reports show what’s failing, but not when or how badly. By turning them into a time series, you shift from reactive to proactive monitoring. A spike in SPF failures? Correlate it with a campaign. A steady 92% pass rate over 30 days? That’s a sign of stable reputation. This is part of what DMARC’s RFC 7489 envisions: continuous, measurable feedback for email health.

The 5 steps described in “Process: From XML to Time Series”, in order.

Running these aggregations in a warehouse (like BigQuery, Snowflake, or Redshift) lets you connect DMARC data directly to your marketing and deliverability dashboards. The same logic that powers email verification tools such as MailTester’s real-time address validation can now scale across entire domains, enabling automated alerts and compliance tracking.

Use SQL to Detect Anomalies in Email Authentication Patterns

Let’s use SQL to turn DMARC XML data into actionable insights. Query your email authentication logs to detect when failure rates spike beyond 5% for two days in a row—triggering alerts before deliverability drops. Correlate failures with source IPs or domains to isolate misconfigurations. Then, flag non-compliant IPs that consistently fail alignment checks to catch sender misconfigurations early.

Set up threshold-based anomaly detection

Aggregate daily DMARC report data by authentication result (pass, fail, policy, none) using a SQL window function.
Calculate the percentage of failed reports relative to total reports for each day.
Use a self-join or window function to compare today’s failure rate with yesterday’s, flagging any instance where both are above 5%.
Set up automated notifications via your monitoring system when this condition is met. This helps catch sender issues before they impact inbox placement.

Correlate failures with source IPs and domains

Join DMARC report data with source IP and domain metadata, using a common identifier like the "spf" or "d" tag from the report.
Group failed reports by source IP and domain, then rank them by frequency and persistence over time—consistently failing domains may indicate sender misconfiguration.
Apply statistical correlation analysis: if a specific IP or domain accounts for over 30% of all failures across consecutive days, investigate it further.
Use this to identify whether a failing domain is part of a known compromised system, or if it's an internal sender not aligning properly—common across poorly managed transactional systems.

DMARC is designed to protect domains from unauthorized use; consistent failures indicate misconfiguration or abuse. Monitoring these patterns is a key part of email governance. RFC 7483 outlines the structure and intent of DMARC reports.

For real-world context, organizations with strong email hygiene see consistent alignment and low fail rates—those that don’t often face deliverability drops. You can test your own email sending reliability with a full inbox placement diagnosis.

Test how your emails land in inboxes across providers using our inbox placement tool, which simulates real-world delivery conditions based on actual email client behavior.

Real-World Use Case: Tracking DMARC Failures Across a Global Campaign

One company sent 2 million emails via a third-party platform and saw a 98% DMARC pass rate — misleadingly high until SQL queries revealed that 2.1% of messages failed DKIM validation from a single IP. Using SQL to parse DMARC XML reports and aggregate by IP, region, and time, they detected a spike in failures tied to a specific partner server in Southeast Asia. After fixing a misconfigured outbound gateway, failure rates dropped to 0.3% within three days, demonstrating how structured data analysis can uncover hidden deliverability risks even when overall metrics appear stable.

Why DMARC XML Alone Isn't Enough

DMARC reports are raw and dense — they arrive as XML files with millions of records, often from multiple sources and time spans. Without filtering and aggregation, it's easy to miss subtle but critical patterns. The company's initial assumption—that 98% pass meant everything was fine—was undermined by the sheer volume of data masked by averages. You can't see what’s broken if the data isn’t transformed into a usable format.

Let’s say you receive daily DMARC aggregate reports from multiple domains and partners. A simple pass/fail count gives a false sense of security. But when you extract sender IPs, timestamps, and alignment results via SQL, you can query: “Which IP shows consistent DKIM failure over the past seven days?” Or “What’s the trend in failures by country?” The answer reveals the real sender — not just the reported one.

How SQL Turns XML into Timely Insights

DMARC reports follow a standard format defined in RFC 7489. Each report contains a <row> element with a <source_ip>, <count>, and <policy_evaluated> section. By using SQL’s XML parsing functions (available in PostgreSQL, SQL Server, or even BigQuery), you can extract these fields and reshape the data into time-series tables.

For example, a query can group failures by day, IP, and domain, then join with geolocation databases. That way, a 2.1% failure rate from one IP in Jakarta becomes a visible outlier — not buried in a global average. It’s not magic. It’s just structured data analysis, and it’s critical for catching problems before they erode sender reputation. You might not need a full data warehouse—processing one report per domain per day in a simple SQL engine suffices.

This kind of insight is only possible when you stop treating DMARC reports as static logs and start treating them as time-series signals. A small percentage of failures across a large send can be the first sign of a misconfigured server, a compromised domain, or even a malicious actor using your brand. The fix is faster when you can isolate the source early—often within hours, not days.

How DMARC Data Links to Overall Sender Reputation and Inbox Placement

You can’t ignore DMARC failures — they directly impact sender reputation and inbox placement. Consistent authentication failures signal poor mailing hygiene, making mailbox providers more likely to distrust your messages. Over time, this leads to higher spam filtering, lower inbox delivery rates, and even blocking. Aggregated DMARC data is a core input in reputation models used by Gmail, Outlook, and other major providers to decide whether your email lands in the inbox or the trash.

Why DMARC Signals Matter to Inbox Placement Algorithms

Mailbox providers don’t just check headers — they build reputation profiles over time. A consistent pass rate for DMARC, SPF, and DKIM is a key signal. When a sender fails DMARC across a significant portion of their traffic, it raises red flags. Gmail, for example, considers authentication success rates in its ranking and filtering decisions. Low pass rates correlate with higher likelihood of messages being flagged, delayed, or sent to spam folders.

Let’s say you’re sending to 50,000 addresses. If 2,000 of them fail DMARC each day, that’s a 4% failure rate. That’s not a one-off. On a weekly or monthly basis, this becomes visible in aggregate reports. Providers like Microsoft and Yahoo track these patterns closely. The longer the pattern persists, the more likely your sender reputation declines. Some providers will start rate-limiting or rejecting emails from sources with sustained authentication gaps.

Turning DMARC Data into Actionable Insights

Raw DMARC XML logs are useful, but not immediately actionable. You need to parse the data and transform it into time-series metrics — daily pass/fail rates, trends in reported domains, and breakdowns by receiver. Once you do that, you can build dashboards that show how authentication hygiene affects deliverability trends. For example: a sudden spike in non-aligned DKIM might predict a drop in inbox placement within 48 hours.

Tools like MailTester can help validate email lists before sending — reducing the risk of sending to addresses that are invalid or misconfigured. You can use their email checker to verify individual addresses, or the bulk verification tool to scrub large lists. These steps reduce the number of failing emails before they even leave your server and help prevent sender reputation damage.

Detecting and fixing authentication issues early protects your long-term deliverability. A high DMARC pass rate across your domains isn’t a one-time fix — it’s a continuous practice. By monitoring and acting on DMARC data with real-time analysis, you create a feedback loop that maintains healthy sender reputation and improves inbox placement over time.

For deeper insight into how your messages are received across major inboxes, consider testing your delivery path with inbox placement testing, which can reveal how authentication, content, and sender reputation collectively influence where your emails land.

What DMARC Metrics Matter Most for Deliverability Dashboards

Focus on pass rate, fail vs. quarantine, source IP distribution, and alignment results. These four metrics reveal authentication health, sender reputation risks, and whether your email infrastructure is consistent or compromised. Use SQL to aggregate DMARC XML reports into time series so you can track changes in real time and react before deliverability drops.

Key Metrics to Track in Your DMARC Dashboard

Pass rate (SPF + DKIM): The percentage of messages that pass both SPF and DKIM checks. A sustained drop below 95% signals issues with authentication setup or compromised senders. You can verify sender infrastructure health using tools like MailTester’s real-time API to validate domains and detect misconfigurations early.
Fail vs. quarantine status: A failure marked as "quarantine" (policy=quarantine) means the email was not rejected but treated as suspicious. A "reject" (policy=reject) failure indicates proper enforcement. Focus on identifying why quarantined messages occur — often due to misaligned headers or weak DKIM key rotation.
Source IP distribution: High variance in source IPs across reports suggests inconsistent sending infrastructure or compromised accounts. A single IP sending 90% of messages is safer than 15 different IPs with erratic traffic patterns. Use SQL to group by IP and visualize spikes in outlier activity — this helps detect spoofing or shared inbox abuse.
Alignment results: DMARC only applies if either SPF or DKIM aligns with the From domain. A pass in SPF but a fail in alignment means the email still fails DMARC. Track alignment separately from pass/fail to catch subtle issues like subdomain mismatches or third-party sending without proper alignment.

How to Apply This in Practice

After extracting DMARC XML reports, use SQL to transform raw data into timestamped metrics. For example, group daily counts of pass/fail by policy and align them with sender IPs. This creates a time series you can visualize in tools like Grafana or Tableau. Standards around this process are defined in RFC 7489, the DMARC specification, which details how reports should be structured and interpreted.

“DMARC is the backbone of email authentication — but only if you’re measuring the right signals.”

Regularly auditing these metrics helps you spot anomalies before they impact inbox placement. For example, a sudden spike in non-aligned DKIM failures might point to a third-party tool using outdated signing methods. Use MailTester’s bulk verification to clean your sender list and reduce the risk of sending from unauthorized or compromised domains.

Integrate DMARC Data with Email-Verification Insights from MailTester

You can use SQL queries to transform DMARC XML reports into time series data that reveal patterns in email deliverability failures. Then, cross-reference those patterns with pre-send verification results from MailTester—validating sender lists to catch invalid, disposable, or role addresses before they impact your reputation. When deliverability drops coincide with sends to unverified addresses, it signals poor list hygiene. With MailTester’s 98.9% accuracy rate, you can trust the verification data when diagnosing sender health. This integration helps isolate whether delivery issues stem from sender reputation or list quality.

Validate Your Send List Before Distribution

Before you send, run your email list through MailTester’s bulk verification tool. This catches invalid addresses, disposable domains, and role accounts like admin@ or postmaster@—common sources of bouncebacks and reputation damage. Using their bulk verification feature, you can clean entire lists in minutes, reducing the risk of failing DMARC validation due to poor sender practices.

Let’s say you notice spikes in DMARC failures for a campaign. If those failures align with sends to addresses flagged as "catch-all" or "risky" by MailTester, it suggests a direct link between bad data and poor inbox placement. This is not a coincidence—it’s a signal that list hygiene is affecting deliverability.

Correlate Verified Data with Post- Send DMARC Metrics

DMARC reports provide detailed failure data by sender IP, domain, and authentication result. You can parse this XML into time-series tables using SQL—grouping failure counts by date, source IP, and envelope-from domain. When you join these tables with MailTester’s results, you gain a clearer view of whether your delivery failures stem from technical issues or data quality.

For example, a spike in SPF failures for a campaign targeting 10K new subscribers? Run a quick comparison: how many of those addresses were verified via MailTester? If the failure rate correlates tightly with unverified or disposable inboxes, the root cause isn’t your infrastructure—it’s your list. This kind of insight supports actionable changes in your acquisition and onboarding workflows.

Industry standards like RFC 7483 emphasize the importance of sender reputation and address legitimacy in email authentication. When you combine verified addresses with DMARC telemetry, you’re aligning your operations with best practices. This isn’t just theory—it’s how top-tier senders isolate and fix deliverability issues at scale.

Automate DMARC Analysis: From Batch Reports to Real-Time Dashboards

You can automate DMARC analysis by scheduling weekly SQL-based ETL jobs that parse incoming XML reports, extract key metrics like failure rates and source IPs, and store them in a time-series database or BI tool. This enables real-time dashboards that track trends, detect anomalies, and trigger alerts—turning raw data into actionable insights for email deliverability teams.

Turn Batch Reports into Actionable Metrics

Set up automated ingestion using a script or tool that downloads new DMARC XML files as they arrive from your email provider. Process them weekly, or on a schedule that aligns with your reporting needs.
Use SQL to extract and normalize data. Map fields like org_name, row.source_ip, row.count, and row.disposition into consistent columns. Filter out noise—like reports from non-email sources—before transforming.
Aggregate across time and domain. Write SQL queries to roll up counts by day, by source IP, and by email domain. This enables you to see patterns: are spoofing attempts climbing? Is a specific IP generating more failures?
Store outcomes in a time-series database like InfluxDB, TimescaleDB, or directly in a BI platform such as Looker, Tableau, or Power BI. These systems are optimized for date-based queries and visualizing trends over time.
Embed key deliverability metrics in dashboards. Display daily failure rates, SPF/DKIM alignment issues, and top sources of failure. Include anomalies—like sudden spikes in non-delivery reports—using threshold-based alerts.

Monitor Deliverability Trends with Confidence

Real-time dashboards let you answer critical questions faster: Is your DMARC configuration effective? Are new domains being spoofed? When a spike occurs, you can act before it impacts sender reputation. The ability to trace failure sources back to specific IPs or domains helps prioritize technical fixes.

The 5 steps described in “Turn Batch Reports into Actionable Metrics”, in order.

For context, the IETF’s DMARC specification (RFC 7483) defines how aggregate reports should be structured. It's the foundation of how you extract meaningful data from raw XML. Using SQL to process this standardized format brings consistency and scalability to your analysis.

Consider integrating email verification tools like MailTester’s bulk verification to validate your sending list against known invalid or risky addresses. This reduces the chances of DMARC failures caused by sending to non-existent or abused email accounts. For real-time validation, use the MailTester verification API in your application flow—ensuring every address is valid before it leaves your system.

The Bottom Line: Turn DMARC XML into Strategic Deliverability Intelligence

DMARC reports are more than compliance checkboxes. They’re real-time signals of sender health — revealing authentication failures, spoofing attempts, and trends that impact inbox placement.

SQL queries turn raw, often unwieldy DMARC XML into structured time series data. This enables teams to track sender reputation, identify policy gaps, and act before deliverability degrades.

When paired with a tool like MailTester — which verifies email lists with 98.9% accuracy — you connect clean data with strong authentication. The result? Higher inbox delivery and fewer surprises.

Sources

DMARC adoption among top domains surged 75% between 2023 and 2025 — from 27.2% to 47.7% — in the wake of Google and Yahoo's bulk-sender authentication requirements. — EasyDMARC 2025 DMARC Adoption Report (2025)
Since May 5, 2025, Microsoft Outlook requires SPF, DKIM, and DMARC from domains sending 5,000+ emails per day, rejecting non-compliant mail outright at the SMTP level with error 550 5.7.515. — Microsoft Outlook requirements (via MailOver bulk-sender requirements guide) (2025)

Keep reading

Ready to put this into practice? MailTester verifies emails with 98.9% accuracy — start with 100 free verifications.

Frequently asked questions

Can I use SQL to parse DMARC XML without coding?

You can, but only with tools that support XML-to-SQL conversion. Most databases require a preprocessing step. Consider using a dedicated ETL service or platform with built-in XML support.

What database is best for DMARC time-series analysis?

PostgreSQL or BigQuery are ideal. Both support JSON/XML parsing, time-series functions, and scalable aggregations for large report volumes.

How often do DMARC reports come in?

Daily reports are typically sent by receivers. Some providers may send them every 24 hours, while others use longer intervals. Consistency varies by domain and provider.

Do DMARC reports include the actual email content?

No. They contain metadata: sender IP, authentication results, domain, policy, and message counts. They do not include subject lines, body, or attachments.

How do I handle multiple DMARC reports from different domains?

Use domain or org_name as a dimension in your SQL queries. Aggregate separately for each domain and correlate across senders to detect spoofing or misuse.

What’s the difference between DKIM and SPF in DMARC reports?

SPF checks the sending IP against authorized senders. DKIM verifies a digital signature on the message body. DMARC evaluates both and reports alignment with the From domain.

Can I detect spoofing attempts using DMARC XML?

Yes. DMARC reports flag messages that claim to come from your domain but fail SPF or DKIM checks. A spike in failures from unauthorized IPs often indicates spoofing.

How does sender reputation affect DMARC reporting?

Mailbox providers like Gmail and Yahoo use sender reputation when evaluating incoming mail. High failure rates in DMARC reports can reduce reputation scores and hurt inbox placement.

Are DMARC reports free?

Yes. Receivers (including Google, Microsoft, and Yahoo) send DMARC reports at no cost to domain owners who publish a DMARC record.

How do I get started with DMARC analysis?

Publish a DMARC record with a reporting email, wait for reports, extract them, parse the XML, and load into a SQL database. Start small with daily summaries and expand to full time-series monitoring.

Can I verify email addresses using MailTester with DMARC data?

Yes — MailTester verifies email address validity, catch-all status, and risk level. When combined with DMARC insights, you get a complete picture of list health and sender security.

What’s the accuracy of email verification with MailTester?

MailTester’s email verification accuracy is 98.9%, helping you clean lists before sending and reducing bounce rates and spam complaints.