Stop 2 of 8 · Weekly
Orders and customers
Two exports and one cost list turn last month into best sellers by margin, repeat buyers and a return rate you can act on.
Two files and a list you type yourself
Orders, Export, last 60 days. Customers, Export. Then a third thing Omar types by hand: handle and cost per unit for the products he cares about this month.
That third one matters more than it looks. Revenue is in the orders export. Margin is not. No Shopify order file knows what a crew neck cost to land in the warehouse, so a margin column that appears without Omar's costs was made up. The rule for the rest of this course: Omar supplies the cost, Claude does the arithmetic, and any handle with no cost gets a blank cell and a mention in the count of blanks.
Sixty days, not thirty. Refunds land weeks after the order that caused them, so a 30-day window flatters your return rate.
What leaves the building
From the orders export delete customer name, email, phone, shipping and billing address. Keep order number, date, handle, variant, quantity, price, discount, refund and fulfillment status. From the customers export keep customer id, order count, total spent, first and last order date. Nothing that names a person needs to be in either file for any of the work below.
The three decisions
The table exists to produce three answers, and if it does not, it was a nice table.
Reorder. Which handles sold through and made money after discounts and refunds. That list feeds stop 7.
Promote. Which handles earn well per unit and barely sold. Those are what the Klaviyo flows in stop 4 carry, because a flow built around your best margin beats a flow built around your loudest photo.
Fix or drop. Which handles come back. A four percent return rate across the store is a business. Twenty two percent on one trouser is a sizing problem for the size chart in stop 3, or a photo problem for stop 6.
Try this
[upload the 60 day orders export with the name, email, phone and address columns deleted] My costs per unit, handle then cost: [paste the handle and cost list] Build one table: handle, units, gross revenue, discounts, refunds, net revenue, cost, gross margin in dollars and in percent. Sort by gross margin in dollars. Leave cost and margin blank for any handle not in my list and tell me how many rows you left blank. Do not estimate a cost or a price for any reason.
[upload the same orders export] Give me return rate per handle: units sold, units refunded, rate, and net revenue after refunds. Include only handles with at least ten units sold, and sort by rate. Then list separately the five handles where refunds cost me the most money rather than the highest rate, and tell me which size each of those refunds came from.
[upload the customers export with the naming columns deleted, and the same orders export] Split these customers into first order only, two orders, and three or more. For each group give me the count, the average order value and the average days between orders. Then tell me which product handle appears most often as the first purchase of the customers who went on to order three or more times.