Connect Shopify Orders API to Google Sheets

Connecting to the Shopify Orders API to Google Sheets via SyncWith

In this reference we’ll look at how to setup a Shopify Orders API connection.
We’ll use SyncWith to get your Shopify Order Data into Google Sheets. Please see our Shopify Guide to see a full listing of all our Shopify connections, templates, tutorial and specific Shopify use cases.
In SyncWith if you search for shopify you’ll get a few results, this guide deals with moving Shopify Order data into Google Sheets. You can check out our guides for Shopify Products and General Shopify API GraphQL Queries.
As a reference this connector utilizes the Shopify Orders API, if you’re curious about calling the api directly we have published a comprehensive Shopify API Guide. You can read the official Shopify API specs at https://shopify.dev/api/admin-rest/2022-04/resources/order.

Connect to Your Shopify Store

Connecting to shopify works slightly differently - you need to add the SyncWith app in Shopify and then your stores will automagically show up in the Google Sheets addon, here are instructions: Connect your Shopify account
notion image

Don’t Have SyncWith?

  • SyncWith is a Google Sheets Add On
  • SyncWith allows you to move data from any API into Google Sheets
  • Learn more about why Marketers love SyncWith
  • Check out our Google Workspace Listing and see why we’re the highest rated API add on with over 100,000 installs. Install today and get your data into Sheets in minutes.

Choose a Reporting Time Frame

