I have 5 years of inventory data in a spreadsheet and I want to make this work inside Shopify.
Current spreadsheet fields: Customer name, product name, varients (size and color), when ordered from supplier, when delivered to me from supplier, production cost, material cost, shipping (inbound), duties (inbound), Total COGS, Revenu, net margin dollars.
Store context: small apparel brand with size/color variants. Online sales, occasional in-person events.
What I want to do in Shopify
-
Track cost per item over time so historical margins stay accurate after costs change
-
See margin dollars and margin % by product, variant, order, and time period
-
Import my past 5 years of data so reports line up with what I already have
-
Keep things accurate with returns, exchanges, and discounts
-
Export clean CSVs for my accountant
Questions
-
What is the best practice to handle changing cost over time so old orders do not recalc with the new cost
-
Can I import historical COGS tied to past orders, or is it better to load opening balances and move forward
-
If you solved this with an app, which one and why Did it handle purchase orders and landed costs
-
For apparel variants, any tips on keeping costs in sync across sizes and colors
-
Any gotchas when moving from spreadsheet to Shopify reports or when using POS
Happy to share a sample CSV if that helps. Thanks in advance for any workflows, app suggestions, or “wish I had known this first” advice.
Hey @RealChance!
This is a sticky one because the structure and depth of your data plays heavily into how one might build out a plan to solve for your questions above.
That said, we have run into scenarios like this with clients in the past. Here is a distilled rundown of a theoretical integration path for your team.
Full disclosure, the rundown below is a blend between our internal notes and ChatGPT’s context distilled into a clean step-by-step format. I find that’s the best way to provide high context and depth in responses on the Shopify Community boards without eating up tons of time composing:
1. Our understanding of your goals, constraints and our recommendation
-
Non-negotiables:
- Preserve historical margin accuracy (no recalcs when costs change).
- Ability to see profit by product, variant, order, and period.
- Support returns, exchanges, and discounts.
- Export clean CSVs for accountants.
-
Nice-to-haves:
- Automated landed cost allocation (duties, inbound freight).
- PO workflows.
- Variant-level cost inheritance.
-
Constraints:
- Small team (don’t over-engineer).
- 5 years of legacy data, but forward-looking accuracy is more valuable than perfect backfill.
-
Our Recommendation:
- For a small apparel brand looking to move beyond spreadsheets and bring true cost and margin visibility into Shopify, Settle stands out as a strong partner because it combines purchase order management, landed cost allocation, and vendor payment workflows in one platform. That said, Settle’s strength lies in forward-looking inventory and cost control—it is less of a historical analytics engine.
- Alternatives like Profiteer or TrueProfit focus more tightly on COGS and margin reporting, and can be layered in if historical order-level accuracy or advanced profitability dashboards are the priority. Taken together, Settle provides the operational backbone while these analytics tools fill reporting gaps, making Settle the recommended anchor solution, with the option to supplement rather than replace depending on the depth of financial reporting the client requires.
2. Strategy for Data & Workflow
a. Treat history and future differently
b. Cost handling best practice
-
Use a tool (Settle or another) that locks cost snapshots at the time of sale.
-
If Settle can’t guarantee that, supplement with an analytics app (e.g. Profiteer or TrueProfit) that does.
c. Variant cost management
d. Returns, exchanges, and discounts
-
Confirm workflows in Shopify + app before go-live.
-
Run test scenarios with CSVs (return with restock, return without restock, exchange for different SKU).
-
Validate margin reports after those actions.
e. Exports
3. Recommended App Approach
-
Primary candidate: Settle (for POs, landed costs, vendor management).
-
Supplementary app: Profiteer or TrueProfit if Settle’s margin reporting isn’t granular enough or if it can’t freeze costs at order level.
-
Backup candidate: Craftybase if they want a maker-style COGS focus and don’t need Settle’s financing/PO extras.
This layered approach ensures:
-
Settle = operational control (inventory, POs, landed cost).
-
Analytics app = margin truth (historical accuracy, order snapshots, easy exports).
4. Phased Migration Plan
-
Discovery & Cleanup
-
Normalize SKU/variant naming across Shopify and the historical spreadsheet.
-
Identify variant cost rules.
-
Decide “cutoff date” (likely Jan 1, 2025).
-
Pilot Import
-
Parallel Run
-
Run live Shopify + Settle for 1–2 months while still maintaining the spreadsheet.
-
Test returns, discounts, PO receipt costs.
-
Full Adoption
-
Ongoing
5. Risks & Gotchas to Mitigate
-
Over-importing history → bogs down app, misaligns reports.
-
Unclear cost rules across variants → inconsistent margins.
-
Returns/exchanges → always test before accountants rely on reports.
-
POS sales → validate integration (offline sales must flow into cost tracking cleanly).
The Bottom line
Vet Settle as your potential backbone for inventory, landed cost, and vendor management, but don’t rely on it alone for historical margin integrity. Limit backfill to ~1–2 years, keep older history in spreadsheets/BI, and pair with a COGS/margin app (Profiteer/TrueProfit) if Settle can’t guarantee frozen order-level costs. Phase it in carefully with parallel runs before going live.
Your mileage may vary with any of the partners listed above. Every use case is different, so what has worked for us in the past may not suite your needs.
Finally, if you ever want to talk with a potential partner about this or any other project don’t hesitate to reach out! This is what we live for: Let's connect | Fraction Studio
Shopify does not track historical landed costs (production + shipping + duties). It only keeps the current cost you set for a product. When costs change, old orders will adjust to the new cost. This can mess up your historical profit margins.
To fix this, load summarized opening balances for prior years in your accounting system, then track accurate COGS going forward using an app or integration.
For most sellers, it’s important to understand that Shopify is your sales platform, not your source of truth for financial reporting. The smartest approach is to use an accounting automation platform that also streamlines inventory management and pushes clean reports back to Shopify or your accounting software.
Look for tools that can handle purchase orders and landed costs, allowing you to “receive” inventory at a set unit cost. This locks in historical costs and keeps your current margins accurate.
To find the best tools, you can search “Best ecommerce accounting automation and inventory management tools” on Google (AI Overviews), or try Perplexity or ChatGPT. Just make sure the solution is integrated and supports both accounting and inventory.
Hope this helps!
Those are good questions. At first thought it would be ideal to have all the past data. However, your second question might determine the route. Currently i don’t track the returns and exchanges in the spread sheet.
Thanks. This is what I was affraid of. What I really want is to be able to tie the cost of a single unit at the time it is entered into inventory. My costs can vary wildly, 10-25% depending on the qty I order from the factory.
Thanks for taking the time to respond. The beauty of tracking it in the spreadsheet is that each time I recieve product I can tie it to an actual cost. Since my costs vary wildly, 10-25%, based on the qty I am ordering from the factory (price breaks are significant when moving from making 5-6 of an item to 10-15 and then again 50+, additionally the shipping cost for 10 is roughly the same as 50…so that costs gets spread out more). Shopify won’t allow me to do that will it? You have to put in a cost at the product level?
Spreadsheets give control but quickly become time-consuming and error-prone, especially when order volume climbs.
I get where you’re coming from; Shopify only allows one cost per product/variant, and it won’t update historically when purchase costs change. So you can’t natively tie inventory receipts to their actual landed unit cost within Shopify.
If you want to keep that level of accuracy (tracking each batch of inventory with its real cost basis), you’ll need an inventory or accounting app that supports landed costs and cost layer tracking (like average cost or FIFO).
Hi @RealChance
For the part about keeping things accurate with returns, one thing to keep in mind is that Shopify’s default reports often don’t give you the full visibility you’d expect. For example, a return processed as s refund will reduce revenue, but if you issue store credit or an exchange, the impact on your COGS and margin can get messy unless you track it consistently.
A few tips that might work:
-
Always log returns/exchanges against the original order so your margin reports reflect the adjustment.
-
Use order tags or notes to mark the reason (e.g., damaged, customer preference, warranty). It helps later when reconciling net margins.
-
If you offer discounts on exchanges, make sure to capture the reduced revenue side by side with the cost of the replacement item, otherwise margins look inflated.
-
For accounting, try to export data that shows both the refund amount and the associated COGS adjustment, rather than just the net order balance.
If you need a more automated way to do this, apps like ParcelPanel Returns can help. It gives customers a branded returns page where they choose upfront whether they want a refund, exchange, or store credit. On the back end, it prevents refunds from exceeding the order’s actual paid amount and keeps those adjustments tied to the order, which makes your margin reporting cleaner.
Hope this helps!
Heidi
Hey RealChance,
Webgility_hq’s right — Shopify only stores a single “cost per item” per variant/product, so every time your factory cost changes (which you said swings 10-25% depending on order quantity), your historical margins on past orders quietly get recalculated using today’s cost instead of what it actually cost you at the time.
The tools mentioned above (Settle, Profiteer, TrueProfit, Craftybase) are solid if you want a full purchase-order/landed-cost workflow. I’m building something more focused on this exact gap (MargeFlow) — it keeps a cost history per SKU so past margins stay accurate after a cost change, and it also tells you the max discount you can safely run on a product given your current cost and target margin, which was the original reason I started building it. Still going through Shopify’s app review right now, so it’s not listed yet, but if this is still unsolved for you once it’s live, happy to give you early access — feel free to DM me.