---
id: varejo/midia-paga-unificada
industry: Retail and e-commerce
usage: widely used
effort: medium
grain: Platform x account x campaign x day (and product x day to match against sales)
---

# Unified paid media

Google, Meta, TikTok and marketplace ads in one table, with ROAS measured against the sales the store and the ERP recorded, not against what each platform claims it sold.

## Executive summary

**For:** Retailers and e-commerce businesses that invest in more than one media platform and need to know how much of it came back as real sales.

**What it solves**

- Media return is measured against invoiced sales from the store or the ERP, not against the sum of what each platform attributes to itself.
- Map directions, estimated store visits and WhatsApp conversations stop being counted as leads or sales.
- Spend shows up by product, store and state, so you can see budget spent where the chain does not sell.

**Why it matters:** In retail, conversion happens at the store checkout, in the website order or in a WhatsApp conversation, and no platform sees all of it. Adding up Google's and Meta's conversion value counts the same sale twice, and comparing a Google CPL full of direction requests with a Meta CPL that counts conversations moves budget to the wrong channel. It is the base for order margin, the salesperson funnel and the per-channel target.

**Business questions it answers**

- How much did we spend on media this month and what was the ROAS on the store's real sales?
- How many real contacts did Google bring, without counting direction requests and estimated visits?
- How much of the spend landed outside the states where we have stores or deliver?
- Which product gets the most media, and which returns the most sales per real spent?
- Is Mercado Livre advertising paying for itself, looking at the channel's total revenue?
- Are the conversations Meta reports actually reaching the store's WhatsApp?

**What you need**

- Media platforms and marketplace ads connected to Nekt, all accounts
- Online store orders or ERP sales connected, with status and date
- The campaign naming convention (product, store, channel) and the list of expected accounts

**What you get**

- A table by platform, account, campaign and day with spend, contacts and labeled platform value
- Real ROAS and TACOS by channel, product and month, against invoiced sales
- Breakdowns by state, age range and search term to review where the budget goes
- An AI agent that tells real contacts, estimated conversions and sales apart

## Detailed

### The problem

Each platform measures return its own way. In Google, the conversions column of a physical store is dominated by direction requests and estimated visits. In Meta, without a purchase pixel, the conversion is a started conversation and the value comes back empty. Marketplace ads attribute to themselves part of the sales the channel already reports. Whoever adds it all up delivers a ROAS that does not exist, and whoever only looks at the ad manager does not know how much invoiced revenue the media brought.

### Business rules

