Storing at purchase cost of goods value

New Member
3 0 0

Hi all, I'm working on an accounting integration project where I need to account for the cost of goods of a product.  The product space I'm working with has a significant lag between purchase time and shipment time so the cost of goods value may not be what's currently set in the system (i.e. purchase 1/1 cost of goods is $50, when actually shipped 6/1 cost of goods is $65 but I'm recording the 1/1 COG)  

I was hoping that cost of goods value was persisted within the order object but it doesn't seem to be.  I have the sense I need to build a custom database where cost of goods is persisted with the general structure of 

[timestamp] [order id] [transaction id] [line item id] [cost of goods at purchase]  

1. Am I correct my understanding of the API being limited in this way?

Furthermore, as COG at time of order is apparently not reachable/stored in the Order object it looks like retrieving the COG value is two part effort.  Please note I'm using pseudocode as I'm not very familiar with the API

productID =  product ID from the order. transaction

variantID = variantID from the order.transaction.product.variant

COG = system.getProduct(productID).getVariant(variantID).getCOG()

2. Is there an alternate best-practice way of doing this?

3. I'm unclear if non-inventory items (inventory not tracked) has COG set.  It's certainly in the product attribute screen, but the documentation is vague on this matter when it comes to the API.  Most of the products in this application are handmade and hence do not have inventory levels set.

Comments and thoughts are welcome.

Dave

 

 

0 Likes
Shopify Staff
Shopify Staff
697 81 150

Hey @spiritpieces,

The reasoning here looks sound to me. I can confirm that the inventory_item cost field isn't persisted, so storing the value at time of purchase is the best way to go. 

For each product variant in the store, an inventory item will exist whether or not inventory is tracked for the variant. So you can definitely track cost for all variants regardless of inventory tracking settings.

Rather than keeping a separate db to track costs, another option is to add this data to the order as a metafield or note attribute. You can do this by listening for webhooks or doing periodic API pulls, when you find a new order you can do separate calls to retrieve the variant cost, and then make a call to add a metafield/note_attribute to that order. This will allow you to retrieve that cost data through Shopify's API without having to maintain a separate db. 

JB | Developer Support @ Shopify
 - Was my reply helpful? Click Like to let me know! 
 - Was your question answered? Click Accept as Solution 

0 Likes
New Member
3 0 0

@_JB Thank you.  I thought about the order metafields but we may have several items in each order, and I need to get the COG per item.  Unless I'm not understanding how metafields work that seems difficult, unless I do something like insert a separate metafield based on the variantid, i.e. VARID_45555444_40.50 and parse out the elements...

Is there a limit on metafields per order?

0 Likes
Shopify Partner
871 76 189

Not to butt in, but this is an interesting thread. Utilizing metafields for this purpose lends itself to other purposes. Especially when Shopify objects lack an "audit trail" such as in this case. I don't think there is a hard limit on metafields. One idea that would make things easier to parse for an order for numerous items would be to define the metafield as a JSON string data type. Then you can break out an array of the order's items in there. 

Shopify Staff
Shopify Staff
697 81 150

Hey @spiritpieces,

I don't believe there's a limit, if there is it's > 250 so it shouldn't affect what you're trying to do here.

Metafields contain a key/value pair, along with a namespace value. The namespace allows you "categorize" metafields and retrieve all metafields of the same type later. So for this use case, an example metafield could be:

 

{
  "metafield": {
    "namespace": "line_item_cost", // namespace to be used for all metafields
    "key": "26540348928997",       // line_item_id on order
    "value": 25,               // cost value
    "value_type": "integer"
  }
}

 

This allows you to create 1 metafield per line_item on the order, and retrieve all metafields using the endpoint /admin/api/2020-10/orders/{{order_id}}/metafields.json 

 

Edit: I like Greg's suggestion better actually, storing a single JSON metafield containing all the costs probably makes more sense. 

 

JB | Developer Support @ Shopify
 - Was my reply helpful? Click Like to let me know! 
 - Was your question answered? Click Accept as Solution 

0 Likes
New Member
3 0 0

I agree if everything can be encapsulated in a single JSON line that's certainly ideal.  I believe I'm still looking at custom code to construct/deconstruct this correct?  Meaning I would need a server with codebase somewhere to act as the interpretation bridge between the accounting system (XERO) and Shopify

0 Likes
Shopify Partner
871 76 189

Correct. Quickest thing I do is spin up an ECC instance on AWS or bring up a virtual machine on Azure. This would be talking to both the Xero API and the Shopify API in order to push/pull the appropriate data. If you have internal development resources then they can start digging in. From the Shopify API development end, there are some nice Ruby packages that hide some of the complexities of working with the API. Although your resources can use C#, Python, PHP, etc. depending on their preferences. At the end of the day, the API calls are just a series of web requests and responses being passed.

0 Likes