---
name: shopify-api
description: Interact with Desert Fox Golf's US Shopify store (desert-fox-golf) via the Admin REST API and ShopifyQL analytics queries. Covers products, variants, inventory, orders, collections, and metafields — including listing, searching, creating, updating, and bulk-editing — plus analytics questions answered via ShopifyQL (net sales, gross sales, top products, sales by channel/day, revenue trends, matching the Shopify admin GUI reports). ALWAYS use this skill whenever Eric mentions Shopify, products, variants, SKUs in a Shopify context, inventory levels, Shopify orders, collections (smart or custom), product tags, metafields, net sales, gross sales, Shopify revenue, sales by channel, or asks to push, pull, edit, look up, or analyze anything on the desertfoxgolf.com store. Use even if "Shopify" isn't said explicitly — any request to look up, modify, or analyze a product/variant/order/sales figure on the DFG storefront flows through this skill.
---

# Shopify API (Desert Fox Golf — US)

Direct access to the Desert Fox Golf US Shopify store via the Admin REST API.

## Store Configuration

- **Store name:** `desert-fox-golf`
- **Storefront:** `www.desertfoxgolf.com`
- **API version:** `2026-04` (required for ShopifyQL to match the admin GUI net sales report. Earlier versions like `2026-01` return slightly different numbers — see the `UNTIL` semantics note below). REST endpoints work on `2024-10` or any newer version.
- **Base URL:** `https://desert-fox-golf.myshopify.com/admin/api/2026-04`
- **Access token:** `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`
- **Auth header:** `X-Shopify-Access-Token: <token>`

> Token is hardcoded here per Eric's preference. If a call returns `401 Unauthorized`, the token has been rotated — re-read `/Users/server/DOC/doc-dev/.env` via DFG MCP `read_file` to get the current `SHOPIFY_ACCESS_TOKEN`, then update this skill.

## Core rules

- **Always use `curl` via `bash_tool`** for Shopify API calls — never `web_fetch` (caching issues).
- **Always pipe responses through `python3 -m json.tool`** (or `jq` if needed) for readable output.
- **Always check the `link` response header for pagination** when listing — Shopify uses cursor-based pagination, not page numbers (`page_info=...`).
- **REST Admin API for product/order/inventory CRUD.** For analytics questions ("how much revenue", "top products", "sales by channel/day"), use **ShopifyQL** via the GraphQL `shopifyqlQuery` endpoint instead — see the ShopifyQL section below. Reach for plain GraphQL (non-ShopifyQL) only for bulk operations (>250 items in one call) or endpoints REST doesn't expose; surface that to Eric first.
- **Be explicit about what you're about to mutate.** Before any `POST`, `PUT`, or `DELETE`, summarize the change and confirm with Eric. Do not bulk-edit silently.

## Quick verification

Before doing anything else in a fresh session, confirm the token works:

```bash
curl -s -H "X-Shopify-Access-Token: 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" \
  "https://desert-fox-golf.myshopify.com/admin/api/2024-10/shop.json" \
  | python3 -m json.tool | head -20
```

If that returns `Desert Fox Golf` shop data, you're good.

## Endpoint reference

### Products

| Action | Method | Path |
|---|---|---|
| List products | GET | `/products.json?limit=250` |
| Search by title | GET | `/products.json?title=<query>` |
| Get product | GET | `/products/{id}.json` |
| Get product count | GET | `/products/count.json` |
| Create product | POST | `/products.json` |
| Update product | PUT | `/products/{id}.json` |
| Delete product | DELETE | `/products/{id}.json` |

**List with filters:**
- `?vendor=Desert Fox Golf`
- `?product_type=Phone Caddy`
- `?status=active` (also: `archived`, `draft`)
- `?collection_id={id}`
- `?created_at_min=2026-01-01T00:00:00-07:00`
- `?fields=id,title,handle,variants` — limit response payload
- `?limit=250` — max per page; paginate via `link` header

