Sales Reporting Dashboard

Sales Reporting Dashboard

Overview

A sales reporting dashboard surfaces the metrics that matter most to an ecommerce operation: revenue, orders, average order value (AOV), conversion rate, and trend comparisons. Having a single source of truth for these metrics — accessible to the whole team and automatically up to date — replaces manual spreadsheet reports and gives operators the data they need for daily decisions.

This skill guides you through building a sales reporting dashboard using your platform's built-in tools, BI apps, and data connections.

When to Use This Skill

• When the business needs a single source of truth for daily/weekly revenue reporting
• When building an internal analytics dashboard to replace manual spreadsheet reports
• When implementing time-comparison metrics (week-over-week, month-over-month, year-over-year)
• When product managers need category and channel drill-down beyond top-level revenue
• When building an executive dashboard that surfaces GMV, conversion rate, and AOV trends
• When integrating with a BI tool (Metabase, Looker Studio) via an API or direct database views

Core Instructions

Step 1: Choose your reporting tool by platform

Step 2: Set up your core sales reporting dashboard

---

Shopify

Built-in Shopify Analytics (start here):

1. Go to Analytics → Overview — the default dashboard shows:

• Today's total sales, orders, and sessions in real time
• Conversion rate for the current day vs. prior period
• AOV trend
• Top products by revenue

2. Go to Analytics → Dashboards — Shopify lets you create custom dashboards:

• Click + Add report to add tiles for any built-in report metric
• Recommended tiles: Total sales, Net sales, Orders, Conversion rate, AOV, Top products, Sales by channel, Sales by location

3. Go to Analytics → Reports for all available reports:

• Sales over time: Revenue by day/week/month with period comparison
• Sales by product: Top products by revenue and units sold
• Sales by channel: Revenue breakdown by sales channel (online store, POS, draft orders, etc.)
• Sales by traffic source: Revenue by UTM source/medium (last-click)
• Average order value over time: AOV trend with comparison
• Returning customer rate: New vs. returning customer ratio

4. All reports export to CSV for further analysis

Setting up a Shopify + Looker Studio dashboard (free, for custom visualization):

1. Go to Looker Studio at lookerstudio.google.com 2. Add a data source: select Google Sheets 3. In Google Sheets, set up a connection to Shopify using Sheets for Shopify app or by scheduling CSV exports from Shopify Analytics 4. Build your dashboard in Looker Studio: add scorecards for key metrics, time-series charts for revenue trend, bar charts for channel breakdown 5. Share the dashboard URL with your team — refreshes automatically when the Google Sheet updates

---

WooCommerce

WooCommerce Analytics (built-in):

1. Go to WooCommerce → Analytics → Overview — shows revenue, orders, items sold, and refunds for the selected date range with period comparison 2. Go to WooCommerce → Analytics → Revenue — detailed revenue breakdown: gross sales, returns, coupons, net revenue, taxes, shipping by day 3. Go to WooCommerce → Analytics → Orders — order count, average order value, refund rate by day 4. Go to WooCommerce → Analytics → Products — revenue and units sold by product 5. Go to WooCommerce → Analytics → Categories — revenue and units by product category 6. All WooCommerce Analytics reports export to CSV

Metorik (advanced WooCommerce dashboards):

1. Connect Metorik to your WooCommerce store 2. Go to Metorik → Dashboard — real-time revenue, orders, and customer metrics with period comparison 3. Go to Metorik → Reports → Revenue — revenue by day/week/month; compare any two custom date ranges 4. Go to Metorik → Reports → Products — revenue, units, refunds by product 5. Go to Metorik → Reports → Customers → Cohorts — monthly cohort retention matrix showing what % of each acquisition cohort is still buying 6. Set up Metorik Digest emails — automated daily or weekly summary emails sent to your team

---

BigCommerce

