# Dr Expense Analysis > Top expense categories, monthly expense trends, and concentration analysis from real aggregated data. Self-contained — discovers the client's financials table and fields itself, no profile or setup step required. - Skill: `datarails/dr-expense-analysis` (Agent Skill) - Install (CLI): `npx skillmds@latest add datarails/dr-expense-analysis` - Raw SKILL.md: https://api.skillmd.com/api/skills/datarails/dr-expense-analysis/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Coding & Dev Tools - Author: Datarails (https://skillmd.com/u/datarails) - Updated: 2026-09-10 - Page: https://skillmd.com/skills/datarails/dr-expense-analysis --- # Expense Analysis Where the money is going — top expense categories with complete totals (not sample estimates), monthly trend, and concentration analysis. Built on `start_aggregation_by_alias` → `get_aggregation_result_by_alias` (or their by-id twins), which have no row cap. This skill is **self-contained**: it discovers the client's financials table and field names itself (Step 2). It does not depend on a saved profile, a learn step, or any prior setup — every Datarails environment names its table and fields differently, so discovery happens inline, once per conversation. ## Workflow ### Step 1: Verify Authentication If a tool call fails with auth or connection error, tell the user to connect via Connectors UI ("+" → Connectors → Datarails → Connect), then stop. ### Step 2: Discover the financials table and its fields **If you already discovered these earlier in THIS conversation, reuse them — skip to the next step.** Discovery is cheap but not free; do it once per conversation, then carry the values forward. 1. `list_data_models`. Pick the financials table: the one whose name (or alias) matches `/financial|cube|p&?l|ledger|gl/i`; if none match, the largest by row count. Note **both** its numeric `id` and its `alias` (the alias may be empty). **Prefer the alias path when an alias exists** — friendlier field names, far fewer tokens. 2. Fields. If the table has an alias, `list_aliased_fields()`; otherwise `get_fields_by_id()` (capture each field's numeric `id` — the by-id tools address fields by id). Bind these by case-insensitive match on the field alias/name (respecting the noted type): - `` — numeric: `^amount$` → `transaction_amount` → `value` - `` — categorical: `^scenario$` → `^version$` - `` — date/timestamp: `reporting_date` → `posting_date` → `^date$` - `` — categorical, one per hierarchy level: `dr_acc_l\d` → `account_l\d` → `account_group_l\d` (collect every level you find — L0/L1/L2-like; the working grain among them is picked in item 3, and the next level down serves as breakdown depth) > **Alias coverage is per field, not per table.** A table having an alias does *not* mean its fields are aliased — real orgs often expose only a handful of aliased fields (e.g. ~5 of ~185 on a mapped financials table), and the load-bearing fields (`amount`, `scenario`, account groups, dates) are frequently *not* among them. Treat the alias/by-id choice **per field**: `get_fields_by_id()` returns every field with its numeric `id` and its `alias` (empty if none). Address a field by alias (via the `*_by_alias` tools) when it has one, else by numeric `id` (via the `*_by_id` tools). By-id always works — never abandon the query because the aliased set is thin. If `` or `` has no clear match, ask the user which field to use, then continue. 3. Find the expense-side account values — **at the right grain** (see the data-scope preamble below, item 2). Pull distinct values of each account-hierarchy level field: `start_distinct_values_by_alias(, )` (or `start_distinct_values_by_id(, )`) → poll the matching `get_distinct_values_result_by_*(handle)` until ready (async-fetch pattern). If a distinct call errors, fall back to `get_data_by_alias(, select=[], limit=500)` (or the by-id twin) and collect the distinct values. Pick the level whose values partition P&L flows into revenue/COGS/opex-like buckets — on many orgs the top level is the balance-sheet equation and the P&L line items live one level deeper. Call the chosen level `` and the next level down (if any) ``. At that grain, bind over the DISCOVERED values: - `` ← every P&L bucket matching `/cogs|cost|expense|opex|sg&a/i` — this **include-list** is what "expenses" means below. Never define expenses as "everything except revenue": on real orgs that sweeps balance-sheet buckets into the ranking. - `` ← `/revenue|sales|income/i` (kept out of the expense list; useful for context ratios) - Balance-sheet buckets (`/asset|liabilit|equity/i`) are excluded entirely — never report them as expenses. If a bucket has several candidates, pick the broadest top-level one; if genuinely ambiguous (or nothing expense-like matches at any level), ask the user once. Aggregation-field failures are handled reactively (see below), not pre-probed. > **Async fetch — aggregations and distinct values run as start → poll.** `start_aggregation_by_id`/`_by_alias` and `start_distinct_values_by_id`/`_by_alias` take the same arguments as the retired blocking calls (dimensions/metrics/filters; table id + field id, or alias + field alias) and return immediately with `{"status": "pending", "handle": {...}}`. Echo that `handle` back verbatim to the matching `get_aggregation_result_by_*` / `get_distinct_values_result_by_*` tool: a `{"status": "running", "retry_after_seconds": N}` response means poll again with the same handle after ~N seconds (≈5s) — it is not an error, and large jobs may take several polls; when ready, the result arrives in the familiar shape (for distinct values, pass `limit` to the result tool). An expired/unknown-handle error means restart with the `start_*` tool. *Transitional fallback:* if the `start_*` tools aren't available on the connector (older server), the blocking twins `get_aggregated_data_by_*` / `get_distinct_values_by_*` still work with the same arguments. > **Data-scope discovery — run before any aggregate (reuse anything already discovered this conversation).** > 1. **Scenario domain.** Pull distinct values of the scenario field (`start_distinct_values_by_alias`/`_by_id` → poll the matching result tool) — never assume a scenario name exists (`Budget` frequently doesn't; many orgs carry only `{Actuals, Forecast}`). For budget/plan questions, if no budget-like scenario exists, look for a planning-version-like field (alias/name matching `/plan|version|cycle|budget/i`) and use its versions as the plan side; if neither exists, say so and offer a comparison across the scenarios that do exist. > 2. **Account grain.** Pull distinct values of each account-hierarchy level field (L0/L1/L2-like). Use the level whose values partition P&L flows into revenue/COGS/opex-like buckets — on many orgs the top level is the balance-sheet equation (ASSET/LIABILITY/EQUITY/INCOME) and P&L line items live one level deeper. For P&L work, scope to P&L flows and exclude balance-sheet buckets; never present asset/liability/equity totals as revenue or expenses. > 3. **Period scope.** Discover the date field's range (distinct values of the reporting-month field, or MIN and MAX in two separate calls — one aggregation per field per call). Default every P&L question to the latest complete fiscal year (or trailing 12 closed months) — never an unscoped all-time total: financials tables are multi-year cumulative and mix balance-sheet stock with P&L flow. **Label every output with the period + scenario it covers.** > 4. **Reading GROUP BY responses.** Each response returns **exactly one row per requested group** — no subtotal rows and no grand-total row mixed into the `data` list; grand totals arrive in a separate top-level `totals` field beside the rows (`{"data": [...], "totals": {...}}`), computed across **all** groups, not just the returned prefix. **For a grand total, read `totals` — never sum the rows when the response carries `truncated: true`** (summing the returned prefix silently under-counts; dev repro: 474 of 31,455 rows summed to 21% of the true total). **`totals` combines the per-group results rather than re-scanning the rows**, so it is exact exactly when the aggregation is decomposable: SUM (sum of the group sums), COUNT (sum of the group counts), MIN, and MAX. It is **WRONG for AVG** (unweighted mean of the group averages) and **COUNT_UNIQUE** (sum of the per-group distinct counts, so a value recurring across groups is counted once per group) — true average = SUM total ÷ COUNT total (two calls: a field may be aggregated at most once per request); true distinct count = the distinct-values tools. Treat every aggregation type not named exact above — **`UNIQUE_VALUES` included**, whose cross-group de-duplication is unverified (the `COUNT_UNIQUE` behaviour above is evidence the engine may not de-duplicate across groups at all) — as not decomposable: derive it from complete rows or the distinct-values tools, never from `totals`. `totals` is absent on dimension-less aggregations (the single returned row IS the total) and may be absent on responses cached before the rollout (cache TTL ≤ 7 days) — only in those two cases is a total obtained by summing complete (untruncated) rows. Null groups arrive explicitly labeled `[null]` and are real groups; read null counts from that bucket. **Defensive filter:** keep only rows in which **every requested dimension key is present** — a roll-up row *omits* one or more keys entirely, whereas a genuine null is *present* with the value `[null]`. On a correct response this is a no-op; it guards against a stale cached response still carrying legacy subtotal and grand-total rows, each of which equals the whole total and would inflate any sum. When COUNT-ing rows per group, aggregate a different field than the GROUP BY dimension itself — a same-field COUNT of the grouped dimension can 500. > 5. **Truncated results.** Any data tool may return `{"data": [...], "truncated": true, "total_rows": N, "returned_rows": M, "guidance": "..."}` when the result exceeds the response size limit (~50 KB). The `data` prefix is **incomplete** — never compute totals, shares, or trends from it, and never present it as the full result. On aggregations the top-level `totals` field is **unaffected by truncation** (computed across all groups, not just the returned prefix) — read grand totals from it instead of re-fetching. Narrow the query (fewer dimensions, more filters, fewer selected columns — or a business metric for a named KPI) and re-fetch **only when the rows themselves are needed** beyond the cap; with `totals` present, a SUM/COUNT/MIN/MAX grand total never requires a re-fetch or chunking by dimension (AVG, COUNT_UNIQUE and UNIQUE_VALUES never read `totals` — true average = SUM total ÷ COUNT total from two calls; true distinct count = the distinct-values tools). A truncated response **without** `totals` (pre-rollout cache) cannot answer a grand-total question from its prefix. Re-run the aggregation **once** — a fresh run may miss the stale entry and return `totals`. If the re-run still carries no `totals`, stop re-running and fall back to narrowing or chunking by dimension until the responses are complete, then sum those rows. Never total the prefix. ### Step 3: Pull totals by expense category Scope first (data-scope preamble, items 1 and 3): scenario = `--scenario` if given, else the actuals-like value (`/actual/i`) from the discovered scenario domain; period = the latest complete fiscal year (or trailing 12 closed months) unless `--year` narrows it — never an unscoped all-time total. Default breakdown is `` × `` if a deeper level exists, otherwise the grain field alone. Alias path (preferred): ``` start_aggregation_by_alias( alias=, dimensions=["", ""], metrics=[{"field": "", "agg": "SUM"}], filters=[ {"name": "", "values": [""], "is_excluded": false}, {"name": "", "values": [], "is_excluded": false}, — see Date scoping below> ] ) ``` → poll `get_aggregation_result_by_alias(handle)` until ready (async-fetch pattern). By-id fallback (no alias): `start_aggregation_by_id(table_id=, dimensions=[, ], metrics=[{"field_id": , "agg": "SUM"}], filters=[{"field_id": , "values": [...]}, {"field_id": , "values": [], "is_excluded": false}, ])` → poll `get_aggregation_result_by_id(handle)` until ready. Filter rules: - **Value-list filters** match any of the listed values — expenses are an *include-list* of the discovered ``, never a NOT-IN of revenue (that would sweep balance-sheet buckets into the ranking). `is_excluded: true` turns a list into NOT-IN when you genuinely need one. - **Date scoping** can go in `filters` directly via an advanced `total_range` condition (epoch-second strings), or you can add `` to `dimensions` and filter client-side — both work; apply the default period scope one of these two ways. - **Comparisons, ranges, text matching, and null checks** are all supported via advanced filters (see the `--year` note below). If the detail-level field is rejected: retry without it (group by `` only) and note the limitation in the output. ### Step 4: Pull monthly expense trend Same scenario and period scope as Step 3. ``` start_aggregation_by_alias( alias=, dimensions=["", ""], metrics=[{"field": "", "agg": "SUM"}], filters=[ {"name": "", "values": [""], "is_excluded": false}, {"name": "", "values": [], "is_excluded": false} ] ) ``` → poll `get_aggregation_result_by_alias(handle)` until ready (async-fetch pattern). (By-id twin: `start_aggregation_by_id` with field ids and `metrics=[{"field_id": , "agg": "SUM"}]` → poll `get_aggregation_result_by_id(handle)`.) ### Step 5: Compute concentration and present Before computing anything, apply the aggregate-reading rule (data-scope preamble, item 4): every row in an aggregation response is a real category and **no total row is appended to the rows**. **Total Expenses reads from the response's top-level `totals` field** — the Step 3/4 calls are already filtered to the discovered expense buckets, so their `totals` IS total expenses, exact even when the rows are truncated (`totals` absent + complete rows: sum them; absent + truncated: re-run the aggregation). Per-category amounts, shares of that total, rankings, and bucket counts derive from the rows — complete responses only. Null groups arrive as an explicit `[null]` bucket — surface them as "(unmapped)" rather than silently merging or dropping them. Calculate client-side from the grain × detail result: - Total expenses - Top N categories by absolute amount - Each category's share of total (concentration) - Categories above 20% of total → concentration flag - From the monthly series: direction, highest/lowest month, MoM changes ``` ## Your Expense Analysis Scenario: [scenario] · Period: [period covered] Total Expenses: $[real_total] Top Expense Categories: | Category | Amount | % of Total | |------------------------|-------------|------------| | [category 1] | $[amount] | [X]% | | [category 2] | $[amount] | [X]% | | ... | | | Concentration: - Largest category: [category] at [X]% of total - Top 3 share: [X]% - [Concentration flag if top 1 > 20% or top 3 > 60%] Monthly Trend: Direction: [growing / stable / declining] Highest: [month] at $[amount] Lowest: [month] at $[amount] Things to Watch: - [Concentration risk on a single category, if any] - [Unusually large MoM jump, if any] - [Categories with sparse coverage, if any] Recommended Actions: 1. [Specific suggestion grounded in the numbers] 2. ... ``` ## Arguments | Argument | Description | Default | |---|---|---| | `--scenario ` | Scenario to analyze | Actuals-like value from the discovered scenario domain | | `--year ` | Restrict to one fiscal year (advanced `total_range` date filter, or a date dimension + client-side filter) | Latest complete fiscal year (or trailing 12 closed months) | | `--breakdown ` | Hierarchy depth for the category table (grain only vs grain + one level deeper) | `l2` if a deeper level exists, else `l1` | ## Backward compatibility - Alias-first: uses the by-alias data tools when the table has an alias, and falls back to the by-id twins otherwise (both always available). - Date ranges filter directly via an advanced `total_range` filter, or by adding the date as a dimension and filtering client-side. - Comparison, range, text-matching, and null filter operators are available via advanced filters if a narrower scope is needed. ## Handling failures **Detail-level field rejected (500):** retry grouping by `` only (or a sibling account-level field from the discovered schema); note the reduced depth in the output. If no field works, tell the user which field failed. If an alias call errors, retry the by-id twin. **Expense buckets ambiguous:** ask the user which account values at the chosen grain represent expenses (COGS/OpEx-like) — never fall back to "everything except revenue", which mislabels balance-sheet buckets as expenses. **All aggregation fails:** fall back to `get_data_by_alias` (or `get_data_by_id`) with the scenario as a value-list filter (500-row cap). Note that totals are partial. ## Related skills - `/dr-financial-summary` — top-level snapshot - `/dr-revenue-trends` — revenue counterpart - `/dr-departments` — by department/cost-center - `/dr-intelligence` — full FP&A workbook - `/dr-anomalies` — data quality check