One CAC, defined before it is counted
Ad platforms, web analytics, CRM and revenue data joined in one warehouse and snapshotted daily, so campaign performance, lead efficiency, CAC and attribution carry one written definition each.
For heads of growth, marketing directors and founders who own the ad budget.
If this sounds like your week
- Marketing reports one CAC, finance reports another, and the difference is whose spend and whose customers each side counted.
- The ad platform’s conversions keep changing for days after the fact, so last week’s numbers never match the report you already sent.
- Google Ads, Google Analytics and the CRM each show a different number of leads, and nobody can follow one lead from click to invoice.
- Attribution lives inside the platform’s own dashboard, which grades its own homework.
- Ad spend sits outside the margin calculation, so a product looks profitable in one report and loses money in another.
- You want to know which campaigns brought customers who stayed, and retention sits in a system marketing has never opened.
What gets built
- Sources
- Advertising platforms through their APIs, web analytics, CRM leads and deals, billing or order data; marketplace advertising reports where you sell through marketplaces.
- Google Ads, Google Analytics, advertising-platform APIs, REST APIs, CRM data
- Warehouse
- An analytical warehouse loaded through automated pipelines, with advertising data kept as daily snapshots: a conversion the platform restates is re-read on a fixed lag instead of quietly rewriting last month. PostgreSQL where you already run it.
- Google BigQuery, ClickHouse, PostgreSQL, Apache Airflow, SQL
- Data model
- CAC as a written definition first: which spend, which customers, which window. Then campaign performance, lead efficiency, acquisition and retention by channel and cohort; revenue attribution; CAC beside MRR, ARPU, churn and LTV where the business is subscription, and inside SKU-level margin where it sells products; month-over-month and rolling periods.
- DAX, Power Query (M), dbt, SQL
- Reporting
- Dashboards for the growth team and for finance from the same model, so the CAC in the budget review is the CAC in the campaign review.
- Power BI, Looker
Work I can describe
For a B2B SaaS company selling subscriptions to business clients, I built marketing analytics inside the BI model rather than beside it: campaign performance, lead efficiency, and customer acquisition and retention metrics in the same DAX metric layer as MRR, ARPU, churn and LTV, with revenue attribution, funnel performance and conversions in the sales analytics on top. The Power BI model combined billing, subscriptions, CRM, customer activity and marketing data, with duplicates and incorrect master data cleaned out of the source databases, and executive dashboards delivered to management.
For a seller operating on third-party marketplaces, advertising expenses were part of SKU-level profitability from the start: revenue, cost price, margin and advertising spend per SKU, on ClickHouse and PostgreSQL feeding Power BI, with data integrated from marketplace APIs, databases and advertising platforms, and ABC/XYZ analysis to find the revenue and profit drivers. The dashboards supported the daily decisions on pricing, assortment and advertising.
In my current role I designed a unified KPI layer where CAC sits beside P&L, LTV and retention with one formal definition each, removing duplicate metrics and discrepancies between departmental reports.
Clients are not named.
How we start
- I
Discovery call
Which budget decisions the reporting has to support, and how marketing and finance each count CAC today.
- II
Data audit
Ad accounts, web analytics, CRM and billing or orders: how many leads each one reports, where the conversion lag sits, which spend never reaches the margin.
- III
Model, reporting, handover
A CAC definition agreed with marketing and finance in writing; daily snapshots and the model; then the dashboards; then documentation, scheduled refresh and monitoring so your team runs it.
Send the two CACs that disagree.
Attach marketing’s number and finance’s number and say which spend each one counts. That is enough for a first call.
Email dauren.m@lief.devEmail Dauren