← Back to Dimensions
Store Inventory Status
Also known as: Stock Status, Local Inventory Availability, In-Store Availability
Retail store-level availability state — In Stock, Low Stock or Out of Stock — that was displayed to the shopper for the requested item at the store they selected, at the moment of the check. The value is resolved per store rather than per item: it is keyed by store_id on the store inventory check, is sourced from store-level salable quantity rather than the global catalog, and gates whether a BOPIS order can be placed at all. A store-context event that arrives without a resolved availability label is reported as (not set), the single unknown bucket, so the complete value set is In Stock, Low Stock, Out of Stock or (not set).
Business Use Case
1️⃣ The Merchandising team reviews the weekly availability mix by store and category; when Out of Stock exposure on a top-selling item exceeds its threshold at a cluster of stores, they reallocate assortment depth and raise the store replenishment minimum for that item so BOPIS demand stops being turned away at the product page.,2️⃣ The Store Operations team monitors the gap between displayed availability and picked orders; when a store shows In Stock but its BOPIS pick failure rate rises, they trigger a cycle count and a shelf audit at that location to correct the position feeding the storefront.,3️⃣ The Marketing team suppresses paid local inventory listings and store-targeted campaigns for item-store pairs that have been shown as Out of Stock over a sustained period, redirecting budget to stores with confirmed availability and cutting spend on clicks that cannot convert to a pickup order.,4️⃣ The Product team quantifies lost conversion by comparing add-to-cart and BOPIS order rates across the three availability states; when Low Stock underperforms In Stock beyond the expected margin, they test alternative messaging and nearby-store substitution on the product page.
Business Criticality
High
Journey / Navigation Area
Discovery
Owning Function
Merchandising
Scope
Event
Calculation Notes
1️⃣ Point-in-time semantics: the value is the availability label as shown to the shopper when the store inventory check resolved, stamped onto the event payload. Reporting must read the stamped value and never re-derive or restate it from current inventory position, otherwise a store that has since been replenished will retrospectively erase the out-of-stock experiences that suppressed demand.,2️⃣ Allowed values are restricted to the shopper-visible set — In Stock, Low Stock, Out of Stock — matching the availability attribute published to local inventory listings. Any store or locale rendering a different label (for example Limited Availability) maps to the nearest of the three before the event is dispatched; free-text passthrough is rejected at collection. A store-context event that arrives with no resolved label is reported as (not set), the one unknown value, so the complete reported value set is In Stock, Low Stock, Out of Stock or (not set).,3️⃣ Exclusion: on-hand unit counts, reserved and safety-stock quantities, incoming replenishment dates and store transfer positions are backend values and are explicitly out of scope. Only the displayed availability state is carried, so the dimension can never be used to reconstruct stock depth.,4️⃣ Carried on the unified standard events view_item, select_item and add_to_cart — the collection points specified by the Product View, Add to Cart, Store Inventory Check and Store Selection event specs — and only when a store has actually been selected or defaulted from the shopper's saved or geolocated store. Events with no store context are not assigned a value and must be filtered out of availability reporting rather than bucketed as Out of Stock.,5️⃣ Low Stock is a threshold state defined by the merchandising availability rules, not a distinct physical condition; because thresholds differ by category and store format, trend comparisons that treat Low Stock as a single homogeneous band must be qualified by category and store format.,6️⃣ Multi-line and multi-store journeys resolve per item and per store, so a single session can legitimately contain several different values. Session-level rollups take the status of the last check per item-store pair within the 30-minute GA4 session window, and cross-session analysis must not carry the value forward — availability is re-checked on every new session.,7️⃣ Not related to the published logistics dimension Inventory Class, which classifies items by ABC value contribution for stocking policy. The two are never interchangeable and must not be joined as synonyms in reporting.
Technical Details
Data Typestring
Lookback WindowEvent, Session, Daily, Weekly
Events
| Platform | Event |
|---|---|
| Google Analytics 4 | view_item |
| Adobe | Product View: Store Availability Check |
Data Mappings
W3 Data Layer
{
"digitalData": {
"pageInstanceID": "prod-web-pdp",
"page": {
"pageInfo": {
"pageID": "pdp-sku-48219",
"pageName": "PDP: 20V Cordless Drill Kit",
"destinationURL": "https://www.example-retail.com/tools/20v-cordless-drill-kit",
"language": "en-US",
"issueDate": "2026-09-17T14:32:05.000Z"
},
"category": {
"primaryCategory": "Tools",
"subCategory1": "Power Tools",
"pageType": "product detail"
}
},
"product": [
{
"productInfo": {
"productID": "SKU-48219",
"productName": "20V Cordless Drill Kit",
"manufacturer": "ToolCraft",
"sku": "SKU-48219"
},
"category": {
"primaryCategory": "Tools",
"subCategory1": "Power Tools"
},
"attributes": {
"price": 129.99,
"currency": "USD",
"fulfillmentMethod": "bopis",
"storeID": "STR-0412",
"storeName": "Chicago River North",
"storeInventoryStatus": "Low Stock",
"storeInventoryStatusCheckedAt": "2026-09-17T14:32:05.000Z",
"storeSelectionMethod": "saved_store"
}
}
],
"event": [
{
"eventInfo": {
"eventName": "view_item",
"eventAction": "Store Availability Check",
"timeStamp": "2026-09-17T14:32:05.000Z"
},
"attributes": {
"storeID": "STR-0412",
"storeInventoryStatus": "Low Stock",
"fulfillmentMethod": "bopis"
}
}
],
"version": "1.0"
}
}GA4 Data Layer
{
"event": "view_item",
"store_id": "STR-0412",
"store_name": "Chicago River North",
"store_selection_method": "saved_store",
"store_inventory_status": "Low Stock",
"store_inventory_status_checked_at": "2026-09-17T14:32:05.000Z",
"fulfillment_method": "bopis",
"ecommerce": {
"currency": "USD",
"value": 129.99,
"items": [
{
"item_id": "SKU-48219",
"item_name": "20V Cordless Drill Kit",
"item_brand": "ToolCraft",
"item_category": "Tools",
"item_category2": "Power Tools",
"price": 129.99,
"quantity": 1,
"item_store_id": "STR-0412",
"item_store_inventory_status": "Low Stock",
"item_store_inventory_status_checked_at": "2026-09-17T14:32:05.000Z",
"item_fulfillment_method": "bopis"
}
]
}
}Adobe Client Data Layer
{
"event": "view_item",
"eventInfo": {
"path": "component.productDetail.storeAvailability",
"eventAction": "Store Availability Check",
"eventTimestamp": "2026-09-17T14:32:05.000Z"
},
"productContext": {
"productID": "SKU-48219",
"productName": "20V Cordless Drill Kit",
"productBrand": "ToolCraft",
"productCategory": "Tools",
"productSubCategory": "Power Tools",
"price": 129.99,
"currency": "USD",
"quantity": 1,
"storeID": "STR-0412",
"storeInventoryStatus": "Low Stock",
"storeInventoryStatusCheckedAt": "2026-09-17T14:32:05.000Z"
},
"storeContext": {
"storeID": "STR-0412",
"storeName": "Chicago River North",
"storeFormat": "full-line",
"storeSelectionMethod": "saved_store",
"fulfillmentMethod": "bopis"
}
}XDM Mapping
{
"eventType": "commerce.productViews",
"timestamp": "2026-09-17T14:32:05.000Z",
"commerce": {
"productViews": {
"value": 1
}
},
"productListItems": [
{
"SKU": "SKU-48219",
"name": "20V Cordless Drill Kit",
"quantity": 1,
"priceTotal": 129.99,
"currencyCode": "USD",
"productAvailability": "Low Stock",
"availabilityCheckedAt": "2026-09-17T14:32:05.000Z",
"fulfillmentMethod": "bopis",
"storeID": "STR-0412"
}
],
"placeContext": {
"POIinteraction": {
"POIDetail": {
"poiID": "STR-0412",
"name": "Chicago River North",
"category": "retail store"
}
}
},
"_experience": {
"analytics": {
"customDimensions": {
"eVars": {
"eVar41": "Low Stock",
"eVar42": "STR-0412"
}
}
}
}
}SQL Queries
Google Analytics 4 (BigQuery)
-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Dimension: Store Inventory Status (string, Event scope)
-- Source: GA4 export, unified events view_item / select_item / add_to_cart.
-- Point-in-time rule: the availability label is read exactly as it was stamped on the
-- event payload at the moment the store inventory check resolved. No join to a live or
-- current inventory position is performed anywhere below, so replenishment after the fact
-- can never restate an Out of Stock experience.
-- Store and item labels are read from the event payload itself; no reference joins are used.
-- Window: the 28 complete days ending yesterday, matching the AEP script exactly.
DECLARE start_date STRING DEFAULT FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY));
DECLARE end_date STRING DEFAULT FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY));
WITH
-- 1) Base population: every candidate event, with event-scoped parameters pivoted.
-- Every parameter is pivoted with UNNEST + MAX(IF(...)); no fan-out joins on event_params.
base_events AS (
SELECT
PARSE_DATE('%Y%m%d', event_date) AS event_day,
event_name AS unified_event_name,
user_pseudo_id,
TIMESTAMP_MICROS(event_timestamp) AS event_ts,
CONCAT(
user_pseudo_id, '.',
CAST((SELECT MAX(IF(ep.key = 'ga_session_id', ep.value.int_value, NULL))
FROM UNNEST(event_params) ep) AS STRING)
) AS session_key,
(SELECT MAX(IF(ep.key = 'store_id', ep.value.string_value, NULL))
FROM UNNEST(event_params) ep) AS store_id,
(SELECT MAX(IF(ep.key = 'store_name', ep.value.string_value, NULL))
FROM UNNEST(event_params) ep) AS store_name_evt,
(SELECT MAX(IF(ep.key = 'store_selection_method', ep.value.string_value, NULL))
FROM UNNEST(event_params) ep) AS store_selection_method,
(SELECT MAX(IF(ep.key = 'store_inventory_status', ep.value.string_value, NULL))
FROM UNNEST(event_params) ep) AS store_inventory_status_evt,
(SELECT MAX(IF(ep.key = 'store_inventory_status_checked_at', ep.value.string_value, NULL))
FROM UNNEST(event_params) ep) AS status_checked_at_evt,
(SELECT MAX(IF(ep.key = 'fulfillment_method', ep.value.string_value, NULL))
FROM UNNEST(event_params) ep) AS fulfillment_method_evt,
device.category AS device_category,
IFNULL(geo.region, '(not set)') AS region,
IFNULL(traffic_source.medium, '(not set)') AS traffic_source_medium,
items
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN start_date AND end_date
AND event_name IN ('view_item', 'select_item', 'add_to_cart')
),
-- 2) Item grain: the status is resolved per item and per store, so it is read from the
-- item-scoped parameters first and falls back to the event-scoped stamp.
item_grain AS (
SELECT
b.event_day,
b.unified_event_name,
b.user_pseudo_id,
b.session_key,
b.event_ts,
b.device_category,
b.region,
b.traffic_source_medium,
b.store_selection_method,
it.item_id,
it.item_name,
IFNULL(it.item_category, '(not set)') AS item_category,
COALESCE(
(SELECT MAX(IF(ip.key = 'item_store_id', ip.value.string_value, NULL))
FROM UNNEST(it.item_params) ip),
b.store_id
) AS store_id,
b.store_name_evt,
COALESCE(
(SELECT MAX(IF(ip.key = 'item_store_inventory_status', ip.value.string_value, NULL))
FROM UNNEST(it.item_params) ip),
b.store_inventory_status_evt
) AS store_inventory_status_raw,
COALESCE(
(SELECT MAX(IF(ip.key = 'item_store_inventory_status_checked_at', ip.value.string_value, NULL))
FROM UNNEST(it.item_params) ip),
b.status_checked_at_evt
) AS status_checked_at_raw,
COALESCE(
(SELECT MAX(IF(ip.key = 'item_fulfillment_method', ip.value.string_value, NULL))
FROM UNNEST(it.item_params) ip),
b.fulfillment_method_evt
) AS fulfillment_method
FROM base_events b
LEFT JOIN UNNEST(b.items) AS it
),
-- 3) Normalise to the documented value set: the three shopper-visible states plus the
-- '(not set)' unknown bucket for a store-context event that carried no resolved label.
-- Any other raw string also resolves to '(not set)' — free-text labels are rejected at
-- collection and are never passed through as dimension values.
typed AS (
SELECT
g.*,
CASE LOWER(TRIM(g.store_inventory_status_raw))
WHEN 'in stock' THEN 'In Stock'
WHEN 'low stock' THEN 'Low Stock'
WHEN 'out of stock' THEN 'Out of Stock'
ELSE '(not set)'
END AS store_inventory_status,
SAFE_CAST(g.status_checked_at_raw AS TIMESTAMP) AS status_checked_at,
(g.store_id IS NOT NULL AND g.store_id <> '') AS has_store_context
FROM item_grain g
),
-- 4) Session resolution: a session takes the status of the LAST check per item-store pair
-- inside the 30-minute session window, so one session is never counted under every
-- status it encountered for the same item at the same store.
resolved AS (
SELECT
t.*,
ROW_NUMBER() OVER (
PARTITION BY t.session_key, t.item_id, t.store_id
ORDER BY COALESCE(t.status_checked_at, t.event_ts) DESC, t.event_ts DESC
) = 1 AS is_last_check_in_session
FROM typed t
)
-- ============================================================
-- A. Value distribution: the dimension value, the events it rides on, and volume by value.
-- Denominator = every store-context event occurrence in the window, including the
-- occurrences where the status was absent. Nothing is silently dropped.
-- ============================================================
SELECT
t.store_inventory_status,
t.unified_event_name,
COUNT(*) AS event_occurrences,
COUNT(DISTINCT IF(t.is_last_check_in_session, t.session_key, NULL)) AS sessions,
COUNT(DISTINCT t.user_pseudo_id) AS users,
COUNT(DISTINCT t.item_id) AS distinct_items,
COUNT(DISTINCT t.store_id) AS distinct_stores,
ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct_of_all_store_context_events,
ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY t.unified_event_name), 2)
AS pct_of_event_population,
MIN(t.status_checked_at) AS first_status_checked_at,
MAX(t.status_checked_at) AS last_status_checked_at
FROM resolved t
WHERE t.has_store_context
GROUP BY t.store_inventory_status, t.unified_event_name
ORDER BY event_occurrences DESC;
-- ============================================================
-- B. Item x store exposure: which item-store pairs shoppers are most often shown as
-- unavailable. Store and item labels are taken from the event payload itself; the status
-- is never re-derived.
-- ============================================================
SELECT
t.store_id,
MAX(t.store_name_evt) AS store_name,
t.item_id,
ANY_VALUE(t.item_name) AS item_name,
ANY_VALUE(t.item_category) AS item_category,
COUNT(*) AS store_context_events,
COUNTIF(t.store_inventory_status = 'In Stock') AS in_stock_events,
COUNTIF(t.store_inventory_status = 'Low Stock') AS low_stock_events,
COUNTIF(t.store_inventory_status = 'Out of Stock') AS out_of_stock_events,
COUNTIF(t.store_inventory_status = '(not set)') AS status_not_set_events,
ROUND(100 * COUNTIF(t.store_inventory_status = 'Out of Stock') / COUNT(*), 2)
AS pct_out_of_stock_exposure,
ROUND(100 * COUNTIF(t.store_inventory_status IN ('Low Stock', 'Out of Stock')) / COUNT(*), 2)
AS pct_constrained_exposure
FROM typed t
WHERE t.has_store_context
GROUP BY t.store_id, t.item_id
ORDER BY out_of_stock_events DESC, store_context_events DESC;
-- ============================================================
-- C. Daily trend and collection coverage. The coverage columns keep every candidate event
-- in the window as their denominator, store context present or not, so gaps in the
-- tagging stay visible. The availability shares use the store-context population as
-- their denominator: events with no store context are filtered out of availability
-- reporting rather than bucketed.
-- ============================================================
SELECT
t.event_day,
t.store_selection_method,
t.fulfillment_method,
t.device_category,
COUNT(*) AS candidate_events,
COUNTIF(t.has_store_context) AS store_context_events,
COUNTIF(t.has_store_context AND t.store_inventory_status <> '(not set)') AS events_with_status,
ROUND(100 * COUNTIF(t.has_store_context AND t.store_inventory_status <> '(not set)') / COUNT(*), 2)
AS pct_status_coverage_of_all_events,
ROUND(100 * COUNTIF(t.has_store_context AND t.store_inventory_status = 'In Stock') / NULLIF(COUNTIF(t.has_store_context), 0), 2)
AS pct_in_stock_of_store_context_events,
ROUND(100 * COUNTIF(t.has_store_context AND t.store_inventory_status = 'Low Stock') / NULLIF(COUNTIF(t.has_store_context), 0), 2)
AS pct_low_stock_of_store_context_events,
ROUND(100 * COUNTIF(t.has_store_context AND t.store_inventory_status = 'Out of Stock') / NULLIF(COUNTIF(t.has_store_context), 0), 2)
AS pct_out_of_stock_of_store_context_events
FROM typed t
GROUP BY t.event_day, t.store_selection_method, t.fulfillment_method, t.device_category
ORDER BY t.event_day DESC, candidate_events DESC;Adobe Experience Platform (AEP Query Service)
-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Dimension: Store Inventory Status (string, Event scope)
-- Source: ExperienceEvent dataset, XDM paths declared in the mapping:
-- productListItems[].productAvailability -> the stamped availability label
-- productListItems[].availabilityCheckedAt -> when the store check resolved
-- productListItems[].storeID / fulfillmentMethod
-- placeContext.POIinteraction.POIDetail.poiID / name
-- _experience.analytics.customDimensions.eVars.eVar41 / eVar42 (event-scoped fallback)
-- Carrier events (same three unified events as the BigQuery script):
-- view_item -> eventType commerce.productViews
-- select_item -> eventType web.webinteraction.linkClicks with web.webInteraction.name = 'select_item'
-- (the product-list click convention used across this catalog; commerce.productListOpens
-- is the initialisation of a new product list/cart, NOT a product-list click, so it is
-- deliberately not used here)
-- add_to_cart -> eventType commerce.productListAdds
-- Parity notes against the BigQuery script: XDM carries no store-selection-method field, so block C
-- groups by the carrier event where BigQuery groups by store_selection_method, and block B omits the
-- item_category column BigQuery reports (productListItems carries no category in this mapping).
-- Point-in-time rule: the label is read as captured on the event. No join to a current
-- inventory dataset is made, so a later replenishment cannot restate past exposure.
-- Store and item labels are read from the event payload itself; no reference joins are used.
-- Window: the 28 complete days ending yesterday, matching the BigQuery script exactly.
WITH base_events AS (
SELECT
to_date(timestamp) AS event_day,
timestamp AS event_ts,
CASE
WHEN eventType = 'commerce.productViews' THEN 'view_item'
WHEN eventType = 'web.webinteraction.linkClicks'
AND web.webInteraction.name = 'select_item' THEN 'select_item'
WHEN eventType = 'commerce.productListAdds' THEN 'add_to_cart'
END AS unified_event_name,
_id AS event_id,
enduserids._experience.mcid.id AS visitor_id,
concat(enduserids._experience.mcid.id, '-',
cast(_experience.analytics.session.num AS string)) AS session_key,
placeContext.POIinteraction.POIDetail.poiID AS poi_store_id,
placeContext.POIinteraction.POIDetail.name AS poi_store_name,
_experience.analytics.customDimensions.eVars.eVar41 AS status_evar,
_experience.analytics.customDimensions.eVars.eVar42 AS store_evar,
device.type AS device_category,
placeContext.geo.stateProvince AS region,
productListItems
FROM experience_events
WHERE timestamp >= current_date - interval '28' day
AND timestamp < current_date
AND (
eventType IN ('commerce.productViews', 'commerce.productListAdds')
OR (eventType = 'web.webinteraction.linkClicks' AND web.webInteraction.name = 'select_item')
)
),
-- Item grain: availability is resolved per item and per store.
item_grain AS (
SELECT
b.event_day,
b.event_ts,
b.unified_event_name,
b.event_id,
b.visitor_id,
b.session_key,
b.device_category,
b.region,
pli.SKU AS item_id,
pli.name AS item_name,
coalesce(pli.storeID, b.poi_store_id, b.store_evar) AS store_id,
b.poi_store_name AS store_name_evt,
coalesce(pli.productAvailability, b.status_evar) AS store_inventory_status_raw,
pli.availabilityCheckedAt AS status_checked_at_raw,
pli.fulfillmentMethod AS fulfillment_method
FROM base_events b
LATERAL VIEW OUTER explode(b.productListItems) exploded AS pli
),
-- Normalise to the documented value set: the three shopper-visible states plus the
-- '(not set)' unknown bucket for a store-context occurrence with no resolved label. Any
-- other raw string also resolves to '(not set)'; free-text labels are never passed through.
typed AS (
SELECT
g.*,
CASE lower(trim(g.store_inventory_status_raw))
WHEN 'in stock' THEN 'In Stock'
WHEN 'low stock' THEN 'Low Stock'
WHEN 'out of stock' THEN 'Out of Stock'
ELSE '(not set)'
END AS store_inventory_status,
cast(g.status_checked_at_raw AS timestamp) AS status_checked_at,
(g.store_id IS NOT NULL AND g.store_id <> '') AS has_store_context
FROM item_grain g
),
-- Session resolution: a session takes the status of the LAST check per item-store pair
-- inside the session window, so one session is never counted under every status it
-- encountered for the same item at the same store.
resolved AS (
SELECT
t.*,
row_number() OVER (
PARTITION BY t.session_key, t.item_id, t.store_id
ORDER BY coalesce(t.status_checked_at, t.event_ts) DESC, t.event_ts DESC
) = 1 AS is_last_check_in_session
FROM typed t
)
-- ============================================================
-- A. Value distribution by carrier event. Denominator = all store-context occurrences,
-- including the ones where the status was absent.
-- ============================================================
SELECT
t.store_inventory_status,
t.unified_event_name,
count(*) AS event_occurrences,
count(DISTINCT CASE WHEN t.is_last_check_in_session THEN t.session_key END)
AS sessions,
count(DISTINCT t.visitor_id) AS visitors,
count(DISTINCT t.item_id) AS distinct_items,
count(DISTINCT t.store_id) AS distinct_stores,
round(100.0 * count(*) / sum(count(*)) OVER (), 2) AS pct_of_all_store_context_events,
round(100.0 * count(*) / sum(count(*)) OVER (PARTITION BY t.unified_event_name), 2)
AS pct_of_event_population,
min(t.status_checked_at) AS first_status_checked_at,
max(t.status_checked_at) AS last_status_checked_at
FROM resolved t
WHERE t.has_store_context
GROUP BY t.store_inventory_status, t.unified_event_name
ORDER BY event_occurrences DESC;
-- ============================================================
-- B. Item x store exposure. Store and item labels come from the event payload itself.
-- ============================================================
SELECT
t.store_id,
max(t.store_name_evt) AS store_name,
t.item_id,
max(t.item_name) AS item_name,
count(*) AS store_context_events,
count(CASE WHEN t.store_inventory_status = 'In Stock' THEN 1 END) AS in_stock_events,
count(CASE WHEN t.store_inventory_status = 'Low Stock' THEN 1 END) AS low_stock_events,
count(CASE WHEN t.store_inventory_status = 'Out of Stock' THEN 1 END) AS out_of_stock_events,
count(CASE WHEN t.store_inventory_status = '(not set)' THEN 1 END) AS status_not_set_events,
round(100.0 * count(CASE WHEN t.store_inventory_status = 'Out of Stock' THEN 1 END) / count(*), 2)
AS pct_out_of_stock_exposure,
round(100.0 * count(CASE WHEN t.store_inventory_status IN ('Low Stock', 'Out of Stock') THEN 1 END) / count(*), 2)
AS pct_constrained_exposure
FROM typed t
WHERE t.has_store_context
GROUP BY t.store_id, t.item_id
ORDER BY out_of_stock_events DESC, store_context_events DESC;
-- ============================================================
-- C. Daily trend and collection coverage. Coverage columns are measured against every
-- candidate occurrence; the availability shares use the store-context population as
-- their denominator, because occurrences with no store context are filtered out of
-- availability reporting rather than bucketed.
-- ============================================================
SELECT
t.event_day,
t.unified_event_name,
t.fulfillment_method,
t.device_category,
count(*) AS candidate_events,
count(CASE WHEN t.has_store_context THEN 1 END) AS store_context_events,
count(CASE WHEN t.has_store_context AND t.store_inventory_status <> '(not set)' THEN 1 END)
AS events_with_status,
round(100.0 * count(CASE WHEN t.has_store_context AND t.store_inventory_status <> '(not set)' THEN 1 END) / count(*), 2)
AS pct_status_coverage_of_all_events,
round(100.0 * count(CASE WHEN t.has_store_context AND t.store_inventory_status = 'In Stock' THEN 1 END) / nullif(count(CASE WHEN t.has_store_context THEN 1 END), 0), 2)
AS pct_in_stock_of_store_context_events,
round(100.0 * count(CASE WHEN t.has_store_context AND t.store_inventory_status = 'Low Stock' THEN 1 END) / nullif(count(CASE WHEN t.has_store_context THEN 1 END), 0), 2)
AS pct_low_stock_of_store_context_events,
round(100.0 * count(CASE WHEN t.has_store_context AND t.store_inventory_status = 'Out of Stock' THEN 1 END) / nullif(count(CASE WHEN t.has_store_context THEN 1 END), 0), 2)
AS pct_out_of_stock_of_store_context_events
FROM typed t
GROUP BY t.event_day, t.unified_event_name, t.fulfillment_method, t.device_category
ORDER BY t.event_day DESC, candidate_events DESC;Source Data
Digital Analytics (GA4) + Storefront-Stamped Store Availability Label (Adobe Commerce MSI)
Dependencies
Prerequisite: List the dependencies required for this Dimension
Events:
product-viewadd-to-cartstore-inventory-checkstore-selection
Dimensions:
Report Attributes
store-id fulfillment-method item-category device-category delivery-region traffic-source
Segment Eligibility
true
Governance
Data SensitivityInternal
Derived Dimensions
Governance
Created bydevyendarm
Created on9/18/2026
Last modified bydevyendarm
Last modified on9/20/2026
Version1.0.0
Validation stateqa passed
StatusPUBLISHED
GitHub
View related Pull Request
catalog/dimensions/store-inventory-status.yml