Are Your Patrons Browsing or Just Picking Up?

Clearinghouse Repository Polaris Repository
1

Shoutout to @bgaydos for the Sierra origins of this script. Please don’t tell him :shushing_face: this version for the Polaris ILS has been vibe-coded. Also, this output has been lightly tested. If something seems weird to you, the script is likely broken. Feel free to wiki-edit or comment if something seems off.

This script attempts to measure patron behavior inside the building. Did the patron come in just to pick up a hold they requested online? Or did they come for a hold, see a display, and grab three more books?

Below is a SQL script for the Polaris ILS that attempts to answer the question: Is our library a destination for browsing, or a fulfillment center for holds?

The Logic: What defines a “Visit”?

This script uses a specific logic to group transactions together:

  1. The 2-Hour Window: Using the SQL LAG function, the script looks at the timestamps of a patron’s transactions. If a checkout occurs more than 120 minutes after their previous checkout, it counts as a New Visit. If it happens within that window, it’s part of the same visit.

  2. Excluding Renewals: Renewals (Transaction SubType 124) are excluded. We only care about physical interactions with the collection at the desk or self-check.

  3. The Classification: Once a visit is defined, the script analyzes the items checked out during that session:

    • Hold Only: Every item checked out was linked to a Hold Request.

    • Browse Only: No items had a Hold Request; everything was picked from the shelf.

    • Hybrid: The patron picked up a hold and found other items to borrow while they were there.

The Script

SQL

/* Polaris Visit Classification Report 
   Excludes Renewals (SubType 124).
*/

-- 1. Setup Report Parameters
--    The CKO transaction has to happen between these two dates
DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
DECLARE @NewVisitIntervalMinutes INT = 120; -- 2 Hours determines a "New Visit"

-- Calculate "Previous Month" dynamically
SET @StartDate = DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0); 
SET @EndDate = DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0);        

-- Cleanup temp tables
IF OBJECT_ID('tempdb..#RawTransactions') IS NOT NULL DROP TABLE #RawTransactions;
IF OBJECT_ID('tempdb..#VisitCalculations') IS NOT NULL DROP TABLE #VisitCalculations;

-- 2. Gather Checkout Transactions (6001)
SELECT 
    th.TransactionID,
    th.OrganizationID,
    o.Name AS BranchName,
    th.TranClientDate,
    MAX(CASE WHEN td.TransactionSubTypeID = 6 THEN td.numValue ELSE NULL END) AS PatronID,
    MAX(CASE WHEN td.TransactionSubTypeID = 233 THEN td.numValue ELSE NULL END) AS HoldRequestID
INTO #RawTransactions
FROM PolarisTransactions.polaris.TransactionHeaders th WITH (NOLOCK)
JOIN Polaris.polaris.Organizations o WITH (NOLOCK) ON th.OrganizationID = o.OrganizationID
JOIN PolarisTransactions.polaris.TransactionDetails td WITH (NOLOCK) 
    ON th.TransactionID = td.TransactionID 
WHERE th.TransactionTypeID = 6001 
AND th.TranClientDate >= @StartDate 
AND th.TranClientDate < @EndDate
AND td.TransactionSubTypeID IN (6, 233, 124) 
GROUP BY 
    th.TransactionID, 
    th.OrganizationID, 
    o.Name, 
    th.TranClientDate
HAVING MAX(CASE WHEN td.TransactionSubTypeID = 124 THEN 1 ELSE 0 END) = 0; -- Exclude Renewals

CREATE INDEX IX_RawTrans_PatronDate ON #RawTransactions(PatronID, TranClientDate);

