How to calculate LTV/CAC by product and turn the funnel into a budget simulator

    At a B2B SaaS with two products, we calculated the LTV/CAC (LTV to CAC ratio) of each one separately and built a funnel simulator that tells you how much to invest to reach a pipeline target. The total ratio was hiding two businesses with very different economics.

    Profile

    B2B SaaS · Growth stage

    Problem

    Marketing didn't know how much to invest to deliver the opportunity target to sales, and LTV/CAC was treated as a single number for the whole company. Funnel and cost data were scattered and poorly structured.

    Solution

    Bring the funnel history and cost per channel into a simulator that starts from the target and works back to the investment, and break LTV/CAC down by product, with sensitivity to the assumptions that matter most.

    What we did

    • Funnel simulator: conversion rates from lead to prospecting, MQL, SQL and SAL, cost per channel (inbound, outbound, partnerships and events) and a reverse calculation: given the SAL target, how many leads and how much investment are needed. The real conversion can be updated every month.
    • LTV/CAC by product: CAC with the total acquisition cost, including the sales team, which was the largest share, and shared teams allocated between the products; LTV with an explicit customer lifetime assumption.
    • Sensitivity to team allocation, freemium-to-paid conversion and the length of the free period. Asking for a payment method at sign-up converted far more users into paying customers; extending the free period didn't increase conversion, because users disengage.

    Result

    Marketing gained budget predictability for inbound, and the company could see the cost structure of each product. One product's LTV/CAC came out about 10 times higher than the other's, with CAC in the same order of magnitude: the difference was in LTV.

    Key learnings

    • A total LTV/CAC can hide two different businesses. When CAC is similar across products, what separates the outcome is LTV: paid conversion, ticket and retention. Changing team allocation moves the number, but doesn't change the order of magnitude.
    • In many cases, the median describes the business much better than the average. We often see LTV/CAC in the industry calculated with averages pulled up by a few large customers or unusual months. Removing outliers, separating freemium from paying customers and looking by cohort shows the business as it is and avoids the most common mistakes.
    • Whether to include salaries and people costs in CAC is always a contentious topic. Align the definition with founders and investors before comparing numbers, and show both versions.

    Do it yourself

    1. Build the simulator in a spreadsheet, working backwards:

      • an assumptions tab per channel: monthly cost, leads and conversion rate at each stage (lead, MQL, SQL, opportunity), using the median of the last 3 to 6 months;
      • a calculation tab: desired opportunities ÷ SQL→opportunity rate ÷ MQL→SQL rate ÷ lead→MQL rate = leads needed; leads × cost per lead = investment per channel;
      • a "real conversion" cell to update every month and compare with the assumption.
    2. Calculate LTV/CAC by product, not for the company:

      • CAC in two versions: media and tools only, and with marketing and sales salaries plus the allocated share of shared teams, divided by new paying customers in the period;
      • LTV = median monthly revenue per paying customer × gross margin × expected months of life, after removing outliers (for example, customers above the 95th percentile of revenue) and with the assumption written down;
      • a sensitivity table with team allocation, paid conversion and churn. A common market reference: LTV/CAC above 3.
    3. Ask an LLM, with the CRM export stripped of customer names:

      I'll paste two tables: (1) leads by month, channel and funnel stage (lead, MQL, SQL, opportunity, sale) and (2) monthly costs by channel and by team, with the product each cost relates to. Calculate conversion rates by channel using the median, build the reverse calculation of the investment needed to reach [N] opportunities per month and calculate the LTV/CAC of each product with a customer lifetime of [X] months and a margin of [Y]%. Identify and exclude outliers, showing what you removed, and calculate CAC in two versions, with and without people costs. Then build a sensitivity table varying the allocation of shared teams, paid conversion and churn, and tell me which of the three changes the result most.