Shopify as a data source

Bring data from Shopify to Nekt.

Shopify is a leading e-commerce platform for creating and managing online stores. Connecting Shopify to Nekt lets you centralize core commerce data — orders, products, customers, inventory, collections, and locations — sourced from the Shopify Admin GraphQL API. With incremental syncs and consistent schemas, you can join Shopify with marketing, finance, and operations data to drive end-to-end analytics like revenue performance, cohort behavior, merchandising impact, inventory health, and fulfillment efficiency.

Configuring Shopify as a Source

In the Sources tab, click on the “Add source” button located on the top right of your screen. Then, select the Shopify option from the list of connectors.

Click Next and you’ll be prompted to add your access.

1. Add account access

You’ll need a Shopify Admin API access token and your store identifier.

• Access Token: The private app access token for authenticating API requests.
• Store ID: The name of your Shopify store extracted from your store admin URL. Use only the store subdomain: https://[store].myshopify.com/admin → Store ID = [store].
• Start Date: The earliest record date to sync.

How to create a Custom App and generate an Access Token

Important: As of January 1, 2026, you can no longer create new custom apps directly in the Shopify admin. You must use the Dev Dashboard to create new apps. Legacy custom apps created before this date can still be managed in the Shopify admin.

Follow these steps to create a custom app in Shopify and generate a long-lived (non-expiring) access token for Nekt:

1. Access the Dev Dashboard

   1. Log in to your Shopify store admin at https://[your-store].myshopify.com/admin
   2. Go to Settings > Apps
   3. Click Develop apps
   4. Click Build apps in Dev Dashboard to open the Dev Dashboard
2. Create a new Custom App

   1. In the Dev Dashboard, click Create app
   2. In the “Start from Dev Dashboard” section, enter a name for your app (e.g., “Nekt Data Integration”)
   3. Click Create
3. Configure your app version

   1. In the URLs section:
      • Set your App URL to https://shopify.dev/apps/default-app-home
      • Add https://developers.google.com/oauthplayground as an Allowed redirection URL
   2. Select a Webhooks API Version (use the latest stable version)
   3. In the Access section, enter the following scopes:
      read_customers,read_inventory,read_locations,read_orders,read_products,read_reports
      
   4. Click Release
   5. Optionally enter a version name (e.g., “v1.0”) and message
   6. Click Release to confirm
4. Get your Client Credentials

   1. In the Dev Dashboard, go to your app’s Settings page
   2. Copy your Client ID and Client Secret

   Important: The client secret is sensitive information. Store it securely and never expose it in frontend code or public repositories.

5. Open the Google OAuth Playground

   Click the link below to open Google OAuth Playground pre-configured for Shopify:

   Open Shopify OAuth Playground →

6. Configure the OAuth Playground

   In the OAuth Playground, click the gear icon (⚙️) on the right side to open “OAuth 2.0 configuration” and update these values:

   1. Replace SHOP_NAME with your store’s subdomain in both:
      • OAuth authorization endpoint: https://[your-store].myshopify.com/admin/oauth/authorize
      • OAuth token endpoint: https://[your-store].myshopify.com/admin/oauth/access_token
   2. Replace SHOPIFY_CLIENT_ID with your Client ID
   3. Replace SHOPIFY_CLIENT_SECRET with your Client Secret
   4. Click Close
7. Authorize and Get Your Access Token

   1. Click Authorize APIs (Step 1 in the playground)
   2. You’ll be redirected to Shopify - click Install app to grant permissions
   3. After returning to the playground, click Exchange authorization code for tokens (Step 2)
   4. Your access token will appear in the response on the right side

   {
     "access_token": "shpat_xxxxxxxxxxxxxxxxxxxxx",
     "scope": "read_customers,read_inventory,read_locations,read_orders,read_products"
   }
   

   This token will remain valid indefinitely until you uninstall the app or rotate the client secret.

8. Copy your credentials for Nekt

   You now have everything needed to configure Shopify in Nekt:

   • Access Token: The access_token from the response (starts with shpat_)
   • Store ID: Your store’s subdomain from https://[store-id].myshopify.com

Managing Legacy Custom Apps (created before January 2026)

If you created a custom app before January 1, 2026, you can still manage it directly from your Shopify admin. These legacy apps provide a simpler experience with non-expiring tokens:

1. Access Legacy Apps

   1. Go to Settings > Apps in your Shopify admin
   2. Find your app in the Legacy custom apps section
   3. Click on the app to manage its settings
2. View or Regenerate Access Token

   1. Go to the API credentials tab
   2. Click Reveal token once to view your access token
   3. Copy the token immediately - it’s only shown once
   4. If you need a new token, uninstall and reinstall the app

   Legacy custom apps have non-expiring access tokens that are shown directly in the admin UI, making them simpler to use than new Dev Dashboard apps.

Once you’re done, click Next.

2. Select streams

Choose which data streams you want to sync — you can select all streams or pick specific ones that matter most to you. For faster extractions, select only the streams relevant to your analysis.

Tip: The stream can be found more easily by typing its name.

Select the streams and click Next.

3. Configure data streams

Customize how you want your data to appear in your catalog. Select the desired layer where the data will be placed, a folder to organize it inside the layer, a name for each table (which will effectively contain the fetched data) and the type of sync.

• Layer: Choose between the existing layers on your catalog. This is where you will find your new extracted tables as the extraction runs successfully.
• Folder: A folder can be created inside the selected layer to group all tables being created from this new data source.
• Table name: We suggest a name, but feel free to customize it. You have the option to add a prefix to all tables at once and make this process faster!
• Sync Type: You can choose between INCREMENTAL and FULL_TABLE.
  • Incremental: Every time the extraction happens, we’ll get only the new data — which is good if, for example, you want to keep every record ever fetched.
  • Full table: Every time the extraction happens, we’ll get the current state of the data — which is good if, for example, you don’t want to have deleted data in your catalog.

Once you are done configuring, click Next.

4. Configure data source

Describe your data source for easy identification within your organization, not exceeding 140 characters.

To define your Trigger, consider how often you want data to be extracted from this source. This decision usually depends on how frequently you need the new table data updated (every day, once a week, or only at specific times).

Optionally, you can determine when to execute a full sync. This will complement the incremental data extractions, ensuring that your data is completely synchronized with your source every once in a while.

Once you are ready, click Next to finalize the setup.

5. Check your new source

You can view your new source on the Sources page. If needed, manually trigger the source extraction by clicking on the arrow button. Once executed, your data will appear in your Catalog.

For you to be able to see it on your Catalog, you need at least one successful source run.

Streams and Fields

Below you’ll find all available data streams from Shopify and their corresponding fields:

Orders

Stream containing orders, transactions and line items. Supports incremental sync using updatedAt.

Key Fields:

• id - Unique identifier for the order
• name - The order name (e.g., #1001)
• legacyResourceId - Legacy numeric ID
• email, phone - Contact information
• processedAt, createdAt, updatedAt, cancelledAt, closedAt - Timestamps
• confirmed - Whether the order is confirmed
• currencyCode, presentmentCurrencyCode - Currency codes

Transactions:

• transactions[].id, kind, status, gateway, authorizationCode, test, paymentId
• transactions[].createdAt, processedAt
• transactions[].amountSet.presentmentMoney.amount, currencyCode
• transactions[].amountSet.shopMoney.amount, currencyCode
• transactions[].parentTransaction.id

Totals:

• totalPriceSet - Total price (shopMoney + presentmentMoney)
• subtotalPriceSet - Subtotal price
• totalShippingPriceSet - Total shipping
• totalDiscountsSet - Total discounts
• totalTaxSet - Total tax
• totalWeight, taxesIncluded, taxExempt

Statuses and Flags:

• displayFinancialStatus - Financial status (PAID, PENDING, REFUNDED, etc.)
• displayFulfillmentStatus - Fulfillment status (FULFILLED, UNFULFILLED, PARTIAL, etc.)
• fullyPaid, fulfillable, restockable, test, customerAcceptsMarketing
• sourceName, sourceIdentifier, currentSubtotalLineItemsQuantity
• tags, note, clientIp

Customer and Addresses:

• customer.id, firstName, lastName, email
• shippingAddress.* - Full shipping address
• billingAddress.* - Full billing address

Discounts and Metafields:

• discountCode, discountCodes[]
• metafields[].id, namespace, key, value, type, description, createdAt, updatedAt

Sales Channel:

• channelInformation.channelDefinition.handle

Line Items:

• lineItems[].id, title, quantity, sku, vendor, variantTitle
• lineItems[].customAttributes[].key, value
• lineItems[].discountedTotalSet.*, originalTotalSet.*
• lineItems[].discountAllocations[].allocatedAmount.amount, currencyCode
• lineItems[].discountAllocations[].discountApplication.targetType, allocationMethod, targetSelection, title, description, code
• lineItems[].variant.id, title, sku, product.id, product.title

Products

Stream containing products, variants, images and metafields. Supports incremental sync using updatedAt.

Key Fields:

• id - Unique product identifier
• title - Product title
• descriptionHtml - HTML description
• vendor - Vendor name
• productType - Product type/category
• handle - URL-friendly slug
• createdAt, updatedAt, publishedAt - Timestamps
• status - Product status (ACTIVE, ARCHIVED, DRAFT)
• tags - Product tags
• templateSuffix - Liquid template suffix

Options:

• options[].id, name, position, values[]

Variants:

• variants[].id, title, sku, price, position
• variants[].inventoryPolicy - Inventory policy (DENY, CONTINUE)
• variants[].compareAtPrice - Original price before discount
• variants[].createdAt, updatedAt, taxable, barcode
• variants[].inventoryItem.id - Link to inventory item

Images:

• images[].id, altText, originalSrc
• featuredImage.id, altText, originalSrc

Metafields:

• metafields[].id, namespace, key, value, type, description

Inventory Items

Stream containing inventory items, levels, and costing information. Supports incremental sync using updatedAt.

Key Fields:

• id - Unique inventory item ID
• legacyResourceId - Legacy numeric ID
• sku - Stock keeping unit
• tracked - Whether inventory is tracked
• trackedEditable.reason - Why tracked field is editable
• requiresShipping - Whether item requires shipping
• duplicateSkuCount - Count of duplicate SKUs
• inventoryHistoryUrl - URL to inventory history
• createdAt, updatedAt - Timestamps

Origin & Customs:

• countryCodeOfOrigin, provinceCodeOfOrigin
• harmonizedSystemCode
• countryHarmonizedSystemCodes[].countryCode, harmonizedSystemCode

Inventory Levels (per location):

• inventoryLevels[].id - Inventory level ID
• inventoryLevels[].location.id, location.name - Location info
• inventoryLevels[].item.id, item.sku - Item info
• inventoryLevels[].quantities[].id, name, quantity, updatedAt - Quantity by type (available, committed, incoming, etc.)

Cost and Measurements:

• unitCost.amount, unitCost.currencyCode
• measurement.weight.unit, measurement.weight.value
• locationsCount.count

Variant & Product:

• variant.id, variant.title, variant.sku
• variant.product.id, variant.product.title

Customers

Stream containing customer profiles. Supports incremental sync using updatedAt.

Key Fields:

• id - Unique customer ID
• firstName, lastName
• createdAt, updatedAt
• verifiedEmail, taxExempt, tags, state
• numberOfOrders - Total order count

Spend and Contacts:

• amountSpent.amount, amountSpent.currencyCode
• defaultEmailAddress.emailAddress, defaultEmailAddress.marketingState
• defaultPhoneNumber.phoneNumber, defaultPhoneNumber.marketingState, defaultPhoneNumber.marketingCollectedFrom

Addresses:

• addresses[].id, firstName, lastName, address1, city, province, country, zip, phone, name, provinceCode, countryCodeV2
• defaultAddress.* - Same structure as addresses

Metafields:

• metafields[].id, namespace, key, value, type, description

Collections

Stream containing product collections and included products. Supports incremental sync using updatedAt.

Key Fields:

• id, title, handle, description
• publishedAt, updatedAt

Image:

• image.altText, image.originalSrc

Products:

• products[].id, title, handle, createdAt, updatedAt
• products[].productType, vendor, tags, totalInventory, status, price, inventoryQuantity
• products[].variants[].id, title, sku, price, inventoryQuantity

Sorting and Template:

• sortOrder, sortType, sortValue, templateSuffix, url

Metafields:

• metafields[].id, namespace, key, value, type, description

Locations

Stream containing store and warehouse locations. Uses full table sync.

Key Fields:

• id, name
• active, activatable, deactivatable, deletable
• createdAt, updatedAt, deactivatedAt
• addressVerified, isFulfillmentService

Fulfillment Service:

• fulfillmentService.id, fulfillmentService.serviceName

Address:

• address.address1, address2, city, country, countryCode, province, provinceCode, zip

Metafields:

• metafields[].id, namespace, key, value, type, description

Abandoned Checkouts

Stream containing abandoned checkouts. Uses full table sync.

Key Fields:

• id - Unique identifier for the abandoned checkout
• abandonedCheckoutUrl - URL for the abandoned checkout
• createdAt, updatedAt, completedAt - Timestamps
• taxesIncluded - Whether taxes are included

Price Sets:

• subtotalPriceSet.presentmentMoney.amount, currencyCode
• totalPriceSet.presentmentMoney.amount, currencyCode
• totalTaxSet.presentmentMoney.amount, currencyCode
• totalDiscountSet.presentmentMoney.amount, currencyCode

Customer:

• customer.id, firstName, lastName, email, tags, state, note, createdAt, updatedAt

Addresses:

• billingAddress.* - Address lines, city, province, country, zip, phone
• shippingAddress.* - Address lines, city, province, country, zip, phone

Line Items:

• lineItems[].id, title, sku, quantity
• lineItems[].variant.id, variant.price

Taxes and Discounts:

• discountCodes[]
• taxLines[].rate, title, priceSet.presentmentMoney.amount, currencyCode

ShopifyQL Reports

In addition to the standard streams above, the Shopify connector supports ShopifyQL Reports — custom analytics queries written in ShopifyQL, Shopify’s analytics query language. This allows you to extract aggregated metrics directly from Shopify’s analytics engine, such as sales breakdowns, traffic data, and product performance reports.

Each ShopifyQL report you configure becomes its own data stream with a dynamically discovered schema based on the query’s result columns.

How to configure ShopifyQL Reports

ShopifyQL Reports are configured as an advanced setting when setting up the Shopify source.

Your token needs to have the read_reports scope to be able to use ShopifyQL Reports.

1. Open Advanced Settings

   When configuring your Shopify source access (step 1), look for the ShopifyQL Reports section under the advanced settings.

2. Add a report

   Click Add to create a new report entry. Each report requires two fields:

   • Report name: A unique identifier for the report (letters, numbers, and underscores only). This becomes part of the stream name — for example, a report named daily_sales produces a stream called report_daily_sales.
   • ShopifyQL query: The full ShopifyQL query to execute. The query must be valid ShopifyQL syntax.
3. Add more reports (optional)

   You can add as many ShopifyQL reports as needed. Each one becomes a separate stream that you can independently select and configure in the following steps.

ShopifyQL reports always run as full table syncs. Every execution re-runs the query and replaces the previous data. If your query includes a time range (e.g. SINCE -30d), the results will reflect the most recent window each time it runs.

The schema of each report is automatically discovered from the query results. If the query returns no data (e.g., the time range matches no records), schema discovery will fail. Make sure your query covers a time range that includes data.

ShopifyQL query examples

Below are ready-to-use ShopifyQL queries you can paste directly into the report configuration. Each example includes the recommended Report name and the Query to use.

Daily Sales Summary

Aggregated daily sales metrics including gross sales, discounts, returns, net sales, shipping, taxes, and total sales.

Report name: daily_sales

Query:

FROM sales
SHOW gross_sales, discounts, returns, net_sales, shipping, taxes, total_sales
GROUP BY day
SINCE -90d
UNTIL today
ORDER BY day ASC

Output columns:

Column	Type	Description
day	String	Date (YYYY-MM-DD)
gross_sales	String	Gross sales amount
discounts	String	Total discounts applied
returns	String	Total returns amount
net_sales	String	Net sales (gross - discounts - returns)
shipping	String	Shipping charges
taxes	String	Tax amount
total_sales	String	Total sales (net + shipping + taxes)

Monthly Sales Summary

Monthly aggregated sales for longer-term trend analysis.

Report name: monthly_sales

Query:

FROM sales
SHOW gross_sales, discounts, returns, net_sales, shipping, taxes, total_sales, orders
GROUP BY month
SINCE -12m
UNTIL today
ORDER BY month ASC

Output columns:

Column	Type	Description
month	String	Month (YYYY-MM)
gross_sales	String	Gross sales amount
discounts	String	Total discounts applied
returns	String	Total returns amount
net_sales	String	Net sales
shipping	String	Shipping charges
taxes	String	Tax amount
total_sales	String	Total sales
orders	String	Number of orders

Sales by Product

Sales breakdown by product title, useful for identifying top-performing products.

Report name: sales_by_product

Query:

FROM sales
SHOW product_title, product_type, gross_sales, net_sales, orders, total_sales
GROUP BY product_title, product_type
SINCE -90d
UNTIL today
ORDER BY net_sales DESC

Output columns:

Column	Type	Description
product_title	String	Product name
product_type	String	Product type/category
gross_sales	String	Gross sales for this product
net_sales	String	Net sales for this product
orders	String	Number of orders containing this product
total_sales	String	Total sales including shipping and taxes

Sales by Traffic Referrer

Sales attributed to each traffic source, useful for marketing attribution.

Report name: sales_by_referrer

Query:

FROM sales
SHOW referrer_name, referrer_source, gross_sales, net_sales, orders
GROUP BY referrer_name, referrer_source
SINCE -90d
UNTIL today
ORDER BY net_sales DESC

Output columns:

Column	Type	Description
referrer_name	String	Referrer name (e.g., Google, Facebook)
referrer_source	String	Referrer source type
gross_sales	String	Gross sales from this referrer
net_sales	String	Net sales from this referrer
orders	String	Number of orders from this referrer

Sales by Billing Country

Geographic breakdown of sales by customer billing country.

Report name: sales_by_country

Query:

FROM sales
SHOW billing_country, gross_sales, net_sales, orders, total_sales
GROUP BY billing_country
SINCE -90d
UNTIL today
ORDER BY net_sales DESC

Output columns:

Column	Type	Description
billing_country	String	Customer billing country
gross_sales	String	Gross sales from this country
net_sales	String	Net sales from this country
orders	String	Number of orders from this country
total_sales	String	Total sales from this country

Sales by Discount Code

Performance of each discount code, useful for evaluating promotion effectiveness.

Report name: sales_by_discount

Query:

FROM sales
SHOW discount_code, gross_sales, discounts, net_sales, orders
GROUP BY discount_code
SINCE -90d
UNTIL today
ORDER BY discounts DESC

Output columns:

Column	Type	Description
discount_code	String	Discount code used
gross_sales	String	Gross sales with this discount
discounts	String	Total discount amount applied
net_sales	String	Net sales after discount
orders	String	Number of orders using this discount

Daily Orders and Average Order Value

Daily order count with average order value for tracking purchasing patterns.

Report name: daily_orders

Query:

FROM sales
SHOW orders, gross_sales, net_sales, average_order_value
GROUP BY day
SINCE -90d
UNTIL today
ORDER BY day ASC

Output columns:

Column	Type	Description
day	String	Date (YYYY-MM-DD)
orders	String	Number of orders
gross_sales	String	Gross sales amount
net_sales	String	Net sales amount
average_order_value	String	Average order value

Working with ShopifyQL data

All ShopifyQL report columns are returned as String type, regardless of their original data type (money, number, percentage, etc.). Cast them to the appropriate types when querying in your Lakehouse:

AWS

SELECT
  day,
  CAST(gross_sales AS DOUBLE) AS gross_sales,
  CAST(net_sales AS DOUBLE) AS net_sales,
  CAST(orders AS INTEGER) AS orders,
  CASE
    WHEN CAST(orders AS INTEGER) > 0
    THEN ROUND(CAST(net_sales AS DOUBLE) / CAST(orders AS INTEGER), 2)
    ELSE 0
  END AS avg_order_value
FROM
  nekt_raw.shopify_report_daily_sales
ORDER BY
  day DESC

GCP

SELECT
  day,
  CAST(gross_sales AS FLOAT64) AS gross_sales,
  CAST(net_sales AS FLOAT64) AS net_sales,
  CAST(orders AS INT64) AS orders,
  CASE
    WHEN CAST(orders AS INT64) > 0
    THEN ROUND(SAFE_DIVIDE(CAST(net_sales AS FLOAT64), CAST(orders AS INT64)), 2)
    ELSE 0
  END AS avg_order_value
FROM
  `nekt_raw.shopify_report_daily_sales`
ORDER BY
  day DESC

ShopifyQL money values may include currency symbols or formatting depending on your store settings. If casting fails, you may need to strip non-numeric characters first.

Data Model

The following diagram illustrates the relationships between the core data streams in Shopify.

Use Cases for Data Analysis

This guide outlines valuable business intelligence use cases focused on inventory intelligence, helping you optimize stock levels, identify sales velocity, and prevent lost revenue from stockouts or overstocking. Run these queries on Explorer.

1. Inventory Health Dashboard

Get a comprehensive view of your inventory health by product, including stock levels, cost valuation, and risk classification across all locations.

Business Value:

• Identify products at risk of stockouts before they happen
• Spot overstocked items tying up capital
• Understand inventory distribution across locations

SQL query

AWS

WITH inventory_by_product AS (
  SELECT
    ii.variant.product.id AS product_id,
    ii.variant.product.title AS product_title,
    ii.sku,
    ii.variant.title AS variant_title,
    CAST(ii.unitCost.amount AS DOUBLE) AS unit_cost,
    ii.unitCost.currencyCode AS currency,
    SUM(
      CASE WHEN q.name = 'available' THEN q.quantity ELSE 0 END
    ) AS available_qty,
    SUM(
      CASE WHEN q.name = 'committed' THEN q.quantity ELSE 0 END
    ) AS committed_qty,
    SUM(
      CASE WHEN q.name = 'incoming' THEN q.quantity ELSE 0 END
    ) AS incoming_qty,
    COUNT(DISTINCT il.location.id) AS location_count
  FROM
    nekt_raw.shopify_inventory_items ii
    CROSS JOIN UNNEST(ii.inventoryLevels) AS t(il)
    CROSS JOIN UNNEST(il.quantities) AS t2(q)
  WHERE
    ii.tracked = true
  GROUP BY
    ii.variant.product.id,
    ii.variant.product.title,
    ii.sku,
    ii.variant.title,
    ii.unitCost.amount,
    ii.unitCost.currencyCode
)
SELECT
  product_title,
  sku,
  variant_title,
  available_qty,
  committed_qty,
  incoming_qty,
  (available_qty + incoming_qty) AS total_incoming_available,
  location_count,
  ROUND(unit_cost * available_qty, 2) AS inventory_value,
  currency,
  CASE
    WHEN available_qty <= 0 THEN 'OUT_OF_STOCK'
    WHEN available_qty <= 5 THEN 'LOW_STOCK'
    WHEN available_qty <= 20 THEN 'NORMAL'
    WHEN available_qty > 100 THEN 'OVERSTOCK'
    ELSE 'HEALTHY'
  END AS stock_status
FROM
  inventory_by_product
ORDER BY
  available_qty ASC

GCP

WITH inventory_by_product AS (
  SELECT
    ii.variant.product.id AS product_id,
    ii.variant.product.title AS product_title,
    ii.sku,
    ii.variant.title AS variant_title,
    CAST(ii.unitCost.amount AS FLOAT64) AS unit_cost,
    ii.unitCost.currencyCode AS currency,
    SUM(
      CASE WHEN q.name = 'available' THEN q.quantity ELSE 0 END
    ) AS available_qty,
    SUM(
      CASE WHEN q.name = 'committed' THEN q.quantity ELSE 0 END
    ) AS committed_qty,
    SUM(
      CASE WHEN q.name = 'incoming' THEN q.quantity ELSE 0 END
    ) AS incoming_qty,
    COUNT(DISTINCT il.location.id) AS location_count
  FROM
    `nekt_raw.shopify_inventory_items` ii,
    UNNEST(ii.inventoryLevels) AS il,
    UNNEST(il.quantities) AS q
  WHERE
    ii.tracked = true
  GROUP BY
    ii.variant.product.id,
    ii.variant.product.title,
    ii.sku,
    ii.variant.title,
    ii.unitCost.amount,
    ii.unitCost.currencyCode
)
SELECT
  product_title,
  sku,
  variant_title,
  available_qty,
  committed_qty,
  incoming_qty,
  (available_qty + incoming_qty) AS total_incoming_available,
  location_count,
  ROUND(unit_cost * available_qty, 2) AS inventory_value,
  currency,
  CASE
    WHEN available_qty <= 0 THEN 'OUT_OF_STOCK'
    WHEN available_qty <= 5 THEN 'LOW_STOCK'
    WHEN available_qty <= 20 THEN 'NORMAL'
    WHEN available_qty > 100 THEN 'OVERSTOCK'
    ELSE 'HEALTHY'
  END AS stock_status
FROM
  inventory_by_product
ORDER BY
  available_qty ASC

Sample Result

product_title	sku	variant_title	available_qty	committed_qty	incoming_qty	total_incoming_available	location_count	inventory_value	currency	stock_status
Classic White Tee	CWT-S	Small	0	3	50	50	2	0.00	USD	OUT_OF_STOCK
Denim Jacket	DJ-M	Medium	2	8	0	2	1	89.98	USD	LOW_STOCK
Canvas Sneakers	CS-42	EU 42	4	1	20	24	3	159.96	USD	LOW_STOCK
Leather Belt	LB-L	Large	15	2	0	15	2	224.85	USD	NORMAL
Summer Dress	SD-M	Medium	47	5	0	47	2	1,551.00	USD	HEALTHY
Basic Socks Pack	BSP-OS	One Size	342	12	200	542	3	1,710.00	USD	OVERSTOCK

Inventory Status Distribution:

Status	SKU Count	% of Catalog	Action
Healthy	112	45%	No action needed
Normal	62	25%	Monitor regularly
Low Stock	38	15%	Plan reorder
Out of Stock	20	8%	Urgent reorder
Overstock	18	7%	Consider markdowns

2. Sales Velocity & Days of Stock Remaining

Combine order data with inventory levels to understand how fast each product sells and estimate days of stock remaining — the core metric for preventing stockouts.

Business Value:

• Forecast when products will run out of stock
• Prioritize reordering based on sales velocity
• Identify fast-moving products that need safety stock buffers

SQL query

AWS

WITH daily_sales AS (
  SELECT
    li.variant.product.id AS product_id,
    li.variant.product.title AS product_title,
    li.sku,
    SUM(li.quantity) AS units_sold,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(CAST(li.discountedTotalSet.shopMoney.amount AS DOUBLE)) AS revenue
  FROM
    nekt_raw.shopify_orders o
    CROSS JOIN UNNEST(o.lineItems) AS t(li)
  WHERE
    o.displayFinancialStatus IN ('PAID', 'PARTIALLY_REFUNDED')
    AND o.test = false
    AND o.processedAt >= CURRENT_DATE - INTERVAL '30' DAY
  GROUP BY
    li.variant.product.id,
    li.variant.product.title,
    li.sku
),
current_stock AS (
  SELECT
    ii.variant.product.id AS product_id,
    ii.sku,
    SUM(
      CASE WHEN q.name = 'available' THEN q.quantity ELSE 0 END
    ) AS available_qty
  FROM
    nekt_raw.shopify_inventory_items ii
    CROSS JOIN UNNEST(ii.inventoryLevels) AS t(il)
    CROSS JOIN UNNEST(il.quantities) AS t2(q)
  WHERE
    ii.tracked = true
  GROUP BY
    ii.variant.product.id,
    ii.sku
)
SELECT
  ds.product_title,
  ds.sku,
  ds.units_sold AS units_sold_30d,
  ds.order_count AS orders_30d,
  ROUND(ds.revenue, 2) AS revenue_30d,
  ROUND(ds.units_sold / 30.0, 2) AS daily_velocity,
  cs.available_qty,
  CASE
    WHEN ds.units_sold / 30.0 > 0
    THEN CAST(ROUND(cs.available_qty / (ds.units_sold / 30.0), 0) AS INTEGER)
    ELSE NULL
  END AS days_of_stock,
  CASE
    WHEN cs.available_qty <= 0 THEN 'STOCKOUT'
    WHEN ds.units_sold / 30.0 > 0 AND cs.available_qty / (ds.units_sold / 30.0) <= 7 THEN 'CRITICAL'
    WHEN ds.units_sold / 30.0 > 0 AND cs.available_qty / (ds.units_sold / 30.0) <= 14 THEN 'REORDER_NOW'
    WHEN ds.units_sold / 30.0 > 0 AND cs.available_qty / (ds.units_sold / 30.0) <= 30 THEN 'MONITOR'
    ELSE 'HEALTHY'
  END AS reorder_urgency
FROM
  daily_sales ds
  LEFT JOIN current_stock cs ON ds.product_id = cs.product_id AND ds.sku = cs.sku
ORDER BY
  days_of_stock ASC NULLS LAST

GCP

WITH daily_sales AS (
  SELECT
    li.variant.product.id AS product_id,
    li.variant.product.title AS product_title,
    li.sku,
    SUM(li.quantity) AS units_sold,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(CAST(li.discountedTotalSet.shopMoney.amount AS FLOAT64)) AS revenue
  FROM
    `nekt_raw.shopify_orders` o,
    UNNEST(o.lineItems) AS li
  WHERE
    o.displayFinancialStatus IN ('PAID', 'PARTIALLY_REFUNDED')
    AND o.test = false
    AND DATE(o.processedAt) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  GROUP BY
    li.variant.product.id,
    li.variant.product.title,
    li.sku
),
current_stock AS (
  SELECT
    ii.variant.product.id AS product_id,
    ii.sku,
    SUM(
      CASE WHEN q.name = 'available' THEN q.quantity ELSE 0 END
    ) AS available_qty
  FROM
    `nekt_raw.shopify_inventory_items` ii,
    UNNEST(ii.inventoryLevels) AS il,
    UNNEST(il.quantities) AS q
  WHERE
    ii.tracked = true
  GROUP BY
    ii.variant.product.id,
    ii.sku
)
SELECT
  ds.product_title,
  ds.sku,
  ds.units_sold AS units_sold_30d,
  ds.order_count AS orders_30d,
  ROUND(ds.revenue, 2) AS revenue_30d,
  ROUND(ds.units_sold / 30.0, 2) AS daily_velocity,
  cs.available_qty,
  CASE
    WHEN ds.units_sold / 30.0 > 0
    THEN CAST(ROUND(cs.available_qty / (ds.units_sold / 30.0), 0) AS INT64)
    ELSE NULL
  END AS days_of_stock,
  CASE
    WHEN cs.available_qty <= 0 THEN 'STOCKOUT'
    WHEN ds.units_sold / 30.0 > 0 AND cs.available_qty / (ds.units_sold / 30.0) <= 7 THEN 'CRITICAL'
    WHEN ds.units_sold / 30.0 > 0 AND cs.available_qty / (ds.units_sold / 30.0) <= 14 THEN 'REORDER_NOW'
    WHEN ds.units_sold / 30.0 > 0 AND cs.available_qty / (ds.units_sold / 30.0) <= 30 THEN 'MONITOR'
    ELSE 'HEALTHY'
  END AS reorder_urgency
FROM
  daily_sales ds
  LEFT JOIN current_stock cs ON ds.product_id = cs.product_id AND ds.sku = cs.sku
ORDER BY
  days_of_stock ASC NULLS LAST

Sample Result

product_title	sku	units_sold_30d	orders_30d	revenue_30d	daily_velocity	available_qty	days_of_stock	reorder_urgency
Classic White Tee	CWT-S	89	76	2,225.00	2.97	0	0	STOCKOUT
Denim Jacket	DJ-M	42	38	3,318.00	1.40	2	1	CRITICAL
Canvas Sneakers	CS-42	35	30	2,794.65	1.17	4	3	CRITICAL
Leather Belt	LB-L	28	25	839.72	0.93	15	16	MONITOR
Summer Dress	SD-M	18	15	1,079.82	0.60	47	78	HEALTHY

Reorder Urgency Summary:

Urgency	SKU Count	Avg Days of Stock	Revenue at Risk (30d)
STOCKOUT	3	0	$6,750.00
CRITICAL (< 7 days)	5	3	$8,420.00
REORDER NOW (7-14 days)	8	11	$4,230.00
MONITOR (14-30 days)	12	22	$3,100.00
HEALTHY (> 30 days)	72	65	—

Products with STOCKOUT and CRITICAL urgency represent potential lost revenue. The “Revenue at Risk” column estimates how much revenue these products generated in the last 30 days — revenue you may lose if stock isn’t replenished.

3. Inventory Distribution by Location

Analyze how your inventory is distributed across fulfillment locations to optimize warehouse allocation and reduce shipping times.

Business Value:

• Balance inventory across warehouses to reduce shipping costs
• Identify locations running low on key products
• Optimize fulfillment routing

SQL query

AWS

WITH location_inventory AS (
  SELECT
    il.location.id AS location_id,
    il.location.name AS location_name,
    ii.variant.product.title AS product_title,
    ii.sku,
    SUM(
      CASE WHEN q.name = 'available' THEN q.quantity ELSE 0 END
    ) AS available_qty,
    SUM(
      CASE WHEN q.name = 'committed' THEN q.quantity ELSE 0 END
    ) AS committed_qty,
    CAST(ii.unitCost.amount AS DOUBLE) AS unit_cost
  FROM
    nekt_raw.shopify_inventory_items ii
    CROSS JOIN UNNEST(ii.inventoryLevels) AS t(il)
    CROSS JOIN UNNEST(il.quantities) AS t2(q)
  WHERE
    ii.tracked = true
  GROUP BY
    il.location.id,
    il.location.name,
    ii.variant.product.title,
    ii.sku,
    ii.unitCost.amount
)
SELECT
  location_name,
  COUNT(DISTINCT sku) AS unique_skus,
  SUM(available_qty) AS total_available,
  SUM(committed_qty) AS total_committed,
  ROUND(SUM(unit_cost * available_qty), 2) AS total_inventory_value,
  SUM(CASE WHEN available_qty <= 0 THEN 1 ELSE 0 END) AS out_of_stock_skus,
  SUM(CASE WHEN available_qty BETWEEN 1 AND 5 THEN 1 ELSE 0 END) AS low_stock_skus,
  ROUND(
    SUM(CASE WHEN available_qty <= 0 THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(DISTINCT sku), 0),
    1
  ) AS stockout_rate_pct
FROM
  location_inventory
GROUP BY
  location_name
ORDER BY
  total_inventory_value DESC

GCP

WITH location_inventory AS (
  SELECT
    il.location.id AS location_id,
    il.location.name AS location_name,
    ii.variant.product.title AS product_title,
    ii.sku,
    SUM(
      CASE WHEN q.name = 'available' THEN q.quantity ELSE 0 END
    ) AS available_qty,
    SUM(
      CASE WHEN q.name = 'committed' THEN q.quantity ELSE 0 END
    ) AS committed_qty,
    CAST(ii.unitCost.amount AS FLOAT64) AS unit_cost
  FROM
    `nekt_raw.shopify_inventory_items` ii,
    UNNEST(ii.inventoryLevels) AS il,
    UNNEST(il.quantities) AS q
  WHERE
    ii.tracked = true
  GROUP BY
    il.location.id,
    il.location.name,
    ii.variant.product.title,
    ii.sku,
    ii.unitCost.amount
)
SELECT
  location_name,
  COUNT(DISTINCT sku) AS unique_skus,
  SUM(available_qty) AS total_available,
  SUM(committed_qty) AS total_committed,
  ROUND(SUM(unit_cost * available_qty), 2) AS total_inventory_value,
  SUM(CASE WHEN available_qty <= 0 THEN 1 ELSE 0 END) AS out_of_stock_skus,
  SUM(CASE WHEN available_qty BETWEEN 1 AND 5 THEN 1 ELSE 0 END) AS low_stock_skus,
  ROUND(
    SAFE_DIVIDE(SUM(CASE WHEN available_qty <= 0 THEN 1 ELSE 0 END) * 100.0, COUNT(DISTINCT sku)),
    1
  ) AS stockout_rate_pct
FROM
  location_inventory
GROUP BY
  location_name
ORDER BY
  total_inventory_value DESC

Sample Result

location_name	unique_skus	total_available	total_committed	total_inventory_value	out_of_stock_skus	low_stock_skus	stockout_rate_pct
Main Warehouse - NYC	248	12,450	892	187,250.00	8	14	3.2
West Coast Fulfillment - LA	195	8,320	645	124,800.00	12	22	6.2
Pop-Up Store - Miami	86	1,240	78	18,600.00	5	18	5.8

Location Inventory Share:

Location	Inventory Value Share	Available Units Share	Stockout Rate
Main Warehouse - NYC	56.6%	56.5%	3.2%
West Coast Fulfillment - LA	37.7%	37.8%	6.2%
Pop-Up Store - Miami	5.6%	5.6%	5.8%

The West Coast Fulfillment location has the highest stockout rate at 6.2%. Consider redistributing stock from the Main Warehouse or increasing replenishment frequency for this location.

4. Inventory Turnover & Dead Stock Analysis

Identify products that aren’t selling (dead stock) versus those with healthy turnover, helping you make markdown, promotion, or discontinuation decisions.

Business Value:

• Free up capital tied in dead stock through markdowns
• Identify candidates for clearance sales or bundles
• Optimize purchasing decisions based on actual turnover rates

SQL query

AWS

WITH product_sales_90d AS (
  SELECT
    li.variant.product.id AS product_id,
    li.variant.product.title AS product_title,
    li.sku,
    SUM(li.quantity) AS units_sold_90d,
    SUM(CAST(li.discountedTotalSet.shopMoney.amount AS DOUBLE)) AS revenue_90d,
    MIN(o.processedAt) AS first_sale,
    MAX(o.processedAt) AS last_sale
  FROM
    nekt_raw.shopify_orders o
    CROSS JOIN UNNEST(o.lineItems) AS t(li)
  WHERE
    o.displayFinancialStatus IN ('PAID', 'PARTIALLY_REFUNDED')
    AND o.test = false
    AND o.processedAt >= CURRENT_DATE - INTERVAL '90' DAY
  GROUP BY
    li.variant.product.id,
    li.variant.product.title,
    li.sku
),
current_stock AS (
  SELECT
    ii.variant.product.id AS product_id,
    ii.sku,
    SUM(
      CASE WHEN q.name = 'available' THEN q.quantity ELSE 0 END
    ) AS available_qty,
    CAST(ii.unitCost.amount AS DOUBLE) AS unit_cost
  FROM
    nekt_raw.shopify_inventory_items ii
    CROSS JOIN UNNEST(ii.inventoryLevels) AS t(il)
    CROSS JOIN UNNEST(il.quantities) AS t2(q)
  WHERE
    ii.tracked = true
  GROUP BY
    ii.variant.product.id,
    ii.sku,
    ii.unitCost.amount
)
SELECT
  COALESCE(cs.product_id, ps.product_id) AS product_id,
  COALESCE(ps.product_title, 'Unknown') AS product_title,
  COALESCE(cs.sku, ps.sku) AS sku,
  COALESCE(ps.units_sold_90d, 0) AS units_sold_90d,
  ROUND(COALESCE(ps.revenue_90d, 0), 2) AS revenue_90d,
  cs.available_qty,
  ROUND(cs.unit_cost * cs.available_qty, 2) AS stock_value_at_cost,
  CASE
    WHEN COALESCE(ps.units_sold_90d, 0) = 0 AND cs.available_qty > 0 THEN 'DEAD_STOCK'
    WHEN COALESCE(ps.units_sold_90d, 0) > 0 AND cs.available_qty > 0
      THEN ROUND(ps.units_sold_90d * 4.0 / cs.available_qty, 2)
    ELSE NULL
  END AS annual_turnover_rate,
  CASE
    WHEN COALESCE(ps.units_sold_90d, 0) = 0 AND cs.available_qty > 0 THEN 'Dead Stock'
    WHEN COALESCE(ps.units_sold_90d, 0) > 0 AND cs.available_qty > 0
      AND (ps.units_sold_90d * 4.0 / cs.available_qty) < 2 THEN 'Slow Mover'
    WHEN COALESCE(ps.units_sold_90d, 0) > 0 AND cs.available_qty > 0
      AND (ps.units_sold_90d * 4.0 / cs.available_qty) >= 2
      AND (ps.units_sold_90d * 4.0 / cs.available_qty) < 6 THEN 'Moderate'
    WHEN COALESCE(ps.units_sold_90d, 0) > 0 AND cs.available_qty > 0
      AND (ps.units_sold_90d * 4.0 / cs.available_qty) >= 6 THEN 'Fast Mover'
    ELSE 'No Stock'
  END AS turnover_category
FROM
  current_stock cs
  LEFT JOIN product_sales_90d ps ON cs.product_id = ps.product_id AND cs.sku = ps.sku
WHERE
  cs.available_qty > 0
ORDER BY
  annual_turnover_rate ASC NULLS FIRST

GCP

WITH product_sales_90d AS (
  SELECT
    li.variant.product.id AS product_id,
    li.variant.product.title AS product_title,
    li.sku,
    SUM(li.quantity) AS units_sold_90d,
    SUM(CAST(li.discountedTotalSet.shopMoney.amount AS FLOAT64)) AS revenue_90d,
    MIN(o.processedAt) AS first_sale,
    MAX(o.processedAt) AS last_sale
  FROM
    `nekt_raw.shopify_orders` o,
    UNNEST(o.lineItems) AS li
  WHERE
    o.displayFinancialStatus IN ('PAID', 'PARTIALLY_REFUNDED')
    AND o.test = false
    AND DATE(o.processedAt) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  GROUP BY
    li.variant.product.id,
    li.variant.product.title,
    li.sku
),
current_stock AS (
  SELECT
    ii.variant.product.id AS product_id,
    ii.sku,
    SUM(
      CASE WHEN q.name = 'available' THEN q.quantity ELSE 0 END
    ) AS available_qty,
    CAST(ii.unitCost.amount AS FLOAT64) AS unit_cost
  FROM
    `nekt_raw.shopify_inventory_items` ii,
    UNNEST(ii.inventoryLevels) AS il,
    UNNEST(il.quantities) AS q
  WHERE
    ii.tracked = true
  GROUP BY
    ii.variant.product.id,
    ii.sku,
    ii.unitCost.amount
)
SELECT
  COALESCE(cs.product_id, ps.product_id) AS product_id,
  COALESCE(ps.product_title, 'Unknown') AS product_title,
  COALESCE(cs.sku, ps.sku) AS sku,
  COALESCE(ps.units_sold_90d, 0) AS units_sold_90d,
  ROUND(COALESCE(ps.revenue_90d, 0), 2) AS revenue_90d,
  cs.available_qty,
  ROUND(cs.unit_cost * cs.available_qty, 2) AS stock_value_at_cost,
  CASE
    WHEN COALESCE(ps.units_sold_90d, 0) = 0 AND cs.available_qty > 0 THEN NULL
    WHEN COALESCE(ps.units_sold_90d, 0) > 0 AND cs.available_qty > 0
      THEN ROUND(ps.units_sold_90d * 4.0 / cs.available_qty, 2)
    ELSE NULL
  END AS annual_turnover_rate,
  CASE
    WHEN COALESCE(ps.units_sold_90d, 0) = 0 AND cs.available_qty > 0 THEN 'Dead Stock'
    WHEN COALESCE(ps.units_sold_90d, 0) > 0 AND cs.available_qty > 0
      AND SAFE_DIVIDE(ps.units_sold_90d * 4.0, cs.available_qty) < 2 THEN 'Slow Mover'
    WHEN COALESCE(ps.units_sold_90d, 0) > 0 AND cs.available_qty > 0
      AND SAFE_DIVIDE(ps.units_sold_90d * 4.0, cs.available_qty) >= 2
      AND SAFE_DIVIDE(ps.units_sold_90d * 4.0, cs.available_qty) < 6 THEN 'Moderate'
    WHEN COALESCE(ps.units_sold_90d, 0) > 0 AND cs.available_qty > 0
      AND SAFE_DIVIDE(ps.units_sold_90d * 4.0, cs.available_qty) >= 6 THEN 'Fast Mover'
    ELSE 'No Stock'
  END AS turnover_category
FROM
  current_stock cs
  LEFT JOIN product_sales_90d ps ON cs.product_id = ps.product_id AND cs.sku = ps.sku
WHERE
  cs.available_qty > 0
ORDER BY
  annual_turnover_rate ASC NULLS FIRST

Sample Result

product_title	sku	units_sold_90d	revenue_90d	available_qty	stock_value_at_cost	annual_turnover_rate	turnover_category
Vintage Scarf	VS-BLU	0	0.00	85	1,275.00		Dead Stock
Wool Gloves	WG-M	3	89.97	67	670.00	0.18	Slow Mover
Leather Belt	LB-L	28	839.72	15	224.85	7.47	Moderate
Classic White Tee	CWT-M	156	3,900.00	22	220.00	28.36	Fast Mover
Canvas Sneakers	CS-42	35	2,794.65	4	159.96	35.00	Fast Mover

Stock Value by Turnover Category:

Turnover Category	SKU Count	Stock Value ($)	% of Total Value	Recommended Action
Fast Movers (6x+/yr)	24	12,400.00	15%	Increase safety stock
Moderate (2-6x/yr)	48	24,800.00	30%	Maintain current levels
Slow Movers (< 2x/yr)	35	20,650.00	25%	Reduce reorder quantities
Dead Stock (0 sales)	18	24,750.00	30%	Markdown / bundle / discontinue

$24,750 in dead stock represents 30% of your total inventory value. Consider running clearance promotions, creating product bundles, or discontinuing these items to free up capital.

5. Abandoned Cart Recovery Intelligence

Analyze abandoned checkout data to understand lost revenue potential and identify high-value recovery opportunities.

Business Value:

• Quantify revenue lost from abandoned carts
• Identify the most abandoned products to address pricing or UX issues
• Prioritize recovery campaigns by cart value

SQL query

AWS

WITH abandoned_analysis AS (
  SELECT
    ac.id,
    ac.createdAt,
    CAST(ac.totalPriceSet.presentmentMoney.amount AS DOUBLE) AS cart_value,
    ac.totalPriceSet.presentmentMoney.currencyCode AS currency,
    ac.customer.email AS customer_email,
    ac.completedAt,
    CASE WHEN ac.completedAt IS NOT NULL THEN 'RECOVERED' ELSE 'ABANDONED' END AS status,
    CARDINALITY(ac.lineItems) AS item_count
  FROM
    nekt_raw.shopify_abandoned_checkouts ac
  WHERE
    ac.createdAt >= CURRENT_DATE - INTERVAL '30' DAY
)
SELECT
  DATE(createdAt) AS abandon_date,
  COUNT(*) AS total_abandoned,
  COUNT(CASE WHEN status = 'RECOVERED' THEN 1 END) AS recovered,
  ROUND(COUNT(CASE WHEN status = 'RECOVERED' THEN 1 END) * 100.0 / COUNT(*), 1) AS recovery_rate_pct,
  ROUND(SUM(cart_value), 2) AS total_cart_value,
  ROUND(SUM(CASE WHEN status = 'ABANDONED' THEN cart_value ELSE 0 END), 2) AS lost_revenue,
  ROUND(SUM(CASE WHEN status = 'RECOVERED' THEN cart_value ELSE 0 END), 2) AS recovered_revenue,
  ROUND(AVG(cart_value), 2) AS avg_cart_value,
  ROUND(AVG(item_count), 1) AS avg_items_per_cart
FROM
  abandoned_analysis
GROUP BY
  DATE(createdAt)
ORDER BY
  abandon_date DESC

GCP

WITH abandoned_analysis AS (
  SELECT
    ac.id,
    ac.createdAt,
    CAST(ac.totalPriceSet.presentmentMoney.amount AS FLOAT64) AS cart_value,
    ac.totalPriceSet.presentmentMoney.currencyCode AS currency,
    ac.customer.email AS customer_email,
    ac.completedAt,
    CASE WHEN ac.completedAt IS NOT NULL THEN 'RECOVERED' ELSE 'ABANDONED' END AS status,
    ARRAY_LENGTH(ac.lineItems) AS item_count
  FROM
    `nekt_raw.shopify_abandoned_checkouts` ac
  WHERE
    DATE(ac.createdAt) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
)
SELECT
  DATE(createdAt) AS abandon_date,
  COUNT(*) AS total_abandoned,
  COUNTIF(status = 'RECOVERED') AS recovered,
  ROUND(COUNTIF(status = 'RECOVERED') * 100.0 / COUNT(*), 1) AS recovery_rate_pct,
  ROUND(SUM(cart_value), 2) AS total_cart_value,
  ROUND(SUM(CASE WHEN status = 'ABANDONED' THEN cart_value ELSE 0 END), 2) AS lost_revenue,
  ROUND(SUM(CASE WHEN status = 'RECOVERED' THEN cart_value ELSE 0 END), 2) AS recovered_revenue,
  ROUND(AVG(cart_value), 2) AS avg_cart_value,
  ROUND(AVG(item_count), 1) AS avg_items_per_cart
FROM
  abandoned_analysis
GROUP BY
  DATE(createdAt)
ORDER BY
  abandon_date DESC

Sample Result

abandon_date	total_abandoned	recovered	recovery_rate_pct	total_cart_value	lost_revenue	recovered_revenue	avg_cart_value	avg_items_per_cart
2026-01-27	34	6	17.6	4,892.50	4,032.00	860.50	143.90	2.4
2026-01-26	41	8	19.5	5,740.80	4,618.30	1,122.50	140.02	2.1
2026-01-25	28	4	14.3	3,980.20	3,410.00	570.20	142.15	2.6
2026-01-24	52	11	21.2	7,280.00	5,726.40	1,553.60	140.00	2.3
2026-01-23	38	7	18.4	5,320.60	4,342.10	978.50	140.02	2.2

5-Day Abandonment Summary:

Metric	Value
Total Abandoned Carts	193
Total Cart Value	$27,214.10
Lost Revenue	$22,128.80
Recovered Revenue	$5,085.30
Average Recovery Rate	18.6%
Average Cart Value	$141.01

At the current 18.6% recovery rate, improving recovery by just 5 percentage points (to ~24%) would capture an additional $2,800+ per week in revenue. Consider optimizing abandoned cart email timing, offering targeted discounts, or implementing SMS recovery flows.

Implementation Notes

GraphQL API

• This connector uses the Shopify Admin GraphQL API for efficient data extraction
• All monetary values are returned as strings (e.g., "29.99") — cast them to numeric types in your queries
• The connector uses cursor-based pagination for optimal API performance

ShopifyQL Reports

• ShopifyQL reports use a newer API version (2026-01) than the standard GraphQL streams (2025-07)
• Reports always run as full table syncs (no incremental support)
• The schema is automatically discovered at extraction time by executing the query and reading column metadata
• If a query returns no data, schema discovery will fail — ensure your time range (SINCE / UNTIL) covers a period with data
• ShopifyQL supports querying the sales, orders, products, and customers data models — refer to the ShopifyQL reference for the full syntax and available fields

Incremental Sync

• Orders, Products, Inventory Items, Customers, and Collections support incremental sync using updatedAt
• Locations, Abandoned Checkouts, and ShopifyQL Reports use full table sync
• Use incremental sync for large stores to minimize API calls and extraction time

Nested Data Structures

• Line items, transactions, variants, metafields, and inventory levels are stored as nested arrays
• Use UNNEST (GCP) or CROSS JOIN UNNEST (AWS) to flatten these arrays in your queries
• The connector automatically flattens GraphQL connection structures (edges/node) into clean arrays

Inventory Quantities

• The inventoryLevels[].quantities[] array contains multiple quantity types per location:
  • available — Units available for sale
  • committed — Units reserved for unfulfilled orders
  • incoming — Units expected from incoming transfers
  • on_hand — Total physical units at the location
• Filter by name to get the quantity type you need

Price Sets

• Shopify provides monetary values in both shop currency (your store’s base currency) and presentment currency (the customer’s local currency)
• Use shopMoney for internal reporting and presentmentMoney for customer-facing analysis

API Scopes Required

The Shopify connector requires the following API scopes:

• read_orders — Orders and transactions
• read_products — Products, variants, and collections
• read_customers — Customer profiles
• read_inventory — Inventory items and levels
• read_locations — Store locations
• read_reports — ShopifyQL reports

Skills for agents

Download Shopify skills file

Shopify connector documentation as plain markdown, for use in AI agent contexts.