Which keyword or field from the orders.json API response should I use to locate a specific column?

[pc_propero98]

I have exported order data to a CSV file from my Shopify store. Now, I have also extracted order data using the Shopify API. However, I am having trouble identifying the corresponding data or keywords in the API response for the following columns present in the exported CSV file from the Shopify store:

• Tax1 name
• Tax1 value
• Tax2 name
• Tax2 value
• Tax3 name
• Tax3 value
• Tax4 name
• Tax4 value
• Tax5 name
• Tax5 value
• Receipt number
• Duties
• Billing province name
• Payment ID
• Next payment due date
• Payment references
• Risk level
• Employee
• Location
• Device ID
• Refunded amount
• Payment reference
• Note attributes
• Notes
• Line item compare-at price
• Shipping method
• Discount amount
• Shipping"

[Jonathan-HA · Accepted solution]

Hey there,

With the REST API, there are multiple endpoints involved here:

• Tax1 name
• Tax1 value
• Tax2 name
• Tax2 value
• Tax3 name
• Tax3 value
• Tax4 name
• Tax4 value
• Tax5 name
• Tax5 value

Order API: tax_lines

• Receipt number

I'm wondering about this myself, this might even be deprecated as it's not mentioned on their docs: https://help.shopify.com/en/manual/orders/export-orders#order-export-csv-structure

• Duties

Order API: original_total_duties_set

• Billing province name

Order API: billing_address.province

• Payment ID

Order Transaction API: payment_id

• Next payment due date

Order API: payment_terms.payment_schedules.due_at

• Payment references

Order Transaction API: payment_id

• Risk level

Order Risk API: recommendation

• Employee

Order API: can't get actual staff name, but you can get the record ID via the user_id field.

• Location

Not sure about this one, there's a location_id field though in the Order API.

• Device ID

Order API: device_id

• Refunded amount

Order API: refunds (you'll need to parse the list of refund transactions and add up the amounts)

• Payment reference

Order Transaction API: payment_id

• Note attributes
• Notes
• Shipping method
• Discount amount
• Shipping

Order API: note_attributes, note, shipping_lines.title, total_discounts, shipping_lines (add up the price attribute)

• Line item compare-at price

Order API and Product Variant API: Get the variant_id from the line_items.variant_id field of the Order API. Use that ID and make a call to the Product Variant API to get the compare_at_price field.

[Jonathan-HA · Accepted solution]

Hey there,

With the REST API, there are multiple endpoints involved here:

• Tax1 name
• Tax1 value
• Tax2 name
• Tax2 value
• Tax3 name
• Tax3 value
• Tax4 name
• Tax4 value
• Tax5 name
• Tax5 value

Order API: tax_lines

• Receipt number

I'm wondering about this myself, this might even be deprecated as it's not mentioned on their docs: https://help.shopify.com/en/manual/orders/export-orders#order-export-csv-structure

• Duties

Order API: original_total_duties_set

• Billing province name

Order API: billing_address.province

• Payment ID

Order Transaction API: payment_id

• Next payment due date

Order API: payment_terms.payment_schedules.due_at

• Payment references

Order Transaction API: payment_id

• Risk level

Order Risk API: recommendation

• Employee

Order API: can't get actual staff name, but you can get the record ID via the user_id field.

• Location

Not sure about this one, there's a location_id field though in the Order API.

• Device ID

Order API: device_id

• Refunded amount

Order API: refunds (you'll need to parse the list of refund transactions and add up the amounts)

• Payment reference

Order Transaction API: payment_id

• Note attributes
• Notes
• Shipping method
• Discount amount
• Shipping

Order API: note_attributes, note, shipping_lines.title, total_discounts, shipping_lines (add up the price attribute)

• Line item compare-at price

Order API and Product Variant API: Get the variant_id from the line_items.variant_id field of the Order API. Use that ID and make a call to the Product Variant API to get the compare_at_price field.

[pc_propero98]

Thanks a lot!

I need one more help:
Which keyword corresponds to "paid_at" column?

[Jonathan-HA · Accepted solution]

This one's a bit tricky, you'll have to use multiple conditions. Basically the logic will be:

If the order's financial_status == 'paid', then the latest created_at value from the list of transactions coming from the Transactions API will essentially be the "paid at" date. 

Note that it's important that the order's financial_status is paid, since if the order was refunded later, then the last transaction date will be the refund date.

There's a 'status' field and a 'kind' field in the transaction data as well, so you could use that further for the filtering: https://shopify.dev/docs/api/admin-rest/2023-04/resources/transaction

For example, an alternate logic would be something like:

Filter the list of transactions where 'status' == 'success' and ('kind' == 'sale' or 'kind' == 'capture'). The latest created_at from this list would then be the paid at date.