Attribution report builder

The ask

Every account the team reports on needs a one-slide Campaign Overview: flight dates, reach and frequency, the website visits its TV and streaming ads drove, the creatives and networks that drove the most, and room for recommendations. Building one by hand meant opening the PowerPoint template, going to three or four internal sites, copying the metrics over, filling them in and exporting: 30 minutes or more a report. 650+ accounts are being onboarded in late Q4, and a senior director asked for the reports to be automated, for a planned Q1 deployment.

Each slide lives in that account’s folder in the shared library and has to be rebuilt whenever new numbers arrive.

What was actually wrong

Filling in one slide is easy. Doing it 650 times, from data that wasn’t settled, is where the work was:

• The source wasn’t settled. The first plan was a one-row-per-account file from a data flow that was its own project and didn’t exist yet. The source became the vendor’s report instead, laid out for reading rather than loading: a row per account per media group, creative and network lists packed into text cells with stray | marks and the word “None”, and a second account under the same name marked only by a trailing -1.
• Gaps that look finished. A blank written as 0, or a logo box still reading “Logo Here”, reaches a client looking complete.
• Fixed boxes, variable text. Names run from a few letters to a few dozen, and the slide’s boxes don’t grow.
• Hundreds of files in a synced library. A half-written file can sync, someone can have a deck open, and a renamed account shouldn’t get a second folder.

How I found out

I started with the slide. Every field got a column, a type and a rule: required or optional, how dates and percentages arrive, which pairs must add up to 100%, and that incremental reach can’t exceed reach. That became the data contract, with rules for the upstream flow that would write the file.

Then I built a test file of 320 made-up accounts. The first twelve each test one thing, such as a missing reach, a duplicate row, a renamed account or an ended flight. A 12-account version ran on a work machine at the end of September.

When the vendor’s report became the source, its layout set the rules for a second reader: which rows belong to which account, which media groups count as streaming, and how to clean a list cell.

Options and the call

Nothing to sign in to. The script runs on a work machine and writes into the library’s synced folder; OneDrive does the uploading. There are no stored passwords, no API keys and no Office automation, just three open-source Python libraries for PowerPoint, Excel and images.

A new file each run. The first version kept one deck per account and rewrote its numbers in place, skipping decks whose numbers hadn’t changed and keeping recommendations typed into them. The current version writes a dated deck, such as Lakeside Dental Campaign Overview 2026-10-05.pptx. A new day adds files and leaves earlier ones alone; a second run the same day replaces that day’s. That protects two things: a rerun on the same day can’t stack up duplicates, and past reports stay put for anyone comparing against them.

Drawn in code, with the template as an option. The team’s PowerPoint template marks each value with a {{Field_Name}} token, and the script can fill those in the token’s own formatting, even when PowerPoint has split one across several runs of text. But anything placed by hand can shift, so by default the script draws the slide itself, laid out like the template with every position and size fixed.

A gap stays visible. A missing or unreadable value never becomes a number. The template keeps its token, and the drawn slide shows the field’s own name in the value’s style, for whoever finishes the deck to type over. Accounts with no visits still get a slide showing 0, flagged in the run log so sales can look into it; one setting skips them instead.

What I built

The vendor’s report, an Excel file, lands in the import folder, and the run starts on its own on a schedule. One double-click runs it by hand, and installs the Python packages the first time. Then:

1. Pick the report. It takes the newest file in the folder and says which one. If that file is byte-for-byte the one the last run used, the run warns that the data may not have refreshed.
2. One account per name. Rows are grouped by account name exactly as written and added up before a trailing -1 is dropped from the display name. So lakeside-dental and lakeside-dental-1 stay two accounts, Lakeside Dental (40112) and Lakeside Dental (40112-1), each with its own folder.
3. Totals and the media split. Website visits are the sum over an account’s rows. Linear is traditional TV; VOD and addressable merge into streaming. Each side keeps its own top five creatives and networks, duplicates dropped, and the run log names anything past five.
4. Draw the slide. Each value is measured with the font’s real metrics and steps down in size until it fits its box. The four name columns share one row height and type size so their rows line up, and a name gets an ellipsis only after shrinking to 10 pt. Reach, frequency, incremental reach and the KPIs aren’t in the vendor’s report, so they’re placeholders filled in by hand.
5. File it. Each account’s folder is named Client Name (Client ID) and found by the ID, so a renamed account keeps its folder and any saved links. Long names are cut to stay under Windows’ 260-character path limit, always keeping the ID and the run date.
6. Log the run. One CSV line per account: built, replaced today’s, skipped (with the reason) or failed, plus anything missing and any warnings. One bad account never stops the run, and the script exits with an error code if any account failed.

Every file is written beside its target and swapped in, so the sync never uploads half a deck, and a deck someone has open is reported instead of overwritten:

def save(data, target):
    tmp = target.with_name(target.name + ".tmp")
    tmp.write_bytes(data)
    try:
        os.replace(tmp, target)
    except PermissionError:
        tmp.unlink(missing_ok=True)
        raise PermissionError(
            f"{target.name} is open or locked; "
            "close it and run again"
        ) from None

A check mode shows what a run would write without writing it. An optional Excel file beside each deck lists every field as Found, Missing, Invalid or Manual; it’s off by default because it doubles what the sync has to upload.

Unit tests cover the token filling (including a token PowerPoint split across runs), same-day replace and next-day add, the drawn layout, exports-only runs, skipping accounts with no visits, and the vendor reader on rows laid out like the real report.

The demo above re-creates a Monday run on six made-up accounts, a rerun that afternoon, and the next week’s report. Open any deck in the folders to see its slide, or download it as a .pptx.

What stayed familiar, what went away, what people had to learn

Stayed familiar: PowerPoint, in each account’s folder in the library the team already uses, with the same sections as the team’s template. People still write the recommendations.

Went away: building each report by hand, 30+ minutes apiece: opening the template, going to three or four internal sites, copying the metrics over, filling them in and exporting.

New:

• the run starts on its own, on a schedule;
• sales opens their account’s folder and downloads the deck;
• a dated deck per run;
• placeholders that show what still needs filling in;
• a run log that names anything missing.

Result

It’s built and tested, for a planned Q1 2027 deployment that covers the 650+ accounts being onboarded in late Q4. Each run builds every account’s deck, and sales downloads them from their folders. The figures above are estimates, not timings: at 30+ minutes a report by hand, 650+ accounts come to 325+ hours every time each one gets a report. There’s no after figure yet; that gets measured once it’s live.

The current version (Oct 7) reads the vendor’s report directly and keeps the one-row-per-account reader for when the upstream flow exists. On the 320-account test file, every deck the earlier version built passed Office’s file validation.

Still open:

• Search. The slide shows TV only. Either its data stays TV-only, or search gets its own columns and section.
• Recommendations. Rules that pick from a pre-written library, or a language model called from the script. I’ve recommended the library: the same numbers always get the same wording, it needs one brand review, and there’s no API key or client data to send. A middle path is having the model write the library once.
• Production. The output library and who can see it, a brand review of client logos next to the company’s, and a scheduled run after the upstream flow.