Ascend HQ Analytics Metrics

This article covers the metrics currently included on the Analytics page of Ascend HQ.

Sherpa tip: All metrics measure finished (closed) transactions only and exclude tax. Sales with no sales items (e.g. gift card/gift certificate sales) are excluded from calculations.


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
To verify these numbers in Ascend, do the following:
  1. In Ascend HQ, select a single location
  2. Set the Date Range to Year to Date
  3. In Ascend, go to Reports and click Prod. Query on the toolbar
  4. Set your From date to the first day of the calendar year
  5. Click the Totals Only button on the toolbar
  6. Click Execute to run the Query
  7. Units should equal Units Sold in Ascend HQ
  8. Gross Sale Price should equal Sales in Ascend HQ
  9. Profit should equal Profit in Ascend HQ
  10. Margin (Sale) should equal Margin in Ascend HQ

Mountain Metrics

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 Labor category (i.e. top parent category contains Labor or Labour), 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 LabCats
AS
(SELECT
              Categories.*
       FROM.Categories
       WHERE (Categories.Topic LIKE '%Labor%'
       OR Categories.Topic LIKE '%Labour%')
       AND Categories.TopParentID IS NULL
       AND Categories.Hide = 0 UNION ALL SELECT
              Categories.*
       FROM.Categories
       INNER JOIN LabCats
              ON LabCats.ID = Categories.ParentID
       WHERE Categories.Hide = 0),
BikeCat
AS
(SELECT
              Categories.*
       FROM.Categories
       WHERE (Categories.Topic LIKE '%Bike%'
       OR Categories.Topic LIKE '%Bicycle%')
       AND Categories.TopParentID IS NULL
       AND Categories.Hide = 0
       AND Categories.Topic NOT LIKE '%Part%'
       AND Categories.Topic NOT LIKE '%Comfort%' UNION ALL SELECT
              Categories.*
       FROM.Categories
       INNER JOIN BikeCat
              ON BikeCat.ID = Categories.ParentID
       WHERE Categories.Hide = 0
       AND Categories.Topic NOT LIKE '%Frame%'),
Frame
AS
(SELECT
              Categories.*
       FROM.Categories
       WHERE Categories.Hide = 0
       AND Categories.Topic LIKE '%Frame%'
       AND Categories.TopParentID IN (SELECT
                     TopParentID
              FROM BikeCat)),
Excl
AS
(SELECT
              Categories.*
       FROM.Categories
       WHERE (Topic LIKE '%Services%'
       OR Topic LIKE 'Rental'
       OR ID IN (SELECT
                     ID
              FROM BikeCat)
       OR ID IN (SELECT
                     ID
              FROM LabCats)
       )
       AND Categories.TopParentID IS NULL
       AND Categories.Hide = 0 UNION ALL SELECT
              Categories.*
       FROM.Categories
       INNER JOIN Excl
              ON Excl.ID = Categories.ParentID
       WHERE Categories.Hide = 0
       AND Categories.ID NOT IN (SELECT
                     ID
              FROM Frame)),
AddCats
AS
(SELECT
              Categories.*
       FROM.Categories
       WHERE Hide = 0
       AND ID NOT IN (SELECT
                     ID
              FROM Excl)),
LabProds
AS
(SELECT
              Products.*
       FROM.Products
       WHERE TopicID IN (SELECT
                     ID
              FROM LabCats)),
AddProds
AS
(SELECT
              Products.*
       FROM.Products
       WHERE TopicID IN (SELECT
                     ID
              FROM AddCats))
