Attribution Models for Marketing ROI

A practical guide to understanding how credit for conversions is assigned across marketing touchpoints. Choose the right model to accurately measure your campaigns.

What Is Attribution?

Attribution answers the question: "Which marketing touchpoint(s) deserve credit for this conversion?" A customer rarely sees one ad and immediately buys. They might see a display ad, click a search result, receive an email, and then convert. Attribution determines how you split the credit (and the revenue) among those touchpoints.

The Five Common Models

1. Last-Touch Attribution

How it works: 100% of credit goes to the final touchpoint before conversion.

Example: Customer sees a LinkedIn ad → reads a blog post → clicks a Google ad → converts.

Credit: Google Ads gets 100%.

Best for:

• Short sales cycles (< 7 days)
• E-commerce / direct response
• When you want to optimize bottom-of-funnel campaigns

Limitation: Completely ignores awareness and nurture touchpoints that made the final click possible.

In the tracker: Set Attribution_Model = "Last Touch" and Attribution_Weight = 1.00

2. First-Touch Attribution

How it works: 100% of credit goes to the first touchpoint that introduced the customer.

Example: Customer sees a LinkedIn ad → reads a blog post → clicks a Google ad → converts.

Credit: LinkedIn gets 100%.

Best for:

• Measuring awareness channel effectiveness
• Long sales cycles where initial discovery matters
• Brand marketing evaluation

Limitation: Ignores everything that happened after initial contact—including the touchpoint that closed the deal.

In the tracker: Set Attribution_Model = "First Touch" and Attribution_Weight = 1.00

3. Linear Attribution

How it works: Credit is split equally among all touchpoints.

Example: Customer has 4 touchpoints → each gets 25% of the conversion value.

Best for:

• When you genuinely don't know which touchpoints matter most
• Balanced multi-channel strategies
• Teams early in their attribution journey

Limitation: Treats an accidental display impression the same as an intent-driven search click.

In the tracker: Set Attribution_Model = "Linear" and Attribution_Weight = 1/N (where N is total touchpoints). For 3 touches: 0.33.

4. Time-Decay Attribution

How it works: More recent touchpoints get more credit. Earlier ones get less. Typically uses a half-life of 7 days.

Example (7-day half-life):

• Touchpoint 21 days ago → 12.5% credit
• Touchpoint 14 days ago → 25% credit
• Touchpoint 7 days ago → 50% credit
• Final touchpoint → 100% credit (then normalize all to sum to 1)

Best for:

• B2B with long sales cycles
• When recency genuinely indicates influence
• Retargeting-heavy strategies

Formula for weight:

Weight = 2^(-(days_before_conversion / half_life))

In the tracker: Calculate weights manually and enter in Attribution_Weight. Weights for a single conversion should sum to 1.00.

5. Position-Based (U-Shaped) Attribution

How it works: First and last touchpoints each get 40% credit. Middle touchpoints split the remaining 20%.

Example with 4 touchpoints:

• First touch → 40%
• Second touch → 10%
• Third touch → 10%
• Last touch → 40%

Best for:

• Businesses that value both discovery and closing
• Balanced B2B and B2C measurement
• When you have clear funnel stages

In the tracker: Set weights manually: first = 0.40, last = 0.40, middle = 0.20 / (N-2).

Choosing the Right Model

Implementing in the Tracker

The Conversion Tracking tab includes Attribution_Model and Attribution_Weight columns. Here's how to use them:

Single-Touch Models (First/Last)

One row per conversion. Weight = 1.00. Simple.

Multi-Touch Models (Linear/Time-Decay/Position-Based)

Create one row per touchpoint-conversion combination. Same Conversion_ID, different Campaign_ID, with fractional weights that sum to 1.00.

Example: Linear attribution for conversion CVR-025 with 3 touchpoints:

CVR-025, MKT-002, ..., Linear, 0.33
CVR-025, MKT-005, ..., Linear, 0.33
CVR-025, MKT-007, ..., Linear, 0.34

Revenue Attribution Formula

In the ROI Summary tab, use attribution-weighted revenue instead of raw revenue:

=SUMPRODUCT(
  (ConversionTracking!B:B=A2) *    -- Match Campaign_ID
  ConversionTracking!K:K *          -- Deal_Value
  ConversionTracking!M:M            -- Attribution_Weight
)

This gives each campaign its proportional share of revenue.

Common Mistakes

1. Using last-touch for everything — undervalues awareness channels, leading to budget cuts that eventually hurt the funnel

2. Mixing models mid-analysis — pick one model for a given reporting period and stick to it

3. Ignoring assisted conversions — a campaign with zero last-touch conversions might assist hundreds of them

4. Not having enough data — attribution is meaningless with < 50 conversions per channel per month

5. Attributing to impressions — only count intentional interactions (clicks, form fills, email opens) as touchpoints

Moving Beyond Spreadsheet Attribution

This tracker handles straightforward attribution well. When you outgrow it, consider:

