Ascend HQ Analytics Metrics
Mini Metrics

- Sales = Sales Dollars
- Profit = Sales minus Cost of Goods Sold
- Margin = (Profit / Sales) * 100 displayed as a percentage
- Units Sold = Quantity of Items Sold
- In Ascend HQ, select a single location
- Set the Date Range to Year to Date
- In Ascend, go to Reports and click Prod. Query on the toolbar
- Set your From date to the first day of the calendar year
- Click the Totals Only button on the toolbar
- Click Execute to run the Query
- Units should equal Units Sold in Ascend HQ
- Gross Sale Price should equal Sales in Ascend HQ
- Profit should equal Profit in Ascend HQ
- Margin (Sale) should equal Margin in Ascend HQ

Retail Profit Machine (RPM)
Sherpa tip: Values are color-coded according to their score – green indicates exceptional performance, orange indicates adequate performance, and blue indicates performance which needs improvement. Click the toggle button (double square icon) to display the minimum target value for each score.
Bikes – Av. Price: Total value (sales dollars, excluding tax) divided by total quantity of sales items which were finished (closed) in the selected date range and which are assigned to a Bikes category (i.e. top parent category contains Bikes or Bicycles).
Av. Add-on $: Total value (sales dollars, excluding tax) divided by total quantity of sales items which were finished (closed) in the selected date range and which are not assigned to a Bikes category (i.e. top parent category contains Bikes or Bicycles), which are not assigned to a serialized category, and which are not assigned to a Labor category (i.e. top parent category contains Labor or Labour).
Av. Work Order: For Work Order Details which are completed an whose transaction was finished (closed) in the selected date range: total value (sales dollars, excluding tax) of sales items which were added to a Work Order Detail and which are not assigned to a Bikes category (i.e. top parent category contains Bikes or Bicycles), which are not assigned to a serialized category, which are not assigned to a Rental category (i.e. top parent category contains Rental), and which are assigned to a category divided by the total number of Work Order Details.
To verify this number, run the following query in Ascend. Be sure to enter the date range that equals what you're looking at in Ascend HQ.
-- Average Work Order WITH WorkOrderSales
AS
(SELECT
SUM(Si.Quantity * Si.Price) AS [WOSales]
FROM Sales S
JOIN SalesItems Si
ON S.ID = Si.SalesID
JOIN WorkOrderDetails WO
ON Si.WorkOrderDetailID = WO.ID
WHERE S.Hide = 0
AND Si.Hide = 0
AND WO.DateCompleted IS NOT NULL
AND Si.RentalReservationID IS NULL
AND Si.ProductID IN (SELECT
ID
FROM Products
WHERE TopicID IS NOT NULL)
AND Si.SerialID IS NULL
AND S.FinishDate BETWEEN <<StartDate>> AND <<EndDate>>),
WOCount
AS
(SELECT
COUNT(DISTINCT Si.WorkOrderDetailID) AS [WOCount]
FROM Sales S
JOIN SalesItems Si
ON S.ID = Si.SalesID
JOIN WorkOrderDetails WO
ON Si.WorkOrderDetailID = WO.ID
WHERE S.Hide = 0
AND Si.Hide = 0
AND WO.DateCompleted IS NOT NULL
AND Si.RentalReservationID IS NULL
AND Si.SerialID IS NULL
AND S.FinishDate BETWEEN <<StartDate>> AND <<EndDate>>),
Results
AS
(SELECT
WorkOrderSales.WOSales
,WOCount.WOCount
FROM WorkOrderSales
,WOCount)
SELECT
[Avg Work Order] =
CASE
WHEN R.WOSales = 0 THEN 0
WHEN R.WOCount = 0 THEN 0
ELSE R.WOSales / R.WOCount
END
FROM Results R
Sales/Hour: Total value (sales dollars, excluding
tax) of transactions finished (closed) divided by total hours worked (as
recorded in Ascend’s Time Clock) by all users.
Sherpa tip: This data is only
included when all of your location have updated to the January 2020
version of Ascend.
Email Capture: Percent of total transactions for the store with a customer associated who has an email address entered which were finished (closed) in the selected date range.
Av. DIS: The estimated number of days products have
been in stock without selling. Total number of days in the selected date range
divided by the number of turns (total inventory difference – beginning
inventory value minus ending inventory value – divided by two).
Sherpa tip: Av. DIS is NOT an average of the Remaining DIS metric. Inventory
values prior to April 1, 2019 are not tracked.
NPS: Net Promoter Score as calculated by Listen360.
Average Transaction Size
Sherpa tip: Transactions that were finished (closed) but which did not include any sale items (e.g. cancelled layaways/work orders or gift card/certificate sales) are not included in this calculation.

