Is Your Dashboard Lying to You? A Ten Minute Data Quality Audit

The confident wrong number

Here is something I have learned the hard way, more than once, standing in front of a dashboard with a client while we both nodded at a number that turned out to be nonsense. A wrong number that looks confident is more dangerous than no number at all. When you have no number you stay careful, you ask around, you hedge, you keep your options open. When you have a number, especially a tidy one on a nice chart, you stop asking. You act. And if that number is quietly wrong, you have just made a decision on bad data with full confidence, which is exactly the worst way to be wrong.

Most dashboards I open carry at least a couple of these. Not because anyone was careless, but because data quality problems hide. They do not throw errors. The chart still renders, the average still computes, the line still goes up and to the right. Nothing screams. That is the whole problem. A broken pipeline shouts at you, so you fix it before lunch. A subtly wrong average whispers, and you believe it, and you keep believing it for months because it never gives you a reason not to.

So this is a practical piece. I want to give you a ten minute audit you can run on your own dashboard this afternoon, no data science team required, just a suspicious mind and a few quick checks. I borrowed the mindset from two books I keep on the shelf, The Data Analytics Handbook (2014) and Business Intelligence, Analytics and Data Science (2018), and from a habit I think of as the data quality detective: assume the number is guilty until it proves itself innocent. That single reframing changes everything about how you read a screen. You stop admiring the chart and start interrogating it.

Why bad data hides so well

Before the checks, it is worth understanding why this problem is so sneaky, because once you see the mechanism you start spotting it everywhere.

A software bug tends to be loud. Something crashes, a page goes blank, a red error appears, and everyone rushes to fix it. A data quality problem is the opposite kind of animal. The code runs perfectly. Every function returns a value. The pipeline finishes green. The only thing wrong is that one of those values does not mean what you think it means, and there is no test in the world that catches a number which is technically valid but factually false. Eighty is a perfectly good number. It is a real double, it renders on a chart, it sorts and sums and averages without complaint. It is just not the truth.

Here is the first A HA I want to leave with you early, because everything else hangs off it. The absence of an error is not evidence of correctness. A dashboard that has never once thrown a warning is not a trustworthy dashboard, it is a quiet one, and quiet is not the same as honest. We treat the two as if they were the same thing all the time, and it costs us.

The one that poisons everything: blanks counted as zero

Let me start with the check that catches the most damage for the least effort, because it is the one that surprised me most when I first saw how far it reaches.

The symptom

You look at a headline average and it feels a little low. Not alarmingly low, just softer than you expected. Your gut says average order value should be around a hundred and the screen says eighty, and you shrug, because eighty is plausible and gut feelings are not evidence. That shrug is the symptom. The number is quietly deflated and it is deflated by exactly the amount that keeps it believable.

The cause

Somewhere between the database and the chart, blank values got read as zero instead of being skipped. This happens constantly, at every layer. A spreadsheet treats an empty cell as zero in one function and ignores it in another. A SQL average ignores nulls, but the moment someone wraps that column in a coalesce that turns null into zero, the nulls join the calculation as real zeros. An export writes empty strings, the import reads them as numbers, and empty becomes nought. Nobody decided to do this. It is the default behaviour of a dozen tools, and defaults are where data quietly goes wrong.

The quick test

Count your rows two ways. Total rows, and rows where the value field is actually populated. If those two numbers differ, and your average is being computed over the larger one, your average is understated. Ten seconds, and it catches an astonishing amount.

The worked example

Imagine a simple report: average order value across fifty orders. The chart shows one number, your average order value this month. Clean, single figure, easy to trust. Now suppose ten of those fifty orders came in through a channel that did not record a value, so the field is blank. And suppose those ten blanks got read as zero rather than skipped.

The mean of a set of values is just the sum divided by the count.

$\bar{x} = \frac{1}{n}\sum_{i=1}^{n} x_i$

Take the honest version first. Forty orders actually recorded a value, and those forty values add up to 4000. The true average order value, over the orders you actually have data for, is

$\bar{x} = \frac{1}{40}\sum_{i=1}^{40} x_i = \frac{4000}{40} = 100$

Now the poisoned version. The ten blanks got silently turned into zeros, so the sum is unchanged at 4000, but you are now dividing by fifty instead of forty.

$\bar{x} = \frac{1}{50}\sum_{i=1}^{50} x_i = \frac{4000}{50} = 80$

Same underlying business, same real orders, and your headline average order value just fell from 100 to 80. To size the damage, put the poisoned figure over the true one.

$\frac{80}{100} = 0.8$

A twenty percent understatement, and not a single thing on the dashboard looks broken. The chart is tidy. The number is plausible. If anything an eighty looks more believable than a hundred, because round confident numbers always look believable.

How far the poison travels

