Platform insights, marketplace ads and store or ERP orders
Conversion by type, unmeasured value as null, spend linked to product and store
Spend, contacts, real ROAS and TACOS by channel and product
Media dashboard, AI agent and budget alerts
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.
Business questions it answers
Ask your agent these questions once the case is live.
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
Where to activate
Spend, contacts, real ROAS and TACOS by channel, product, store and state, with the conversion type next to each number.
Answers about media return with real ROAS as the default and warns when a metric is not tracked.
Spend outside the chain's states, campaigns without a mapped product, accounts without data and gaps between Meta conversations and real contacts.
Each platform on its own is simple. The work is linking spend to product and store through the naming convention and agreeing with the team on which revenue goes into ROAS. The detailed view has the business rules, the pitfalls and the acceptance test. Or send everything straight to your agent with the Nekt MCP.
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.
How it is usually done, and the rule
Each row is a mistake that already happened in practice.
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.
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.
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.
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.
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.
spend by each product's share of sales.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.
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.
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.
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.
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.
What each source delivers in retail
How to treat each conversion before comparing it with sales.
Definitions
spend in the same scope and period, recomputed for each slicespend / total revenue of the scope (paid and organic) x 100spend / revenue attributed by the ads platform x 100, within the platform's own attribution windowspend within one platform; null when the platform does not measure valuespend of contact campaigns / (conversations started + calls + contacts + forms) in the periodspend in states with no store and no delivery / total platform spend in the periodPitfalls that already happened
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.
Split conversions by category and compare only contact with contact.
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.
Measure real contacts by the CRM source or by the ad's pre-filled message and compare with Meta every month.
The account had no purchase pixel, the null conversion value became zero and the dashboard showed zero ROAS for Meta.
Keep it null and answer that Meta's return is not tracked on the platform; use real ROAS.
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.
Register expected accounts and complete the past with the previous tool's export, with a date cutoff.
Product Ads attributed revenue was added to Mercado Livre revenue and the channel grew on the dashboard without selling more.
Treat ads revenue as a subset and use only its cost in spend.
Campaigns named outside the convention fell into the generic category and a new product's spend disappeared from the ranking for weeks.
Alert when Unmapped or the generic category grows above normal.
Acceptance test
The case is done when every item passes.
- 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.
Agent package
Paste into Claude, ChatGPT or any client with the Nekt MCP connected.
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>.
Build the "Unified paid media" case in my Nekt workspace following the rules at https://academy.nekt.com/en/industrias/varejo/midia-paga-unificada.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
Terms with spend and no conversions become negative keyword candidates. Google omits low-volume terms, so the sum of terms stays below campaign cost.
Instagram, TikTok, Facebook and Search Console go into their own columns, with reach and engagement labeled, never mixed with spend.
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.