Data quality audits: catching bots, duplicate IDs and broken pipelines before they skew decisions

A streaming platform once reported a 40% spike in plays for a mid-tier documentary overnight. Nobody had marketed it. Nobody had licensed it to a new territory. The cause, discovered three weeks later: a bot farm was looping the title to farm referral payouts from a regional telecom bundle deal. By the time the anomaly was caught, the title had already been greenlit for a second season based on the fake demand signal. A data qualitydata qualityThe degree to which data is fit for purpose: accurate, complete, consistent, timely, valid and unique. Poor quality data undermines analytics, reporting and AI.View full definition → failure costs more than bad numbers in media; it costs the decisions built on top of them.

This lesson walks through how to audit a content catalog and viewing log the way a data or analytics team should, before those numbers reachreachThe number of unique people exposed to your message in a given period. Unlike impressions, reach counts each person once, no matter how often they see it.View full definition → a greenlight meeting or an investor deck.

Why media data breaks more than other sectors

Media data pipelines are unusually fragile for a few structural reasons:

• High cardinality metadata. A single title can have a dozen IDs (studio ID, distributor ID, platform-internal ID, EIDR, an industry standard identifier for movies and TV episodes) across the supply chain, and they frequently do not match.
• Machine-generated demand. Bots, scrapers and click farms target streaming and ad-tech data because plays, views and impressionsimpressionsThe total number of times an ad or piece of content is displayed, regardless of clicks. Each display counts as one impression, even to the same person.View full definition → often convert directly to money (ad revenue, licensing fees, algorithmic promotion).
• Fragmented pipelines. Viewing logs, catalog metadata and rights data usually live in separate systems (a CMS for content, a CDN for delivery, a BIBITechnologies and processes that turn raw data into actionable insights via reporting, dashboards and analysis, so teams can decide based on facts rather than intuition.View full definition → warehouse for analytics) stitched together by ETLETLETL (Extract, Transform, Load) is a data integration process that pulls data from sources, reshapes it into a consistent format, and writes it into a target system.View full definition → (Extract, Transform, Load) jobs that break silently.

The core datasets you're auditing

Before auditing quality, know what you're actually looking at.

Content catalog data: title metadata (genre, runtime, cast, release date, territory rights, language tracks). Source of truth is usually the studio or the platform's content management system.

Viewing/engagement logs: timestamped events (play start, pause, completion, skip) at the user-session level. This is the rawest, highest-volume dataset, often billions of rows a day for a major streamer.

Rights and licensing data: which territories, windows and platforms a title is cleared for. Errors here create compliance exposure, not just reporting noise.

Ad delivery and measurement data: impressions, viewability, completion rate, verified by third parties like Nielsen in the US or BARB in the UK for linear/streaming convergence measurement.

Step 1: Duplicate ID detection

Duplicate title IDs are the most common catalog error. They happen when the same content is ingested twice, once from a studio feed and once from a regional distributor feed, with different internal keys.

A simple audit query looks like this:

SELECT title_name, release_year, COUNT(DISTINCT internal_title_id) AS id_count
FROM content_catalog
GROUP BY title_name, release_year
HAVING id_count > 1;

If this returns hundreds of rows for a catalog of 20,000 titles, you likely have double-counted engagement metrics: each duplicate ID fragments the true view count across two rows, understating a title's actual performance, or worse, one ID gets promoted in recommendations while the other sits with zero data, skewing personalization models.

Fix: enforce a single canonical identifier. EIDR (Entertainment Identifier Registry) is the closest thing to an industry standard for this, similar in spirit to an ISBN for books.

Step 2: Bot and fraud detection in viewing logs

Bot-inflated plays show up as statistically abnormal patterns:

• Implausibly high plays-per-user-per-hour
• Identical session durations down to the millisecond, across thousands of sessions
• Geographic clustering inconsistent with a title's marketing footprint
• Completion rates near 100% for content types (long documentaries, foreign-language films) that normally show high drop-off

A basic anomaly flag: compare a title's plays-per-unique-device ratio against the catalog median.