Here is the part that makes it genuinely dangerous, and it is the second big A HA. This one bad handling of blanks does not stay in its lane. That average feeds your revenue forecast, your target cost per acquisition, your margin per customer, the ratio the whole ads budget is pegged to. One field, silently misread, and every number downstream that touches it is now quietly wrong. Nobody notices, because nothing errored.

Think of it as a chain. If your true average order value is a hundred and you plan a campaign to a target return on ad spend, the most you can pay to acquire a customer flows straight out of that figure. Peg your allowable acquisition cost to eighty pounds of value when the truth is a hundred, and you will throttle perfectly profitable campaigns because they look like they lose money. The blank field did not just cost you an accurate chart. It cost you the growth you throttled. That is the real bill, and it never shows up on the dashboard that caused it.

Sit with that for a second. A handful of blanks counted as zero can poison every average on the page, and the page still looks perfect, and the cost lands somewhere completely different from where the mistake lives.

The ten minute audit, check by check

Right, here is the routine. You do not need to be technical. You need to be nosy. Go through these in order and flag anything that fails. A flag is not a disaster, it is a lead to follow.

Let me walk the checks the flowchart runs, with the ten second version of each, plus the symptom you feel, the cause underneath, and a worked example so you know exactly what you are looking at.

Check one: do the totals reconcile

Symptom and cause

The symptom is a total that feels bigger or smaller than reality, and you have no outside number to argue with it. The cause splits two ways. If the dashboard says more than the source, you probably have double counted rows, which happens the moment a join fans out, one order matching three shipping lines and getting counted three times. If the dashboard says less, something is being dropped or filtered before it reaches you.

Quick test and worked example

Pick one number you can verify against something outside the dashboard. Total orders this week against the raw order table. Total revenue against what actually landed in the bank or the payment processor.

Say the dashboard reports 1500 orders this week and the raw order table has 500 rows. That is not random noise, it is a factor of three, and a clean multiple like that is the fingerprint of a fan out join. Each order is matched to its shipping lines, and orders with three parcels get counted once per parcel. The revenue chart is inflated by the same mechanism. A number that does not reconcile to a source you trust is not a number yet. It is a rumour. Reconciling one figure a week keeps every figure honest, because the totals are the load bearing wall the rest of the dashboard rests on.

Check two: blanks against zeros

We introduced this one already, so here I want to push it one layer further, because blanks do not only wreck averages. They wreck rates, and rates run your whole marketing.

The rate version

A conversion rate is conversions over sessions.

$\text{conversion rate} = \frac{\text{conversions}}{\text{sessions}}$

Now ask the rude question about both halves. If sessions with no recorded outcome get counted as zero conversions, your numerator is honest but your denominator is padded, and the rate drops. If, the other way round, a batch of sessions never got recorded at all because a tag failed to fire, your denominator shrinks and the rate jumps. Same underlying reality, two opposite lies, both depending on how one blank field got handled.

Where the blank lands	What it does to the rate	What you wrongly conclude
Padding the denominator	Pushes the rate down	The channel looks worse than it is
Missing from the denominator	Pushes the rate up	The channel looks better than it is
Padding the numerator	Pushes the rate up	The offer looks like it converts more
Missing from the numerator	Pushes the rate down	The offer looks like it converts less
This is why I always ask how blanks are treated in every division on the page, not just the one average that first caught my eye. Anything with a slash in it, conversion rate, bounce rate, refund rate, margin, is a blank waiting to break in one of two directions.

Quick test

Count total rows, count populated rows, compare, then repeat the thought for the numerator and denominator of every rate. If either side is quietly counting blanks, the rate is fiction dressed as a percentage.

Check three: the missing segment

Symptom and cause

The symptom is subtle by design: nothing looks wrong, because the problem is a thing that is not there. A segment that silently vanished is worse than one that shows a bad number, because a bad number draws the eye and a missing row does not. The cause is almost always a filter or a naming mismatch upstream. A channel gets renamed at the source, the dashboard filter still looks for the old name, and the whole channel drops out of every chart without a whisper.

Quick test and worked example

Look at your breakdown by channel, or region, or product, and ask the boring question: is everything I expect actually here? List the segments you know exist and tick them off against the screen. I have seen a whole sales channel disappear from a report for a month because a naming change upstream meant it no longer matched a filter, and the totals just quietly got smaller with nobody the wiser. The tell was not in the missing row, because you cannot see a missing row. The tell was that the weekly total had drifted down and nobody could say why. If a segment you know exists is not on the chart, something is filtering it out, and it is taking its numbers with it.

Check four: suspiciously flat or round averages

This is the detective's favourite, so it gets a little more room.

Why flat and round is a red flag

