| |
| |
| |
| |
| |
| |
| |
| |
| |
| key: analytics |
| label: Analytics transforms (table calcs, stats, business templates) |
|
|
| |
| |
| |
| |
| |
| |
| |
| |
|
|
| |
| 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: |
| - 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. |
| |
