How to Build a CAC, LTV and Revenue Breakdown Dashboard with AI Forecasting

A useful revenue dashboard breaks total revenue into net new, expansion and churn; separates recurring revenue from one-time fees and projects; attributes revenue to its source (paid ads, organic search, referrals, outbound); and calculates CAC and LTV by channel. An AI agent then reads the numbers each month and writes a plain-language report and forecast, so leadership gets an explanation, not just charts.

Why most revenue reporting falls short

Most B2B companies can tell you their total revenue. Far fewer can confidently answer:

• How much of this month's growth came from new customers vs existing ones?
• How much revenue did we lose to churn and downgrades?
• How much of our revenue is recurring, and how much won't repeat?
• Which channel actually produces our best customers, not just our most leads?
• What does it cost to acquire a customer from each channel, and is it worth it?

Without those answers, budget decisions get made on lead volume and gut feel. Marketing spend goes to the channels that produce the most form fills rather than the most revenue, and leadership can't tell whether growth is healthy.

The five views every revenue dashboard needs

1. Net new vs expansion vs churn

Break revenue movement into its parts each month:

• New business: revenue from customers who weren't customers before
• Expansion: upsells, cross-sells and price increases from existing customers
• Renewal: existing contracts that renewed at the same value
• Contraction: downgrades
• Churn: revenue from customers who left

This shows whether growth comes from acquisition or retention. A business growing only through new logos while churning heavily is on a treadmill.

2. Recurring vs one-time revenue

Separate subscription or retainer revenue from setup fees, implementation, professional services and projects. Recurring revenue is far more predictable and valuable. If a large share of a good month came from one-time projects, the next month may look very different.

3. Attribution by source

Tie every closed-won deal back to where it came from: paid ads (split by platform and campaign), organic search, referral, partners, events and outbound. The key is attributing revenue, not leads. A channel that produces many leads but few closed deals looks great in a lead report and poor in a revenue report.

For paid channels, this closed-won data can also be sent back to ad platforms such as Google Ads and Meta, so their bidding optimizes for revenue rather than form fills.

4. CAC by channel

Customer acquisition cost is total sales and marketing spend for a channel divided by new customers acquired from it in the same period. Calculate it blended and by channel. Include ad spend, tools, and the relevant portion of salaries and agency fees, or the number will be misleadingly low.

5. LTV and LTV:CAC

Lifetime value estimates the total gross margin a customer generates over their relationship with you. A simple version is average monthly revenue per customer, times gross margin percentage, divided by monthly churn rate. Compare LTV to CAC by channel to see which channels produce customers worth acquiring, and track the CAC payback period (months to earn back acquisition cost).

How the AI reporting agent works

Charts show what happened. The agent explains why and what's likely next.

Each month (or week), the agent:

1. Pulls the latest metrics from the dashboard's data source.
2. Compares them to the previous period, the same period last year and targets.
3. Identifies the biggest changes and their likely drivers (for example, expansion revenue rose because of three large upsells; CAC on paid search rose because spend increased while conversion rate dropped).
4. Builds a forecast for the next one to three months from current pipeline, historical conversion rates, renewal schedules and churn trends.
5. Writes a short report: summary, key movements, channel performance, risks and recommended actions.

The math stays in code and SQL. The LLM interprets results and writes the narrative, which keeps the numbers accurate.

How to build it

Data sources

• CRM (HubSpot or Salesforce): deals, close dates, amounts, lead source, deal type (new, expansion, renewal)
• Billing system (Stripe, QuickBooks, Xero or your subscription platform): invoices, subscriptions, cancellations, one-time charges
• Ad platforms (Google Ads, Meta, LinkedIn): spend by campaign
• Cost data: tools, salaries and agency fees allocated to sales and marketing

Steps

1. Standardize fields first: a required deal type (new, expansion, renewal), a required lead source with consistent values, and a revenue type on line items (recurring vs one-time).
2. Pull data from each source into one place with n8n or a data pipeline: a Google Sheet for small teams, BigQuery or a warehouse as you grow.
3. Join CRM deals with billing records so revenue is tied to the right customer and source.
4. Build calculated tables for revenue movement, recurring vs one-time, revenue by source, CAC and LTV.
5. Visualize it in Looker Studio, HubSpot or Salesforce reports, or your BI tool.
6. Set up a scheduled n8n workflow that sends the period's metrics to an LLM with your reporting prompt and delivers the written report by email or Slack.

Common pitfalls

• Missing or messy lead source data. Fix it at the point of entry with required fields and hidden form fields that capture UTM parameters.
• Counting leads instead of revenue for attribution.
• Leaving salaries out of CAC.
• Mixing one-time and recurring revenue in growth metrics.

Metrics to watch over time

• Net revenue retention (recurring revenue kept and expanded from existing customers)
• Gross churn rate
• CAC and CAC payback by channel
• LTV:CAC ratio by channel
• Share of revenue that's recurring
• Forecast accuracy