median_ratio = df['plays'].sum() / df['unique_devices'].nunique()
title_ratio = title_df['plays'].sum() / title_df['unique_devices'].nunique()

if title_ratio > median_ratio * 5:
    flag_for_review(title_id)

This won't catch sophisticated fraud, but it catches the crude, high-volume cases that actually move business decisions, the ones that get a title renewed or a marketing budget reallocated on false signal.

The ad industry has organized around this problem through the Media Rating Council (MRC), which accredits measurement vendors and defines invalid traffic (IVT) standards, general invalid traffic (obvious bots, crawlers) versus sophisticated invalid traffic (harder to detect, mimics human behavior). As of 2024 industry estimates, ad fraud losses globally have been estimated in the tens of billions of dollars annually (Juniper Research and similar trackers; treat any specific figure as an estimate, methodologies vary widely).

Step 3: Broken pipelinepipelineAll active sales opportunities across the stages of the sales process, together with their combined potential value and probability of closing.View full definition → and missing metadata checks

The quietest failure mode is a dropped field, not fraud. A common scenario: a platform migrates its CDN (Content Delivery Network, the infrastructure that streams video to users) provider, and the new provider's logs don't populate the "device type" field for three weeks. Nobody notices because total play counts look normal. But every report segmentingsegmentingDividing a market into distinct groups of customers who share similar needs, characteristics or behaviours, so each group can be served with a tailored approach.View full definition → by device (mobile vs. connected TV) is now silently wrong for that period.

Governance metrics to track routinely:

These are illustrative targets, not regulatory standards. Every platform should set its own baseline from historical data rather than borrowing a number from a competitor's very different pipeline.

A worked mini-example

Suppose a title shows 1,000,000 recorded plays. Your audit finds:

• 60,000 plays came from 40 devices with identical session durations (bot pattern) → remove
• 80,000 plays are duplicated because the title has two catalog IDs merged late → deduplicate, don't remove, reassign to canonical ID
• 15,000 plays have a null "completion" field due to a three-day pipeline outage → flag as incomplete data, exclude from completion-rate calculations only

Adjusted clean count for fraud purposes: 1,000,000 − 60,000 = 940,000 genuine plays.

Completion rate should be calculated on 940,000 − 15,000 = 925,000 plays with valid completion data, not the raw million.

Reporting the raw 1,000,000 figure to a content acquisitions team overstates true demand by roughly 6% from bots alone, before even touching the metadata gaps. In a business where renewal decisions hinge on relative title performance, a 6% distortion can flip a ranking.

Building the audit into a routine, not a one-off

A one-time audit finds problems. A governance process prevents recurrence. Three practical habits:

1. Automated data contracts: define expected schema, null tolerances and value ranges for every incoming feed, and alert when a feed violates its contract (tools like Great Expectations are free and widely used for this).
2. Reconciliation checkpoints: reconcile catalog counts and play totals across at least two independent systems (e.g., CDN logs vs. billing/ad-serving logs) weekly, not quarterly.
3. Named data ownership: every dataset needs an accountable owner, not just an engineering team on call. Media orgs increasingly appoint a data governance lead specifically because "everyone's job" means no one's job.

🎬 [VIDEO: "How Ad Fraud Works (and How to Stop It)" - youtube.com/results?search_query=how+ad+fraud+works+bots - search this term for current MRC/IAB-aligned explainer content on bot detection in digital media measurement]

Key Takeaways

• Duplicate title IDs silently fragment or double-count engagement data; enforce a canonical identifier standard like EIDR and audit for it regularly.
• Bot-inflated plays follow detectable statistical patterns (abnormal plays-per-device ratios, identical session durations); build automated anomaly flags rather than relying on manual review.
• Missing or null metadata fields from broken pipelines are more dangerous than obvious errors because they pass basic sanity checks while corrupting every downstream segment analysis.
• Set illustrative internal benchmarks (null rates under 1 to 2%, duplicate rates under 0.5%) calibrated to your own historical baseline, not borrowed industry figures.
• Treat data quality as a continuous governance process (data contracts, reconciliation checkpoints, named ownership), not a one-time cleanup before a big report.