1. Go to Analytics → Store Overview — shows revenue, orders, conversion rate, and AOV for the selected period with trend chart 2. Go to Analytics → Purchase Funnel — shows session-to-order conversion funnel: sessions → product views → add to cart → purchase 3. Go to Analytics → Products → Analytics → Merchandising → Products — revenue and units by product 4. Go to Analytics → Customers — new vs. returning customer breakdown, customer lifetime value 5. For advanced dashboards: install Glew.io from the BigCommerce App Marketplace — pre-built executive sales dashboard with channel comparison, cohort retention, and automated weekly digest emails

---

Step 3: Build the key metrics your dashboard must answer

Regardless of tool, your sales dashboard should answer these questions at a glance:

Daily check (5-minute morning review):

• Revenue today vs. same day last week (and same day last year for seasonal businesses)
• Order count today vs. prior
• Conversion rate today vs. 7-day average (significant drops usually indicate a site issue)

Weekly review:

• Revenue this week vs. prior week vs. same week last year
• AOV trend (is it stable, growing, or declining?)
• Top 10 products by revenue and units sold this week
• Channel breakdown: website vs. Amazon vs. wholesale revenue share

Monthly executive summary:

• Total net revenue vs. budget
• Gross margin % (if COGS is tracked in platform)
• New customer revenue vs. returning customer revenue
• Cohort retention: what % of last month's new customers have placed a second order?

Step 4: Set up period-over-period comparison

All platform analytics tools support date range comparison. Here is how to configure it:

• Shopify: In any report, click the date range picker → select Compare to → choose Prior period, Prior year, or Custom
• WooCommerce Analytics: The date range selector includes a comparison toggle; select "Previous period" or "Previous year"
• Metorik: Every chart has a "Compare" button that adds a prior-period line to the chart
• Google Analytics 4: Date range picker includes a comparison checkbox; select "Preceding period" or "Same period last year"
• Looker Studio: Add date range control to the dashboard; use "Comparison date range" in the control to enable period comparison

Step 5: Add channel and category drill-down

Channel drill-down:

• Shopify: Analytics → Sales by traffic source (shows revenue by UTM source/medium)
• WooCommerce: Metorik → Reports → UTM (shows orders and revenue by utm_source, utm_medium)
• BigCommerce: Analytics → Marketing → Campaigns (shows revenue attributed to marketing campaigns)
• All platforms: GA4 → Monetization → Ecommerce purchases → filter by "Session source/medium"

Category drill-down:

• Shopify: Analytics → Sales by product type (shows revenue by product type/collection)
• WooCommerce: WooCommerce Analytics → Categories (built-in)
• BigCommerce: Analytics → Merchandising → Categories

Best Practices

• Cache or pre-aggregate for large date ranges — revenue queries over 90+ days on large stores can be slow; use pre-built aggregate reports in Shopify Analytics or Metorik rather than exporting raw order data
• Always filter cancelled orders — including cancelled orders inflates GMV and skews AOV; all platform analytics tools exclude cancelled orders by default; verify this in custom SQL or exports
• Separate GMV from net revenue — GMV (gross merchandise value) includes full selling price before discounts; net revenue is after discounts and refunds; report both explicitly and label clearly
• Provide period-over-period context for every KPI — a $50K revenue day is meaningless without knowing whether it is up or down vs. last week
• Use consistent time zones — store all timestamps in UTC and apply timezone conversion only in reporting; mixed timezone data creates apparent revenue discrepancies
• Build one authoritative source of truth — if the marketing team uses GA4 revenue and the finance team uses Shopify Analytics revenue, they will often show different numbers (attribution timing, tax inclusion differences); agree on one source per metric

Common Pitfalls

Related Skills

• @product-analytics
• @customer-analytics
• @attribution-modeling
• @financial-analytics-dashboard
• @ab-testing-ecommerce

Channel Performance and Category Revenue Breakdown

Problem/Feature Description

The marketing and merchandising teams at Vantage Shop have been working from separate spreadsheets and need a unified analytics module that answers two key questions: (1) Which acquisition channels are driving the most revenue, and how many new vs. returning customers does each channel bring? (2) Which product categories are driving the most revenue, and what share of total sales does each represent?

