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.
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.
| Order | Revenue | Touch 1 | Touch 2 | Touch 3 | Touch 4 | Touches | Last-click channel | First-click channel |
|---|---|---|---|---|---|---|---|---|
| LO-1001 | €148 | Display | Paid Search | 2 | Paid Search | Display | ||
| LO-1002 | €89 | Paid Search | 1 | Paid Search | Paid Search | |||
| LO-1003 | €236 | Paid Social | Organic Search | 3 | Paid Social | |||
| LO-1004 | €312 | Display | Paid Social | Paid Search | 4 | Display | ||
| LO-1005 | €64 | Organic Search | 1 | Organic Search | Organic Search | |||
| LO-1006 | €179 | Paid Social | 2 | Paid Social | ||||
| LO-1007 | €425 | Display | Organic Search | Paid Search | 3 | Paid Search | Display | |
| LO-1008 | €118 | 1 | ||||||
| LO-1009 | €274 | Paid Social | Paid Search | 2 | Paid Search | Paid Social | ||
| LO-1010 | €96 | Organic Search | 2 | Organic Search | ||||
| LO-1011 | €358 | Display | Paid Social | 3 | Display | |||
| LO-1012 | €141 | Paid Search | Organic Search | 2 | Organic Search | Paid Search | ||
| Total | €2,440 | 26 |
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.
- 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. - 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. - 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. - 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.
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:
| Channel | Orders closed | Last-click revenue | Orders opened | First-click revenue | First-click minus last-click |
|---|---|---|---|---|---|
| Paid Search | 4 | €936 | 2 | €230 | −€706 |
| Paid Social | 0 | €0 | 3 | €689 | +€689 |
| Display | 0 | €0 | 4 | €1,243 | +€1,243 |
| 6 | €1,299 | 1 | €118 | −€1,181 | |
| Organic Search | 2 | €205 | 2 | €160 | −€45 |
| Total | 12 | €2,440 | 12 | €2,440 | €0 |
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.
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:B15becomesB5:B16when 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
| Function | What it does | Used here for |
|---|---|---|
COUNTA(range) | Counts non-empty cells | Number of touches in a path |
INDEX(range, n) | Returns the n-th cell of a row or column | The last filled touch, with n = COUNTA |
SUMIFS(sum_range, criteria_range, criterion) | Adds the values that meet a condition | Revenue per last-click or first-click channel |
COUNTIF(range, criterion) | Counts the cells that meet a condition | Orders closed or opened per channel |
$B$4:$B$15 | Absolute reference, does not move when copied | Data 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.
- Linear Attribution: Equal Credit per Touch, Touch Counts per Channel and the Shift Away from Last-Click
- Position-Based (U-Shaped) Attribution: 40 % First, 40 % Last, the Middle Shared, with IFS on a Touch Log
- Time-Decay Attribution: Half-Life Weights with Powers of 0.5, Normalising per Order and Credit per Channel
- One Table, Five Models: a Channel × Model Matrix with Mixed References, Model Shares and the Spread per Channel
- Channel ROAS Under Each Model: a ROAS Grid, the Lowest and Highest Reading and a Robust / Model-Dependent Verdict
- Lookback Windows: Days Between Touch and Order, Touches Inside the Window and Linear Credit That Respects It
- Credit Shift Analysis: What Changes When You Leave Last-Click, in Euros, in Percent, in ROAS and in Rank
- Path Length and Time to Convert: How Many Orders Are Multi-Touch, How Long Paths Take and What That Means for the Window
- Lookback Window Sensitivity: How Many Touches Each Window Captures, Upper vs Lower Funnel and the Shortest Sufficient Window
- Half-Life Sensitivity: One Path, Eight Half-Lives, from Almost Last-Click to Almost Linear
- Custom Position Weights: a Scenario Grid from 40/20/40 to First-Heavy and Last-Heavy, and Which Channels Care
- Assists and Channel Roles: Last-Click Conversions, Assisted Conversions and the Assist Ratio per Channel
- Path Data Quality Checks: Touches After the Order, Duplicates, Missing Channels and Credit That Does Not Add Up
- Fractional Conversions and CPA by Model: Why 112.8 Orders Is a Valid Number and How Cost per Acquisition Moves
- Budget Reallocation What-If: Revenue-Share Budgets, a ±20 % Cap per Channel and a Cautious Revenue Projection
- The Gap Between Models Over Time: Ratio, Trend with SLOPE, Correlation with Spend and a Forecast
- Do the Models Agree? Ranking Campaigns Under Each Model, Rank Spread and Rank Correlation
- Model × Window Scenario Grid: 36 Ways to Measure One Channel, Break-Even ROAS and the Share of Profitable Scenarios
- Final Project: an Attribution Dashboard from a Raw Touch Log with Four Models, ROAS Range, Verdict and Chart