Tabellab

Last-Click vs First-Click Attribution: Reading Conversion Paths, Finding the Last Touch and Crediting Revenue per Channel

Lesson 1 of 20Lab

All lessons
  1. Last-Click vs First-Click Attribution: Reading Conversion Paths, Finding the Last Touch and Crediting Revenue per ChannelLabFree
  2. Linear Attribution: Equal Credit per Touch, Touch Counts per Channel and the Shift Away from Last-ClickLab Locked
  3. Position-Based (U-Shaped) Attribution: 40 % First, 40 % Last, the Middle Shared, with IFS on a Touch LogLab Locked
  4. Time-Decay Attribution: Half-Life Weights with Powers of 0.5, Normalising per Order and Credit per ChannelLab Locked
  5. One Table, Five Models: a Channel × Model Matrix with Mixed References, Model Shares and the Spread per ChannelLab Locked
  6. Channel ROAS Under Each Model: a ROAS Grid, the Lowest and Highest Reading and a Robust / Model-Dependent VerdictLab Locked
  7. Lookback Windows: Days Between Touch and Order, Touches Inside the Window and Linear Credit That Respects ItLab Locked
  8. Credit Shift Analysis: What Changes When You Leave Last-Click, in Euros, in Percent, in ROAS and in RankLab Locked
  9. Path Length and Time to Convert: How Many Orders Are Multi-Touch, How Long Paths Take and What That Means for the WindowLab Locked
  10. Lookback Window Sensitivity: How Many Touches Each Window Captures, Upper vs Lower Funnel and the Shortest Sufficient WindowLab Locked
  11. Half-Life Sensitivity: One Path, Eight Half-Lives, from Almost Last-Click to Almost LinearLab Locked
  12. Custom Position Weights: a Scenario Grid from 40/20/40 to First-Heavy and Last-Heavy, and Which Channels CareLab Locked
  13. Assists and Channel Roles: Last-Click Conversions, Assisted Conversions and the Assist Ratio per ChannelLab Locked
  14. Path Data Quality Checks: Touches After the Order, Duplicates, Missing Channels and Credit That Does Not Add UpLab Locked
  15. Fractional Conversions and CPA by Model: Why 112.8 Orders Is a Valid Number and How Cost per Acquisition MovesLab Locked
  16. Budget Reallocation What-If: Revenue-Share Budgets, a ±20 % Cap per Channel and a Cautious Revenue ProjectionLab Locked
  17. The Gap Between Models Over Time: Ratio, Trend with SLOPE, Correlation with Spend and a ForecastLab Locked
  18. Do the Models Agree? Ranking Campaigns Under Each Model, Rank Spread and Rank CorrelationLab Locked
  19. Model × Window Scenario Grid: 36 Ways to Measure One Channel, Break-Even ROAS and the Share of Profitable ScenariosLab Locked
  20. Final Project: an Attribution Dashboard from a Raw Touch Log with Four Models, ROAS Range, Verdict and ChartLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Loading the spreadsheet…

Which channel gets the credit for a sale?

A customer sees a banner for a trekking backpack, clicks a social ad a week later, searches for the shop, and finally orders after opening a newsletter. Four channels were involved and each of their reports would like to claim the sale. Attribution is the set of rules that decides how the revenue of that order is credited to the touches that came before it. It sounds like bookkeeping, but it steers real money: budgets follow the channels that appear to earn the most.

This free lesson covers the two simplest rules, last-click and first-click attribution, and shows how to calculate both from raw path data in Excel. They are called single-touch models because each gives the whole order to one touch. They are also the two extremes between which every multi-touch model sits, so understanding them well makes linear, position-based and time-decay attribution easy to follow later.

The example is Lumeridge Outfitters, a fictional online shop for outdoor gear. The company, its 12 orders, the channels and every number are invented for learning. The data are the same as in the lab above, so you can check each figure in this article against your own sheet.

Conversion paths: the raw material

Every attribution model starts from conversion paths. A path is the ordered list of marketing contacts, called touches or touchpoints, that one customer had before one order. Written out, a path looks like Display > Paid Social > Paid Search > Email. Each touch is recorded with its channel; richer exports also carry the campaign, the date and whether the touch was a click or only an ad view.

