Solved

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

pc_propero98
Shopify Partner
10 0 0

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"
Accepted Solutions (2)

Jonathan-HA
Shopify Partner
320 25 100

This is an 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.

Co-Founder / Developer at Highview Apps
Our Shopify Apps: EZ Exporter | EZ Inventory | EZ Importer | EZ Notify | EZ Fulfill

View solution in original post

Jonathan-HA
Shopify Partner
320 25 100

This is an 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.

Co-Founder / Developer at Highview Apps
Our Shopify Apps: EZ Exporter | EZ Inventory | EZ Importer | EZ Notify | EZ Fulfill

View solution in original post

Replies 3 (3)

Jonathan-HA
Shopify Partner
320 25 100

This is an 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.

Co-Founder / Developer at Highview Apps
Our Shopify Apps: EZ Exporter | EZ Inventory | EZ Importer | EZ Notify | EZ Fulfill
pc_propero98
Shopify Partner
10 0 0

Thanks a lot!


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

Jonathan-HA
Shopify Partner
320 25 100

This is an 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.

Co-Founder / Developer at Highview Apps
Our Shopify Apps: EZ Exporter | EZ Inventory | EZ Importer | EZ Notify | EZ Fulfill