Markov chain attribution: the removal effect, step by step

From journeys to a transition matrix

Every customer journey is a path such as Start → Social → Search → Conversion or Start → Email → Null, where Null means the visitor left without buying. Count how often each step occurs across all journeys, then divide each row by its row total. The result is the transition matrix: the probability of moving from one state to the next.

In the example, 50% of journeys start with Search, 30% with Social and 20% with Email. From Search, 25% of visitors convert next, 10% move to Social, 15% to Email and 50% drop out. Every row adds up to 1. Conversion and Null are absorbing states: once a visitor is there, the journey is over.

The conversion probability with MMULT and MINVERSE

Visitors can loop between channels (Search → Email → Search …), so the conversion probability is not a simple product. Split the matrix into Q, the moves between channels (B3:D5), and R, the moves from each channel to Conversion (E3:E5). With the start row s (B2:D2), the exact answer is:

P(conversion) = s × (I − Q)⁻¹ × R

In Excel: =MMULT(MMULT(B2:D2,MINVERSE(MUNIT(3)-B3:D5)),E3:E5) = 29.3%. The fundamental matrix (I − Q)⁻¹ adds up every possible number of steps at once. You get the same result by multiplying the matrix by itself many times, step after step, until the probability stops changing.

The removal effect

To remove a channel, set its whole row to zero: a visitor who would have reached it now goes nowhere, which is the same as Null. Journeys that never touch the channel are not affected. Recalculate the conversion probability, then:

Removal effect = 1 − P(conversion without the channel) ÷ P(conversion with all channels)

Without Search the probability falls from 29.3% to 8.5%, a removal effect of 71.0%. Without Email it falls to 17.0% (41.9%), and without Social to 22.4% (23.4%). One formula handles all three by masking the removed channel inside MINVERSE: $B$3:$D$5*($A$3:$A$5<>A11) zeroes that row of Q, and the same mask zeroes its entry in R.

Worked example: three channels, removal effects and credit

Rows 2 to 5 hold the transition probabilities (yellow inputs, every row adds up to 1). E7 is the conversion probability of the full chain. Rows 11 to 13 remove one channel each and turn the drop into a removal effect, a share and credited conversions.

Worked example: three channels, removal effects and credit

	A	B	C	D	E	F
1	From \ to	Search	Social	Email	Conversion	Null
2	Start	0.50	0.30	0.20	0.00	0.00
3	Search	0.00	0.10	0.15	0.25	0.50
4	Social	0.30	0.00	0.10	0.05	0.55
5	Email	0.20	0.05	0.00	0.30	0.45
6
7	P(conversion), all channels				29.3%
8	Conversions to credit				1,200
9
10	Channel	P(conv.) without	Removal effect	Share	Credited conv.
11	Search	8.5%	71.0%	52.1%	625
12	Social	22.4%	23.4%	17.2%	206
13	Email	17.0%	41.9%	30.7%	369
14	Total		136.2%	100.0%	1,200

Fictional transition probabilities. In practice they come from counted journeys divided by row totals. MMULT and MINVERSE return arrays, but here every result is a single cell.

From removal effects to credited conversions

The removal effects add up to 136.2%, more than 100%, because many conversions need more than one channel. Removing either of two channels would lose the same conversion, so both claim it. To share out the real conversions, normalise:

Share = removal effect ÷ sum of all removal effects

Search gets 71.0% ÷ 136.2% = 52.1%, Email 30.7% and Social 17.2%. Multiply by the 1,200 conversions in the period and the credit is 625, 369 and 206. Multiply by revenue instead and you get attributed revenue, the basis for channel ROAS. The ROAS vs ROI guide shows what to do with it next.

Markov vs last click

Last-click attribution gives the whole conversion to the final touch. Channels that start or assist journeys, such as Social here, often look worthless under last click even when removing them would cost conversions. Markov attribution uses the full paths, so assisting channels get credit in proportion to how much the chain depends on them. Channels that often close journeys usually lose some credit compared with last click.

Limits of Markov attribution

A first-order Markov chain only remembers the current channel, not the whole history. The removal effect is a model answer, not an experiment: it shows how conversions depend on a channel within the observed paths, not what would truly happen if you switched it off. Few journeys make the probabilities noisy, so check how stable the shares are from month to month, and use holdout tests or incrementality experiments before moving large budgets.

Common mistakes

• Rows that do not add up to 1. Each row of the transition matrix is a probability distribution. If a row adds up to 0.95, journeys are missing, usually the Null endings.
• Forgetting the Null state. Without non-converting journeys every path ends in a conversion and every channel looks perfect.
• Using raw removal effects as shares. They add up to more than 100% (136.2% here). Normalise them before you credit conversions or revenue.
• Removing a channel only from the start row. The channel's own row in Q and its conversion probability in R must be zeroed as well.
• Wrong matrix sizes in MMULT. The start row needs as many columns as Q has rows. A mismatch returns #VALUE!.
• Reading the credit as causal. The removal effect describes the observed paths. Test real budget shifts with holdout or incrementality experiments.