---
id: agencias/midia-paga-carteira
industry: Marketing agencies
usage: widely used
effort: medium
grain: Client x platform x account x campaign x day (ad, when the platform has it)
---

# Paid media for the whole portfolio

Spend, clicks and results for every client and platform in a single table, with each ad account tied to the right client.

## Executive summary

**For:** Agencies that manage ad accounts for many clients across more than one platform.

**What it solves**

- A single table with spend for every client, comparable across Meta, Google, TikTok and LinkedIn.
- Each ad account belongs to a declared client, and one client's numbers never show up in another's.
- The total per client and per day matches each ad manager, with no factor of a million, mixed currencies or shifted days.

**Why it matters:** It is the foundation for everything else: the client report, the funnel down to the sale, alerts and the AI agent all read from this table. A scale or mapping error here shows up in every report at once, and a leak between clients is the kind of mistake that costs you the account.

**Business questions it answers**

- How much did each client spend this month, by platform?
- How did client X's week go on Meta and Google?
- September spend vs. August, client by client.
- What are the portfolio's consolidated CPC and CPM by platform?
- Which ad accounts are spending without an assigned client?
- Which clients have active paid media today?

**What you need**

- Each client's ad accounts connected in Nekt (one source per account)
- An account-to-client registry: platform, account id, client, currency, time zone and status
- Each client's rule for shared accounts and taxes on the media budget

**What you get**

- A unified table by client, platform, campaign and day, already in reais
- A versioned registry stating which client owns each account
- An alert for accounts without a client and for stalled sources
- An AI agent that answers questions about the portfolio without mixing clients

## Detailed

### The problem

Each client becomes one source per platform, and the agency ends up with dozens of tables that look the same on the outside and differ on the inside. Google sends cost in micros, Meta sends everything as text, and each account has its own currency and time zone. And the ad account is not the client: one client has several accounts, one account serves more than one brand, and the account name is rarely the client name.

### Business rules