Real data is lumpy. Genuine averages land on awkward numbers like 97.3 and 104.8, because they are the residue of hundreds of messy real values. When an average sits at exactly a round number, or a column of averages is suspiciously flat across groups that should differ, be suspicious. Flat and round usually means a default value got substituted for missing data somewhere. A load of records stamped with the same placeholder age, or the same default price, will drag a whole distribution towards a fake centre and make it look calm and healthy.

The test that beats the mean

The mean alone hides this, so look at the spread as well. The simplest honest measure of spread is the standard deviation, the typical distance of a value from the mean.

$\sigma = \sqrt{\frac{1}{n}\sum_{i=1}^{n}\left(x_i - \bar{x}\right)^2}$

You do not need to compute it by hand. You need the intuition: real business data almost never has a tiny spread. If a column of prices has a standard deviation near zero, or every group posts the same mean to the penny, someone has filled the blanks with a default and flattened the life out of it. Comparing the mean to the median helps too. When a default value has been stuffed in, the median often sits right on the placeholder while the mean drifts, and the gap between them is the fingerprint of the fill. Calm data is often data that has been quietly overwritten.

Check five: broken UTMs and inconsistent naming

The symptom

Open your traffic sources and just read them. You will find Facebook, facebook, FB, fb, and fb ads, all describing the same channel, each counted as a separate thing, each splitting your numbers into smaller and less trustworthy piles.

The cause and why it matters

None of this errors. The machine treats two spellings as two truths, so every variant becomes its own row, and the channel you actually care about is scattered across five of them. You will find UTMs with typos, campaigns that never got tagged sitting in direct or unattributed, and the same product spelled three ways. The damage is quiet fragmentation of the very groupings you are trying to make decisions on. A channel that would clearly clear your target if its five spellings were added up looks like five underperformers instead, and you cut the one that was actually working.

Quick test

Read the raw list of source and campaign labels, not the tidy chart, and hunt for the same thing wearing different clothes. Inconsistent naming is a data quality problem wearing a disguise, and the fix is a naming convention enforced at the source, not a manual clean up every month.

Check six: is the data even fresh

Last one, and people forget it constantly.

Symptom, cause, test

The symptom is numbers that feel frozen, a chart that looks the same on Thursday as it did on Monday and you cannot say whether business really went that flat or the data simply stopped moving. The cause is a broken pipeline that failed silently, so the last good load just sits there looking current. The test is to find the timestamp that tells you when this data last updated, and if there is not one on the dashboard, that absence is itself the flag.

A dashboard with no freshness indicator is asking you to trust that every pipeline behind it ran successfully today, which is a lot of faith. Stale data is the sneakiest wrong number of all, because it was correct, yesterday. A pipeline broke on Tuesday, the numbers froze, and the chart kept showing Tuesday's world as if it were today's. Everyone kept making calls on it. Always know how old your data is, and put that age on the screen where you can see it.

The pocket version

Here is the whole audit as a table you can keep next to the screen. Symptom on the left, the usual culprit in the middle, the ten second test on the right.

Symptom	Likely cause	Quick test
Average looks too low	Blanks counted as zero	Compare total rows to populated rows
Total is higher than the source	Double counted rows from a join	Reconcile against the raw table
A known segment is missing	Silent filter or naming mismatch	List all segments, check for gaps
Averages oddly flat or round	Default value filling nulls	Look at the spread, not just the mean
Same channel split many ways	Broken UTMs, inconsistent naming	Read the raw source and campaign labels
Numbers feel frozen	Stale data, broken pipeline	Find the last updated timestamp

None of these checks needs a specialist. They need someone willing to distrust a tidy chart for ten minutes. That person can be you.

A worked mini case: ten minutes on a real looking dashboard

Let me put the whole routine together on one illustrative dashboard, so you can see how the flags chain into a story. Picture a shop owner opening their weekly report on a Monday. Everything is green. Here is the ten minutes.

Minute one, reconcile. The dashboard says 1500 orders, the raw order table says 500. Factor of three, so a fan out join is triple counting, probably on shipping lines. Flag one: every count and every revenue figure on this page is inflated threefold until proven otherwise.

Minute three, blanks. Total order rows 500, rows with a populated value 400. A hundred blanks, and average order value is being taken over all 500. Flag two: the headline average is deflated, and so is every plan pegged to it.

Minute five, segments. The channel breakdown lists paid search, email, and direct, but the owner knows there is a healthy affiliate channel that is simply not on the list. Flag three: a segment has dropped out, its revenue is missing from the totals, and the tell was a total that quietly shrank.

Minute seven, flatness. Average delivery time reads exactly 3.0 days for every region, to one decimal, identical across the board. Real delivery times are never that tidy. Flag four: a default of three days has been stuffed into the blanks and flattened the whole distribution.

Minute nine, naming. The source list shows google, Google, and google cpc as three rows. Flag five: one channel scattered across three spellings, each looking weaker than the real combined channel.