• Google Analytics 4 — data-driven attribution using machine learning
• Marketing mix modeling (MMM) — statistical approach using spend and outcome data
• Incrementality testing — controlled experiments to measure true lift

For most businesses doing < $100K/month in ad spend, the spreadsheet approach combined with disciplined data entry provides 80% of the insight at 0% of the cost of enterprise tools.

Calculating CAC and ROAS

A methodology guide for Customer Acquisition Cost and Return on Ad Spend — the two metrics that tell you whether your marketing is making or losing money.

Customer Acquisition Cost (CAC)

Definition

CAC measures how much you spend to acquire one new paying customer.

CAC = Total Marketing & Sales Spend / Number of New Customers Acquired

What Counts as "Spend"

Include everything that contributes to acquiring customers:

Types of CAC

Blended CAC: Total spend ÷ all new customers (includes organic)

Blended CAC = $52,150 total spend / 905 total new customers = $57.62

Paid CAC: Only paid channel spend ÷ customers from paid channels

Paid CAC = $52,150 paid spend / 632 paid customers = $82.51

Channel CAC: Spend on one channel ÷ customers from that channel

Google Ads CAC = $11,270 / 272 conversions = $41.43
LinkedIn CAC = $13,630 / 157 conversions = $86.82

CAC in the Tracker

The ROI Summary tab calculates CPA (Cost per Acquisition) per campaign:

Cell I2: =IF(K2=0, "N/A", ROUND(D2/K2, 2))

For blended CAC across all campaigns:

=ROUND(SUM(D2:D17) / SUM(K2:K17), 2)

Benchmarks by Industry

The CAC:LTV Ratio

CAC alone is meaningless without context. Compare it to Customer Lifetime Value (LTV):

LTV:CAC Ratio = Customer Lifetime Value / Customer Acquisition Cost

The "3:1 rule" is widely accepted: you want LTV to be at least 3× your CAC.

Calculating LTV (for the ratio)

Simple LTV formula:

LTV = Average Revenue per Customer × Average Customer Lifespan (months) × Gross Margin %

Example:

LTV = $50/month × 24 months × 0.70 = $840
CAC = $280
Ratio = $840 / $280 = 3:1 ✓

Return on Ad Spend (ROAS)

Definition

ROAS measures revenue generated per dollar of advertising spend.

ROAS = Revenue from Ads / Cost of Ads

A ROAS of 5.0 means you earn $5 for every $1 spent on advertising.

ROAS vs. ROI — What's the Difference?

ROAS is simpler and used for day-to-day campaign optimization. ROI gives the full business picture.

ROAS in the Tracker

Per-campaign ROAS (ROI Summary tab, Column G):

=IF(D2=0, 0, ROUND(E2/D2, 2))

Per-channel ROAS (Channel Performance tab, Column N):

=IF(F2=0, 0, ROUND(L2/F2, 2))

Blended ROAS (all campaigns):

=ROUND(SUM('ROI Summary'!E2:E17) / SUM('ROI Summary'!D2:D17), 2)

What's a "Good" ROAS?

It depends entirely on your margins:

Break-even ROAS formula:

Break-Even ROAS = 1 / Gross Margin %

If your gross margin is 60%: Break-even = 1 / 0.60 = 1.67. You need at least $1.67 in revenue per $1 of ad spend just to cover the cost of goods sold.

ROAS by Channel (from the sample data)

Common ROAS Pitfalls

1. Measuring too early — conversions take time. A 7-day ROAS window will undercount campaigns with longer sales cycles. Use 30–90 day attribution windows for B2B.

2. Ignoring assisted conversions — a campaign might show 2× ROAS on last-touch but assist conversions that push other campaigns to 10×.

3. Confusing revenue with profit — a $500 sale with 20% margin only contributes $100 of gross profit. If you spent $200 to get it, your real return is negative despite a 2.5× ROAS.

4. Not accounting for returns — especially in e-commerce. Use net revenue (after returns and refunds) for accurate ROAS.

CAC Payback Period

How long until a customer's revenue covers the cost of acquiring them.

Payback Period (months) = CAC / Monthly Revenue per Customer

Example:

CAC = $300
Monthly revenue = $50
Payback = 300 / 50 = 6 months

This means you're cash-flow negative on each new customer for 6 months. Important for planning.

In the tracker (ROI Summary, Column N — Payback_Period_Days):

=IF(E2=0, "N/A", ROUND((D2/E2)*30, 0))

Payback Period Benchmarks

Putting It All Together

For each campaign in your tracker, you should be able to answer:

1. How much did it cost to acquire each customer? (CPA column)

2. How much revenue did each dollar of spend generate? (ROAS column)

3. Is this sustainable given our margins? (Compare ROAS to break-even ROAS)

4. How long until we recover the spend? (Payback period)

5. What's the long-term value? (LTV estimate × Attribution weight)

When all five questions have clear answers, you can confidently decide whether to scale, optimize, or cut a campaign.