Automate Customer Acquisition Cost (CAC) — Monthly Auto-Report in n8n — Step by Step
Your marketing team spends money every month. Your sales team closes new customers every month. But the single number that tells you whether those two facts add up to a healthy business — your Custome
Your marketing team spends money every month. Your sales team closes new customers every month. But the single number that tells you whether those two facts add up to a healthy business — your Customer Acquisition Cost — probably lives in a spreadsheet someone updates when they remember to. That's the gap this n8n workflow closes: on the 5th of every month, it pulls your new customer count from HubSpot, your total marketing spend from Google Sheets, computes CAC, estimated LTV, and the LTV:CAC ratio, and drops a clean summary into Slack. No dashboard to open, no analyst to ping, no month where the number quietly gets skipped.
The problem: CAC is the metric everyone agrees matters and nobody maintains
CAC is deceptively simple — total acquisition spend divided by new customers acquired — but the inputs are scattered across systems that don't talk to each other. Spend sits in ad platforms and a finance spreadsheet. New customers live in your CRM. LTV requires assumptions about retention and margin that live in someone's head. So the calculation happens sporadically: during board prep, during a fundraise, during a panic when burn looks high.
That irregularity is expensive. A LTV:CAC ratio below 1 means you are losing money on every customer, and the longer it takes to notice, the deeper the hole. The healthy benchmark for most SaaS and subscription businesses is a ratio of 3:1 or higher, with a CAC payback period under 12 months. If your ratio drifted from 4:1 to 1.8:1 over a quarter because a new paid channel underperformed, you want to know on the 5th of the next month — not at the quarterly review. Manual reporting guarantees you find out late. Automation guarantees you find out on schedule, every time, with zero human dependency.
The solution: one workflow, three data sources, one Slack message
The architecture is intentionally lean. A scheduled trigger fires monthly. Two parallel branches fetch the raw numbers — new customer count from HubSpot, marketing spend from a Google Sheet your finance or ops team already maintains. A merge step combines them, a code node runs the math, and a Slack node formats and posts the result to your #metrics or #leadership channel.
Keeping marketing spend in Google Sheets is a deliberate choice, not a limitation. Spend data is messy — it spans Google Ads, Meta, LinkedIn, sponsorships, agency retainers, and salaries you may or may not want to allocate. A spreadsheet lets a human own that allocation logic while the workflow owns the calculation and delivery. You get the reliability of automation without pretending that ad-spend attribution is a solved problem.
Step-by-step setup in n8n
1. Schedule Trigger node. Add a Schedule Trigger and set it to Cron mode with the expression 0 8 5 * * — that fires at 08:00 on the 5th of every month. The 5th matters: it gives your CRM time to settle late-attributed signups and your finance team time to close out the prior month's spend. Reporting on the 1st means reporting on incomplete data.
2. HubSpot node — count new customers. Add a HubSpot node authenticated with an OAuth2 or private-app token. Use the Contact → Get Many (or Deal → Get Many if you count closed-won deals as customers) operation with a filter on createdate (or closedate) between the first and last day of the previous month. Set Return All to true so pagination doesn't silently cap you at 100 records. If you'd rather push the counting to HubSpot, use the Search API with a filterGroups date range and read the total field directly — that's cheaper and avoids pulling every contact record.
3. Google Sheets node — read spend. Add a Google Sheets node with the Read Rows operation, pointed at a sheet with two columns: month and total_spend. Have your team enter one row per month. In the node, filter for the previous month's row so you always read the right value. Keep the sheet dead simple — the moment it grows complex formulas, someone breaks it and your report goes silent.
4. Merge node. Add a Merge node in Combine → Combine by Position mode to bring the HubSpot count and the Sheets spend into a single item, so the next node sees both values on one incoming payload.
5. Code node — the math. Add a Code node (JavaScript) to compute the three metrics. LTV is estimated from your average revenue per account and gross margin, with churn baked in as an assumption you can tune:
const customers = $input.first().json.newCustomers;
const spend = $input.first().json.totalSpend;
// Tunable business assumptions
const ARPA = 90; // avg monthly revenue per account
const grossMargin = 0.80; // 80%
const avgLifespanMonths = 24;
const cac = spend / customers;
const ltv = ARPA * grossMargin * avgLifespanMonths;
const ratio = ltv / cac;
return [{ json: {
customers,
spend,
cac: Math.round(cac),
ltv: Math.round(ltv),
ratio: ratio.toFixed(2),
health: ratio >= 3 ? '✅ Healthy' : ratio >= 1 ? '⚠️ Watch' : '🚨 Losing money'
}}];Expose ARPA, grossMargin, and avgLifespanMonths at the top so anyone can adjust them without touching logic. If you have real cohort retention data, replace the fixed lifespan with 1 / monthlyChurnRate for a sharper LTV.
6. Slack node. Add a Slack node with the Send Message operation. Post a formatted block referencing the code node's output:
📊 *CAC Monthly Report — {{ previous month }}*
New customers: {{ $json.customers }}
Marketing spend: ${{ $json.spend }}
*CAC: ${{ $json.cac }}*
Estimated LTV: ${{ $json.ltv }}
*LTV:CAC ratio: {{ $json.ratio }}:1* {{ $json.health }}Save, activate the workflow, and test with the Execute Workflow button before you trust the schedule — verify the Slack message renders and the numbers match a manual calculation for last month.
The benefits: a metric that maintains itself
Once this is live, three things change. First, CAC stops being a fire-drill number and becomes a heartbeat — it shows up the same day every month whether or not anyone asks. Second, the LTV:CAC ratio with its health flag turns a raw number into a decision: green means keep spending, yellow means investigate, red means stop and diagnose. Third, because the workflow runs unattended, the marginal cost of the report is zero, which means you'll actually have twelve months of consistent history a year from now instead of four scattered snapshots.
The compounding value is trend visibility. A single CAC reading tells you little; twelve consecutive readings tell you whether your acquisition is getting more or less efficient as you scale — the single most important thing to know before you pour more money into a channel.
Common pitfalls to avoid
Undercounting spend. The most common way CAC lies is by omitting costs. If your Google Sheet only captures ad spend and ignores salaries, tools, and agency fees, your CAC will look artificially healthy. Decide upfront whether you're reporting blended CAC (all spend) or paid CAC (ad spend only) and label the report accordingly — mixing them month to month makes the trend meaningless.
Off-by-one date ranges. Running on the 5th but accidentally filtering the current month gives you five days of data and a wildly inflated CAC. Always anchor both the HubSpot filter and the Sheets lookup to the previous full month, and test the boundary at month-end.
Division by zero. In a slow month with zero new customers, spend / 0 returns Infinity and your Slack message shows nonsense. Add a guard in the code node: if customers === 0, post an alert flagging zero acquisition rather than a broken number.
Silent failures. An expired HubSpot token or a renamed Sheet tab makes the workflow fail quietly, and a report that stops arriving is easy to not notice. Turn on n8n's Error Trigger workflow to post to the same Slack channel when an execution fails — so a missing report is as visible as a bad one.
Treating estimated LTV as gospel. Your LTV is only as good as your retention and margin assumptions. Revisit them quarterly, and when you present the ratio to leadership, be explicit that LTV is a modeled estimate — the CAC number is hard, the LTV number is a well-reasoned guess.
Ja construimos isso pra voce
Nao comece do zero. O Customer Acquisition Cost (CAC) — Monthly Auto-Report e um workflow n8n pronto para instalar — conecta suas ferramentas em minutos, sem codigo.
Instalar por $49 →
