Channel Reallocation analysis

In the Real World · Brief · Data science · Advanced · 3 to 5 days

The budget is fixed and the board wants twenty per cent of it moved to whatever is working. You have ninety days of channel data and a week. Work it yourself before reading the walkthrough.

The situation

Fellrun is a UK running and outdoor retailer. Three acquisition channels carry the media budget, and each has an owner with a slide showing their channel works. The finance director wants one recommendation, with a number attached and a statement of how confident you are in it.

The data

One file, fellrun-channel-performance.csv, 810 rows covering 2026-03-02 to 2026-05-30. It is the channel report as the analytics platform and the media team produce it, joined and exported. It has not been cleaned.

What the room believes

1. Paid social is the weakest channel and should be cut first.
2. Email is the strongest and should absorb the budget.
3. Site conversion is healthy, so the problem is traffic quality rather than the site.
4. The channel report is accurate enough to reallocate on.

Your job includes finding out which of those are true.

Constraints

• The budget is fixed. Every pound added somewhere is taken from somewhere else.
• The file has conversions but no revenue, so any pound figure needs an assumption you state.
• You cannot commission new data for this decision.
• Whatever you recommend, someone will ask how sure you are. Have an interval.

Definition of done

1. A verdict on each of the four beliefs above, with the evidence.
2. A channel ranking you are willing to defend, with intervals rather than point estimates.
3. A reallocation recommendation with a number and a confidence statement.
4. An explicit list of anything that has to be fixed in the reporting, with an owner.
5. A clear statement of what this data cannot tell you, and what you would need instead.

If you want to go further

• Before recommending a budget move, check whether the data can price the next pound at all. Look at how much spend varies and how much of the conversion count it explains.
• If it cannot, design the test that would answer it, and size it.
• Work out which of your corrections change the recommendation on their own, and which only matter in combination.

In the Real World · Data science · Advanced · 3 to 5 days

The board wants twenty per cent of the media budget moved to whatever is working. The channel report is unambiguous: paid social converts worst, so cut it. Three corrections later the same data says paid social is the strongest channel in the business, and the reallocation everyone proposed would have been backwards.

Section 1Problem Definition

No code yet. A reallocation decision is one of the few analyses where being wrong is immediately expensive, because the money moves and the channel you starved takes months to recover.

Business objective

Move roughly twenty per cent of media spend toward the channels that earn it. The budget is fixed, so every pound added to one channel comes out of another. The finance director wants the recommendation with a number attached and a statement of how confident it is.

Problem statement

Using 90 days of channel performance between 2026-03-02 and 2026-05-30, decide where the next pound of media spend should go.

Success metrics

What the business already believes

1. H1. Paid social is the weakest channel and should be cut first.
2. H2. Email is the strongest and should absorb the budget.
3. H3. Site conversion is healthy at around 5.6%, so the problem is traffic quality, not the site.
4. H4. The channel report is accurate enough to reallocate on.

Assumptions

Three of those four are not really assumptions, they are corrections. That is the first sign that this analysis is going to be about the data before it is about the decision.

Section 2Data Collection

import numpy as np
import pandas as pd
from scipy import stats

d = pd.read_csv('data/fellrun-channel-performance.csv',
                parse_dates=['session_date', 'spend_date'])

print('rows      :', len(d))
print('days      :', d['session_date'].nunique())
print('channels  :', sorted(d['channel'].unique()))
print('devices   :', sorted(d['device'].unique()))
print('currencies:', sorted(d['spend_currency'].unique()))

rows      : 810
days      : 90
channels  : ['checkout.stripe.com', 'email', 'fellrun.co.uk', 'organic', 'paid_social', 'paypal.com']
devices   : ['desktop', 'mobile']
currencies: ['EUR', 'GBP']

Read that channel list again before going any further. Six values, and three of them are checkout.stripe.com, paypal.com and fellrun.co.uk, which is the company’s own website. Two currencies in a file that reports one budget.

Section 3Data Preprocessing

Three faults in this file, and each one on its own is enough to reverse the recommendation. That is unusually bad luck, and it is also completely typical of channel reporting, which is assembled from more systems than anyone maintains.

3aDuplicates and schema checks

print('exact duplicate rows :', d.duplicated().sum())
print('missing cells        :', d.isna().sum().sum())

# one row per session_date, channel and device is the intended grain
grain = d.groupby(['session_date', 'channel', 'device']).size()
print('grain violations     :', (grain > 1).sum())

exact duplicate rows : 0
missing cells        : 0
grain violations     : 0

Clean on all three, which is worth reporting because it makes the next finding harder to dismiss. Nothing here is a data entry problem. Everything here is a definition problem.

3bHandling categorical mess

The channel column contains three things that are not channels.

