Media dashboard aggregation for a marketing agency

This one comes from discovery work with a real agency: a regional marketing firm in the Southeast that serves banks and credit unions, doing creative strategy, media buying, and reporting. We scoped the build and walked them through it. The numbers below are projections from that discovery, not measured results from a finished deployment.

The problem

Every quarter for 15 years, someone built the client media reports by hand:

1. Collect the reports. Media reps send PDFs and CSVs by email; some numbers live only in online dashboards. Ten or more sources per cycle.
2. Enter the data into Excel.
3. Normalize it. Every rep formats differently.
4. Aggregate it into the client-facing report. Each bank client sees only its own data, and spend figures are sensitive, so the segmentation has to hold.

That is 40-plus hours a quarter of assembly work. It also meant no month-over-month comparison existed, because the data only ever lived in one quarter's spreadsheet. The agency never solved this in 15 years of trying.

The build we scoped

• An inbox reader that pulls the PDFs and CSVs out of email and extracts the tables.
• Scrapers for the online media dashboards, so the numbers that never get exported also land in the database.
• A normalization step that standardizes across rep formats, so a new rep's format is a mapping problem, not a rebuild.
• Per-client segmentation: each banking client sees only its own data, with spend-level access controls.
• Month-over-month views, which is the reporting the agency had never been able to offer.

The plan we scoped: four weeks of discovery on the data sources, eight weeks of build, two weeks of launch with the team. The agency's staff spend their time on the exceptions and the analysis instead of the assembly.

The projected math

• Manual assembly: 40-plus hours a quarter down to a small exception queue. Call it 90 percent of the labor gone.
• Hours freed: about 160 a year.
• Labor value: $25,000 to $40,000 a year at agency rates, against a subscription-priced build.
• Month-over-month reporting: new capability, no prior baseline to compare against.

The 833 percent ROI figure from the original pitch assumed the top of the labor range and a low subscription price. Treat it as the best case in the range, not the expectation.