What a lead retrieval data model must hold before reporting works

The Monday after the show closes, someone in exhibitor services asks a question that should take two minutes. How many leads did stand H41 capture?

Inside ten minutes you can produce three answers and none of them agree. The lead retrieval vendor's portal says 4,120. The file the exhibitor downloaded on Friday evening says 3,906. The number sitting in the draft post-show pack says 3,180, because whoever built it counted distinct badges. Each of those numbers is correct about something. One of them is an answer to the question that was asked, and nobody in the room can tell you which.

This is rarely a reporting problem. It is a lead retrieval data model that never decided what one row means, and the fault only becomes visible at reporting time, which is the worst possible moment to find it, because by then the exhibitor has already seen a figure.

Declare the grain before you argue about columns

Kimball and Ross set out a four step dimensional design process in The Data Warehouse Toolkit, third edition, published in 2013: select the business process, declare the grain, identify the dimensions, identify the facts. The Kimball Group's published summary of those techniques, drawn from the same edition, describes a transaction fact table as one where a row corresponds to a measurement event at a point in space and time.

Teams skip step two. They go straight from "we need exhibitor reporting" to a list of columns, and the grain ends up implied by whatever the vendor export happened to contain.

For lead retrieval the grain is one row per capture action. Not one row per lead, because a lead is a decision you make later. Not one row per badge, because that throws away the second conversation. One row per time a person on a stand pressed the button, including the times they typed a name in by hand because the visitor had left their badge in the hotel.

Once the grain is a capture action, every question exhibitor services gets asked becomes a filter or a count of distinct values over one table. Total scans is a row count. Unique leads is a distinct count of badge id. Rework is a count of rows with an empty qualifier payload. Nobody has to reconcile three systems, because there is one table and the disagreements become explicit clauses in a query that anyone can read.

What columns does a scan row have to carry?

Nine fields cover almost everything an organiser is asked for.

exhibitor_id. The contracted exhibiting company, not the stand number and not the display name. Stand numbers move between editions and display names get edited by whoever last touched the floorplan.

badge_id. The registration record identifier, kept as a string exactly as it came off the badge. Do not helpfully strip leading zeros.

licence_id. The lead retrieval licence or device the scan came from.

user_id. The named person on the stand who was holding it. These are two different things and collapsing them costs you the ability to see that one person captured most of the file.

captured_at. A timestamp with a timezone, from the device, at the moment of capture.

ingested_at. The moment the row landed in your warehouse. Different from captured_at, sometimes by days.

source. Badge scan, manual entry, business card transcription, third party capture app. Anything badge keyed silently drops manual entries, so you need to know how many there were.

qualifier_payload. Whatever the exhibitor's own question set returned, kept raw.

status. Live, deleted, or superseded, with the timestamp of the change.

That last field is the one that gets argued about, and it is the one I would not give up.

Notice what is absent. There is no grade column and no lead score on the scan row, because both are judgements applied to a capture and both change when the rules change. Put them in their own table keyed on scan id with the version of the rule that produced them, and you can rerun a grading scheme in November without rewriting history. There is also no company name, no job title and no country on the scan row. Those belong to the registration record that badge_id points at, and copying them into the scan table means that when a registration is corrected on Wednesday your scan history still carries Monday's typo. What the exhibitor eventually receives is a different contract again, and the export column set is worth settling on its own terms rather than inherited from this table.

What does the lead retrieval vendor do to your rows?

Cvent's LeadCapture exhibitor guide is worth reading as a data engineering document, because it describes a set of behaviours that break naive schemas.

Leads collected on a device sync to the server after the event, and the guide is explicit that if leads have not synced, the exhibitor admin cannot export them. So captured_at and ingested_at genuinely diverge, and any report generated before the last device reconnects is a partial report presented as a complete one.

The exhibitor portal lets an admin delete a lead so that it no longer appears in reports. If your pipeline does a full reload from the vendor each night, a lead deleted on Tuesday quietly vanishes from Monday's count, and the exhibitor scorecard you sent on Monday no longer reproduces. Soft delete on your side, driven by an explicit status column, keeps the history.

A licence, once consumed, cannot be used on a different device, and staff must already be registered attendees before a licence can be assigned to them. That is why licence_id and user_id belong in separate columns with a join to the registration record.