1. **Real ROAS uses the store's sales.** Real ROAS = net revenue of valid orders from the online store or the ERP (without tax, without shipping charged and without canceled orders) / spend, in the same scope and period. TACOS = spend / total revenue of the scope, organic included. Platform ROAS (conversion value / spend) only compares campaigns and creatives within the same platform and is never added across platforms, because each one credits the same sale to itself. (Avoid: Adding Google's and Meta's conversion value and calling it media revenue.)
2. **Physical store conversions are split by type.** In Google, read conversions from the conversion actions by campaign table (conversion_action_category). GET_DIRECTIONS (asked for map directions) and STORE_VISIT (visit estimated by Google itself) are top of funnel and never become leads. PHONE_CALL_LEAD, CONTACT and SUBMIT_LEAD_FORM are the closest to a contact; PURCHASE is a website purchase. Keep the category in a column and compute cost per contact only with contact categories. (Avoid: Using metrics_conversions of a physical store account as the lead total.)
3. **Without a purchase pixel, Meta's conversion is a conversation.** When the account has no pixel or conversions API with a purchase event, each campaign's result is onsite_conversion.messaging_conversation_started_7d (conversation started on WhatsApp, Messenger or Direct). A conversation is the start of contact, not a sale. The conversion value of those campaigns is null, and so is platform ROAS. (Avoid: Treating a started conversation as a sale or computing Meta ROAS with a zero value.)
4. **Null means not measured, and each metric says what it is.** A metric the platform does not provide stays null, never zero, and the agent answers that it is not tracked. Next to each platform number, keep a type label: a platform conversion is not an order, conversion value is not confirmed revenue, daily reach does not add up across days or ads. The same applies to coverage: a day with no rows in an active account is missing data until proven otherwise. (Avoid: Filling with zero what the platform does not measure and adding reach across days.)
5. **Marketplace ads are a subset of channel sales.** In Mercado Livre Product Ads, total_amount is the revenue attributed to the sponsored listing (direct and indirect) and is already inside the marketplace's revenue. The cost (cost) goes into the channel's spend; attributed revenue is only used for ACOS (spend / attributed revenue). To know whether ads pay off, use the channel's TACOS: ad spend / total marketplace revenue. (Avoid: Adding Product Ads revenue to Mercado Livre revenue.)
6. **Spend reaches the product through the campaign name.** Link each campaign to a product, store or site through a documented naming convention (for example, the first segment before the separator is the product code) and an alias table maintained by marketing (short code to product name). When the name does not carry the store, use the account as the default. Brand or whole-store campaigns stay as Store, with no invented allocation. Whatever does not match goes to Unmapped with its value and never disappears. (Avoid: Allocating all spend by each product's share of sales.)
7. **Budget checks against the store map.** Build spend by state with Google's location report (user_location_performance and geo_target_constant, dropping the State of prefix) and Meta's region breakdown (adsinsights_by_region), with names normalized so both match. An empty region becomes Not specified, because it is part of the key. Compare with the states where the chain has stores or delivers and show spend outside them. (Avoid: Looking only at the campaign total, without knowing where the budget was shown.)
8. **Google age and gender are not crossed.** Google delivers age range and gender in separate reports (age_range_performance and gender_performance). Meta delivers both together per row (adsinsights_by_age_and_gender). In the common schema, a Google row has one of the two dimensions filled and the other marked N/A. Sum by age ignoring age N/A, and by gender ignoring gender N/A. Never show age x gender for Google. (Avoid: Crossing Google age and gender as if they came together.)
9. **All accounts, one source per period.** Keep a table of expected accounts per platform. Live connectors sometimes cover only part of the accounts, and history before the connection may come from a frozen export of the previous tool. Each account and each period comes from one source only, with a declared cutoff date, and no account appears in both. (Avoid: Adding the old export to the new connector, or ignoring accounts that were not connected.)
10. **Affiliate commission is spend per order.** Affiliates and partners paid per sale (a fixed amount per order or a percentage of revenue) have no ad manager. Their cost is computed on the channel's order rows and enters spend the same way as platform cost. Amounts, percentages and channel reclassification dates live in a parameter table with start and end dates, not as literals in the code. (Avoid: Leaving affiliates at zero cost or changing the percentage over history.)

### What each source delivers in retail

How to treat each conversion before comparing it with sales.

- **Google · GET_DIRECTIONS, STORE_VISIT** → Estimated top of funnel, never a lead (`conversion_action_category`)
- **Google · PHONE_CALL_LEAD, CONTACT, SUBMIT_LEAD_FORM** → Contact (`conversion_action_category`)
- **Google · PURCHASE** → Website purchase, platform value (`metrics_conversions_value`)
- **Meta without a purchase pixel** → Conversation started, null value (`onsite_conversion.messaging_conversation_started_7d`)
- **Meta with pixel or conversions API** → Purchase, platform value (`offsite_conversion.fb_pixel_purchase`)
- **Mercado Livre Product Ads** → Attributed revenue, subset of the channel (`cost, total_amount`)
- **CRM · WhatsApp source** → Real contact, minimum count (`SOURCE_ID`)
- **Online store or ERP · valid order** → Real revenue for ROAS (`net, without tax and shipping`)
- **Affiliate or per-sale partner** → Spend computed per order

### Definitions

| Metric | Definition |
|---|---|
| Spend | sum of cost in reais by platform, account, campaign and day, including marketplace ads and per-order affiliate commission in their own rows |
| Real revenue | net sales of valid orders from the online store or the ERP, without tax, without shipping charged and without canceled orders, by order date, channel and day |
| Real ROAS | sum of real revenue / sum of spend in the same scope and period, recomputed for each slice |
| TACOS | spend / total revenue of the scope (paid and organic) x 100 |
| ACOS | ad spend / revenue attributed by the ads platform x 100, within the platform's own attribution window |
| Platform ROAS | conversion value / spend within one platform; null when the platform does not measure value |
| Cost per contact | spend of contact campaigns / (conversations started + calls + contacts + forms) in the period |
| Spend outside the area | spend in states with no store and no delivery / total platform spend in the period |

### Pitfalls

- **Google with a fake CPL.** In a store chain, most Google conversions were direction requests and estimated visits. Counted as leads, they put Google's CPL far below Meta's cost per conversation. How to avoid: Split conversions by category and compare only contact with contact.
- **Meta and WhatsApp told different stories.** The conversations reported by Meta reached more than three times the contacts that actually came into WhatsApp from the ad. Months later, Meta almost stopped reporting while real contacts went up. How to avoid: Measure real contacts by the CRM source or by the ad's pre-filled message and compare with Meta every month.
- **Meta with no return.** The account had no purchase pixel, the null conversion value became zero and the dashboard showed zero ROAS for Meta. How to avoid: Keep it null and answer that Meta's return is not tracked on the platform; use real ROAS.
- **Marketing undercounted for the year.** The connectors covered only part of one platform's accounts and had no history for another. Website spend was 15% to 36% below reality, and ROAS looked great. How to avoid: Register expected accounts and complete the past with the previous tool's export, with a date cutoff.
- **Marketplace sales inflated by ads.** Product Ads attributed revenue was added to Mercado Livre revenue and the channel grew on the dashboard without selling more. How to avoid: Treat ads revenue as a subset and use only its cost in spend.
- **New campaign without a product.** Campaigns named outside the convention fell into the generic category and a new product's spend disappeared from the ranking for weeks. How to avoid: Alert when Unmapped or the generic category grows above normal.

### Acceptance test

- [ ] For each platform and account, spend for a closed month matches the ad manager and the marketplace ads panel (difference below 1%).
- [ ] The sum of spend by product, plus Store and Unmapped, equals total spend for the month.
- [ ] The real revenue used in ROAS equals net sales for the month in the store's or ERP's sales report, for the same scope.
- [ ] No row from an account without a purchase pixel has a zero conversion value, and no GET_DIRECTIONS or STORE_VISIT conversion goes into cost per contact.
- [ ] Every account in the expected accounts registry appears with spend in the month or is marked as paused.

### Context document

```text
Unified paid media (retail)
- Grain: platform x account x campaign x day. Spend in reais, already converted from each platform.
- Real ROAS = net revenue of valid store or ERP orders (without tax, shipping and canceled orders) / spend. It is the default ROAS in every answer.
- Platform ROAS = conversion value / spend, only to compare within the same platform. Never add conversion value across platforms.
- TACOS = spend / total revenue of the scope. ACOS = spend / revenue attributed by ads.
- Google: split conversions by conversion_action_category. GET_DIRECTIONS and STORE_VISIT are not leads. Contact = PHONE_CALL_LEAD, CONTACT, SUBMIT_LEAD_FORM.
- Meta without a purchase pixel: conversion = conversation started; value and platform ROAS are null. Say "not tracked", never zero.
- Mercado Livre Product Ads: attributed revenue is already inside the channel's revenue. Do not add it.
- Spend by product and store comes from the campaign naming convention and the alias table. Unmapped is shown, never dropped.
- Google does not cross age with gender. Reach does not add up across days or ads.
- Real WhatsApp contacts come from the CRM source and are a floor, not the total.
- Before answering zero, check account and day coverage.
Client exceptions:
- <client>: Meta account without a purchase pixel; result = conversation started.
- <client>: product code = first segment of the campaign name before "|"; aliases in table <alias table>.
- <client>: affiliate <partner> costs <amount> per order since <date>.
- <client>: stores and delivery only in <states>.
```

### Variations

- **Search terms:** Terms with spend and no conversions become negative keyword candidates. Google omits low-volume terms, so the sum of terms stays below campaign cost.
- **Organic in the same model:** Instagram, TikTok, Facebook and Search Console go into their own columns, with reach and engagement labeled, never mixed with spend.
- **Creatives with images:** Image URLs from Meta's CDN expire and do not open in the browser. Store the converted image or the landing page's og:image for the creative ranking.