**Find by SKU:** Shopify doesn't filter products by SKU directly. Use the variants search:
```bash
curl -s -H "X-Shopify-Access-Token: $TOKEN" \
  "https://desert-fox-golf.myshopify.com/admin/api/2024-10/variants.json?sku=MC001H"
```
Or pull `id,title,variants` for all products and filter locally.

### Variants

| Action | Method | Path |
|---|---|---|
| List a product's variants | GET | `/products/{product_id}/variants.json` |
| Get variant | GET | `/variants/{id}.json` |
| Search variant by SKU | GET | `/variants.json?sku=<sku>` |
| Create variant | POST | `/products/{product_id}/variants.json` |
| Update variant | PUT | `/variants/{id}.json` |
| Delete variant | DELETE | `/products/{product_id}/variants/{id}.json` |

Variant fields commonly edited: `price`, `compare_at_price`, `sku`, `barcode`, `inventory_policy`, `inventory_management`, `weight`, `weight_unit`, `option1/option2/option3`.

### Inventory

Inventory is tracked per `inventory_item_id` × `location_id`, not on the variant directly. Variant has `inventory_item_id`; you set levels via the inventory_levels endpoint.

| Action | Method | Path |
|---|---|---|
| List locations | GET | `/locations.json` |
| Get inventory levels | GET | `/inventory_levels.json?inventory_item_ids=X,Y&location_ids=Z` |
| Set absolute level | POST | `/inventory_levels/set.json` body `{inventory_item_id, location_id, available}` |
| Adjust by delta | POST | `/inventory_levels/adjust.json` body `{inventory_item_id, location_id, available_adjustment}` |
| Connect item to location | POST | `/inventory_levels/connect.json` |

### Orders

| Action | Method | Path |
|---|---|---|
| List orders | GET | `/orders.json?status=any&limit=250` |
| Get order | GET | `/orders/{id}.json` |
| Order count | GET | `/orders/count.json` |
| Update order | PUT | `/orders/{id}.json` |
| Cancel order | POST | `/orders/{id}/cancel.json` |
| Close order | POST | `/orders/{id}/close.json` |

**Filters:**
- `?status=any|open|closed|cancelled` (default: `open`)
- `?financial_status=paid|pending|refunded|...`
- `?fulfillment_status=shipped|unshipped|partial|any`
- `?created_at_min=...`, `?created_at_max=...`
- `?name=#1234` — order number
- `?fields=id,name,total_price,line_items`

### Collections

Two types: **custom** (manually curated) and **smart** (rule-based).

| Action | Method | Path |
|---|---|---|
| List custom collections | GET | `/custom_collections.json` |
| List smart collections | GET | `/smart_collections.json` |
| Get collection | GET | `/collections/{id}.json` |
| Products in a collection | GET | `/collections/{id}/products.json` |
| Add product to custom collection | POST | `/collects.json` body `{collect: {product_id, collection_id}}` |
| Remove from custom collection | DELETE | `/collects/{collect_id}.json` |

> Use `/custom_collections.json` and `/smart_collections.json` separately — `/collections.json` is read-only and merges both.

### Metafields

Metafields live on products, variants, customers, orders, and the shop itself.

| Action | Method | Path |
|---|---|---|
| List product metafields | GET | `/products/{id}/metafields.json` |
| List variant metafields | GET | `/variants/{id}/metafields.json` |
| Create metafield | POST | `/products/{id}/metafields.json` |
| Update metafield | PUT | `/metafields/{id}.json` |
| Delete metafield | DELETE | `/metafields/{id}.json` |

Body shape:
```json
{
  "metafield": {
    "namespace": "custom",
    "key": "ncaa_school",
    "value": "Iowa",
    "type": "single_line_text_field"
  }
}
```

Common types: `single_line_text_field`, `multi_line_text_field`, `number_integer`, `number_decimal`, `boolean`, `json`, `url`, `date`.

## Pagination

Shopify uses cursor pagination. The `link` response header has `<...page_info=ABC>; rel="next"`.

