Automated Price Reporting & Dashboards #

Competitive price intelligence is only worth the compute it costs if a pricing manager, a merchandiser, or a category lead actually reads the answer on the cadence they need it. A daily crawl that resolves millions of competitor offers but never surfaces a clean win/loss number is a data-lake artifact, not a decision system. This guide covers the reporting layer of Competitive Price Intelligence & Repricing: the data model behind recurring price reports, aggregation with pandas, chart and report rendering, scheduled delivery to email and Slack, and the materialized-summary patterns that keep an interactive dashboard fast. It reads the same normalized, matched price records that feed price positioning and competitive benchmarking, and it shares an alerting spine with price anomaly alerting and monitoring — reporting answers “how are we positioned this week”, alerting answers “something just broke, look now”.

Problem Framing & Prerequisites #

The failure mode this stage exists to prevent is metric divergence. When the emailed “we are 4% above market” and the dashboard’s “we are 2% below market” disagree, every stakeholder quietly stops trusting both, and pricing decisions drift back to gut feel. Divergence almost always comes from two teams computing the same KPI from raw observations with slightly different filters — one excludes out-of-stock competitors, the other does not; one uses the median competitor, the other the minimum. The fix is architectural: define each metric once, compute it once per period in a single aggregation job, materialize the result, and make every downstream surface — the email, the CSV, the interactive dashboard — read that one materialized row rather than recomputing from raw data.

That imposes three prerequisites on what reaches this layer. First, the price observations must already be matched and normalized. Reporting cannot reconcile identity; it assumes each competitor offer is already tied to a canonical product and expressed in a base currency with tax and shipping resolved, which is the job of the data normalization and promo parsing pipelines upstream. A report built on unnormalized prices compares a tax-inclusive EU listing against a tax-exclusive US one and produces a confident, wrong number. Second, every observation carries a captured_at timestamp and a source tag, so the aggregation can slice deterministically by day and reconstruct exactly which crawl produced a figure. Third, the reporting job is a read-only consumer. It never mutates the price store; it derives summaries and writes them to a separate summary table. That isolation means a broken report can never corrupt the underlying dataset, and the job can be re-run for any historical day without side effects.

The minimal contract for an observation entering the aggregation:

from pydantic import BaseModel
from datetime import datetime

class PriceObservation(BaseModel):
    product_id: str          # canonical internal id (post-match)
    competitor: str          # e.g. "amazon", "walmart"
    price: float             # base currency, tax/shipping normalized
    our_price: float         # our current price for the same product
    in_stock: bool           # competitor availability at capture time
    cost: float              # our landed cost, for margin
    captured_at: datetime    # crawl timestamp, for the daily slice

Architecture Detail: The Reporting Data Model #

A price report is a set of a few well-defined metrics computed over a time slice and a segmentation. Four metrics carry most of the decision weight, and it pays to define them precisely rather than let each consumer improvise:

• Price index — our price relative to a competitor benchmark, expressed as a ratio. Using the median competitor price as the benchmark, the index is $I = \frac{P_{\text{ours}}}{\tilde{P}_{\text{comp}}}$, so 1.00 is parity, 1.05 is 5% above market. The full construction, including weighting and basket selection, lives in building a competitive price index.
• Win/loss — per product, whether our price is the lowest (win), tied, or beaten (loss) against in-stock competitors. Aggregated, it becomes a win rate: the share of the assortment where we lead on price.
• Margin — gross margin at our current price, (our_price - cost) / our_price. Reporting price position without margin invites a race to the bottom; the two must sit on the same row.
• Coverage — the share of our catalog for which we have a fresh competitor observation in the period. Coverage is the report’s own honesty metric: a win rate computed over 40% coverage is not a market position, it is a sampling artifact, and stakeholders must see the denominator.

The reporting job reads a day’s observations and collapses them into one row per segment. The core aggregation is a straightforward pandas group-by, but the discipline is in computing every metric in one pass so they are mutually consistent:

import pandas as pd

