You don't need a data warehouse to automate a weekly report. A spreadsheet and scheduled exports from Google Ads, GA4 and your CRM are enough for a first version that you can trust on Monday morning.
This guide walks through a small example from start to finish. You'll define the week, map each figure to its source, combine the inputs without double counting and add a check that stops the report when data is missing.
Step 1. Define the week and its figures
Most reporting arguments start because two people mean different things by "last week". Write the definition down.
Reporting week. Monday 00:00 to Sunday 23:59, in one named timezone. In this example, America/Chicago. Make sure your Google Ads account, GA4 property and CRM export all use the same week, or note where they don't.
Field map
Every figure in the report gets one row. If you can't fill in a row, the figure isn't ready to automate.
| Figure | Source | Date field | Owner | Notes |
|---|---|---|---|---|
| Spend | Google Ads campaign report | Day | Paid media lead | Account timezone |
| Clicks | Google Ads campaign report | Day | Paid media lead | |
| Enquiries | CRM leads export | Lead created date | Marketing operations | Count each lead once |
| Enquiries from Google Ads | CRM leads export, Source field | Lead created date | Marketing operations | Uses the CRM's source, not GA4 |
| Qualified leads | CRM leads export | Qualified date | Sales operations | Your agreed definition of qualified |
| Cost per qualified lead from Google Ads | Calculated | Same week for both parts | Analyst | Spend divided by qualified leads from Google Ads |
Pick one source for each figure and stick to it. If you count enquiries from GA4 one week and the CRM the next, the trend line means nothing. The guide Why GA4 and Google Ads conversions don't match explains why those two sources rarely agree.
Step 2. Get the inputs into one report
Set up the exports
- Google Ads. Schedule a daily campaign report with Day, Campaign, Cost and Clicks, and schedule email delivery. Import the file into your spreadsheet. One row per campaign per day.
- CRM. Schedule a weekly export of leads created or qualified in the reporting week, with lead ID, created date, qualified date and source.
- GA4, if you use it. Schedule or export the key event count for the same week, for context only.
Paste or import each export into its own tab. Don't edit the raw tabs by hand. Build the report on a separate tab that reads from them.
Aggregate before you join
The most common way to double count is to join daily ad rows to individual leads. Every lead gets repeated once for each matching ad row, and spend gets added up several times.
Instead, total each source to the week first, then put the weekly totals side by side.
- Spend. Sum of Cost for every row in the week.
- Enquiries. Count of unique lead IDs created in the week.
- Qualified leads. Count of unique lead IDs qualified in the week.
Worked example, week of October 19 to 25, 2026
Fictional data. Google Ads daily rows for the Services campaign.
| Day | Cost |
|---|---|
| Mon Oct 19 | $268.10 |
| Tue Oct 20 | $281.55 |
| Wed Oct 21 | $274.30 |
| Thu Oct 22 | $259.85 |
| Fri Oct 23 | $247.20 |
| Sat Oct 24 | $248.90 |
| Sun Oct 25 | $256.50 |
| Week total | $1,836.40 |
Weekly totals across campaigns.
| Campaign | Spend |
|---|---|
| Brand | $412.60 |
| Services | $1,836.40 |
| Performance Max | $951.00 |
| Total | $3,200.00 |
CRM totals for the same week. 48 enquiries, 31 from Google Ads. 19 qualified leads, 12 from Google Ads.
Cost per qualified lead from Google Ads. $3,200.00 divided by 12 equals $266.67.
Check the subtotal against the original
Before you trust the weekly total, compare it with the platform. In Google Ads, set the date range to October 19 to 25 and read the total cost for each campaign. If your spreadsheet says $1,836.40 for Services and Google Ads says $1,836.40, the subtotal passes. Do the same for each campaign and for the CRM counts.
Step 3. Add commentary and a release check
The release check
Run these checks before anyone writes a word of commentary.
- Every source has a file for the reporting week.
- Every daily source has seven days of rows.
- Each weekly subtotal matches the platform total for the same dates.
If any check fails, the report is held. It is not sent with a gap or an estimate. The analyst gets a note saying which check failed.
The missing export
Here is the same week with one problem. The Sunday export for the Services campaign didn't arrive.
| Check | Result |
|---|---|
| Files present | Pass |
| Days of rows for Services | 6 of 7. Sun Oct 25 missing. Fail |
| Services subtotal | $1,579.90 in the sheet, $1,836.40 in Google Ads, difference $256.50. Fail |
| Release | Held |
Without the check, the report would show total spend of $2,943.50 and cost per qualified lead of $245.29. Both look better than the truth, and nobody would know why.
The note to the analyst.
Report held. Google Ads Services campaign has no rows for Sun Oct 25. Sheet subtotal is $256.50 below the Google Ads total for Oct 19 to 25. Re-run the Services export for Oct 25 and refresh the report.
Once the export is re-run, all three checks pass and the report can go out.
Add commentary last
Commentary comes after the numbers pass. Keep it short and tied to figures the reader can check. If you use AI to draft it, give it only the checked totals and the previous week's totals, and have the analyst edit and approve it.
Sample decision note, fictional.
Spend was $3,200.00. Google Ads produced 12 qualified leads at $266.67 each. Services drove most of the spend. Review Services search terms before next Monday.
The filled report
| Figure | Week of Oct 19 to 25 |
|---|---|
| Spend | $3,200.00 |
| Enquiries | 48 |
| Enquiries from Google Ads | 31 |
| Qualified leads | 19 |
| Qualified leads from Google Ads | 12 |
| Cost per qualified lead from Google Ads | $266.67 |
| Release check | Passed after Services export re-run |
| Approved by | Analyst name and date |
You can rebuild this example in a spreadsheet with the tables above and get the same numbers.
When to have it built
This method works well for a few sources and one report. When you need more sources, data that refreshes on its own, source links behind every figure or a team that can't spend time maintaining exports, it may be worth having it built. See marketing reporting automation for how we build and hand over a custom reporting workflow. Back to all guides.
Share your current report with us, and we'll tell you what an automated version would check.