Step-by-step: How we audited and cut 30% of wasted spend on placement fees.

Hey folks,

I wanted to share a recent deep-dive my team and I performed on our programmatic ad stack. We were seeing healthy overall performance, but I had a nagging feeling about the "placement fees" and "partner costs" line items in our platform reports. They felt... bloated. After a three-week audit and remediation project, we managed to identify and cut about 30% of what we're now calling "wasted" spend on these fees. It wasn't magic, just systematic analysis and some automation. Here’s exactly how we did it, step-by-step.

**Step 1: Centralized Log Ingestion**
Our spend data was scattered across three different platforms (DV360, The Trade Desk, a smaller network). Manually collating reports was a nightmare. We set up a simple but effective pipeline:
1. Used the platforms' APIs (where available) or scheduled CSV exports to a dedicated S3 bucket.
2. Wrote a Python script (Ansible could handle the orchestration here) to normalize the data fields (e.g., renaming `partner_fee` to `placement_fee`).
3. Loaded everything into a PostgreSQL database for querying.

Here's a snippet of the Ansible task to fetch one of the reports. We used `uri` and `aws_s3` modules.

```yaml
- name: Fetch and push daily DV360 report
uri:
url: "{{ dv360_report_url }}"
method: GET
headers:
Authorization: "Bearer {{ dv360_access_token }}"
return_content: yes
register: dv360_report
delegate_to: localhost

- name: Upload raw report to S3 staging
aws_s3:
bucket: "our-ad-audit-bucket"
object: "/raw/dv360/{{ ansible_date_time.date }}.csv"
src: "{{ dv360_report.content | b64decode }}"
mode: put
```

**Step 2: The Analysis - Finding the Culprits**
With all data in one place, we ran SQL queries to find anomalies. We looked for:
* **Placements with fees > 15% of media cost.** This was our initial high-water mark.
* **Partners/sites where the *effective CPM* (media cost + fees) was significantly higher than the average for similar performance tiers.** A placement with a $0.50 media CPM but a $2.50 fee is a massive red flag.
* **"Zero-value" exchanges:** Partners where fees consumed over 20% of spend but delivered zero conversions over a 90-day lookback window.

The query structure was simple but powerful:
```sql
SELECT
placement_name,
partner_name,
SUM(media_cost_usd) as total_media_cost,
SUM(placement_fee_usd) as total_fees,
(SUM(placement_fee_usd) / NULLIF(SUM(media_cost_usd), 0)) * 100 as fee_percentage
FROM impressions
WHERE date >= NOW() - INTERVAL '90 days'
GROUP BY 1, 2
HAVING (SUM(placement_fee_usd) / NULLIF(SUM(media_cost_usd), 0)) * 100 > 15
ORDER BY fee_percentage DESC;
```

**Step 3: Action & Automation**
Finding the waste was only half the battle. We took two key actions:
1. **Blacklisting:** We exported the list of high-fee, low-performance placements/partners and added them to our platform-level blocklists.
2. **Guardrails:** We built a weekly monitoring job (using Terraform to schedule a Lambda function) that runs a similar analysis and posts a summary to our security Slack channel. If any new placement breaches our fee threshold, we're alerted within 7 days.

The real win came from realizing that many of these fees were negotiable or could be bypassed by buying through different, more direct paths. We're now having those conversations with our partners.

The process feels obvious in retrospect, but without bringing the data together in a queryable state, we were flying blind. The 30% savings is now being reallocated to higher-performing inventory. Hope this walkthrough helps someone else scrutinize those often-overlooked fee columns!

—John