A Missing Sales Day Is Not Automatically a Zero

A daily sales chart connects Monday directly to Wednesday. Tuesday had no imported rows, so it disappeared from the query result. The dashboard now looks continuous, but the analyst cannot tell whether Tuesday had no sales, the store was closed, or an import failed. That ambiguity affects averages and decisions about demand.

EcomToolkit recommends building the reporting calendar independently of the transactions and attaching a completeness state to each day. A date spine supplies the expected dates. It does not establish that the underlying data is trustworthy. This guide uses a fictional store and a small SQL example to keep those two responsibilities separate.

Table of Contents

• Define what a trading day means
• Separate three states in the output
• Join the calendar to daily totals and completeness
• Make the average explain its denominator
• Extend the spine without multiplying the dataset blindly
• Test gaps before trusting the chart
• The EcomToolkit view

Define what a trading day means

Start with the reporting timezone and the timestamp used to assign orders. Order creation, payment capture and fulfilment are different events. A revenue report may use one while an operational report uses another. Document that choice before generating the calendar, otherwise two internally correct reports can disagree about the same evening’s orders.

Decide whether the period includes calendar days or scheduled trading days. An online storefront may remain available while fulfilment stops for a holiday. Removing the holiday from a sales denominator because the warehouse was closed mixes two operating calendars. Keep the calendar appropriate to the question being answered.

Use explicit start and end dates. Avoid allowing the first and last observed transactions to define the reporting window: that approach can erase quiet days at both ends. For period comparisons, establish equivalent maturity and cutoffs, especially when the newest day is still receiving late records.

Our timezone and daylight-saving reporting guide covers timestamp assignment. Here, the example begins after that assignment and assumes a daily table keyed by a local reporting date. Separating those steps makes each rule easier to inspect.

Separate three states in the output

A completed import with no qualifying sales can support a zero. An incomplete import supports an unknown final result, even if some sales have arrived. A date outside the intended store or market scope is not applicable. These states should not share one unlabeled blank cell.

Consider this original teaching dataset, where the store is in scope on all four dates. Completeness comes from an independent ingestion audit rather than the presence of order rows. The amounts are invented and are not trading targets.

The third day’s £80 may be useful as a provisional amount, but it is not a completed daily total. Display it in a separate field if the trading team needs an early signal. The final revenue column should not silently promote partial data into a fully comparable observation.

Join the calendar to daily totals and completeness

A production implementation can use a maintained calendar table. PostgreSQL also provides generate_series for generating sequences. The compact query below uses literal dates so the whole example is readable and its expected result can be checked without an existing warehouse.

WITH calendar(day) AS (
  VALUES ('2026-09-01'), ('2026-09-02'),
         ('2026-09-03'), ('2026-09-04')
), daily(day, revenue) AS (
  VALUES ('2026-09-01', 120),
         ('2026-09-03', 80),
         ('2026-09-04', 200)
), coverage(day, is_complete) AS (
  VALUES ('2026-09-01', 1), ('2026-09-02', 1),
         ('2026-09-03', 0), ('2026-09-04', 1)
)
SELECT c.day,
       CASE WHEN q.is_complete = 1
            THEN COALESCE(d.revenue, 0)
            ELSE NULL END AS final_revenue,
       CASE WHEN q.is_complete = 1
            THEN 'complete'
            ELSE 'incomplete' END AS data_state
FROM calendar c
LEFT JOIN daily d ON d.day = c.day
LEFT JOIN coverage q ON q.day = c.day
ORDER BY c.day;

The output is 120, zero, null and 200, with a state for each date. The literal keys are consistently formatted strings for portability in this example; production tables should use a suitable date type. Ensure that both joined tables have at most one record per reporting key.

The PostgreSQL table-expression documentation explains the outer-join behaviour used here. Be careful with a later WHERE clause on a right-hand table: a filter requiring an order field can remove the very unmatched calendar rows the left join was meant to preserve.

Make the average explain its denominator

For the three complete days in the example, final revenue totals £320 and averages approximately £106.67 per complete calendar day. That is not a four-day period average. One day’s final revenue remains unknown, so a complete-period figure is not yet supported.

If the confirmed zero disappears, the average of the two complete days with sales becomes £160. That number answers a different question: revenue per revenue-producing day. It should not appear under the same daily-sales label. A quiet day is still part of a calendar-based denominator.

Filling the incomplete day with zero produces £80 across four days, but this assumes away missing sales. Including the partial £80 instead produces £100 across four days, which is still provisional. Neither output should be presented as a settled total merely because the arithmetic completes successfully.

Publish coverage beside any provisional summary: three of four days complete, one incomplete, and a clear reporting cutoff. This gives the reader a reason to defer a decision or inspect the import, rather than treating a suspiciously low average as evidence of a failed campaign.

Extend the spine without multiplying the dataset blindly

A store-level calendar is small. A date-by-product-by-market calendar can become much larger and create meaningless combinations. A product that had not launched in a country should not automatically receive zero sales for every earlier day in that country.

Build eligibility rules before expanding the dimensions. For product reporting, define launch and retirement dates, relevant markets and the scope of the assortment. Retain the distinction between eligible with zero sales and not eligible. Otherwise a long history of artificial zeros can distort velocity calculations.

Aggregate transactions to the intended grain before joining. If the output is one row per date and market, join date-and-market totals and date-and-market completeness. Joining line items to a calendar and later combining another repeated table can inflate revenue. Our join fanout guide explains that separate failure mode.

Do not infer completeness from a scheduled job reporting success alone. Decide what success proves: all expected pages imported, source totals reconciled, watermark reached, or a documented subset completed. The calendar depends on that contract, so its owner should know when an import can legitimately mark a date complete.

Test gaps before trusting the chart

Create a small fixture with an empty but complete day, a partial day, a missing audit row and an ordinary sales day. Check that the chart retains every expected date and uses a visual gap or explicit annotation for unknown values. A line connecting across unknown days can imply an observed path that does not exist.

When late records arrive, update the appropriate historical date under the agreed restatement policy. Preserve a record of what changed and why. The calendar should remain stable while the data state and revenue can mature; repeatedly changing the date range makes reconciliation needlessly difficult.

The EcomToolkit view

A date spine is valuable because it makes absence visible. Its purpose is not to turn every gap into a neat row of zeros. Keep the reporting calendar, data completeness and commercial result separate, then combine them deliberately in the published metric.

Bring a daily sales query and its import audit to an EcomToolkit analytics review. A reliable report should explain both what happened on the quietest day and what is still unknown about the latest one.