Store Inventory Checks
Also known as: Check Store Availability, Store Availability Checks, Local Inventory Lookups, In-Store Stock Checks, Check In Store
Counts the lookups in which a shopper requested real-time availability of a specific item (SKU and variant) at a specific store, recorded through the unified view_item event with view_context = 'store_availability' and a populated store_id, once per SKU × store × session. It sizes the local-inventory demand that feeds Buy Online, Pick Up In Store, and when read against the Store Inventory Status returned it shows how often shoppers look for local stock and find none.
Business Use Case
1️⃣ The Omnichannel Merchandising Manager reviews Store Inventory Checks by store and category every Monday; any store × category cell where the out_of_stock share of checks exceeds 25% for two consecutive weeks goes to the Replenishment team as a request to raise the store's safety stock or allocation for the SKUs most frequently checked. 2️⃣ The Store Operations Director compares weekly checks per store with BOPIS Orders for the same store; when checks grow while orders stay flat for three weeks, the store's inventory accuracy (cycle count variance) is audited, since shoppers are finding stock they no longer trust. 3️⃣ The Product Manager for the product detail page tracks checks per 1,000 product views after each release of the availability module; a drop of more than 15% against the trailing four-week average triggers a rollback review of the module's placement or store-selection flow. 4️⃣ The Local Marketing lead uses checks by store ZIP code for SKUs flagged out_of_stock to exclude those stores from local inventory ads and store-pickup promotions until stock is restored.
Business Criticality
High
Journey / Navigation Area
Discovery
Owning Function
Merchandising
Scope
Session
Formula
Calculation Notes
1️⃣ Qualification: a check counts only when the view_item event carries view_context = 'store_availability', a non-null store_id and a returned store_inventory_status (in_stock, limited_stock, out_of_stock, not_carried). A lookup that timed out or errored before the inventory service answered has no status and is excluded, and is reported separately as a service-health signal rather than as shopper demand. 2️⃣ Deduplication: one check per session_id × item_id × store_id. Re-opening the same availability panel, refreshing the page or a background re-poll of stock for the same SKU and store collapses into the first qualifying occurrence; changing the variant (size, colour) is a new item_id and counts again, as does switching to a different store. 3️⃣ Multi-store results (firing rule): when a lookup renders availability for several nearby stores ("check nearby stores" list), view_item with view_context = 'store_availability' fires once, for the store the shopper selected, and never once per store in the list; the metric therefore counts what the tag emits, and the rendered list belongs to Store Locator Searches. 4️⃣ Passive badges excluded (firing rule): stock badges rendered automatically on product listing and product detail pages for the shopper's saved store are impressions and never emit view_context = 'store_availability'; that value is set only when the shopper actively opens the availability module or changes the store, so view_context alone separates checks from badge impressions. 5️⃣ Session and timing: sessions follow the 30-minute GA4 inactivity timeout; checks are dated by event timestamp in the property's reporting time zone, and store_inventory_status_checked_at is kept to measure freshness of the stock snapshot but never used to date the check. 6️⃣ Exclusions: internal and store-associate traffic (traffic_type = internal, clienting and endless-aisle devices on the store network) and known bots are removed, because associates use the same module to answer in-store questions and would inflate shopper demand. 7️⃣ Reading the number: report the count with its store_inventory_status mix; a rising share of out_of_stock or not_carried results at constant check volume indicates a local assortment or replenishment gap, not a change in shopper intent.
Technical Details
Events
| Platform | Event |
|---|---|
| Google Analytics 4 | view_item |
| Adobe | prodView |
Data Mappings
W3 Data Layer
{
"event": "view_item",
"product": [
{
"productInfo": {
"productID": "{{item_id}}",
"productName": "{{item_name}}",
"sku": "{{item_id}}",
"variant": "{{item_variant}}",
"category": "{{item_category}}"
},
"attributes": {
"viewContext": "store_availability",
"storeId": "{{store_id}}",
"storeName": "{{store_name}}",
"storeSelectionMethod": "{{store_selection_method}}",
"storeInventoryStatus": "{{store_inventory_status}}",
"storeInventoryStatusCheckedAt": "{{store_inventory_status_checked_at}}",
"trafficType": "{{traffic_type}}",
"fulfillmentMethod": "{{fulfillment_method}}"
}
}
]
}GA4 Data Layer
{
"event": "view_item",
"view_context": "store_availability",
"store_id": "{{store_id}}",
"store_name": "{{store_name}}",
"store_selection_method": "{{store_selection_method}}",
"store_inventory_status": "{{store_inventory_status}}",
"store_inventory_status_checked_at": "{{store_inventory_status_checked_at}}",
"traffic_type": "{{traffic_type}}",
"fulfillment_method": "{{fulfillment_method}}",
"currency": "{{currency}}",
"value": "{{value}}",
"items": [
{
"item_id": "{{item_id}}",
"item_name": "{{item_name}}",
"item_variant": "{{item_variant}}",
"item_category": "{{item_category}}"
}
]
}Adobe Client Data Layer
{
"event": "prodView",
"eventInfo": {
"viewContext": "store_availability"
},
"product": {
"storeId": "{{store_id}}",
"storeName": "{{store_name}}",
"storeSelectionMethod": "{{store_selection_method}}",
"storeInventoryStatus": "{{store_inventory_status}}",
"storeInventoryStatusCheckedAt": "{{store_inventory_status_checked_at}}",
"trafficType": "{{traffic_type}}",
"fulfillmentMethod": "{{fulfillment_method}}",
"itemId": "{{item_id}}",
"itemVariant": "{{item_variant}}",
"itemCategory": "{{item_category}}"
}
}XDM Mapping
{
"eventType": "commerce.productViews",
"commerce": {
"productViews": {
"value": 1
}
},
"productListItems": [
{
"SKU": "{{item_id}}",
"name": "{{item_name}}",
"category": "{{item_category}}",
"fulfillmentMethod": "{{fulfillment_method}}"
}
],
"_experience": {
"analytics": {
"customDimensions": {
"eVars": {
"eVar1": "{{view_context}}",
"eVar2": "{{store_id}}",
"eVar3": "{{store_name}}",
"eVar4": "{{store_selection_method}}",
"eVar5": "{{store_inventory_status}}",
"eVar6": "{{traffic_type}}"
}
}
}
}
}SQL Queries
Google Analytics 4 (BigQuery)
-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
-- Store availability lookups from view_item, event params pivoted, one row per item; event_date is already in the property reporting time zone
WITH lookups AS (
SELECT
CONCAT(e.user_pseudo_id, '.', CAST((SELECT value.int_value FROM UNNEST(e.event_params) WHERE key = 'ga_session_id') AS STRING)) AS session_id,
i.item_id,
e.event_timestamp,
MAX(IF(p.key = 'view_context', p.value.string_value, NULL)) AS view_context,
MAX(IF(p.key = 'store_id', COALESCE(p.value.string_value, CAST(p.value.int_value AS STRING)), NULL)) AS store_id,
MAX(IF(p.key = 'store_inventory_status', p.value.string_value, NULL)) AS store_inventory_status,
MAX(IF(p.key = 'traffic_type', p.value.string_value, NULL)) AS traffic_type
FROM `your-project.analytics_123456789.events_*` AS e,
UNNEST(e.items) AS i,
UNNEST(e.event_params) AS p
WHERE e._TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)
AND e.event_name = 'view_item'
GROUP BY session_id, i.item_id, e.event_timestamp
),
-- Qualifying checks: active availability module (passive badges carry no store_availability context), store and returned status present, internal and bot traffic removed
qualified AS (
SELECT *
FROM lookups
WHERE view_context = 'store_availability'
AND store_id IS NOT NULL
AND store_inventory_status IS NOT NULL
AND item_id IS NOT NULL
AND COALESCE(traffic_type, 'external') NOT IN ('internal', 'bot')
),
-- One check per session x item_id x store_id, keeping the first qualifying occurrence (re-opens, refreshes and re-polls collapse)
checks AS (
SELECT session_id, item_id, store_id, store_inventory_status
FROM qualified
QUALIFY ROW_NUMBER() OVER (PARTITION BY session_id, item_id, store_id ORDER BY event_timestamp) = 1
)
-- Store Inventory Checks with its store_inventory_status mix; the 'all' row is the metric total
SELECT
COALESCE(store_inventory_status, 'all') AS store_inventory_status,
COUNT(*) AS store_inventory_checks
FROM checks
GROUP BY ROLLUP (store_inventory_status)
ORDER BY store_inventory_checks DESC;Adobe Experience Platform (AEP Query Service)
-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Reporting window: the last 90 complete days, dated in the property reporting time zone
WITH params AS (
SELECT date_sub(current_date(), 90) AS start_date, date_sub(current_date(), 1) AS end_date
),
-- Store availability lookups from product views, one row per product line; view_context, store_id, status and traffic_type read from the mapped eVars
lookups AS (
SELECT
concat(e.endUserIDs._experience.ecid.id, '.', CAST(e._experience.analytics.session.num AS STRING)) AS session_id,
pli.SKU AS item_id,
e.timestamp AS event_timestamp,
e._experience.analytics.customDimensions.eVars.eVar1 AS view_context,
e._experience.analytics.customDimensions.eVars.eVar2 AS store_id,
e._experience.analytics.customDimensions.eVars.eVar5 AS store_inventory_status,
e._experience.analytics.customDimensions.eVars.eVar6 AS traffic_type
FROM aep_web_events_dataset e
CROSS JOIN params pr
LATERAL VIEW explode(e.productListItems) AS pli
WHERE e.eventType = 'commerce.productViews'
AND to_date(from_utc_timestamp(e.timestamp, 'America/New_York')) BETWEEN pr.start_date AND pr.end_date
),
-- Qualifying checks: active availability module (passive badges carry no store_availability context), store and returned status present, internal and bot traffic removed
qualified AS (
SELECT *
FROM lookups
WHERE view_context = 'store_availability'
AND store_id IS NOT NULL
AND store_inventory_status IS NOT NULL
AND item_id IS NOT NULL
AND COALESCE(traffic_type, 'external') NOT IN ('internal', 'bot')
),
-- One check per session x item_id x store_id, keeping the first qualifying occurrence (re-opens, refreshes and re-polls collapse)
checks AS (
SELECT session_id, item_id, store_id, store_inventory_status
FROM (
SELECT q.*, ROW_NUMBER() OVER (PARTITION BY session_id, item_id, store_id ORDER BY event_timestamp) AS rn
FROM qualified q
) ranked
WHERE rn = 1
)
-- Store Inventory Checks with its store_inventory_status mix; the 'all' row is the metric total
SELECT
COALESCE(store_inventory_status, 'all') AS store_inventory_status,
COUNT(*) AS store_inventory_checks
FROM checks
GROUP BY ROLLUP (store_inventory_status)
ORDER BY store_inventory_checks DESC;Source Data
Digital Analytics (GA4)
Dependencies
Prerequisite: List the dependencies required for this Metric
Events:
Dimensions:
Report Attributes
Store Inventory Status Store / ZIP Code / Local Market Item Category Product Name Device Category Fulfillment Method
Recommended Reporting Views
Segment Eligibility
true
Governance
Derived KPIs
Governance
GitHub
catalog/metrics/store-inventory-checks.yml