Best answer

Inventory Tracking: Shopify Orders to Google Sheets (multiple of same SKU and upsells issue)

  • 28 February 2023
  • 7 replies
  • 123 views

Hey everyone!

We use Zapier to link Shopify & Google Sheets, primarily for the purpose of inventory tracking as well as some forecasting.

I’ve just realised (11,000 orders later - 🙃), that when a customer purchase 2x of the same SKU, it is only registered as one within the Google Sheet. If two seperate products are purchased, they are listed correctly.

I then use some code to count X times certain SKU’s appear, then apply that to our inventory levels - so missing out on double orders is throwing our numbers off slightly.

For reference, in Zapier, I am using “Line items SKU” to transfer this data.

 

Would anyone know how to define how many times each SKU was purchased per order?

We also use post-purchase upsell apps, which do not fire the Zap at all - if anyone knows how to solve that, please let me know!

Thanks!

icon

Best answer by Troy Tessalone 28 February 2023, 03:33

View original

7 replies

Userlevel 7
Badge +11

Hey @HelloEverybody!

Off the top of my head, are you able to use a quantity field to track the number of items a person is purchasing?

Without seeing how the Zap is set up, it’s difficult to troubleshoot. Any chance you can share a bit more about the Zap, maybe some screenshots (without personally identifiable information, please)?

That will help us troubleshoot things :) Thanks!

Userlevel 7
Badge +14

Hi @HelloEverybody 

Good question.

Each Shopify Order Line Item include a Quantity that you should use.

Thanks for your responses @nicksimard & @Troy Tessalone 

This is the complete Zap we have setup. It fires when there is a New Paid Order in Shopify and the screenshot references Zapier taking that info and Creating a Spreadsheet Row in Google Sheets. There are currently no other steps (aside from a date/time formatter)

“The SKU → 1. Line Items Sku:” is the area I’m referring to in my question. 

I hope this helps! I have not used a quantity field before, if this is the correct path to take is there anything I should be aware of?

Thanks!

Userlevel 7
Badge +14

@HelloEverybody 

On the GSheet add a new field for Quantity.

The similar to the Line Items SKU, there will be a Line Items Qty variable available to map.

 

@Troy Tessalone 

Thanks for that. It appears that if I use line items qty it will correctly count if multiple of the same SKU are purchased, however if the order is as follows:
XYZ x2
XXY x 1

The output will read 2,1

How would I split this to then correspond the figures to the correct SKU?

Thanks

Userlevel 7
Badge +14

@HelloEverybody 

Try using the Looping app to handle line items if you want each SKU on it’s own line.

Userlevel 7
Badge +14

@HelloEverybody 

You can also use the Formatter > Numbers > Perform Math Operation to handle the SUM of line items.

 

Reply