```bash
curl -sD - -H "X-Shopify-Access-Token: $TOKEN" \
  "https://desert-fox-golf.myshopify.com/admin/api/2024-10/products.json?limit=250" \
  -o /tmp/products_page1.json

# Extract next page_info from headers, then:
curl -s -H "X-Shopify-Access-Token: $TOKEN" \
  "https://desert-fox-golf.myshopify.com/admin/api/2024-10/products.json?limit=250&page_info=<ABC>"
```

Once `page_info` is in the URL, do NOT re-pass other filters — they're encoded in the cursor.

## Rate limits

REST Admin API is leaky-bucket: 40 requests filling at 2/sec (or 80 / 4/sec on Shopify Plus — DFG is on Plus, confirm via `/shop.json` → `plan_name`).

- Watch the `X-Shopify-Shop-Api-Call-Limit` response header (e.g., `32/40`).
- If you hit `429`, sleep 1–2s and retry.
- For bulk reads, `sleep 0.3` between calls is safe.

## Common workflows

### Find a product by SKU
```bash
TOKEN="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"
curl -s -H "X-Shopify-Access-Token: $TOKEN" \
  "https://desert-fox-golf.myshopify.com/admin/api/2024-10/variants.json?sku=MC001H" \
  | python3 -m json.tool
```
Then fetch the parent product via `product_id` from the variant.

### List all NCAA products (by tag or product_type)
```bash
curl -s -H "X-Shopify-Access-Token: $TOKEN" \
  "https://desert-fox-golf.myshopify.com/admin/api/2024-10/products.json?limit=250&fields=id,title,handle,tags,product_type" \
  | python3 -m json.tool
```

### Update price on a variant
```bash
curl -s -X PUT \
  -H "X-Shopify-Access-Token: $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"variant":{"id":VARIANT_ID,"price":"49.99"}}' \
  "https://desert-fox-golf.myshopify.com/admin/api/2024-10/variants/VARIANT_ID.json"
```

### Set inventory at the primary location
```bash
# 1. Get location IDs
curl -s -H "X-Shopify-Access-Token: $TOKEN" \
  "https://desert-fox-golf.myshopify.com/admin/api/2024-10/locations.json" \
  | python3 -m json.tool

# 2. Set level
curl -s -X POST \
  -H "X-Shopify-Access-Token: $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"inventory_item_id":INV_ITEM_ID,"location_id":LOC_ID,"available":50}' \
  "https://desert-fox-golf.myshopify.com/admin/api/2024-10/inventory_levels/set.json"
```

## ShopifyQL (Analytics Queries)

**For any "how much / how many / over time" question, default to ShopifyQL — not the Orders API.** ShopifyQL is Shopify's analytics query language, exposed via the GraphQL Admin API at `shopifyqlQuery`. It runs against the same analytics warehouse the admin GUI's reports use, which means **numbers match the GUI exactly** without having to reconstruct net sales from line items + refunds + adjustments.

### Endpoint

```bash
curl -s -X POST \
  -H "X-Shopify-Access-Token: $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "query { shopifyqlQuery(query: \"FROM sales SHOW net_sales GROUP BY day SINCE -30d ORDER BY day ASC\") { parseErrors tableData { rows columns { name dataType } } } }"
  }' \
  "https://desert-fox-golf.myshopify.com/admin/api/2026-04/graphql.json" \
  | python3 -m json.tool
```

