Admin Panel

What It Does

The RCO Metrics Dashboard is an automated operational dashboard that replaces manual spreadsheet tracking. Instead of someone checking Shopify every day and typing in numbers, the dashboard pulls directly from both Shopify stores (Retail and Wholesale) and calculates everything automatically.

How the Data Works

• Data Sources: All data is pulled using READ-ONLY API access to both the Retail (rowe-casa) and Wholesale (wholesale-rowe-casa) Shopify stores. Nothing on this dashboard can modify orders, products, or any store data.
• Backend: A Cloudflare Worker fetches and processes Shopify data from both stores, caching results in Cloudflare KV for instant page loads.
• Frontend: Hosted on Firebase with Google authentication restricted to @rowecasaorganics.com accounts.
• Authentication: The Cloudflare Worker handles automatic reauthentication of Shopify API tokens for both stores, so the connection never expires or requires manual intervention.
• Refresh: Historical data (KPI, orders, international, top products) refreshes automatically every 6 hours via a cron job. Live dashboards (daily metrics, sales, unfulfilled, skip the line) pull fresh data from Shopify on each page load.
• Historical Data: Complete retail and wholesale data going back to January 2024 through present.

Dashboard Tabs

Weekly Overview

High-level summary of all weekly metrics across every dashboard. Shows this week's combined revenue, orders, AOV, units sold with percentage change vs last week. Includes retail/wholesale breakdown, daily breakdown table, fulfillment KPI rates, fulfillment status, skip the line snapshot, and top products for the week.

Fulfillment KPI

The main view. Shows daily order counts and fulfillment rates for 4-day and 7-day windows. The 4-day rate measures what percentage of orders ship within 4 business days (target: 85%). The 7-day rate is the same for 7 days (target: 95%). Toggle between Retail, Wholesale, and Total (combined). Switch between monthly table view and calendar heat map.

Shipping Leaderboard

Today's top shippers ranked by products shipped. Pulls from ShipStation via a Google Sheet that auto-refreshes throughout the day. Split into full-time, part-time, and wholesale categories.

Daily Metrics

Live snapshot of today's performance for both Retail and Wholesale side by side — total sales, order count, average order value, average revenue per unit, units sold, fulfillment progress, and top selling products.

Sales

Sales summary showing Today, Month-to-Date, and Year-to-Date revenue and orders for both Retail and Wholesale. Includes YTD combined totals (revenue, orders, AOV) at the bottom.

Top Products

Product rankings by units sold over the last 7 and 30 days, combining both Retail and Wholesale data.

Orders Overview

Monthly summary cards showing combined retail + wholesale totals: orders placed, fulfilled, unique customers, and top products. Toggle between Calendar card view and Chart trend view. Click any month for detailed stats.

Unfulfilled Orders

Current unfulfilled and partially fulfilled orders grouped by date, with average order size. Shows Retail and Wholesale separately.

International Orders

World map and US state map showing where orders come from across both stores. Filter by year and see country/state breakdowns with counts, percentages, and weighted averages.

Skip the Line

Tracks orders tagged with the "Skip the Line" priority fulfillment service — shows how many are open, unfulfilled, and partially fulfilled with a date breakdown.

Site Features

• Dark Mode: Toggle between light and dark themes. Preference is saved.
• TV Mode: Enters fullscreen and automatically cycles through all dashboards — ideal for wall-mounted displays.
• Guided Tour: Click the ? button in the sidebar to walk through each dashboard with explanations.
• Deep Linking: Each dashboard has a unique URL hash so you can share direct links.
• Role-Based Access: 5 roles (IT Admin, C-Suite, Director, Supervisor, Employee) each with configurable dashboard permissions and revenue visibility.

Access Control

The site uses role-based access. IT Admins have full access including the admin panel. C-Suite can see all operational dashboards with revenue. Directors and Supervisors can see dashboards but revenue is restricted. Employees can see the Shipping Leaderboard and International Orders only.

1. Weekly Overview

Updates

• Cached for 10 minutes per day (cache key includes today’s date)
• Browser auto-refreshes every 10 minutes

Data Source

