Customer Lifetime Value vs Customer Acquisition Cost

In the dynamic world of SaaS, data is your roadmap to clarity and confidence.

This playbook equips you with ready-to-use SQL queries for the metrics that drive growth, retention, and profitability.

No need for advanced technical skills—just adapt the table and column names to your database, and let the insights unfold.

How to Use This Playbook

1. Choose your metric.
   Find the metric that answers your most pressing questions.
2. Copy the SQL.
   Swap in your own table and column names.
3. Run it in your database tool.
   Watch as the numbers reveal your business’s story.

Industry Benchmarks & Standards

Your Data Foundation

These tables are the pillars of your SaaS analytics:

• main.saas_data_clients: The foundation of every client relationship.
  
  • client_id, client_name, created_at
• main.saas_data_customers: Every user’s journey, captured in detail.
  
  • customer_id, client_id, customer_name, signup_date, is_active
• main.saas_data_plans: Your product’s menu of options.
  
  • plan_id, client_id, plan_name, monthly_price, annual_price, is_active
• main.saas_data_subscriptions: Captures the full lifecycle: sign-ups, renewals, and cancellations.
  
  • subscription_id, client_id, customer_id, plan_id, start_date, end_date, status
• main.saas_data_payments: The flow of revenue through your business.
  
  • payment_id, client_id, customer_id, subscription_id, amount, payment_date, is_refund
• main.saas_data_events: The story of every login, upgrade, and farewell.
  
  • event_id, client_id, customer_id, event_type, event_date, is_churn
• main.saas_data_costs: The price of delivering value to your customers.
  
  • cost_id, client_id, customer_id, cost_type, cost_amount, cost_date

Customer Lifetime Value (CLTV) vs Customer Acquisition Cost (CAC)

The balance between LTV and CAC is one of the clearest indicators of a healthy, scalable business model. LTV tells you how much revenue you can expect to earn from a customer over the entire relationship, while CAC measures what it costs to win that customer in the first place. When your LTV significantly exceeds your CAC, it means you’re not just covering acquisition costs but generating sustainable profit. Monitoring this ratio helps you decide how much to invest in marketing, sales, and customer success — ensuring that growth stays both efficient and profitable

WITH
-- Calculate total revenue per plan (CLTV)
"plan_revenue_cte" AS (
  SELECT
    "plans"."plan_name",
    SUM("payments"."amount") AS "total_revenue",
    COUNT(DISTINCT "payments"."customer_id") AS "customers"
  FROM "main"."saas_data_payments" AS "payments"
  JOIN "main"."saas_data_subscriptions" AS "subscriptions"
    ON "payments"."subscription_id" = "subscriptions"."subscription_id"
  JOIN "main"."saas_data_plans" AS "plans"
    ON "subscriptions"."plan_id" = "plans"."plan_id"
  WHERE NOT "payments"."is_refund"
  GROUP BY "plans"."plan_name"
),

-- Calculate CAC per plan (approximation)
"plan_cac_cte" AS (
  SELECT
    "plans"."plan_name",
    SUM("costs"."cost_amount") AS "total_cost",
    COUNT(DISTINCT "subscriptions"."customer_id") AS "customers"
  FROM "main"."saas_data_subscriptions" AS "subscriptions"
  JOIN "main"."saas_data_plans" AS "plans"
    ON "subscriptions"."plan_id" = "plans"."plan_id"
  JOIN "main"."saas_data_costs" AS "costs"
    ON "subscriptions"."client_id" = "costs"."client_id"
  -- Optional filter for marketing/sales costs:
  -- WHERE "costs"."cost_type" IN ('marketing', 'sales')
  GROUP BY "plans"."plan_name"
)

SELECT
  "plan_revenue_cte"."plan_name",
  "plan_revenue_cte"."customers" AS "customers_acquired",
  ROUND("plan_revenue_cte"."total_revenue", 2) AS "total_revenue",
  ROUND("plan_cac_cte"."total_cost", 2) AS "total_cost",
  -- CLTV
  ROUND(
    "plan_revenue_cte"."total_revenue" / NULLIF("plan_revenue_cte"."customers", 0),
    2
  ) AS "avg_cltv",
  -- CAC
  ROUND(
    "plan_cac_cte"."total_cost" / NULLIF("plan_cac_cte"."customers", 0),
    2
  ) AS "avg_cac",
  -- CLTV:CAC Ratio
  ROUND(
    (
      "plan_revenue_cte"."total_revenue" / NULLIF("plan_revenue_cte"."customers", 0)
    ) / NULLIF(
      (
        "plan_cac_cte"."total_cost" / NULLIF("plan_cac_cte"."customers", 0)
      ), 0
    ),
    2
  ) AS "cltv_cac_ratio"
FROM "plan_revenue_cte"
JOIN "plan_cac_cte"
  ON "plan_revenue_cte"."plan_name" = "plan_cac_cte"."plan_name"
ORDER BY "plan_revenue_cte"."plan_name"

Visualization Ideas:

• Combo Chart with Dual Axis: Compare CLTV with CAC
• Scorecard: Make the CLTV:CAC ratio your dashboard’s headline.

Implementation Checklist

1. Define your metrics and add them to a data dictionary.
2. Ensure your database schema supports the required calculations.
3. Write and test SQL queries for each metric.
4. Import results into your BI tool. Get started with DataBrain today!
5. Build and share dashboards with stakeholders.
6. Schedule regular reviews and updates.

Unlock the power of your data.
Start using this SQL Playbook today!

“In the world of SaaS, data is everyone’s responsibility. With this playbook, anyone can own their metrics and drive better outcomes.”

Now, simply run the SQL queries on your data and unlock the power of your SaaS analytics!