Your First Week as a Marketing Data Analyst. They’re Already Asking for Target-CPAs

No LTV model. No attribution pipeline. Here’s what you can actually ship by Friday.

You just joined a SaaS startup. Day three. You’re still figuring out which Slack channels actually matter when the growth team drops a message: “Hey, can you give us a target CPA by end of week? We’re launching a campaign.”

No LTV model. No attribution pipeline. No historical CPA benchmarks. The closest thing to a data warehouse is a Stripe export and a spreadsheet someone built two years ago and nobody has touched since.

You have a week. Here’s what you can actually ship.

Why Not Just Build an LTV Model?

Survival curves, churn prediction, multi-touch attribution. These are the right long-term answers. They also take months to do properly, and a model that looks sophisticated but sits on thin data is worse than something simpler and honest.

What you need right now is something defensible. When the CMO asks “how did you get this number?”, you should be able to walk them through it in five minutes and they’ll follow along.

This approach runs on two things you almost certainly have: first transaction revenue and renewal rates. That’s it.

One assumption upfront: this is written for an annual subscription model. Monthly plans work the same way — you’d just use monthly renewal data instead. Annual is simpler to start with because renewal rates are more stable, and most early-stage SaaS revenue concentrates there anyway.

What Does a User Actually Generate Over Time?

When someone subscribes for the first time, they pay you something. Call that the ARPFT — Average Revenue per First Transaction. Not list price; actual revenue received, averaged across the cohort.

ARPFT = Total First Revenue / Total Cohort Size

But that’s only year one. A portion of those users will renew. So the real question is: given that a user paid you X on their first transaction, how much can you expect to collect over the following years, accounting for churn?

Renewal rates answer this. If 55% of year-one subscribers renew for year two, and 70% of those renew again for year three, you can build a simple expected value across the subscription lifetime.

Once you have that, you apply an ROI target, the minimum return your business needs on marketing spend, and back into a target CPA:

Target CPA = [ARPFT + (ARPFT × commission_adjustment) × Retention Rate] / ROI Target

The commission adjustment accounts for platform differences. On iOS, Apple drops its cut from 30% to 15% after the first year of a subscription, so effective revenue per renewal is higher than year one. Multiply the renewal portion by roughly 1.15 to capture that. On Android, treat it as 1 — Google’s commission structure is more variable and harder to model cleanly at this stage.

On the ROI target: if your company hasn’t set one, start with 2. For every dollar spent acquiring a customer, you’re targeting two back over that customer’s lifetime. You can make this a live input in a dashboard later, but 2 gives the team something concrete to work with today.

Where to Get Your Renewal Rates

You probably don’t have a clean “renewal rate” metric sitting in a BI tool. What you do have is subscriber records, each row telling you when a subscription started, when it ended or renewed, whether the user is still active.

From that, you build a retention cohort. Group users by the month they first subscribed, then for each subsequent month, count how many are still active. Divide by original cohort size.

Here’s the SQL, already structured as a dbt model since you’ll want this running on a schedule:

-- models/mart/agg_retention_cohorts.sql
with first_subscriptions as (
    select
        user_id,
        date_trunc('month', started_at) as cohort_month,
        started_at as first_subscription_date
    from {{ ref('dim_subscriptions') }}
    where is_first_subscription = true
      and plan_type = 'annual'
),
subscription_activity as (
    select
        s.user_id,
        f.cohort_month,
        f.first_subscription_date,
        date_trunc('month', s.date_day) as activity_month,
        date_diff('month', f.first_subscription_date, s.date_day) as months_since_start
    from {{ ref('dim_subscription_activity') }} s
    inner join first_subscriptions f using (user_id)
    where s.date_day < current_date
      and s.date_day < s.cancelled_at
),
cohort_sizes as (
    select
        cohort_month,
        count(distinct user_id) as cohort_size
    from first_subscriptions
    group by 1
),
retained as (
    select
        cohort_month,
        months_since_start,
        count(distinct user_id) as retained_users
    from subscription_activity
    group by 1, 2
)
select
    r.cohort_month,
    c.cohort_size,
    r.months_since_start,
    r.retained_users,
    round(r.retained_users / c.cohort_size, 4) as retention_rate
from retained r
inner join cohort_sizes c using (cohort_month)
order by 1, 3

What you’re building is a survival table by cohort. Average across cohorts to get a stable retention rate at month 12 (first renewal) and month 24 (second renewal). Those two numbers are your foundation.

One practical note on platform splits: since iOS 14, SKAdNetwork data has been noisy enough that source-level breakdowns for iOS aren’t reliable. For iOS, aggregate across all sources. For Android, you can go deeper, break out Google UAC, Facebook, Organic, and lump the rest, since the data is more trustworthy there.

Adjusting When the Data Isn’t Clean

You will hit markets where the data is thin. Maybe you only started running paid campaigns in Germany six months ago, so there’s no meaningful second-renewal rate yet. Maybe one cohort had a promotional offer that skewed first-transaction revenue in a way that won’t repeat.

Don’t pretend the model output is correct when you know it isn’t. Make the manual adjustment, document it, flag it clearly. A labeled override is more useful than a number that looks precise but is anchored to noise.

Same goes for markets without source-level data. Use the aggregated retention rate, note the limitation, and revisit when you have more history.

From Cohort to CPA

Once you have renewal rates by country and platform, and ARPFT from first transaction data, the calculation is:

Lifetime Value = ARPFT
              + (ARPFT × 1.15) × First Renewal Rate
              + (ARPFT × 1.15) × Second Renewal Rate × First Renewal Rate

Target CPA = Lifetime Value / ROI Target

At an ROI target of 2, you’re willing to spend up to half the expected lifetime value to acquire a customer. Whether that’s the right number for your business depends on your growth stage and how much you trust the retention data — but it’s a number you can stand behind on day three.

What This Model Actually Tells You

Here’s the thing that surprises most people: target CPAs are almost entirely driven by ARPFT, not by your retention assumptions. Renewal rates are relatively stable month to month. What moves your target CPA dramatically is the conversion rate into that first paid transaction.

This matters when your growth team wants to test longer trial periods. A 30-day trial instead of 7 days might improve trial-to-paid conversion, or it might pull in lower-intent users who look fine until they don’t renew. Either way, it hits ARPFT directly, and therefore every CPA target the model produces. Make sure the team understands that relationship before they start blaming channel quality for shifts that are really about trial length.

What to Build for the Team

A well-structured Google Sheet or a Metabase dashboard is enough. What the team needs is simple:

• Target CPAs by country and platform
• An adjustable ROI target input that updates the numbers live
• Enough transparency that when a market looks off, they can trace it back to ARPFT or the retention curve

The goal isn’t a black box. It’s a tool someone can interrogate at 4pm on a Thursday when the numbers don’t add up.

No survival models. No 12-month data science sprint. Cohort logic, clean SQL, a number the team can use by Friday.

When you eventually build a proper LTV mode, and you should, this doesn’t become obsolete. It becomes the sanity check. When the LTV model says something unexpected, you’ll come back to ARPFT and renewal rates to figure out why.

Tags: Data Science, SaaS, Growth Marketing, Analytics, SQL