- The 2026-01 schema replaced the old `TableResponse | ParseError` union with flat fields on `ShopifyqlQueryResponse`: `tableData` (object) and `parseErrors` (list of error strings — scalar, no subselection).
- `tableData.rows` is an array of row objects keyed by column name (was `rowData` in older versions); `columns` describes the columns in order.
- **Date literals are bare, not quoted.** Use `SINCE 2026-03-01`, NOT `SINCE '2026-03-01'` — quoted dates throw `Syntax input mismatch ... expecting {'+', '-', IDENTIFIER_, INTEGER_, DATE_}`.
- **Group with `GROUP BY`, not `BY`.** `SHOW net_sales BY day` throws `Syntax unwanted token - extraneous input 'BY'`. Correct: `SHOW net_sales GROUP BY day`. Same for any other grouping column (`GROUP BY product_title`, `GROUP BY sales_channel_name`, etc.).
- **`UNTIL` is INCLUSIVE of the date specified** (on API version `2026-04`). For a full calendar month, use the last day of that month — NOT the first day of the next month.
  - Full January: `SINCE 2026-01-01 UNTIL 2026-01-31`
  - Full April: `SINCE 2026-04-01 UNTIL 2026-04-30`
  - Full Q1: `SINCE 2026-01-01 UNTIL 2026-03-31`
  - **Verified May 19, 2026** against the Shopify admin GUI "Net sales by month" report: Jan = $79,826.24, Feb = $77,480.31, Mar = $129,506.08, Apr = $173,542.87 — all match to the penny using `UNTIL <last-day-of-month>` on API `2026-04`.
  - Using `UNTIL <first-day-of-next-month>` (e.g. `UNTIL 2026-02-01` for January) returns MORE than the GUI shows. The extra amount is the first day of the next UTC month, which the Shopify admin attributes to the prior month in store-local time (Phoenix, UTC-7). Don't do this.
  - `UNTIL today` is INCLUSIVE of today; safe for YTD and last-N-day windows.
- Always pipe through `python3 -m json.tool`.

### When to use ShopifyQL vs the Orders API

| Question | Best tool |
|---|---|
| "Net sales last month" | ShopifyQL |
| "Top 10 orders by net sales in Jan" | ShopifyQL |
| "Sales by school / by product / by channel" | ShopifyQL |
| "What's on order #74610?" (line items, address, tags) | Orders API |
| "What SKUs did wholesale customer X buy in Q1?" | Orders API (need line items) |
| "Update tags on these 50 orders" | Orders API (mutations) |
| "Refund detail with `order_adjustments`" | Orders API |
| "Compare wholesale vs retail revenue by month" | ShopifyQL |
| "All orders with the Magnetic Cigar Holder SKU" | Orders API |

**Common pattern: use both in sequence.** ShopifyQL identifies the interesting orders ("top 10 by revenue"), then a follow-up Orders API call pulls the detail on just those 10 IDs. Fast and precise.

### Useful ShopifyQL queries

**Copy-paste templates for common windows (verified May 19, 2026 against admin GUI):**

```
# Full calendar month (e.g. April 2026)
FROM sales SHOW gross_sales, discounts, returns, net_sales, total_sales, orders SINCE 2026-04-01 UNTIL 2026-04-30

# Full quarter (e.g. Q1 2026)
FROM sales SHOW gross_sales, discounts, returns, net_sales, total_sales, orders SINCE 2026-01-01 UNTIL 2026-03-31

# Year-to-date through today (inclusive)
FROM sales SHOW gross_sales, discounts, returns, net_sales, total_sales, orders SINCE 2026-01-01 UNTIL today

# Last 7 days (rolling, inclusive of today)
FROM sales SHOW gross_sales, discounts, returns, net_sales, total_sales, orders SINCE -7d
```

Net sales by day, last 30 days:
```
FROM sales SHOW net_sales GROUP BY day SINCE -30d ORDER BY day ASC
```

Net sales totals only (last 30 days):
```
FROM sales SHOW net_sales, gross_sales, discounts, returns, total_sales SINCE -30d UNTIL today
```

Top products by net sales, last 90 days:
```
FROM sales SHOW net_sales, gross_sales GROUP BY product_title SINCE -90d ORDER BY net_sales DESC LIMIT 25
```

Sales by channel (column is `sales_channel`, not `sales_channel_name`):
```
FROM sales SHOW net_sales GROUP BY sales_channel SINCE -30d ORDER BY net_sales DESC
```

Sales by sales channel + day (stacked time series):
```
FROM sales SHOW net_sales GROUP BY day, sales_channel SINCE -30d ORDER BY day ASC
```

Orders count by day:
```
FROM orders SHOW orders GROUP BY day SINCE -30d
```