SELECT
       [Avg. WO Detail Created $] =
       CASE
              WHEN ISNULL((SELECT
                                  COUNT(DISTINCT WorkOrderDetails.ID)
                           FROM.Sales
                           INNER JOIN.WorkOrderDetails
                                  ON Sales.ID = WorkOrderDetails.SalesID
                           WHERE Sales.Hide = 0
                           AND Sales.FinishDate BETWEEN <> AND <>
                           AND WorkOrderDetails.Hide = 0
                           AND WorkOrderDetails.DateCompleted IS NOT NULL)
                     , 0) = 0 THEN 0
              ELSE (ISNULL((SELECT
                                  SUM(SalesItems.Price * SalesItems.Quantity)
                           FROM.Sales
                           INNER JOIN.SalesItems
                                  ON Sales.ID = SalesItems.SalesID
                           INNER JOIN.WorkOrderDetails
                                  ON WorkOrderDetails.ID = SalesItems.WorkOrderDetailID
                           WHERE Sales.Hide = 0
                           AND Sales.FinishDate BETWEEN <> AND <>
                           AND WorkOrderDetails.Hide = 0
                           AND SalesItems.Hide = 0
                           AND WorkOrderDetails.DateCompleted IS NOT NULL
                           AND SalesItems.ProductID IN (SELECT
                                         ID
                                  FROM AddProds))
                     , 0) + ISNULL((SELECT
                                  SUM(SalesItems.Price * SalesItems.Quantity)
                           FROM.Sales
                           INNER JOIN.SalesItems
                                  ON Sales.ID = SalesItems.SalesID
                           INNER JOIN.WorkOrderDetails
                                  ON WorkOrderDetails.ID = SalesItems.WorkOrderDetailID
                           WHERE Sales.Hide = 0
                           AND Sales.FinishDate BETWEEN <> AND <>
                           AND WorkOrderDetails.Hide = 0
                           AND SalesItems.Hide = 0
                           AND WorkOrderDetails.DateCompleted IS NOT NULL
                           AND SalesItems.ProductID IN (SELECT
                                         ID
                                  FROM LabProds))
                     , 0)) / ISNULL((SELECT
                                  COUNT(DISTINCT WorkOrderDetails.ID)
                           FROM.Sales
                           INNER JOIN.WorkOrderDetails
                                  ON Sales.ID = WorkOrderDetails.SalesID
                           WHERE Sales.Hide = 0
                           AND Sales.FinishDate BETWEEN <> AND <>
                           AND WorkOrderDetails.Hide = 0
                           AND WorkOrderDetails.DateCompleted IS NOT NULL)
                     , 0)
       END

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: Inventory values prior to April 1, 2019 are not tracked.

NPS: Net Promoter Score as calculated by Listen360.

Average Transaction Size

This metric measures Sales Dollars divided by the Number of Transactions.
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.




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 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

This metric measures Total Work Order Add-on Dollars divided by Total Work Order Labor Dollars and is displayed as a percentage.
NOTE: Work Order Add-on Dollars = Non-Labor and Non-serialized products on a Work Order with at least 1 Labor SKU




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.  
-- 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

A Target Category is a key category of the business. Right now, the two Target Categories that we measure are Labor and Bikes. Soon, retailers will be able to define their own Target Categories. The following metrics will be displayed for each designated Target Category.

% of Total Sales

This metric measures the Total Sales Dollars in a Target Category divided by Total Sales from all categories and is displayed as a percentage.

To verify these numbers in Ascend, do the following: 
  1. Run the Activity Summary
  2. Divide the Target Category Sales by Total Sales
  3. 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

This metric measures the Total Sales Dollars in a Target Category divided by the Units Sold the same Target Category.



To verify Average Price in Ascend, do the following:
  1. In Ascend HQ, select a single location
  2. Set the Date Range to Year to Date
  3. In Ascend, go to Reports and click Prod. Query on the toolbar
  4. Set your From date to the first day of the calendar year
  5. Select the Target Category you're measuring. In this example, we'll choose Bikes.
  6. Click the Totals Only button on the toolbar
  7. Click Execute to run the Query
  8. Divide Gross Sale Price by Units 


Add-on Dollars

This metric measures Total Sales Dollars (excluding Target Category, Serialized, and Labor Item ) divided by the Number of Sales containing a Target Item.

IMPORTANT: Ascend Analytics did not exclude other Target categories from the Add-ons calculation. So if two bikes were present on a transaction, for example, the other bike would be included as an Add-on. We corrected this in Ascend HQ. This means the value in Ascend Analytics will likely be higher than it is in Ascend HQ.  



Run this query to verify your Add-on Dollars for your target category in your database.
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

**Coming Soon**

Average Work Order

WITH LabCats
AS
(SELECT
              Categories.*
      FROM.Categories
      WHERE (Categories.Topic LIKE '%Labor%'
      OR Categories.Topic LIKE '%Labour%')
      AND Categories.TopParentID IS NULL
      AND Categories.Hide = 0 UNION ALL SELECT
              Categories.*
      FROM.Categories
      INNER JOIN LabCats
              ON LabCats.ID = Categories.ParentID
      WHERE Categories.Hide = 0),
BikeCat
AS
(SELECT
              Categories.*
      FROM.Categories
      WHERE (Categories.Topic LIKE '%Bike%'
      OR Categories.Topic LIKE '%Bicycle%')
      AND Categories.TopParentID IS NULL
      AND Categories.Hide = 0
      AND Categories.Topic NOT LIKE '%Part%'
      AND Categories.Topic NOT LIKE '%Comfort%' UNION ALL SELECT
              Categories.*
      FROM.Categories
      INNER JOIN BikeCat
              ON BikeCat.ID = Categories.ParentID
      WHERE Categories.Hide = 0
      AND Categories.Topic NOT LIKE '%Frame%'),
