Reconcile Shopify Sales With Xero Using AI

Reconciling Shopify sales with Xero is the job most ecommerce finance teams dread. Shopify says you did £48,200 last month. Xero shows £43,700 landing in the bank. The gap might be refunds not yet processed, gateway fees sitting uncategorised, payouts still in the settlement window, or a genuine error. Without a way to query both systems together, you are back to exporting two CSVs and building a reconciliation spreadsheet that is out of date before you finish it.

This guide is for ecommerce owners and finance leads who run Shopify and Xero side by side and want to ask questions across both at once, in Claude, ChatGPT or Gemini. It covers why single-system reporting misses the important questions, how Tugger handles two connectors in one conversation, and three prompts you can copy: a revenue reconciliation, a refund and fee check by product, and a customer-level cross-check for B2B stores.

Why one system at a time is not enough

Query Shopify alone and you see orders, fulfilment and gross revenue. Query Xero alone and you see invoices, payments and bank transactions. Neither shows the relationship between the two, and that is where the useful questions live:

• Which Shopify orders from last month have no matching money in Xero yet?
• Which products look strong on the Shopify dashboard but lose a chunk to refunds and processing fees?
• Are refunds processed in Shopify but not yet showing against revenue in Xero?
• Which customers are ordering through Shopify while sitting on an overdue Xero invoice?

Each of those needs both datasets in the same place. That is the point of connecting both to one warehouse.

How Tugger puts Shopify and Xero in one conversation

Tugger syncs each system into a managed data warehouse on a schedule, and its built-in MCP server lets Claude, ChatGPT and Gemini query that warehouse directly. If MCP is new to you, our MCP server guide covers it in one page. The connection is read-only: the AI queries the warehouse, never Shopify or Xero themselves.

With both connectors active, the AI can run a query against Shopify, run another against Xero, and join the results on a shared field such as an order number in a Xero invoice Reference or a customer email address. It also reads the column descriptions, so it knows that Shopify's totalPriceSet is gross and totalRefundedSet has to come off it, that ShopifyOrderTransactionFees holds the gateway fees per transaction, and that Xero revenue uses SubTotal on ACCREC invoices with credit notes subtracted. Setup for each side is in how to connect Shopify to Claude and how to connect Xero to Claude.

The same Shopify orders and customers also answer the retention questions, which customers are drifting and which are worth a personal effort. That side is covered in Shopify customer retention analysis with AI.

One thing to settle first: how Shopify reaches Xero

Before any reconciliation prompt, be clear about how your Shopify sales get into Xero, because it changes what "matches" means.

Tell the AI which of these you use in the context of every prompt. It stops the AI matching per-order invoices that do not exist, or hunting for payout receipts in a file that books every order individually.

Prompt 1: The revenue reconciliation check

The question everyone asks first: do the Shopify numbers match the Xero numbers, and if not, why not?

Role: You are a financial analyst for an ecommerce business.

Context: Use the ShopifyOrders, ShopifyOrderTransactions, ShopifyOrderTransactionFees, XeroInvoices, XeroPayments and XeroBankTransactions data provided via Tugger MCP. Our store sells in GBP. Shopify sales reach Xero as daily payout summary invoices, and Shopify Payments settles to our bank in two to three business days. State clearly which system each figure comes from.

Task: For 1 June to 30 June, calculate from Shopify: gross sales (totalPriceSet), refunds (totalRefundedSet), payment processing fees (from ShopifyOrderTransactionFees) and net payments (netPaymentSet), excluding cancelled and test orders. From Xero, total the summary invoices dated in June and the RECEIVE bank transactions on the Shopify payout account in June. Reconcile the two sides and attribute the difference to settlement timing, refunds, fees or unmatched items.

Output: One table with Shopify gross, refunds, fees and net payments; Xero invoiced and Xero received; and the difference. Below it, the difference broken down by cause. Flag any individual Shopify order over £500 with no corresponding money in Xero.

What you get. A reconciliation summary with the gap explained by cause rather than a single unexplained number. Most of the time the first run tells you the gap is settlement timing. That is reassuring but not yet useful, which is where the next step comes in.

When the first answer is not enough