Date windows: `-7d`, `-30d`, `-90d`, `-12m`, `today`, `yesterday`, or explicit bare dates like `2026-01-01` (no quotes). Use `SINCE` + `UNTIL` for fixed windows — `UNTIL` is INCLUSIVE of the date specified (full April = `UNTIL 2026-04-30`, NOT `UNTIL 2026-05-01`).

### Constraints to know

- **Read-only.** No mutations — for tag updates, price changes, etc., use the REST Admin API.
- **Limited fields.** Only columns Shopify exposes in the analytics warehouse. Line-item-level data, custom metafields not enabled for analytics, and refund-adjustment detail aren't queryable here.
- **Slight lag for very recent data.** Minutes to ~an hour. For something live ("did this order I just placed come through?") use the Orders API.
- **DOCdb is still its own thing.** When joining Shopify data with non-Shopify data (Knack leads, Amazon orders, inventory) or when independence from Shopify uptime matters, DOCdb stays the right answer.

## Net Sales Reconciliation

**The fastest way to match the Shopify admin's net sales report is ShopifyQL — `FROM sales SHOW net_sales` returns the same number the GUI shows, no reconstruction needed.** Use the formula below only when ShopifyQL won't do (e.g., joining against non-analytics fields, or the analytics warehouse is lagged and you need to-the-minute precision).

### Manual reconciliation formula

To match the GUI to the penny from the Orders API, three rules:

1. **For in-window orders, use `subtotal_price`** (the original net at order creation), **not `current_subtotal_price`.** Refunds are subtracted separately on their processed date — using `current_subtotal_price` would double-count them.
2. **Subtract `refund_line_items.subtotal`** on the refund's `processed_at` date (which may fall outside the order's `created_at` window — that's intentional, refunds book on their own date).
3. **Also subtract `refund.order_adjustments`** (skip kinds where `kind = 'shipping_refund'`). These are non-line-item refund amounts — e.g., the case where a discount-related mismatch on a return produces an `order_adjustment` that wouldn't otherwise be captured.

Formula:
```
net_sales(day) =
    sum(subtotal_price for orders created on day, not cancelled)
  - sum(refund_line_items.subtotal for refunds processed on day)
  - sum(refund.order_adjustments.amount for refunds processed on day where kind != 'shipping_refund')
```

**Verified May 19, 2026:** This formula matched the Shopify admin's 30-day net sales report to the penny ($219,669.37) for desert-fox-golf. Two days had small ($204.50 and $24.95) attribution differences vs the GUI's per-day breakdown, but they cancel each other out — totals match exactly.

### What the order fields actually mean

| Field | Meaning |
|---|---|
| `total_price` | Includes tax + shipping (too high for net sales) |
| `current_total_price` | Post-refund, still includes tax + shipping |
| `subtotal_price` | Original net at creation, excludes tax/shipping — **use this** |
| `current_subtotal_price` | Post-refund subtotal — **don't use for daily net sales; double-counts** |
| `refunds[].refund_line_items[].subtotal` | The merchandise portion of a refund |
| `refunds[].order_adjustments[]` | Non-line-item refund amounts (incl. shipping refunds — filter those out) |
| `refunds[].processed_at` | The date the refund books against — may differ from the original order date |

## DFG-specific notes

- **NCAA SKU pattern:** `PC%C-%` (the SKU column on products tagged for university lines). When searching, this is a useful filter to narrow to collegiate inventory.
- **Primary product lines on Shopify:** Magnetic Phone Caddy (Maggy, MC001H), Phone Caddy, Cigar Holder, Speaker, Can Cooler, Wing.
- **Cross-reference with DOCdb:** Shopify orders join to the DOCdb `shopify_orders` table on `id`. If Eric asks something that's faster to answer from DOCdb (like "all orders from school X over the last 90 days"), use `DFG MCP:docdb_query` instead of the Shopify API.
- **Mutation safety:** For bulk edits (e.g., updating tags on 50+ products), draft the change list, show Eric a sample, and confirm before iterating.