Field notes / Refunds and reconciliation

Diagnostic Refunds and reconciliation

Shopify orders whose transactions do not match the total

Every order has a running tally of money: what was captured, what was refunded, what is left. Those pieces should add up to the amount you actually received. When they do not, the order's ledger is quietly wrong, and it drags your revenue and refund reports off with it. The gap is small per order and easy to miss, which is exactly why it grows. Here is why the numbers drift and a small job that finds the orders that do not tie out and flags them for review.

Python and Node.js Admin GraphQL API Read only apart from a tag

A calculator sitting on top of a wooden table — Photo by FIN on Unsplash

The short answer

An order's money should tie out: successful captures minus successful refunds equals the amount received. Run a small Python or Node.js job that sums each recent order's successful transactions in cents, compares that to totalReceivedSet, and tags any order that does not match with a review tag using tagsAdd. The only change it makes is the tag, so it never moves money. Full code, tests, and a dry run guard are below.

The problem in plain words

Each payment action on an order is a transaction: a sale, a capture, a refund. Add up the successful charges, take away the successful refunds, and you should land on exactly the amount the order shows as received. That is the order's ledger, and it should always balance.

Sometimes it does not. A refund is recorded but the gateway leg failed, so the money never moved yet the refund still counts. A capture returns a partial result. An edit changes the total without a matching transaction. Now the sum of the transactions and the received amount disagree. The order looks normal, but its numbers no longer describe reality, and your reports inherit the error.

When the transactions and the received amount disagree, the order's ledger is wrong, and every report built on it drifts a little further from the truth.

Why it happens

A ledger drift is almost always a transaction that did not land the way the order thinks it did. Common causes:

A refund was created in Shopify but the gateway leg failed or is still pending, so the refund counts before the money moved.
A capture returned a partial or error result, leaving the captured amount short of what the order records.
An order edit changed the total without a matching payment or refund transaction.
A third party app posted or reversed a transaction in a way the order total did not follow.

Any one of these is small on its own. Across thousands of orders they add up to a revenue and refund report you cannot trust. The fix is not to guess which is which, it is to surface the orders that do not balance so a person can look. See the citations at the end for the docs on transactions and received amounts.

The key insight

You do not need to know why an order drifted to know that it did. The arithmetic is simple: captures minus refunds should equal received. Do that math in cents, on every order, and the ones that fail are your review list. Finding them is automatic, deciding what to do stays human.

The fix, as a flow

We do not move money or edit orders. We add a job that reads each recent order's transactions and its received amount, adds up the successful charges and refunds in minor units, and compares. If the two do not agree, we tag the order so it shows up in one filtered view for someone to reconcile.

Only orders whose transactions do not equal the received amount are tagged. Balanced orders are left exactly as they were.

Build it step by step

Get an Admin API access token

Create a custom app in your Shopify admin under Settings, Apps and sales channels, Develop apps. Give it the read_orders and write_orders scopes and install it to get an Admin API access token that starts with shpat_. Keep the token and the shop domain in environment variables, never in the file.

setup (shell)

pip install requests

export SHOPIFY_SHOP="yourstore.myshopify.com"
export SHOPIFY_ACCESS_TOKEN="shpat_..."
export LOOKBACK_DAYS="7"
export REVIEW_TAG="ledger-mismatch"
export DRY_RUN="true"   # start safe, change to false to tag

setup (shell)

// Node 18+ has fetch built in, no dependencies needed

export SHOPIFY_SHOP="yourstore.myshopify.com"
export SHOPIFY_ACCESS_TOKEN="shpat_..."
export LOOKBACK_DAYS="7"
export REVIEW_TAG="ledger-mismatch"
export DRY_RUN="true"   // start safe, change to false to tag

List recent orders with transactions and received amount

Ask for orders created in your lookback window, and for each read the tags, the totalReceivedSet, and the transactions with their kind, status, and amount. Everything the check needs is on the order, so one query per page is enough. We page through with a cursor.

step2.py

ORDERS_QUERY = """
query($cursor: String, $q: String!) {
  orders(first: 25, after: $cursor, query: $q) {
    pageInfo { hasNextPage endCursor }
    nodes {
      id name tags
      totalReceivedSet { shopMoney { amount currencyCode } }
      transactions(first: 30) {
        id kind status
        amountSet { shopMoney { amount currencyCode } }
      }
    }
  }
}"""

