Paid media for the whole portfolio

Source

Meta, Google, TikTok and LinkedIn, one source per account

Rule

Account-to-client registry and each platform's scale

Outcome

Media for the whole portfolio in a single table

Activation

Dashboard, report, AI agent and alerts

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.

Business questions it answers

Ask your agent these questions once the case is live.

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

Where to activate

Portfolio dashboard

Spend and results for every client side by side, with filters by client and platform.

AI agent via MCP

Answers about any client, isolating each one through the registry.

Alerts

Account without a client, stalled source and a client whose spend dropped to zero.

Ready to build?

Each platform's rules are settled. The work is in the account-to-client registry and keeping it current as clients join or leave. The detailed view has the business rules, the pitfalls and the acceptance test. Or send everything straight to your agent with the Nekt MCP.

See detailed view

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.

How it is usually done, and the rule

Each row is a mistake that already happened in practice.

The common wayThe rule

Taking the client from the table or account name.

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.

Unioning the cost columns as they arrive and converting in the dashboard.

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.

Summing spend across all accounts without checking the currency.

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.

Converting the platform date to the São Paulo time zone.

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.

SELECT * in each branch of the UNION ALL.

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.

Starting from the accounts or campaigns list and bringing in spend with a LEFT JOIN.

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.

INNER JOIN with the registry, which drops new accounts without warning.

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.

Attributing the whole account to the client.

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.

Unioning every table that exists for the account.

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.

Adding the campaign report to the ad report.

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.

How each platform delivers cost and day

Convert in each platform's branch, before joining.

Meta Ads · adsinsightsCost in account currency, as textspend → number; date_start (text, account time zone)

Google Ads · campaign_performanceCost in microsmetrics_cost_micros ÷ 1,000,000; date

LinkedIn Ads · campaign_analyticsCost in local currency, as textcostInLocalCurrency → number; date

TikTok Ads · daily_reportCost in account currencyspend; stat_time_day

GA4Date as YYYYMMDD textdate → date

Each platform's accounts tableAccount currency, time zone and statuscurrency, timezone_name

Definitions

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 that already happened

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.

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.

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.

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.

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.

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.

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

The case is done when every item passes.

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.

Agent package

Paste into Claude, ChatGPT or any client with the Nekt MCP connected.

Full Markdown

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.

Build the "Paid media for the whole portfolio" case in my Nekt workspace following the rules at https://academy.nekt.com/en/industrias/agencias/midia-paga-carteira.md. 1) Find the source tables and confirm the columns the rules use. 2) Create a transformation at the stated grain applying every rule. 3) Run the acceptance test and show me the result of each item. 4) Create the case's context document in the workspace, leaving my clients' exceptions open for me to fill in.

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.