← ALL BLUEPRINTS / AGENT BLUEPRINT
Revenue analyst agent for DTC ecommerce brands on Salesforce that reconciles marketing's attributed revenue with sales' net revenue after returns
A custom AI data agent that pulls orders, returns, ad spend, wholesale pipeline and support data through MCP connectors into one warehouse, applies a single return-adjusted revenue definition, and answers founder questions in plain English with the reconciliation shown. It ends the quarter-end standoff where marketing quotes attributed gross revenue and sales quotes net booked revenue, and replaces it with a weekly executive digest that explains every gap in dollars.
| ROLE | FOUNDER / LEADERSHIP | PLATFORM | CUSTOM |
|---|---|---|---|
| INDUSTRY | ECOMMERCE / DTC | ||
| DRAWN BY | HACKING DEMAND | ISSUED | OCTOBER 25, 2026 |
What The Revenue Analyst does
A 51 to 200 person direct-to-consumer brand with a growing wholesale arm closes the quarter and gets two revenue numbers. Marketing's comes from ad platform and web analytics attribution, counted at checkout, including shipping and tax, before returns, and with the same order often claimed by two channels. Sales runs wholesale accounts and retail partners in Salesforce and reports invoiced revenue net of credits, while the storefront's own report shows a third figure. The founder spends the first leadership meeting of every quarter deciding whose spreadsheet is right. The Revenue Analyst works for the founder and leadership team. It owns the metric dictionary (gross sales, net sales after returns and discounts, contribution margin, attributed versus incremental revenue, DTC versus wholesale), the nightly reconciliation that maps every order to one source of truth, the weekly executive digest, and on-demand answers to questions like why net revenue dropped while ad-attributed revenue rose. It does not set targets, change attribution models on its own, or touch customer records.
Custom build
The brand runs Salesforce for wholesale and a separate storefront for DTC, and the disagreement lives in the gap between those systems and the ad platforms, so no single CRM-native assistant can see the whole picture. A custom AI build on top of a cloud warehouse with a governed metric layer fits better: the agent queries one modeled source through read-only MCP connectors, every number is computed from the same versioned definitions, and the reconciliation logic is code that can be reviewed. n8n handles the extraction jobs and alerting cheaply, a dashboard tool such as Databox can mirror the digest metrics for anyone who prefers a screen, and because the client wants it built and maintained, someone owns connector breakages after storefront or ad platform updates rather than leaving a stale agent behind.
What starts it
- Monday 6am: build and send the weekly executive digest covering the prior week, quarter to date, and the same week last year
- A leader asks a question in the team chat channel or the agent's chat interface (for example, why marketing's Q3 number is higher than the board number)
- Nightly reconciliation finds the marketing and finance revenue figures for any day diverging beyond the agreed tolerance
- Quarter close or a peak-season window ends (holiday, a major sale event), prompting a return-adjusted restatement once the return window closes
- A source connector fails or a source's row count or totals swing outside normal range, pausing answers that depend on it
What it reads
- Salesforce (wholesale accounts, opportunities, orders, invoices and credits for retail partners)
- Ecommerce storefront platform and payment processor (orders, refunds, discounts, shipping and tax lines) via API
- Returns management platform and 3PL or warehouse system (return requests, received units, restock status, ship dates)
- Ad platforms and web analytics (spend, attributed conversions, UTM and click data) synced into the warehouse
- Help desk such as Help Scout (ticket volume by reason: where is my order, returns, damaged items) for cost-to-serve context
- Cloud data warehouse with a governed metric layer, exposed to the agent through read-only MCP connectors
How it works, step by step
- Lock the metric dictionary with marketing, sales and financeBefore any build, run one working session to write down definitions: gross sales (merchandise only, excluding shipping and tax), net sales (after discounts, refunds and received returns), DTC versus wholesale split, and the rule that attributed revenue is a marketing diagnostic, never a revenue line. The founder signs off and the dictionary lives in the warehouse metric layer, versioned, so the agent can only compute from approved definitions.
- Land and model every source nightlyn8n jobs pull storefront orders and refunds, Salesforce wholesale orders and credits, returns platform and 3PL events, ad spend, and help desk ticket counts into the warehouse. Models deduplicate orders claimed by multiple channels, convert currencies, and tag each order with one channel of record so the same dollar is never counted twice.
- Run the reconciliation bridgeFor each day and quarter to date, the agent builds a bridge from marketing's attributed figure to the official net number: minus shipping and tax, minus double-attributed orders, minus discounts, minus refunds and returns, plus wholesale invoices not visible to marketing. Each line is a dollar amount with a drill-down query, so the gap stops being a debate and becomes a list.
- Answer leadership questions in plain EnglishWhen the founder asks a question, the agent queries the warehouse through MCP, answers in two or three sentences, shows the number, the definition used, the date range, data freshness, and the SQL it ran. If a question needs a definition that is not in the dictionary (for example, revenue by influencer code), it says so and routes the request instead of improvising.
- Compose the weekly executive digestEvery Monday the agent writes a one-page digest: net sales DTC and wholesale versus plan and last year, the marketing-to-net bridge, return rate trend by product category, blended acquisition cost against net (not gross) revenue, and support tickets per hundred orders as a cost-to-serve signal. It flags anything that moved more than the agreed threshold and states the likely driver with the evidence.
- Restate peak periods when returns settleFor holiday and big sale windows the digest marks revenue as provisional with an estimated return reserve based on the brand's own trailing return rates by category. When the return window closes, the agent publishes a restated figure and a short note on how far provisional differed from actual.
- Monitor health and keep the build currentThe maintaining team watches connector health, schema changes from storefront or ad platform updates, and question logs. Unanswered or escalated questions become dictionary additions or model fixes on a regular cadence, so the agent's coverage grows with the business.
When a person takes over
- Definition disputes: when marketing or sales challenges how a metric is computed, the agent shows the current definition and routes the dispute to the finance lead as dictionary owner; only a signed-off change updates the metric layer
- Unexplained variance: if the reconciliation bridge leaves a residual gap above tolerance that no known line item explains, the agent stops calling the number final and assigns the investigation to the data owner on the maintaining team
- Board and investor numbers: any figure leaving the leadership team (board update, lender report, investor deck) is drafted by the agent but approved by the finance lead before use
- Attribution model changes: requests to change how channels get credit go to the head of marketing and the founder together, because the agent never rebalances credit between teams on its own
Rules it follows
- Read-only access everywhere: MCP connectors use service accounts that cannot write to Salesforce, the storefront, ad platforms or the help desk
- Every answer carries its definition, date range, freshness timestamp and query; if a source is stale or failed its checks, the agent says so and does not answer from partial data
- Peak-season revenue is labeled provisional until the return window closes, and the agent never reports holiday gross sales as net or compares provisional to settled figures without saying so
- No customer-level personal data in digests or chat answers; outputs are aggregated by channel, product category, region or account tier, and order-level drill-downs stay inside the warehouse for authorized analysts
- Attributed revenue is always shown as a marketing diagnostic beside, never in place of, net sales, so channel credit cannot be summed into a revenue total
How you know it works
- Size of the unexplained residual between marketing's reported revenue and official net sales each quarter, tracked toward zero
- Hours leadership spends reconciling numbers before monthly and quarterly reviews
- Share of founder questions answered without a human analyst, and share escalated due to missing definitions
- Digest delivered on time each Monday with all sources passing freshness checks
- Gap between provisional and restated peak-season net revenue, as a measure of return reserve accuracy
From pilot to live
- Pilot: one quarter, one bridgeAgree the metric dictionary, connect the storefront, Salesforce, returns platform and the top two ad platforms, and reconcile the last closed quarter. Leadership checks the agent's bridge against the numbers each team presented and resolves every line before the agent goes live.
- Weekly digest and chat answersTurn on the Monday digest for the founder and leadership team and open chat questions. Run alongside existing team reports for four to six weeks, logging every question the agent could not answer and every number someone disputed, then close those gaps.
- Full rollout with peak-season modeAdd remaining ad channels, the help desk and 3PL data, enable provisional and restated reporting for peak windows, and retire the separate marketing and sales revenue slides in favor of the digest. The maintaining team takes over ongoing schema fixes, connector upkeep and dictionary changes.
Tools that fit
n8n
Workflow automation platform
Databox
Dashboards and AI analytics
Help Scout
Shared inbox & help desk
Airtable
Visual data organization
Some tool links are partner links. Hacking Demand may earn a commission at no extra cost to you.
This blueprint is a version of The Revenue Analyst (data agent) ↗
Want The Revenue Analyst built for you?
Our experts build it inside your stack. Then we hand it off, maintain it, or operate it with you. Agents and programs get better with tuning, so most teams keep us on to run them. Bring this blueprint to a scoping call.
Scope this agent ↗ Get your own blueprint ↗Good questions.
Straight answers.
Our holiday quarter looks great in October and worse in February once returns land. How does the agent handle that?
It treats peak-period revenue as provisional and applies a return reserve based on your own trailing return rates by product category, so the digest shows both gross and expected net from day one. When the return window closes it publishes the settled figure and how far off the reserve was, which also tunes the next season's estimate.
Will this tell us whether marketing or sales was right?
Usually both were right about different things. The bridge shows exactly how marketing's attributed figure becomes net sales: shipping and tax, double-credited orders, discounts, returns, and wholesale invoices marketing never saw. Once each line has a dollar value, the conversation moves from whose number is right to which line item needs attention.
Support is swamped with where-is-my-order and return tickets. Why put that in a revenue digest?
In a high-volume, low-ticket business, support cost per order can quietly eat the margin on a channel that looks profitable. The digest shows tickets per hundred orders by channel and reason next to net revenue, so a promotion that drives orders but also drives shipping and return contacts shows up as a cost, not just a win. The agent reports counts only, never ticket contents or customer details.
Every good build
starts with a conversation.
New builds, remodels, and additions, powered by AI. Let’s plan the work.