def recent_orders(lookback_days):
    q = f"created_at:>-{lookback_days}d"
    cursor = None
    while True:
        data = gql(ORDERS_QUERY, {"cursor": cursor, "q": q})["orders"]
        for node in data["nodes"]:
            yield node
        if not data["pageInfo"]["hasNextPage"]:
            return
        cursor = data["pageInfo"]["endCursor"]

step2.js

const ORDERS_QUERY = `
query($cursor: String, $q: String!) {
  orders(first: 25, after: $cursor, query: $q) {
    pageInfo { hasNextPage endCursor }
    nodes {
      id name tags
      totalReceivedSet { shopMoney { amount currencyCode } }
      transactions(first: 30) {
        id kind status
        amountSet { shopMoney { amount currencyCode } }
      }
    }
  }
}`;

async function* recentOrders(lookbackDays) {
  const q = `created_at:>-${lookbackDays}d`;
  let cursor = null;
  while (true) {
    const data = (await gql(ORDERS_QUERY, { cursor, q })).orders;
    for (const node of data.nodes) yield node;
    if (!data.pageInfo.hasNextPage) return;
    cursor = data.pageInfo.endCursor;
  }
}

Do the math, with pure functions

Work in minor units, cents, so there is no floating-point drift. One function sums the successful captures and subtracts the successful refunds. Another compares that net to the received amount and allows a one-cent tolerance for rounding. Both are pure, so every case is easy to test.

decide.py

CHARGE_KINDS = {"SALE", "CAPTURE"}

def to_cents(amount):
    return round(float(amount) * 100)

def net_captured_cents(transactions):
    total = 0
    for t in transactions or []:
        if t.get("status") != "SUCCESS":
            continue
        amount = to_cents(t["amountSet"]["shopMoney"]["amount"])
        if t.get("kind") in CHARGE_KINDS:
            total += amount
        elif t.get("kind") == "REFUND":
            total -= amount
    return total

def is_mismatch(order):
    received = to_cents((order.get("totalReceivedSet") or {}).get("shopMoney", {}).get("amount", "0"))
    return abs(net_captured_cents(order.get("transactions")) - received) > 1

decide.js

const CHARGE_KINDS = new Set(["SALE", "CAPTURE"]);

export function toCents(amount) {
  return Math.round(parseFloat(amount) * 100);
}

export function netCapturedCents(transactions) {
  let total = 0;
  for (const t of transactions || []) {
    if (t.status !== "SUCCESS") continue;
    const amount = toCents(t.amountSet.shopMoney.amount);
    if (CHARGE_KINDS.has(t.kind)) total += amount;
    else if (t.kind === "REFUND") total -= amount;
  }
  return total;
}

export function isMismatch(order) {
  const received = toCents(order.totalReceivedSet?.shopMoney?.amount ?? "0");
  return Math.abs(netCapturedCents(order.transactions) - received) > 1;
}

Tag the orders that do not tie out

When an order does not balance, and it is not already tagged, call tagsAdd with your review tag. That is the only change the job makes. It never refunds, captures, or edits, so a false positive costs nothing but a look. Read back userErrors and stop on them.

apply.py

TAGS_ADD = """
mutation($id: ID!, $tags: [String!]!) {
  tagsAdd(id: $id, tags: $tags) { node { id } userErrors { field message } }
}"""

def tag_for_review(order_id, review_tag):
    result = gql(TAGS_ADD, {"id": order_id, "tags": [review_tag]})["tagsAdd"]
    if result["userErrors"]:
        raise RuntimeError(result["userErrors"])

apply.js

const TAGS_ADD = `
mutation($id: ID!, $tags: [String!]!) {
  tagsAdd(id: $id, tags: $tags) { node { id } userErrors { field message } }
}`;

async function tagForReview(orderId, reviewTag) {
  const result = (await gql(TAGS_ADD, { id: orderId, tags: [reviewTag] })).tagsAdd;
  if (result.userErrors.length) throw new Error(JSON.stringify(result.userErrors));
}

Wire it together with a dry run guard

The loop ties every piece together and skips orders that already carry the tag, so re-running never double tags. On the first run, leave DRY_RUN on so the job only reports which orders do not tie out. Read the list, confirm they really are off, then switch it off. A daily audit keeps drift from piling up.

Run it safe

Start with DRY_RUN=true. The only write is a tag, so even a false positive is harmless. Set up a saved order view filtered by the review tag so your finance person can work the list and clear each order as they reconcile it.

The full code

Here is the complete audit in one file for each language. It reads settings from the environment, logs what it does, respects the dry run flag, works in cents to avoid rounding drift, and is safe to run again and again because it skips orders that already carry the tag.

