Your business knowledge. Shared across tools. Owned by you.

# Account Manager Performance Review

This is the standard, repeatable format for account manager (AM) performance

reviews. When a review is requested, always produce these two deliverables, in

this order.

**Every chart must be followed by an AI summary markdown cell** that explains, in

plain language, what is going on in the chart — who is up, who is down, where the

risk is. Compute the narrative dynamically from the chart's own dataframe (pivot

/ sort / format inline in the markdown cell) so it stays correct if the data

refreshes. The reviews should read as a story, not just a data dump.

## Deliverable 1 — Year-over-year AM sales (bar chart)

A grouped bar chart of **net (post-discount) revenue by account manager**,

current year vs prior year, as the opening view.

- One bar pair per AM (`xOffset` on year), sorted by AM name or by current-year

revenue.

- Net revenue = `SUM(orders.post_discount_revenue)`.

- **Align the timeframe.** Probe `MAX(purchased_at)` first, then filter BOTH

years to Jan 1 through the same day-of-year. Never compare a full prior year

against a partial current year. The day-of-year filter

`(EXTRACT(MONTH...)*100 + EXTRACT(DAY...)) <= MMDD` handles this cleanly.

- **Always include the apples-to-apples note.** The prior year is closed and the

current year is still in progress, so the AI summary after this chart must

explicitly state that both years are clipped to the same Jan 1 -> cutoff window

(and name the cutoff date). Otherwise every AM looks like they are shrinking

simply because the current year is partial.

## Deliverable 2 — Discount intensity by AM

The goal of this view is to surface **which AMs lean on discounts to drive

sales**, because heavy discounting erodes the bottom line even when top-line

sales look healthy.

- Primary metric: **effective (revenue-weighted) discount rate** =

`1 - SUM(post_discount_revenue) / SUM(pre_discount_revenue)`.

Prefer this over `AVG(discount_percentage)` — it weights large discounted

deals appropriately and is the executive-grade figure.

- Also surface **discount dollars given away** =

`SUM(pre_discount_revenue - post_discount_revenue)` so the absolute cost is

visible, not just the rate.

- Sort descending by effective discount rate so the heaviest discounters are at

the top. A small-volume AM with a high rate matters less than a high-volume AM

with a high rate — keep order count / net revenue in the tooltip for context.

- Default window: current year to date (same aligned cutoff as Deliverable 1).

## Presentation — always deliver as a layout

The review is a stakeholder-facing report, so always arrange it into a dashboard

layout, full-width and stacked, in exactly this order — and nothing else:

1. Title / intro markdown — **the title must always be exactly "AM Perf Rev"**

(use `# AM Perf Rev` as the heading).

2. YoY sales bar chart

3. YoY AI summary

4. Discount-intensity bar chart

5. Discount AI summary

Use `clear_unlisted=true` so only these five cells appear. The underlying SQL

data cells stay in the notebook but are kept OUT of the layout (hidden). Each

cell spans the full 12 columns; charts get a taller row height than the markdown

cells.

## Standard filters (apply to every query in this review)

```sql

WHERE o.status NOT IN ('cancelled','returned')

AND o.cancelled_at IS NULL AND o.returned_at IS NULL

AND p.paper_type NOT IN ('Kraft Roll','Cardstock') -- discontinued

AND a.account_manager NOT IN ('Michael Scott','Meredith Palmer') -- not active AMs

```

- Revenue is always **net (post-discount)** unless explicitly asked for gross.

- Cardstock and Kraft Roll are discontinued — always excluded. Printer is active (reinstated 2026-06-25) and should be included.

- Michael Scott is not an account manager; Meredith Palmer was fired. Both are

always excluded from AM-level analysis.

## Reference queries

### Deliverable 1 — YoY net revenue by AM (aligned window)

```sql

SELECT a.account_manager,

EXTRACT(YEAR FROM o.purchased_at)::int AS yr,

SUM(o.post_discount_revenue) AS net_revenue

FROM orders o

JOIN accounts a ON o.account_id = a.account_id

JOIN products p ON o.product_id = p.product_id

WHERE o.status NOT IN ('cancelled','returned')

AND o.cancelled_at IS NULL AND o.returned_at IS NULL

AND p.paper_type NOT IN ('Kraft Roll','Cardstock')

AND a.account_manager NOT IN ('Michael Scott','Meredith Palmer')

AND (EXTRACT(MONTH FROM o.purchased_at)*100 + EXTRACT(DAY FROM o.purchased_at)) <= 630 -- align to MAX(purchased_at) day-of-year

AND EXTRACT(YEAR FROM o.purchased_at) IN (2025, 2026)

GROUP BY 1, 2

ORDER BY 1, 2;

```

Chart: grouped bars, `x=account_manager`, `y=net_revenue` (axis format `~s`),

`color` + `xOffset` on year. Convert year to string via `transform_calculate`

(`toString(datum.yr)`) before using it in a scale domain.

### Deliverable 2 — Discount intensity by AM (current YTD)

```sql

SELECT a.account_manager,

SUM(o.pre_discount_revenue) AS gross_revenue,

SUM(o.post_discount_revenue) AS net_revenue,

SUM(o.pre_discount_revenue - o.post_discount_revenue) AS discount_dollars,

1 - SUM(o.post_discount_revenue)/NULLIF(SUM(o.pre_discount_revenue),0) AS eff_discount_rate,

COUNT(*) AS n_orders

FROM orders o

JOIN accounts a ON o.account_id = a.account_id

JOIN products p ON o.product_id = p.product_id

WHERE o.status NOT IN ('cancelled','returned')

AND o.cancelled_at IS NULL AND o.returned_at IS NULL

AND p.paper_type NOT IN ('Kraft Roll','Cardstock')

AND a.account_manager NOT IN ('Michael Scott','Meredith Palmer')

AND EXTRACT(YEAR FROM o.purchased_at) = 2026

AND (EXTRACT(MONTH FROM o.purchased_at)*100 + EXTRACT(DAY FROM o.purchased_at)) <= 630

GROUP BY 1

ORDER BY eff_discount_rate DESC;

```

Chart: single-series bars sorted `-y` on `eff_discount_rate` (axis format `.0%`),

with discount dollars, net revenue, and order count in the tooltip.

## Closing summary

Always end the review with a short markdown summary that calls out: top YoY

gainers and decliners, and the AMs whose discount intensity is out of line with

their peers (high rate and high dollars given away). The point of the discount

view is to flag margin erosion, not just rank sales.