How to Use Product Category Mix Analysis in Squarespace: Step-by-Step Tutorial

Introduction to Product Category Mix Analysis

Understanding which product categories drive the most revenue is fundamental to running a successful e-commerce business on Squarespace. Product category mix analysis helps you answer critical questions: Are you over-investing in low-performing categories? Should you expand your bestselling lines? Is your inventory allocation aligned with customer demand?

Unlike simple sales reports that show total revenue, product category mix analysis reveals the underlying patterns in your business. It shows which categories bring in the largest share of revenue and how concentrated that revenue is. Profit is a separate question: the order export has no cost column, so margins need cost data you add yourself. This analytical approach transforms raw sales data into actionable insights that directly impact your profitability.

In this comprehensive tutorial, you'll learn how to perform a complete product category mix analysis for your Squarespace store. We'll walk through data extraction, preparation, analysis, and interpretation, giving you the knowledge to make confident, data-driven decisions about your product portfolio.

Prerequisites and Data Requirements

• A Squarespace Commerce site with order history and permission to export orders
• A product list with a category for each product. Squarespace's help page on exporting Commerce orders lists no category column among the export fields, so the category comes from your own list, not from the file
• One currency. Totals in mixed currencies cannot be added together
• Python with pandas, or a spreadsheet pivot table if you prefer to compute the same totals there

Decide up front what to do with test orders and with refunded or cancelled orders: include them, exclude them or net them out on purpose. The help page notes that for partially refunded orders "Lineitem price" shows the full purchase price, so revenue computed from price and quantity is before refunds.

Step-by-Step Analysis Process

Step 1: Export Your Squarespace Orders

Export your orders as a CSV by following Squarespace's help page on exporting Commerce orders. The page lists "Lineitem name", "Lineitem sku", "Lineitem quantity" and "Lineitem price" among the export fields. It also says that order-wide details, such as the shipping address, appear only in the first row of each order ID and that the other rows show line item details only. This tutorial uses only line item columns, so the rule does not affect it. If you add an order-level column such as a discount or a total, count it once per order (for example by keeping the first row of each Order ID) or it will be added in once for every line.

The code below builds a small synthetic file in the same layout. It is made up for this tutorial and does not come from any store; every figure in the outputs on this page comes from it.

import pandas as pd

# Synthetic line items made up for this tutorial. Column names follow the Squarespace
# order export (one row per line item); the values do not come from any store.
lines = pd.DataFrame({
    "Order ID": [1001, 1002, 1002, 1003, 1004, 1005, 1005, 1006, 1007, 1008, 1009, 1010, 1010],
    "Lineitem name": ["Leather Wallet", "Cotton T-Shirt", "Baseball Cap", "Yoga Mat", "Cotton T-Shirt",
                      "Hoodie", "Leather Wallet", "Gift Card", "Resistance Band", "Candle", "Hoodie",
                      "Candle", "Sticker Pack"],
    "Lineitem quantity": [1, 2, 1, 1, 3, 1, 1, 1, 2, 2, 2, 1, 1],
    "Lineitem price": [45.00, 25.00, 25.00, 65.00, 25.00, 60.00, 45.00, 50.00, 15.00, 18.00, 60.00, 18.00, 5.00],
})
print(lines.to_csv(index=False))

Output:

Order ID,Lineitem name,Lineitem quantity,Lineitem price
1001,Leather Wallet,1,45.0
1002,Cotton T-Shirt,2,25.0
1002,Baseball Cap,1,25.0
1003,Yoga Mat,1,65.0
1004,Cotton T-Shirt,3,25.0
1005,Hoodie,1,60.0
1005,Leather Wallet,1,45.0
1006,Gift Card,1,50.0
1007,Resistance Band,2,15.0
1008,Candle,2,18.0
1009,Hoodie,2,60.0
1010,Candle,1,18.0
1010,Sticker Pack,1,5.0

Step 2: Map Products to Categories and Check the Gaps

Because the export has no category column, add one by mapping each product name to a category. Then list what the map missed, so those lines are not silently dropped. Spelling matters here: "Apparel" and "apparel" would count as two categories, so keep the names in your map consistent.

# The export has no category column. Map each product name to a category yourself,
# for example from your own product list. Products left out of the map stay blank.
category_of = {
    "Leather Wallet": "Accessories", "Baseball Cap": "Accessories",
    "Cotton T-Shirt": "Apparel", "Hoodie": "Apparel",
    "Yoga Mat": "Fitness", "Resistance Band": "Fitness",
    "Candle": "Home Goods", "Gift Card": "Gift Cards",
}
lines["Category"] = lines["Lineitem name"].map(category_of)

missing = lines[lines["Category"].isna()]
print("Line items with no category:", len(missing), "->", sorted(missing["Lineitem name"].unique()))
print("Categories found:", sorted(lines["Category"].dropna().unique()))

