Skip to content

Dashboard Design — E-commerce Analytics

Dashboard Purpose

A 3-page Tableau dashboard for Olist leadership: - Page 1 — Business Overview: revenue, orders, growth, top categories - Page 2 — Customer Intelligence: LTV, RFM segments, repeat rate, geography - Page 3 — Operations & Satisfaction: delivery times, review drivers, seller performance

Page 1 — Business Overview

┌──────────────────────────────────────────────────────────────────┐
│  Revenue      Orders      Unique Customers   AOV      Avg Review   │
│  R$15.4M      96,478      93,358             R$160    4.09 ★       │
├──────────────────────────────────────────────────────────────────┤
│  Monthly Revenue Trend (line)                                      │
├────────────────────────────────┬───────────────────────────────────┤
│  Revenue by Category (top 10)   │  Orders by State (map)            │
└────────────────────────────────┴───────────────────────────────────┘

Page 2 — Customer Intelligence

┌──────────────────────────────────────────────────────────────────┐
│  Repeat Rate: 3%   Avg LTV: R$165   Champions: 8,200              │
├──────────────────────────────────────────────────────────────────┤
│  RFM Segment Distribution (treemap)    LTV Distribution (histogram)│
├────────────────────────────────┬───────────────────────────────────┤
│  Top States by Revenue          │  Customer Acquisition by Month    │
└────────────────────────────────┴───────────────────────────────────┘

Page 3 — Operations & Satisfaction

┌──────────────────────────────────────────────────────────────────┐
│  Avg Delivery: 12.5 days    Late Rate: 8%    Bad Review Rate: 12% │
├──────────────────────────────────────────────────────────────────┤
│  Review Score by Delivery Speed (the headline bar chart)           │
├────────────────────────────────┬───────────────────────────────────┤
│  Worst-Rated Categories         │  Delivery Time by State           │
└────────────────────────────────┴───────────────────────────────────┘

Building in Tableau

Step 1 — Connect and Join

  1. Connect to the CSV files
  2. Build the data model with relationships (Tableau 2020.2+ relationships handle the multi-table joins elegantly):
  3. orders ↔ customers on customer_id
  4. orders ↔ order_items on order_id
  5. orders ↔ order_reviews on order_id
  6. order_items ↔ products on product_id

Step 2 — Key Calculated Fields

// Order revenue
Order Revenue = [Price] + [Freight Value]

// Delivery days
Delivery Days = DATEDIFF('day', [Order Purchase Timestamp], [Order Delivered Customer Date])

// Delivery speed bucket
Delivery Speed =
IF [Delivery Days] <= 7 THEN "Fast"
ELSEIF [Delivery Days] <= 14 THEN "Normal"
ELSEIF [Delivery Days] <= 30 THEN "Slow"
ELSE "Very Slow"
END

// Is late
Is Late = IF [Order Delivered Customer Date] > [Order Estimated Delivery Date] THEN 1 ELSE 0 END

// Customer LTV (use FIXED LOD on customer_unique_id)
Customer LTV = { FIXED [Customer Unique Id] : SUM([Order Revenue]) }

// Repeat customer flag
Order Count per Customer = { FIXED [Customer Unique Id] : COUNTD([Order Id]) }
Is Repeat = IF [Order Count per Customer] > 1 THEN "Repeat" ELSE "One-time" END

Step 3 — The Headline Chart (Review by Delivery Speed)

This is the most important visual. Build it carefully: - Columns: Delivery Speed (sorted Fast → Very Slow) - Rows: AVG(Review Score) - Color: gradient from green (high) to red (low) - Add reference line at 4.0 (the overall average)

Step 4 — RFM Treemap

  • Use the RFM calculated fields to assign each customer a segment
  • Treemap: size = customer count, color = segment
  • Tooltip: avg LTV per segment

Step 5 — Geographic Map

  • Tableau auto-recognises Brazilian states
  • Color: revenue or avg delivery time
  • This reveals delivery bottlenecks geographically

Design Notes

Use LOD expressions for customer-level metrics

Because the data is at order/item grain but LTV is customer-level, { FIXED [Customer Unique Id] : SUM([Order Revenue]) } computes each customer's total correctly regardless of the view's grain. This is the killer Tableau feature for relational e-commerce data.

Always use customer_unique_id for customer metrics

Same trap as the SQL analysis — customer_id is per-order. Every customer-level calculation must use customer_unique_id.

← SQL Analysis · Next: Insights and Recommendations →