def build_daily_summary(obs: pd.DataFrame, day: str) -> pd.DataFrame:
    """Collapse a day's price observations into one summary row per category.

    obs columns: product_id, category, competitor, price, our_price,
                 in_stock, cost, captured_at
    Returns one row per (day, category) with the four core KPIs.
    """
    d = obs[obs["captured_at"].dt.strftime("%Y-%m-%d") == day].copy()

    # Benchmark = median of IN-STOCK competitor prices per product.
    live = d[d["in_stock"]]
    bench = (live.groupby("product_id")["price"].median()
                 .rename("bench_price"))
    d = d.drop_duplicates("product_id").set_index("product_id")
    d = d.join(bench)

    # Per-product KPIs.
    d["index"] = d["our_price"] / d["bench_price"]
    d["is_win"] = d["our_price"] <= d["bench_price"]        # we lead on price
    d["margin"] = (d["our_price"] - d["cost"]) / d["our_price"]
    d["has_obs"] = d["bench_price"].notna()

    # Roll up to one row per category.
    g = d.groupby("category")
    summary = pd.DataFrame({
        "day": day,
        "price_index": g["index"].median(),               # median position
        "win_rate": g["is_win"].mean(),                    # share we win
        "gross_margin": g["margin"].median(),
        "coverage": g["has_obs"].mean(),                   # obs / assortment
        "n_products": g.size(),
    }).reset_index()
    return summary.round(4)

Running this against a day of observations yields a compact frame that is the single source of truth for every downstream surface:

#   category  day         price_index  win_rate  gross_margin  coverage  n_products
# 0 audio     2026-07-15        0.982     0.612         0.284     0.874          412
# 1 kitchen   2026-07-15        1.041     0.398         0.351     0.796          688

Notice that price_index < 1 for audio (we are 1.8% below the median competitor) pairs with a healthy 61% win rate, while kitchen sits 4.1% above market with a sub-40% win rate — exactly the trade-off a merchandiser needs to see in one glance. The price positioning and competitive benchmarking work defines how those benchmarks are constructed; reporting consumes them.

Aggregation, Materialization & Report Rendering #

Two rendering paths hang off the summary frame, and they should share code up to the last mile. A static report is a self-contained HTML or PDF plus a CSV attachment, delivered on a schedule to people who will not open a BI tool. A dashboard is an interactive surface that queries the materialized table on demand. Both read the same daily_summary rows, which is what keeps the emailed number and the dashboard number identical.

For the static path, pandas’ own Styler renders a presentation-grade HTML table without a templating engine, and a small matplotlib figure carries the trend that a table cannot:

import matplotlib
matplotlib.use("Agg")            # headless: no display in a scheduled job
import matplotlib.pyplot as plt

def render_report(summary: pd.DataFrame, history: pd.DataFrame) -> dict:
    """Return {'html': str, 'chart_png': bytes} artifacts for delivery."""
    styled = (summary.style
              .format({"price_index": "{:.3f}", "win_rate": "{:.1%}",
                       "gross_margin": "{:.1%}", "coverage": "{:.1%}"})
              # Flag any category priced >3% above market in red.
              .map(lambda v: "color:#b91c1c"
                   if isinstance(v, float) and v > 1.03 else "",
                   subset=["price_index"]))
    html = styled.to_html()

    # 30-day price-index trend, one line per category.
    fig, ax = plt.subplots(figsize=(7, 3))
    for cat, grp in history.groupby("category"):
        ax.plot(grp["day"], grp["price_index"], marker="o", label=cat)
    ax.axhline(1.0, color="#6b7280", linestyle="--", linewidth=1)  # parity
    ax.set_ylabel("price index (1.0 = parity)")
    ax.legend(loc="upper left", fontsize=8)
    fig.autofmt_xdate()

    from io import BytesIO
    buf = BytesIO()
    fig.savefig(buf, format="png", dpi=120, bbox_inches="tight")
    plt.close(fig)
    return {"html": html, "chart_png": buf.getvalue()}

The materialization step is what makes the dashboard fast and the report reproducible: write each day’s summary rows into a daily_summary table keyed on (day, category), upserting so a re-run overwrites rather than duplicates. A dashboard querying SELECT * FROM daily_summary WHERE day >= now() - 30 returns in milliseconds because the expensive group-by already ran once in the scheduled job, not on every page load. This is the classic BI pattern — precompute the aggregate, serve from the summary — and it is the difference between a dashboard that loads instantly and one that hammers the price store on every refresh.

def materialize(summary: pd.DataFrame, engine) -> None:
    """Idempotent upsert of one day's rows into the summary table."""
    day = summary["day"].iloc[0]
    with engine.begin() as conn:
        conn.execute(
            text("DELETE FROM daily_summary WHERE day = :d"), {"d": day})
        summary.to_sql("daily_summary", conn, if_exists="append", index=False)

Deleting the day’s rows before inserting makes the whole operation idempotent: run it once or five times for 2026-07-15 and the table ends in the same state. That property is non-negotiable for a scheduled job that will occasionally be retried after a transient failure. The concrete mechanics of putting this on a scheduler — cron, GitHub Actions, or APScheduler — with backfill and delivery retries are worked end to end in scheduling price reports with Python.

Scheduled Delivery & CI-Driven Reporting #

Delivery is where reports quietly die. A perfectly correct summary that fails to reach the merchandiser’s inbox because an SMTP credential rotated is, operationally, a missing report. Treat delivery as a first-class step with its own error handling, not a fire-and-forget send() at the end of the job.

Two delivery channels cover most needs. Email carries the full static artifact — the styled HTML inline and the CSV plus chart as attachments — to a distribution list. Slack carries a terse digest: three headline numbers and a link to the dashboard, posted to a pricing channel where a threshold breach can start a conversation immediately. The digest deliberately omits detail; Slack is for “look at this now”, the attached report is for the audit trail.

import ssl, smtplib
from email.message import EmailMessage

def deliver_email(html: str, csv_bytes: bytes, chart: bytes,
                  to: list[str], day: str) -> None:
    msg = EmailMessage()
    msg["Subject"] = f"Competitive price report — {day}"
    msg["From"], msg["To"] = "pricing-bot@example.com", ", ".join(to)
    msg.set_content("HTML report attached; enable HTML to view inline.")
    msg.add_alternative(html, subtype="html")
    msg.add_attachment(csv_bytes, maintype="text", subtype="csv",
                       filename=f"price_summary_{day}.csv")
    msg.add_attachment(chart, maintype="image", subtype="png",
                       filename=f"trend_{day}.png")
    ctx = ssl.create_default_context()
    with smtplib.SMTP_SSL("smtp.example.com", 465, context=ctx) as s:
        s.login("pricing-bot@example.com", os.environ["SMTP_PASSWORD"])
        s.send_message(msg)

The natural home for a daily report job is CI-driven reporting: a scheduled GitHub Actions workflow (or GitLab schedule) that checks out the code, runs the aggregation and delivery, and — crucially — fails loudly when the job errors. CI gives you three things a cron box on a forgotten VM does not: the run history is visible, secrets live in the platform’s secret store rather than a .env on disk, and a failed run pages someone through the normal build-notification path. The same alerting muscle that price anomaly alerting and monitoring applies to the data, CI applies to the report job itself — a report that silently stopped running is its own incident.

Configuration & Threshold Tuning #

Cadence, metric set, and audience are not universal constants; they are per-report configuration that different stakeholders need tuned to their decision loop. A pricing analyst wants the full assortment daily; a VP wants a weekly one-page roll-up; a category manager wants their category the moment a competitor undercuts a hero SKU. Encode this as versioned configuration rather than forking the job, so the definition of each report is auditable and a cadence change is a config commit, not a code change.

Three parameters recur across every report and deserve explicit tuning. Coverage floor: suppress or badge any segment whose coverage falls below a threshold (commonly 60–70%), because a KPI over thin data misleads more than a blank cell. Staleness window: how old an observation may be and still count as “fresh” for the period — a daily report treating a five-day-old price as current will misstate position during fast-moving promotions. Aggregation statistic: median versus mean for the index. Median resists the skew of a single mispriced marketplace listing and is the safer default; mean is only appropriate once outliers have been filtered upstream by statistical outlier detection for price data.

Failure Modes & Mitigations #

Reporting fails quietly, which makes it dangerous — a wrong number looks exactly like a right one. Each failure mode below has a mitigation that belongs in the job, not in a stakeholder’s head.

• Silent coverage collapse. A scraper regression halves the day’s observations; every KPI still renders, but over a fraction of the assortment. Mitigation: compute coverage first and either abort the report or stamp a prominent low-coverage banner when it drops below the floor. Never ship a confident win rate over 30% coverage.
• Timezone and cutoff drift. “Today” is ambiguous across regions; a job that slices on server-local midnight double-counts or drops observations near the boundary. Mitigation: define the reporting day in a single declared timezone (UTC is the safe default) and slice on that, consistently, everywhere.
• Metric divergence between surfaces. The email and the dashboard disagree because one recomputes from raw data. Mitigation: the single-materialized-table rule — every surface reads daily_summary, nobody recomputes. This is the architectural point of the whole stage.
• Delivery failure treated as success. The job completes, the report never arrives. Mitigation: wrap delivery in explicit retry-with-backoff, and on final failure raise a non-zero exit so CI marks the run failed and pages someone.
• Backfill duplication. Re-running for a past day appends a second set of rows and doubles the counts. Mitigation: the idempotent delete-then-insert materialization shown earlier.

Compliance & Auditability #

A price report is often the artifact a supplier dispute or a regulatory query lands on, so each figure must be reconstructable. Stamp every report with the exact inputs that produced it: the reporting day, the timezone, the metric-definition version, the coverage achieved, and the crawl window the observations came from. When a supplier contests a MAP-enforcement decision or an executive questions a week-over-week swing, that provenance block turns an argument into a lookup.

report_manifest = {
    "report": "ops_daily_digest",
    "reporting_day": "2026-07-15",
    "timezone": "UTC",
    "metric_defs_version": "v4",
    "coverage": 0.83,
    "observation_window": ["2026-07-15T00:00Z", "2026-07-15T23:59Z"],
    "n_observations": 41_882,
    "generated_at": "2026-07-16T06:00Z",
}

Because reporting only reads competitor prices already gathered upstream, the scraping-side obligations — respecting robots.txt, rate limits, and data-use terms — are owned in the ingestion stage, not here. The reporting layer inherits one duty: never expose in a shared report any field that upstream flagged as restricted, and retain each report and its manifest for the audit period your jurisdiction requires so a historical position can always be defended with the numbers that were actually shown at the time.

Deployment Checklist #

• Define every KPI once in a shared, versioned metrics module; forbid consumers from recomputing from raw observations.
• Compute all four core metrics — index, win/loss, margin, coverage — in a single aggregation pass so they are mutually consistent.
• Materialize daily summary rows into a daily_summary table with idempotent delete-then-insert upserts.
• Point every surface (email, CSV, dashboard) at the materialized table, never at the raw price store.
• Slice the reporting day in one declared timezone; make the window explicit in the manifest.
• Enforce a coverage floor: suppress or badge any segment below threshold rather than shipping a KPI over thin data.
• Run the job under CI (scheduled workflow) with secrets in the platform store and a non-zero exit on failure.
• Wrap delivery in retry-with-backoff; a final delivery failure must fail the run, not pass silently.
• Stamp each report with a provenance manifest (day, timezone, metric version, coverage, crawl window) and retain it for the audit period.
• Verify backfill reproduces byte-identical summary rows for any historical day.

Automated price reporting is the layer that converts a competitive dataset into a decision the business actually makes. By defining metrics once, materializing them into a summary table, and serving every surface from that single source, retail teams get reports that stakeholders trust — because the emailed number, the dashboard number, and the audit record are, by construction, the same number.

Related #

• Competitive Price Intelligence & Repricing — the parent guide that frames how monitored prices drive positioning and repricing decisions.
• Price Positioning & Competitive Benchmarking — defines the benchmarks and index construction this reporting layer consumes.
• Building a Competitive Price Index — the index methodology behind the headline number in every report.
• Price Anomaly Alerting & Monitoring — the real-time counterpart to scheduled reporting for break-glass signals.
• Scheduling Price Reports with Python — the runnable recipe for putting this job on a schedule with backfill and delivery retries.
• Data Normalization & Promo Parsing Pipelines — the upstream stage that delivers the normalized, matched prices reporting assumes.