Data Cleaning Techniques for Analytics Data, With a Worked Example

Reports built on raw analytics data inherit its problems: bots, test orders, duplicate events, internal visits and inconsistent labels. This guide lists eight cleaning techniques for web and product data, applies them to one month of orders, and shows how to stop the problems at the source.

Why analytics data needs cleaning

Analytics data is collected automatically from browsers, apps and servers. That makes it large and fast, but also messy: bots visit, staff test the checkout, a payment page reloads and sends the purchase twice, one campaign is tagged "LinkedIn" and another "linkedin". Each problem is small; together they can move a conversion rate by several points.

Cleaning means finding these records and removing, fixing or flagging them before you report.

Eight techniques

The table below lists eight techniques for web and product data, with the problem each solves.

Apply in roughly this order, from removing junk to judging outliers.

A worked cleanup: one month of orders

An online shop compares its analytics purchase events with the order database (sample numbers):

• 10,000 purchase events in analytics for the month
• 320 test orders from the staging checkout and QA accounts → removed
• 180 duplicates: the confirmation page fired twice on reload → removed by order ID
• 95 orders placed by staff for internal use → removed by account flag
• 1,400 rows with campaign names in mixed case ("Spring_Sale", "spring_sale") → normalised to lower case, not removed
• 40 refunded orders → kept, flagged with a refund property for revenue reports

Result: 9,405 clean orders, within 0.3% of the database. Before cleaning, analytics overstated orders by about 6%, and the "spring_sale" campaign looked like two smaller campaigns.

The order to apply them

1. Remove what should never be counted: bots, test data, internal traffic.
2. Remove duplicates, by a unique ID.
3. Standardise labels: case, spelling, channel names.
4. Fix time zones and dates.
5. Handle missing values: fill only when the value is certain, otherwise label as "unknown".
6. Review outliers last, after the obvious errors are gone; when unsure whether a value is real, check with the data owner.

Keep the raw data; clean into a new view or report, and write down each rule.

Prevent dirt at the source

• A tracking plan with required properties and allowed values.
• Unique IDs on every order and server event, so duplicates can be dropped.
• Separate projects or flags for staging and test environments.
• One written UTM convention in lower case.

Cleaning on the way in with TapCub

• Bots: known bots and AI crawlers are excluded from human data by default; see Web analytics › Bots.
• Internal traffic and test paths: exclusion rules in Web analytics › Configuration › Web settings match address, path, hostname or user agent; Exclude myself stores your current address. App test devices go in App settings as a device rule.
• Duplicates from servers: server events carry an insert_id (up to 36 characters); if the same ID arrives twice, the first copy wins.
• Spotting problems: All-platform analytics › Data › Data quality shows collector rejects by reason, ingestion warnings, high-cardinality properties and tracking rules that suddenly stopped firing.
• Fixing labels without code: virtual properties in All-platform analytics › Data › Virtual events / props can map messy values into clean ones.

Related: data quality, referral spam and data taxonomy.

See also: What is data preparation? Steps, examples and common mistakes.