Cohort payback analysis for acquisition channels

Cohort payback asks when the accumulated contribution from an acquired customer group covers the acquisition cost assigned to that group. Follow the same customers through their observed lifetime; do not combine this month's acquisition spending with revenue from all historical customers and call it cohort payback.

Stripe's CAC payback explanation describes cost recovery as the central question. The example below calculates an observed contribution path directly rather than assuming a constant monthly return.

Define two synthetic acquisition cohorts

The acquisition fixture has ten customers in channel A with acquisition cost of 100,000 paise and five customers in B with cost of 60,000 paise. The cohort contribution fixture records contribution by cohort age month.

Amounts are paise and entirely fictional. Contribution is assumed to be after the variable costs included in this exercise's definition, before acquisition cost. These rows already aggregate the original acquired cohort, including the effect of customers who cease contributing.

Find the first observed crossing

WITH cumulative AS (
 SELECT channel,age_month,
 SUM(contribution_paise) OVER (
   PARTITION BY channel ORDER BY age_month
   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 ) AS cumulative_contribution
 FROM cohort_margin
)
SELECT a.channel,a.customers,a.cost_paise,
 MIN(CASE WHEN c.cumulative_contribution>=a.cost_paise
          THEN c.age_month END) AS first_observed_payback_month
FROM acquisition a LEFT JOIN cumulative c USING(channel)
GROUP BY a.channel,a.customers,a.cost_paise ORDER BY a.channel;

A's cumulative contribution is 20,000, 50,000, 90,000 and 110,000 paise, reaching its 100,000 cost in month four. B accumulates 10,000, 30,000 and 60,000, reaching its cost in month three.

from build_and_verify import database

db = database()
result = {}
for channel,customers,cost in db.execute('SELECT * FROM acquisition'):
    rows = db.execute('SELECT age_month,contribution_paise FROM cohort_margin WHERE channel=? ORDER BY age_month',(channel,)).fetchall()
    assert [age for age,value in rows] == list(range(1,len(rows)+1))
    cumulative = 0
    first = None
    for age,value in rows:
        cumulative += value
        if first is None and cumulative >= cost:
            first = age
    result[channel] = {'cac_paise':cost/customers, 'payback_month':first,
                       'observed_months':len(rows), 'cumulative_paise':cumulative}
db.close()
assert result['A'] == {'cac_paise':10000,'payback_month':4,'observed_months':4,'cumulative_paise':110000}
assert result['B'] == {'cac_paise':12000,'payback_month':3,'observed_months':3,'cumulative_paise':60000}
print(result)

B has a higher acquisition cost per customer but earlier observed payback. CAC alone does not determine the recovery period.

Keep missing age periods visible

B has no observed fourth month. That is not evidence of zero contribution. If a cohort has not yet crossed its cost by the latest observed month, report “not reached within observed horizon” with that horizon. Do not label it as never paying back.

The Python check requires consecutive observed age months. A missing month between two observed months would make a cumulative sum incomplete. Repair the source or mark the result unresolved rather than letting SQL silently skip the gap.

This example reports the first monthly crossing. Interpolating a fractional month assumes something about contribution timing inside the month. If later negative contribution can take the cumulative value below cost again, distinguish first crossing from sustained recovery.

Compare channels without changing the cost contract

Use consistent treatment of paid media, sales effort, agency fees and shared acquisition costs. If one channel receives fully loaded costs while another receives only direct spending, their apparent efficiency is not comparable.

Contribution payback is also different from cash payback. Annual prepayments, delayed collections, refunds and payment timing can make cash recovery differ from the contribution curve. State which question the report answers.

A historical channel comparison is descriptive. It does not prove that moving budget to B will reproduce B's past result. Audience saturation, attribution, capacity and customer mix may change at a different spending level.

Exercise: truncate A after month three and verify that its result becomes “not reached within three observed months.” Then add a negative month after its first crossing and define a separate sustained-recovery criterion.

NeuraPath's Data Analytics with Generative AI course connects SQL windows with business interpretation. A useful payback analysis preserves the cohort, cost scope and observation horizon behind the headline month.

Continue learning

This article is part of the Customer and product analytics sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Analyze support contacts per active customer.
• Continue with Attribution windows: why marketing reports disagree.