You can choose from a wide range of default reporting time frames as well as dynamic ones. It’s important to understand that Shopify’s API is Slow, they will return a max 250 results per page and all orders must be cursored through with each request taking `10 seconds. While we handle all the complexity of getting a large number of orders retrieved we cannot make Shopify’s API respond faster. For this reason if you want to collect a large number of orders in your sheet you can use our append mode and keep the period shorter
As a rule of thumb assume it’s about 1000 orders per minute, so 100,000 orders will take ~ 100 minutes to retrieve.
Popular options include:
  • Last 30 days
  • Last 7 days
  • Last Month
  • Month to Date
notion image

Choosing Your Fields

The Shopify Orders API has the concept of fields. Every row returned corresponds to a order so there is a cardinality of 1 and no additional dimensions to expand across. Any time multiple values could apply to an order - such as line items or discount codes they are included in a single cell comma separated. You can use SPLIT() as a method to extract them. If you feel we’re missing anything please contact support and we’ll prioritize the updating of our Shopify Orders connector.
notion image
 
Shopify Order API Fields
Metric
Description
Billing Info - Multiple Metrics
All of the billing address fields are available including: Billing Address 1 Billing Address 2 Billing City Billing Country Billing Phone Billing Name Billing Latitude, Billing Longitude are also available if you if you want to do marker based lat long mapping of sales, payment issues, etc.)
Shipping Info - Multiple Metrics
Similarily to Billing info all the shipping information is available (Address, City, Country, Phone, Name, Etc.
Order ID
This is the unique identifier of the order in question. If you ever wanted to cancel, close or re-open an order you need the order ID on any POST request to the orders API. Also useful for looking up or referencing the order in the Shopify app.
Order Status URL
Order status URL points to the order status page, this is handy to provide to customers as the order status page allows them to: - Check the status of their shipment - See real-time updates on the location of their shipment - Opt in or out of Messenger, SMS, and email notifications - Click to begin shopping in your online store again
Tags
Tags attached to the order, formatted as a string of comma-separated values. Tags are additional short descriptors, commonly used for filtering and searching. Each individual tag is limited to 40 characters in length.
Note
An optional note that a shop owner can attach to the order. For example Package arrive damaged Courier delivered to the wrong house
Line Item Names
All the line item names (using the title of the product) from the order are included, if there are multiple line items in the order they will be comma separated and can be extracted with the SPLIT() function.
Email
Customer’s email address
Phone
Customer’s phone number for receiving SMS notifications
Customer Locale
The two or three-letter language code, optionally followed by a region modifier, for example english speaking in Canada would be en-ca, english speaking with no locale would be en
Browser IP
The IP address of the browser used by the customer when they placed the order. Both IPv4 and IPv6 are supported.
Created At
Time when the order was created in the Shopify app.
Closed At
The date and time when the order was closed. Returns null if the order isn't closed.
Updated At
The date and time when the order was last modified. Because the value can change when no visible fields of an order have been updated it may not be an appropriate field to sort by.
Cancelled At
There is no direct Status field so if you want to look at all cancelled orders within a list of all orders the presence of a cancelled date will help you do that. The date and time when the order was canceled. Returns null if the order isn't canceled. Use Cancel Reason to understand why a specific order was cancelled.
Cancelled Reason
Reason why the order was cancelled, values include: Customer The customer canceled the order. Fraud The order was fraudulent. Inventory Items in the order were not in inventory. Declined The payment was declined. Other A reason not in this list.
Currency Code
3 Letter code representing the currency of the shop (uses ISO 4217) examples include: AUD - Australian dollar EUR - Euro USD - United States Dollar CAD - Canadian Dollar
Total Price
Total of all item prices, discounts, shipping, taxes, and tips in the shop currency.
Subtotal Price
The price of the order in the shop currency after discounts but before shipping, duties, taxes, and tips.
Total Tax
The sum of all the taxes applied to the order in the shop currency.
Total Line Items Price
The sum of all line item prices in the shop currency.
Total Tip Received
The sum of all the tips in the order in the shop currency.
Total Outstanding
The total outstanding amount of the order in the shop currency.
Total Discounts
Provides the total discount on the order in the shop currency. This is useful to have to analyze at a broad scale if discounts are getting in the way of profitability.
Discount Codes
Provides a list of all discount applied to the order. If multiple discounts are applied, they will be comma separated and could be extracted with the SPLIT() function. Useful if you want to analyze the discount usage in the recent period, see if certain codes are being abused, etc. For example CYBER_MONDAY
Landing Site
The URL for the page where the buyer landed when they entered the shop. This can be useful if you want to understand the pages that drove orders. If you’ve created landing pages that you want to track the performance of this can be helpful or if you want to discover which landing pages are driving the most $.
Referring Site
The website where the customer clicked a link to the shop. Useful as another source of understanding where you’re driving sales from. Not all marketing is paid or controlled so you will not be able to set UTMs and other tracking variables against them. For example https://instagram.com
Source Name
The source of the checkout. To use this field for sales attribution, you must register the channels that your app is managing. You can find a list of your channels in the Partner Dashboard, in your app's Marketplace extension. You can specify a handle for each source which is what will show up here. For example instagram, web
Gateway
Payment gateway used for the order, for example shopify_payments.
Processing Method
Total Impressions less Viral Impressions. See Viral Impressions
Fulfillment Status
The order's status in terms of fulfilled line items. fulfilled Every line item in the order has been fulfilled. null None of the line items in the order have been fulfilled. partial At least one line item in the order has been fulfilled. restocked Every line item in the order has been restocked and the order canceled.
Financial Status
The financial status of the order: authorized Show only authorized orders pending Show only pending orders paid Show only paid orders partially_paid Show only partially paid orders refunded Show only refunded orders voided Show only voided orders partially_refunded Show only partially refunded orders any Show orders of any financial status. unpaid Show authorized and partially paid orders.
Location ID
The fulfillment origin of the order - used by Shopify to calculate applicable taxes. This is not the ID of the location where the order was placed.
Processed At
The date and time when an order was processed.
Total Weight
The sum of all line item weights in grams. The sum is not adjusted as items are removed from the order.

Filtering by Order Status

If you wish to return orders of a specific status you can set the Status filter to
  • All
  • Open
  • Closed
  • Cancelled
Alternatively if you return All and want to filter in Sheets you can use:
  • Cancelled At - if a date is present the order was cancelled
  • Closed At - if a date is present the order is closed
  • If Closed At and Cancelled At are both null then the order is Open
notion image

Filtering by Financial Status

While you can return Financial Status as a field in the results you can also filter by it. The following statuses and their meanings are available:
authorized Show only authorized orders pending Show only pending orders paid Show only paid orders partially_paid Show only partially paid orders refunded Show only refunded orders voided Show only voided orders partially_refunded Show only partially refunded orders any Show orders of any financial status. unpaid Show authorized and partially paid orders.
notion image

Filtering by Fulfillment Status

While you can return the Fulfillment Status of the orders in the results you can also filter by it. The following statuses and their meanings are available:
fulfilled Every line item in the order has been fulfilled. null None of the line items in the order have been fulfilled. partial At least one line item in the order has been fulfilled. restocked Every line item in the order has been restocked and the order canceled.
notion image

Scheduling How Often you Sync Your Data to Google Sheets

Manual Refresh

Manual Refresh Is the default scheduling for a connection. Data will not be updated until you actively refresh it. In order to refresh the data you must either:
  • Refresh your data from in the SyncWith addon side bar via the refresh button - all connections can be manually refreshed
notion image
  • Refresh via the Add-on menu, allowing you to refresh all connections setup, or all connection(s) pushing data to the current active sheet
notion image
  • Refresh via the check box (located in the header above the data table)
notion image

Scheduled Refreshes

There are 3 options for scheduled refreshes:
  • Automatically refresh daily will refresh the data every day at the same time. The time is set based on when the connection was last updated. If you wish to set the refresh to happen at a specific time you would need to open the connection and save it at that time.
  • Automatically refresh hourly will refresh the data every hourly
  • Automatically refresh every 5 minutes will refresh the data every 5 minutes. This is not typically used for shopify data. But if you had a staff looking at all the recent orders it could make sense, you could constanly pull the current and last days data rapidly to monitor things in close to real time.

Sample Connections

Coupons, Cancellations and Fulfilment Pivots

We’re going to start by getting a handful of dimensions in order to build a few different pivots / order analysis . Here are the dimensions we’ll need:
  • Total Line Items Price
  • Total Discounts
  • Discount Codes
  • Fulfilment Status
  • Canceled At
  • Cancel Reason
    • Analysis

      With the selected fields we’ll be able to:
    • Pivot which coupons are most popular and the average order size per coupon code (including no coupon code)
    • Discount distribution - how much discount are we offering on average based on order size
    • Pivot of current open orders and their fulfilment status
    • Pivot of order cancellation status and lost revenue
notion image

Getting the Data

Build the connector (see example above). Reminder you have to connect your Shopify account, for your stores to show up in our addon - you can follow our connect your Shopify account tutorial to do so.
After connecting and building the connection you’ll see data looking like the following
notion image

Analyzing Our Shopify Coupons

We can build a pivot table to look at our coupons, here’s what we get:
notion image
Based on this we can get some insights:
  • Our $20 Off coupon Coupon 10 - has a really high % discount - this is unsurprising - users will spend the least they possibly can to still be able to use the coupon
  • Our most popular coupon for 20% off Coupon 1 is also unsuprisingly much more popular than our Coupon for 10% off - Coupon 2
  • Fortunately most of our sales have no coupon attached and we can see in the totals at the bottom of the pivot that on average we’re discounting ~6% of order amounts - which may or may not be acceptable.

Analyzing Order Cancellations

Again a pivot table is the fastest route here, and we can see our cancellations are in a healthy state
notion image
 

Need Help Analyzing your Shopify Order Data?

Contact Support and we can try and answer any questions you have.