Booth staff cannot see leads captured by their colleagues in the app. The organiser holds the only complete view of an exhibitor's own file, which is an argument for treating the scan table as a first class asset rather than a pass through.

Counting from one table

Take the stand from the opening. The scan table holds 4,120 rows for exhibitor H41 across four days and fourteen licences.

Of those, 87 carry a deleted status, set by the exhibitor's own admin in the portal. Live rows: 4,033.

The Friday evening export ran at 18:40, when 3,906 live rows had synced. Two devices reconnected on Saturday morning and contributed the remaining 127. That accounts for the gap between the exhibitor's file and the portal, and it is not an error in either.

Distinct badge_id across the 4,033 live rows is 3,180. Repeat rows: 853. Which of those 853 are device retries and which are real second conversations is a windowed deduplication question the schema only has to make answerable. The ratio of scans to distinct badges is 4,033 divided by 3,180, which is 1.27, and what a healthy ratio looks like is a separate argument from whether you can compute it.

Rows carrying at least one qualifier answer: 1,122. That is 27.8 per cent of live rows, which means seven scans in ten arrived at the exhibitor's CRM as a name and a company with no indication of what was discussed.

Scans by licence tells the last part. The busiest licence produced 1,090 rows, 27 per cent of the live file, and four of the fourteen licences produced fewer than 40 rows each.

One more cut is worth running while you are in there, and it is the one that finds broken plumbing. Join the live rows to the registration file on badge_id and count the failures. If 61 of the 4,033 rows point at a badge that does not exist in registration, you have either a badge printed outside the system, a test badge, or a string handling bug in the ingest. Sixty one is small enough to ignore in a headline and large enough to matter when somebody asks why the job function breakdown does not sum to the lead total.

Every one of those figures came from the same table with a different WHERE clause, which is what makes the rest of exhibitor analytics tractable. That is the entire argument for fixing the grain first. The three numbers in the opening are now reconciled rather than debated: 4,120 is every row ever written, 3,906 is the file as it stood at 18:40 on Friday, 3,180 is distinct badges, and the honest headline is 4,033 captures from 3,180 people.

Two design arguments worth having out loud

The first is whether to keep deleted rows. Exhibitors delete leads for reasons that are usually good, such as a staffer testing the device or a competitor scanned by mistake. Deleting the row from your own table means your counts change retroactively and you can never answer why. I would keep every row, mark status, and default every report to live rows only. The cost is a column. The benefit is that a scorecard sent in March still reproduces in June.

The second is what to do with the qualifier payload. The tidy answer is to normalise it into a child table, one row per scan per question, keyed on scan id. The fast answer is to keep it as a document in a single column. Both camps are partly right, and the choice usually gets made by whoever writes the ingest job first.

I would do both, and I would not apologise for the duplication. Keep the raw payload exactly as the vendor sent it, because question sets differ by exhibitor and change mid show, and you will want the original when someone disputes a grade. Then normalise into a child table so that counting scans with at least one answer is a join instead of a parser. Storage is cheap. Re-parsing a season of payloads to answer one question from a sales director in October is not.

Where this stops

A scan table tells you that a capture happened. It cannot tell you whether a conversation happened, and it never will.

Two rows can be identical in every column and represent a real two minute qualifying discussion in one case and a badge waved at a scanner in exchange for entry into a prize draw in the other. No amount of schema design distinguishes those. The qualifier payload is the only signal you get, and CEIR's 2015 study of exhibitor ROI and performance metric practices puts lead generation first among stated exhibiting objectives, which makes the low qualifier completion rate above the interesting number on the page rather than a footnote.

Two further limits are worth stating before anyone builds on this. Badge_id is stable within one event and not across editions, so any cross year question needs identity resolution underneath it, which is its own discipline and not part of this table. And manual entries have no badge_id at all, so every badge keyed metric drops them without saying so. Report the manual share alongside the unique count, or accept that your denominator is quietly wrong for exactly the stands that talked to the most people.

Pull last edition's raw scan export for your five largest exhibitors and count four things: total rows, distinct badge ids, rows with a populated capture timestamp, and rows with no scanning user attached. If the fourth number is anything other than zero, your vendor feed is not carrying user attribution and no amount of reporting work downstream will invent it.