Frame
AS
(SELECT
              Categories.*
      FROM.Categories
      WHERE Categories.Hide = 0
      AND Categories.Topic LIKE '%Frame%'
      AND Categories.TopParentID IN (SELECT
                    TopParentID
              FROM BikeCat)),
Excl
AS
(SELECT
              Categories.*
      FROM.Categories
      WHERE (Topic LIKE '%Services%'
      OR Topic LIKE 'Rental'
      OR ID IN (SELECT
                    ID
              FROM BikeCat)
      OR ID IN (SELECT
                    ID
              FROM LabCats)
      )
      AND Categories.TopParentID IS NULL
      AND Categories.Hide = 0 UNION ALL SELECT
              Categories.*
      FROM.Categories
      INNER JOIN Excl
              ON Excl.ID = Categories.ParentID
      WHERE Categories.Hide = 0
      AND Categories.ID NOT IN (SELECT
                    ID
              FROM Frame)),
AddCats
AS
(SELECT
              Categories.*
      FROM.Categories
      WHERE Hide = 0
      AND ID NOT IN (SELECT
                    ID
              FROM Excl)),
LabProds
AS
(SELECT
              Products.*
      FROM.Products
      WHERE TopicID IN (SELECT
                    ID
              FROM LabCats)),
AddProds
AS
(SELECT
              Products.*
      FROM.Products
      WHERE TopicID IN (SELECT
                    ID
              FROM AddCats))
SELECT
      [Avg. WO Detail Created $] =
      CASE
              WHEN ISNULL((SELECT
                                  COUNT(DISTINCT WorkOrderDetails.ID)
                          FROM.Sales
                          INNER JOIN.WorkOrderDetails
                                  ON Sales.ID = WorkOrderDetails.SalesID
                          WHERE Sales.Hide = 0
                          AND Sales.FinishDate BETWEEN <<StartDate>> AND <<EndDate>>
                          AND WorkOrderDetails.Hide = 0
                          AND WorkOrderDetails.DateCompleted IS NOT NULL)
                    , 0) = 0 THEN 0
              ELSE (ISNULL((SELECT
                                  SUM(SalesItems.Price * SalesItems.Quantity)
                          FROM.Sales
                          INNER JOIN.SalesItems
                                  ON Sales.ID = SalesItems.SalesID
                          INNER JOIN.WorkOrderDetails
                                  ON WorkOrderDetails.ID = SalesItems.WorkOrderDetailID
                          WHERE Sales.Hide = 0
                          AND Sales.FinishDate BETWEEN <<StartDate>> AND <<EndDate>>
                          AND WorkOrderDetails.Hide = 0
                          AND SalesItems.Hide = 0
                          AND WorkOrderDetails.DateCompleted IS NOT NULL
                          AND SalesItems.ProductID IN (SELECT
                                        ID
                                  FROM AddProds))
                    , 0) + ISNULL((SELECT
                                  SUM(SalesItems.Price * SalesItems.Quantity)
                          FROM.Sales
                          INNER JOIN.SalesItems
                                  ON Sales.ID = SalesItems.SalesID
                          INNER JOIN.WorkOrderDetails
                                  ON WorkOrderDetails.ID = SalesItems.WorkOrderDetailID
                          WHERE Sales.Hide = 0
                          AND Sales.FinishDate BETWEEN <<StartDate>> AND <<EndDate>>
                          AND WorkOrderDetails.Hide = 0
                          AND SalesItems.Hide = 0
                          AND WorkOrderDetails.DateCompleted IS NOT NULL
                          AND SalesItems.ProductID IN (SELECT
                                        ID
                                  FROM LabProds))
                    , 0)) / ISNULL((SELECT
                                  COUNT(DISTINCT WorkOrderDetails.ID)
                          FROM.Sales
                          INNER JOIN.WorkOrderDetails
                                  ON Sales.ID = WorkOrderDetails.SalesID
                          WHERE Sales.Hide = 0
                          AND Sales.FinishDate BETWEEN <<StartDate>> AND <<EndDate>>
                          AND WorkOrderDetails.Hide = 0
                          AND WorkOrderDetails.DateCompleted IS NOT NULL)
                    , 0)
      END

Additional resources

Feedback and Knowledge Base