Sales, growth and profitability

Business question

How are store sales changing, and which categories contribute revenue and gross profit? The intended user is an e-commerce manager deciding which categories and channels need investigation before changing assortment or marketing spend.

Data model

1,647 rows cover 183 days, three channels and three categories. Every session is assigned to one landing-page category and channel. Revenue and cost are stored as integer USD cents.

Metric definitions

Findings

Revenue was $291,359.54. Total revenue rose 0.12% versus August, while revenue per day rose 3.45%.

September has 30 days; August has 31. A daily comparison adjusts for month length, but not for weekday mix or seasonality.

Footwear contributes 53.0% of revenue but has a lower margin rate than accessories. This suggests reviewing assortment and cost structure; margin percentage alone does not justify reallocating spend.

Without advertising costs, returns, discounts and repeat-purchase data, this analysis cannot measure ROAS or CAC or determine which acquisition channel is most profitable.

SQL and evidence

-- SQLite. Parameters :month (YYYY-MM or NULL) and :channel (label or NULL).
-- Denominators are aggregated first; rates are never averages of row rates.
SELECT SUM(revenue_cents) / 100.0 AS revenue_usd,
       SUM(orders) AS orders,
       SUM(sessions) AS sessions,
       SUM(cost_cents) / 100.0 AS cost_usd,
       SUM(revenue_cents) / (100.0 * NULLIF(SUM(orders), 0)) AS aov_usd,
       (SUM(revenue_cents) - SUM(cost_cents)) * 1.0 /
         NULLIF(SUM(revenue_cents), 0) AS gross_margin_rate,
       SUM(orders) * 1.0 / NULLIF(SUM(sessions), 0) AS conversion_rate
FROM fact_sales_daily f JOIN dim_date d USING (date)
WHERE (:month IS NULL OR d.month = :month)
  AND (:channel IS NULL OR f.channel = :channel);

-- Complete calendar months, with an explicit per-day comparison for unequal lengths.
WITH monthly AS (
  SELECT d.month, COUNT(DISTINCT f.date) AS observed_days,
         SUM(f.revenue_cents) / 100.0 AS revenue_usd,
         SUM(f.orders) AS orders
  FROM fact_sales_daily f JOIN dim_date d USING (date)
  WHERE (:channel IS NULL OR f.channel = :channel)
  GROUP BY d.month
), compared AS (
  SELECT *, revenue_usd / observed_days AS revenue_per_day,
         LAG(revenue_usd) OVER (ORDER BY month) AS prior_revenue_usd,
         LAG(revenue_usd / observed_days) OVER (ORDER BY month) AS prior_revenue_per_day
  FROM monthly
)
SELECT *, revenue_usd / NULLIF(prior_revenue_usd, 0) - 1 AS revenue_change_rate,
       revenue_per_day / NULLIF(prior_revenue_per_day, 0) - 1 AS daily_change_rate
FROM compared ORDER BY month;

WITH category_totals AS (
  SELECT category, SUM(orders) AS orders,
         SUM(revenue_cents) / 100.0 AS revenue_usd,
         (SUM(revenue_cents)-SUM(cost_cents)) / 100.0 AS gross_profit_usd,
         SUM(sessions) AS sessions
  FROM fact_sales_daily f JOIN dim_date d USING (date)
  WHERE (:month IS NULL OR d.month = :month)
    AND (:channel IS NULL OR f.channel = :channel)
  GROUP BY category
)
SELECT *, gross_profit_usd / NULLIF(revenue_usd,0) AS gross_margin_rate,
       revenue_usd / NULLIF(SUM(revenue_usd) OVER (),0) AS revenue_share
FROM category_totals ORDER BY revenue_usd DESC;

Validation

Primary keys, foreign keys and CHECK constraints prevent invalid loads. SQL rules are in 08_quality_checks.sql; Python adds independent reconciliation checks.

These checks establish internal consistency of the synthetic fixture. They do not establish the completeness of real business events or a causal effect.

Reproduce the analysis

python3 analytics/run_analysis.py

Python 3 only; no external dependencies or network requests. The script rebuilds the local database and result files in analytics/results/.

The source contains prepared aggregates, not raw transactions or user events. Source dimensions retain their original labels; English display labels are mapped without changing the numerical data. The website loads verified extracts and recalculates filters in the browser, without a live SQL server.

Skills demonstrated

Fact-table grain, star schema design, weighted ratios, period comparison and separating revenue from profitability.

Created with Codex. Charts and controls use the standard Data Analytics runtime. The data model, SQL, metric definitions and analytical logic are available for inspection and independent adaptation.