Completeness rate by field turns a vague data problem into a queue

Somebody says the country data is bad. It has been said in that form at every show I have worked on, and it never leads anywhere, because there is nothing in the sentence that anyone can pick up.

The completeness rate by field is the smallest measurement that ends that conversation. One number per column, stated as a share of a declared population, published next to the column name. It converts a complaint into a fraction, and a fraction into a list of records somebody can work through.

The measurement is nearly trivial and one detail decides whether it is honest.

One number per field, defined precisely

The definition has three parts and all three need writing down.

The numerator is the count of records holding a usable value. Usable means not null, not an empty string once whitespace is trimmed, and not on the placeholder list for that field.

The denominator is the record count for a declared population. Registrations for one edition, or one edition and one registration type, or the whole file. Whichever it is, it goes in the definition, because a completeness rate is meaningless without it and two people using different denominators will argue for an hour before discovering they agree.

The output is a proportion. Publish it to one decimal place, publish the two counts underneath it, and resist the temptation to publish a colour.

That is the whole measurement. The interesting part is the placeholder list, which is where most published completeness rates go wrong.

Why does a not null test overstate completeness?

Because people fill in required fields with whatever gets them to the next screen, and everything they type passes a null check.

Take a file of 40,000 registrations. The country column has 3,200 records with no value at all, so the non-null count is 36,800 and the not-null rate is 92.0 per cent. That is the figure most tools produce and most dashboards display.

Now look at what is in the 36,800. On this file, 1,450 of them hold a value that is present and useless: N/A in three spellings, none, null written out as text, a single dot, a single dash, xx, and 190 rows where somebody typed the word test.

Excluding those leaves 35,350 usable values out of 40,000, which is 88.4 per cent.

The two numbers are 92.0 and 88.4, a gap of 3.6 percentage points. On this file, 1,450 records are the difference between a country breakdown that quietly buckets them somewhere and one that reports them honestly as unknown. Across a portfolio of eight shows the same gap is several thousand records and one recurring argument about why the geographic mix keeps shifting.

The gap is also directional. Placeholders concentrate in required fields, because an optional field can just be skipped. So the more aggressively a form enforces a field, the larger the divergence between its not-null rate and its real completeness, which is the opposite of what the person who made the field mandatory expected.

The placeholder list worth having

Keep it short, keep it per field, and build it from your own data rather than from a blog post.

The general list that catches most of the volume on a registration file: NA, N/A, N.A., na, none, nil, null, unknown, a single full stop, a single hyphen, xx, xxx, asdf, qwerty, test, testing, and any value that is one character repeated.

Then the field-specific additions, which is where the yield is. On company, the placeholders are self, none, self employed, private and the registrant's own surname. On postcode, they are 00000, 12345 and 99999. On phone, they are 0000000000 and any value with fewer than seven digits. On job title, the interesting placeholder is the word yes, which appears when a form's field ordering has drifted and people are answering the previous question.

The list comes out of the twenty most frequent values in a profile, which is why the profile runs first. You cannot guess the placeholder list for a file you have not looked at, and the ones you would guess are rarely the ones with the volume.

One rule about maintaining it. When you add a value to the placeholder list, recompute the historical series, because otherwise the rate will drop on the day you improve the measurement and somebody will read that as the data getting worse.

From two numbers to a queue

Here is the part that makes the measurement worth doing. The 4,650 records missing a usable country are not one job.

Split them by cause. 3,200 are blank and 1,450 are placeholders.

Split them again by source. On this file, 2,900 of the 3,200 blanks came in through one media partner's import, which delivers a file with no country column at all because the partner never collects it. That is a single conversation and a single backfill, since the partner's own system holds a postal address they can send.

The 1,450 placeholders resolve by rule. Every one of them is a known string, they map to unknown, and the fix is one mapping table applied at load. No human touches a record.

What is left is 300 genuine blanks scattered across normal registrations. At around 40 records an hour for a steward doing a per-record lookup, that is seven and a half hours of work.

So a field sitting at 88.4 per cent, with 4,650 defective records, resolves into one supplier conversation, one mapping table, and less than a day of manual effort. That is a plan. The undifferentiated version of the same fact, 4,650 records missing country, reads like a month of work and gets deferred every quarter.

The classification is what does the work here, and it is a two-line group by. Cause, then source. Run it before anyone estimates anything.

The same split also tells you where the rate will go next quarter without any intervention. The 2,900 partner rows arrive every edition, so a backfill fixes this year and does nothing for next year unless the partner starts collecting country. The 1,450 placeholders will keep appearing at roughly the same rate as long as the field is mandatory and free text. The 300 scattered blanks are the only part of the gap that is genuinely random. Knowing which of the three you are looking at is the difference between a fix and a chore that recurs.

What should the tooling do?

Less than people expect, and it should be told what to check rather than asked to work it out.

dbt Labs' documentation lists four generic data tests shipped out of the box: unique, not_null, accepted_values and relationships. Those four cover a lot of ground and not_null covers exactly half of completeness, which is the half that is easy.

Placeholder detection is not in the box, and it should not be, because the list is specific to your fields. Two ways to get it in. Where the field has a value domain, accepted_values does the job directly, since a country restricted to ISO 3166-1 alpha-2 codes rejects NA and test along with everything else. Where the field is free text, it needs a custom test with a not-in list, which is a dozen lines of SQL you write once.

The important design choice is where the rate is computed. Compute it in the warehouse, on the same table the reports read, and it will agree with the reports. Compute it in the registration platform's own dashboard and it will describe a different population, usually including cancelled and deleted registrations, and the two numbers will differ by a few points forever.

One more thing worth building early: store the rate per field per day rather than only the current value. A completeness rate that is 88.4 per cent today is a fact. A completeness rate that fell from 94 to 88.4 over six weeks is a finding, and only the stored series can tell you which one you have.

Where this stops

A field completeness rate says nothing about whether the completed values are correct. Country at 100 per cent, with every row set to the show's host country by a default in a form, scores perfectly and describes nothing. Defaults are the failure mode that placeholder detection misses, because a default is a legitimate value in a legitimate format, and the only way to catch one is to look at the distribution and notice a share that is too high to be real.

The measurement is also blind to records that never arrived. A file cut before onsite registration closed will show excellent per-field rates on the rows it contains, and the rows it does not contain have no fields to be incomplete. That is a population problem, and it is why the denominator has to be a declared population rather than the row count of whatever file you happen to have opened.

The last limit is about aggregation. Per-field rates do not add up to a record-level answer, because the gaps overlap. Four fields at 90 per cent each can produce anything between 60 and 90 per cent of records complete on all four, depending on whether the same records are missing them. Counting records against the fields a named output reads is a separate query, and scoring each record against weighted fields is a third one. All three are useful and none substitutes for another, which is also why a single field rate should never be described as the completeness of the file.

Take the field you complain about most, count non-null values, then count non-null values that are not on a placeholder list you build from the top twenty. Put both numbers on one line in your record definition documentation. The gap between them will tell you within a minute whether you have a collection problem or a form problem.