Suppose the first pass comes back with "difference £4,210, likely 31 orders inside the gateway settlement window". You still do not know which orders, or whether any have been sitting there longer than they should.

Role: You are a financial analyst for an ecommerce business.

Context: Same data and booking method as before.

Task: Identify every Shopify order placed in June where a successful capture exists in ShopifyOrderTransactions but no corresponding Xero receipt or payment has appeared after five or more business days. For each, show the order name, order date, order value, customer name and business days since the order.

Output: A table sorted by days outstanding, longest first. Flag anything over ten business days as high priority.

Why the refinement works. The five-day threshold turns "timing differences" into a list of specific anomalies, and the ten-day flag gives you a tier to act on. This is the pattern for cross-connector work: the first prompt finds the problem, the second locates it precisely enough to fix.

Prompt 2: Refund and fee drag by product

The Shopify dashboard shows gross revenue by product. It does not show what each product costs you in refunds and payment processing before the money reaches the books. Both are in Shopify's data, and the AI can put them side by side.

Role: You are an ecommerce performance analyst.

Context: Use the ShopifyOrderLineItems, ShopifyProducts, ShopifyRefundLineItems, ShopifyOrderTransactions and ShopifyOrderTransactionFees data provided via Tugger MCP.

Task: For the last 90 days, list the top ten products by gross line revenue. For each, calculate refunded value (ShopifyRefundLineItems), the share of orders containing that product that had a refund, and an apportioned processing fee (each order's fees split across its line items by value). Show revenue after refunds and fees. Flag any product where more than 8% of orders were refunded.

Output: A table with Product, Gross Revenue, Refunds, Fees, Net After Refunds and Fees, Refund Rate and a flag column. Finish with two sentences on which products look materially weaker after refunds than the dashboard suggests.

Why it matters. Run this before a restocking decision. A product that tops the Shopify sales report but carries a 15% refund rate has a very different margin from the one the order dashboard implies. If you book per-order invoices in Xero, add XeroInvoices and XeroCreditNotes to the context and ask the AI to confirm the Xero side agrees.

Prompt 3: Customers across both systems

If you sell B2B through Xero invoicing alongside a Shopify storefront, some of your most active Shopify customers may also be your slowest payers in Xero. This prompt finds the overlap.

Role: You are an accounts receivable analyst.

Context: Use the ShopifyCustomers, ShopifyOrders, XeroContacts, XeroInvoices and XeroPayments data provided via Tugger MCP. Match customers across systems on email address (ShopifyCustomers.email to XeroContacts.EmailAddress), and fall back to company name where the email differs.

Task: Find customers with two or more Shopify orders in the last 60 days who also have an ACCREC invoice in Xero with Status AUTHORISED and AmountDue above zero more than 30 days past DueDate. For each, show Shopify order value for the period, overdue Xero balance, oldest overdue invoice date and most recent Shopify order date.

Output: A list sorted by overdue balance, highest first, with a recommended action per row: payment reminder, or review credit terms before the next order.

Why it matters. This is the cross-reference a finance team member would otherwise do by hand between two tabs, and it prevents the awkward moment of sending a discount code to a customer who owes you two thousand pounds.

What to do with the answers

Run the reconciliation weekly rather than monthly. A £4,000 gap found after four days is a different problem from one found after thirty. Run the refund and fee check before placing stock orders. Check the overdue customer list before any marketing send.

Once the prompts settle down, put your booking method, payout account name, fee treatment and thresholds in a short instruction file and load it into a Claude Project or a ChatGPT custom GPT. Then anyone on the team can ask "run the Shopify to Xero reconciliation" and get the same answer, built the same way.

Related guides

• How to connect Shopify to Claude
• Shopify customer retention analysis with AI
• How to connect Xero to Claude
• Consolidated reporting across multiple Xero organisations with AI
• Why Claude needs a data warehouse
• What is an MCP server?

Get started

Connect Shopify and Xero to Tugger, open Claude, ChatGPT or Gemini, and run the reconciliation prompt against last month. Tugger is UK-based and ISO 27001:2022 certified, and it connects 70+ systems including Xero, HubSpot, Simpro and Harvest, no code, no faff.

Try Tugger free for 10 days or book a demo.