Skip to content
Academy
Marketing Academy · Field Work●Paid Ads
All projects·From: Ad Fraud & Invalid Traffic (IVT) Prevention
MiniAudit· 25 minutes

The Bot Filter: Auditing a Raw Click Log for GIVT

Blue Apron

Objective: Given a raw programmatic click log, apply the lesson's GIVT criteria (known crawlers, data center IPs, pre-fetch loads) to separate filterable invalid traffic from traffic that needs deeper SIVT review.

You're the paid media analyst at Blue Apron reviewing why a display retargeting campaign's CTR looks unusually high this week.

Filter the click log by IP range and user agent, isolate the GIVT rows, and report what share of 'clicks' were never a human at all.

Before you start

What you'll need

Free path (everything below is enough to finish)

Google Sheets
FreeFilter and flag the raw click log by IP range and user agent

Free, handles a few hundred to a few thousand rows without issue

Paid upgrades (optional, faster/deeper)

Google Analytics 4(optional)
FreeCross-check filtered traffic against session quality signals (bounce, engagement time)

Confirms the remaining non-GIVT traffic actually behaves like real sessions

No access? Google Sheets pivot on session duration if GA4 access isn't available

The process

1 step

Step 01 of 01

GIVT filtering by IP range and user agent

The lesson defines GIVT as traffic that identifies itself: known crawlers, data center IP ranges, and browser pre-fetch loads, all catchable with routine list-matching and IP filtering.

This click log has 300 rows. How many are GIVT you can filter out with a simple IP-range and user-agent rule, before you even start looking for SIVT?

Google Sheets— Import click-log-export.csv, add a helper column flagging known data center ASNs and bot user agents.

Procedure

  1. Import click-log-export.csv and freeze row 1
  2. Add a helper column matching the IP column against a known data-center ASN list
  3. Filter the user_agent column for known crawler strings (Googlebot, bingbot, etc.)
  4. Sum the flagged rows to get the GIVT count
Sample output
Total rows: 300
Data-center ASN matches: 41
Known crawler user agents: 9
GIVT total: 50 rows (16.7% of logged clicks)
Remaining 250 rows require SIVT-level review (behavior, device, proxy signals)

Healthy

GIVT is filtered out before any spend or performance conclusion is drawn, the remaining 250 rows are what actually gets analyzed for campaign performance.

Unhealthy

Reporting CTR off the full 300-row log, including the 50 rows that were never a human, inflating the apparent engagement rate.

What this means

GIVT is the easy layer, a list-match catches nearly all of it, but it is only step one, the remaining traffic still needs a SIVT check before you trust it.

So what do I do about it?

SymptomActionEffort
CTR spikes with no creative or targeting changeRun the IP-range and user-agent filter first, GIVT inflation is the fastest thing to rule out5 min
GIVT rate is consistently above 10% on one specific publisher or exchangeAdd that source to a block list and flag it to the media buyer for renegotiation or removal30 min
YouYou can do this yourself, no engineering access required.

Final deliverable

A GIVT-filtered click count with the percentage of raw clicks removed, plus a flag list of any single source over a 10% GIVT rate.

See a reference example
Sample output
HelloFresh, display retargeting click log audit (excerpt)

Raw clicks: 1,240
Data-center ASN matches: 187 (15.1%)
Known crawler UAs: 22 (1.8%)
GIVT total: 209 rows (16.9%)
Flagged source: exchange-partner-14, 34% GIVT rate, recommend blocklist

Success criteria

You're done when you can:

  • Correctly separates GIVT rows using IP range and user-agent criteria
  • States a clean percentage of raw clicks that were GIVT
  • Flags any single traffic source with a disproportionately high GIVT rate