/shopify-admin-variant-performance-report

name: shopify-admin-variant-performance-report
role: merchandising
description: "Rank every product variant by revenue, units sold, and refund rate, then cross-reference against current inventory to identify dead weight vs. top performers."
toolkit: shopify-admin, shopify-admin-execution
api_version: "2025-01"
graphql_operations:
  - orders:query
  - productVariants:query
status: stable
compatibility: Claude Code, Cursor, Codex, Gemini CLI

Purpose

Goes beyond product-level revenue by ranking every individual variant (size, color, option combination) on revenue, units sold, and refund rate, then joining against live inventory levels. Reveals which specific SKUs are driving the business and which are tying up capital on the shelf. Read-only — no mutations are executed.

Prerequisites

• Authenticated Shopify CLI session: `shopify auth login --store <domain>`
• API scopes: `read_orders`, `read_products` (validator-confirmed: orders query traverses variant→product graph)

Parameters

| Parameter | Type | Required | Default | Description | |-----------|------|----------|---------|-------------| | store | string | yes | — | Store domain (e.g., mystore.myshopify.com) | | format | string | no | human | Output format: `human` or `json` | | dry_run | bool | no | false | Preview operations without executing mutations | | date_range_start | string | yes | — | Start date in ISO 8601 (e.g., `2025-01-01`) | | date_range_end | string | yes | — | End date in ISO 8601 (e.g., `2025-01-31`) | | top_n | integer | no | 30 | Number of top and bottom variants to display | | sort_by | string | no | revenue | Ranking metric: `revenue`, `units`, or `refund_rate` | | min_units | integer | no | 1 | Exclude variants with fewer than N units sold in the period |

Workflow Steps

1. **OPERATION:** `orders` — query **Inputs:** `first: 250`, `query: "created_at:>='<date_range_start>' created_at:<='<date_range_end>'"`, pagination cursor; select `lineItems` with `variant { id, sku, title, selectedOptions }`, `quantity`, `originalTotalSet`; and `refunds.refundLineItems` with variant id and `subtotalSet` **Expected output:** All orders in range; paginate until `hasNextPage: false`; aggregate in-memory per `variant.id`: units sold, gross revenue, refunded units, refunded amount, refund rate

2. **OPERATION:** `productVariants` — query **Inputs:** List of variant IDs collected in step 1, `first: 250`, pagination cursor; select `id`, `sku`, `title`, `selectedOptions`, `inventoryQuantity`, `product { id, title }`, `price` **Expected output:** Current inventory levels and metadata for each sold variant; joined with step-1 aggregates; variants present in inventory but with zero sales are flagged as dead stock candidates

3. **In-memory computation:** Sort merged dataset by `sort_by` metric; compute revenue-per-inventory-unit ratio (net revenue ÷ inventory quantity) to highlight variants earning little relative to shelf space; split output into top-N performers and bottom-N by the same metric

GraphQL Operations

# orders:query (variant line items + refunds) — validated against api_version 2025-01
query OrdersForVariantPerformance($first: Int!, $after: String, $query: String) {
  orders(first: $first, after: $after, query: $query) {
    edges {
      node {
        id
        createdAt
        lineItems(first: 50) {
          edges {
            node {
              quantity
              originalTotalSet {
                shopMoney { amount currencyCode }
              }
              variant {
                id
                sku
                title
                selectedOptions { name value }
                product { id title }
              }
            }
          }
        }
        refunds {
          refundLineItems(first: 50) {
            edges {
              node {
                quantity
                subtotalSet {
                  shopMoney { amount currencyCode }
                }
                lineItem {
                  variant { id }
                }
              }
            }
          }
        }
      }
    }
    pageInfo {
      hasNextPage
      endCursor
    }
  }
}

# productVariants:query (inventory snapshot) — validated against api_version 2025-01
query VariantInventorySnapshot($first: Int!, $after: String, $query: String) {
  productVariants(first: $first, after: $after, query: $query) {
    edges {
      node {
        id
        sku
        title
        price
        selectedOptions { name value }
        inventoryQuantity
        product {
          id
          title
        }
      }
    }
    pageInfo {
      hasNextPage
      endCursor
    }
  }
}

Session Tracking

**Claude MUST emit the following output at each stage. This is mandatory.**

**On start**, emit:

╔══════════════════════════════════════════════╗
║  SKILL: variant-performance-report           ║
║  Store: <store domain>                       ║
║  Started: <YYYY-MM-DD HH:MM UTC>             ║
╚══════════════════════════════════════════════╝

**After each step**, emit:

[N/TOTAL] <QUERY|MUTATION>  <OperationName>
          → Params: <brief summary of key inputs>
          → Result: <count or outcome>

**On completion**, emit:

For `format: human` (default):

══════════════════════════════════════════════
OUTCOME SUMMARY
  Orders processed:     <n>
  Variants analysed:    <n>
  Date range:           <start> to <end>
  Sort by:              <metric>
  Errors:               0
  Output:               variant_performance_<date>.csv
══════════════════════════════════════════════

For `format: json`, emit:

{
  "skill": "variant-performance-report",
  "store": "<domain>",
  "started_at": "<ISO8601>",
  "completed_at": "<ISO8601>",
  "dry_run": false,
  "steps": [
    { "step": 1, "operation": "OrdersForVariantPerformance", "type": "query", "params_summary": "<date_range_start> to <date_range_end>", "result_summary": "<n> orders, <