Minute ten, freshness. There is no last updated stamp anywhere on the page. Flag six: nobody can tell whether this is Monday's data or last Tuesday's.

Six flags in ten minutes on a dashboard that looked flawless. Notice the shape of it. Not one flag threw an error, not one chart looked broken, and the owner had been making decisions on all six for months. That is the point of the routine. It finds the wrongness that is designed not to be found.

Common mistakes when you run this

A few traps I see people fall into once they start auditing, so you can skip them.

The first is fixing the chart instead of the cause. You spot the blanks, you add a filter on the one chart to exclude them, the number looks right, and you move on. Three other reports still read the same field the same broken way, and next month someone builds a fourth. You fixed a symptom and left the disease.

The second is trusting a number because it matches your expectation. Confirmation is not verification. A wrong number that happens to land where you hoped is the hardest of all to catch, because you have every motive not to look. Audit the numbers you like at least as hard as the ones you do not.

The third is auditing once and calling it done. Data quality is not a state you reach, it is a property that decays. A source gets renamed, a tag gets removed, a new channel comes online untagged, and yesterday's clean dashboard is today's liar. The audit is a routine, not a project.

The fourth is confusing precision with accuracy. A number quoted to two decimal places feels more trustworthy than a round one, but the decimals describe the arithmetic, not the truth of the inputs. You can compute a false average to any number of decimal places you like. Precision is how tidy the sum is. Accuracy is whether the inputs were real. Do not let the decimals talk you out of the audit.

Why the tidy ones are the dangerous ones

The instinct people have is to trust the clean dashboard and distrust the messy one. It is exactly backwards. A messy dashboard, the one throwing obvious errors and blank panels, keeps you honest, because you can see it is broken so you go and check. The tidy one, all green, every chart rendered, every number round, is the one that walks you confidently off a cliff.

That is worth sitting with. The polish of a dashboard tells you nothing about whether its numbers are true. Polish is a design property. Truth is a data property. They have almost nothing to do with each other, and we confuse them constantly because a confident presentation feels like a confident fact. A beautiful chart of a wrong number is just a wrong number in a nice suit.

Which is why I would rather you run this ten minute audit on a dashboard that looks perfect than on one that looks broken. The broken one you will fix anyway. The perfect one is the one quietly making your decisions for you.

What to do once you have found one

When a check flags, resist the urge to just patch the chart. The wrong number on the screen is a symptom, and the interesting question is always how far upstream the cause lives, and what else it has already touched. If blanks are being counted as zero in this average, they are almost certainly doing it in the three other reports that read the same field. Fix it at the source, once, where the data is shaped, not at the surface in every chart. A number fixed at the source stays fixed. A number fixed at the chart breaks again the next time someone builds a report.

Here is the fix decision as a flow, so you always push the repair as far upstream as it will go.

This is the honest reason I keep steering owners towards systems and shops they actually control, with data they can reach into and reconcile, rather than rented dashboards that hand you a pretty number and no way to check it. You cannot audit what you cannot open. If your reporting is a black box that shows you an average and refuses to show you the rows behind it, you are being asked to trust on faith, and this whole piece is an argument against trusting on faith.

If you want a second pair of eyes on your own dashboards, running exactly this kind of audit and then fixing the causes at the source rather than papering over the charts, that is squarely the data and analytics work I do. Bring your messiest report. The messy ones are honest. It is the tidy ones I worry about.

How to apply this next week

Do not treat this as a one off spring clean. Turn it into a small standing habit and it pays you back every week for almost no effort.

Start by picking one number that actually drives a decision, the one your budget or your pricing hangs on, and reconcile it against a source outside the dashboard once a week. Just one number, one reconciliation, every week. Next, add a last updated timestamp to every dashboard you own, right at the top where you cannot miss it, so freshness stops being a question you forget to ask. Then, whenever you build or inherit a report, write down for each key figure how blanks are handled, because the moment you have to write it down you usually discover you do not know, and not knowing is the whole risk.

Agree a naming convention for channels and campaigns and enforce it where the data is created, not where it is read, so you are never cleaning up the same five spellings by hand again. And once a quarter, run the full ten minute audit from the top on your most important dashboard, precisely because it is the one you trust the most and therefore the one you check the least. The habit is small. The decisions it protects are not.

The one habit to leave with

If you take a single thing from this, make it a reflex. Before you make any decision on a number, ask it one rude question: how could you be wrong? Are you counting blanks as zero, are you double counting a join, are you missing a segment, are you flat because someone filled you with a default, are you stale? Ten minutes of suspicion, run as a routine, will catch more bad decisions than any amount of fancier analysis run on data you never checked. The goal is not a prettier dashboard. The goal is a number you have actually earned the right to trust.