View this code on GitHub Full runnable folder with tests in the shopify-fixes repo.

find_ledger_mismatch.py

"""Flag Shopify orders whose transactions do not add up to what was received.
Read only apart from a review tag. Run on a schedule.
"""
import os
import logging
import requests

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("find_ledger_mismatch")

SHOP = os.environ["SHOPIFY_SHOP"]
TOKEN = os.environ["SHOPIFY_ACCESS_TOKEN"]
API_VERSION = os.environ.get("SHOPIFY_API_VERSION", "2025-01")
ENDPOINT = f"https://{SHOP}/admin/api/{API_VERSION}/graphql.json"
LOOKBACK_DAYS = int(os.environ.get("LOOKBACK_DAYS", "7"))
REVIEW_TAG = os.environ.get("REVIEW_TAG", "ledger-mismatch")
DRY_RUN = os.environ.get("DRY_RUN", "true").lower() == "true"

CHARGE_KINDS = {"SALE", "CAPTURE"}

ORDERS_QUERY = """
query($cursor: String, $q: String!) {
  orders(first: 25, after: $cursor, query: $q) {
    pageInfo { hasNextPage endCursor }
    nodes {
      id name tags
      totalReceivedSet { shopMoney { amount currencyCode } }
      transactions(first: 30) {
        id kind status
        amountSet { shopMoney { amount currencyCode } }
      }
    }
  }
}"""

TAGS_ADD = """
mutation($id: ID!, $tags: [String!]!) {
  tagsAdd(id: $id, tags: $tags) { node { id } userErrors { field message } }
}"""


def gql(query, variables=None):
    r = requests.post(
        ENDPOINT,
        json={"query": query, "variables": variables or {}},
        headers={"X-Shopify-Access-Token": TOKEN, "Content-Type": "application/json"},
        timeout=30,
    )
    r.raise_for_status()
    body = r.json()
    if body.get("errors"):
        raise RuntimeError(body["errors"])
    return body["data"]


def to_cents(amount):
    return round(float(amount) * 100)


def net_captured_cents(transactions):
    total = 0
    for t in transactions or []:
        if t.get("status") != "SUCCESS":
            continue
        amount = to_cents(t["amountSet"]["shopMoney"]["amount"])
        if t.get("kind") in CHARGE_KINDS:
            total += amount
        elif t.get("kind") == "REFUND":
            total -= amount
    return total


def is_mismatch(order):
    received = to_cents((order.get("totalReceivedSet") or {}).get("shopMoney", {}).get("amount", "0"))
    return abs(net_captured_cents(order.get("transactions")) - received) > 1


def tag_for_review(order_id, review_tag):
    result = gql(TAGS_ADD, {"id": order_id, "tags": [review_tag]})["tagsAdd"]
    if result["userErrors"]:
        raise RuntimeError(result["userErrors"])


def recent_orders():
    q = f"created_at:>-{LOOKBACK_DAYS}d"
    cursor = None
    while True:
        data = gql(ORDERS_QUERY, {"cursor": cursor, "q": q})["orders"]
        for node in data["nodes"]:
            yield node
        if not data["pageInfo"]["hasNextPage"]:
            return
        cursor = data["pageInfo"]["endCursor"]


def run():
    flagged = 0
    for order in recent_orders():
        if not is_mismatch(order):
            continue
        if REVIEW_TAG in (order.get("tags") or []):
            continue
        log.warning("Order %s ledger does not tie out. %s",
                    order["name"], "would tag" if DRY_RUN else "tagging")
        if not DRY_RUN:
            tag_for_review(order["id"], REVIEW_TAG)
        flagged += 1
    log.info("Done. %d order(s) %s.", flagged, "to tag" if DRY_RUN else "tagged")


if __name__ == "__main__":
    run()

find-ledger-mismatch.js

/**
 * Flag Shopify orders whose transactions do not add up to what was received.
 * Read only apart from a review tag. Run on a schedule.
 */
import { pathToFileURL } from "node:url";

const SHOP = process.env.SHOPIFY_SHOP || "example.myshopify.com";
const TOKEN = process.env.SHOPIFY_ACCESS_TOKEN || "shpat_dummy";
const API_VERSION = process.env.SHOPIFY_API_VERSION || "2025-01";
const ENDPOINT = `https://${SHOP}/admin/api/${API_VERSION}/graphql.json`;
const LOOKBACK_DAYS = Number(process.env.LOOKBACK_DAYS || 7);
const REVIEW_TAG = process.env.REVIEW_TAG || "ledger-mismatch";
const DRY_RUN = (process.env.DRY_RUN || "true").toLowerCase() === "true";

