When you’re managing multiple products across different sales channels or regions, raw data tables don’t usually cut it. You need to see everything at once. For example, which products are crushing it online but underperforming in retail, or which regions are driving growth while others stagnate. A product performance matrix gives you that bird’s-eye view, turning rows of transaction data into a grid that shows patterns instantly.
Why Matrix Views Can Help
The advantage of a matrix layout is cognitive efficiency. Instead of scanning through hundreds of rows trying to mentally compare Product A’s performance in the Northeast versus the Southwest, you see it all in one glance. Your brain is built to process grids. This is probably why spreadsheets are so popular.
SQL’s pivoting capabilities let you generate these views directly from your transactional data without having to export to Excel first.
Setting Up Sample Data
Let’s work with a scenario that’s common in e-commerce. We’ll track product sales across different channels.
Here’s a script that creates some sample data for our examples:
-- Create the sales table
CREATE TABLE ProductSales (
SaleID INT PRIMARY KEY,
SaleDate DATE NOT NULL,
ProductName VARCHAR(50),
SalesChannel VARCHAR(30),
Quantity INT,
Revenue DECIMAL(10, 2)
);
-- Populate with sample data
INSERT INTO ProductSales (SaleID, SaleDate, ProductName, SalesChannel, Quantity, Revenue)
VALUES
(1, '2025-01-15', 'Wireless Headphones', 'Online Store', 5, 749.95),
(2, '2025-01-18', 'Bluetooth Speaker', 'Retail Partner', 3, 269.97),
(3, '2025-01-22', 'USB-C Cable', 'Online Store', 12, 167.88),
(4, '2025-02-03', 'Wireless Headphones', 'Marketplace', 8, 1199.92),
(5, '2025-02-07', 'Phone Case', 'Online Store', 15, 374.85),
(6, '2025-02-14', 'Bluetooth Speaker', 'Online Store', 6, 539.94),
(7, '2025-02-19', 'USB-C Cable', 'Retail Partner', 20, 279.80),
(8, '2025-03-05', 'Wireless Headphones', 'Retail Partner', 4, 599.96),
(9, '2025-03-11', 'Phone Case', 'Marketplace', 25, 624.75),
(10, '2025-03-18', 'Bluetooth Speaker', 'Marketplace', 7, 629.93),
(11, '2025-03-25', 'USB-C Cable', 'Online Store', 18, 251.82),
(12, '2025-04-02', 'Wireless Headphones', 'Online Store', 6, 899.94),
(13, '2025-04-09', 'Phone Case', 'Retail Partner', 10, 249.90),
(14, '2025-04-16', 'Bluetooth Speaker', 'Retail Partner', 5, 449.95),
(15, '2025-04-23', 'USB-C Cable', 'Marketplace', 22, 307.78),
(16, '2025-05-06', 'Wireless Headphones', 'Marketplace', 9, 1349.91),
(17, '2025-05-13', 'Phone Case', 'Online Store', 18, 449.82),
(18, '2025-05-20', 'Bluetooth Speaker', 'Online Store', 8, 719.92),
(19, '2025-05-27', 'USB-C Cable', 'Retail Partner', 16, 223.84);I used SQL Server when putting this together, but most of the examples should work across various RDBMSs with little to no modification.
Basic Product by Channel Matrix
The most straightforward matrix shows products as rows and sales channels as columns, with revenue as the value. This immediately reveals which product-channel combinations are working.
SELECT
ProductName,
SUM(CASE WHEN SalesChannel = 'Online Store' THEN Revenue ELSE 0 END) AS [Online Store],
SUM(CASE WHEN SalesChannel = 'Marketplace' THEN Revenue ELSE 0 END) AS Marketplace,
SUM(CASE WHEN SalesChannel = 'Retail Partner' THEN Revenue ELSE 0 END) AS [Retail Partner],
SUM(Revenue) AS Total
FROM ProductSales
GROUP BY ProductName
ORDER BY SUM(Revenue) DESC;Result:
ProductName Online Store Marketplace Retail Partner Total
------------------- ------------ ----------- -------------- -------
Wireless Headphones 1649.89 2549.83 599.96 4799.68
Bluetooth Speaker 1259.86 629.93 719.92 2609.71
Phone Case 824.67 624.75 249.9 1699.32
USB-C Cable 419.7 307.78 503.64 1231.12
This gives you a clean view of where each product generates revenue. You might discover that your wireless headphones sell best through marketplaces, while USB cables move more through retail partners.
Quantity-Based Matrix
Revenue tells one story, but unit sales tell another. Higher-priced items might dominate revenue even with lower volumes. A quantity matrix can help you understand this dynamic:
SELECT
ProductName,
SUM(CASE WHEN SalesChannel = 'Online Store' THEN Quantity ELSE 0 END) AS [Online Store],
SUM(CASE WHEN SalesChannel = 'Marketplace' THEN Quantity ELSE 0 END) AS Marketplace,
SUM(CASE WHEN SalesChannel = 'Retail Partner' THEN Quantity ELSE 0 END) AS [Retail Partner],
SUM(Quantity) AS Total
FROM ProductSales
GROUP BY ProductName
ORDER BY SUM(Quantity) DESC;Result:
ProductName Online Store Marketplace Retail Partner Total
------------------- ------------ ----------- -------------- -----
USB-C Cable 30 22 36 88
Phone Case 33 25 10 68
Wireless Headphones 11 17 4 32
Bluetooth Speaker 14 7 8 29
Now you can see which products have actual volume momentum. That USB cable might not be your revenue leader, but if it’s flying off the shelves.
Average Order Value Matrix
Sometimes you want to understand the typical transaction size for each product-channel combination. This helps with pricing strategy and channel selection:
SELECT
ProductName,
AVG(CASE WHEN SalesChannel = 'Online Store' THEN Revenue ELSE NULL END) AS [Online Store],
AVG(CASE WHEN SalesChannel = 'Marketplace' THEN Revenue ELSE NULL END) AS Marketplace,
AVG(CASE WHEN SalesChannel = 'Retail Partner' THEN Revenue ELSE NULL END) AS [Retail Partner],
AVG(Revenue) AS OverallAvg
FROM ProductSales
GROUP BY ProductName
ORDER BY ProductName;Result:
ProductName Online Store Marketplace Retail Partner OverallAvg
------------------- ------------ ----------- -------------- ----------
Bluetooth Speaker 629.93 629.93 359.96 521.942
Phone Case 412.335 624.75 249.9 424.83
USB-C Cable 209.85 307.78 251.82 246.224
Wireless Headphones 824.945 1274.915 599.96 959.936
Notice we use NULL in the ELSE clause rather than 0 (we don’t want to artificially lower the averages by including channels where no sales occurred).
Transaction Count Matrix
Knowing how many separate transactions each product generates across channels helps with inventory planning and understanding purchase patterns:
SELECT
ProductName,
SUM(CASE WHEN SalesChannel = 'Online Store' THEN 1 ELSE 0 END) AS [Online Store],
SUM(CASE WHEN SalesChannel = 'Marketplace' THEN 1 ELSE 0 END) AS Marketplace,
SUM(CASE WHEN SalesChannel = 'Retail Partner' THEN 1 ELSE 0 END) AS [Retail Partner],
COUNT(*) AS Total
FROM ProductSales
GROUP BY ProductName
ORDER BY COUNT(*) DESC;Result:
ProductName Online Store Marketplace Retail Partner Total
------------------- ------------ ----------- -------------- -----
Bluetooth Speaker 2 1 2 5
USB-C Cable 2 1 2 5
Wireless Headphones 2 2 1 5
Phone Case 2 1 1 4
This shows frequency of purchases. A product with high transaction count but lower revenue might be a consistent performer that keeps customers coming back.
Regional Performance Matrix
Let’s add some regional data to explore another dimension. First, we’ll create a regional sales table:
CREATE TABLE RegionalSales (
SaleID INT PRIMARY KEY,
SaleDate DATE NOT NULL,
ProductName VARCHAR(50),
Region VARCHAR(30),
Revenue DECIMAL(10, 2)
);
INSERT INTO RegionalSales (SaleID, SaleDate, ProductName, Region, Revenue)
VALUES
(1, '2025-01-10', 'Wireless Headphones', 'Northeast', 899.94),
(2, '2025-01-15', 'Bluetooth Speaker', 'West', 629.93),
(3, '2025-01-22', 'USB-C Cable', 'Midwest', 195.86),
(4, '2025-02-05', 'Phone Case', 'South', 449.82),
(5, '2025-02-12', 'Wireless Headphones', 'West', 1049.93),
(6, '2025-02-20', 'Bluetooth Speaker', 'Northeast', 539.94),
(7, '2025-03-03', 'USB-C Cable', 'South', 223.84),
(8, '2025-03-14', 'Phone Case', 'Midwest', 374.85),
(9, '2025-03-21', 'Wireless Headphones', 'South', 749.95),
(10, '2025-04-08', 'Bluetooth Speaker', 'Midwest', 449.95),
(11, '2025-04-17', 'USB-C Cable', 'West', 279.80),
(12, '2025-04-25', 'Phone Case', 'Northeast', 499.80),
(13, '2025-05-05', 'Wireless Headphones', 'Midwest', 599.96),
(14, '2025-05-18', 'Bluetooth Speaker', 'South', 719.92),
(15, '2025-05-26', 'USB-C Cable', 'Northeast', 167.88);Now we can create a product-by-region matrix:
SELECT
ProductName,
SUM(CASE WHEN Region = 'Northeast' THEN Revenue ELSE 0 END) AS Northeast,
SUM(CASE WHEN Region = 'South' THEN Revenue ELSE 0 END) AS South,
SUM(CASE WHEN Region = 'Midwest' THEN Revenue ELSE 0 END) AS Midwest,
SUM(CASE WHEN Region = 'West' THEN Revenue ELSE 0 END) AS West,
SUM(Revenue) AS Total
FROM RegionalSales
GROUP BY ProductName
ORDER BY SUM(Revenue) DESC;Result:
ProductName Northeast South Midwest West Total
------------------- --------- ------ ------- ------- -------
Wireless Headphones 899.94 749.95 599.96 1049.93 3299.78
Bluetooth Speaker 539.94 719.92 449.95 629.93 2339.74
Phone Case 499.8 449.82 374.85 0 1324.47
USB-C Cable 167.88 223.84 195.86 279.8 867.38
This reveals geographic patterns. Maybe wireless headphones dominate in the West, while Bluetooth speakers perform better in the South. These insights drive regional marketing decisions and inventory allocation.
Performance Percentage Matrix
Raw numbers can be useful, but percentages often show relative performance more clearly. Here’s a matrix showing what percentage of each product’s total revenue comes from each channel:
WITH ProductTotals AS (
SELECT
ProductName,
SUM(Revenue) AS TotalRevenue
FROM ProductSales
GROUP BY ProductName
)
SELECT
ps.ProductName,
CAST(SUM(CASE WHEN SalesChannel = 'Online Store' THEN Revenue ELSE 0 END) * 100.0 / pt.TotalRevenue AS DECIMAL(5,1)) AS [Online Store %],
CAST(SUM(CASE WHEN SalesChannel = 'Marketplace' THEN Revenue ELSE 0 END) * 100.0 / pt.TotalRevenue AS DECIMAL(5,1)) AS [Marketplace %],
CAST(SUM(CASE WHEN SalesChannel = 'Retail Partner' THEN Revenue ELSE 0 END) * 100.0 / pt.TotalRevenue AS DECIMAL(5,1)) AS [Retail Partner %]
FROM ProductSales ps
JOIN ProductTotals pt ON ps.ProductName = pt.ProductName
GROUP BY ps.ProductName, pt.TotalRevenue
ORDER BY ps.ProductName;Result:
ProductName Online Store % Marketplace % Retail Partner %
------------------- -------------- ------------- ----------------
Bluetooth Speaker 48.3 24.1 27.6
Phone Case 48.5 36.8 14.7
USB-C Cable 34.1 25 40.9
Wireless Headphones 34.4 53.1 12.5
This normalizes the data, making it easier to compare distribution patterns across products regardless of their absolute revenue levels.
Time-Based Comparison Matrix
Sometimes you want to see how the same matrix looked in different time periods. Let’s compare Q1 versus Q2 performance:
SELECT
ProductName,
SUM(CASE WHEN SalesChannel = 'Online Store' AND MONTH(SaleDate) BETWEEN 1 AND 3 THEN Revenue ELSE 0 END) AS [Online Q1],
SUM(CASE WHEN SalesChannel = 'Online Store' AND MONTH(SaleDate) BETWEEN 4 AND 6 THEN Revenue ELSE 0 END) AS [Online Q2],
SUM(CASE WHEN SalesChannel = 'Marketplace' AND MONTH(SaleDate) BETWEEN 1 AND 3 THEN Revenue ELSE 0 END) AS [Marketplace Q1],
SUM(CASE WHEN SalesChannel = 'Marketplace' AND MONTH(SaleDate) BETWEEN 4 AND 6 THEN Revenue ELSE 0 END) AS [Marketplace Q2],
SUM(CASE WHEN SalesChannel = 'Retail Partner' AND MONTH(SaleDate) BETWEEN 1 AND 3 THEN Revenue ELSE 0 END) AS [Retail Q1],
SUM(CASE WHEN SalesChannel = 'Retail Partner' AND MONTH(SaleDate) BETWEEN 4 AND 6 THEN Revenue ELSE 0 END) AS [Retail Q2]
FROM ProductSales
GROUP BY ProductName
ORDER BY ProductName;Result:
ProductName Online Q1 Online Q2 Marketplace Q1 Marketplace Q2 Retail Q1 Retail Q2
------------------- --------- --------- -------------- -------------- --------- ---------
Bluetooth Speaker 539.94 719.92 629.93 0 269.97 449.95
Phone Case 374.85 449.82 624.75 0 0 249.9
USB-C Cable 419.7 0 0 307.78 279.8 223.84
Wireless Headphones 749.95 899.94 1199.92 1349.91 599.96 0
This starts to get dense, but it can be helpful in revealing trends. Are marketplace sales growing? Is retail declining? These patterns inform strategic decisions.
Dynamic Product Matrix
You can make product matrices dynamic so they adapt to whatever products and channels exist in your data:
DECLARE @SQL NVARCHAR(MAX);
DECLARE @Columns NVARCHAR(MAX);
-- Build column list dynamically
SELECT @Columns = STRING_AGG(
'SUM(CASE WHEN SalesChannel = ''' + SalesChannel +
''' THEN Revenue ELSE 0 END) AS [' + SalesChannel + ']',
', '
)
FROM (
SELECT DISTINCT SalesChannel
FROM ProductSales
) AS Channels;
-- Build and execute the query
SET @SQL = '
SELECT
ProductName,
' + @Columns + ',
SUM(Revenue) AS Total
FROM ProductSales
GROUP BY ProductName
ORDER BY SUM(Revenue) DESC;
';
EXEC sp_executesql @SQL;Result:
ProductName Marketplace Online Store Retail Partner Total
------------------- ----------- ------------ -------------- -------
Wireless Headphones 2549.83 1649.89 599.96 4799.68
Bluetooth Speaker 629.93 1259.86 719.92 2609.71
Phone Case 624.75 824.67 249.9 1699.32
USB-C Cable 307.78 419.7 503.64 1231.12
This automatically adjusts if you add new sales channels or if the data you’re querying has different channel options. It’s particularly useful for reports that need to work across different product lines or time periods without modification.
Combining Metrics in One Matrix
Sometimes you want multiple metrics visible simultaneously. Here’s a matrix that shows both revenue and quantity for each combination:
SELECT
ProductName,
SUM(CASE WHEN SalesChannel = 'Online Store' THEN Revenue ELSE 0 END) AS [Online Rev],
SUM(CASE WHEN SalesChannel = 'Online Store' THEN Quantity ELSE 0 END) AS [Online Qty],
SUM(CASE WHEN SalesChannel = 'Marketplace' THEN Revenue ELSE 0 END) AS [Mkt Rev],
SUM(CASE WHEN SalesChannel = 'Marketplace' THEN Quantity ELSE 0 END) AS [Mkt Qty],
SUM(CASE WHEN SalesChannel = 'Retail Partner' THEN Revenue ELSE 0 END) AS [Retail Rev],
SUM(CASE WHEN SalesChannel = 'Retail Partner' THEN Quantity ELSE 0 END) AS [Retail Qty]
FROM ProductSales
GROUP BY ProductName
ORDER BY ProductName;Result:
ProductName Online Rev Online Qty Mkt Rev Mkt Qty Retail Rev Retail Qty
------------------- ---------- ---------- ------- ------- ---------- ----------
Bluetooth Speaker 1259.86 14 629.93 7 719.92 8
Phone Case 824.67 33 624.75 25 249.9 10
USB-C Cable 419.7 30 307.78 22 503.64 36
Wireless Headphones 1649.89 11 2549.83 17 599.96 4
This can get wide very quickly, but for key metrics you need to monitor together, it can be worth the screen space.