platform/model/skills/analytics.skill.yml

# In-product SKILL: the ANALYTICS TRANSFORM LIBRARY (v1, 2026-07-18) — the Tableau-class
# table-calc + analytics capability set, mapped onto the ONE governed transform_result tool.
# Grounding: .claude/wiki/research/analytics-tools.md. Every op here EXISTS in harness/transforms.py
# (OPS) and is unit-proven in _demo_analytics.py — recipes and code version together.
#
# HOW IT WORKS: run_semantic_query -> transform_result([{op, ...}, ...]) -> make_chart|make_table.
# Ops CHAIN (each takes the previous derived result). The model picks op names + params; the
# platform computes deterministically over the exact governed rows and every derived result still
# drills to its numbers. NEVER do arithmetic in your head — there is an op for it.
key: analytics
label: Analytics transforms (table calcs, stats, business templates)

# ========================================================================= THE ADDITIVITY LAW
# The ONE rule that must never be broken: revenue, units, margin, cogs, orders are ADDITIVE — you
# may sum/accumulate them. customers (a DISTINCT count) and margin_pct / aov (RATIOS) are NOT
# additive — summing them over months or groups OVER-COUNTS and is forbidden. The accumulating ops
# (running_total, share_of_total, cum_share, moving_sum, abc_classify, concentration) REFUSE a
# non-additive measure with an error. For a CUMULATIVE or TRAILING distinct count / ratio, use the
# re-query ops ytd / rolling (they re-run the governed query per window — correct by construction),
# never running_total/moving_sum.

