May 9, 2018 at 4:40 pm
I need some suggestions on the best method to assign points based on how a store performed on different metrics. I have 7 metrics that are used to determine a store's performance. For each of the metrics have a total amount of possible points, and each of the total possible points for each metric will then be added together to assign a ranking from 0 - 100 for each store. I also, need the method flexible enough to handle the addition or reduction of metrics, and also different Store Regions (For example different regions (South, North, East, etc. may have the same metric categories with value ranges that differ or metric categories that differ).
Here are the Metrics and Values for each of the metric categories.
| Metric | High (100%) | Mid-High (67%) | Mid-Low (33%) | Low (0%) | Total Possible Points |
| Customer Returns | 1.5 | 3.33 | 6.67 | 10 | 15 |
| Customer Complaints | 1.7 | 1.8 | 1.99 | 2 | 15 |
| Customer Reward Ratio | 90% | 85.00% | 80.00% | 79.00% | 10 |
| Corporate Calls | 0% | 0.50% | 1.00% | 1.01% | 5 |
| Customer Satisfaction | 90% | 85.00% | 80.00% | 79.00% | 35 |
| Stop Loss | 3% | 5.00% | 8.99% | 9.00% | 10 |
| Store Sales | $ 8,000.00 | $ 5,000.00 | $ 3,000.00 | $ 1,000.00 | 10 |
Each of the total possible points for each metric will then be added together to assign a ranking from 0 - 100 for each store.
So for example...A store has Customer Returns that is
>= 10 (Low) they get 0% of the possible points
< 10 (Low) and >= 6.67 (Mid-Low) they get 33% of the possible points
< 6.67 (Mid-Low) to >= 3.33 (Mid-High) they get 67% of the possible points.
< 3.33 (Mid-High) they get 100% of points.
Here is the temp table that contains each store and the values for each of the 7 metrics.
if object_id ('tempdb..#stores') is not null
drop table #stores;
create table #stores (
Store_ID varchar(7)
, Customer_Returns numeric(13,4)
, Customer_Complaints numeric(13,4)
, Customer_Reward_Ratio numeric(13,4)
, Corporate_Calls numeric(13,4)
, Customer_Satisfaction numeric(13,4)
, Stop_Loss numeric(13,4)
, Store_Sales numeric(13,2)
);
insert into #stores (Store_ID, Customer_Returns, Customer_Complaints, Customer_Reward_Ratio, Corporate_Calls, Customer_Satisfaction, Stop_Loss, Store_Sales) values
('A120','1.2','0','0.9','0','0.52','0.006','200')
,('A121','2','1.5','0.7','0.2','0.84','0.05','3000')
,('A122','1.87','1.5','0.8','0.05','0.82','0.007','7051')
,('A123','3','1.8','1','0.002','0.99','0.03','8901')
,('A124','5','3','0.98','0.01','0.7','0.001','5055')
,('A125','6','0.05','0.81','0','0.84','0.1','5485')
,('A126','2.1','0.07','0.8','0.005','0.91','0','1507')
,('A127','15','7','0.87','0.012','0.89','0','8573')
,('A128','6.2','0','0.52','0','0.8','0.09','5103')
,('A129','4.85','1.52','0.84','0.017','0.7','0.04','1503')
,('A130','3.87','1.34','0.82','0','0.65','0.03','9702')
,('A131','5.7','1.5','0.99','0.02','0.94','0','1508')
,('A132','8.99','2.1','0.7','0','0.9','0','5085')
,('A133','1.12','0','0.84','0.01','0.7','0','6503')
,('A134','2','2.12','0.91','0.012','0.8','0','9215')
,('A135','2.89','0','0.89','0','1','0','10500')
,('A136','2.54','1.47','0.8','0.02','0.98','0.001','15087')
,('A137','2.2','0','0.7','0','0.81','0','5656')
,('A138','2.3','1.54','0.65','0','0.8','0.009','2105')
,('A139','1.67','0','0.94','0','0.87','0','8434');
May 10, 2018 at 1:09 pm
After reading some other forum posts I am thinking that I might be able to write the metric ranges (High, Mid-High, Mid-Low, and Low) to variables. Would that be the most efficient way to allow me to update the values, metrics, and be able to use for other regions?
May 10, 2018 at 1:16 pm
Put the metrics in a table. Then we can join to each metric as needed.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 10, 2018 at 2:59 pm
There is not enough data. In table #stores you provided absolute values for metrics - column names are metrics names, I assume. We don't know which value corresponds to which category (Low, Mid-Low,Mid-High,High). We know that each (Low, Mid-Low,Mid-High,High) carries certain weight (0, 33%, 67% and 100%).
If my understanding of the problem is correct, wWe perhaps need something like this:
table Metrics:
Metric, FromValue, ToValue, Category
---------------------------------------------------
'Customer returns', 0, 15, 'Low'
'Customer returns', 16, 33, 'Low Mid'
'Customer returns', 34, 67, 'High Mid''
'Customer returns', 68, 100, 'High'
'Customer_Complaints', 0, 15, 'Low'
'Customer_Complaints', 16, 33, 'Low Mid'
'Customer_Complaints', 34, 67, 'High Mid''
'Customer_Complaints', 68, 100, 'High'
... and so on for each category
Then you arrange your input data like this:
Table StoreMetrics:
Store_ID Metric Value
-------- --------------------- ----------------
A120, Corporate_Calls ,0.0000
A120, Customer_Complaints ,0.0000
A120, Customer_Returns ,1.2000
A120, Customer_Reward_Ratio ,0.9000
A120, Customer_Satisfaction ,0.5200
A120, Stop_Loss ,0.0060
A120, Store_Sales ,200.0000
A121, Corporate_Calls ,0.2000
A121, Customer_Complaints ,1.5000
A121, Customer_Returns ,2.0000
A121, Customer_Reward_Ratio ,0.7000
A121, Customer_Satisfaction ,0.8400
A121, Stop_Loss ,0.0500
A121, Store_Sales ,3000.0000
A122, Corporate_Calls ,0.0500
A122, Customer_Complaints ,1.5000
A122, Customer_Returns ,1.8700
A122, Customer_Reward_Ratio ,0.8000
......
You may need different From-To values for different metrics, since values in table StoreMetrics are different order of magnitude (say, Store_Sales, Customer_Returns, and Stop_Loss for example.
Then it is easy to write query like (typing into reply window, may be types)
:
SELECT S.StoreID, S.metric
M.Category, M.FromValue, S.Value, M.ToValue
FROM StoreMetrics AS S
JOIN Metrics AS M ON M.Metric = S.Metric
WHERE S>Value BETWEEN M.FromValue and M.ToValue
I hope this helps.
🙂
May 14, 2018 at 11:02 am
I agree with the idea that the existence of the "Low" range and it having a numeric value associated with it has "difficulties" where meaning is concerned. The question is, what about values either higher or lower (depending on the "direction" of that metrics range values for highs versus lows ? That's easily enough settled, and the solution I am providing below is a bit more complex than what was presented previously. I could probably have simplified it somewhat, but I don't have enough time to be sure I have it as minimal as possible. It takes into account the direction of each metric range, and also adds some ordering to the range values to make it easier to navigate the ranges and make use of simple aggregates. Not sure that it would perform well with large data volumes, but metrics such as this would have inherent traits that would likely keep the data volume from being all that large:CREATE TABLE #Metrics (
MetricID smallint NOT NULL,
MetricName varchar(30) NOT NULL,
MetricOrderBy tinyint NOT NULL,
MetricDirection bit NOT NULL,
MetricValue decimal(10,4) NOT NULL,
MetricPoints decimal(10,8) NOT NULL,
CONSTRAINT PK_TEMP_Metrics_Name_OrderBy_ PRIMARY KEY CLUSTERED
(
MetricName ASC,
MetricOrderBy ASC
)
);
INSERT INTO #Metrics (MetricID, MetricName, MetricOrderBy, MetricDirection, MetricValue, MetricPoints)
VALUES (1, 'Customer_Returns', 1, 0, 10.0000, 0.),
(1, 'Customer_Returns', 2, 0, 6.6667, 5.),
(1, 'Customer_Returns', 3, 0, 3.3333, 5.),
(1, 'Customer_Returns', 4, 0, 1.5000, 5.),
(2, 'Customer_Complaints', 1, 0, 2.0000, 0.),
(2, 'Customer_Complaints', 2, 0, 1.9900, 5.),
(2, 'Customer_Complaints', 3, 0, 1.8000, 5.),
(2, 'Customer_Complaints', 4, 0, 1.7000, 5.),
(3, 'Customer_Reward_Ratio', 4, 1, 0.7900, 0.),
(3, 'Customer_Reward_Ratio', 3, 1, 0.8000, 3.33333333),
(3, 'Customer_Reward_Ratio', 2, 1, 0.8500, 3.33333333),
(3, 'Customer_Reward_Ratio', 1, 1, 0.9000, 3.33333334),
(4, 'Corporate_Calls', 1, 0, 0.0101, 0.),
(4, 'Corporate_Calls', 2, 0, 0.0100, 1.66666666),
(4, 'Corporate_Calls', 3, 0, 0.0050, 1.66666667),
(4, 'Corporate_Calls', 4, 0, 0.0000, 1.66666667),
(5, 'Customer_Satisfaction', 4, 1, 0.7900, 0.),
(5, 'Customer_Satisfaction', 3, 1, 0.8000, 11.66666666),
(5, 'Customer_Satisfaction', 2, 1, 0.8500, 11.66666667),
(5, 'Customer_Satisfaction', 1, 1, 0.9000, 11.66666667),
(6, 'Stop_Loss', 1, 0, 0.0900, 0.),
(6, 'Stop_Loss', 2, 0, 0.0899, 3.33333333),
(6, 'Stop_Loss', 3, 0, 0.0500, 3.33333333),
(6, 'Stop_Loss', 4, 0, 0.0300, 3.33333334),
(7, 'Store_Sales', 4, 1, 1000.00, 0.),
(7, 'Store_Sales', 3, 1, 3000.00, 3.33333333),
(7, 'Store_Sales', 2, 1, 5000.00, 3.33333333),
(7, 'Store_Sales', 1, 1, 8000.00, 3.33333334);
CREATE TABLE #Stores (
Store_ID varchar(7) NOT NULL PRIMARY KEY CLUSTERED,
Customer_Returns numeric(13,4),
Customer_Complaints numeric(13,4),
Customer_Reward_Ratio numeric(13,4),
Corporate_Calls numeric(13,4),
Customer_Satisfaction numeric(13,4),
Stop_Loss numeric(13,4),
Store_Sales numeric(13,4)
);
INSERT INTO #Stores (Store_ID, Customer_Returns, Customer_Complaints, Customer_Reward_Ratio, Corporate_Calls, Customer_Satisfaction, Stop_Loss, Store_Sales)
VALUES ('A120','1.2','0','0.9','0','0.52','0.006','200.'),
('A121','2','1.5','0.7','0.2','0.84','0.05','3000.'),
('A122','1.87','1.5','0.8','0.05','0.82','0.007','7051.'),
('A123','3','1.8','1','0.002','0.99','0.03','8901.'),
('A124','5','3','0.98','0.01','0.7','0.001','5055.'),
('A125','6','0.05','0.81','0','0.84','0.1','5485.'),
('A126','2.1','0.07','0.8','0.005','0.91','0','1507.'),
('A127','15','7','0.87','0.012','0.89','0','8573.'),
('A128','6.2','0','0.52','0','0.8','0.09','5103.'),
('A129','4.85','1.52','0.84','0.017','0.7','0.04','1503.'),
('A130','3.87','1.34','0.82','0','0.65','0.03','9702.'),
('A131','5.7','1.5','0.99','0.02','0.94','0','1508.'),
('A132','8.99','2.1','0.7','0','0.9','0','5085.'),
('A133','1.12','0','0.84','0.01','0.7','0','6503.'),
('A134','2','2.12','0.91','0.012','0.8','0','9215.'),
('A135','2.89','0','0.89','0','1','0','10500.'),
('A136','2.54','1.47','0.8','0.02','0.98','0.001','15087.'),
('A137','2.2','0','0.7','0','0.81','0','5656.'),
('A138','2.3','1.54','0.65','0','0.8','0.009','2105.'),
('A139','1.67','0','0.94','0','0.87','0','8434.');
WITH MetricRanges AS (
SELECT 0 AS MetricDirection, 1 AS MetricRange, 'Low' AS RangeName
UNION ALL
SELECT 0 AS MetricDirection, 2 AS MetricRange, 'Mid-Low' AS RangeName
UNION ALL
SELECT 0 AS MetricDirection, 3 AS MetricRange, 'Mid-High' AS RangeName
UNION ALL
SELECT 0 AS MetricDirection, 4 AS MetricRange, 'High' AS RangeName
UNION ALL
SELECT 1 AS MetricDirection, 1 AS MetricRange, 'High' AS RangeName
UNION ALL
SELECT 1 AS MetricDirection, 2 AS MetricRange, 'Mid-High' AS RangeName
UNION ALL
SELECT 1 AS MetricDirection, 3 AS MetricRange, 'Mid-Low' AS RangeName
UNION ALL
SELECT 1 AS MetricDirection, 4 AS MetricRange, 'Low' AS RangeName
),
StoreValues AS (
SELECT X.Store_ID, MD.MetricID, X.Metric, X.MetricValue, MD.MetricDirection
FROM (
SELECT Store_ID, Metric, MetricValue
FROM #Stores
UNPIVOT(MetricValue FOR
Metric IN ([Customer_Returns], [Customer_Complaints], [Customer_Reward_Ratio],
[Corporate_Calls], [Customer_Satisfaction], [Stop_Loss], [Store_Sales])
) AS UPVT
) AS X
CROSS APPLY (
SELECT DISTINCT M.MetricID, M.MetricDirection
FROM #Metrics AS M
WHERE M.MetricName = X.Metric
) AS MD
)
SELECT
S.Store_ID,
S.MetricID,
S.Metric,
S.MetricValue,
GT.RangeName AS MetricRange,
ISNULL(GT.PointsGT, 0) AS TotalPoints
FROM StoreValues AS S
CROSS APPLY (
SELECT X.PointsGT, X.MetricRangeValue, MR.RangeName
FROM (
SELECT ISNULL(SUM(M.MetricPoints), 0) AS PointsGT,
CASE S.MetricDirection
WHEN 0 THEN ISNULL(MAX(M.MetricOrderBy), 1)
WHEN 1 THEN ISNULL(MIN(M.MetricOrderBy), 4)
END AS MetricRangeValue
FROM #Metrics AS M
WHERE M.MetricName = S.Metric
AND M.MetricDirection = S.MetricDirection
AND (
(
M.MetricDirection = 0
AND
M.MetricValue >= S.MetricValue
)
OR
(
M.MetricDirection = 1
AND
M.MetricValue <= S.MetricValue
)
)
) AS X
INNER JOIN MetricRanges AS MR
ON X.MetricRangeValue = MR.MetricRange
AND S.MetricDirection = MR.MetricDirection
) AS GT
ORDER BY
S.Store_ID,
S.MetricID;
DROP TABLE #Metrics;
DROP TABLE #Stores;
Take a good close look at what it's doing and see if it offers value.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply