Find what makes you money in a year of sales

1. 01Done

   Export a year of sales as a CSV

   You want the most detailed sales file your software gives you: one row per sale or per item sold, with a date, the item or service, the quantity and the amount. A cost column is a bonus. Without one you get revenue answers, not margin.

   • Square: export the report from Square Dashboard. You can pick the columns, and Square downloads a CSV.
   • Shopify: export the report from your admin. For line-level detail, export from the Orders page: an export of orders by date arrives by email.
   • Anything else: look for the export on its sales or item-sales report, and pick CSV.

   Take twelve full months if you have them, so every month is whole. Keep the downloaded file as it is and do all your edits on a copy: the last step checks numbers against the original.

   Checkpoint

   One CSV covering a year of sales, and the original kept untouched.

   Copy step as prompt

   ---

2. 02Done

   Strip the people out before you upload

   Open the copy in a spreadsheet and delete every column that names or reaches a person. Shopify's order export, for one, carries Email, Phone, Billing Name, Shipping Name, Billing Company, Shipping Company, both addresses, order Notes, Note Attributes, Tags, Employee and payment reference IDs. Then read every remaining column header and check a few rows of each, because notes and tags often hold a name.

   Keep the date, the item, the quantity, prices, discounts, refunds, the total and the location. To count repeat buyers, the model needs a column that tells customers apart. Shopify's order export has no customer ID: its Id is the order ID and its Name is the order number. So before you delete the Email column, sort by it and add a column that gives each new email a number, then delete the emails.

   A customer number can still be traced back to a person by anyone who has your original file. Treat the stripped copy as customer data, not as anonymous, and read the data settings of whichever tool you use before the first upload. Save it as a new file.

   Checkpoint

   A copy of the export where you've checked every column, with no names, emails, phone numbers, addresses or payment details left in it.

   Copy step as prompt

   ---

3. 03Done

   Check what the model read

   the step that pays off

   In Claude, check that Code execution and file creation is switched on; on a Team or Enterprise plan the organisation owner controls it. Claude's limit for this feature is 30MB a file. ChatGPT writes and runs Python for data tasks like this one. Attach the stripped CSV to a new chat and run the first prompt.

   Then compare the row count it reports with the row count your spreadsheet shows. OpenAI's own help page says a file can upload and still not be analysed in full. If the numbers differ, stop there.

   Read point 4 carefully. Shopify notes that some of its exports put one row per product in an order, so counting rows overcounts orders. Answer every [ASK: ...] in a few plain lines: count refunds as negative, drop test orders, treat these two names as one product, cost is per unit so multiply it by quantity.

   Step 1: Check what the model read before it counts anything

   Copy

   Attached is a sales export from {{SOFTWARE_NAME}} covering {{DATE_RANGE}}.
   Before any analysis, tell me what you read. Use code to read the whole
   file. Do not work from a sample of rows.
   Report, in this order:
   1. The exact number of data rows you read, not counting the header row.
   2. Every column name, with one line on what it appears to hold.
   3. The earliest and the latest date in the file.
   4. What one row stands for: one order, one line item in an order, one
      payment, or something else. Say which columns tell you.
   5. Anything that would distort totals: refund or return rows, cancelled
      or test orders, blank rows, a totals row at the bottom, the same
      product or service under two different names.
   6. Whether the file has a cost column. If it does, say what one value
      in it appears to mean: cost per unit, cost for the whole line, or
      cost for the whole order, and whether it has to be multiplied by
      quantity to get the cost of a line.
   7. Whether the file has a column that tells customers apart without
      naming them.
   Do not calculate any totals yet. Where the file does not settle
   something, write [ASK: the question] instead of guessing.

   Checkpoint

   A row count that matches your spreadsheet, and a written answer to every question it asked.

   Copy step as prompt

   ---

4. 04Done

   Ask the five questions

   Paste your answers from the last step into the block at the bottom of the second prompt, and run it in the same chat so the file is still loaded.

   The rule doing the most work is the one that makes it show, under every answer, which columns and how many rows went into the number. A top-10 list built from 400 rows of a 9,000-row file looks exactly like one built from all of them until you read that line. OpenAI's help page says the same thing in its own words: review the code, outputs and assumptions before relying on the result. Claude's help page says you can review what it did in its sandbox.

   Question 4 compares one season with the one before it. A café's summer against its spring is a different animal from a slump.

   Step 2: Answer five owner questions, with the working shown

   Copy

   Use the sales export attached to this chat. If this is a new chat,
   the file is attached again. Answer the five questions below. Rules:
   - Every number must be calculated from the file with code. Do not
     estimate, do not round a trend into a story, and do not fill a gap
     with what is typical for a business like mine. If the file cannot
     answer a question, write [ASK: what is missing] and move on.
   - Clean the data using the rules I confirmed, pasted at the bottom.
   - Under every answer, show how you got it: the columns used, the filters
     applied, and the number of rows that went into that number.
   - Revenue means sales after refunds and discounts, before tax and
     shipping. If the file cannot separate those, say which total you used.
   - Gross margin means revenue minus cost. Margin percentage means gross
     margin divided by revenue. Read the cost column the way the cleaning
     rules below say it works (per unit, per line or per order).
   Questions:
   1. The top 10 products or services by revenue for the whole period,
      with units sold and each one's share of total revenue.
   2. If there is a cost column: the top 10 by gross margin in dollars,
      with the margin percentage for each. If there is no cost column,
      write [ASK: cost per item] and skip this question.
   3. Revenue by month, as a table, oldest month first.
   4. The 5 products or services whose revenue fell the most, in dollars,
      comparing the last three full months with the three full months
      before them. State that this comparison ignores seasonality.
   5. If there is a column that tells customers apart: how many customers
      bought more than once, and what share of revenue came from them. If
      there is not, write [ASK: a customer ID column] and skip this question.
   End with a list headed "Check these by hand": the two numbers above that
   the rest of the answers depend on most.
   Do not explain why any of this happened. Report what the file shows.
   Cleaning rules I confirmed:
   """
   {{YOUR_ANSWERS_FROM_STEP_1}}
   """

   Pro tip

   Ask for the monthly table as a line chart too. Both tools can draw one from the same file, and a dip is easier to see than to read.

   Checkpoint

   An answer or a marked gap for each of the five questions, each answer with the columns, filters and row count behind it.

   Copy step as prompt

   ---

5. 05Done

   Check two numbers against your software's own report

   Take the two numbers the model listed under "Check these by hand", for example one month's revenue and the top seller's total. Open the same report in Square, Shopify or your accounting software, set the same date range, and compare.

   Small gaps usually have a plain cause. Shopify's sales reports include pending, unpaid and cancelled orders, leave gift card sales out, and can list a renamed product on two lines. If the numbers don't match, paste both into the chat and ask which rows explain the difference. Don't act on the answers until they match or you know why they don't.

   Checkpoint

   Two numbers that match the source report, or a known reason they don't.

   Copy step as prompt

← All use cases