The database has an orders table, an order_attribution table (columns: order_id, source, medium), an order_items table, a products table, a product_categories join table, and a categories table. Monetary values in the orders and order_items tables are stored as integer cents. Some orders have no attribution record — those should not be dropped from the channel analysis but should be grouped under a sensible default.

Additionally, the CFO wants a high-level revenue waterfall that shows how gross revenue becomes net revenue, accounting for discounts, refunds, and shipping. The VP of Merchandising also wants a weekly "top products" report that ranks products by revenue and shows whether each product moved up or down in rank compared to the prior week.

Output Specification

Produce the following files:

1. channel-drilldown.sql — SQL query for revenue by acquisition channel including new vs. returning customer breakdown. 2. category-revenue.sql — SQL query for revenue and revenue share by product category. 3. revenue-waterfall.ts — TypeScript function getRevenueWaterfall(start: Date, end: Date) that returns the waterfall array. 4. top-products.sql — SQL query that returns the top 20 products by revenue for a period with their rank and rank change vs. the prior period (use named parameters :current_start, :current_end, :prior_start, :prior_end). 5. schema-notes.md — A brief document (max 250 words) covering any important schema design or query optimisation decisions.

Assume db.orders.sumField(field, options) is available for the waterfall function.

Conversion Funnel and Trend Report

Problem/Feature Description

The growth team at Meridian Retail wants to understand how effectively their website converts visitors into buyers, and whether that efficiency is improving or declining week over week. They have two tables: sessions (one row per website visit, with a boolean is_new_visitor column and a started_at timestamp) and orders (with a session_id foreign key, a status column, and a created_at timestamp). Sessions without a matching order represent non-converting visits.

The team wants a TypeScript module that can be called with any arbitrary date range and return a conversion funnel report for that period alongside an equivalent comparison to the immediately preceding period of the same length. The analyst using this wants to know: how many sessions occurred, how many resulted in purchases, what the conversion rate was, and how the new-visitor vs. returning-visitor breakdown looked. They also want to know immediately how all those numbers shifted compared to the prior period.

Output Specification

Produce the following files:

1. conversion-query.sql — A PostgreSQL SQL query that computes the conversion rate and visitor segmentation for a given date range, using named parameters :start_date and :end_date. 2. conversion-report.ts — A TypeScript function getConversionReport(currentStart: Date, currentEnd: Date) that:

• Fetches the conversion data for the current period
• Determines the prior period (same length, immediately preceding) and fetches that too
• Returns an object containing current metrics, prior metrics, and percentage-change values for each metric

3. report-notes.md — A short document (max 200 words) noting any important design decisions made in the query or TypeScript code.

Assume a database query function db.query(sql, params) is available.

Revenue Analytics API Endpoint

Problem/Feature Description

Fenwick Commerce, a mid-sized e-commerce company, has outgrown their weekly spreadsheet revenue reports. The BI team is building an internal React dashboard powered by Recharts and needs a backend API that can serve aggregated revenue data across flexible date ranges and time granularities. The dashboard will be queried dozens of times per page load by multiple analysts simultaneously, so the team is concerned about query performance on their PostgreSQL database, which contains several years of order history.

The engineering team has a working database with an orders table that stores monetary values as integer cents fields (subtotal_cents, discount_cents, refund_cents, shipping_cents). They also need the API to clearly distinguish between gross merchandise value and net revenue so the finance and marketing teams stop confusing the two numbers. Every metric displayed must include context about how it compares to the equivalent prior period so analysts can immediately spot trends.

Output Specification

Implement the following:

1. A TypeScript Express route handler for GET /api/analytics/revenue that accepts start, end, and granularity query parameters. 2. A PostgreSQL SQL query that the handler uses to fetch the revenue data. 3. A brief api-design.md document (max 300 words) explaining the performance strategy, how the prior period is calculated, and the granularity options supported.

The output files should be:

• revenue-handler.ts — the Express route handler
• revenue-query.sql — the SQL query used for fetching revenue data
• api-design.md — the design notes document

Assume a database query function db.query(sql, params) is available. The infrastructure stack includes Node.js, Express, PostgreSQL, and Redis.