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

Parameter	Type	Required	Default	Description
store	string	yes	—	Store domain (e.g., mystore.myshopify.com)
days_back	integer	no	30	Lookback window covering payouts issued in this period
tolerance	number	no	0.01	Acceptable delta in store currency before flagging a discrepancy
format	string	no	human	Output format: human or json

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:

   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

# 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 }
    }
  }
}

# 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:

╔══════════════════════════════════════════════╗
║  SKILL: Payout Reconciliation                ║
║  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):

══════════════════════════════════════════════
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:

{
  "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

Error	Cause	Recovery
THROTTLED	API rate limit exceeded	Wait 2 seconds, retry up to 3 times
shopifyPaymentsAccount is null	Store does not use Shopify Payments	Exit cleanly; suggest reconciling via the third-party gateway dashboard
Currency mismatch within payout	Multi-currency store	Reconcile only payouts in the presentment currency that matches account currency; flag others as currency_mismatch
Payout still IN_TRANSIT	Not yet final	Skip — reconcile after status moves to PAID

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.