Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

Marketing Analytics and Reporting › Advanced

Auditing Campaign Data Quality for a Coach Tour Operator

An invented coach tour operator selling in several countries spends days each month assembling a report whose totals never match. You audit an extract of its campaign and analytics data, log each failure with its root cause and owner, write a taxonomy and naming rules that every platform follows, set weekly checks and define how market results are converted and consolidated.
ExerciseAdvanced levelModule 3: Data quality and reporting at scale5 minUpdated

Why this matters

A report that takes days to assemble is a symptom, and patching it by hand guarantees the same patching next month. The marketing data quality guide gives the failure catalogue and the weekly routine; the guide to data operations and taxonomies gives the taxonomy and controls at the point of creation. At scale, failures cross platforms, currencies and teams, so most fixes need an owner outside analytics.

The operator and its monthly scramble

Coachlark Tours is an invented coach tour operator selling escorted holidays to customers in the UK, Ireland, the Netherlands and Germany. Each market has its own ad accounts; one analytics property (GA4) runs in UK time and pounds; bookings are recorded in a booking system with booking date, departure date and sales market.

Illustrative example — not a real result

Coachlark Tours: one month's campaign extract

RowPlatform and accountCurrencyCampaign nameSource / medium / campaign on linksSpendPlatform bookings
ASearch, UKGBPUK_Brand_SearchAuto-tagged4,200310
BSearch, GermanyEURBrand-DE-SearchAuto-tagged2,90095
CSearch, NetherlandsEURnl brand searchAuto-tagged1,10040
DSocial, UKGBPRhine Spring 26facebook / paid_social / rhine-spring3,000120
ESocial, EuropeEURrhine spring DEFacebook / Social / RhineSpring2,50070
FSocial, EuropeEURrhine spring NLfacebook / paid_social / rhine-spring, copied from the UK ad set1,20035
GEmailGBPMarch newsletterTags on 4 of 9 linksn/an/a
HAffiliate networkGBPNetwork defaultAffiliate / affiliates1,80060

What the report showed:

  • Total spend 16,700, adding pounds and euros in one column.
  • German spend converted at the spot rate on report day this month and at the budget rate last month.
  • Row E's sessions in Organic Social; row F's bookings credited to the UK; untagged email visits in Direct; affiliate traffic split across two channel rows.
  • Analytics purchases include 38 duplicates with a repeated transaction ID, from confirmation-page reloads.
  • Marketing reports bookings by booking date; finance reports revenue by departure date.

Budget rate for the year, agreed with finance: €1 = £0.85.

From failure log to root causes

Log every symptom, then ask where it was created: a hand-typed name, a missing rule, a platform default, a tag, or two valid definitions sharing one word. Fix it where it is born, with an owner who works there, and record every fix in a document so it is not undone.

Task

  1. Log each failure with its symptom, root cause, fix at source and owner.
  2. Write the campaign taxonomy and the naming and tagging rules for every platform.
  3. Set the weekly checks, each with an owner and a tolerance.
  4. Define how market results are converted, dated and consolidated, and restate this month's spend.

Worked answer

Failure log.

FailureRoot causeFix at sourceOwner
Names in several styles (A to C)No taxonomy; names typed by handTaxonomy and a name builder in the intake formMarketing operations
Paid social read as organic (E)Medium typed outside a controlled listBuilder with fixed values, lower caseMarketing operations
Dutch bookings credited to the UK (F)Ad set copied without updating tagsLaunch check comparing live tags with the work itemSocial lead
Email in Direct (G)Links tagged by hand in each sendTemplate that tags every link automaticallyCustomer relationship manager
Affiliate split in two (H)Network default differs from oursSet the tracking template once in the networkAffiliate manager
Currencies added togetherReport built by pasting platform exportsRate table in the warehouse; convert on loadAnalyst
Rates change month to monthNo written ruleRule in the measurement planHead of analytics, with finance
Duplicate purchasesPurchase tag fires on reloadFire once per transaction ID; deduplicate in the warehouseAnalytics
Marketing and finance disagreeTwo valid date rules under one nameTwo named metrics: bookings by booking date, revenue by departure dateAnalyst and finance

Taxonomy and rules. Attributes: market, objective, product, audience, launch month, variant, each from a list owned by marketing operations with a change log. Campaign names follow market_objective_product_audience_month in lower case, such as de_prospecting_rhine_families plus the launch month. Search keeps auto-tagging on with the same names; social uses the pattern for campaigns, the audience for ad sets and the variant for ads. Every manual UTM link comes from the builder: source is the platform, medium from a fixed list, campaign is the campaign name exactly, content is the variant. The UTM and naming conventions guide sets case and separator rules.

Weekly checks.

CheckOwnerTolerance
New campaign names that do not parseMarketing operationsNone; fix within the week
Paid sources appearing in organic or unassigned channelsAnalystNear zero
Analytics purchases against booking system bookingsAnalystRatio within the recorded band
Rate table and spend by account currencyAnalystMatches finance's rate file
Conversion settings changed in any platformChannel leadsEvery change logged

Consolidation. Report in pounds at the budget rate for performance against plan, local currency alongside, and reconcile with finance at actual rates separately. Take the market from the booking system, never from visitor location. Date bookings by booking date in UK time for marketing, and revenue by departure date for finance. Keep shared definitions, market targets and data notes, as the guide to measuring and reporting across markets recommends, so each headline KPI compares like with like.

Illustrative example — not a real result

This month's spend restated

Euro accounts: €2,900 + €1,100 + €2,500 + €1,200 = €7,700 × 0.85 = £6,545. Pound accounts: £4,200 + £3,000 + £1,800 = £9,000. Total: £15,545, not the 16,700 the report showed, which added the two currencies as if they were one. German spend alone, €2,900 at the budget rate, is £2,465 in the performance view; finance's reconciliation at its actual monthly rate sits on a separate line, so a currency movement never reads as a change in cost per booking.

Common mistakes

  • Cleaning the report, not the source. Next month needs the same cleaning.
  • A taxonomy nobody owns. New values appear and the parsing check fails silently.
  • Converting at whatever rate is handy. Currency movement then looks like performance.

Bring this governance into the analytics and reporting plan capstone, or revisit metric definitions at the intermediate marketing analytics level.

Pages this lesson applies

Terms used on this page

Also related

Related