Paths come from an analytics tool or a data warehouse that stitches visits to orders, usually with tracking parameters on campaign links and an identifier that recognises the returning visitor. In a spreadsheet they appear in one of two layouts. The wide layout has one row per order and one column per touch position (Touch 1, Touch 2 and so on). The long layout, often called a touch log, has one row per touch. This lesson uses the wide layout because it is the easiest to read; from lesson 3 the course mostly works with the touch log, which scales better.

One path, two rules: order LO-1004 (€312)1DisplayTouch 12Paid SocialTouch 23Paid SearchTouch 34EmailTouch 4Order€312Last-click0 %0 %0 %100 %€312First-click100 %€3120 %0 %0 %credit tothe last touchcredit tothe first touch
Figure 1. The path of order LO-1004 has four touches. Last-click credits the whole €312 to Email, the final touch before the order; first-click credits the same €312 to Display, the touch that started the path.

A path only contains the touches that could be tracked. A recommendation from a friend, a shop window or a podcast mention is invisible. Attribution therefore describes the measurable part of marketing, not the whole of it.

Last-click attribution

Last-click attribution gives 100 % of the order to the final touch before the purchase. In the path above that is Email. The rule is popular for good reasons: it is simple, it needs no modelling decisions, and the last touch is the one most reliably recorded, because it happens in the same visit as the order.

Its weakness is just as clear. Last-click rewards the channels that tend to appear at the end of a path, such as brand search, email to existing subscribers, voucher and cashback partners or retargeting, and it gives nothing to the channels that made the customer aware of the shop in the first place. A business that plans budgets purely on last-click numbers will, over time, cut the top of its own funnel and then wonder why the closing channels have fewer people to close.

First-click attribution

First-click attribution gives 100 % of the order to the first touch of the path. In the example that is Display. First-click answers a different question: which channels start customer journeys? It is useful when the goal is growth and you want to know where new customers come from.

First-click has its own blind spot. It ignores everything that happened between the first contact and the order, including reminders and offers without which the purchase might not have happened. It is also more fragile in the data: the first touch may lie weeks back, outside the period the tool looks at, or on another device. What a report calls the first touch is often only the first touch it could see.

A fully worked example: 12 orders at Lumeridge Outfitters

Here are the 12 orders of the lab with their revenue and touches. The last three columns are calculated: the number of touches, the last-click channel and the first-click channel.

OrderRevenueTouch 1Touch 2Touch 3Touch 4TouchesLast-click channelFirst-click channel
LO-1001€148DisplayPaid Search2Paid SearchDisplay
LO-1002€89Paid Search1Paid SearchPaid Search
LO-1003€236Paid SocialOrganic SearchEmail3EmailPaid Social
LO-1004€312DisplayPaid SocialPaid SearchEmail4EmailDisplay
LO-1005€64Organic Search1Organic SearchOrganic Search
LO-1006€179Paid SocialEmail2EmailPaid Social
LO-1007€425DisplayOrganic SearchPaid Search3Paid SearchDisplay
LO-1008€118Email1EmailEmail
LO-1009€274Paid SocialPaid Search2Paid SearchPaid Social
LO-1010€96Organic SearchEmail2EmailOrganic Search
LO-1011€358DisplayPaid SocialEmail3EmailDisplay
LO-1012€141Paid SearchOrganic Search2Organic SearchPaid Search
Total€2,44026

The orders are typical for a shop with a considered purchase. Only 3 of the 12 orders have a single touch; they bring €271. The other 9 orders, worth €2,169 or 89 % of the revenue, have two to four touches, 2.2 touches per order on average over all orders. For single-touch orders the model does not matter, because first and last touch are the same. For all the others the two models disagree whenever the path starts and ends with different channels.

The formulas step by step

