How to Build a Historical Baseline to Spot Abnormal Affiliate Referral Patterns

To build a historical baseline for spotting abnormal affiliate referral patterns, calculate a 90-day rolling average of referrals, conversions, and revenue per affiliate and flag any affiliate that deviates more than 2 standard deviations from its own baseline.

What You Will Build

You will create a per-affiliate reference model that tracks their typical referral volume, conversion rate, and revenue over a rolling 90-day window. Any day or week where an affiliate’s metrics fall outside two standard deviations from their average triggers a review. This method accounts for organic growth, seasonality, and campaign changes without manual guesswork.

Prerequisites

Before you start, you need three things:

• Clean referral data – a log of every affiliate referral with at least a timestamp, affiliate ID, conversion status, and revenue. Minimum 90 days of history.
• Tool access – a spreadsheet (Google Sheets, Excel) or a database query tool (SQL, Python) to compute rolling averages and standard deviations.
• Business rules – decide how you will handle new affiliates (no baseline yet) and affiliates with very low volume (statistical noise).

Step 1: Define the Metrics

Pick the metrics that matter most to your program. The three essential ones are:

• Number of referrals – total clicks or visits sent per day.
• Conversion rate – percentage of referrals that result in a sale or lead.
• Revenue per referral – average order value or commission generated.

You can also add secondary metrics like time-of-day concentration or device breakdown, but start with these three.

Step 2: Choose the Baseline Window

A 90-day rolling window is the standard for most affiliate programs. It smooths out weekly cycles and short-term promotions while still reacting to long-term trends. If your sales cycle is longer (e.g., B2B), use 180 days. If your program is very seasonal, you may need a year-over-year comparison instead.

Important: roll the window forward each day. Do not use a fixed calendar quarter – that would ignore recent changes.

Step 3: Calculate Rolling Average and Standard Deviation

For each affiliate and each day, compute:

• Rolling average – the mean of the metric over the last 90 days.
• Rolling standard deviation – the spread of the metric over the same period.

In a spreadsheet, use AVERAGE and STDEV.P (or STDEV.S for sample) with a sliding range. In SQL, use window functions like AVG() OVER (ORDER BY date ROWS BETWEEN 89 PRECEDING AND CURRENT ROW).

Step 4: Set the Threshold

Flag any affiliate where the current day’s metric is more than 2 standard deviations above or below the rolling average. This is a common statistical threshold that catches roughly 5% of normal fluctuations – so you will get some false positives, which is fine because you review them manually.

For programs with high fraud risk, tighten to 1.5 standard deviations. For low-risk programs, use 3 standard deviations to reduce noise.

Step 5: Automate the Monitoring

Set up a daily or weekly report that lists every affiliate that crossed the threshold. Include the metric, the baseline value, the actual value, and the number of standard deviations away. Automate this in your spreadsheet with a flag formula, or use a simple dashboard.

Send the list to the person responsible for affiliate quality – do not auto-block affiliates without human review.

How to Verify a Flagged Affiliate

When an affiliate appears on your anomaly list, verify the cause before taking action. Check:

• Timing – did the spike happen at the same time as a coupon extension or browser plugin injection? (See the coupon extension abuse guide for how these hijack referrals.)
• Referrer URLs – are the clicks coming from a suspicious domain or a known coupon site?
• Conversion quality – do the converted users actually complete a purchase, or do they bounce after checkout?
• Click speed – are clicks happening faster than a human could realistically browse?

If the anomaly is a real outlier (e.g., a viral post), you can update the baseline or adjust the threshold. If it matches fraud patterns, block the affiliate and request a refund if applicable.

Scope and Definition

This method is designed for affiliate programs that track individual referrals with a unique ID. It works best when you have at least 90 days of data per affiliate and a minimum of 30 referrals per month to get a reliable standard deviation. For very small affiliates, use a program-wide baseline instead.

Key Facts About Affiliate Referral Hijacking

Source: BotRefund blog on coupon extension abuse (S1).

Limitations and When This Advice Doesn’t Apply

• New affiliates – they have no baseline. Use a program average for the first 30 days, then switch to their own rolling window.
• Seasonal businesses – a 90-day window will miss annual patterns. Use a year-over-year comparison instead.
• Low-volume affiliates – standard deviation becomes unreliable. Group them into a “tier” and use a shared baseline.
• Promotional periods – a planned campaign may legitimately spike referrals. Exclude those days from the baseline calculation or mark them as known events.
• Cookie overwrite fraud – this method will flag the hijacker affiliate, but it won’t tell you that the referral was stolen. You need client-side timing data (like BotRefund provides) to prove the override.

Terminology

Rolling average

The mean of a metric over a sliding window of time, recalculated each day.

Standard deviation

A measure of how spread out the numbers are around the average. Two standard deviations capture about 95% of normal variation.

Cookie override

When a browser extension or script replaces the original affiliate tracking cookie with its own, stealing credit for the sale.

Anomaly

A data point that falls outside the expected range (more than 2 standard deviations from the baseline).

Frequently Asked Questions

How often should I update the baseline?

Recalculate the rolling average and standard deviation daily. The flagging report can run daily or weekly depending on your program volume.

What if an affiliate has a legitimate seasonal spike?

Exclude known promotional periods from the baseline window, or use a year-over-year comparison for that affiliate. You can also add a note to the review process to ignore planned campaigns.

How do I handle new affiliates with no history?

Use the program-wide average for the first 30 days, then switch to their own 90-day rolling window once they have enough data.

Can this method detect coupon extension abuse?

Yes – if a coupon extension hijacks a referral, the affiliate ID for that extension will show a sudden spike in referrals and conversions. But the baseline alone won’t prove it was a hijack. You need client-side tracking (like BotRefund) to see the timing of the cookie write.

What tool do I need to build this baseline?

A spreadsheet like Google Sheets or Excel is sufficient for programs with up to a few hundred affiliates. For larger programs, use SQL or a BI tool like Tableau.

How many standard deviations should I use?

2 standard deviations is the standard starting point. Use 1.5 if you see a lot of fraud; use 3 if you have a low-risk program and want fewer false positives.