Turning Data into Profits: The Step-by-Step Guide to Building a Sales Velocity Analysis from Scratch with SQL (Code Included)

The idea of turning data into profits is stupid

That’s what I used to think before I learned about “sales velocity” and how important it is to business owners. Especially someone in a sales director type of role.

Sales velocity is how fast a business can make money.

It can get really complicated, but that’s the gist. And in this guide, you’ll learn how to analyze sales data to create a sales velocity analysis from scratch in SQL.

The first time I had to create this type of analysis, I was drowning.

Between extracting necessary sales data from our legacy database, calculating sales velocity for each SKU, and segmenting the sales velocity data to identify top and bottom-performing products…I was losing myself.

Every day was:

• Frustratingly slow data retrieval.
• Struggling to manage thousands of SKUs.
• Hours spent trying to calculate and analyze sales velocities.

The load was unmanageable.

​It reached a point where I wondered whether this was worth it. Maybe I should just leave it to chance and pray for the best.

But then, an idea struck me.

A Sales Velocity Analysis System.

What if I could make this work easier, faster, and more effective? What if I could create a system that simplifies this complex process?

I set to work.

And I was amazed at the transformation.

No more sifting through endless SKUs. No more painful hours spent extracting data. No more guessing games with product performance.

I instituted a system to:

• Extract data in an organized, efficient manner.
• Automate sales velocity calculations for each SKU.
• Segment and analyze the data to effortlessly identify top and bottom-performing products.

It’s useful for inventory management and sales pipeline optimization. That kind of thing.

Suddenly, the challenge didn’t seem as daunting.

I now had a path toward better sales management, increased revenue, and better product performance. The sales director at the time was thrilled. And I felt amazing because I solved a major headache for him.

I want to spare you the headaches. The late nights. The feelings of helplessness with a pile of data.

Here’s what you’ll learn in this guide:

1. How to set up your database
2. Building your analysis from scratch
3. Solving real-world business problems

This is going to be for an online store called Applesoft, the world’s best provider of tech equipment.

Let’s dig in:

Step 1: Setting up your database

First up, let’s create the three tables needed for sales velocity analysis: products, locations, and sales.

The products table stores information about the various products Applesoft sells. It includes the product ID, product name, product category, launch date, and unit price of each product.

The locations table stores data about different sales locations, including the location ID, location name, and the region where the location is based.

The sales table captures sales information. It records the ID of the sold product, the location ID where the sale took place, the date of the sale, the quantity sold, and the total sales value.

Here’s the code (try it on your own online)

-- Creating Products Table
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(255),
product_category VARCHAR(255),
launch_date DATE,
unit_price NUMERIC(10,2)
);

-- Creating Locations Table
CREATE TABLE locations (
location_id SERIAL PRIMARY KEY,
location_name VARCHAR(255),
region VARCHAR(255)
);

-- Creating Sales Table
CREATE TABLE sales (
sales_id SERIAL PRIMARY KEY,
product_id INTEGER REFERENCES products(product_id),
location_id INTEGER REFERENCES locations(location_id),
sale_date DATE,
quantity_sold INTEGER,
total_sales NUMERIC(10,2)
);

Okay, now that we have our tables created. Let’s add some data using the SQL INSERT statement.

-- Inserting data into Products Table
INSERT INTO products (product_name, product_category,
launch_date, unit_price) VALUES
('MacFruit Pro', 'Laptop', '2020-11-13', 1800),
('MacFruit Air', 'Laptop', '2020-11-13', 1200),
('iFruit 13', 'Phone', '2022-09-14', 800),
('iFruit 13 Pro', 'Phone', '2022-09-14', 1000),
('FruitWatch Series 7', 'Watch', '2021-09-14', 400),
('FruitPods Pro', 'Earphones', '2021-05-21', 250),
('iFruit 12', 'Phone', '2020-10-23', 700),
('iFruit 12 Pro', 'Phone', '2020-10-23', 900),
('FruitWatch Series 6', 'Watch', '2020-09-18', 350),
('FruitPods', 'Earphones', '2019-10-30', 200);

-- Inserting data into Locations Table
INSERT INTO locations (location_name, region) VALUES
('Walt Disney Store', 'Orlando, USA'),
('Bolt Sprinter Sales', 'Kingston, Jamaica'),
('Eiffel Tech', 'Paris, France'),
('Queen Vic Electronics', 'London, England'),
('Nelson Medela Outlet', 'Johannesburg, South Africa'),
('Gandhi Peace Electronics', 'Delhi, India'),
('Pele Football Tech', 'Sao Paulo, Brazil'),
('Jordan Jumpman Tech', 'Chicago, USA'),
('Beethoven Symphony Store', 'Vienna, Austria'),
('Da Vinci Tech', 'Florence, Italy');

-- Inserting data into Sales Table
INSERT INTO sales (product_id, location_id,
sale_date, quantity_sold, total_sales) VALUES
(1, 1, '2021-01-15', 50, 90000),
(2, 2, '2021-02-20', 60, 72000),
(3, 3, '2022-10-05', 80, 64000),
(4, 4, '2022-10-06', 90, 90000),
(5, 5, '2021-10-07', 70, 28000),
(6, 6, '2021-06-10', 60, 15000),
(7, 1, '2021-01-10', 75, 52500),
(8, 2, '2021-02-12', 65, 58500),
(9, 3, '2020-10-10', 50, 17500),
(10, 4, '2019-11-05', 45, 9000);

Alrighty, now that our data is set up with products, sales, and location data, let’s build the sales velocity analysis.

Step 2: Quickly analyze the sales data

The first step in any analysis is to get up to speed as quickly as possible with the data.

I recommend creating a simple query so that you can get a “feel” for how everything’s organized.

