Build a live price-volume-mix revenue walk that queries the selected sales dataset each time it opens.
Use the Marquis IQ MCP server for this request.
This analysis is for {YOUR_BUSINESS_NAME}.
Build a live, published artifact titled "{YOUR_BUSINESS_NAME} PVM Walk". It is a price-volume-mix (PVM) revenue walk that queries {DATA_SOURCE} every time it opens. Follow this spec exactly. Do not hard-code a customer name, a dataset id, a connector display name, or a column list from another client. Before writing the walk query, inspect {DATA_SOURCE} for {YOUR_BUSINESS_NAME} and lock each role below to the exact field or fields on that dataset. Show those client field names in the artifact. If a required role cannot be mapped, stop and name the missing role and the fields you checked instead of guessing.
DATA SOURCE
- Dataset: {DATA_SOURCE}. Confirm it on this Marquis IQ MCP connection before querying.
- Call the connector through the mcp capability for the Marquis IQ MCP server already connected in this chat. Use tools "dataset_query" and "initialize_session_context". Call initialize_session_context once on load and ignore any failure. Run every query through dataset_query with maxRows 5000.
- Also declare the downloads capability, for CSV export.
- Cache results for 5 minutes (staleTime 300000, gcTime 24h). The "Refresh data" button bypasses the cache (refresh: true).
- On load, query the latest invoice or transaction date and show it as a "Data through" pill with that date. Turn the pill amber if the latest date is more than 3 days old.
- Revenue is net sales or invoice amount. Quantity is invoiced or sold quantity. The walk is revenue only. Do not invent cost or margin. If the dataset has no cost field, say so in the calculation notes.
ROLES
Inspect the columns on {DATA_SOURCE} and map each role to the exact field or fields for {YOUR_BUSINESS_NAME}. Record that mapping in the calculation notes. Use the client's field labels on every control, grid, and chip.
- Date (required): invoice or transaction date.
- Revenue (required): net sales or invoice amount. Do not assume a currency code in the column name.
- Quantity (required): invoiced or sold quantity.
- Intercompany (optional): a flag that marks intercompany activity. If none exists, omit the exclude-intercompany control and say so.
GRAIN
The walk grain is Organization x Warehouse (or Facility) x Customer x Part Category (or an equivalent such as Commercial Segment) x Item (or an equivalent such as Part or Product). Evaluate {DATA_SOURCE} and choose the exact field for each level. Prefer the field that matches the level's meaning for this client, not a similarly named field from another client. Where a level has both a code and a name, keep both and display name, then code.
- Organization: company, legal entity, or business unit. Include this level when a matching field exists. If it does not, omit the level and name the fields you checked.
- Warehouse or Facility: warehouse, facility, branch, plant, or ship-from location. Include this level when a matching field exists. If it does not, omit the level and name the fields you checked.
- Customer (required): customer code and customer name. If this level cannot be mapped, stop.
- Part Category, or an equivalent such as Commercial Segment (required unless Item is mapped): product category, commercial segment, product family, or the closest category-level field. Label blanks '(Uncategorized)'.
- Item, or an equivalent such as Part or Product: item, part, or product code and description. This is the finest level. If it cannot be mapped, offer only the category grain and explain why.
Label every other blank key '(None)'. If both the category level and the item level are missing, stop.
PVM LOGIC (compute server-side in one query)
1. Base rows. Group by every mapped grain level. Keep a display label for each level that has a name, description, or both a code and a name. Sum prior revenue and quantity (pr, pq) and current revenue and quantity (cr, cq) with a date range for each period.
2. Base filter. Scan only dates that fall in either period. When "Exclude intercompany" is checked and an intercompany flag exists, drop intercompany rows.
3. Grain. The user picks one. Name both options with the client's field labels, in this level order, skipping any level that was not mapped:
- Category grain (default): Organization x Warehouse or Facility x Customer x Part Category or equivalent. Do not include Item in the partition.
- Item grain: the category grain plus Item, Part, or Product.
Using window functions over the base rows, compute grain totals gpr, gpq, gcr, and gcq with SUM() OVER (PARTITION BY the grain keys). Add grn = ROW_NUMBER() OVER (PARTITION BY the grain keys ORDER BY the item key, or by the category key when Item is not mapped).
4. Classify each grain:
- L (lost): gcr = 0
- G (new): gpr = 0
- B (both): gpq is not 0 AND gcq is not 0
- X (exclusion): everything else
5. Metrics. Put these on the grn = 1 row only, so each grain counts once. All other rows get 0.
- Mix Loss = -gpr (L)
- Mix Gain = gcr (G)
- Volume Impact = (gcq - gpq) x gpr/gpq (B)
- Price Impact = (gcr/gcq - gpr/gpq) x gcq (B)
- PVM Exclusion = gcr - gpr (X)
These must reconcile: prior revenue + all five metrics = current revenue.
6. Cross-filter flags. One flag for each mapped grain level. A flag is 1 when that key is selected, or 1 when that grid has no selection.
7. Aggregate once with GROUP BY GROUPING SETS for each mapped grain level and the grand total. Identify each level with GROUPING_ID. For every metric (pr, pq, cr, cq, mix loss, mix gain, volume, price, exclusion), compute one all-flag context plus one context per grid:
- A = multiplied by every grain flag
- One context per grid = multiplied by every flag except that grid's own
Each grid uses its own context, so a grid is never filtered by its own selection.
8. Output.
- Up to Top N rows per grid, ranked by "Largest revenue" (the larger of the absolute values of pr and cr) or "Largest $ change" (the absolute value of cr - pr).
- Include the total row count for that grid.
- Always include rows that are currently selected.
- Also return the grand total, unpivoted into the all-filter total and one total per grid. The all-filter total drives the waterfall. The others are each grid's grand-total row.
9. Escape string literals as Unicode string literals with doubled single quotes. Validate dates as YYYY-MM-DD.
CONTROLS (top bar)
- Calculation grain: the category grain by default, or the item grain. Label the control with the exact fields found on {DATA_SOURCE}, for example "Company x Warehouse x Customer x Commercial Segment" and the same list plus "Part".
- Comparison preset. Anchor presets to the latest invoice or transaction date. The last closed month is the anchor's month when the anchor falls on a month end, otherwise the previous month. Options:
- Last 3 closed months vs same months prior year (default)
- Last 3 closed months vs prior 3 months
- Last closed month vs same month prior year
- Last closed month vs prior month
- Year to date vs prior year to date
- Trailing 90 days vs same days prior year
- Custom periods
- Prior-period and current-period start and end date inputs. Editing a date switches the preset to Custom. Validate that each start is on or before its end, and that the two periods do not overlap.
- Rows ranked by: Largest revenue, or Largest $ change.
- Rows per grid: 100, 250 (default), or 500.
- "Exclude intercompany" checkbox, checked by default, only when an intercompany flag exists.
- "Run comparison" button.
- A chip row for active selections. Label each chip with the client's field name, such as "Commercial Segment: Chemical". Each chip can be cleared on its own, plus a "Clear all filters" button. With no selection, show a hint explaining click and Ctrl/Cmd-click.
WATERFALL (top of page)
- KPI row:
- Prior revenue, with its date range
- Current revenue, with its date range
- Change, in amount and percent
- Like-for-like (volume + price)
- Inline SVG waterfall with 7 bars: Prior revenue, Mix loss, Mix gain, Volume impact, Price impact, PVM exclusion, Current revenue.
- Total bars grey, positive steps blue, negative steps coral.
- Dashed connector lines between bars.
- Up and down value labels in compact currency (thousands and millions). Use the dataset currency. Do not assume US dollars.
- A y-axis with readable steps that includes zero.
- A hover tooltip on each bar with the exact value.
GRIDS
One stacked panel for each mapped grain level, in this order: Organization, Warehouse or Facility, Customer, Part Category or equivalent, Item or equivalent. Skip a level that was not mapped. Title each panel with the client's field label plus "detail".
- Columns: label, Prior WAP, Current WAP, Unit price diff, Prior volume, Current volume, Volume gain/loss, Prior revenue, Current revenue, Amount gain/loss, Mix loss, Mix gain, Volume impact, Price impact, PVM exclusion.
- WAP = revenue / quantity, blank when quantity is 0 or less.
- Negatives in parentheses and coral. Positives in blue.
- Row labels use the client's field. Show name or description, then code, when both exist. A category-equivalent level with only one field shows that value.
- Layout:
- Sticky header and a pinned Grand total row.
- Sticky label column.
- The table scrolls horizontally, with a max height of about 420px.
- Header click sorts rows client-side. A per-grid search box filters the loaded rows.
- Each grid shows which filters currently apply, and a footer in the form "Top N of M by ...".
- Row selection:
- Click selects a single row. Click it again to clear.
- Ctrl/Cmd-click toggles multi-select.
- Selected rows are highlighted.
- Any selection change re-runs the query, debounced 200 ms.
- On the category grain, the Item grid shows revenue, volume, and WAP only:
- Its mix, volume, price, and exclusion cells are blank except on the grand total row.
- Its rows are not clickable.
- A note explains this, using the client's item field label.
- Switching to the category grain clears any item selection.
- If Item was not mapped, omit this grid.
- An "Export CSV" button on each grid, shown only when downloads is available.
DESIGN AND UX
- Clean analytics style: cool slate neutrals, a navy accent, Archivo headings, Libre Franklin body text, and tabular numbers.
- Full light and dark theme support via CSS tokens. Responsive down to phone width.
- A thin animated loading bar while queries run. Dim the content while busy.
- A collapsible "How the walk is calculated" section covering the exact fields chosen for each grain level on {DATA_SOURCE}, the grain choice, the classification rules above, and each metric's formula.
- Error handling branches on the MCP error code:
- needs_reauth: say to reconnect the Marquis IQ connector in Settings, then Connectors
- server_not_connected: say to add the Marquis IQ connector
- not_in_manifest or consent_required: say access was not allowed and to reload
- blocked_by_policy
- server_unavailable: retry once, only when the error is marked retryable
- tool_error: show the message
Publish it as a private artifact with the chart icon.