In the lab the order ID is in column A, the revenue in column B and the four touch columns are C to F, with the data in rows 4 to 15. The summary table lists the channels in K4:K8. Four formulas do all the work.

  1. Count the touches (H4): =COUNTA(C4:F4). COUNTA counts the cells that are not empty. COUNT would return 0 here, because it only counts numbers and the touches are text.
  2. Find the last-click channel (I4): =INDEX(C4:F4,COUNTA(C4:F4)). INDEX returns the cell at a given position of a range. The position is the number of touches, so for a path with three touches INDEX returns the third cell. If you already have the count in H4, =INDEX(C4:F4,H4) does the same.
  3. Add up last-click revenue per channel (L4): =SUMIFS($B$4:$B$15,$I$4:$I$15,K4). SUMIFS adds the revenue of all rows whose last-click channel equals the channel in K4. The dollar signs lock both ranges, so the formula can be copied down to L8; K4 stays relative and moves to K5, K6 and so on.
  4. Add up first-click revenue per channel (M4): =SUMIFS($B$4:$B$15,$C$4:$C$15,K4). The first touch needs no helper column: it is always in column C.
Check the totals before you look at anything else. Each model must hand out exactly the total revenue, here €2,440. If the sum of a model column is lower, some orders found no matching channel (a typing variant such as "E-mail" is a classic cause); if it is higher, something is counted twice.

Walk through one row to see the logic. Order LO-1003 has the touches Paid Social, Organic Search and Email. COUNTA returns 3. INDEX takes the third cell of C6:F6, which is Email, so the order's €236 lands in Email's last-click total. Its first touch in C6 is Paid Social, so the same €236 lands in Paid Social's first-click total. Nothing is created or lost; the two models merely file the order under different channels.

Reading the result

Summed per channel, the two models give this picture:

ChannelOrders closedLast-click revenueOrders openedFirst-click revenueFirst-click minus last-click
Paid Search4€9362€230−€706
Paid Social0€03€689+€689
Display0€04€1,243+€1,243
Email6€1,2991€118−€1,181
Organic Search2€2052€160−€45
Total12€2,44012€2,440€0

Same 12 orders, two models: revenue credited per channelLast-clickFirst-click€0€350€700€1,050€1,400€936€230Paid Search€0€689Paid Social€0€1,243Display€1,299€118Email€205€160Organic SearchBoth models distribute the same €2,440
Figure 2. Last-click and first-click revenue per channel for the same 12 orders. Display and Paid Social receive nothing under last-click and most of the revenue under first-click; Email and Paid Search are the mirror image.

Display is the most striking line. It closes no order, so last-click gives it €0. It opens 4 paths, among them the largest orders, so first-click gives it €1,243, more than half of all revenue. Paid Social follows the same pattern: €0 against €689. Email is the opposite: it is the last touch of 6 orders and collects €1,299 under last-click, but it starts only 1 path and keeps €118 under first-click.

Neither report is wrong. Each answers its own question correctly: who closed? and who opened? A marketing manager who sees only the last-click column would conclude that Display is wasted money; one who sees only the first-click column would conclude that Email hardly matters. Both conclusions overreach. What the two columns show together is the role of each channel: Display and Paid Social introduce, Email and Paid Search close, and Organic Search does a little of both (€205 and €160).

Why single-touch models are not enough

A single-touch model throws away most of the path. In order LO-1004 three of four touches receive nothing, whichever of the two rules you choose. Multi-touch models were invented to use the whole path:

  • Linear attribution splits the order evenly across all touches.
  • Position-based (U-shaped) attribution gives most of the credit to the first and the last touch, commonly 40 % each, and shares the rest among the touches in between.
  • Time-decay attribution gives more credit the closer a touch lies to the order, controlled by a half-life.

All of these are still rule-based: a person chooses the rule, and the spreadsheet applies it. That is their strength, because anyone can check the calculation, and their limit, because the rule is an assumption. The sensible way to use them is not to search for the one true model but to compare several and see which conclusions survive every reasonable rule.

Attribution is not the same as incrementality. A model distributes credit among the touches that were recorded; it cannot tell whether an order would have happened without a particular channel. That question needs an experiment, for example a holdout group or a regional test. Use attribution to describe and to prioritise, and experiments to prove.

Common mistakes and how to spot them

