Aligning Promotion Start/End Dates with Sales Data

Q: Why floor to a day instead of comparing full timestamps?

Contract windows are negotiated in calendar days, not seconds. Truncating to the promotion's local day collapses closing-second precision and batch-posting latency into a single deterministic comparison.

Q: A retailer changed its fiscal calendar mid-cycle — what breaks?

Nothing, if periods are resolved through the calendar dimension. Add the new fiscal-to-Gregorian rows and re-run; the resolver picks up the revised boundaries without code changes.

Q: Should a plus-or-minus three-day grace window auto-approve near-boundary sales?

Only when the contract grants tolerance. Use merge_asof with a bounded tolerance to attach the sale for review, but still log a POS_LATE_SETTLEMENT code so the override is auditable.

Q: Where does this hand off to volume and tier logic?

Once attribution is set, aligned rows flow into volume threshold validation, which checks whether in-window volume clears the contracted minimum before any accrual is recognized.

When a promotion runs October 1–31 but a store-level batch posts a sale at September 30 23:45 UTC, the transaction silently falls outside the contracted window and the rebate claim is rejected as ineligible. This page documents the exact procedure for binding sales transactions to the correct promotional period — normalizing timezones, fiscal calendars, and transmission latency so that date-driven attribution is deterministic. It is the implementation-level companion to the date window alignment checks cluster, which governs how temporal boundaries are enforced inside the broader claim validation rule engine.

Prerequisites

Before running any date-alignment logic, confirm the following are in place:

Contract metadata with explicit temporal baselines. Each promotion line item must declare a timezone (IANA name, e.g. America/Chicago) and, where applicable, a fiscal_calendar_id. A bare start_date / end_date pair without a baseline is not actionable.
A calendar dimension table mapping each fiscal_period to its Gregorian start/end boundaries. This is required for retailers that close periods on a 4-4-5 or 4-5-4 fiscal cadence rather than calendar months.
Normalized sales records delivered from the upstream data ingestion normalization pipelines — parsed, deduplicated, and carrying a raw txn_timestamp plus a source_timezone tag.
Python packages: pandas>=2.2, pydantic>=2.6, and the standard-library zoneinfo and decimal modules. All monetary fields use decimal.Decimal; never float.
Access role: write access to the quarantine table and read access to the contract registry (typically the reconciliation_etl service role).

Step-by-step implementation

Step 1 — Model the promotion window with an explicit baseline

Encode the contract window as a Pydantic v2 model so that an unset timezone is a load-time failure rather than a silent default to UTC.

python

from datetime import date
from decimal import Decimal
from zoneinfo import ZoneInfo, available_timezones
from pydantic import BaseModel, field_validator

class PromotionWindow(BaseModel):
    promo_id: str
    start_date: date
    end_date: date
    timezone: str               # IANA name, required
    rebate_rate: Decimal        # money math stays in Decimal

    @field_validator("timezone")
    @classmethod
    def known_tz(cls, v: str) -> str:
        if v not in available_timezones():
            raise ValueError(f"unknown IANA timezone: {v}")
        return v

    @field_validator("end_date")
    @classmethod
    def ordered(cls, v: date, info) -> date:
        start = info.data.get("start_date")
        if start and v < start:
            raise ValueError("end_date precedes start_date")
        return v

Validation check: instantiate the model against a known-good contract row and assert ZoneInfo(window.timezone) resolves without raising.

Step 2 — Normalize every sales timestamp to the promotion’s local day

Parse raw timestamps to UTC first, then convert to the promotion’s declared timezone, then truncate to the day boundary. Truncation after conversion is what prevents the September-30-bleed described above.

python

import pandas as pd

def to_promo_local_day(sales: pd.DataFrame, tz: str) -> pd.DataFrame:
    ts = pd.to_datetime(sales["txn_timestamp"], utc=True, errors="coerce")
    local = ts.dt.tz_convert(tz)
    sales = sales.assign(
        txn_local_day=local.dt.floor("D").dt.tz_localize(None).dt.date
    )
    return sales

Validation check: after conversion, sales["txn_local_day"].isna().sum() must be 0. Any NaT means an unparseable source timestamp — route those rows straight to quarantine (see Step 5) rather than letting NaN propagate into the join.

Step 3 — Resolve fiscal periods through the calendar dimension

If the contract is anchored to a fiscal period rather than calendar dates, translate the period into Gregorian bounds before any comparison. Never apply ad-hoc day offsets.

python

def resolve_window(window: PromotionWindow, fiscal_dim: pd.DataFrame,
                   fiscal_period: str | None) -> tuple[date, date]:
    if fiscal_period is None:
        return window.start_date, window.end_date
    row = fiscal_dim.loc[fiscal_dim["fiscal_period"] == fiscal_period]
    if row.empty:
        raise KeyError(f"fiscal_period {fiscal_period} not in calendar dimension")
    return row.iloc[0]["gregorian_start"], row.iloc[0]["gregorian_end"]

Validation check: assert the resolved (start, end) spans at least one day and that fiscal_period produced exactly one calendar row — a multi-row match indicates a duplicated dimension entry.

