Weekly email tracking that doesn't fall apart
Most people building a weekly email marketing tracker start with the wrong columns. They put open rate at the top, click rate right after it, and then wonder why their spreadsheet looks fine while their revenue line stays flat. I learned this the hard way in 2019 when I spent three months analyzing campaigns that looked healthy on paper but were quietly hemorrhaging money. The breakthrough came when I stopped tracking what email providers report and started tracking what actually moves the needle.Building an Email Marketing Tracker Weekly
The core structure is simpler than most tutorials make it. You need these columns at minimum: campaign name, send date, list segment, total recipients, unique opens, unique clicks, click-to-open rate, unsubscribe count, bounce count, attributed revenue, and UTM parameter snapshot. That's it. Everything else is decoration that inflates your spreadsheet without adding insight. I used to add reply rate and complaint rate columns too. Stopped doing that after realizing my ESP only tracks bounces properly if you set up the feedback loop, and nearly every small business I work with skips that step. Unsubscribes are visible by default. Complaints are not, unless you've actually configured it. Tracking a metric your data source can't reliably deliver creates false confidence.Here's the practical workflow that saves time: Export your weekly report as CSV, clean the column headers to match your spreadsheet template, paste into a dedicated sheet, and let formulas handle the rest. The formula-driven approach means you're not manually calculating CTR or CTO each week. You're just importing and reviewing.
The CTO formula alone is where most people waste time. Click-to-open rate equals unique clicks divided by unique opens, multiplied by 100. If your open rate is 22% and your click rate is 3.5%, your CTO is 15.9%. That number tells you whether your email content actually convinced people who opened it to do anything. Open rate tells you nothing about that. It tells you whether your subject line worked, which matters less than most people think once you've sent fifty campaigns.Attributed revenue is the column that changes how you look at everything else. Without it, you're optimizing for engagement metrics that don't pay bills. Set up UTM parameters on every link in every campaign. Use a consistent naming convention like utm_source=newsletter&utm_medium=email&utm_campaign=weekly_update_jan15. When someone clicks through and buys, your analytics platform can trace it back. Your tracker then shows you which campaigns actually earned money, not just which ones got clicked.
A problem I ran into that nobody warns you about
Mailchimp and similar platforms don't count an open if the email is loaded in the Apple Mail privacy protector background before the user even sees it. This skews open rates upward by 8 to 15% during the first 24 hours after send. Your tracker will show a spike, you'll celebrate, and nothing has actually changed. I caught this by cross-referencing open rate trends against click-through patterns over several months. The opens kept climbing while clicks stayed flat, which should never happen if engagement was genuinely improving. Once I realized privacy protections were inflating the open numbers, I stopped using open rate as a primary KPI entirely. Now I track CTO and attributed revenue as the two headline metrics. Everything else is reference data.The workaround for your tracker is straightforward. Add a column called "open rate adjusted" that applies a flat 10% reduction to the raw open rate for a sanity check. It's not scientifically perfect, but it stops you from making decisions based on inflated numbers. I've seen teams pause underperforming campaigns and double down on strong ones based on open rate alone. This correction factor keeps you grounded.
Get the Full Details
The parts that actually matter and the parts you can ignore
Spam score and deliverability rates deserve monitoring but shouldn't live in your main tracker unless your inbox placement is actively dropping. Most ESPs show this in their native dashboard. Pulling it into a separate tab keeps your weekly view clean. Same with list growth rate. It matters for quarterly planning, not for deciding whether Tuesday's send performed well. What does matter week over week is CTO trending. If your CTO drops from 15% to 9% between sends, something broke. Could be a broken link. Could be a mismatched audience segment. Could be that you suddenly started sending to addresses that never opted in properly. The tracker makes the drop visible. What it doesn't do is tell you why, and that's where your actual expertise has to kick in. Unsubscribe velocity is another one people overlook. A normal unsubscribe rate sits between 0.1% and 0.5% of recipients per campaign. If you're seeing 1% or higher consistently, your list quality is degrading or your content is drifting from what subscribers signed up for. I tracked this by adding a running average column that calculates the last four campaigns. It catches gradual drift that a single week's numbers hide.Click rate deserves a different treatment. Raw click rate is useful for comparing campaigns within the same list segment. It becomes dangerously misleading when you compare across segments of different sizes or different historical engagement levels. A 4% click rate on a cold list segment might be excellent. A 4% click rate on a warm segment that usually averages 8% is a warning sign. Always compare within similar cohort groups.
Where this system breaks down
Your tracker is only as good as your attribution setup. If you're sending to a list that primarily converts on mobile, and your website's session duration tracking is broken or your e-commerce platform isn't feeding conversions back properly, your revenue column will be garbage regardless of how clean the rest of the spreadsheet is. I've inherited spreadsheets where the CTO and click rates looked amazing and the revenue column was flatlining. The problem was always a broken attribution chain, not bad email performance. Another failure mode is tracking too many campaigns in one sheet. Once you pass roughly twenty-five campaigns in a single file, filtering and sorting become tedious. I keep three months of data per file, then archive the rest into a compressed backup. The active tracker stays lean enough to scan in under two minutes each Monday morning.Platform API limits also bite people. If you're using a tool that auto-syncs your ESP data into the tracker, you'll hit rate limits during high-volume send weeks. The spreadsheet will show stale data until the sync catches up. I switched from auto-sync to manual CSV export specifically to avoid this. It takes two minutes extra per week and eliminates the frustration of waiting for corrupted data.
What to download and how to use it
I've been refining my tracker template for about four years now. The current version includes predefined formulas for CTO, attributed revenue per recipient, unsubscribe rate, and a rolling four-campaign average for unsubscribe velocity. It has conditional formatting that flags abnormal spikes in unsubscribes and drops in CTO below your personal baseline. The baseline is something you set once and update quarterly.You can find the template by searching for my Email Marketing Tracker Weekly spreadsheet on Google Sheets. It's free, no email gate required, and built for anyone running a newsletter or promotional email campaign on a weekly schedule. It works with Mailchimp, ConvertKit, Klaviyo, and any platform that exports clean CSV data. The column headers in the export just need to roughly match what the template expects, and the cleanup script handles the rest.