Most errors in single-touch attribution are small and mechanical. These are the ones that occur again and again:

  • Reading Touch 4 as the last touch. Most paths are shorter than the widest one, so the last column is usually empty. Always take the last filled cell, with INDEX and COUNTA.
  • Counting orders when the question is revenue. COUNTIF tells you how many orders a channel closed; SUMIFS tells you what they were worth. Both are valid numbers, but they answer different questions and must not be mixed in one column.
  • Unlocked ranges. Without dollar signs, B4:B15 becomes B5:B16 when you copy the formula down, and the last rows silently fall out of the sum.
  • Inconsistent channel names. "Paid search", "Paid Search " with a trailing space and "SEA" are three different texts to SUMIFS. Clean the channel column before you attribute.
  • Comparing reports with different rules. An advertising platform typically reports the conversions it can claim under its own rules and window, while a web analytics tool applies another rule across all channels. Adding such numbers up counts orders more than once.
  • Treating one model as the truth. A last-click report is one view. Decisions about channels that mostly assist should never rest on it alone.

Quick reference: the functions in this lesson

FunctionWhat it doesUsed here for
COUNTA(range)Counts non-empty cellsNumber of touches in a path
INDEX(range, n)Returns the n-th cell of a row or columnThe last filled touch, with n = COUNTA
SUMIFS(sum_range, criteria_range, criterion)Adds the values that meet a conditionRevenue per last-click or first-click channel
COUNTIF(range, criterion)Counts the cells that meet a conditionOrders closed or opened per channel
$B$4:$B$15Absolute reference, does not move when copiedData ranges inside SUMIFS

Where last-click and first-click are still useful

Single-touch models have not disappeared, and for good reasons:

  • As a common baseline. Last-click is the number most teams and most historical reports share. Keeping it next to any new model shows what actually changed.
  • For short buying cycles. If most orders have one or two touches, as for low-priced repeat purchases, all models give nearly the same answer and the simplest one is enough.
  • For questions about roles. First-click is a quick way to see which channels bring in new journeys; last-click shows which channels are present at the moment of purchase.
  • As the two ends of a range. If a channel looks good under both extremes, it will look good under any rule in between. If it looks good under only one, you know exactly which assumption its budget depends on.

A word on lookback windows

Every attribution report also has a lookback window: the maximum time between a touch and the order for the touch to count. With a 30-day window a banner seen 40 days before the purchase is not part of the path at all. The window therefore decides what the "first" touch is, and a short window quietly turns first-click into something closer to last-click. In the lab all touches are inside the window; lesson 7 builds the window from touch and order dates, and lesson 10 tests how much several windows capture.

What you will build in the rest of this course

The free lesson is the foundation. The other 19 labs build the complete rule-based toolkit on fresh data sets of the same fictional shop:

Linear, position-based and time-decay attribution. You split each order evenly, by a 40/20/40 position rule with IFS, and by a half-life with powers of 0.5, each time checking that the credit adds up to the revenue.

One table, five models. A channel-by-model matrix built with a single mixed-reference SUMIFS, followed by ROAS per channel under every model and a robust or model-dependent verdict.

Lookback windows and sensitivity. You apply a window from dates, see what a 30-day window does to an upper-funnel channel, and test windows and half-lives like the parameters they are.

Diagnostics. Path length and time to convert, assists and channel roles, a data-quality audit for duplicates, touches after the order and credit that does not add up.

Decisions. Credit shift analysis, fractional conversions and CPA, a capped budget what-if, the trend of the gap between models, rank agreement between models and a 36-cell model-by-window scenario grid.

Final project. An attribution dashboard built from a raw touch log, with four models, a ROAS range per channel and a chart.

Summary

Attribution distributes the revenue of an order across the marketing touches before it. Last-click gives everything to the final touch, first-click everything to the first. In Excel you count the touches with COUNTA, find the last one with INDEX and add revenue per channel with SUMIFS. At Lumeridge Outfitters both models distribute the same €2,440, yet Display receives €0 under one and €1,243 under the other, while Email moves from €1,299 to €118.

The lesson to take away is not that one of them is right. It is that a channel's reported value depends on a rule someone chose, and that you can read a channel's role from the way its numbers move between rules. Now scroll up to the lab and build the two models yourself; it takes about fifteen minutes, and the Coach checks every step.

