Meta, Google, CRM, accounts payable and media plan
CRM lead, platform cost and project from the campaign name
CPL, CAC and ROI by channel and project
Launch dashboard, AI agent and budget alert
What it solves
- CPL uses the leads that actually entered the CRM, not the number the platform reports.
- Every lead, visit and sale is tied to a channel and a project, including listing portals, which have no cost API.
- Sales and rentals, or each launch, have their own CPL, CAC and ROI, without mixing accounts.
Business questions it answers
Ask your agent these questions once the case is live.
What you need
- Meta Ads and Google Ads connected, with every account (sales, rentals, each brand)
- The CRM connected, with the lead source field filled in
- The list of projects with the snippet that identifies each one in the campaign name, and the month's media plan
What you get
- A daily cost table by account, channel, campaign and project
- A table per CRM lead with channel, project, visit and sale
- Monthly CPL, CAC and ROI by channel and launch
- A media plan vs. actuals alert for the current month
Where to activate
Leads, visits and sales by project and channel, with CPL and CAC next to the media plan target.
Answers 'how many leads did launch X get' with the CRM rule and the list of campaigns in 'other'.
Warns when projected spend exhausts the budget before the end of the month or when projected leads fall below target.
Cost and leads come out fast. The work is in the mapping from CRM source to channel and from campaign to project, which has to be agreed with marketing. The detailed view has the business rules, the pitfalls and the acceptance test. Or send everything straight to your agent with the Nekt MCP.
Meta and Google count conversions their own way, and the CRM counts leads another way. Real estate listing portals bring many leads and have no cost API, so they vanish from CPL. Dividing platform cost by platform leads delivers a CPL that does not match sales, and without tying campaigns to projects nobody knows which launch is expensive.
How it is usually done, and the rule
Each row is a mistake that already happened in practice.
spend by the leads reported by Meta and Google.The number of leads is counted in the CRM, from the contact's or deal's source field (for example, media_source in Imobzi, source_id in Bitrix24, the source in Vista or CV CRM). Meta and Google contribute only spend, impressions and clicks. In a real case, the leads read from the platforms' APIs came in as zero or inconsistent, and CPL only matched sales once the count moved to the CRM.
Active prospecting sources (cold calls, marketing WhatsApp blasts, SDR) and sources that are not customers (broker job openings, 'Nenhum', '-') stay out of media CPL. They have their own cost, such as lists, events and tools, and can have a separate cost per lead. In a real case, outbound had twice the volume of inbound and cut CPL in half.
Keep a single table that maps each CRM source and each marketing automation traffic_source to a channel: Meta, Google, Portals, Organic site, Referral, Offline or Outbound. A new source falls into 'Unmapped' and shows up in a report until someone classifies it. Generate each source's id from a hash of the normalized name: ids derived from text length collided ('Live' and 'Site' got the same id).
Real estate listing portals, website ads, email marketing, signs, billboards and events have no cost API. Read the entry in accounts payable by subcategory and declare whether you use the payment date or the due date. Pick one cost source per channel: if Meta and Google are also in finance, do not add them to the API cost.
Normalize the campaign name (lowercase, no accents) and look for the snippets in a closed list per project. A short snippet that appears inside other words is matched as a whole word. A campaign with no recognized snippet goes to 'other', is not counted per project and is listed so someone fixes the name or the list. The project's own brand campaigns count toward it. In the CRM, the project comes from the canonical field, never from the deal name.
Sales and rentals, or different brands, usually have separate ad accounts on each platform, and sometimes separate forms and marketing automations. Union the accounts in one table with account and business line columns. Sales and rental CPL never mix: the ticket, the cycle and the acceptable cost are different.
To compare with the CRM, count the Meta lead by the ad set's destination_type. In ON_AD, the ad opens a form and never leads to the website: the lead is the form lead, and a pixel lead there is the same lead counted again. In other destinations, the website pixel counts. Messaging campaigns count conversations started, in their own column. Before summing cost, keep one value per ad, ad set and day.
metrics_conversions counts primary conversions by click date, fractioned by attribution. metrics_conversions_by_conversion_date counts by the date the conversion happened. To compare with leads created in the CRM in the month, use the conversion date; to reproduce the ads manager, use the click date. Cost comes in micros: divide metrics_cost_micros by 1,000,000. conversions includes every action marked as primary, so confirm that only lead is marked.
A buyer usually generates several leads before closing. For media VGV, tie each sale to the contact's most recent lead created before the sale date. Also keep the first lead for the first-touch view, but the dashboard uses only one, declared. Each sale has at most one attributed lead.
ROI = (revenue - spend) / spend x 100. In real estate the sale usually lands months after the lead, so a month with cost and no sale shows a neutral ROI with the note 'no sale in the period', not -100%. Cap ROI at a ceiling (for example, 9,999%) so a near-zero-cost channel does not distort the chart. Declare whether revenue is VGV or commission.
Source-to-channel mapping
CRM source, marketing automation traffic_source and GA4.
Definitions
spend in Meta, metrics_cost_micros / 1,000,000 in Google, bills in the subcategory in accounts payable for portals and offlinespend / channel leads, in the same period and the same business line (sales or rentals)spend / channel leads with a completed visit in the periodspend in the month / sales attributed to the channel in the month, by official sale datespend) / spend x 100, with declared revenue (VGV or commission), neutral and flagged when there is no sale, with a declared ceilingspend and leads / net budget and lead target in the plan, with a linear projection to the last dayPitfalls that already happened
Leads came from the Meta and Google APIs and arrived as zero on many days, and the dashboard showed an empty CPL.
Count leads in the CRM and use the platforms only for cost.
In GA4, Meta uses medium 'paid', not 'cpc'. The largest paid channel showed up as 'Other', and l.instagram.com links as 'Referral'.
Explicit rule: medium 'paid' with a social source is paid social; Instagram and Facebook subdomains are social.
The CRM integration sent status change events, such as deal won, to the marketing automation, and the channel funnel counted those sales as conversions.
Exclude CRM status events from the conversion count by channel.
After the marketing automation connection was replaced, the old table stopped updating and the channel funnel stayed frozen for weeks without anyone noticing.
Point to the current source and monitor each table's most recent date.
Meta form test leads, test emails and the agency's internal contacts entered the count and the audience sent back to the platforms.
Exclude test leads, emails containing 'teste' or 'test' and internal domains before counting.
A campaign with a non-standard name fell into 'other', and the project showed zero leads in the launch meeting.
Always publish the list of campaigns with results that fell into 'other'.
Acceptance test
The case is done when every item passes.
- For a closed month, the sum of leads by channel equals the CRM's total inbound leads for the month, and every lead without a channel shows up as 'Unmapped'.
- Meta and Google spend by account and month matches each platform's ads manager (difference below 1%).
- Leads by project plus 'other' add up to the total media leads for the period, and the list of campaigns in 'other' is published.
- No outbound source appears in media CPL.
- Each sale has at most one attributed lead, and media VGV does not exceed total VGV for the period.
Agent package
Paste into Claude, ChatGPT or any client with the Nekt MCP connected.
Media through to sale (real estate) - Leads are counted in the CRM, from the contact's or deal's source field. Meta and Google contribute only cost, impressions and clicks. - CPL = channel spend / channel leads in the CRM, in the same period and the same business line (sales or rentals). - Outbound sources (cold calls, marketing WhatsApp, SDR) and non-customers (broker job openings, 'Nenhum', '-') stay out of media CPL. - Each source's channel comes from the mapping table. A source with no match is 'Unmapped': say so, do not guess the channel. - Cost of listing portals and offline media comes from accounts payable, by subcategory. One cost source per channel. - Campaign project: normalized name (lowercase, no accents) and a closed list of snippets. No snippet = 'other'. Always list the campaigns with results in 'other'. - Meta: in an ad set with destination_type = ON_AD, lead = form; elsewhere, the website pixel. Conversations go in their own column. - Google: cost = metrics_cost_micros / 1,000,000. Conversions by conversion date to compare with the CRM. - A sale is attributed to the contact's most recent lead before the sale date. - ROI with no sale in the period is neutral and flagged, never -100%. - CRM status events sent to the marketing automation are not conversions. Client-specific exceptions: - <client>: ad accounts <conta_venda> and <conta_locacao>; never add the CPL of the two. - <client>: campaign snippets per project: <snippet> = <project>. - <client>: ROI revenue = <VGV or commission>.
Build the "Media through to sale by project" case in my Nekt workspace following the rules at https://academy.nekt.com/en/industrias/imobiliario/midia-ate-venda-empreendimento.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
Send the lead's real CRM stage (visit, proposal, won) to Meta and Google, within Meta's 7-day window, matching on normalized email and phone.
Leads with a visit or a win become lookalike seeds; audiences that are too small, like rental ones, serve only for remarketing and exclusion.
Lead, qualified, meeting and won by day for paid sources only, in each pipeline, without duplicates or tests.