Data solution

Sales & Customer Analysis with SQL and Excel

Turn a year of raw orders into a monthly report, product and margin analysis, customer cohorts and RFM segments: 10 steps, real data, every query included.

10 steps · 6 code snippets · Excel, PostgreSQL, Google Sheets

Step 1 · Free preview

Meet the data and load it

Kora Home & Living sells kitchenware, appliances, bedding, decor and bathroom items online across six Nigerian cities. You have four files: products (what we sell, at what price and cost), customers (when they signed up, where they live and how they found us), orders (who ordered, when, and whether it was completed, cancelled or refunded) and order_items (what was in each order). One order has one or more order lines.

Create the tables

01-create-tables.sql

CREATE TABLE products (
  product_id   text PRIMARY KEY,
  product_name text NOT NULL,
  category     text NOT NULL,
  unit_price   numeric(12,2) NOT NULL CHECK (unit_price >= 0),
  unit_cost    numeric(12,2) NOT NULL CHECK (unit_cost >= 0)
);

CREATE TABLE customers (
  customer_id         text PRIMARY KEY,
  signup_date         date NOT NULL,
  city                text,
  acquisition_channel text NOT NULL
);

CREATE TABLE orders (
  order_id    text PRIMARY KEY,
  customer_id text NOT NULL REFERENCES customers (customer_id),
  order_date  date NOT NULL,
  status      text NOT NULL CHECK (status IN ('completed', 'cancelled', 'refunded'))
);

-- No primary key yet: step 2 removes duplicate lines first.
CREATE TABLE order_items (
  order_id   text NOT NULL REFERENCES orders (order_id),
  product_id text NOT NULL REFERENCES products (product_id),
  quantity   integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0)
);

Import the CSV files

02-import.psql

\copy products    FROM 'products.csv'    WITH (FORMAT csv, HEADER true)
\copy customers   FROM 'customers.csv'   WITH (FORMAT csv, HEADER true)
\copy orders      FROM 'orders.csv'      WITH (FORMAT csv, HEADER true)
\copy order_items FROM 'order_items.csv' WITH (FORMAT csv, HEADER true)

03-check-counts.sql

SELECT 'products' AS table_name, COUNT(*) AS row_count FROM products
UNION ALL SELECT 'customers', COUNT(*) FROM customers
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items;

Step 2 · Free preview

Check and clean the data

Never analyse data you have not checked. Real exports contain duplicate rows, blank fields and stray spaces, and each one quietly changes your totals. Here we test for three common problems, fix them, then add a primary key so the duplicate can never come back.

04-quality-checks.sql

-- 1. The same product twice in one order (should never happen)
SELECT order_id, product_id, COUNT(*) AS copies
FROM order_items
GROUP BY order_id, product_id
HAVING COUNT(*) > 1;

-- 2. Blank cities or cities with extra spaces
SELECT customer_id, '[' || city || ']' AS city_as_stored
FROM customers
WHERE city IS NULL OR city = '' OR city <> TRIM(city);

-- 3. Orders with no lines at all
SELECT o.order_id, o.status
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.order_id
WHERE i.order_id IS NULL;

05-fix.sql

-- Keep one copy of each duplicated line
DELETE FROM order_items a
USING order_items b
WHERE a.ctid > b.ctid
  AND a.order_id = b.order_id
  AND a.product_id = b.product_id;

-- Trim spaces; turn empty text into a real NULL (unknown)
UPDATE customers
SET city = NULLIF(TRIM(city), '')
WHERE city IS DISTINCT FROM NULLIF(TRIM(city), '');

-- Make duplicates impossible from now on
ALTER TABLE order_items ADD PRIMARY KEY (order_id, product_id);

06-status-mix.sql

SELECT status,
       COUNT(*) AS orders,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_orders
FROM orders
GROUP BY status
ORDER BY orders DESC;

You should see 994 completed orders (91.7%), 58 cancelled (5.4%) and 32 refunded (3.0%). From now on we count only completed orders as sales; cancelled and refunded orders brought in no money.

Keep reading

8 more steps

Build a sales view and monthly revenue
Create one reusable view of completed sales, then total revenue, orders and customers by month.
Measure growth with window functions
Add month-over-month growth and a running total to the monthly figures.
Order value and top products
Calculate average and median order value by quarter and find the products that drive revenue.
Gross margin by category
See which categories actually make money, and what the November discount cost.
Do customers come back? Cohort retention
Group customers by the month of their first purchase and track how many buy again.
Segment customers with RFM
Score every customer on recency, frequency and spend, and turn the scores into action groups.
Compare marketing channels and cities
Find which acquisition channels bring the most valuable customers, and where revenue comes from.
Monthly report view and Excel dashboard
Save a one-row-per-month report in the database, export it and build a KPI dashboard in Excel.

Buy once, keep it

$0.07One payment · lifetime access · no subscription

Want everything? All-Access is $29 / month

CREATE TABLE products ( product_id text PRIMARY KEY, product_name text NOT NULL, category text NOT NULL, unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0), unit_cost numeric(12,2) NOT NULL CHECK (unit_cost >= 0) ); CREATE TABLE customers ( customer_id text PRIMARY KEY, signup_date date NOT NULL, city text, acquisition_channel text NOT NULL ); CREATE TABLE orders ( order_id text PRIMARY KEY, customer_id text NOT NULL REFERENCES customers (customer_id), order_date date NOT NULL, status text NOT NULL CHECK (status IN ('completed', 'cancelled', 'refunded')) ); -- No primary key yet: step 2 removes duplicate lines first. CREATE TABLE order_items ( order_id text NOT NULL REFERENCES orders (order_id), product_id text NOT NULL REFERENCES products (product_id), quantity integer NOT NULL CHECK (quantity > 0), unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0) );

\copy products FROM 'products.csv' WITH (FORMAT csv, HEADER true) \copy customers FROM 'customers.csv' WITH (FORMAT csv, HEADER true) \copy orders FROM 'orders.csv' WITH (FORMAT csv, HEADER true) \copy order_items FROM 'order_items.csv' WITH (FORMAT csv, HEADER true)

SELECT 'products' AS table_name, COUNT(*) AS row_count FROM products UNION ALL SELECT 'customers', COUNT(*) FROM customers UNION ALL SELECT 'orders', COUNT(*) FROM orders UNION ALL SELECT 'order_items', COUNT(*) FROM order_items;

-- 1. The same product twice in one order (should never happen) SELECT order_id, product_id, COUNT(*) AS copies FROM order_items GROUP BY order_id, product_id HAVING COUNT(*) > 1; -- 2. Blank cities or cities with extra spaces SELECT customer_id, '[' || city || ']' AS city_as_stored FROM customers WHERE city IS NULL OR city = '' OR city <> TRIM(city); -- 3. Orders with no lines at all SELECT o.order_id, o.status FROM orders o LEFT JOIN order_items i ON i.order_id = o.order_id WHERE i.order_id IS NULL;

-- Keep one copy of each duplicated line DELETE FROM order_items a USING order_items b WHERE a.ctid > b.ctid AND a.order_id = b.order_id AND a.product_id = b.product_id; -- Trim spaces; turn empty text into a real NULL (unknown) UPDATE customers SET city = NULLIF(TRIM(city), '') WHERE city IS DISTINCT FROM NULLIF(TRIM(city), ''); -- Make duplicates impossible from now on ALTER TABLE order_items ADD PRIMARY KEY (order_id, product_id);