• This week’s orders: Shopify count endpoints (open + closed) + 250-order sample for AOV/units/products
• Last week’s orders: Shopify count endpoints only; revenue and units estimated from this week’s AOV
• Fulfillment KPI rates: read from KV (kpi-retail/wholesale-{year}), filtered to this/last week dates
• Top Products, Unfulfilled, Skip the Line, Fulfillment Dashboard: read from existing KV caches

Calculations

• Week = Monday through Sunday (current week shows Mon through today)
• Weekly Revenue = sample AOV × total order count (exact if sample ≥ total)
• Units Sold = sample units-per-order × total order count
• % Change vs Last Week = (this week − last week) / last week × 100
• Daily breakdown scaled proportionally if 250-order sample < total orders
• KPI fill rates: weighted average across all days in the week (total fulfilled / total orders)

---

3. Fulfillment KPI

Updates

• Current month: automatic every 6 hours via cron (both Retail + Wholesale)
• Previous month: also refreshed during the first 10 days of each new month so end-of-month rates settle
• Browser refreshes from cache every 10 minutes

Data Source

• Shopify Admin REST API — paginates through ALL orders for the month
• Both Retail (rowe-casa) and Wholesale (wholesale-rowe-casa) stores
• Date filter: processed_at (matches Shopify Analytics)
• Fields fetched: id, processed_at, fulfillment_status, fulfillments, cancelled_at, financial_status

Calculations

• Total Orders = all orders processed that day (including cancelled/refunded)
• Remaining (4-Day) = non-cancelled, non-refunded orders that are currently unfulfilled (fulfillment_status ≠ “fulfilled”)
• Remaining (7-Day) = same as 4-Day (both check current fulfillment status at time of refresh)
• 4-Day Fill Rate = (Total − Remaining) / Total × 100
• 7-Day Fill Rate = same formula
• Cancelled/refunded orders are included in the total but skipped in the remaining count (effectively counted as fulfilled)
• “Total” dataset combines Retail + Wholesale raw counts by date, then recalculates rates
• Monthly average fill rate = mean of daily rates; Avg daily orders = total orders / days with data

Why “Current State”?

This matches Alisha’s KPI spreadsheet methodology. She checks Shopify Analytics on the 4th and 7th day after each order date and counts how many are still unfulfilled at that moment. Our cron does the same — it checks the current fulfillment status of each order at refresh time. For historical months where all orders are shipped, both rates will be 100%.

---

4. Fulfillment Dashboard

Updates

• Fulfilled counts: computed every 30 minutes via cron, stored in KV
• Unfulfilled counts: live from Shopify on each page load, cached 10 minutes

Data Source

• Unfulfilled: Shopify orders/count.json with status=open, fulfillment_status=unfulfilled + partial (4 API calls: 2 Retail + 2 Wholesale)
• Fulfilled counts: 30-min cron paginates through shipped orders (updated_at filter), checks each order’s fulfillment created_at date

Calculations

• Unfulfilled = unfulfilled count + partially fulfilled count (exact, live)
• Yesterday Fulfilled = unique orders with fulfillment_status=shipped, updated since yesterday, where any fulfillment was created yesterday
• Today Fulfilled = same logic for today
• Counts unique orders (not individual shipments)
• Yesterday’s count is cached after midnight (it’s static); only today’s count recomputes every 30 min

---

5. Shipping Leaderboards

Updates

• Pulls from a Google Sheet on page load; updates when the sheet is updated

Data Source

• Google Sheets published CSV export (manually maintained)

Calculations

• None — displays values directly from sheet columns (rank, name, products shipped, orders)

Note: Cannot be automated because the BambooHR API does not expose full-time vs part-time status, which is needed for the leaderboard categories.

---

6. Daily Metrics

Updates

• Live Shopify API calls, cached for 10 minutes
• Regardless of how many users have the site open, Shopify is only called once per 10 min

Data Source

• 8 Shopify API calls per refresh (4 Retail + 4 Wholesale)
• Order counts (open + closed) filtered by processed_at for today
• Shipped count filtered by processed_at
• Full paginated fetch of all orders with total_price, line_items, financial_status
• All dates in Central Time (America/Chicago)

Calculations

