Kemang Data

SQL · BigQuery · Data quality

Exhibition Visitor 360

An exhibition organizer kept visitor data in 3 separate systems, and the numbers never matched. We cleaned and merged them in BigQuery into one table with one row per person, then checked the result against the official reports.

  1. 1
    Load3 systems + official report, 5 editions
  2. 2
    Cleanemails, phones, company names, duplicates
  3. 3
    Match peoplesame email or phone = same person
  4. 4
    Checkcompare with official numbers →
6,061unique people
99.0%data quality
≤ 3registration gap
Exhibition Visitor 360 dashboard: 6,061 unique people split into four segments, with show-up rate per segment
Dashboard. 6,061 unique people in 4 segments. Loyal visitors show up 93% of the time.
Reconciliation table comparing raw and clean counts with the official report for each of 5 editions
Reconciliation. Our counts vs the official report, per edition, before and after cleaning.

The problem

What was wrong

  • Registrations (CRM) had duplicates, typo emails, mixed phone formats and company names written many ways.
  • Attendance scans (ERP) had double scans and IDs with different casing or spaces.
  • Campaign events (marketing, nested JSON) had duplicate events and emails in capital letters.
  • Registrations were 126 to 157 higher than the official report in every edition.

Questions nobody could answer

  • How many unique people really came?
  • Who are the loyal visitors that come every year?
  • Which registered people never showed up?
  • Can we trust the numbers in the official report?

What we did

Pipeline (BigQuery SQL)

  • Loaded 4 raw tables and kept them untouched.
  • Wrote reusable SQL functions to clean email, phone and company name.
  • Removed duplicate registrations, scans and events.
  • Gave each person one ID by matching email or phone across all 3 systems.
  • Built the visitor_360 table: editions attended, segment and campaign engagement.

Segments

  • Loyal: attended 3 or more editions (1,294 people).
  • Returning: attended 2 editions (968).
  • New: attended 1 edition (2,435).
  • Registered, no show: never attended (1,364).

Step 2 · Data quality, before and after

Quality score is the average of completeness, validity, uniqueness, consistency and integrity checks.

MeasureBeforeAfter
Data quality score75.5%99.0%
Registration rows12,93412,218
Phones in standard format23%98%
Registration gap vs official report+126 to +157 per edition0 to +3 per edition

All data is synthetic and generated by a script (seed 42). No company data was used.

Step 3 · Matching people across systems

Two records with the same clean email or the same clean phone are the same person. Example from the campaign step: remove duplicate events first, then attach a person ID.

CREATE OR REPLACE TABLE exhibition_cdp.stg_campaign_events AS
WITH dedup AS (
  SELECT * EXCEPT (rn)
  FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY event_id ORDER BY event_timestamp) AS rn
    FROM exhibition_cdp.raw_campaign_events
  )
  WHERE rn = 1
)
-- then clean email and phone, and join to person_id

Checked against the answer key from the data generator: 0% wrong merges, and 99% of people kept whole.

Step 4 · Reconciliation with the official report

Difference between our count and the official number, per edition. Closer to 0 is better.

EditionRegistrations beforeRegistrations afterVisitors beforeVisitors after
NFE2024+126+2+135-12
NFE2025+1570+101-7
NFE2026+144+3+112-12
PPI2025+1430+127-18
PPI2026+153+2+128-14

Registrations now match almost exactly. Visitors end up 7 to 18 below the official figure (about 1%). The remaining gap comes from gate scans with no registration, which cannot be linked to a person. We report it as is instead of hiding it.

Tools

BigQuery (sandbox, free), SQL with CTEs, window functions, user-defined functions and nested JSON, Python (pandas) for the first version of the pipeline, and Data Studio for the dashboard.

See the code on GitHub

Need a dashboard or cleaner data?

Send us a sample of your data and what you want to track. We will tell you what we can build and how long it takes.

Chat me on WhatsAppEmail usUpwork profileGitHubLinkedIn