-- 3. Calculate Visits
SELECT 
    rt.TransactionID,
    rt.OrganizationID,
    rt.BranchName,
    rt.PatronID,
    rt.TranClientDate,
    CASE WHEN rt.HoldRequestID IS NOT NULL THEN 1 ELSE 0 END AS IsHoldCheckout,
    CASE WHEN rt.HoldRequestID IS NULL THEN 1 ELSE 0 END AS IsBrowsedCheckout,
    CASE 
        WHEN DATEDIFF(MINUTE, 
            LAG(rt.TranClientDate, 1, '1900-01-01') OVER (PARTITION BY rt.PatronID, rt.OrganizationID ORDER BY rt.TranClientDate), 
            rt.TranClientDate
        ) > @NewVisitIntervalMinutes -- uses 1900 as the first visit if we can't find a visit before the time period
        THEN 1 
        ELSE 0 
    END AS IsNewVisit
INTO #VisitCalculations
FROM #RawTransactions rt;

-- 4. Aggregate and Classify
;WITH VisitsGrouped AS (
    SELECT 
        OrganizationID,
        BranchName,
        SUM(IsNewVisit) OVER (PARTITION BY PatronID, OrganizationID ORDER BY TranClientDate) AS VisitSequence,
        PatronID,
        IsHoldCheckout,
        IsBrowsedCheckout
    FROM #VisitCalculations
),
VisitSummary AS (
    SELECT 
        OrganizationID,
        BranchName,
        PatronID,
        VisitSequence,
        -- Determine the Nature of the Visit
        CASE 
            WHEN SUM(IsHoldCheckout) > 0 AND SUM(IsBrowsedCheckout) > 0 THEN 'Hybrid'
            WHEN SUM(IsHoldCheckout) > 0 AND SUM(IsBrowsedCheckout) = 0 THEN 'Hold Only'
            WHEN SUM(IsHoldCheckout) = 0 AND SUM(IsBrowsedCheckout) > 0 THEN 'Browse Only'
            ELSE 'Unknown'
        END AS VisitType
    FROM VisitsGrouped
    GROUP BY OrganizationID, BranchName, PatronID, VisitSequence
)
SELECT 
    BranchName AS [Branch],
    COUNT(VisitSequence) AS [Total Visits],
    
    -- Breakdown of Visit Types
    SUM(CASE WHEN VisitType = 'Hold Only' THEN 1 ELSE 0 END) AS [Hold Only Visits],
    SUM(CASE WHEN VisitType = 'Browse Only' THEN 1 ELSE 0 END) AS [Browse Only Visits],
    SUM(CASE WHEN VisitType = 'Hybrid' THEN 1 ELSE 0 END) AS [Hybrid Visits],

    -- Percentages for Context
    CAST((CAST(SUM(CASE WHEN VisitType = 'Hold Only' THEN 1 ELSE 0 END) AS DECIMAL(10,1)) / COUNT(VisitSequence)) * 100 AS DECIMAL(5,1)) AS [% Hold Only],
    CAST((CAST(SUM(CASE WHEN VisitType = 'Browse Only' THEN 1 ELSE 0 END) AS DECIMAL(10,1)) / COUNT(VisitSequence)) * 100 AS DECIMAL(5,1)) AS [% Browse Only],
    CAST((CAST(SUM(CASE WHEN VisitType = 'Hybrid' THEN 1 ELSE 0 END) AS DECIMAL(10,1)) / COUNT(VisitSequence)) * 100 AS DECIMAL(5,1)) AS [% Hybrid]

FROM VisitSummary
GROUP BY BranchName, OrganizationID
ORDER BY BranchName;

-- Cleanup
DROP TABLE #RawTransactions;
DROP TABLE #VisitCalculations;

How to Read the Results

When you analyze the output, look for these trends:

  • High “Hold Only” %: Your patrons are treating the library efficiently, likely using the catalog or app heavily. If this is too high, your in-library displays areas might need a refresh.

  • High “Browse Only” %: Your physical collection is working well. Patrons are coming in and finding things serendipitously but your online presence might need some work to encourage patrons to place more hold requests.

  • The “Hybrid” Sweet Spot: This is the metric to watch. A high Hybrid score means your Hold pickup process is successfully tempting patrons to grab “just one more book” while they are there.

  • Outliers: If you see a browse CKO at a hold pickup only location like a locker, it likely means that a staff member logged into a workstation at that location and processed a non-hold CKO.

6 Likes