1. **The client comes from the registry, never from the name.** Keep a registry with one row per account: platform, account_id (or Google's customer_id), client_id, currency, time zone, status and start and end dates. The unified table resolves the client by platform + account_id. The table prefix and the account name in the ad manager belong to the account, not the client, and matching by name is not proof. (Avoid: Taking the client from the table or account name.)
2. **Convert each platform to currency before joining.** Google: metrics_cost_micros ÷ 1,000,000 (budget and bid are in micros too). Meta: spend, impressions and clicks arrive as text and become numbers. LinkedIn: costInLocalCurrency is text. TikTok: spend by stat_time_day. Convert in each branch, before the UNION, so the spend column has the same unit on every row. (Avoid: Unioning the cost columns as they arrive and converting in the dashboard.)
3. **Never add up different currencies.** Read the account currency (currency in the platform's accounts table) and carry the currency column all the way through. To consolidate the portfolio, convert with a declared, dated exchange rate, or leave the account out and say so. A dollar account added to real accounts produces a meaningless total. (Avoid: Summing spend across all accounts without checking the currency.)
4. **The platform date is a day label.** Meta aggregates date_start in the account time zone, and Google delivers date as the account's day, often as a midnight timestamp. Use the day as it arrives. Converting that timestamp to another time zone pushes spend to the previous day. Store each account's time zone in the registry and only compare accounts in different time zones knowing that. (Avoid: Converting the platform date to the São Paulo time zone.)
5. **Common schema with explicit columns.** Define a column contract (date, client, platform, account, campaign, ad set, ad, currency, spend, impressions, clicks, result) and list the columns in each branch of the UNION ALL, with forced types. Ids become text: campaign_id is a number on one platform and text on another, and joining to Google's campaigns table requires the CAST. (Avoid: SELECT * in each branch of the UNION ALL.)
6. **Actuals come from the performance report.** Spend, impressions and clicks come from the daily reports (adsinsights, campaign_performance, campaign_analytics, daily_report). The accounts, campaigns and ad sets tables only enrich name, status and objective. The accounts list does not define which accounts exist, and missing configuration does not erase actual spend. (Avoid: Starting from the accounts or campaigns list and bringing in spend with a LEFT JOIN.)
7. **An account without a client shows up, it does not disappear.** Every row carries client_id. When the account does not match the registry, the row gets the mapping status UNMAPPED_ACCOUNT and goes into an alert. It is not assigned to any client and is not silently dropped. Mapping coverage (spend with a client / total spend) is a number the agency tracks. (Avoid: INNER JOIN with the registry, which drops new accounts without warning.)
8. **A shared account is filtered by the registry rule.** When the account is shared with the client's in-house team or between brands, only the agency's or the brand's campaigns count, based on a term in the campaign name stored in the registry (name lowercased, with _ turned into a space). An agency source that serves several clients, such as a Google Business Profile, needs a filter by each client's location id. (Avoid: Attributing the whole account to the client.)
9. **One live source per account.** A retired, frozen or reconnected source with a different prefix sits alongside the new one. Mark in the registry which source is active and for which period, and never add a frozen table to the consolidated one. For sources that re-extract the same day, keep the latest version of each key before summing. (Avoid: Unioning every table that exists for the account.)
10. **Performance Max comes in at campaign level.** PMax has no individual ads. Use the ad report for Search, Display and Video and the campaign report only for PMax campaigns. Never add both levels for the same campaign. (Avoid: Adding the campaign report to the ad report.)

### How each platform delivers cost and day

Convert in each platform's branch, before joining.

- **Meta Ads · adsinsights** → Cost in account currency, as text (`spend → number; date_start (text, account time zone)`)
- **Google Ads · campaign_performance** → Cost in micros (`metrics_cost_micros ÷ 1,000,000; date`)
- **LinkedIn Ads · campaign_analytics** → Cost in local currency, as text (`costInLocalCurrency → number; date`)
- **TikTok Ads · daily_report** → Cost in account currency (`spend; stat_time_day`)
- **GA4** → Date as YYYYMMDD text (`date → date`)
- **Each platform's accounts table** → Account currency, time zone and status (`currency, timezone_name`)

### Definitions

| Metric | Definition |
|---|---|
| Spend | sum of cost converted to currency (Google ÷ 1,000,000), by client, platform and account day |
| Impressions and clicks | sum from the daily performance reports; on Meta, link clicks come from link_click |
| CTR | sum of clicks / sum of impressions in the period |
| CPC | sum of spend / sum of clicks in the period |
| CPM | sum of spend / sum of impressions × 1,000 in the period |
| CPA and ROAS | spend / conversions and conversion value / spend, using the period sums |
| Mapping coverage | spend with a resolved client_id / total spend, per day |
| Client with active media | client with spend above zero in the last 7 days on at least one account active in the registry |

### Pitfalls

- **Factor of a million.** Standardizing the Google and Meta cost columns the same way makes one of them wrong by a million. In the total it jumps out; in CPA and ROAS the micros can cancel out and slip through. How to avoid: Convert in each branch and check one day's total against each ad manager.
- **Spend on the wrong day.** Google's date arrives as a midnight timestamp. Converting that value to the São Paulo time zone moves everything to the previous day, and in a freshness monitor this made the source look late. How to avoid: Treat the platform date as a day, with no time zone conversion.
- **A UNION that breaks on its own.** UNION ALL matches by position. One account's connector gaining a column was enough to stop the whole portfolio table. How to avoid: List the columns in each branch and keep the output schema identical.
- **Double counting with an old source.** Retired sources stayed frozen next to the consolidated one, and whoever added the two doubled those clients' spend. How to avoid: Record the active source and the validity period of each account.
- **Matching by name.** The client name differs across media, CRM, finance and the control spreadsheet, and matching by name failed on most rows. Accounts with similar names are distinct units. How to avoid: Match by account id or company document, with an alias table only as a hint.
- **A client that leaves the portfolio.** Deleting the data of a client who left without removing their branch broke the portfolio query. Leaving the branch makes the alert flag a delay forever. How to avoid: Mark the status in the registry with an end date and remove the branch without changing the output schema.

Validated on real data: In Nekt's own workspace, the Meta accounts table lists accounts in three currencies (real, dollar and euro) and three time zones, but only one of them has performance rows: the accounts list does not define the universe. In the same workspace, Meta's spend, impressions and clicks arrive as text, Google's cost comes in micros, LinkedIn's costInLocalCurrency comes as text, and Google's and LinkedIn's dates arrive as midnight timestamps.

### Acceptance test

- [ ] For one client and one closed month, each platform's spend matches the corresponding ad manager (difference below 1%).
- [ ] Spend for a chosen day matches the ad manager on the same day, on each platform (proof that the day was not shifted).
- [ ] Total spend in the unified table equals the sum of the sources active in the registry, already converted, with no frozen source.
- [ ] No row lacks a client_id outside the UNMAPPED_ACCOUNT status, and the list of unmapped accounts is reviewed every week.
- [ ] The key platform, account, campaign, day (and ad, when present) is never repeated.

### Context document

```text
Paid media for the portfolio
- The client of each row comes from the account-to-client registry (platform + account_id). Never infer the client from the table, account or campaign name.
- Another client's data is not evidence for this client, even when tables and metrics have similar names.
- Spend is already in currency: Google was divided by 1,000,000; Meta and LinkedIn were converted from text to numbers.
- Never add up spend in different currencies. If you need to consolidate, state the exchange rate used or that the account was left out.
- The date is the account's day on the platform. Do not convert time zones.
- CTR, CPC, CPM, CPA and ROAS are recalculated from the period sums, never as an average of daily rates.
- Accounts with status UNMAPPED_ACCOUNT belong to no client: mention them as a pending item, do not attribute them.
- Frozen or retired sources are not included in totals.
- For results by objective on Meta, follow the rules of the Meta Ads results by objective case.
Per-client exceptions:
- <client>: account shared with the in-house team; only campaigns with <term> in the name count.
- <client>: account <id> operates in <currency>; it stays out of the portfolio total.
- <client>: Meta cost reported with a <x>% tax on the media budget.
```

### Variations

- **Per-client access:** The same registry defines which team members or client users see each account in the dashboard.
- **Microsoft Ads and others:** A new branch with the same column list: the portfolio table does not change.
- **Group with several brands:** The registry maps account to brand and brand to group, with the same isolation rule.