Here’s the basic SQL query code (try it on your own here)

-- Analysis 1: Extract and Organize Detailed Sales Data
SELECT
s.sales_id,
p.product_name,
p.product_category,
p.unit_price,
l.location_name,
l.region,
s.sale_date,
s.quantity_sold,
s.total_sales
FROM
sales s
JOIN
products p ON s.product_id = p.product_id
JOIN
locations l ON s.location_id = l.location_id
ORDER BY
s.sale_date;

This is a basic detailed analysis of the data, which is always a good way to start.

Results

sales_id	product_name	product_category	unit_price	location_name	region	sale_date	quantity_sold	total_sales
10	FruitPods	Earphones	200.00	Queen Vic Electronics	London, England	2019-11-05	45	9000.00
9	FruitWatch Series 6	Watch	350.00	Eiffel Tech	Paris, France	2020-10-10	50	17500.00
7	iFruit 12	Phone	700.00	Walt Disney Store	Orlando, USA	2021-01-10	75	52500.00
1	MacFruit Pro	Laptop	1800.00	Walt Disney Store	Orlando, USA	2021-01-15	50	90000.00
8	iFruit 12 Pro	Phone	900.00	Bolt Sprinter Sales	Kingston, Jamaica	2021-02-12	65	58500.00

Step 3: Calculate Sales Velocity

Next, let’s build the actual sales velocity calculation.

This gives each of your products a sales velocity “score”, letting sales leadership know how quickly (compared to all products) each product is selling.

This is super useful for:

• inventory management (when to reorder low-stocked items)
• sales rep training (make sure reps know how to sell popular products)
• retail store organization (put faster selling products in key locations)

Here’s the code for that (try it on your own here)

-- Analysis 2: Calculate Sales Velocity
SELECT
p.product_name,
SUM(s.quantity_sold)/COUNT(DISTINCT s.sale_date) as sales_velocity
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.product_name
ORDER BY sales_velocity DESC;

This query calculates the sales velocity for each product (total quantity sold divided by distinct sales days). It uses SUM and COUNT to aggregate data, GROUP BY to segment data by product, and ORDER BY to arrange the products by sales velocity in descending order.

Results

product_name	sales_velocity
iFruit 13 Pro	90
iFruit 13	80
iFruit 12	75
FruitWatch Series 7	70
iFruit 12 Pro	65
MacFruit Air	60
FruitPods Pro	60
MacFruit Pro	50
FruitWatch Series 6	50
FruitPods	45

Step 4: Create Segmentation for your Sales Velocity Analysis

Now that you’ve got the basic sales velocity metric created, you can start to categorize each of the products into high, medium, or low-velocity segments.

• High-velocity >= 75
• Medium: 60-75
• Low: below 60

Here’s the code (try it on your own here)

-- Analysis 3: Segment Sales Velocity Data
SELECT p.product_name, 
SUM(s.quantity_sold)/COUNT(DISTINCT s.sale_date) as sales_velocity,
CASE
WHEN SUM(s.quantity_sold)/COUNT(DISTINCT s.sale_date) >= 75 THEN 'High'
WHEN SUM(s.quantity_sold)/COUNT(DISTINCT s.sale_date) < 75 
AND SUM(s.quantity_sold)/COUNT(DISTINCT s.sale_date) >= 60 THEN 'Medium'
ELSE 'Low'
END as sales_velocity_segment
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.product_name
ORDER BY sales_velocity DESC;

This query not only calculates the sales velocity but also segments it into ‘High’, ‘Medium’, and ‘Low’ categories using a CASE statement.

It helps identify top-performing (high velocity), average-performing (medium velocity), and underperforming (low velocity) products.

Results

product_name	sales_velocity	sales_velocity_segment
iFruit 13 Pro	90	High
iFruit 13	80	High
iFruit 12	75	High
FruitWatch Series 7	70	Medium
iFruit 12 Pro	65	Medium
MacFruit Air	60	Medium
FruitPods Pro	60	Medium
MacFruit Pro	50	Low
FruitWatch Series 6	50	Low
FruitPods	45	Low

Just imagine what a sales director could do with these insights at their fingertips!

Step 5: Bring it to the next level

Okay, so you’ve now analyzed sales data and created an entire sales velocity analysis from scratch using SQL.

Now comes the fun part: presenting your insights and helping sales leaders make decisions.​

This is where you actually solve real-world business problems (which I’m all about).

Here are some questions you can answer using this data. Can you create the queries? Give them a try on your own using this interactive SQL session.

Q1: Which product category has the highest sales velocity?

We could define sales velocity as the total quantity of a product sold per day.

By aggregating the quantity sold per product category and dividing it by the number of days since the product launch, we can calculate the sales velocity for each category.

Q2: How does sales velocity vary by location?

Different locations may have different market dynamics, affecting how quickly products sell.

Compare the sales velocity across different locations, to identify regional trends or discrepancies.

Q3: Has the sales velocity of a specific product increased or decreased over time?

Sales velocity isn’t always constant—it can fluctuate based on a variety of factors.

Analyze sales velocity over time to pinpoint periods of increase or decrease, which could inform decisions about marketing efforts, pricing strategies, and inventory management.

Okay, that’s it for this guide.

A quick recap of what you’ve accomplished:

1. Created tables for sales velocity analysis
2. Inserted records for products, sales, and locations
3. Quickly queried the tables to get a feel for the data
4. Created a sales velocity metric for Applesoft
5. Created a segmented sales velocity analysis from scratch

Well done!

This is a super useful skill for businesses all around the world. It’s one that companies pay experts to solve sales problems every single day.

​I believe you can be that expert. And I believe that this guide will help you on your journey.