> ## Documentation Index
> Fetch the complete documentation index at: https://docs.nekt.com/llms.txt
> Use this file to discover all available pages before exploring further.
# 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](https://app.nekt.ai/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.
**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. 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
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**
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_all_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
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.
Click the link below to open Google OAuth Playground pre-configured for Shopify:
[**Open Shopify OAuth Playground →**](https://developers.google.com/oauthplayground/#step1\&scopes=read_customers%2Cread_inventory%2Cread_locations%2Cread_orders%2Cread_all_orders%2Cread_products\&url=https%3A%2F%2F\&content_type=application%2Fjson\&http_method=GET\&useDefaultOauthCred=unchecked\&oauthEndpointSelect=Custom\&oauthAuthEndpointValue=https%3A%2F%2FSHOP_NAME.myshopify.com%2Fadmin%2Foauth%2Fauthorize\&oauthTokenEndpointValue=https%3A%2F%2FSHOP_NAME.myshopify.com%2Fadmin%2Foauth%2Faccess_token\&oauthClientId=SHOPIFY_CLIENT_ID\&oauthClientSecret=SHOPIFY_CLIENT_SECRET\&includeCredentials=checked\&accessTokenType=bearer\&autoRefreshToken=unchecked\&accessType=offline\&prompt=consent\&response_type=code\&wrapLines=on)
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**
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
```json theme={null}
{
"access_token": "shpat_xxxxxxxxxxxxxxxxxxxxx",
"scope": "read_customers,read_inventory,read_locations,read_orders,read_all_orders,read_products"
}
```
This token will remain valid indefinitely until you uninstall the app or rotate the client secret.
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`
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. 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
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](https://docs.nekt.com/runs/scheduling-and-triggers), 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](https://docs.nekt.com/get-started/core-concepts/types-of-sync#additional-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](https://app.nekt.ai/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](https://app.nekt.ai/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:
**Important:** The `read_all_orders` scope is required to access all historical orders. If not provided, only orders created in the last 60 days will be available.
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`
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`
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`
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`
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`
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`
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](https://shopify.dev/docs/api/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.
When configuring your Shopify source access (step 1), look for the **ShopifyQL Reports** section under the advanced settings.
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.
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.
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 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 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 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 |
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 |
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 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:
```sql theme={null}
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
```
```sql theme={null}
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.
```mermaid theme={null}
erDiagram
Orders {
string id PK
string name
datetime processedAt
string displayFinancialStatus
string displayFulfillmentStatus
string currencyCode
object totalPriceSet
object customer
array lineItems
array transactions
}
Products {
string id PK
string title
string vendor
string productType
string status
array variants
array options
}
Customers {
string id PK
string firstName
string lastName
string state
object amountSpent
string numberOfOrders
}
InventoryItems {
string id PK
string sku
boolean tracked
object unitCost
object variant
array inventoryLevels
}
Locations {
string id PK
string name
boolean active
object address
}
Collections {
string id PK
string title
string handle
array products
}
AbandonedCheckouts {
string id PK
string abandonedCheckoutUrl
object totalPriceSet
object customer
array lineItems
}
Orders }o--|| Customers : "customer.id"
Orders }o--o{ Products : "lineItems[].variant.product.id"
Products ||--o{ InventoryItems : "variants[].inventoryItem.id"
InventoryItems }o--o{ Locations : "inventoryLevels[].location.id"
Collections }o--o{ Products : "products[].id"
AbandonedCheckouts }o--|| Customers : "customer.id"
```
# 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](https://app.nekt.ai/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 theme={null}
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
```
```sql theme={null}
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
```
| 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 theme={null}
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
```
```sql theme={null}
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
```
| 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 theme={null}
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
```
```sql theme={null}
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
```
| 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 theme={null}
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
```
```sql theme={null}
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
```
| 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 theme={null}
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
```
```sql theme={null}
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
```
| 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](https://shopify.dev/docs/api/shopifyql) 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
Shopify connector documentation as plain markdown, for use in AI agent contexts.