An ecommerce dashboard can lose orders without a failed import or a duplicated join. A filter that looks like plain English can exclude rows whose values are unknown, and an average can quietly describe only the records with a populated field. The query runs successfully while the meaning of its result changes.
EcomToolkit’s editorial position is that missing data needs an explicit reporting policy. This guide focuses on SQL NULL semantics in order and refund analysis. It uses a small hypothetical dataset so analysts can verify the arithmetic and adapt the checks to their own warehouse without mistaking example values for industry statistics.
For a related diagnostic, see our guide to explicit SQL window frames for running revenue.
Table of Contents
- Distinguish unknown from zero
- See how a filter removes unknown states
- Audit exclusion lists and anti-joins
- Count the population behind every aggregate
- Use defaults only when the contract supports them
- Build a small reconciliation fixture
- The EcomToolkit view
Distinguish unknown from zero
NULL represents missing or unknown information in the examples here. Zero is a known numeric value. An empty string is another distinct state, often produced by an import or form. Treating these values as interchangeable can turn a data-quality issue into a confident but incorrect revenue statement.
Consider a refund amount. Zero may mean the order was checked and has no refund. NULL may mean refund data has not arrived, a join did not find a record, or the source does not provide that field. The right interpretation depends on the contract for the dataset, not the analyst’s preference for a clean chart.
Create a field-level note containing its business meaning, when it becomes available and why it may be missing. For a payment status, distinguish not yet known from unpaid. For a customer identifier, distinguish a guest order from an integration failure. One generic missing-value rule rarely fits every ecommerce field.
| Field state | Possible business meaning | Reporting treatment to decide |
|---|---|---|
| refund_amount = 0 | Confirmed no refund in the defined window | Include as a known zero |
| refund_amount IS NULL | Unknown or unavailable refund information | Preserve missingness and flag coverage |
| status IS NULL | State not populated | Use a visible unknown group |
| customer_id IS NULL | Guest order or failed identity mapping | Investigate before classifying |
| discount_code is empty text | No entered code or source formatting | Normalize according to the source contract |
The table is a starting policy worksheet. It does not establish what a particular platform means by an absent value. Validate the source behaviour with representative orders and the data owner before changing production transformations.
See how a filter removes unknown states
Suppose a fixture contains three orders: order 1 has status paid, order 2 has status cancelled, and order 3 has a NULL status. The condition status <> ‘cancelled’ selects only order 1. It does not also select order 3 merely because no cancellation label is visible.
SQL filtering keeps rows where the predicate evaluates to true. A comparison involving an unknown value may evaluate to unknown, which is not retained by WHERE. Use explicit NULL tests and decide whether unknown states belong in the output. Do not solve the problem by assuming every unknown status represents a valid sale.
-- Include unknown states for investigation, not revenue recognition.
SELECT order_id, status
FROM orders
WHERE status <> 'cancelled' OR status IS NULL;That query returns orders 1 and 3 in the hypothetical fixture. A paid-sales report should instead use its verified payment eligibility rule. An exception queue may legitimately include the unknown row. The same predicate can be correct for one business question and wrong for another.
Audit exclusion lists and anti-joins
The PostgreSQL comparison documentation describes a particularly surprising case: NOT IN can return NULL when its comparison list contains a NULL and no equal value is found. A single unknown identifier in an exclusion source can therefore cause otherwise expected rows to disappear.
For example, testing order 1 against NOT IN (2, NULL) does not produce true. An analyst excluding cancelled orders might see no output and incorrectly conclude that there were no eligible sales. Check the exclusion source for missing identifiers and test this case before trusting the filter.
A correlated NOT EXISTS query often expresses the intended unmatched-record question more clearly:
SELECT o.order_id
FROM orders AS o
WHERE NOT EXISTS (
SELECT 1
FROM excluded_orders AS e
WHERE e.order_id = o.order_id
);This still needs a policy for missing outer identifiers. If orders.order_id can itself be NULL, the equality cannot establish a matching exclusion, and the row may remain. Validate primary keys separately. Changing the SQL construct does not repair a broken entity identifier or decide whether the order is commercially eligible.
Keep the distinction from join fanout and duplicated revenue clear. Fanout repeats rows; NULL predicates can remove them or change aggregate denominators. A report can contain both errors, with one partly hiding the other in an apparently plausible total.
Count the population behind every aggregate
Consider three order records with refund amounts 0, 20 and NULL. There are three records, two known refund values and one unknown value. SUM of the populated refund amounts is 20, while their average is 10. Replacing the unknown with zero produces an average of approximately 6.67 across all three records.
The PostgreSQL aggregate reference distinguishes COUNT(*) from counting a particular expression and explains NULL handling for aggregates. Use that behaviour deliberately. An average over known records answers a different question from an average under an assumption that unknown values are zero.
| Expression on 0, 20, NULL | Result | Meaning |
|---|---|---|
| COUNT(*) | 3 | All input records |
| COUNT(refund_amount) | 2 | Records with known refund values |
| SUM(refund_amount) | 20 | Total of known refund values |
| AVG(refund_amount) | 10 | Average among known values |
| AVG(COALESCE(refund_amount, 0)) | About 6.67 | Average after assuming missing means zero |
Publish the known-value count beside the result when completeness matters. In this fixture, coverage is 2 divided by 3, or approximately 66.7%. That percentage describes the field’s availability, not the share of orders refunded. Confusing those denominators creates another error even after the SQL is technically correct.
Use defaults only when the contract supports them
COALESCE is useful when the replacement has an agreed meaning. After a complete refund-event source has been aggregated to one row per order, an unmatched order may legitimately mean no recorded refund in that window. Before using zero, verify that the extraction is complete and the matching key is valid.
Keep the raw value or a missingness flag in an intermediate model. A downstream chart may need a presentation default, but investigators should still be able to reconstruct which values were absent. Once missing and genuine zero values are merged irreversibly, later reconciliation becomes much harder.
Watch for left joins followed by filters on the right-hand table. Filtering for a right-side value in WHERE can discard unmatched rows and undermine the intended preservation of the left population. Decide whether the condition belongs in join eligibility, in a conditional aggregate or in the final population definition.
For instance, listing all paid orders with any completed refund is different from listing only paid orders that have a completed refund. Write those two requirements in words before choosing the query. A short business definition prevents more mistakes than a long list of SQL style preferences.
Build a small reconciliation fixture
Test a known zero, a known positive value, a missing value, an unmatched join and a NULL in an exclusion source. Add an empty input set because aggregate outputs can differ from the all-zero case. Record expected rows and values explicitly instead of merely checking whether the query executes.
At the daily pipeline boundary, compare eligible source orders with included orders, explicit exclusions and unresolved exceptions. Investigate any unexplained remainder. Reconcile amounts separately from counts because a missing high-value order can have a small count impact and a large monetary impact.
Segment completeness by source, release and date of ingestion. A sudden improvement in average refund value might be caused by a new integration dropping NULL rows. Conversely, newly populated historical records can change the average without any change in customer behaviour. Annotate restatements and avoid silently comparing incompatible snapshots.
The analytics quality framework provides broader reconciliation context. For this specific failure mode, the handover should include the SQL fixture, field policies, completeness checks and an owner for unresolved states. That package makes the correction reviewable by both analysts and trading teams.
The EcomToolkit view
Unknown information should not disappear simply because the dashboard prefers a number. Make the population explicit, retain enough evidence to explain missingness and apply defaults only when they represent a defensible business rule. A successful query is the beginning of validation, not its conclusion.
Use a focused EcomToolkit analytics review to trace a disputed KPI from source rows to its displayed denominator. Bring a small anonymised fixture and the intended reporting rule so the discussion can resolve a concrete discrepancy.
For a related analytical step, read our guide to integer division and percentage units in SQL conversion rates.
