Export four things: orders from Shopify, daily ad spend from each ad platform, cost per item from your product list, and shipping label and return costs. Give Claude all four with the order number as the join key, ask for profit per order and per product, and reconcile the total to Shopify before trusting it.
The reason this needs doing yourself is simple: Shopify sees what happened in the store, not what you spent to get customers there. Its profit reports leave out advertising, and they only report items that had a cost recorded at the time of sale. The Shopify connector in Claude reads the same store data, so it inherits the same blind spots. What follows is a worked month, the prompts, and the checks that stop a tidy-looking answer being wrong.
What Shopify's own report leaves out
Shopify's profit reports (gross profit by product, variant and POS location, plus profit margin by order and by market) are worth opening first. According to Shopify's help page, they report profit only for products and variants that had a cost recorded when they sold, and they include shipping charges and shipping costs in the order and market views. Refunds and discounts reduce net sales and therefore margin. The page does not mention ad spend, and it notes that cost per item is static, so the report shows costs at one point in time.
That gives four gaps to close:
- Advertising sits in Meta, Google and any other ad account.
- Missing costs for products where nobody entered a cost per item.
- Real shipping cost if you buy labels outside Shopify, or a carrier invoice differs from the label price.
- The true cost of returns: refund, return postage, and the stock you cannot resell.
The Claude connector for Shopify, as listed in Anthropic's directory, covers store info, products, collections, orders, customers, inventory and analytics. None of those carry ad accounts. The connector is built by Shopify and can also make changes, such as creating discount codes or setting inventory levels, so for this job tell Claude to read only. If you want the broader choice between Sidekick, ChatGPT and Claude for store jobs, the comparison by job covers it. This tutorial assumes Claude with files you upload, and the connector as an optional way to pull the orders.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowA small manufacturer's month, in full
Take a small manufacturer that sells hand-finished ceramic planters from its own Shopify store. For the month, 400 orders. Everything below is illustrative, but the structure is the one your own sheet should take.
| Line | Amount | Where it comes from |
|---|---|---|
| Gross sales | $26,400 | Shopify orders export |
| Discounts | -$1,200 | Shopify orders export |
| Returns and refunds | -$1,680 | Shopify refunds, 28 refunds |
| Net sales | $23,520 | Calculated |
| Shipping charged to customers | +$1,800 | Shopify orders export |
| Revenue for profit purposes | $25,320 | Calculated |
| Cost of goods on items kept | -$8,820 | Cost per item x units |
| Returned stock written off (damaged) | -$210 | Your returns notes |
| Shipping labels paid | -$2,480 | Carrier or label invoice, 400 orders |
| Return postage paid | -$392 | 28 returns at $14 |
| Payment fees | -$790 | Payments payout report |
| Packaging | -$220 | $0.55 per order |
| Advertising | -$6,200 | Meta $4,300 plus Google $1,900 |
| Profit | $6,208 | Calculated |
The sums: $25,320 minus $8,820 minus $210 minus $2,480 minus $392 minus $790 minus $220 gives $12,408 before advertising, or $31.02 an order. Subtract $6,200 and the result is $6,208, or $15.52 an order, 24.5% of revenue. Shopify's margin view would have shown something closer to 62.5%, which is $23,520 less $8,820 cost, divided by net sales. Both numbers are correct. They answer different questions, and only the second tells you whether the month paid.
The number worth keeping: break-even return on ad spend
Before ads, each dollar of revenue leaves 12,408 / 25,320 = 49.0% to cover advertising and profit. So the break-even return on ad spend (revenue per dollar of ads) is 1 / 0.49 = 2.04. If your ad dashboard says a campaign returns 3.0, that is profitable. If it says 1.8, it loses money even though the dashboard calls it a 1.8 return. That single figure keeps you from scaling a campaign that looks fine and is not.
Step one: get four clean files
Orders and refunds from Shopify
Export orders from the Orders page. Shopify's help page notes that an export of up to 50 orders downloads in the browser and larger ones are emailed to you, so for a month of 400 orders expect an email with a link. Keep these columns, and check your file's headers because they can differ slightly between exports: order name, date, line item name, SKU, quantity, line price, discount, shipping charged, financial status and refunded amount. Delete name, email, address and phone columns before you upload anything. You do not need them for profit, and fewer personal fields means less to protect.
Cost per item
Export your products and keep the SKU and cost per item columns. Open the file and sort by blank cost. Every blank is a line that Shopify's profit report will skip and your Claude sheet will treat as free, which makes the month look better than it was. Fill the blanks first. If you sell bundles, enter the cost of the whole bundle on the bundle SKU.
Ad spend by day
From each ad platform, export spend by day for the month, one file per platform. Keep date, campaign name and spend. Check the currency and the time zone setting of each ad account match your store, because a mismatch moves a day's spend into the next one.
Shipping labels and return postage
Take the real figure from the label provider or carrier invoice, not the rate you charged the customer. If one invoice covers several orders, export the line-by-line version with the order reference. For returns, list the postage you paid and how many returned units went back on the shelf versus the bin.
Step two: the prompt that joins them
I run a Shopify store that sells ceramic planters. I am attaching four files
for November: orders.csv (order name, date, SKU, quantity, line price,
discount, shipping charged, refunded amount), costs.csv (SKU, cost per
item), ads.csv (date, platform, spend) and labels.csv (order name, label
cost, return postage).
Task:
1. Join orders to costs on SKU and to labels on order name. List every SKU
in orders that has no cost, and every order with no label cost, before
you do anything else.
2. Payment fees for the month were $790 (from my payout report). Share
them across orders in proportion to order total.
3. Share total ad spend across orders in proportion to each day's order
count (days with no orders carry their spend to the month's total).
4. Give profit per order, then a table by SKU with units, revenue, cost,
refunds, labels, ads and profit.
5. Reconcile: show my total net sales and compare it with this figure from
Shopify: [paste Shopify total].
Show the calculation steps. Do not guess any missing cost: leave it blank
and flag it.The line about not guessing is the one that matters. Without it, an assistant may fill a blank with an average, and you will not notice. Ask for the unmatched list first, so you can fix the sources, not argue with the result. With code execution and file creation switched on (Settings, then Capabilities; the toggle exists on the Free plan as well as paid ones), Claude runs the joins as code and can hand back a downloadable spreadsheet. If it answers without running any code, ask for the method and spot-check.
What a realistic reply looks like
Illustrative output: "Matched 398 of 400 orders to a label cost; orders #1187 and #1203 have none. 3 SKUs have no cost: PLT-MINI-GRN, PLT-SET3, GFT-CARD. Net sales reconcile to $23,520 against your Shopify figure of $23,520. Profit before ads: $12,408 ($31.02 an order). After ads: $6,208 ($15.52 an order)." Then a table by SKU.
What to fix: the gift card line should be excluded, because it is not a sale of goods. The set of three needs a bundle cost. Rerun after you correct them, and the profit moves. That loop of "flag, fix the source, rerun" is the whole discipline.
Reading the SKU table
The by-product view is where decisions come from. Three planters from the month, illustrative:
| Product | Units | Revenue | Profit after costs and ads | Per unit |
|---|---|---|---|---|
| Large planter | 140 | $9,800 | $3,060 | $21.86 |
| Set of three | 62 | $6,138 | $1,520 | $24.52 |
| Mini planter | 210 | $4,200 | $180 | $0.86 |
The mini sells the most units and earns almost nothing once ad and shipping costs land, because a $20 item carries nearly the same postage as a larger one. Options: raise the price, add it to the set of three, or set a free-shipping threshold that nudges buyers towards a larger basket. If you are weighing a bundle discount, the bundle pricing guide shows how to check one against your margin floor.
Where ad spend gets allocated wrongly
Allocating ad spend is the one judgement call in this whole exercise, and there are three honest ways to do it:
- Blended, monthly: total ad spend divided by total orders. For the example, $6,200 / 400 = $15.50 per order. Easy to audit, and it treats all orders alike.
- Blended, daily: each day's spend shared across that day's orders. Better where you run short pushes, but a day with spend and no orders needs a rule.
- By platform: credit Meta's spend to orders Meta claims. Avoid this unless you trust the claim. Ad platforms count their own conversions, and the totals usually add up to more orders than you received.
I would start with the monthly blend and change only when you can explain why a more detailed method is better. A more complicated allocation gives a number that looks more precise, and it is no more true.
Returns are a cost line, not a revenue correction
Shopify treats a refund as a reduction in sales. For profit, a return has up to four costs: the refund itself, the outbound label you already paid, the return postage, and any stock you cannot resell. In the worked month the refund line was $1,680, but the cost of those 28 returns was $1,680 plus $392 postage plus $210 of damaged stock, which is $2,282, 36% more than the refund figure suggests. The labels paid on the original orders stay in the cost line, which is why they did not drop.
A second trap is timing. A refund issued in December for a November order may land in December's report. Decide whether you report by order date or by refund date, then tell Claude which. For a monthly sheet most small stores use order date for sales and refund date for refunds, and accept that the numbers wobble at month end. If your return rate looks high for a product, the returns reasons guide shows how to read the reason column, and the return fraud guide covers repeat abusers.
A month that looked great until one column was blank
A distributor selling through its own store ran this analysis and found a 41% margin. Pleased, it doubled its ad budget. Two months later cash was thin. The reason: 46 of its 210 variants had a blank cost per item, including its three best sellers, which had been added in a rush. Claude had been told to leave blanks flagged, but the flag was in the first reply and nobody scrolled up. After filling the costs the true margin was 27%. The lesson is to put the unmatched list at the top of the output, and to treat any non-empty list as a stop sign before reading anything else. (Illustrative.)
Checks that tell you the result is right
- Tie out: net sales in your sheet equals net sales in Shopify's sales report for the same dates. A gap of even $50 means a missing order or a date boundary issue.
- Row count: orders in the join equals orders in the export. Duplicates from multi-line orders are the usual culprit.
- Spot-check three orders by hand: pick one small, one large and one refunded order. Work out its profit on a calculator and compare it with the sheet.
- Cost sanity: total cost of goods divided by net sales should land near your known average cost ratio. Here, $8,820 / $23,520 is 37.5%. A sudden 20% means a missing cost, not a miracle.
- Sign check: refunds and costs are negative, sales positive. Mixed signs are a common cause of silly totals.
If one of these fails, do not ask the assistant to "fix the totals". Ask it to show the rows behind the gap, and correct the source file. The messy data tutorial covers cleaning a file that arrives in poor shape.
Using the sheet to set a free-shipping threshold
Once profit per order exists, the sheet answers questions the standard reports cannot. The planter store wonders whether free shipping above $60 would pay for itself. Today the average order is $63.30 ($25,320 / 400) and the average label costs $6.20. Say 120 of the 400 orders sit between $45 and $60. If a $60 threshold nudges a quarter of them, 30 orders, up by $12 each to reach it, that adds $360 of revenue, of which about 49% survives to profit before ads, roughly $176. The cost is shipping that customers used to pay: the 100 orders already above $60 would stop paying, say, $4.50 each, which is $450. The threshold loses money unless it lifts more than the figures above. Ask Claude to run it as a table with your own distribution of order values, and to show the break-even number of upgraded orders. A rule you have tested on a column of figures is worth far more than a hunch.
Follow-up questions to ask once the sheet is built
Keep the same chat or Project open and ask short, specific questions, each answerable from the table:
- "Which five SKUs lose money after ads and labels? Show units and the price rise needed to reach $5 profit a unit."
- "What would profit have been if returns had been 4% of orders instead of 7%? Show the sum."
- "Which days had ad spend above $250 and fewer than 10 orders?"
- "List orders where the label cost is more than 15% of the order value."
Each question should name the columns it uses. If the assistant answers with a figure that is not in your table, ask where it came from. A good answer points at rows.
Before and after: the same month, two views
| Measure | Shopify report view | Joined sheet view |
|---|---|---|
| Margin | 62.5% (net sales less cost) | 24.5% (after labels, fees, returns and ads) |
| Profit per order | Not shown | $15.52 |
| Cost of a return | The refund, $1,680 | $2,282 including postage and written-off stock |
| Is a 1.8 ad return fine? | Looks fine | Loses money; break-even is 2.04 |
The point of the table is not that Shopify is wrong. Each view answers a different question, and the second is the one that decides what to spend next month.
Turn it into a ten-minute monthly job
Save the prompt in a Claude Project along with a note of your cost ratio, your allocation rule and your reporting convention for refunds. Each month, drop in the four files, paste the Shopify total, and read the unmatched list first. The Project holds the instructions, so you are only supplying data.
Keep a one-line log: month, revenue, profit, profit per order, break-even return on ad spend. After six months that log shows whether margin is moving with your prices, your ad mix or your return rate. If you want the same view for a store that ships from a supplier, the dropshipping margin spreadsheet uses a related layout.
One last caution on numbers: payment fees. The example took the $790 from the payout report, not from a formula. Shopify's published online card rate on the Basic plan is 2.9% plus 30 cents a transaction, and the higher plans list lower rates, but other payment methods, currency conversion and refunds all move the real figure. Put the payout report total in the prompt each month instead of asking Claude to estimate fees.
Questions about profit reporting with Claude
Why not just use Shopify's profit reports?
Use them as your starting point. They report profit only for items that had a cost per item recorded, and they do not include advertising spend. Joining your ad exports and shipping costs afterwards gives the figure that decides whether a month made money.
Can the Shopify connector in Claude do this on its own?
Not fully. The connector reads Shopify data such as orders, products, customers, inventory and analytics. It has no view of your ad accounts or of costs held in other tools, so ad spend and label costs still have to come from exports you supply.
How should I share ad spend across orders?
Start with blended spend: total ad cost for the month divided by total orders. It is crude but honest. Use daily spend matched to daily orders only if your ad platforms report cleanly by day, and avoid crediting orders to one platform from its own dashboard.
Is it safe to upload an orders export to Claude?
Strip personal columns first. Profit work needs order number, date, line items, amounts and refunds, not names, emails or addresses. Check your plan's data settings for how uploaded files are used, and delete the working copy when you finish.
Further reads
- Ecommerce Store Audit Checklist: Run It With ChatGPT or Claude — Audit the store pages that drive the orders.
- How Much Does It Cost to Add AI to a Shopify Store? — Know what AI add-ons cost before buying more.
- Best AI for Ecommerce Operations: Orders, Stock, Returns, Suppliers — Orders, stock and returns in one operations view.
- How to Calculate AI ROI for Your Business (Worked Example) — Apply the same discipline to your own AI spend.
- Use AI to Sell Products Online: One First Job and a 30-Day Test — Choose one first job and test it for 30 days.
- Shopify Sidekick Skills: Worked Recipes for Repeat Store Tasks — Save the weekly profit check as a skill.
- AI for Ecommerce Analytics: Shopify Reports, GA4 or ChatGPT First? — Decide which analytics report to open before this one.
- Shopify Pre-Order App: Which to Pick, Then Set Ship Dates With AI — Pick a pre-order app by order volume, then use AI to turn supplier lead times into ship-date wording and a delay email.
- Shopify RFM Analysis: Segment Customers With ChatGPT From an Export — Score customers on recency, frequency and spend from a Shopify export, then write one email per segment, keeping personal data minimal.
- Applications of AI in E-Commerce: 9 Uses Mapped to a Small Online Store — Nine uses of AI for a small store, ranked by weekly hours saved and matched to Sidekick, Shopify Inbox, Klaviyo and Gorgias, with plan costs.
- AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.
Sources: Shopify Help Center (profit reports, exporting orders), Shopify pricing page (card rates), Claude connector listing for Shopify, Claude Help Center (create and edit files), checked November 2026.