# BigCommerce margin and product performance explorer

> Pick a date range and see units sold, revenue, cost and gross margin for every SKU, with a category rollup and one click export to a spreadsheet.

- Workflow type: app
- Services: BigCommerce, Google Sheets
- Categories: Operations, Finance
- Published: 2026-08-14

## What it does

- Builds the product report your store admin will not give you: units sold, revenue, cost of goods and gross margin for every SKU, over any date range you choose
- Groups results by day, week, month or quarter, and lets you filter by SKU, product name, category and order status all at the same time
- Rolls the same numbers up by category and shows a store wide summary across the top, so you can see profit by product line without touching a spreadsheet formula
- Flags any SKU that has no cost price saved, because those quietly drag your margin numbers off without any warning
- Exports whatever view you are looking at into a new Google Sheet, ready to hand to your accountant

## What you'll need

- A BigCommerce store login, with permission to read orders and products
- Cost prices filled in on your products in BigCommerce, since that is what the margin math is based on
- A Google account, if you want the export to spreadsheet button

## Prompt

Build me a BigCommerce margin and product performance explorer: an app I open whenever I need a number my store admin refuses to give me. The centrepiece is a report builder where I choose a date range and a grouping, apply several filters at once, and get a table of units sold, revenue, cost of goods and gross margin for every SKU.

Across the top, show a store wide summary tile row for the current filter set: total units sold, total revenue, total cost of goods, total gross margin, and margin percentage. Underneath that, the report builder controls: a date range picker, a grouping selector with day, week, month and quarter options, and filters for SKU, product name, category and order status. All filters must apply at the same time, since filtering by SKU and time period together, or by order status and product keyword together, is exactly what the native reporting cannot do.

The main table is one row per SKU per grouping bucket, with columns for period, SKU, product name, category, units sold, revenue, cost of goods and gross margin, plus margin percentage. Give me a second view that rolls the same numbers up by category so I can see gross profit by product line. Let me toggle between units and revenue as the primary sort, and default to units, because I usually care about units for fulfilment and stock planning rather than income.

For the data: handlers should read order history with BigCommerce Get All Orders, scoped by the selected date range and order status, then call List Order Products for each order to get line level SKU, quantity and sale price. Join that against Get All Products to pick up cost_price and product name, and Get All Categories to resolve the category rollup. Use Get All Order Statuses to populate the status filter from the store's real statuses rather than a hardcoded list, and Get a Catalog Summary if you want a cheap catalog wide count for context, such as how many catalogue SKUs actually sold in the period.

Compute margin at the line level as (sale price minus cost price) times quantity, then aggregate up into the SKU row, the period bucket, the category rollup and the summary tiles. Exclude refunded and cancelled orders by default, but keep order status as a filter I can change. Any SKU with no cost price set must be clearly flagged in the table, ideally with a warning icon and a count in the summary row, because a missing cost price silently breaks the margin math and makes profit look better than it is. Do not treat a missing cost as zero cost in the totals: keep those SKUs visible with units and revenue intact, and make clear their margin is unknown.

Add an Export button that pushes whatever view I am currently looking at, meaning the current filters, grouping and sort, into Google Sheets. Use Create Spreadsheet to make a new sheet named for the report and date range, then Append Values to write a header row and the data rows. Show me the resulting spreadsheet link when it finishes so I can hand it to my accountant.

Some API details worth baking in. BigCommerce V2 order endpoints return bare arrays with no pagination envelope, and return 204 No Content when there are no results, so page until an empty response rather than reading a total. V3 catalog endpoints wrap results in a data and meta envelope with meta.pagination, so page using meta.pagination.total_pages. Maximum page limit is 250 on both, so use that to keep the number of round trips down. Money values come back as decimal strings such as "12.50" rather than integer cents, so parse them before doing any arithmetic. Fetching line items per order can mean a lot of calls on a wide date range, so batch and cache the product and category lookups rather than refetching them per order, and show a loading state that makes progress visible on longer ranges.

## How to customize

- Change which order statuses are left out by default, if you count refunded or cancelled orders differently
- Switch the default sort between units sold and revenue, depending on whether you are planning stock or reviewing income
- Adjust the default date range and grouping the report opens on, so your most common question is one click away

## FAQ

### Do I need cost prices set up in BigCommerce for this to work?

Yes, for the margin and profit columns. The app reads the cost price saved on each product and compares it to what the item actually sold for. Any SKU missing a cost price is clearly flagged in the table so you know which rows to trust, and units and revenue still work fine for those products.

### Can I filter by product and date range at the same time?

Yes, and that is the main reason this exists. You can narrow by SKU, product name, category and order status together, over whatever date range you pick, which is the combination the built in store reports make you export to a spreadsheet to get.

### Are refunded and cancelled orders included in the numbers?

No, they are excluded by default so your revenue and margin reflect what you actually kept. Order status is a filter you control, so you can add them back in or focus on a single status when you need to.

### Can I see units sold instead of revenue?

Yes. There is a toggle for whether units or revenue drives the sort and the headline numbers. Units is the default, since that is usually what you need when planning stock and fulfilment.

### How do I get the numbers to my accountant?

Use the export button. It creates a new Google Sheet containing exactly the view you are looking at, including your filters and grouping, so you can share the link or download it as a file.

### How far back can I run a report?

Any date range your store has order history for. Longer ranges read more orders so they take a little longer to load, and it is usually quicker to look at a quarter at a time than a full year.

Use this prompt in General Input: https://www.generalinput.com/prompts/bigcommerce-margin-and-product-performance-explorer