How to Reconcile Daily Order Counts Between Shopify and NetSuite

Build a scheduled Spojit workflow that counts yesterday's Shopify orders, counts the matching NetSuite sales orders, and posts an AI-summarized discrepancy report to a Slack channel every morning.

What This Integration Does

When your storefront and your ERP are kept in sync by a separate process, orders can silently fall out of step: a Shopify order that never became a NetSuite sales order, a duplicate, or a timing gap around midnight. This Spojit workflow checks the two systems against each other once a day and tells you, in plain language, whether yesterday balanced. Instead of opening two admin panels and counting by hand, your finance and operations team gets a short Slack message that says how many orders each side recorded, whether the totals match, and which order references look suspicious.

The workflow runs on a Schedule trigger early each morning for the previous calendar day. It pulls the Shopify order list for that window, runs a SuiteQL count of NetSuite sales orders created in the same window, then hands both result sets to an Agent-mode Connector node to compare them and write the summary. The final summary is delivered with the Slack send-message tool. The workflow keeps no state of its own: each run is a fresh snapshot of one day, so re-running it for the same date simply produces the same report again, which is safe to do while you tune the logic.

Prerequisites

A Shopify connection with read access to orders. See the Shopify connector article for setup.
A NetSuite connection authorized to run SuiteQL queries and read sales orders. See the NetSuite connector article.
A Slack connection that can post to your target channel, plus the channel ID or name. See the Slack connector article.
Agreement on the reconciliation window and timezone (for example, the previous day in Australia/Sydney), and a shared field your two systems use to match orders (commonly the Shopify order name or number stored on the NetSuite sales order).

Step 1: Add a Schedule trigger for the daily run

Start a new workflow and add a Trigger node, then set its type to Schedule. Enter a 5-field cron expression and an IANA timezone so the run fires after the previous day has fully closed. For example, 30 6 * * * with timezone Australia/Sydney runs at 6:30 AM local time every day. The trigger output is { scheduledAt }, which you will use to derive the date window. A single Schedule trigger can hold more than one schedule if you later want the same report at additional times.

Step 2: Compute yesterday's date window

Add a Connector node in Direct mode using the date utility connector. Use the subtract tool to step back one day from {{ trigger.scheduledAt }}, then the start-of and end-of tools (or format) to produce the day's boundaries as ISO timestamps. Capture two values you will reuse downstream, for example {{ window.start }} and {{ window.end }}. Deriving the window once keeps both system queries pointed at the exact same range and avoids off-by-one days caused by timezone differences between Shopify, NetSuite, and the schedule.

Step 3: Count Shopify orders for the window

Add a Connector node in Direct mode for the Shopify connector and select the list-orders tool. Shopify uses its own search syntax in the query field, so build a query that filters by creation date for the window, for example:

created_at:>={{ window.start }} created_at:<={{ window.end }}

Store the result in a variable such as {{ shopifyOrders }}. The response is a paginated list of orders. If a high-volume day can exceed one page, page through with the connector's pagination until you have the full set, or narrow the query (for example by adding financial_status:paid) so you compare the same definition of "order" on both sides. Note the count and the order names or numbers for matching in the comparison step.

Step 4: Count NetSuite sales orders for the window

Add a Connector node in Direct mode for the NetSuite connector and select the run-suiteql tool. SuiteQL is SQL-like and lets you count and list in one query against the same window. For example:

SELECT tranid, trandate, foreignexternalid
FROM transaction
WHERE type = 'SalesOrd'
  AND trandate >= TO_DATE('{{ window.start }}', 'YYYY-MM-DD')
  AND trandate <= TO_DATE('{{ window.end }}', 'YYYY-MM-DD')

Store the result in {{ netsuiteOrders }}. Select whatever field on the sales order carries the Shopify reference in your account (often foreignexternalid or a custom column) so the comparison can match individual orders, not just totals. If you prefer the record endpoint over a query, the list-sales-orders tool returns sales order records you can filter the same way.

Step 5: Compare and summarize with an agent in Agent mode

Add a Connector node in Agent mode so the agent can reason over both result sets and write the report. Pass {{ shopifyOrders }} and {{ netsuiteOrders }} into the prompt, and define a Response Schema so the output is reliable structured JSON rather than free text. A schema with fields such as shopifyCount, netsuiteCount, matched, missingInNetsuite (an array of order references present in Shopify but not NetSuite), missingInShopify, and summary (a one-paragraph plain-text explanation) keeps the Slack message predictable. A prompt like the following works well:

Compare these two order sets for the same day.
Shopify orders: {{ shopifyOrders }}
NetSuite sales orders: {{ netsuiteOrders }}
Match by order reference. Report the count from each system,
whether they balance, and list any order references that appear
in one system but not the other. Write a short summary an
operations manager can read in ten seconds.

Store the structured result in {{ report }}. Agent mode costs AI credits, so keep the inputs to just the order references and counts rather than full order payloads. If you only ever need the two totals compared, you can instead do this deterministically with the math connector and a Condition node and skip AI entirely; use Agent mode when you want the per-order discrepancy reasoning and the human-readable narrative.

Step 6: Post the report to Slack

Add a Connector node in Direct mode for the Slack connector and select the send-message tool. Set the channel to your reconciliation channel and build the message text from the structured report, for example:

Daily order reconciliation for {{ window.start }}
Shopify: {{ report.shopifyCount }} | NetSuite: {{ report.netsuiteCount }}
Balanced: {{ report.matched }}
{{ report.summary }}

If you want to flag mismatches more loudly, place a Condition node before this step that checks whether {{ report.matched }} is false, and only on the false branch route to a second send-message call that mentions an on-call user or adds the discrepancy lists. On clean days you can post a brief "all balanced" line so the team knows the check ran.

Tips

Always derive the date window once (Step 2) and feed the same {{ window.start }} and {{ window.end }} into both connectors so a midnight order is not counted by one system and missed by the other.
Use Shopify search filters (such as financial_status:paid) and a matching SuiteQL WHERE clause so both sides count the same kind of order. Comparing "all orders" against "approved sales orders" will always look unbalanced.
Keep the Agent mode prompt fed with order references and counts only, not entire order objects, to control AI credit usage and stay well under input limits on busy days.
If the schedule must cover multiple stores or subsidiaries, run them as separate branches with a Parallel node and let the agent summarize each before posting, or split into one workflow per store called from a Subworkflow node.

Common Pitfalls

Timezone drift: the Schedule timezone, the Shopify created_at filter, and the SuiteQL trandate filter must all describe the same calendar day. A mismatch shifts the window and produces phantom discrepancies every run.
Pagination: list-orders returns paginated results. On high-volume days a single page undercounts Shopify, making NetSuite look ahead. Page through fully or constrain the query.
Reference field mismatch: if the Shopify order name is stored in a different NetSuite field than you query, every order looks "missing." Confirm which column holds the storefront reference before trusting the per-order lists.
Lag between systems: if your sync job runs hourly, late-evening orders may not reach NetSuite until after the report fires. Schedule the reconciliation after the sync's last daily pass, not immediately at midnight.

Testing

Before enabling the schedule, hard-code {{ window.start }} and {{ window.end }} to a recent low-volume day you can verify by hand, then run the workflow with the Run button and inspect each node's output in the execution log. Confirm the Shopify count, the SuiteQL count, and the structured report all line up with what you see in both admin panels, and check that the Slack message renders cleanly in your channel. Once a known-balanced day reports balanced and a day you know had a missing order reports the right reference, restore the dynamic date window in Step 2 and enable the workflow.