# Dr Revenue Trends > Revenue trends, growth rates, and composition from real aggregated monthly data. Self-contained — discovers the client's financials table and fields itself, no profile or setup step required. - Skill: `datarails/dr-revenue-trends` (Agent Skill) - Install (CLI): `npx skillmds@latest add datarails/dr-revenue-trends` - Raw SKILL.md: https://api.skillmd.com/api/skills/datarails/dr-revenue-trends/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Marketing & Growth - Author: Datarails (https://skillmd.com/u/datarails) - Updated: 2026-09-10 - Page: https://skillmd.com/skills/datarails/dr-revenue-trends --- # Revenue Trends Analyze revenue patterns over time using real aggregated monthly data — growth rates, peak/trough months, composition by sub-category, and overall direction. Built on the aggregation start→poll tools (`start_aggregation_by_alias` → `get_aggregation_result_by_alias`, or the by-id twins) — no row cap, real totals, not samples. 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 an auth or connection error, tell the user to connect via the 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$` - `` — `dr_acc_l1` → `account_l1` → `account_group_l1` - `` — `dr_acc_l2` → `account_l2` (optional, for `--breakdown`) > **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. Resolve the P&L grain, then the revenue category value: `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 the distinct call errors, fall back to `get_data_by_alias(, select=[], limit=500)` (or the by-id twin) and collect the distinct values. **Check the grain before matching** (data-scope preamble below, item 2): on many orgs the top account level is the balance-sheet equation (asset/liability/equity/income-style values), where an income-like bucket is the *entire* income statement — not revenue. If the distinct values look like the balance-sheet equation rather than revenue/COGS/opex-like P&L buckets, pull distinct values one level deeper and rebind: `` := the level whose values partition P&L flows, `` := the level below it. Every filter and breakdown below uses the rebound fields. Match `` ← `/revenue|sales|income/i` **at the P&L grain** — accept an income-like value only when its siblings at the same level are COGS/opex-like line buckets, never when they are asset/liability/equity-like. If several candidates match, pick the broadest one at that grain; if genuinely ambiguous, 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 monthly revenue totals Alias path (preferred): ``` 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 fallback (no alias): `start_aggregation_by_id(table_id=, dimensions=[], metrics=[{"field_id": , "agg": "SUM"}], filters=[{"field_id": , "values": []}, {"field_id": , "values": [...]}])` → poll `get_aggregation_result_by_id(handle)` until ready. Filter rules: - **Scenario value:** `` = `--scenario` when given (verify it exists in the discovered scenario domain — preamble item 1), else the actuals-like value from that domain. Never assume a scenario name exists. - **Default period scope:** with no `--year`, do not pull all-time — scope to the latest complete fiscal year (or trailing 12 closed months) from the discovered date range (preamble item 3), using the same advanced `total_range` shape as below. Financials tables are multi-year cumulative; an unscoped total misleads. - **Scoping by year:** if the user passed `--year`, you can either add `` to `dimensions` and filter the result client-side, or pass an advanced date filter directly: `{"name": , "values": {"type": "advanced", "val": [{"condition": "total_range", "value": ["", ""]}]}}` (epoch seconds as strings; use the org's fiscal-year boundaries — don't assume Jan–Dec). Both work — date filtering is no longer rejected. - Value-list filters take `values: [...]` (set `is_excluded: true` for NOT-IN). The account category and the scenario field are the right shape. ### Step 4: Pull revenue composition (optional, if `--breakdown` or data permits) For a breakdown of revenue one level below the P&L grain (e.g. Product vs Services), apply the **same period scope and scenario as Step 3** so the composition matches the trend: ``` 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 when there's no alias: `start_aggregation_by_id(..., dimensions=[], metrics=[{"field_id": , "agg": "SUM"}], filters=[...])` → poll `get_aggregation_result_by_id(handle)` until ready. If `` is rejected (500), retry with a sibling account-level field from the discovered schema (orgs often carry in-between levels), fall back from the alias path to the by-id twin, or skip this step and present top-level only. Note the limitation in the output. ### Step 5: Pull KPI context (optional) `list_business_metrics` to see whether the org publishes named revenue KPIs (ARR / MRR / Net New ARR / Churn). It returns a flat list — each entry has `id`, `name`, `description`, `category`, `kind`, `dimensions[]`, and `status_info{}`. Filter that list client-side for revenue-related metrics by name/category. > **Render only KPIs you can source.** A KPI may come from (a) the org's metric catalog — `list_business_metrics` (ungated) for discovery; the `get_business_metric_*` data tools are feature-gated and may be absent, and USER-kind metrics often return empty — or (b) aggregation over the discovered P&L grain (revenue, expense buckets, gross/operating margin when COGS/OpEx-like buckets exist). SaaS/unit-economics metrics (ARR, MRR, churn, LTV, CAC, burn, runway, NRR) are **not** derivable from a P&L table — include them only if discovered as populated metrics; otherwise omit the card/slide entirely. Never render a placeholder, estimate, or fabricated value for a KPI you could not source. This is **discovery only** — `list_business_metrics` names the KPIs but the metric-value tools aren't available here. P&L-derivable KPIs (revenue, expense buckets, margins) can be numbered by aggregating the financials table at the discovered P&L grain, using the same aggregation start→poll shape (`start_aggregation_by_alias` / `start_aggregation_by_id` → `get_aggregation_result_by_*`) as above (the metric's `dimensions[]` and name tell you which category / field to sum). SaaS/unit-economics KPIs cannot — if the catalog reports one as populated, note it by name without a value (you can't fetch it here); otherwise leave it out of the output entirely. KPI structures vary heavily across orgs — read field names from the schema discovered in Step 2, don't hardcode them. ### Step 6: Compute and present First, read the series correctly: responses append **no total row to the rows** (preamble item 4). **Apply the item-4 defensive filter before charting** — keep only rows in which the requested date key is present, preserving the genuine `[null]` bucket; a stale cached response can carry legacy roll-up rows that omit the date key, and charting one as a month distorts the peak, trough and growth figures. Every surviving dated row is a real month. Total revenue across the period reads from the response's top-level `totals` field (exact even when the rows are truncated); the monthly series itself comes from the rows — complete responses only (on `truncated: true`, narrow and re-fetch before charting a trend). A `[null]` date bucket is unattributed revenue — report it separately, never as a month in the trend. Calculate client-side: - Total revenue across the period — from the top-level `totals` field (it includes any `[null]`-bucket revenue; if the dated months sum to less, the difference is the unattributed bucket — say so) - Average monthly revenue — from the dated monthly series (rows), never `totals` ÷ months when a `[null]` bucket exists - Peak and trough months - Growth (first month → last month) - MoM changes per month - Direction label: growing / stable / declining ``` ## Your Revenue Trends Overview: Total Revenue: $[total] Scenario: [scenario] Period: [start] to [end] ([N] months) Monthly Trend: | Month | Revenue | MoM Δ% | |-----------|------------|--------| | [month 1] | $[amount] | — | | [month 2] | $[amount] | +X% | | ... Analysis: Direction: [growing / stable / declining] Avg/month: $[mean] Peak: [month] at $[amount] Growth: [first → last] = [X]% [If --breakdown given and L2 succeeded:] Composition: | Source | Amount | Share | |---------------|------------|-------| | [source 1] | $[amount] | [X]% | | ... [KPI Context — apply the "Render only KPIs you can source" rule (Step 5). One line per KPI actually sourced; populated catalog metrics whose values can't be fetched here appear by name only. Omit the whole section if nothing was sourced — never print placeholder ARR/MRR/churn figures:] KPI Context: [KPI name]: $[sourced amount] [KPI name]: (populated metric — value not fetchable here) Insights: - [Notable pattern or change] - [Recommendation based on the data] ``` ## Arguments | Argument | Description | Default | |---|---|---| | `--scenario ` | Scenario to analyze | The actuals-like value from the discovered scenario domain | | `--year ` | Restrict to one fiscal year (client-side filter on the date dimension) | Latest complete fiscal year (or trailing 12 closed months) | | `--breakdown ` | Composition breakdown depth | None (top-level only) | ## Data layers used - Alias-first: aggregate via `start_aggregation_by_alias` → poll `get_aggregation_result_by_alias` when the table has an alias, falling back to the by-id twins (`start_aggregation_by_id` → `get_aggregation_result_by_id`). - Date ranges filter directly via an advanced `total_range` filter (epoch-second strings); adding the date as a dimension and filtering client-side still works and is optional. - Comparison / range filtering is available through advanced filters when you need it. ## Handling failures **Aggregation field rejected (500):** retry with a sibling account-level field from the discovered schema (orgs often carry in-between levels), or fall back from the alias path to the by-id twin; if none works, tell the user which field failed. **No revenue category identifiable:** ask the user which category represents revenue, given the discovered L1 values. **Single period of data:** show the snapshot for that period instead of a trend; note that trend analysis needs ≥2 periods. ## Related skills - `/dr-financial-summary` — top-level snapshot - `/dr-expense-analysis` — flip side - `/dr-forecast-variance` — actuals vs budget vs forecast - `/dr-intelligence` — full FP&A workbook