---
id: imobiliario/midia-ate-venda-empreendimento
industry: Real estate
usage: widely used
effort: medium
grain: Day x account x campaign for cost; CRM lead with channel and project; month x channel for CAC and ROI
---

# Media through to sale by project

What each lead, visit and sale cost by channel and by launch, with leads counted in the CRM and cost coming from the platforms and from finance.

## Executive summary

**For:** Brokerages and developers that invest in Meta, Google and listing portals and need to know which channels and launches bring sales.

**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.

**Why it matters:** Launch budgets are high and concentrated in a few weeks. If CPL comes from the platform, it does not match what the brokers receive and leadership cuts the wrong channel. Tying cost to CRM leads and to sales is what lets you say how much VGV (gross sales value) each real of media brought in.

**Business questions it answers**

- What did each lead, visit and sale cost by channel this month?
- Which project has a CPL above what the media plan expected?
- How much VGV came from paid media leads this quarter?
- Do listing portals bring cheaper leads than Meta and Google?
- Will any channel run out of budget before the end of the month?
- How many leads did launch X get yesterday on each platform?

**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

## Detailed

### The problem

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.

### Business rules

1. **Leads come from the CRM, cost comes from the platform.** 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. (Avoid: Dividing spend by the leads reported by Meta and Google.)
2. **Media CPL only with inbound sources.** 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. (Avoid: Every lead created in the month in the CPL denominator.)
3. **One source-to-channel mapping.** 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). (Avoid: Classifying source with a CASE scattered across every query.)
4. **Portals and offline through accounts payable.** 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. (Avoid: Adding Meta from the API to Meta booked in finance.)
5. **Project from a closed list of snippets.** 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. (Avoid: A loose LIKE on the project name and unmatched campaigns silently disappearing.)
6. **One account per business line, combined with origin.** 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. (Avoid: A single CPL mixing sales and rentals.)
7. **In Meta, the ad set destination picks the lead.** 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. (Avoid: Summing form, pixel and conversation as leads.)
8. **In Google, declare the conversion date.** 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. (Avoid: Mixing the two date bases across months or reports.)
9. **Sale tied to the last lead before it.** 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. (Avoid: Counting the sale on every lead of the contact.)
10. **A month with no sale is not a 100% loss.** 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. (Avoid: ROI of -100% in every month without a sale.)

### Source-to-channel mapping

CRM source, marketing automation traffic_source and GA4.

- **traffic_source with the encoded_ prefix** → Decode the base64 before classifying (`encoded_<base64>`)
- **gclid or gad_ in traffic_source** → Google (`gclid|gad_`)
- **facebook, instagram, paid_social** → Meta (`facebook|instagram|paid_social`)
- **GA4: medium 'paid' with a social source** → Paid social, not 'Other' (`sessionmedium = 'paid'`)
- **GA4: l.instagram.com, facebook.com** → Organic social, not 'Referral' (`sessionsource`)
- **Real estate listing portal, website ad** → Portals, cost from accounts payable (`payable subcategory`)
- **Cold call, marketing WhatsApp, SDR** → Outbound, out of media CPL
- **Broker job opening, 'Nenhum', '-'** → Out of every lead count
- **CRM status event sent to the automation** → Not a media conversion (`e.g.: deal won`)
- **Source with no match in the table** → Unmapped, listed for classification

### Definitions

| Metric | Definition |
|---|---|
| Spend | sum of the channel's cost in the period: spend in Meta, metrics_cost_micros / 1,000,000 in Google, bills in the subcategory in accounts payable for portals and offline |
| Leads | distinct contacts or deals created in the CRM in the period with an inbound source mapped to the channel, excluding tests, duplicates and internal emails |
| CPL | channel spend / channel leads, in the same period and the same business line (sales or rentals) |
| Cost per visit | channel spend / channel leads with a completed visit in the period |
| CAC | channel spend in the month / sales attributed to the channel in the month, by official sale date |
| Media VGV | sum of the sale value of sales whose attributed lead (the last before the sale) is from a paid channel |
| ROI | (revenue - spend) / spend x 100, with declared revenue (VGV or commission), neutral and flagged when there is no sale, with a declared ceiling |
| Month plan vs. actuals | month-to-date spend and leads / net budget and lead target in the plan, with a linear projection to the last day |

### Pitfalls

- **Zero or infinite CPL.** Leads came from the Meta and Google APIs and arrived as zero on many days, and the dashboard showed an empty CPL. How to avoid: Count leads in the CRM and use the platforms only for cost.
- **Paid Meta landing in 'Other'.** In GA4, Meta uses medium 'paid', not 'cpc'. The largest paid channel showed up as 'Other', and l.instagram.com links as 'Referral'. How to avoid: Explicit rule: medium 'paid' with a social source is paid social; Instagram and Facebook subdomains are social.
- **A sale counted as a new lead.** The CRM integration sent status change events, such as deal won, to the marketing automation, and the channel funnel counted those sales as conversions. How to avoid: Exclude CRM status events from the conversion count by channel.
- **Funnel frozen on a date.** After the marketing automation connection was replaced, the old table stopped updating and the channel funnel stayed frozen for weeks without anyone noticing. How to avoid: Point to the current source and monitor each table's most recent date.
- **Test leads in the total.** Meta form test leads, test emails and the agency's internal contacts entered the count and the audience sent back to the platforms. How to avoid: Exclude test leads, emails containing 'teste' or 'test' and internal domains before counting.
- **A launch with no leads.** A campaign with a non-standard name fell into 'other', and the project showed zero leads in the launch meeting. How to avoid: Always publish the list of campaigns with results that fell into 'other'.

Validated on real data: In Nekt's own Google Ads account, from March to September 2026, conversions by click date (metrics_conversions) were between 8% and 19% below conversions by conversion date in every month, and they come fractioned by attribution. Switching date basis changes the month's CPL in the same proportion.

### Acceptance test

- [ ] 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.

### Context document

```text
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>.
```

### Variations

- **Conversions back to the platforms:** 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.
- **Audiences by stage:** Leads with a visit or a win become lookalike seeds; audiences that are too small, like rental ones, serve only for remarketing and exclusion.
- **Paid lead funnel:** Lead, qualified, meeting and won by day for paid sources only, in each pipeline, without duplicates or tests.