How to Create a Claude Skill for Analyzing CAC Payback

CAC payback is an extremely powerful tool for marketing and finance teams to think about how to efficiently unlock revenue growth for a direct to consumer (DTC) retail brand. Specifically, CAC payback is the point in time after a customer’s first purchase where the profit on that customer (or the contribution, for all your finance folks out there) becomes positive. You might think: “Why would anyone acquire a new customer at a net loss?” and the answer reveals a whole undercurrent of important nuance.

At Latticework Insights, we’ve created a custom methodology for thinking about CAC payback and we’ve encapsulated that methodology into a Claude skill. This guide walks through how the Claude skill works, the SQL and Python code behind it, the guardrails we give the agent, the prompts we use, and how to interpret the results. If you’ve never set up a skill, start with the LTV:CAC guide.

What is CAC payback for a retail brand?

A conservative perspective on marketing spend is to never spend more to acquire a customer than they pay you. This is the essence of ROAS (Return on Ad Spend), the idea that there should be a dollar “returned” for every dollar spent. Ideally that multiple is larger than 1x, otherwise, you’ve essentially “gone into debt” on the customer.

But ROAS is misleading! It assumes that a customer will only buy once. Which may be true in many cases. But for many mature brands, their customers’ purchase history bears out a different story: one where customers buy many times or where customers can sign up for a subscription. Seen through the lens of retention, brands can model what sorts of future purchases are likely and can adjust their marketing spend accordingly. By looking at historical retention rates, and the margins on each order, brands can quantify how much marketing “debt” (or CAC, Customer Acquisition Cost) is tolerable, given how fast a customer is likely to pay back the CAC investment.

Maybe you spend $200 on CAC knowing the first order will only net $100. But since you expect customers to buy once a month for at least 3 months, your CAC will pay back after 2 months, and the customer will become profitable after 3. Marketing can be more aggressive about acquiring high-value customers, and finance can feel confident they’ve allocated a pragmatic marketing budget.

This methodology is known as CAC Payback. It’s one of the things that separates retail brands from elite retail brands. But the methodology is part art and part science, and at Latticework Insights, we’ve codified a specific way of doing it into a Claude skill.

How do you build a CAC payback skill?

1. Define payback in markdown files for the agent

A skill is basically just a folder with some markdown files inside. The markdown file gives an agent some definitions like:

1. CAC is marketing spend divided by new customers, per acquisition month and channel. Each customer gets assigned the CAC of their acquisition month and channel.
2. Contribution is net revenue minus COGS minus fulfillment cost. LTV is the cumulative contribution.
3. Payback is the first purchase where the total contribution per customer is equal to or higher than CAC.

2. Define the SQL model, Python logic, and JavaScript interactivity

We don’t allow agents to determine the SQL model at runtime. Agents are terrible at being predictable. So we create the model up front, so the agent just needs to read the data.

-- CAC
CREATE OR REPLACE TEMP TABLE cac_month AS
SELECT month::date AS month, channel,
       CASE WHEN new_customers > 0 THEN spend / new_customers END AS cac
FROM marketing_spend;

-- Orders: sequence, age, and contribution
ROW_NUMBER() OVER (PARTITION BY o.customer_id
                   ORDER BY o.processed_at, o.order_id)         AS seq,
DATE_DIFF('day', c.first_order_at, o.processed_at)              AS days,
o.total_amount - o.refund_amount
  - COALESCE(cg.cogs, 0)
  - COALESCE(b.n, 0) * $fulfillment                             AS contribution

WITH base AS (
  SELECT c.customer_id, c.cac, o.seq, o.days, o.contribution,
         SUM(o.contribution) OVER (PARTITION BY o.customer_id
                                   ORDER BY o.seq) AS cum
  FROM cust c
  JOIN ord o USING (customer_id)
  WHERE c.matured AND c.in_window
),
n AS (
  SELECT COUNT(*) AS n, AVG(cac) AS cac
  FROM cust c
  WHERE c.matured AND c.in_window
)
SELECT b.seq,
       COUNT(*)            AS reached,
       SUM(b.contribution) AS contrib_at_k,
       AVG(b.cum)          AS cum_reached,
       MEDIAN(b.days)      AS med_days,
       n.n, n.cac
FROM base b CROSS JOIN n
WHERE b.seq <= 12
GROUP BY 1, n.n, n.cac
ORDER BY 1;

The resulting data from the Snowflake SQL warehouse gets run through a set of Python scripts. The scripts are deterministic and predictable, unlike agents.

exp, series = 0.0, []
for r in rows:
    exp += r["contrib_at_k"] / n    # running total per acquired customer
    series.append({"k": r["seq"], "share": r["reached"] / n,
                   "expected": round(exp, 2),
                   "reached": round(r["cum_reached"], 2),
                   "days": round(r["med_days"])})

And most importantly we want the dashboards to be interactive, so users can run multiple scenarios, instead of being confined to just yesterday’s numbers.

const pay = S.find(s => s.expected >= cac);

3. Read the output

The dashboard shows the retention curve, the total customers who reach each Nth purchase, the median days from the first order, as well as the first-order CAC.

Dashboard users can move the curve to demonstrate “what if” scenarios that finance might ask.

This is crucial. Now marketing and finance teams don’t need to argue. They can run multiple scenarios against clean data and come to an agreement about the ideal marketing budget.

The Claude skill helps them accelerate this process using the best of LLM capabilities, but with deterministic guardrails.

FAQ

What is CAC payback and how do you calculate it?

CAC payback is the time it takes for a new customer’s contribution margin to repay what it cost to acquire them. For a retail brand, find the day, or the purchase number, where cumulative contribution per acquired customer first reaches CAC. It is dependent on the observed historical retention curve, and finance uses the answer to decide how much budget to allocate to marketing teams.

Do I need a data warehouse to analyze CAC payback?

It’s totally possible to calculate CAC payback from exported CSVs. But the more source systems (e.g. Shopify, Amazon, Meta, Google) required to quantify marketing and revenue, the larger the need for a data warehouse, which is where Snowflake, Databricks, or BigQuery come in. Latticework Insights makes the process of building a data warehouse and modeling custom metrics easy. Claude helps accelerate the process.

Is CAC payback risky?

CAC Payback can go wrong in a couple places:

• Young cohorts, customers acquired last month, shouldn’t be included in a CAC payback model. They only have a small number of purchases and can drag down projected retention rates.
• Old cohorts, customers acquired many years ago, should also be handled with care. Businesses evolve, and many times old customer behavior is no longer relevant to the current state of the business.
• Revenue vs contribution. Gross and net revenue figures can be misleading when not focused on a per-order context. Contribution margin is a key aspect to having a clear line of sight on CAC payback.

Should I build a Claude skill or an agent-as-a-folder to analyze CAC payback?

An agent-as-a-folder is the most flexible and powerful option: point Claude Code at a folder or several old project folders, ask it to read the context, and then pick up where you and your team left off last. A skill is the formalized option: once the method works, you can package the system with a name and a description, and Claude loads it whenever a request matches. Opt for the folder if you’re developing a project and collaborating with teams through GitHub.