Direct-to-consumer ecommerce, revenue and growth reporting
Revenue Analytics Dashboards: 38 SQL Charts in Triple Whale
This is an analysis and dashboard layer, not a pipeline: ten dashboards made of thirty-eight SQL chart queries that run inside Triple Whale against the brand's own connected order, refund and ad-platform data. No ETL, scheduling, warehouse or infrastructure was built or deployed as part of it, and nothing here runs on sample or synthetic data. Fixed-window scorecards take explicit date bounds while the trend and comparison charts are written against relative dates, and no uplift or time-saving figure was measured, so none is claimed.
- Our role
- Analytics engineer, metric definitions, SQL chart layer and dashboard structure
- Timeline
- Built as ten themed dashboards, delivered as versioned SQL
- Sector
- Direct-to-consumer ecommerce, revenue and growth reporting
At a glance
- Dashboards
- 10: one analytical theme each
- Chart queries
- 38 SQL files, one per visualisation
- Revenue basis
- Gross and net of refunds, split by new and returning
- Comparison windows
- MTD vs prior MTD, WTD vs prior WTD
- Time grains
- Hourly, daily, weekly, monthly, yearly
- Dimensions
- Channel, ad platform, state, local area, customer type
- Trend dashboards
- Written without explicit dates, by design
What was breaking
The brand had revenue data and no settled way to read it. Triple Whale was connected and holding order, refund and ad-platform data, which means the hard part of collection was already solved, and the part that was not solved was every question that starts with 'compared to what'. Revenue was up, but up against which window. Revenue by channel existed, but as a share of gross or of net. New customer revenue was available, but not carried through the channel reporting, so nobody could say whether a platform was buying growth or buying repeat purchases it would have had anyway.
The second problem was refunds. Gross revenue is the number that gets quoted because it is the number that is easy to query, and in a brand with a meaningful refund rate it systematically overstates the business. Netting refunds off is not difficult, but it is fiddly in a way that matters: a refund arrives on its own date, often well after the order it relates to, so a naive refund subtraction inside the revenue window misses exactly the refunds that make the biggest difference to a recent period.
Third, there was no agreed grain. Some questions are hourly, when in the day do orders arrive, and does that differ by state. Some are weekly or monthly trend questions. Some are month-to-date comparisons that need yesterday's partial month measured against the same partial window a month ago, not against a whole month. Each of those needs a different query shape, and without a structure they get asked ad hoc, answered inconsistently, and re-asked.
Fourth, and the reason this became a repository rather than a pile of saved queries: nothing was written down. A dashboard whose metric definitions exist only inside chart configuration cannot be reviewed. When two charts disagree, there is nothing to read. Someone has to open both and reconstruct what each one meant, which is the point at which people stop trusting the dashboard and go back to asking for exports.
Revenue reported without a comparison
A figure with no matching prior window attached cannot distinguish growth from seasonality, so the interpretation was made in the room rather than in the data.
Gross revenue standing in for net
Refunds land on their own dates, often after the period that produced them, so the convenient number systematically overstates the business.
New and returning revenue not carried through
Without the split on channel and platform reporting, there was no way to tell whether a channel was acquiring customers or taking credit for repeat purchases.
Every grain answered ad hoc
Hourly, weekly, monthly and month-to-date questions each need a different query shape. Asked one at a time, they get answered inconsistently and then asked again.
No reviewable definitions
Metric logic held only in chart configuration cannot be read or diffed, so a disagreement between two charts has no resolution other than rebuilding both.
What we built
The structure came first and did most of the work. One folder per dashboard, one SQL file per chart, a scorecard query in each dashboard that states the headline figures for that theme, and ten themes chosen so that each answers a single question: overview, hourly behaviour, state-level performance, yearly performance across grains, week-to-date and month-to-date comparisons, weekly trends, monthly trends, new versus returning customers, and channel performance. A chart that does not belong to one of those themes is a sign the theme list is wrong, which is a useful thing for a dashboard set to be able to tell you.
Keeping each chart as its own file is the decision that pays off later. Every visualisation's definition is readable on its own, reviewable in a diff, and changeable without touching the others. When someone asks why two charts show different revenue, the answer is two files side by side rather than an archaeology exercise through chart configuration. That is also what makes the metric definitions arguable, which they should be. The business should be able to disagree with a definition and see precisely what it is disagreeing with.
The scorecards carry the metric work. Gross order revenue, new-customer revenue and returning-customer revenue, the last derived as the difference rather than assumed to be a separate field, are read from Triple Whale's blended statistics function for the period, and a separate refund aggregate is computed and joined in. That gives total, new-customer and returning-customer revenue both gross and net of refunds on one card, which is what stops gross and net being quoted interchangeably.
The refund aggregate takes its own date window rather than inheriting the revenue window, and that is deliberate. A refund has its own event date, frequently later than the order that produced it, so a refund subquery constrained to the revenue period would quietly miss the refunds that matter most to a recent window. Refunds are also split by customer type by joining back to the order to read its new-customer flag, because a refund against a first order means something different from a refund against a repeat one.
The comparison dashboards compare matching partial windows. Month-to-date revenue is summed from the start of the current month to today, and the prior figure is summed from the start of the previous month to the same day of that month, not to the end of it. Both sums are computed in one pass with conditional aggregation over a window that spans both periods, which keeps the comparison to a single query, and the query itself labels the direction of travel rather than leaving the reader to compare two columns. The same pattern is applied week-to-date, and both are cut by state, local area, channel and customer type.
Trend dashboards deliberately omit explicit dates. Weekly and monthly trend charts are about the shape of behaviour (the rhythm of a week, the direction of a month, the hour of day when orders arrive), and a visible calendar axis invites the reader to explain every movement with a specific event. Removing the dates is an editorial decision about what the chart is for, and it is applied consistently so that the trend dashboards read differently from the performance dashboards on purpose.
The remaining dashboards are dimensional cuts over the same definitions. State-level performance covers monthly trends by state, each state's vertical share of revenue, and the hourly contribution within a state. Channel performance covers daily revenue by ad platform and each channel's contribution share. New versus returning is reported overall and then per platform, so the split is visible at the level where budget decisions are actually made rather than only in aggregate.
One file per chart, one folder per dashboard
A visualisation's definition should be readable and diffable on its own. It makes metric logic arguable, and it turns 'why do these two charts disagree' into opening two files instead of reverse-engineering chart configuration.
Report gross and net together
Gross revenue is the convenient number and overstates the business wherever refunds are material. Putting both on the same scorecard removes the opportunity to quote one while meaning the other.
Give refunds their own window
A refund has its own event date, usually later than the order behind it. Constraining refunds to the revenue period misses exactly the refunds that most affect a recent window, so the refund aggregate takes its own range.
Compare partial windows to matching partial windows
Month-to-date must be measured against the same number of elapsed days a month earlier, never a completed month. The comparison is only honest if both sides stop at the same point.
Derive returning revenue rather than assume a field
Returning-customer revenue is total minus new-customer revenue, which guarantees the two halves sum to the whole. Reading two independent fields invites a split that does not reconcile.
Remove the calendar from trend charts on purpose
Trend dashboards exist to show the shape of behaviour. A visible date axis encourages explaining every movement with a specific event, so the dates come out and the distinction between trend and performance dashboards stays clear.
Non-negotiables
- Everything had to run inside Triple Whale against its own warehouse, which means its SQL dialect and its table-valued functions rather than a dialect of our choosing.
- No pipeline, warehouse or scheduling was in scope. The connected data was the data, and the deliverable was the layer that reads it.
- Each chart had to be independently readable. A reviewer should be able to open one file and see exactly what that visualisation claims, without tracing shared state.
- Trend dashboards had to work without calendar anchors, so that a specific week's dip is not mistaken for the shape of the business.
- Comparison charts had to compare like with like, a partial month against the same partial window in the prior month, never against a complete one.
The pipeline
Drawn as text and shapes rather than shipped as an image, so the step labels stay readable and downloadable.
The read path behind the dashboards
This is a query layer, not an ETL pipeline. Nothing here extracts or schedules. Triple Whale already holds the connected order, refund and ad-platform data; the work is the chart SQL and the themes it is organised into.
- Trigger
- Data store or CRM
- Processing step
- Branch / filter
- Delivered output
- Primary path
How the work runs
Each stage, what it does, and the specific failure it is built to prevent.
Fix the revenue definitions before building anything
Four decisions had to be settled first: whether a headline figure is gross or net of refunds, how returning-customer revenue is derived, which date a refund belongs to, and what a month-to-date figure is compared against. None of these are technical questions and all of them determine the SQL. Settling them first is what allows thirty-eight charts to agree with each other.
Definitions agreed up front prevent the most common dashboard failure: two charts that claim the same metric and show different numbers.
Build the scorecard query that carries gross and net together
The scorecard reads period revenue, new-customer revenue and total sales from Triple Whale's blended statistics function, derives returning-customer revenue as the difference so that the two halves necessarily sum to the whole, then joins a refund aggregate to produce net figures alongside the gross ones. Showing both on one card is the point: it removes the chance of quoting gross while meaning net.
Deriving returning revenue rather than reading a second field guarantees the new and returning split reconciles to total revenue.
Give refunds their own date window and their own customer split
The refund aggregate is computed in its own subquery over its own date range, wider than the revenue window, because a refund's event date is usually later than the order that caused it. It also joins back to the order to read the new-customer flag, so refunds are attributed to new and returning customers separately. A refund against a first purchase is a different signal from a refund against a repeat one.
A refund subquery limited to the revenue period would silently exclude the late refunds that most affect a recent window.
Write the comparison dashboards against matching partial windows
Month-to-date revenue is summed from the start of the current month to today; the prior figure is summed from the start of the previous month to the same elapsed day, not to month end. Both are produced in a single pass with conditional aggregation over a span covering both periods, and the query labels growth, decline or flat itself rather than leaving a reader to compare two columns. The same structure is used for week-to-date.
Comparing a partial month against a complete one is the most common way a revenue comparison lies; both sides are cut at the same elapsed point.
Cut the comparisons by the dimensions decisions are made in
Each comparison dashboard repeats the same window logic across state, local area within state, channel and customer type. Repeating the window logic per dimension rather than building one wide query keeps each chart independently readable, which is the trade this structure deliberately makes: a little duplication in exchange for charts that can be reviewed one at a time.
Build the time-grain dashboards from hourly to yearly
Hourly dashboards cover revenue by hour of day and the hourly range, plus each state's hourly contribution, which is how a brand finds out that its states do not buy at the same times. The yearly performance dashboard carries the same revenue measure at daily, weekly, monthly and yearly grain so the same question can be asked at the resolution it deserves rather than the one that happens to be available.
Strip the dates out of the trend dashboards
Weekly and monthly trend charts are written without explicit date anchors. This is an editorial decision, not a technical one: a visible calendar axis invites the reader to explain every movement with a specific event, where a trend chart exists to show the shape of behaviour. Applied consistently, it keeps the trend dashboards legible as trends and leaves the dated comparisons to the performance dashboards.
Keeps a single week's dip from being read as a change in the business.
Deliver as a versioned repository, one file per chart
Thirty-eight SQL files in ten numbered dashboard folders, each file the complete definition of one visualisation. That is the handover artefact: the dashboards can be rebuilt from it, a definition can be reviewed in a diff, and a disagreement between two charts is settled by reading two files. A dashboard set whose logic exists only in chart configuration cannot be audited, and eventually is not trusted.
Makes every metric definition readable and reviewable, which is what keeps a dashboard trusted after the person who built it has moved on.
What changed
| Aspect | Before | After |
|---|---|---|
| Reading a revenue figure | A number with no comparison attached, interpreted in the meeting it was shown in. | Current period against the matching prior period, with the direction of travel labelled in the query itself. |
| Refunds | Netted off manually when someone remembered, and not at all when they did not. | A refund aggregate joined into the scorecards, so gross and net revenue appear side by side. |
| New versus returning | A single blended revenue line that hid which half of the business was moving. | Split on the order's new-customer flag, and carried through the channel and platform dashboards. |
| Metric definitions | Re-decided per chart, so two charts could disagree about what revenue meant. | One SQL file per chart in a versioned repository, so a definition can be read, reviewed and changed deliberately. |
| Trend dashboards | Dominated by the calendar, a specific week's dip read as a pattern. | Explicit dates deliberately removed so the shape of behaviour is what the chart shows. |
What it runs on
Every tool listed with the job it actually does in this build.
Platform
Triple Whale
The analytics platform the dashboards live in. It already held the connected commerce and ad-platform data, so the engagement is the query layer rather than the collection.
Data
Triple Whale SQL editor
Executes every chart query against the platform's own warehouse, which is also what fixed the dialect and the available functions.
Blended statistics function
Table-valued function supplying period revenue, new-customer revenue and total sales, used as the base of the scorecards.
Order and refund tables
Row-level source for the dimensional cuts: event date, revenue, the new-customer flag, refund amounts, state and local area.
Warehouse date functions
Month- and week-start truncation with relative intervals, which is what makes the comparison and trend charts calendar-independent rather than hard-dated.
Integration
Connected store order data
Supplies orders, refunds, customer type and the geographic fields that the state and local-area dashboards group by.
Ad platform revenue attribution
Supplies the channel and platform dimensions behind the channel performance and new-versus-returning-by-platform dashboards.
Delivery
Versioned SQL repository
The handover artefact: one folder per dashboard, one file per chart, so every definition can be read, diffed and rebuilt from.
What stopped being manual
Snippets are illustrative and generic. No client data appears in any of them.
Net revenue with refunds on their own window
Replaces netting refunds off by hand, inconsistently and only when someone remembered. The refund aggregate takes its own date range because a refund's event date is usually later than the order that caused it.
sql
-- Illustrative. Gross and net on one card; returning derived
-- as the difference so the split reconciles to the total.
SELECT
SUM(s.order_revenue) AS total_revenue,
SUM(s.new_customer_revenue) AS new_revenue,
SUM(s.order_revenue) - SUM(s.new_customer_revenue) AS returning_revenue,
r.total_refunds,
SUM(s.order_revenue) - r.total_refunds AS net_revenue
FROM blended_stats_tvf(start_date := @from, end_date := @to) AS s
CROSS JOIN (
-- own, wider window: late refunds still net off
SELECT
SUM(ABS(rf.total_refunded_price)) AS total_refunds,
SUM(CASE WHEN o.is_new_customer = 1
THEN ABS(rf.total_refunded_price) ELSE 0 END) AS new_refunds
FROM refunds_table AS rf
LEFT JOIN orders_table AS o ON rf.order_id = o.order_id
WHERE rf.event_date BETWEEN @refund_from AND @refund_to
) AS r
GROUP BY r.total_refunds;Month-to-date against the matching prior window
Replaces the comparison people make in their heads, usually against a complete prior month. Both sides stop at the same elapsed day, and the query states the direction of travel instead of leaving two columns to be eyeballed.
sql
-- Illustrative. One pass, conditional aggregation, matching
-- partial windows, never a part month against a whole one.
SELECT
state,
current_mtd_revenue,
previous_mtd_revenue,
CASE
WHEN current_mtd_revenue > previous_mtd_revenue THEN 'Growth'
WHEN current_mtd_revenue < previous_mtd_revenue THEN 'Decline'
ELSE 'Flat'
END AS trend
FROM (
SELECT
COALESCE(customer_from_state_code, 'Unknown') AS state,
SUM(CASE WHEN event_date BETWEEN month_start(today) AND today
THEN order_revenue ELSE 0 END) AS current_mtd_revenue,
SUM(CASE WHEN event_date BETWEEN month_start(today) - 1 MONTH
AND today - 1 MONTH
THEN order_revenue ELSE 0 END) AS previous_mtd_revenue
FROM orders_table
WHERE event_date BETWEEN month_start(today) - 1 MONTH AND today
GROUP BY state
) AS by_state
ORDER BY current_mtd_revenue DESC;What it was worth
What this engagement produced is a settled way to read revenue, which is a different deliverable from a pipeline and should not be described as one. The collection was already handled by the platform. The missing part was agreement: whether the headline figure is net of refunds, what a month-to-date number is compared against, how the new and returning split is derived, and which date a refund belongs to. Thirty-eight queries that all answer those the same way is the actual artefact.
The structural decision worth keeping is one file per chart in a versioned folder per dashboard. It costs some duplication: the month-to-date window logic is repeated across the dimensions it is cut by, and buys the ability to read any single chart's definition in isolation, review a change as a diff, and settle a disagreement between two charts by opening two files. Dashboards usually lose trust not because a number is wrong but because nobody can establish what it was supposed to mean.
Two definitional details carry more weight than their size suggests. Giving refunds their own date window means recent periods are not flattered by refunds that have not yet been counted against them. And comparing a partial month against the same elapsed portion of the previous month, rather than against a complete month, removes a comparison that is wrong by construction for most of every month.
An honest statement of scope. This is SQL and dashboard analysis inside Triple Whale, running against the brand's own connected data. No pipeline, warehouse, scheduling or infrastructure was built, nothing here runs on sample or synthetic data, and no revenue uplift or time saving was measured. The deliverable is the measurement surface and the definitions underneath it.
One analytical theme each
Overview, hourly, state, yearly, week-to-date and month-to-date comparisons, weekly and monthly trends, customer type, and channel.
One file per visualisation
Each chart's complete definition is independently readable and reviewable in a diff.
Revenue reported both ways
Refunds are aggregated and joined in, so the two figures appear together rather than being quoted interchangeably.
Comparison basis
Matching partial windows, the current month to date against the same elapsed days of the previous month.
The same pattern weekly
Repeated across channel, state, local area and customer type.
Time grains covered
Hour of day, daily, weekly, monthly and yearly, so a question can be asked at the resolution it deserves.
Split carried through
Reported overall and per ad platform, which is the level at which budget decisions are actually made.
Handed over
- Ten themed dashboards: overview, hourly, state-level, yearly performance, week-to-date comparisons, month-to-date comparisons, weekly trends, monthly trends, new versus returning customers, and channel performance.
- Thirty-eight SQL chart queries, one file per visualisation, in a numbered folder per dashboard.
- A scorecard query per dashboard carrying that theme's headline figures rather than a single global scorecard.
- Gross and net revenue on the same card, with returning-customer revenue derived as the difference so the split reconciles to the total.
- A refund aggregate on its own date window, joined back to orders so refunds are split by new and returning customers.
- Month-to-date and week-to-date comparison charts that measure matching partial windows and label growth, decline or flat in the query.
- Those comparisons repeated across state, local area within state, channel and customer type.
- Hourly dashboards covering revenue by hour, hourly range, and each state's hourly contribution share.
- Channel dashboards covering daily revenue by ad platform and each channel's contribution share, with the new-versus-returning split carried through per platform.
- Weekly and monthly trend dashboards written without explicit date anchors, so the charts read as behaviour rather than calendar.
Questions about this build
What teams usually ask when they recognise their own process here.
No, and it is worth being clear about that. Triple Whale already held the connected order, refund and ad-platform data, so there was no extraction, scheduling or warehouse work in scope. What was built is the layer above it: the metric definitions, thirty-eight chart queries and the ten-dashboard structure they sit in. If the underlying collection had not already been solved, this engagement would have looked completely different.
Because it makes each definition readable, reviewable and arguable. When someone asks why two charts disagree about revenue, the answer is two files side by side rather than an archaeology exercise through chart configuration. It also means the business can disagree with a definition and see exactly what it is disagreeing with. The cost is duplication: the month-to-date window logic is repeated for each dimension it is cut by, and that trade is made deliberately.
Because a refund's event date is usually later than the order that produced it, often by weeks. A refund subquery constrained to the revenue period would therefore exclude precisely the refunds that most affect a recent window, and the net figure would flatter the current period in a way that corrects itself only after the fact. Giving the refund aggregate its own, wider range means late refunds are netted off where they belong.
Because deriving it as total revenue minus new-customer revenue guarantees the two halves sum to the whole. Reading two independently computed fields invites a split that does not reconcile, and a new-plus-returning breakdown that does not add up to total revenue is the fastest way to lose a dashboard's credibility, regardless of which of the three numbers is actually wrong.
It is an editorial decision about what those charts are for. A trend dashboard exists to show the shape of behaviour, the rhythm of a week, the direction of a month, the hours when orders arrive. A visible calendar axis invites the reader to explain every movement with a specific event, which is the opposite of reading a trend. The dated, comparison-driven questions are answered by the performance dashboards instead, and keeping the two kinds visually distinct is the point.
The definitions transfer; the SQL would need rewriting. The queries use the platform's own dialect and its table-valued function for blended statistics, so a move to a general warehouse means re-expressing those against whatever the equivalent source is. What carries over unchanged is the part that took the work: the metric definitions, the matching-partial-window comparison pattern, the refund treatment, and the dashboard structure.
Capabilities this used
Read next
All product & gtm analytics workHave a process that works like this one did?
Book a strategy call. We will walk the workflow with you and say plainly which parts are worth automating and which are not.