Shopify vs NetSuite inventory: 7 reasons the counts differ

#How stock moves between the two

Oracle's troubleshooting page for the connector sets out three facts that explain most differences:

• One direction. "Inventory syncs to the storefront, not from it." Nothing Shopify counts goes back to NetSuite as stock. Orders do, and they change NetSuite's stock once they post.
• Not instant. Most connectors support real-time quantity sync, but "if you don't use real-time sync, the sync runs every hour by default."
• Available, not on hand. When the connector syncs from NetSuite locations, it sends "the available quantity, not the quantity on hand": what isn't committed to orders.

On the way back, the connector posts Shopify POS orders to NetSuite as cash sales by default, or as sales orders when they need shipping. Online orders arrive as sales orders.

This post walks through Oracle's NetSuite Connector (the one set up at app.farapp.com). Other connectors between the two calculate and schedule differently, so check yours against its own documentation. For causes on the Shopify side alone, see the post on Shopify inventory not updating, and for the same questions with Business Central as the ERP, the Business Central connector post.

#The 7 causes

Every connector setting below is in app.farapp.com: select the Shopify connector and the account, then Mappings > Products for stock, or Mappings > Orders for orders.

#1. Two different meanings of "available"

NetSuite's available is on hand less what is committed to its own orders. Shopify's help pages define its on hand as committed plus unavailable plus available, so Shopify's Available also leaves out units set aside as Unavailable (damaged, on hold, safety stock) and units on orders placed but not fulfilled.

The connector writes NetSuite's figure into Shopify's available quantity, so this mostly shows up between syncs. It matters more when someone compares Shopify's On hand with NetSuite's available, or NetSuite's on hand with Shopify's Available: those were never meant to agree.

The pattern: a difference equal to the units committed or unavailable on one side, and gone once those orders are fulfilled.

#2. A sale Shopify has and NetSuite doesn't

A sale at the till lowers Shopify's count at once. NetSuite only lowers its own once the order is imported and posted. If the next inventory sync runs before that, it sends NetSuite's older, higher figure, and Shopify's count goes back up. A store manager sees stock "come back" after a sale.

Orders that stop on the way do the same for longer. Test one with an order you know, using Oracle's manual sync tests, and look at the connector's order errors.

The pattern: Shopify and NetSuite agree with each other, and both are higher than the shelf by the units on orders that haven't reached NetSuite.

#3. Several NetSuite locations feed one Shopify location

With Shopify's multi-location feature on, Oracle says you "must designate the locations in Shopify to which you want to sync inventory by mapping them with the locations in NetSuite". The mapping lives on the inventory_quantity field: Special Mapping - Click to Edit, then one row per store, with the Shopify Location ID and the NetSuite Location Internal ID.

The connector can also add up the quantity available across several NetSuite locations. That is right for one online store fed by several warehouses. For a chain whose stores are Shopify locations, a store row that lists every NetSuite location gets the chain's total.

Oracle also warns that mappings made before multi-location was enabled must be deleted before you map again.

The pattern: a store whose Shopify figure equals the sum of every NetSuite location for that SKU.

#4. POS sales come off the wrong store in NetSuite

Stock and orders are mapped separately. To tie a POS order to the store it was rung up at, add Location under Mappings > Orders > Order Item, with the mapping type Order Line with Translation and Location ID as the Shopify value, then pair each Shopify location ID with its NetSuite location. Oracle notes this mapping "applies only to Shopify POS orders", because only POS orders carry a location ID.

If a store is missing from that list, its cash sales don't reduce its own stock in NetSuite. The next inventory sync then sends that store its old figure.

The pattern: one store higher in NetSuite than on the shelf, often with another location lower by the same units.

#5. A store, or an item, the connector can't write to

Three cases from Oracle's pages:

• The item isn't tracked in Shopify. "If you attempt to send inventory to Shopify for an item you have not set to track inventory, an error will result." The fix is an inventory_management mapping set to shopify (track inventory on site).
• The Shopify location is deactivated. The connector logs Location is deactivated. Reactivate the location in Shopify, or remove it from the inventory_quantity mapping if the store has closed.
• The variant sits under another parent. The error "Variants have not been changed. The following IDs do not exist or do not belong to the product" means Shopify has the variant under a different parent item than NetSuite. Oracle says to delete it from Shopify or adjust the item in NetSuite to match.

The pattern: a SKU or store frozen in Shopify while NetSuite keeps moving.

#6. Arithmetic on the way out

The inventory mapping can change the quantity before it is sent. Add Operation Row lets you add, subtract, multiply or divide; Oracle's own example subtracts 5 as a buffer. Backorders are another rule: by default the connector doesn't subtract backordered quantities across several locations. Oracle's example has 0 available and 19 backordered at one location, 25 available and 1 backordered at another, and the connector sends 25. The box Subtract Backordered Quantity At Selected Locations From Total changes that.

Neither is a fault, but know both before anyone "fixes" a count by hand.

The pattern: Shopify lower than NetSuite by the same amount on every SKU, or a figure that ignores backorders you expected it to net off.

#7. Exchanges and refunds at the till

Oracle says exchanges in Shopify POS are handled by Refund Sync, which "you need to enable on all POS orders". With it on, the connector creates a cash refund on the original cash sale and a new cash sale for the item exchanged. Without it, the exchange happens in Shopify and not in NetSuite, and the next sync carries NetSuite's view back to the store.

The pattern: small differences at stores with many exchanges, on the sizes and colours customers swap.

#Line up every store at once

One product page tells you a count is wrong. Every location side by side tells you which cause it is.

From Shopify:

1. In the Shopify admin, go to Products > Inventory and click Export.
2. Under Export from, choose all locations, and under Inventory states shown, select All states.
3. Export as CSV. The file has one row per variant per location, with SKU, Location and Available.

From NetSuite:

1. Go to Lists > Search > Saved Searches and click New Saved Search.
2. Choose the search type Inventory Balance. It returns each item's quantity on hand and available per location (and per bin, lot or status where you use them).
3. Add criteria for the locations that are stores, then Save & Run and export the results.
4. Save them as netsuite_stock.csv with three columns, sku, location and qty (the quantity available), naming each location as Shopify does. Several rows for one item and location are fine: the script adds them up.

This script prints every SKU and store where the two differ, flags a Shopify figure that equals the chain's total in NetSuite (cause 3), and flags a difference that repeats across many rows (cause 6):

import csv
import sys
from collections import Counter, defaultdict


def number(text):
    text = (text or "").replace(",", "").strip()
    if text == "":
        return 0
    try:
        return int(float(text))
    except ValueError:
        return None  # "not stocked", "not tracked" and the like


def load(path, sku_col, loc_col, qty_col):
    counts, flags = {}, {}
    with open(path, newline="", encoding="utf-8-sig") as f:
        for row in csv.DictReader(f):
            sku = (row.get(sku_col) or "").strip()
            if not sku:
                continue
            key = (sku, row[loc_col].strip())
            qty = number(row[qty_col])
            if qty is None:
                flags[key] = row[qty_col].strip()
            else:
                counts[key] = counts.get(key, 0) + qty
    return counts, flags


shop, shop_flags = load(sys.argv[1], "SKU", "Location", "Available")
ns, _ = load(sys.argv[2], "sku", "location", "qty")

chain_total = defaultdict(int)
for (sku, _loc), qty in ns.items():
    chain_total[sku] += qty

gaps = Counter(shop[k] - ns[k] for k in set(shop) & set(ns) if shop[k] != ns[k])

