Shopify and Zoho Books nightly sales reconciliation

Every night at 11pm, reconcile the day's Shopify sales against Zoho Books and tell me only what does not match.

Start by pulling the day's orders from Shopify with List Orders, covering the full calendar day in my store timezone. Include financial status so paid, pending, and refunded orders are all covered, and do not filter any of them out before comparing.

Then pull the matching Zoho Books records for the same date range with List Invoices and List Customer Payments.

Compare order by order. Matching is rarely clean, so do not assume a missing record too quickly. A Shopify order number can land in the invoice number, the reference number, or the notes, and some orders are invoiced under a slightly different customer name. Match on the order number first, then fall back to customer email or name together with amount and date before you call something missing.

Surface three specific problems. First, Shopify orders with no corresponding Zoho Books invoice. Second, invoices whose total does not match the order total once shipping, discounts, and tax are accounted for. Third, orders marked paid in Shopify with no customer payment recorded in Zoho Books. For refunded or partially refunded orders, compare against the net amount after the refund rather than skipping them.

Treat variances under 1 in the base currency as rounding and ignore them. If an order is in a different currency from the books, convert before comparing and say so in the issue description rather than guessing silently.

Append one row per discrepancy to my Google Sheets tracker with Append Values, using these columns in order: date, order number, customer, Shopify total, Zoho Books total, variance, issue type.

Then post a Slack summary with Send a Message giving the day's order count, gross sales, total variance, and the discrepancies that actually need a human, largest variance first. Keep it short enough to read on a phone. If everything reconciles, post a short all clear rather than staying silent, so silence is never ambiguous.

Do not create, edit, or delete anything in Shopify or Zoho Books. The only things you write are the spreadsheet rows and the Slack message.

What does this prompt do?

Pulls the full day of Shopify orders, including paid, pending and refunded ones, and lines them up against your Zoho Books records
Flags the three problems that quietly cost you money: orders with no invoice, invoices whose total does not match the order, and paid orders with no payment recorded
Adds one row per discrepancy to your Google Sheets tracker, so issues stay visible until somebody actually fixes them
Posts a nightly Slack summary with order count, gross sales and total variance, and a short all clear on the nights when everything matches

What do I need to use this?

A Shopify store with order history
A Zoho Books account for the organization you invoice from
A Google Sheets spreadsheet to use as the discrepancy tracker
A Slack workspace and a channel for the nightly summary

How can I customize it?

Change the run time or timezone so it fires after your last orders of the day land
Raise or lower the rounding tolerance if your tax or currency rounding regularly drifts by more than one unit
Pick which issue types are worth a Slack ping versus quietly logged to the sheet, and tag your bookkeeper on the big ones

FAQs

What counts as a discrepancy?

Three things: a Shopify order with no matching invoice in Zoho Books, an invoice whose total does not match the order total once shipping, discounts and tax are accounted for, and an order marked paid in Shopify with no customer payment recorded against it in Zoho Books.

Will it flag every tiny rounding difference?

No. Differences under one unit of your base currency are treated as rounding and ignored, so you are not chasing a stray cent. You can raise or lower that threshold if your numbers drift more than that.

What happens on a night when everything matches?

You still get a short all clear message in Slack. Silence could mean everything reconciled or that the check never ran, so it always reports either way.

Does this change anything in Shopify or Zoho Books?

No. It only reads from both systems. The only things it writes are the rows in your tracking spreadsheet and the summary message in Slack, so it is safe to run against live books.

What if the same order is invoiced under a slightly different name or number?

It matches on the order number first, then falls back to customer email or name together with the amount and date before calling anything missing, which avoids most false alarms from inconsistent naming.

Does it handle refunds and partial refunds?

Yes. Refunded and partially refunded orders are included in the comparison and checked against the net amount after the refund, rather than being skipped.

Related templates

Call overdue Xero customers with an AI collections agent

Every weekday at 10am, an AI voice agent phones your most overdue Xero accounts, logs what each customer promised, and reports back to finance in Slack.