Frequently asked questions

What is the difference between last-click and first-click attribution?

Last-click gives 100 % of an order to the final marketing touch before the purchase; first-click gives 100 % to the first touch of the path. Both use the same orders and the same total revenue, but they credit different channels whenever a path starts and ends with different channels.

How do I find the last touch of a conversion path in Excel?

With the touches of an order in C4:F4, use =INDEX(C4:F4,COUNTA(C4:F4)). COUNTA counts the filled cells, and INDEX returns the cell at that position, which is the last filled touch even when the path is shorter than four touches.

How do I calculate attributed revenue per channel?

Use SUMIFS. For last-click: =SUMIFS(revenue_range, last_click_channel_range, channel). For first-click, use the Touch 1 column as the criteria range. Lock the ranges with dollar signs before copying the formula down the list of channels.

Why do last-click and first-click totals have to be equal?

Every order is credited exactly once in each model, so each model distributes the same total revenue. If the totals differ, a channel name does not match, a range is too short or an order is counted twice.

Is last-click attribution wrong?

It is not wrong, but it is one-sided. It answers which channel was present at the moment of purchase and gives no credit to channels that started or supported the journey. It is best read next to other models rather than as the only view.

Does attribution show which channel caused a sale?

No. Rule-based attribution distributes credit among the recorded touches according to a chosen rule. Whether an order would have happened without a channel is a question of incrementality, which needs an experiment such as a holdout or regional test.

Keep going: Multi-Touch Attribution with Rule-Based Models: Last-Click, First-Click, Linear, Time-Decay & Position-Based Credit, Channel ROAS by Model and Lookback Windows

This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.

  1. Linear Attribution: Equal Credit per Touch, Touch Counts per Channel and the Shift Away from Last-Click
  2. Position-Based (U-Shaped) Attribution: 40 % First, 40 % Last, the Middle Shared, with IFS on a Touch Log
  3. Time-Decay Attribution: Half-Life Weights with Powers of 0.5, Normalising per Order and Credit per Channel
  4. One Table, Five Models: a Channel × Model Matrix with Mixed References, Model Shares and the Spread per Channel
  5. Channel ROAS Under Each Model: a ROAS Grid, the Lowest and Highest Reading and a Robust / Model-Dependent Verdict
  6. Lookback Windows: Days Between Touch and Order, Touches Inside the Window and Linear Credit That Respects It
  7. Credit Shift Analysis: What Changes When You Leave Last-Click, in Euros, in Percent, in ROAS and in Rank
  8. Path Length and Time to Convert: How Many Orders Are Multi-Touch, How Long Paths Take and What That Means for the Window
  9. Lookback Window Sensitivity: How Many Touches Each Window Captures, Upper vs Lower Funnel and the Shortest Sufficient Window
  10. Half-Life Sensitivity: One Path, Eight Half-Lives, from Almost Last-Click to Almost Linear
  11. Custom Position Weights: a Scenario Grid from 40/20/40 to First-Heavy and Last-Heavy, and Which Channels Care
  12. Assists and Channel Roles: Last-Click Conversions, Assisted Conversions and the Assist Ratio per Channel
  13. Path Data Quality Checks: Touches After the Order, Duplicates, Missing Channels and Credit That Does Not Add Up
  14. Fractional Conversions and CPA by Model: Why 112.8 Orders Is a Valid Number and How Cost per Acquisition Moves
  15. Budget Reallocation What-If: Revenue-Share Budgets, a ±20 % Cap per Channel and a Cautious Revenue Projection
  16. The Gap Between Models Over Time: Ratio, Trend with SLOPE, Correlation with Spend and a Forecast
  17. Do the Models Agree? Ranking Campaigns Under Each Model, Rank Spread and Rank Correlation
  18. Model × Window Scenario Grid: 36 Ways to Measure One Channel, Break-Even ROAS and the Share of Profitable Scenarios
  19. Final Project: an Attribution Dashboard from a Raw Touch Log with Four Models, ROAS Range, Verdict and Chart

Buy course — $9.90 See the full course