Using Pivot Tables to Analyse Email Lists: Domain Distribution, Source Breakdown, Duplicate Counts and Provider Analysis in Excel and Google Sheets
By Email ExtractorPublished 6 min read
On this page
Why Pivot Tables for Email Analysis
Pivot tables transform a flat list of email addresses into actionable intelligence: which domains dominate your list, which sources produce the most contacts, how many duplicates exist across sources, what percentage of your list uses free email providers versus business domains, and how your list composition has changed over time. These analyses take seconds in a pivot table but would require manual counting or complex formulas in a flat spreadsheet:
Analysis
What it tells you
Business decision it informs
Domain distribution
Which email domains appear most frequently
Identify largest company concentrations; detect data quality issues (too many addresses from one domain suggests a list quality problem or a scraping artefact)
Source breakdown
Which sources contribute the most contacts
Allocate acquisition budget; identify highest-value lead sources; detect underperforming channels
Provider type split
Free provider (Gmail, Yahoo, Outlook) vs. business domain vs. education vs. government
B2B list should be majority business domains; high free provider percentage suggests consumer list or unverified data
After uploading your source files to Email Extractor and extracting email addresses, download the results as "CSV with sources". This gives you two columns: the email address and the source file it came from.
Step 2: Add analysis columns
Before creating pivot tables, add columns that make analysis possible. These columns are derived from the email address itself:
In Excel: Select all data > Insert > PivotTable > New Worksheet
In Google Sheets: Select all data > Insert > Pivot table > New sheet
Pivot Table Analyses
Analysis 1: Domain distribution (top domains)
Pivot table setting
Value
Rows
Domain
Values
Count of Email (or COUNTA)
Sort
Descending by count
Filter
Top 20 (or top 50 for larger lists)
What to look for:
Finding
What it means
Action
One domain has 100+ addresses
Large company with many contacts; or a data quality issue
If legitimate, this is a key account; if unexpected, investigate source
gmail.com / yahoo.com / outlook.com dominate
List is heavily consumer / personal email
For B2B purposes, these contacts are less valuable; segment separately
Many addresses from your own domain
Internal email addresses mixed into external contact list
Remove your own domain addresses from outreach lists
Domains with typos (gmial.com, yahooo.com)
Data entry errors
Correct typos before importing to CRM
Domains that do not resolve
Invalid domains; defunct companies
Remove or verify before sending
Analysis 2: Source breakdown
Pivot table setting
Value
Rows
Source (the source file from Email Extractor's CSV with sources)
Values
Count of Email
Sort
Descending by count
What to look for:
Finding
What it means
Action
One source contributes 80%+ of addresses
Concentration risk; if that source degrades, list quality drops
Diversify acquisition sources
Some sources contribute very few addresses
May be low-value sources; or highly targeted niche sources (quality over quantity)
Evaluate quality of contacts from low-volume sources before cutting them
Walk-in / manual sources have lowest counts but may have highest conversion
In-person contacts are often higher intent
Track downstream conversion by source to measure true source value
Analysis 3: Provider type distribution
Pivot table setting
Value
Rows
Provider Type (the column you calculated)
Values
Count of Email; % of total
Benchmarks:
List type
Expected business domain %
Expected free provider %
Expected education %
B2B enterprise prospect list
80-95%
5-15%
0-5%
B2B small business prospect list
50-70%
25-45%
0-5%
Mixed B2B/B2C list
30-50%
45-65%
1-5%
Consumer list
5-20%
75-90%
2-5%
Education / academic list
10-30%
20-40%
40-70%
Analysis 4: Cross-source duplicate analysis
To see how many addresses appear in multiple sources:
Pivot table setting
Value
Rows
Email
Values
Count of Source (COUNTA of source); Distinct Count of Source
Filter
Count > 1 (shows only emails appearing in multiple sources)
What to look for:
Finding
What it means
Action
30%+ of addresses appear in 2+ sources
High overlap between acquisition channels; you are paying for the same contacts from multiple sources
Consider consolidating sources; Email Extractor has already deduplicated, but this analysis shows where you can reduce acquisition cost
Certain sources have 80%+ overlap with each other
These two sources are essentially the same list
Evaluate which source to keep based on cost, freshness and additional data quality
Some contacts appear in 5+ sources
These are either very active / visible contacts or they are scraped addresses appearing in many public directories
Likely valid; may be decision-makers with high public visibility
Analysis 5: Role-based address analysis
Pivot table setting
Value
Rows
Is Role-Based (the column you calculated)
Values
Count of Email; % of total
Role-based addresses (info@, admin@, sales@, support@, office@, contact@, webmaster@, marketing@, hr@) are shared mailboxes rather than individual contacts. A high percentage of role-based addresses indicates a list built from website contact pages or directories rather than from individual contact research. Many email marketing platforms restrict sending to role-based addresses because they tend to generate complaints and have lower engagement.
Tips for Large Email Lists
Challenge
Solution
Pivot table slow on 100,000+ rows
Excel: use Power Pivot (Data Model) for large datasets; Google Sheets: may need to split into smaller files
Too many unique domains to review
Add a "Top domain" column that groups domains below a threshold into "Other"
Need to share analysis with stakeholders
Excel: pivot chart from pivot table; Google Sheets: chart from pivot table; export as PDF
Want to track changes over time
Add acquisition date column; use date as a column field in pivot table to see distribution by period
Need to combine with CRM data
Export pivot table summary; import into CRM as a report or dashboard