Output:

Line items with no category: 1 -> ['Sticker Pack']
Categories found: ['Accessories', 'Apparel', 'Fitness', 'Gift Cards', 'Home Goods']

"Sticker Pack" is not in the map, so it is reported instead of crashing the check. Add it to the map, or leave it in an explicit "(uncategorized)" group as the next step does.

Step 3: Compute Revenue by Category

Line revenue is quantity times price. Group by category, count the distinct orders each category appears in (an order with items in two categories counts once in each, so the order counts add up to more than the number of orders), and divide each category's revenue by the grand total. The last lines compute the Herfindahl-Hirschman index (HHI), the sum of the squared revenue shares.

lines["Category"] = lines["Category"].fillna("(uncategorized)")
lines["line_revenue"] = lines["Lineitem quantity"] * lines["Lineitem price"]

mix = lines.groupby("Category").agg(
    orders=("Order ID", "nunique"),
    units=("Lineitem quantity", "sum"),
    revenue=("line_revenue", "sum"),
).sort_values("revenue", ascending=False)
mix["share_pct"] = 100 * mix["revenue"] / mix["revenue"].sum()
print(mix.round(1).to_string())
print("Total revenue:", mix["revenue"].sum())

shares = mix["revenue"] / mix["revenue"].sum()
hhi = (shares ** 2).sum()
print("Categories:", len(mix), "| HHI:", round(hhi, 3), "| lowest possible with this many categories:", round(1 / len(mix), 3))

Output:

                 orders  units  revenue  share_pct
Category                                          
Apparel               4      8    305.0       48.9
Accessories           3      3    115.0       18.4
Fitness               2      3     95.0       15.2
Home Goods            2      3     54.0        8.7
Gift Cards            1      1     50.0        8.0
(uncategorized)       1      1      5.0        0.8
Total revenue: 624.0
Categories: 6 | HHI: 0.31 | lowest possible with this many categories: 0.167

The revenue column adds up to 624.0 and the shares to 100.0%, so the table reconciles with the total printed under it.

Step 4: Read the Shares and the HHI

• Share: Apparel brings in 48.9% of revenue here from 4 of the 10 orders. A single category near half of revenue means a change in that category moves the whole store, which is a fact about this table, not yet a judgment about risk.
• HHI: 0.31 with 6 categories. The lowest an HHI can be is 1 divided by the number of categories (0.167 for 6, 0.20 for 5, 0.10 for 10), reached when every category has the same share, and the highest is 1.0 when one category has everything. So an HHI has to be read against the category count, and a fixed cut-off such as "below 0.15 is safe" cannot be reached with fewer than 7 categories. Compare your HHI with 1 divided by your number of categories, and with your own HHI from earlier periods.
• Taxonomy: shares and HHI depend on how you cut the categories. Merging two categories raises the HHI and splitting one lowers it, with no change in the business.
• Revenue is not profit: a large share can come with a thin margin. Add unit cost per product before ranking categories by value.

Step 5: Decide What to Test

The table says where revenue comes from; it does not say what would happen if you changed the mix. Reasonable next moves are questions to test, for example: does featuring a smaller category lift its sales, and is a very small category worth its stock? Judge a change by comparing a period before and after (or a planned test with a comparison group) and allow for seasonality, because a few orders per month make category shares noisy. See A/B testing and statistical significance.

Run It on Your Own Squarespace Export

Export your orders from Squarespace as a CSV and upload it at MCP Analytics. The analysis runs a revenue breakdown by product category and a concentration index on your file and shows the result with the method used. You choose the columns; nothing connects to your Squarespace account.

Try it on your data

Common Issues and Solutions

Issue 1: Many Lines Fall Into "(uncategorized)"

The map in Step 2 is missing product names, or the names in the file differ from the names in the map (a renamed product, a variant name, a trailing space). Print the unmapped names as Step 2 does and add them to the map.

Issue 2: Revenue Does Not Match Squarespace's Own Reports

• Check that the date ranges match, including the time zone.
• Revenue here is price times quantity: before discounts, shipping and tax, and before partial refunds (see the help page note above). Compare like with like.
• Check for test orders and for more than one currency in the file.

Issue 3: A Category Swings Wildly From Month to Month

This can be seasonality or just a small number of orders. Compare the same month across years if you have them, and treat a share built on a handful of orders as a rough guide only.

Next Steps with Squarespace Analytics

• Add unit cost per product and compute margin by category.
• Split the same table by month to see which shares are stable and which move.
• Look at product-level detail inside the biggest category, as in the SKU-level performance tutorial.

Conclusion

The export gives you line items, you supply the categories, and a short pandas script turns them into revenue shares and a concentration index you can reconcile to the total. The numbers describe the past; use them to choose what to test next.