A weighted completeness score that reflects what each field is worth

The audience team scored every registration for completeness out of five fields, one point each. A record with badge identifier, company, country and job title but no phone scored 0.80. So did a record with badge identifier, phone, country and job title but no company.

Those two records are not equally useful and everyone knew it. The first can go into every published output the show has. The second cannot appear in the exhibitor scorecard, which is built on company. A weighted completeness score is what separates them.

The fix is about ten lines of SQL. The arithmetic is easy. The weights are the work, and they are a business decision that has to be written down.

Where the weights come from

From the usage count, and from nowhere else if you can help it.

Count how many published outputs read each field, which is the ranking exercise in which registration fields actually matter, and use those counts as the weights. That gives the score a property that matters more than elegance: when somebody asks why company is worth three times phone, the answer is a list of six reports rather than an opinion.

The alternative approach, where a working group assigns importance scores from experience, produces weights nobody can audit and weights that drift with whoever is in the room. Black and Van Nederpelt, writing the DAMA NL guidance on selecting data quality dimensions in 2020, put the general principle plainly: requirements for data quality are context dependent and should be established by their stakeholders, and quality should not simply be as high as possible in all cases, because unnecessary costs may be incurred. Weights are how that judgement gets recorded so it can be argued with.

The framing is older than the vocabulary. Ballou and Tayi called their 1989 paper in Communications of the ACM a methodology for allocating resources for data quality enhancement, which is the right way round: you have a fixed amount of steward time and you are deciding where it goes.

Small integers beat decimals

Use whole numbers between one and three, or one and five at the most. The score divides by the total, so the scale cancels out, and small integers survive a conversation with a show director in a way that 0.2727 does not.

Here is a working set for a registration file, and I want to be explicit that these are illustrative rather than measured.

The badge identifier gets 3. Every output reads it and a record without one cannot be joined to anything.

Company gets 3. On most shows it feeds the exhibitor scorecard, the account view, the company size distribution and the new against returning split.

Country gets 2. It feeds the geographic breakdown and the audit submission, and it has an external obligation behind it, though only two outputs read it.

Job title gets 2. It feeds the job function mix and the seniority view, both of which matter to exhibitors deciding whether the audience is theirs.

Phone gets 1. Sales use it. No published report reads it.

The weights total 11, which is the denominator every record divides by. An odd total is mildly useful, because it stops records landing exactly on 0.5 and gives you fewer ties to break.

Scoring four records by hand

Record A holds all five fields. Its score is 11 over 11, or 1.00.

Record B holds everything except phone. Its filled weight is 3 plus 3 plus 2 plus 2, which is 10. Its score is 10 over 11, or 0.91.

Record C holds everything except company. Its filled weight is 3 plus 2 plus 2 plus 1, which is 8. Its score is 8 over 11, or 0.73.

Record D holds only the badge identifier and phone. Its filled weight is 4, and its score is 4 over 11, or 0.36.

Records B and C both have exactly one field missing, and under the old unweighted count both scored 0.80. Under the weights they sit at 0.91 and 0.73, eighteen points apart, and the eighteen points say something true: one of these records is publishable and the other one is missing the field that four outputs depend on.

That single comparison is the case for the whole method. Everything else is bookkeeping.

What does the distribution look like?

Bimodal, on every registration file I have scored, and the mean alone will hide that from you.

Take a population of 28,400 registrations scored on the weights above.

9,400 records score 1.00. 8,900 score 0.91. 2,600 score 0.82. 3,100 score 0.73. 2,300 score 0.64. 1,500 score 0.55. The remaining 600 score 0.36 or below.

The mean is the total filled weight over the total available weight. Total filled weight is 9,400 times 11, plus 8,900 times 10, plus 2,600 times 9, plus 3,100 times 8, plus 2,300 times 7, plus 1,500 times 6, plus 600 times 4. That comes to 103,400 plus 89,000 plus 23,400 plus 24,800 plus 16,100 plus 9,000 plus 2,400, which totals 268,100.

Total available weight is 28,400 times 11, which is 312,400.

The mean score is 268,100 over 312,400, or 0.858.

Now the share at or above 0.80, which is 9,400 plus 8,900 plus 2,600, or 20,900 records. Against 28,400 that is 73.6 per cent.

Those two numbers do different jobs. The 0.858 goes on a trend line and tells you next quarter whether the file is improving. The 73.6 per cent sizes the queue, because the other 26.4 per cent, 7,500 records, are the ones that cannot go into a published output without enrichment.

Report both. A mean of 0.858 with 73.6 per cent above threshold is a very different file from a mean of 0.858 where every record sits between 0.82 and 0.91, and the mean cannot tell them apart.

Why not weight by revenue or by anything else?

Because you would be building a different measurement and calling it completeness.

Weighting by the revenue attached to a record is tempting, and it produces a score that answers a commercial question: how complete is the data on the registrations we care about most? That is worth knowing. It is a segmented completeness measure, and the honest way to produce it is to run the same weighted score separately for each segment rather than to fold value into the weights, because folding it in makes the score move when the revenue mix moves and nothing about the data changed.

Weighting by cost to fix has the same problem in reverse. A field that is expensive to enrich is not thereby more valuable, and a score built that way will tell you to ignore precisely the fields that are hard and important.

Weighting by how recently somebody complained is the default in most organisations, and it is worth naming so it can be stopped.

The usage count survives all three objections because it is stable, external to the argument, and recomputable. When a report changes, the weight changes, and the change is traceable to a specific commit in a specific query.

A related exercise sits one level up and deserves a mention so the two do not get merged: combining several different measures into a single ranking of exhibitors needs percentile normalisation first, which is the weighting problem on an exhibitor scorecard. Field completeness weights avoid that step only because every component is already a zero or a one.

Where this stops

The score treats a field as present or absent, which throws away everything the per field pass rate knows about placeholder values unless you feed it the cleaned version. Run the placeholder exclusion first, or a file full of NA values will score beautifully.

It also cannot distinguish a record that is thin because the registrant skipped optional questions from one that is thin because a partner import dropped three columns. Both land at 0.55 and only one of them is fixable at source. Carrying the acquisition channel alongside the score and reporting the mean per channel recovers most of that, and it usually shows one channel dragging the whole distribution down.

The deeper limit is that a complete record and a correct record are different things, and this score only sees the first. A registration holding a badge identifier, a company that closed last year, a country typed by someone who moved, and a job title two promotions out of date scores 0.91. Weighted completeness is a good measure of whether your reports can run and a poor measure of whether they will be right, which is why it belongs next to accuracy and validity in a governed view of the record rather than standing in for them.

Take your five most-used fields, give them integer weights from their usage counts, and compute the mean score and the share above 0.80 on your last closed edition. If the two records that started this post exist in your file, the weighted score will now separate them, and the 26 per cent below threshold is your enrichment queue with a size attached.