• Total Orders = open + closed count (exact, excludes cancelled)
• Fulfilled = shipped count (exact)
• Unfulfilled = Total − Fulfilled
• Sales = sum of total_price from all orders (gift card purchases excluded, cross-day refunds subtracted)
• Units Sold = sum of line_item quantities, excluding non-product items (shipping protection, discount cards, etc.)
• AOV = Sales / Total Orders
• Revenue Per Unit = Sales / Units Sold
• Top 10 Products = ranked by quantity from all orders

---

7. Sales

Updates

• Live Shopify API calls, cached for 10 minutes

Data Source

• 14 Shopify API calls per refresh (7 Retail + 7 Wholesale)
• Order counts for Today, MTD, YTD (each open + closed) via processed_at
• 50-order sample for today’s AOV

Calculations

• Today/MTD/YTD order counts = exact from Shopify (excludes cancelled)
• AOV = average total_price from today’s 50-order sample (voided/refunded excluded)
• Today Sales = AOV × Today Orders (estimate)
• MTD Sales = AOV × MTD Orders (estimate — uses today’s AOV for entire month)
• YTD Sales = AOV × YTD Orders (estimate — uses today’s AOV for entire year)
• Combined YTD = Retail + Wholesale totals; Combined AOV = Combined Revenue / Combined Orders

---

8. Top Products

Updates

• Refreshed nightly at 2 AM CT via cron

Data Source

• Shopify orders from last 7 and 30 days, both stores
• Paginates through all orders in each window

Calculations

• Counts total quantity per product title across all orders
• Non-product items excluded (shipping protection, discount cards, gift cards, etc.)
• Ranked highest to lowest by units sold, top 50 returned

---

9. Orders Overview

Updates

• Current month refreshed nightly at 2 AM CT via cron
• Historical months from backfill script (complete data, not sampled)

Data Source

• Shopify orders endpoint — fetches every order for each month from both stores
• Date filter: created_at

Calculations (per month, Retail + Wholesale combined)

• Orders Placed = total count of all orders (including cancelled)
• Orders Fulfilled = count with fulfillment_status=fulfilled, excluding cancelled/refunded
• Fulfillment Rate = Orders Fulfilled / Orders Placed × 100

---

10. Unfulfilled Orders

Updates

• Live Shopify API calls, cached for 10 minutes

Data Source

• 6 Shopify API calls per refresh (3 Retail + 3 Wholesale)
• Counts for unfulfilled + partially fulfilled (status=open)
• Full paginated fetch of unfulfilled orders with created_at and line_items

Calculations

• Total Unfulfilled = unfulfilled count + partial count (exact)
• Date Breakdown = orders grouped by created_at date
• Avg Order Size = total units / order count

---

11. International Orders

Updates

• Current year updated nightly at 2 AM CT via cron (incremental)
• Full historical data from backfill script

Data Source

• Shopify orders endpoint — extracts shipping_address.country_code and province_code
• Both stores, stored per year in Cloudflare KV

Calculations

• Country counts = number of orders shipped to each country
• US State counts = number of orders shipped to each US state
• “All” years merges counts across 2024, 2025, and 2026

---

12. Skip the Line

Updates

• Live Shopify API call, cached for 10 minutes

Data Source

• 1 Shopify API call — fetches open unfulfilled orders, filters for ‘dyn-skip-line’ tag

Calculations

• Total = count of open unfulfilled orders with skip-the-line tag
• Grouped by created_at date to show wait time

---

13. Sidebar — Today’s Orders

Updates

• Uses the /daily-metrics cached endpoint; refreshes every 10 minutes

Calculation

• Retail total orders + Wholesale total orders (same data source as Daily Metrics)

---

General Notes

• All API access is READ-ONLY. The dashboard cannot modify orders, products, or any store data.
• All dates use Central Time (America/Chicago) with automatic DST handling.
• Caching: Live endpoints cache responses for 10 minutes in Cloudflare KV. Multiple users loading the page simultaneously only trigger one Shopify API call.
• Order counts may differ from Shopify Analytics by 2–10 orders per day. This is a documented difference between the REST API’s processed_at filter and Shopify Analytics’ internal counting. Fulfillment rates are not affected.
• Non-product items excluded from unit counts: shipping protection, Navidium, discount cards, gift cards, skip the line, heat sensitive info cards.