# ========================================================================= THE OP CATALOG (families)
ops:
  ordering_rank: >
    sort{by,direction} · head{n} · bottom_n{by,n} · rank{by,direction} · rank_pct{by,direction}
    (percentile rank 0-100) · ntile{by,tiles,direction} (quartile=4/decile=10 buckets) ·
    top_n{by,n,other} (N largest + honest Other) · add_total{label} (grand-total row).
  part_to_whole: "share_of_total{of} (adds <of>_share_pct) · cum_share{of} (pareto prep, sorted). ADDITIVE-only."
  running_moving: >
    running_total{of} (ADDITIVE-only) · running_avg{of} · running_max{of} · running_min{of} ·
    running_count{} (cumulative # of rows) · moving_average{of,window} · moving_sum{of,window}
    (ADDITIVE-only) · moving_median{of,window} · rolling_std{of,window} (trailing volatility).
    running_avg/max/min and the moving averages/medians/std are smoothings — valid on any measure.
  period_over_period: >
    diff{of} (vs previous row, one column) · pct_change{of} · lag{of,k} · lead{of,k} ·
    diff_from_first{of} · index_to_100{of} (rebase a series to 100) · percent_of_max{of} ·
    compare{a,b,how:diff|pct|ratio} (between TWO existing columns, e.g. revenue vs revenue_ly).
    All row-wise — safe on any measure (a period-over-period change of a ratio is fine).
  distribution_stats: >
    bin{of,bins} (histogram buckets) · describe{of} (count/mean/median/std/min/p25/p75/max[/sum]) ·
    zscore{of} · outliers{of,method:zscore|iqr,k} · winsorize{of,p} · clip{of,lo,hi} ·
    normalize{of} (0-1) · correlate{x,y} (Pearson r) · weighted_average{of,weight} ·
    safe_ratio{numerator,denominator} (row-wise, guarded) · product{a,b}.
  business_templates: >
    abc_classify{of,a=80,b=95} (Pareto A/B/C tiers, ADDITIVE-only) · concentration{of}
    (HHI+Gini+top1/5/10 share, ADDITIVE-only) · contribution_to_change{of} (needs <of>_delta from
    yoy — who drove the change) · rfm{recency,frequency,monetary,tiles=5} · funnel_rates{of}
    (stage step% + overall%).
  modeling: >
    trend_line{of} (adds <of>_trend fitted line to draw over the series) · regression{x,y}
    (slope/intercept/r2) · cagr{of} (compound per-period growth) · growth_rate{of} (total first->last).
    Transparent least-squares only — no black-box models.
  reference_annotations: >
    reference_line{of,stat:mean|median|min|max} (adds <of>_ref constant) · reference_band{of,
    method:stddev|percentile,k} (adds <of>_band_lo/_hi) · target_line{value} · xmr_limits{of}
    (Wheeler control chart: adds <of>_center/_ucl/_lcl + <of>_signal bool — the business-native
    way to flag a REAL signal vs noise in a monthly series). Draw the constant columns as extra
    series on a combo/line chart (the Analytics-pane average/reference/target/control line).
  reshape: >
    pivot{index,column,value} (long->wide crosstab; RAISES on collision — regroup the query, never
    a hidden sum) · unpivot{keep,columns} (wide->long melt) · filter_rows{col,cmp,value}
    (cmp: >,>=,<,<=,==,!=,contains — a 'having' clause) · dedupe{by} · resample{grain} (fill a
    GAPPY date spine with None rows — run BEFORE window ops on sparse/filtered time series).
  requery_windows: >
    yoy{} (adds <m>_ly/_delta/_yoy_pct; needs date_from/date_to) · ytd{of} (CORRECT cumulative
    year-to-date, incl. distinct counts) · rolling{of,window} (CORRECT trailing-N-period value,
    incl. distinct counts — T12M revenue, trailing-3-mo customers) · forecast{of,periods,method:
    linear|seasonal_naive} (appends future rows with <of>_forecast). These re-run the governed
    query per window — the ONLY correct path for cumulative/trailing NON-additive measures.

# ========================================================================= RECIPES (compose ops)
recipes:
  - ask: "80/20 — which [customers|SKUs] carry the book? / ABC classification / concentration"
    plan: >
      run_semantic_query(group_by=[dim], measures=[revenue], sort=-revenue, limit=1000) ->
      transform_result([{op: abc_classify, of: revenue}]) -> make_table  (A/B/C tiers + cum %).
      For a single concentration read: transform_result([{op: concentration, of: revenue}]) ->
      make_kpi/table (HHI, Gini, top-5 share). For the picture: make_chart(kind=pareto).
    guard: "abc_classify / concentration are ADDITIVE-only (revenue/units/margin) — never on customers."

  - ask: "cumulative distinct customers YTD / running unique customers this year"
    plan: >
      run_semantic_query(topic=sales_lines, measures=[customers], grain=month, date_from, date_to)
      -> transform_result([{op: ytd, of: customers}]) -> make_chart(kind=line, x=period,
      y=customers_ytd)
    guard: >
      NEVER running_total on customers (distinct counts don't sum). ytd re-runs the query
      Jan1->each month end — the correct cumulative distinct count. Same law: rolling for trailing.

  - ask: "trailing-12-month / LTM [revenue|customers] (smooth out seasonality)"
    plan: >
      run_semantic_query(measures=[revenue], grain=month, date_from, date_to) ->
      transform_result([{op: rolling, of: revenue, window: 12}]) -> make_chart(kind=line,
      x=period, y=revenue_roll12)
    guard: "rolling re-queries the trailing window — correct for customers (distinct) too, unlike moving_sum."

  - ask: "forecast / project the next [3] months of [revenue]"
    plan: >
      run_semantic_query(measures=[revenue], grain=month, date_from, date_to) ->
      transform_result([{op: forecast, of: revenue, periods: 3}]) -> make_chart(kind=line,
      x=period, y=revenue, y2=revenue_forecast) or combo
    guard: >
      transparent linear/seasonal-naive projection, NOT a statistical model — say "a simple trend
      projection, not a forecast model". Use method=seasonal_naive when the series is seasonal and
      you have >= one full cycle of history.

  - ask: "add an average line / target line / ±1 std band to a chart"
    plan: >
      transform_result([{op: reference_line, of: revenue, stat: mean}]) then make_chart(kind=combo,
      y=revenue, y2=revenue_ref). Target: [{op: target_line, value: 500000}] -> make_chart(
      kind=bullet, y=revenue, y2=target). Band: [{op: reference_band, of: revenue, method: stddev,
      k: 1}] -> table/chart with revenue_band_lo/_hi.
    guard: "the reference is drawn as an extra series/column — never annotate by editing the answer text."

  - ask: "who DECLINED the most YoY / biggest movers, and who drove the total change"
    plan: >
      run_semantic_query(group_by=[order_partner], measures=[revenue], team_id?, date_from, date_to,
      limit=1000) -> transform_result([{op: yoy}, {op: contribution_to_change, of: revenue},
      {op: sort, by: revenue_delta, direction: asc}, {op: head, n: 20}]) ->
      make_table(columns=[order_partner, revenue, revenue_ly, revenue_delta, revenue_delta_share_pct])
    guard: "yoy needs date_from/date_to; contribution_to_change needs the <of>_delta yoy adds first."

  - ask: "distribution / outliers of [order value|customer size]"
    plan: >
      run_semantic_query(group_by=[entity], measures=[revenue], limit=1000) ->
      transform_result([{op: describe, of: revenue}]) -> make_table (or {op: outliers, of: revenue,
      method: iqr} then filter_rows revenue_outlier == true, or {op: bin} -> histogram)
    guard: "describe/outliers operate on per-entity values — group by the entity first, not a trend."

  - ask: "crosstab / pivot [revenue] by [month] x [business unit]"
    plan: >
      run_semantic_query(measures=[revenue], grain=month, group_by=[team]) ->
      transform_result([{op: pivot, index: period, column: team, value: revenue}]) -> make_table
    guard: >
      pivot is a pure reshape — if it RAISES a collision, the query grain is too coarse (two rows
      per cell); regroup the query. It never hides a sum.

  - ask: "is [X] correlated with [Y]? / relationship between two measures"
    plan: >
      run_semantic_query(group_by=[entity], measures=[X, Y], limit=1000) ->
      transform_result([{op: correlate, x: X, y: Y}]) -> report r; for the picture make_chart(
      kind=scatter, x=X, y=Y). regression{x,y} adds slope/intercept/r2.
    guard: "correlation is not causation — say so; name the outliers rather than hiding them."

  - ask: "is [this month's revenue] a REAL signal or just noise? / control chart / XmR"
    plan: >
      run_semantic_query(measures=[revenue], grain=month, date_from, date_to) ->
      transform_result([{op: xmr_limits, of: revenue}]) -> make_chart(kind=combo, x=period,
      y=revenue, y2=revenue_ucl) and report the <of>_signal points as the real signals
    guard: >
      XmR (mean ± 2.66·mean-moving-range) is how this business separates signal from noise
      (warehouse/expenses use it). A point is only a SIGNAL if <of>_signal is true — don't call a
      within-limits wiggle a trend.

  - ask: "[any trend/cumulative question on a filtered or sparse monthly series]"
    plan: >
      run_semantic_query(measures=[...], grain=month, ...) -> transform_result([{op: resample,
      grain: month}, {op: running_total, of: revenue}])  # resample FIRST
    guard: >
      if a month has no rows (filtered scope, a quiet SKU), the window ops (running_total,
      moving_average, diff, ytd) would silently skip the gap and misalign. resample inserts the
      missing months as None first so the series is honest.

context: >
  This library is the analytics half of the tool registry (charting.skill.yml is the viz half).
  The additivity law is absolute: additive = revenue/units/margin/cogs/orders; NOT additive =
  customers (distinct) and margin_pct/aov (ratios). When an accumulating op errors on additivity,
  switch to the re-query op (ytd/rolling) — do not work around it. rfm needs recency (days since
  last order), which is not yet a semantic metric — frequency=orders and monetary=revenue exist,
  so if asked for RFM today, deliver F+M scoring and report recency as the missing input (a gap).
  Every derived result keeps its result_id and drills to rows; saved views replay the whole chain.