Shopify Admin Payout Reconciliation

Purpose

Reconciles Shopify Payments payouts to the order transactions that contributed to them. For each payout, sums gross sales, refunds, adjustments, and fees, and compares the computed net to the payout's reported net amount. Discrepancies are flagged with a delta and the suspected cause. Read-only — no mutations.

Prerequisites

• Authenticated Shopify CLI session: shopify store auth --store <domain> --scopes read_shopify_payments_payouts,read_orders
• API scopes: read_shopify_payments_payouts, read_orders
• Store must use Shopify Payments. If the store uses only third-party gateways, this skill exits cleanly with a message.

Parameters

Safety

ℹ️ Read-only skill — no mutations are executed. Reconciliation output is informational; do not treat flagged discrepancies as confirmed errors before reviewing the underlying transactions in the Shopify admin.

Workflow Steps

1. OPERATION: shopifyPaymentsAccount — query Inputs: select payouts(first: 100, query: "issued_at:>='<NOW - days_back days>'") with id, issuedAt, status, net, gross, summary { chargesGross, chargesFee, refundsGross, refundsFee, adjustmentsGross, adjustmentsFee, retriedPayoutsGross, retriedPayoutsFee } Expected output: All payouts in the window. If account is null, exit — store does not use Shopify Payments.

2. For each payout, compute reconciliation expectation:

   Copy code
   expected_net = chargesGross - chargesFee
                - refundsGross + refundsFee   (refundsFee is normally negative / reversed)
                + adjustmentsGross - adjustmentsFee
                + retriedPayoutsGross - retriedPayoutsFee
   

   delta = reported_net - expected_net

3. OPERATION: orders — query (only for flagged payouts to drill down) Inputs: query: "transactions:'gateway:shopify_payments processed_at:>=<payout.issuedAt - 7d> processed_at:<=<payout.issuedAt + 1d>'", first: 250 Expected output: Candidate orders that may have contributed to the payout, used for an order-level cross-check on top discrepancies

4. Classify each payout:

   • ok if |delta| <= tolerance
   • discrepancy otherwise — record sign (positive: payout overpaid us; negative: payout underpaid)

5. Aggregate totals across the window: total payouts, total reconciled, total discrepancies, sum of absolute deltas

GraphQL Operations

graphqlCopy code
# shopifyPaymentsAccount:query — validated against api_version 2025-01
query PayoutReconciliation($payoutQuery: String!, $payoutAfter: String) {
  shopifyPaymentsAccount {
    id
    payoutSchedule { interval }
    payouts(first: 100, after: $payoutAfter, query: $payoutQuery) {
      edges {
        node {
          id
          issuedAt
          status
          net { amount currencyCode }
          gross { amount currencyCode }
          summary {
            chargesGross { amount currencyCode }
            chargesFee { amount currencyCode }
            refundsFeeGross { amount currencyCode }
            refundsFee { amount currencyCode }
            adjustmentsGross { amount currencyCode }
            adjustmentsFee { amount currencyCode }
            retriedPayoutsGross { amount currencyCode }
            retriedPayoutsFee { amount currencyCode }
          }
        }
      }
      pageInfo { hasNextPage endCursor }
    }
  }
}

graphqlCopy code
# orders:query — validated against api_version 2025-01
query OrdersFundingPayout($query: String!, $after: String) {
  orders(first: 250, after: $after, query: $query) {
    edges {
      node {
        id
        name
        processedAt
        totalPriceSet { shopMoney { amount currencyCode } }
        totalReceivedSet { shopMoney { amount currencyCode } }
        transactions {
          id
          gateway
          kind
          status
          processedAt
          amountSet { shopMoney { amount currencyCode } }
        }
      }
    }
    pageInfo { hasNextPage endCursor }
  }
}

Session Tracking

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

On start, emit:

Copy code
╔══════════════════════════════════════════════╗
║  SKILL: Payout Reconciliation                ║
║  Store: <store domain>                       ║
║  Started: <YYYY-MM-DD HH:MM UTC>             ║
╚══════════════════════════════════════════════╝

After each step, emit:

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

On completion, emit:

For format: human (default):

Copy code
══════════════════════════════════════════════
PAYOUT RECONCILIATION  (<days_back> days)
  Payouts in window:    <n>
  Reconciled (ok):      <n>
  Discrepancies:        <n>   ⚠️
  Sum of abs deltas:    $<amount>

  Top discrepancies:
    Payout <date>  Reported: $<n>  Expected: $<n>  Δ: $<n>

  Output: payout_reconciliation_<date>.csv
══════════════════════════════════════════════

For format: json, emit:

jsonCopy code
{
  "skill": "payout-reconciliation",
  "store": "<domain>",
  "period_days": 30,
  "payouts_count": 0,
  "reconciled_count": 0,
  "discrepancy_count": 0,
  "sum_abs_delta": 0,
  "currency": "USD",
  "output_file": "payout_reconciliation_<date>.csv"
}

Output Format

CSV file payout_reconciliation_<YYYY-MM-DD>.csv with columns: payout_id, issued_at, status, reported_net, expected_net, delta, currency, charges_gross, charges_fee, refunds_gross, refunds_fee, adjustments_gross, verdict

Error Handling

Best Practices

• Run weekly on the morning after a payout is scheduled — same-day reconciliation catches discrepancies while transactions are easy to investigate.
• For any flagged discrepancy, drill in: refunds processed inside the payout window often show up in the next payout's refundsGross, which can look like an underpayment if you forget to reconcile across windows.
• Use this skill alongside your accounting export — these numbers should match your bookkeeping software's deposit records to the cent.
• Tolerance > $0.01 should be used cautiously: anything above a few cents typically reflects a real fee or adjustment that deserves an explanation.
• This skill does not detect fraud; it detects accounting deltas. If you suspect fraud, escalate via the Shopify admin's payout detail view.