-- Average Transaction Size SELECT [ATS]= ISNULL(((SELECT CASE WHEN SUM(Si.Quantity * Si.Price)= 0
THEN NULL
ELSE SUM(Si.Quantity * Si.Price)
END
FROM SalesItems Si
JOIN Sales S ON Si.SalesID=S.ID
WHERE S.Hide=0
AND Si.Hide=0
AND S.FinishDate BETWEEN <<StartDate>> AND <<EndDate>>)
/
(SELECT COUNT(DISTINCT si.SalesID)
FROM Sales S
JOIN SalesItems si ON si.SalesID = s.ID
WHERE S.Hide=0
AND si.hide = 0
AND S.FinishDate BETWEEN <<StartDate>> AND <<EndDate>>)),0)
Work Order Attachment Rate

-- Work Order Attachment Rate
Select
Case When w2.LaborDollars = 0 Then 0 Else Round((Cast(w2.AddonDollars as Decimal(9,2))/Cast(w2.LaborDollars as Decimal(9,2))) * 100 , 2) End As AttachmentRate
From (
Select
Sum(w.AddonDollars) as AddonDollars,
Sum(w.LaborDollars) as LaborDollars
From (
Select
Sum(Case When labCat.ID Is Not Null Then 0 Else si.Quantity * Round(si.Price, 4) End) As AddonDollars,
s.ID,
s.LaborDollars
From (
Select
s.ID,
Sum(si.Quantity * Round(si.Price, 4)) As LaborDollars
From Sales s
Join SalesItems si On si.SalesID = s.ID
Join Products p On p.ID = si.ProductID
Join Categories c On c.Id = p.TopicID
Join (
Select c.Id
From Categories c
Where Lower(c.Topic) Like('lab%')
And c.Hide <> 1 And c.ParentID Is Null) labCat
On labCat.Id = c.TopParentID Or labCat.Id = c.ID
Where s.Hide <> 1
And si.Hide <> 1
And s.WorkOrder <> 0
And s.FinishDate BETWEEN <<StartDate>> AND <<EndDate>>
Group By s.ID) s
Join SalesItems si On si.SalesID = s.Id
Join Products p On p.ID = si.ProductID
Left Join Categories c On c.ID = p.TopicID
Left Join (
Select c.Id
From Categories c
Where Lower(c.Topic) Like('lab%')
And c.Hide <> 1 And c.ParentID Is Null) labCat
On labCat.Id = c.TopParentID Or labCat.Id = c.ID
Where si.Hide <> 1
And (c.ID Is Null Or c.Serialized <> 1)
Group By s.ID, s.LaborDollars) w) w2
Target Categories
% of Total Sales
- Run the Activity Summary
- Divide the Target Category Sales by Total Sales
- Example for calculating this metric can be found in the image below. In this case, the Bikes -- % of Total Sales would be 62.95%.

Average Price

- In Ascend HQ, select a single location
- Set the Date Range to Year to Date
- In Ascend, go to Reports and click Prod. Query on the toolbar
- Set your From date to the first day of the calendar year
- Select the Target Category you're measuring. In this example, we'll choose Bikes.
- Click the Totals Only button on the toolbar
- Click Execute to run the Query
- Divide Gross Sale Price by Units

Add-on Dollars

WITH CategoryIds (CategoryId)
AS (
-- Anchor member definition
SELECT
c.Id AS CategoryId
FROM Categories c
WHERE c.Hide <> 1
AND Lower(c.Topic) Like(Lower(<<Category>>))
UNION ALL
-- Recursive member definition
SELECT
c.Id AS CategoryId
FROM Categories c
JOIN CategoryIds ci ON ci.CategoryId = c.ParentId
WHERE c.Hide <> 1
)
SELECT
IsNull(Sum(si.Quantity * Round(si.Price, 2)), 0) / (
Select
ISNULL(SUM(si.Quantity), 1) AS TargetsSold
From Sales s
Join SalesItems si On si.SalesID = s.ID
Join Products p On p.ID = si.ProductID
Join CategoryIds c ON c.CategoryId = p.TopicID
Where s.Hide <> 1
And si.Hide <> 1
And s.FinishDate BETWEEN <<StartDate>> AND <<EndDate>>) AS AverageAddOnDollars
FROM (
Select Distinct
s.Id
From Sales s
Join SalesItems si On si.SalesID = s.ID
Join Products p On p.ID = si.ProductID
Join CategoryIds c ON c.CategoryId = p.TopicID
Where s.Hide <> 1
And si.Hide <> 1
And s.FinishDate BETWEEN <<StartDate>> AND <<EndDate>>) s
Join SalesItems si On si.SalesID = s.Id
Join Products p On p.ID = si.ProductID
Left Join Categories c On c.Id = p.TopicID
Left Join (
Select c.Id
From Categories c
Where Lower(c.Topic) Like('lab%')
And c.Hide <> 1 And c.ParentID Is Null) labCat
On labCat.Id = c.TopParentID Or labCat.Id = c.ID
Left Join CategoryIds cc On cc.CategoryId = p.TopicID
Where labCat.ID Is Null
And (c.ID IS null Or c.Serialized <> 1)
And cc.CategoryId Is Null
And si.Hide <> 1