by_channel = d.groupby('channel').agg(sessions=('sessions', 'sum'),
                                      conversions=('conversions', 'sum'),
                                      spend=('spend', 'sum'))
by_channel['cvr'] = 100 * by_channel['conversions'] / by_channel['sessions']
print(by_channel.round(2).to_string())

                     sessions  conversions     spend    cvr
channel
checkout.stripe.com    12,164        2,550      0.00  20.96
email                 126,570        6,025 44,530.24   4.76
fellrun.co.uk          12,903        2,711      0.00  21.01
organic               125,430        5,005 45,283.34   3.99
paid_social           127,721        4,488 45,645.66   3.51
paypal.com             12,082        2,557      0.00  21.16

Of 23,336 conversions in the channel report, 7,818 come from sources that are not acquisition channels at all.

Three sources converting at around 21% while the real channels sit near 4%. A rate five times the site average is not a great channel, it is a measurement artefact, and the names say exactly what happened. A customer goes to Stripe or PayPal to pay, comes back, and the analytics platform records the return as a brand new session from a new source. The site’s own domain appears for the same reason, from a redirect somewhere in the checkout.

# payment gateways and your own domain are not acquisition channels
POLLUTION = ['checkout.stripe.com', 'paypal.com', 'fellrun.co.uk']
real = d[~d['channel'].isin(POLLUTION)].copy()

print('conversions removed :', d['conversions'].sum() - real['conversions'].sum())
print('real site CVR       :',
      round(100 * real['conversions'].sum() / real['sessions'].sum(), 3))

conversions removed : 7,818
real site CVR       : 4.087

The permanent fix is a referral exclusion list in the analytics property, not a filter in the analysis. Every report anyone runs against this property has the same fault.

3cDealing with outliers

There are no outlying values to trim. The outlier here was categorical, and 3b removed it. What is worth checking is whether any single day distorts a channel.

daily = real.groupby(['session_date', 'channel'])[['sessions', 'conversions']].sum()
daily['cvr'] = 100 * daily['conversions'] / daily['sessions']
print(daily.groupby('channel')['cvr'].describe()[['min', '25%', '50%', '75%', 'max']]
           .round(2).to_string())

The daily rates sit in a tight band with no single day carrying a channel. So the aggregate figures are safe to work with, and the disagreement to come is not caused by one unusual Tuesday.

3dHandling missing values

missing cells : 0

None, in any column. Worth one line and no more.

3eHandling skewed data

Daily spend per channel runs between roughly 328 and 653 pounds, a range of about 1.8 times from the smallest day to the largest. There is no meaningful skew to correct.

Note that range rather than dismissing it. A spend range under two times is the single most consequential fact in this project, and section 7d is where it decides the recommendation.

3fData types and normalisation

The currency

print(d.groupby(['channel', 'spend_currency']).size().unstack(fill_value=0).to_string())

FX = 0.86   # GBP per EUR
real['spend_gbp'] = np.where(real['spend_currency'] == 'EUR',
                             real['spend'] * FX, real['spend'])

One channel, paid social, reports spend in euros. Everything else is in pounds. Nobody converted, so the channel report has been adding euros to pounds for as long as it has existed.

Its cost per acquisition was overstated by 16.3%. Corrected, paid social costs 8.75 pounds per conversion against organic’s 9.05. The channel the business was about to cut for being expensive is cheaper than one it was keeping.

The date join

real['lag_days'] = (real['spend_date'] - real['session_date']).dt.days
print(real['lag_days'].value_counts().sort_index().to_string())

# joining spend on the wrong date moves money between days
by_session = real.groupby(['session_date', 'channel'])['spend_gbp'].sum()
by_spend   = real.groupby(['spend_date',   'channel'])['spend_gbp'].sum()
both = pd.concat([by_session.rename('a'), by_spend.rename('b')], axis=1).dropna()
print('channel-days that disagree :', (both['a'] != both['b']).sum(), 'of', len(both))
print('mean absolute difference   :', round((both['a'] - both['b']).abs().mean(), 2))

lag_days
0    360
1    180
channel-days that disagree : 90 of 180
mean absolute difference   : 239.57

33.3% of rows have spend recorded a day after the sessions it bought. Join on spend_date and 90 of 180 channel-days carry the wrong number, by 239.57 pounds on average and up to 652.63. Over ninety days the totals wash out, which is exactly why nobody caught it. Any daily or weekly view is wrong.

Section 4Exploratory Data Analysis

4aThe primary metric

Pollution removed and currency corrected, the ranking by conversion rate is Email then Organic then Paid social. The intervals do not overlap, so the ordering is not noise. H1 and H2 look confirmed: cut paid social, feed email.

That conclusion is wrong, and the next two sections are why.

4bNumerical variables

Sessions and spend are stable across the window. Nothing in the numeric columns explains anything on its own, which is the honest report. The structure is in the categoricals.

4cCategorical variables

Split the same three channels by device.

cell = real.groupby(['channel', 'device']).agg(
    sessions=('sessions', 'sum'), conversions=('conversions', 'sum'),
    spend=('spend_gbp', 'sum')).reset_index()
cell['cvr'] = 100 * cell['conversions'] / cell['sessions']
cell['cpa'] = cell['spend'] / cell['conversions']
print(cell.pivot(index='channel', columns='device', values='cvr').round(3).to_string())

device       desktop  mobile
channel
email          5.587   2.393
organic        5.313   3.039
paid_social    6.060   2.966

Conversion rate by channel, split by device, with the all-traffic figure alongside. Every channel converts roughly twice as well on desktop, and the ordering is different on every one of the three views.

This is Simpson’s paradox, and it is not a curiosity. A twenty per cent budget move made on the top table would take money from the best desktop channel the business has and give it to the worst mobile one.

4dRelationships between variables

A reversal like that always has a mechanism. Here it is the confounder sitting in plain sight: the channels are not buying the same traffic.

mix = real.pivot_table(index='channel', columns='device',
                       values='sessions', aggfunc='sum')
mix['mobile_share'] = mix['mobile'] / (mix['mobile'] + mix['desktop'])
print(mix.round(3).to_string())

device       desktop  mobile  mobile_share
channel
email          93,811  32,759         0.259
organic        52,477  72,953         0.582
paid_social    22,607 105,114         0.823

Mobile share of sessions by channel, a spread of 56.4 percentage points from one end to the other.

Paid social is 82.3% mobile. Email is 25.9% mobile. Mobile converts at roughly half the desktop rate on every channel, so a channel that is mostly mobile carries a handicap that has nothing to do with the quality of its traffic.

The aggregate table was not measuring channel quality. It was measuring device mix.

4eTesting our hypotheses

Four for four. That is unusual and it is worth saying plainly rather than softening: the channel report is not slightly off, it is pointing the wrong way.

4fSubgroups

If device mix is doing the damage, the fix is to compare the channels on the same mix. Standardise each channel’s rates onto the site’s overall device split.

The ranking inverts end to end: Paid social then Organic then Email, against the reported Email then Organic then Paid social. Compared like for like, paid social is the best-converting channel Fellrun has, and email is the worst.

Section 5Metric Construction

def wilson(k, n, z=1.96):
    """Wilson score interval. Behaves at small counts, unlike the normal approximation."""
    p = k / n
    d = 1 + z * z / n
    c = p + z * z / (2 * n)
    h = z * np.sqrt(p * (1 - p) / n + z * z / (4 * n * n))
    return (c - h) / d, (c + h) / d


# direct standardisation: each channel, on the site's overall device mix
overall = real.groupby('device')['sessions'].sum()
weights = overall / overall.sum()

rates = cell.set_index(['channel', 'device'])['cvr']
standardised = {}
for ch in ['paid_social', 'organic', 'email']:
    standardised[ch] = sum(weights[dev] * rates[(ch, dev)]
                           for dev in ['desktop', 'mobile'])

for ch, rate in sorted(standardised.items(), key=lambda kv: -kv[1]):
    print(f'{ch:12s} standardised {rate:.3f}%')

paid_social  standardised 4.342%
organic      standardised 4.050%
email        standardised 3.814%

Section 6Method Selection

Section 7Analysis and Validation

7aThe baseline the business believes

Cut paid social, move the money to email. It converts at 3.51% against email’s 4.76% and it costs 10.17 a conversion against 7.39. On the report as circulated, that is the obvious call, and anyone who made it was reading the numbers correctly.

The numbers were wrong. Beating that baseline means showing exactly which numbers and by how much, in the order the errors compound.

7bThe analysis

The three corrections, in order

Notice that the pollution correction alone does not change the ranking. It changes the site conversion rate and every target built on it, but the three channels move together. It takes the currency correction to fix the cost comparison and the standardisation to fix the rate comparison. Any one of the three on its own leaves you with a confident wrong answer.

The gap that the recommendation rests on

ps  = cell[cell['channel'] == 'paid_social'].set_index('device')
psd = ps.loc['desktop']
psm = ps.loc['mobile']

# bootstrap the desktop minus mobile gap from the two binomials
rng = np.random.default_rng(20260822)
boot = [100 * (rng.binomial(psd['sessions'], psd['cvr'] / 100) / psd['sessions']
             - rng.binomial(psm['sessions'], psm['cvr'] / 100) / psm['sessions'])
        for _ in range(4000)]