Step 4 — Attribute sales with inclusive, boundary-safe interval logic

Replace exact-equality or naive BETWEEN predicates with an inclusive interval test on the truncated local day. Inclusivity on both ends is what eliminates off-by-one errors at month-end.

python

def attribute_to_window(sales: pd.DataFrame, start: date, end: date) -> pd.DataFrame:
    in_window = (sales["txn_local_day"] >= start) & (sales["txn_local_day"] <= end)
    return sales.assign(in_promo=in_window)

For velocity-sensitive matching where a tolerance band is contractually allowed, pandas.merge_asof with a tolerance of pd.Timedelta(days=3) and direction="nearest" attaches each sale to the closest eligible window instead of dropping near-boundary rows.

Validation check: reconcile counts — sales["in_promo"].sum() plus the quarantined count must equal the total input rows. A shortfall means records vanished in the join.

Step 5 — Quarantine misaligned rows with explicit mismatch codes

Fallback logic must never silently approve a misaligned claim. Route every out-of-window or unparseable row to a quarantine table with a typed reason code so downstream fallback routing logic can adjudicate it.

python

from enum import StrEnum

class MismatchCode(StrEnum):
    TZ_DRIFT = "TZ_DRIFT"                     # crossed a day boundary on conversion
    FISCAL_MISMATCH = "FISCAL_MISMATCH"       # period not in calendar dimension
    POS_LATE_SETTLEMENT = "POS_LATE_SETTLEMENT"  # posted after window close
    UNPARSEABLE_TS = "UNPARSEABLE_TS"         # NaT after parse

def quarantine(row, code: MismatchCode) -> dict:
    return {
        "promo_id": row["promo_id"],
        "txn_id": row["txn_id"],
        "raw_timestamp": row["txn_timestamp"],
        "mismatch_code": str(code),
    }

Validation check: every quarantined row carries a non-null mismatch_code drawn from the enum — a free-text or null code breaks root-cause reporting.

Common failure modes and fixes

Floor-before-convert ordering. Truncating to day before tz_convert strands intra-day transactions in the wrong calendar day. Always convert to the promotion timezone first, then .dt.floor("D").
Naive timestamps treated as UTC. Source feeds that drop the offset arrive as naive local time. Localize explicitly — ts.dt.tz_localize("America/Chicago") — rather than passing utc=True, which silently mislabels them and injects a fixed hours offset of drift.
BETWEEN exclusivity at month-end. SQL BETWEEN is inclusive but date/time BETWEEN against a timestamp column excludes the final day’s transactions after 00:00:00. Compare against the truncated local day, not the raw timestamp, with >= and <=.
Float money in the rebate rate. Reading rebate_rate as float compounds rounding across millions of line items and pushes accruals into the wrong band. Parse as Decimal at ingestion and keep it there through settlement.
Silent NaN propagation. An unparsed timestamp becomes NaT, then in_promo evaluates to False, and the row disappears without a trace. Detect NaT in Step 2 and route it to UNPARSEABLE_TS quarantine before the interval test.

Operational checklist

Every promotion line item declares an IANA timezone and, if fiscal, a fiscal_calendar_id.
Raw timestamps parsed to UTC, converted to promo-local, then floored to day — in that order.
Fiscal periods resolved through the calendar dimension, never via hardcoded offsets.
Interval test is inclusive on both boundaries (>= start, <= end).
All monetary fields carried as decimal.Decimal end to end.
Out-of-window and unparseable rows quarantined with a typed mismatch_code.
Row-count reconciliation (in-window + quarantined = total input) asserted before payout.
Every date-shift override written to an immutable audit log for SOX traceability.

Frequently asked questions

Why floor to a day instead of comparing full timestamps? Contract windows are negotiated in calendar days, not seconds. Comparing the raw timestamp forces you to reason about closing-second precision and batch-posting latency on every row. Truncating to the promotion’s local day collapses that ambiguity into a single deterministic comparison.

A retailer changed its fiscal calendar mid-cycle — what breaks? Nothing, if periods are resolved through the calendar dimension. Add the new fiscal_period → gregorian rows and re-run; the resolver picks up the revised boundaries. Hardcoded offsets, by contrast, would require touching every job.

Should a ±3-day grace window auto-approve near-boundary sales? Only when the contract explicitly grants tolerance. Use merge_asof with a bounded tolerance to attach the sale for review, but still log a POS_LATE_SETTLEMENT code so the override is auditable rather than silent.

Where does this hand off to volume and tier logic? Once attribution is set, aligned rows flow into volume threshold validation, which checks whether the in-window volume clears the contracted minimum before any accrual is recognized.

Parent cluster: Date Window Alignment Checks
Setting Dynamic Volume Thresholds for Rebates — how aligned volumes feed adaptive tier evaluation
SKU Mapping & Deduplication — resolving GTIN-to-PLU drift before windowed aggregation
Scoring & Confidence Models — weighting partially matched claims for automated approval

Aligning Promotion Start/End Dates with Sales Data

Back to