1. Total Revenue

Problem

Question

Sofia (the last VP of Sales):

Hi Yen, thank you for your last help. Actually we are flying blind right now. I need a dashboard that tells me how much money we have processed in history and how many orders that represents. Can you do me that favor? Keep it simple, I need it for my slide deck tomorrow.

Your goal is to answer two questions:

What is our Total Revenue?
How many orders have we processed?

Deliverable:

This time, save it as Metabase's questions:

Metabase questions that answer the two questions above.
- Name: KPI - Total Revenue and KPI - Total Orders
- Description

Logic / Approach:

Processed orders include orders with the order_status of "shipped" or "delivered", and "canceled" orders that have a non-null order_delivered_carrier_date

SELECT
  COUNT(DISTINCT order_id) AS total_processed_orders
FROM orders
WHERE order_status IN ('shipped', 'delivered')
  OR (order_status = 'canceled'
  AND order_delivered_carrier_date IS NOT NULL);

Total revenue is calculated as the actual amount received from customers, based on payment_value

SELECT
  SUM(op.payment_value) AS total_revenue
FROM orders o
JOIN order_payments op ON o.order_id = op.order_id
WHERE o.order_status = 'delivered';

Problem​

Problem