const CHARGE_KINDS = new Set(["SALE", "CAPTURE"]);

export function toCents(amount) {
  return Math.round(parseFloat(amount) * 100);
}

export function netCapturedCents(transactions) {
  let total = 0;
  for (const t of transactions || []) {
    if (t.status !== "SUCCESS") continue;
    const amount = toCents(t.amountSet.shopMoney.amount);
    if (CHARGE_KINDS.has(t.kind)) total += amount;
    else if (t.kind === "REFUND") total -= amount;
  }
  return total;
}

export function isMismatch(order) {
  const received = toCents(order.totalReceivedSet?.shopMoney?.amount ?? "0");
  return Math.abs(netCapturedCents(order.transactions) - received) > 1;
}

async function gql(query, variables = {}) {
  const res = await fetch(ENDPOINT, {
    method: "POST",
    headers: { "X-Shopify-Access-Token": TOKEN, "Content-Type": "application/json" },
    body: JSON.stringify({ query, variables }),
  });
  if (!res.ok) throw new Error(`Shopify ${res.status}`);
  const body = await res.json();
  if (body.errors) throw new Error(JSON.stringify(body.errors));
  return body.data;
}

const ORDERS_QUERY = `
query($cursor: String, $q: String!) {
  orders(first: 25, after: $cursor, query: $q) {
    pageInfo { hasNextPage endCursor }
    nodes {
      id name tags
      totalReceivedSet { shopMoney { amount currencyCode } }
      transactions(first: 30) {
        id kind status
        amountSet { shopMoney { amount currencyCode } }
      }
    }
  }
}`;

const TAGS_ADD = `
mutation($id: ID!, $tags: [String!]!) {
  tagsAdd(id: $id, tags: $tags) { node { id } userErrors { field message } }
}`;

async function* recentOrders() {
  const q = `created_at:>-${LOOKBACK_DAYS}d`;
  let cursor = null;
  while (true) {
    const data = (await gql(ORDERS_QUERY, { cursor, q })).orders;
    for (const node of data.nodes) yield node;
    if (!data.pageInfo.hasNextPage) return;
    cursor = data.pageInfo.endCursor;
  }
}

async function tagForReview(orderId, reviewTag) {
  const result = (await gql(TAGS_ADD, { id: orderId, tags: [reviewTag] })).tagsAdd;
  if (result.userErrors.length) throw new Error(JSON.stringify(result.userErrors));
}

export async function run() {
  let flagged = 0;
  for await (const order of recentOrders()) {
    if (!isMismatch(order)) continue;
    if ((order.tags || []).includes(REVIEW_TAG)) continue;
    console.warn(`Order ${order.name} ledger does not tie out. ${DRY_RUN ? "would tag" : "tagging"}`);
    if (!DRY_RUN) await tagForReview(order.id, REVIEW_TAG);
    flagged++;
  }
  console.log(`Done. ${flagged} order(s) ${DRY_RUN ? "to tag" : "tagged"}.`);
}

if (import.meta.url === pathToFileURL(process.argv[1]).href) {
  run().catch((err) => { console.error(err); process.exit(1); });
}

Add a test

The arithmetic is the whole point, so it is the thing to test. Because the functions are pure and work in cents, the tests need no network and no store. They just feed in plain transactions and a received amount and check the result.

test_ledger_mismatch.py

from find_ledger_mismatch import net_captured_cents, is_mismatch, to_cents


def txn(amount, kind="SALE", status="SUCCESS"):
    return {"kind": kind, "status": status,
            "amountSet": {"shopMoney": {"amount": amount, "currencyCode": "USD"}}}


def order(received, txns):
    return {"totalReceivedSet": {"shopMoney": {"amount": received, "currencyCode": "USD"}},
            "transactions": txns, "tags": []}


def test_to_cents_rounds():
    assert to_cents("9.99") == 999


def test_net_captured_subtracts_refunds():
    assert net_captured_cents([txn("50.00"), txn("10.00", kind="REFUND")]) == 4000


def test_net_captured_ignores_failed():
    assert net_captured_cents([txn("50.00"), txn("50.00", status="FAILURE")]) == 5000


def test_no_mismatch_when_balanced():
    assert is_mismatch(order("40.00", [txn("50.00"), txn("10.00", kind="REFUND")])) is False


def test_mismatch_when_refund_not_reflected():
    assert is_mismatch(order("50.00", [txn("50.00"), txn("10.00", kind="REFUND")])) is True