print("sku,location,shopify,netsuite,difference,note")
for key in sorted(set(shop) | set(ns) | set(shop_flags)):
    sku, loc = key
    if key in shop_flags:
        print(f"{sku},{loc},,{ns.get(key, '')},,{shop_flags[key]}")
    elif key not in shop:
        print(f"{sku},{loc},,{ns[key]},,missing in Shopify")
    elif key not in ns:
        print(f"{sku},{loc},{shop[key]},,,missing in NetSuite")
    elif shop[key] != ns[key]:
        diff = shop[key] - ns[key]
        if shop[key] == chain_total[sku]:
            note = "equals chain total"
        elif gaps[diff] >= 10:
            note = f"same gap on {gaps[diff]} rows"
        else:
            note = ""
        print(f"{sku},{loc},{shop[key]},{ns[key]},{diff},{note}")

Run it as python compare_stock.py inventory_export.csv netsuite_stock.csv > differences.csv. Run it just after an inventory sync, so cause 2 has had the least time to build up.

#A worked example

An invented chain of 120 stores sells through Shopify POS at every till and online, with NetSuite as the ERP and each store a Shopify location. The script returns five rows on Tuesday morning, and the store manager at ST-014 rings about a sixth:

Invented figures. Read the patterns:

• ST-022, 212 against 6. ST-022's row in the inventory_quantity mapping lists every NetSuite location, so the store is sent the chain's total (cause 3). Mapping it to ST-022's location alone sends 6.
• ST-028, missing. ST-028 was deactivated in Shopify during a refit, and the connector log shows Location is deactivated (cause 5). Reactivating it lets the next sync through.
• ST-004, −5, on 340 rows. Someone added a Subtract 5 operation to the inventory mapping as a buffer for the online store, and it applies to every store (cause 6). Nothing is lost; decide whether the stores should have it.
• ST-011, −8. Eight socks sold at ST-011 came off ST-012 in NetSuite: ST-011 opened last month and is missing from the order Location mapping (cause 4). ST-012 shows the mirror image further down the file.
• ST-037, −2. Two scarves are on online orders placed since the last sync and not yet imported, so Shopify has them as committed while NetSuite still counts them as available (cause 1). The first sync after the orders arrive clears it.
• ST-014, 14 and 14, shelf 9. The two systems agree, which is why the script can't see it. Five T-shirts sold at the till on Saturday sit on POS orders that stopped on an error, so NetSuite still counts them and every sync puts them back (cause 2). Fixing the orders and posting them brings both to 9.

#Doing it every morning

The script takes minutes, but the causes come back: another store opens without an order mapping, another batch of POS orders stops over a weekend.

DataMosaic runs that comparison every night against the systems themselves. Its Shopify connection asks for read-only access to orders, products, stock and locations, and arrives with a Stock by location query and an Orders query that names the store for POS sales. Oracle NetSuite is in Mosaic's catalogue of ERPs. A check sets Shopify's stock by location against NetSuite's, per SKU and store, and its rules say how far apart is too far. A row that differs offers an investigation, so a store showing the chain's total points at a location mapping rather than a stock-take. If you want Mosaic to act, say by emailing whoever looks after the connector when the same store differs two nights running, that is a workflow you build.

The same idea, comparing at a grain both systems keep, is how POS and ERP sales are checked each morning.

#Questions people ask

#Does NetSuite Connector sync inventory from Shopify to NetSuite?

No. Oracle says inventory syncs to the storefront, not from it. Shopify's sales reach NetSuite as orders, POS sales as cash sales by default, and change NetSuite's stock when they post.

#How often does NetSuite sync inventory to Shopify?

With real-time sync, within a few minutes of a change, depending on what else is running. Without it, the sync runs every hour by default.

#Does NetSuite send quantity on hand or quantity available to Shopify?

Quantity available, when it syncs from NetSuite locations. Oracle defines it as what isn't committed to orders, so it is lower than on hand whenever orders are open.

#Why does Shopify show more stock than one store has?

Most often that store's row in the inventory_quantity mapping adds up several NetSuite locations, so it is sent their total. Map each Shopify location to its own NetSuite location.

#Why does Shopify stock go back up after a POS sale?

The sale hasn't posted in NetSuite yet, so the next inventory sync sends NetSuite's older figure. Check the connector for orders that stopped on an error, and the order Location mapping for the store.