gap = psd['cvr'] - psm['cvr']

print(f"desktop {psd['cvr']:.3f}%   mobile {psm['cvr']:.3f}%")
print(f"gap {gap:.3f}pp   "
      f"95% CI [{np.percentile(boot, 2.5):.3f}, {np.percentile(boot, 97.5):.3f}]")

desktop 6.060%   mobile 2.966%
gap 3.094pp   95% CI [2.776, 3.418]

A gap of 3.09 percentage points with an interval of 2.78 to 3.42. That is a solid finding, and it is the one number in this project precise enough to plan against.

The reallocation the evidence supports

Paid social currently sends 17.7% of its sessions to desktop, the device it converts on. That is where the reallocation is, and it is inside one channel rather than between three.

Moving paid social’s desktop share from 17.7% to 40.0% is worth about 881 extra conversions across ninety days, roughly 59,900 pounds at the assumed order value.

7cSensitivity and robustness

Two assumptions carry weight. Vary both further than anyone would defend.

Email stays the cheapest channel at every exchange rate, so that part of the ranking is robust. But paid social and organic swap places between 0.86 and 0.90: at the strong end of the range paid social costs 9.15 against organic’s 9.05. That ordering is not safe to build a decision on, and the fix is to get the actual booked rate from finance rather than to pick a number.

Order value scales the prize and changes nothing about the decision. The conversion finding does not depend on it at all.

7dWhat would change the answer

The board asked for a reallocation, which is a causal question. Everything above is observational. So the last job is to check whether this data can support a return-on-spend estimate at all.

for ch in ['paid_social', 'email', 'organic']:
    s = real[real['channel'] == ch].groupby('session_date').agg(
        spend=('spend_gbp', 'sum'), conv=('conversions', 'sum'))
    lin = stats.linregress(s['spend'], s['conv'])
    print(f'{ch:12s} spend {s["spend"].min():.0f} to {s["spend"].max():.0f} '
          f'({s["spend"].max()/s["spend"].min():.2f}x)  '
          f'r2 {lin.rvalue**2:.4f}  p {lin.pvalue:.4f}')

email        spend 369 to 653 (1.77x)  r2 0.0741  p 0.0094
organic      spend 360 to 648 (1.80x)  r2 0.1522  p 0.0001
paid_social  spend 328 to 589 (1.79x)  r2 0.0091  p 0.3724

The share of daily conversion variation that spend explains. The best of the three reaches 0.1522, on spend that never varies by more than 1.8 times.

So the deliverable changes shape. The corrected ranking is solid and worth acting on. The return on the next pound is not knowable from this file, and the correct response is to create the variation rather than to model its absence: run a geo split or a staged budget test, get spend to move by three or four times in one arm, and measure it.

Section 8Decision and Handoff

Fix the reporting first, and who owns each

What to do about the budget

1. Move nothing between channels yet. The ranking that justified the move was an artefact, and the corrected ranking does not on its own tell you the return on the next pound.
2. Shift paid social toward desktop placements, from 17.7% of sessions toward 30.0%, in steps, watching the rate as you go. The ceiling is around 881 extra conversions a quarter and the realistic figure is below it.
3. Run a budget test to answer the question that was actually asked. A geo split or a staged increase that moves spend by three or four times in one arm. That is the only way to price the next pound, and it takes weeks rather than the afternoon the board hoped for.
4. Get the booked exchange rate from finance. The paid social against organic cost ordering flips inside the plausible range, so that comparison is unsafe until the real rate is known.

What not to do

• Do not fit a media mix model to this data. Spend varies by less than 1.8 times and explains at most 15.2% of daily conversions. A model would return a confident number containing no information.
• Do not report the site conversion rate as 5.6%. It is 4.09%. Anyone with a target built on the first number has an easier job than they think.
• Do not compare channels on an aggregate rate again while device mix varies by 56.4 percentage points across them.

Reproducibility

What to take from this

• A conversion rate five times the site average is a bug, not a channel. The name in the source column usually tells you which bug.
• Never compare rates across groups with different mixes. Split by the confounder or standardise on it. Here the mix varied by 56.4 points and reversed every ranking in the report.
• Currency is a unit, and units belong in the column name. A channel report that sums two currencies will do it silently for years.
• Check whether your data can answer the question before you answer it. Spend that varies by 1.8 times cannot price the next pound, and no method fixes that.
• Three faults compounded. Fixing any one alone would still have produced the wrong recommendation, which is an argument for auditing the whole pipeline rather than the number that looks odd.

The board wanted a budget decision in a week. What it got was a corrected report, a reallocation inside one channel worth about 486 conversions a quarter, and the news that the question it actually asked needs a test rather than an analysis. Two of those three are more useful than the answer it wanted.