def test_mismatch_when_capture_missing():
    assert is_mismatch(order("50.00", [])) is True

ledger.test.js

import { test } from "node:test";
import assert from "node:assert/strict";
import { netCapturedCents, isMismatch, toCents } from "./find-ledger-mismatch.js";

const txn = (amount, { kind = "SALE", status = "SUCCESS" } = {}) => ({
  kind, status, amountSet: { shopMoney: { amount, currencyCode: "USD" } },
});

const order = (received, transactions) => ({
  totalReceivedSet: { shopMoney: { amount: received, currencyCode: "USD" } },
  transactions, tags: [],
});

test("toCents rounds", () => {
  assert.equal(toCents("9.99"), 999);
});

test("netCaptured subtracts refunds", () => {
  assert.equal(netCapturedCents([txn("50.00"), txn("10.00", { kind: "REFUND" })]), 4000);
});

test("netCaptured ignores failed", () => {
  assert.equal(netCapturedCents([txn("50.00"), txn("50.00", { status: "FAILURE" })]), 5000);
});

test("no mismatch when balanced", () => {
  assert.equal(isMismatch(order("40.00", [txn("50.00"), txn("10.00", { kind: "REFUND" })])), false);
});

test("mismatch when refund not reflected", () => {
  assert.equal(isMismatch(order("50.00", [txn("50.00"), txn("10.00", { kind: "REFUND" })])), true);
});

test("mismatch when capture missing", () => {
  assert.equal(isMismatch(order("50.00", [])), true);
});

Case studies

Failed refund leg

The refunds that were recorded but never paid

A store issued refunds during a busy return season. For a handful, the Shopify refund was recorded but the gateway leg failed, so the order showed the money as returned while the customer never got it. Support only heard about it when customers chased their missing refunds.

The daily audit now catches these the moment the numbers stop tying out. Each order where captures minus refunds no longer matches the received amount gets tagged, and finance clears the list before customers have to ask.

Month end

The reconciliation that used to take days

A finance team spent the first days of every month hunting for the orders that made their Shopify totals disagree with the bank. It was slow, manual, and never quite complete.

They put this job on a daily schedule, and now the orders that do not balance are already tagged by month end. Reconciliation went from a hunt across thousands of orders to working a short, pre-built list.

What good looks like

After this runs on a schedule, an order whose money does not add up is caught within a day, not at month end. Your revenue and refund reports rest on ledgers that actually balance, and reconciliation becomes a short tagged list instead of a hunt. The arithmetic is automatic, the judgment stays with your finance team.

FAQ

Why do my Shopify order transactions not add up to the total?

The money on an order should tie out: successful captures minus successful refunds should equal the amount received. They drift apart when a refund transaction failed but was still counted, a gateway returned a partial result, or an order was edited. The mismatch means the order's ledger no longer reflects the real money.

How do I reconcile Shopify transactions with the order total?

Sum the successful SALE and CAPTURE transactions, subtract the successful REFUND transactions, and compare the result to the order's totalReceivedSet. Do the math in minor units, cents, to avoid rounding drift. If they do not match, the order needs review.

Is it safe to run this on a live store?

Yes. The only change it makes is adding a review tag to orders that do not tie out, so it never moves money or edits an order. Start in dry run mode to see the list before it tags anything.

Related field notes

Citations

On the problem:

Shopify Help Center: order transactions and the payment timeline. help.shopify.com/en/manual/orders/manage-orders
Shopify Help Center: refunds and how they appear on an order. help.shopify.com/en/manual/orders/refund-cancel-order
Shopify Community: order totals not matching the sum of transactions. community.shopify.com payments and shipping

On the solution:

Shopify Admin GraphQL: the OrderTransaction object, its kind, status, and amountSet. shopify.dev/docs/api/admin-graphql/latest/objects/OrderTransaction
Shopify Admin GraphQL: the Order object, including totalReceivedSet. shopify.dev/docs/api/admin-graphql/latest/objects/Order
Shopify Admin GraphQL: the tagsAdd mutation. shopify.dev/docs/api/admin-graphql/latest/mutations/tagsAdd

Stuck on a tricky one?

If you have a problem in Shopify orders, payments, subscriptions, inventory, or fulfillment that you would rather hand off, this is the kind of work I do. Message me and we can work through it together.

Contact me on LinkedIn

Did this balance your books?

If this found the orders that were throwing off your totals, you can buy me a coffee. It is the best way to keep these field notes free and growing.

Buy me a coffee on Ko-fi

Back to all Shopify field notes