---
title: "Build a Startup CAC Attribution Model with PostgreSQL"
published: true
description: "A step-by-step guide to separating blended CAC from paid CAC using server-side attribution, UTM discipline, and cohort SQL queries — no enterprise tooling required."
tags: postgresql, architecture, api, performance
canonical_url: https://blog.mvpfactory.co/how-to-calculate-true-startup-cac-with-organic-traffic
---
## What We're Building
By the end of this tutorial, you'll have a working PostgreSQL attribution model that separates your blended CAC from your paid CAC — the number that actually matters for ad-spend decisions. We'll build a signups table with automatic channel classification, a cohort-level CAC query, and a decision framework you can run monthly.
Let me show you a pattern I use in every project that touches growth metrics. Most startups calculate one CAC number by dividing total marketing spend by total new customers. This "blended CAC" hides a real distortion: organic signups subsidize your paid channel economics, making paid acquisition look cheaper than it actually is.
## Prerequisites
- A PostgreSQL database (local or hosted)
- Basic SQL knowledge (CTEs, JOINs, aggregations)
- Your monthly marketing spend figures per channel
## Step 1: Understand the Distortion
Here's the minimal setup to get this working. First, internalize why blended CAC misleads you.
Say you spent $5,000 last month and acquired 500 customers. Blended CAC is $10. Looks healthy. But 350 of those customers came from organic search, direct traffic, and word-of-mouth — channels you spent $0 to acquire.
Blended CAC = $5,000 / 500 = $10.00
Paid CAC = $5,000 / 150 = $33.33
That's a **3.3x CAC distortion factor**. Here's the full picture:
| Metric | Formula | Example Value | What It Tells You |
|---|---|---|---|
| Blended CAC | Total spend / all new customers | $10.00 | Overall efficiency (misleading in isolation) |
| Paid CAC | Paid spend / paid-attributed customers | $33.33 | True cost of buying a customer |
| Organic ratio | Organic customers / total customers | 70% | How dependent you are on free channels |
| CAC distortion factor | Paid CAC / Blended CAC | 3.3x | How much organic subsidizes your numbers |
A CAC distortion factor above 2x means your blended number is functionally useless for ad-spend decisions.
## Step 2: Enforce Strict UTM Discipline
Every paid link gets tagged with `utm_source`, `utm_medium`, and `utm_campaign`. No UTM, no ad goes live. Store these parameters **server-side** at signup time — not just in analytics JavaScript that ad blockers strip out.
## Step 3: Build the Attribution Table
sql
CREATE TABLE signups (
id SERIAL PRIMARY KEY,
user_id INT NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
utm_source VARCHAR(100),
utm_medium VARCHAR(100),
utm_campaign VARCHAR(100),
referrer TEXT,
channel VARCHAR(50) GENERATED ALWAYS AS (
CASE
WHEN utm_medium IN ('cpc','paid','ppc') THEN 'paid'
WHEN utm_source IS NOT NULL THEN 'organic_referral'
WHEN referrer ILIKE '%google%' THEN 'organic_search'
ELSE 'direct'
END
) STORED
);
The `GENERATED ALWAYS AS ... STORED` column does the classification automatically. Every signup gets a channel label with zero application code.
## Step 4: Run the Cohort-Level CAC Query
This is the query that stops you from overspending:
sql
WITH monthly_signups AS (
SELECT
DATE_TRUNC('month', created_at) AS cohort_month,
channel,
COUNT(*) AS new_customers
FROM signups
GROUP BY 1, 2
),
monthly_spend AS (
SELECT month, channel, spend
FROM marketing_spend
)
SELECT
s.cohort_month,
s.channel,
s.new_customers,
COALESCE(m.spend, 0) AS spend,
CASE
WHEN s.new_customers > 0
THEN ROUND(COALESCE(m.spend, 0)::NUMERIC / s.new_customers, 2)
ELSE NULL
END AS cac
FROM monthly_signups s
LEFT JOIN monthly_spend m
ON s.cohort_month = m.month AND s.channel = m.channel
ORDER BY s.cohort_month DESC, s.channel;
Run this monthly. You'll see exactly when paid CAC drifts above your LTV threshold while blended CAC still looks comfortable.
## Step 5: Apply the Decision Framework
Use this before increasing paid spend:
| Condition | Action |
|---|---|
| Paid CAC < 1/3 × LTV | Scale paid spend aggressively |
| Paid CAC between 1/3 and 1/2 × LTV | Scale cautiously, optimize creatives |
| Paid CAC > 1/2 × LTV | Pause scaling, investigate channel efficiency |
| Organic ratio declining month-over-month | Invest in content/SEO before more paid |
The danger zone is when your organic ratio drops while you're scaling paid. Your blended CAC stays flat but your true economics are deteriorating.
## Gotchas
**The docs don't mention this, but** ad blockers will strip client-side UTM capture entirely. If you're only reading UTMs in JavaScript analytics, you're losing attribution data on 30%+ of technical users. Persist UTMs server-side on your signup endpoint.
**The "comfortable average" trap.** I've seen teams scale paid spend based on blended CAC, only to discover their actual unit economics were underwater once organic growth plateaued. It hits right when cash is getting tight.
**A declining organic ratio is a leading indicator, not a trailing one.** If it's dropping month-over-month, that's the signal to invest in content, product-led growth, and referral mechanics — not to increase ad budgets.
**Don't forget the `marketing_spend` table.** The cohort query joins against it. Even a simple table with `month`, `channel`, and `spend` columns is enough. Update it manually if you have to — accuracy beats automation here.
## Wrapping Up
The same discipline that lets a small team — like [Healthchecks.io](https://healthchecks.io) rationally evaluating self-hosted storage over managed providers — applies directly to growth spending. A single `channel` column on your signups table gives you the foundation for every growth decision you'll make. Track paid CAC separately from day one. Calculate your CAC distortion factor monthly. Measure the real cost, not the comfortable average.
Top comments (0)