Mastering Multi-SKU Order Analysis in BigCommerce: From Export to API

Mastering Multi-SKU Order Analysis in BigCommerce: From Export to API

Understanding customer purchasing patterns is crucial for any e-commerce business. A common challenge for BigCommerce merchants is efficiently identifying and compiling a list of orders that contain multiple unique products (SKUs). This insight, drawn from a BigCommerce forum discussion, provides practical, step-by-step methods ranging from simple exports to advanced API integrations, empowering merchants and developers to gain deeper insights into their multi-item transactions.

The Core Challenge: Identifying Orders with Multiple Products

The original question from Cherie Sims sought a way to compile a list of completed orders that contained more than one SKU. This seemingly straightforward request often requires a bit of data manipulation, as BigCommerce's standard exports don't always provide this specific metric directly in an easily digestible format.

Solution 1: Quick & Practical Export with Spreadsheet Analysis

For most BigCommerce users, especially those dealing with moderate order volumes or needing a one-off report, the most accessible method involves using the platform's native order export functionality combined with spreadsheet tools like Excel or Google Sheets. This approach was thoroughly detailed in the forum replies:

• Step 1: Export Detailed Orders. Navigate to Orders > View Orders in your BigCommerce control panel. Filter by Order Status = Completed (or any other relevant status). Crucially, when exporting, select the “Detailed Orders” option. This export type includes individual line items/SKUs, which is essential for our analysis.
• Step 2: Process in a Spreadsheet. Open the exported CSV file in Excel or Google Sheets. In this detailed export, each row represents a single product (SKU) within an order. Orders containing multiple SKUs will appear as multiple rows sharing the same Order ID.
• Step 3: Identify Multi-SKU Orders.
  • Using a Pivot Table (Recommended for Cleaner Output):
    • Create a Pivot Table.
    • Set Rows to Order ID.
    • Set Values to Count of SKU (or Count of Product Name, etc.).
    • Filter the Pivot Table to show only rows where Count of SKU > 1. This will give you a clean list of only the Order IDs that contained multiple products.
  • Using COUNTIF (for a quick check): Add a new column to your raw data and use a COUNTIF function (e.g., =COUNTIF(A:A,A2) if Order ID is in column A) to count how many times each Order ID appears. Filter this new column for values greater than 1.

This method is highly effective for ad-hoc reporting and provides a clear, actionable list of multi-SKU orders without requiring any coding expertise.

Solution 2: Leveraging the BigCommerce API for Scalability and Automation

For stores with large order volumes (where the detailed export might cap out around 2,000 rows, as noted by Sajid Jameel) or for businesses requiring automated, scheduled reports, integrating with the BigCommerce Orders API is the superior solution. This approach offers greater flexibility, avoids manual data processing, and can be integrated into custom dashboards or reporting tools.

• Step 1: Access the Orders API. Utilize the BigCommerce Orders API (specifically the /v2/orders endpoint).
• Step 2: Fetch Orders and Products.
  • Fetch orders, filtering by status_id=10 for 'Completed' orders (or other relevant status IDs).
  • For each order, fetch its associated products using the /v2/orders/{id}/products endpoint.
• Step 3: Implement Logic to Count SKUs. Loop through the retrieved orders. For each order, count the number of products (SKUs) associated with it. If the count is greater than one, include that order in your custom report.

This API-driven method is ideal for building custom reporting tools, integrating with business intelligence platforms, or performing advanced analysis like commission calculations based on product bundles. It eliminates the manual "export-and-pivot dance" and offers a robust solution for ongoing data needs.

Community Consensus and Further Considerations

The forum thread highlighted the effectiveness of both methods, with the author confirming the "super helpful" nature of the replies. While the manual export is excellent for one-off tasks, the API approach provides a scalable foundation for more complex analytical requirements. Developers also pointed out that custom reporting tools can further streamline this process, moving beyond the need for manual CSV manipulation altogether.

By understanding and implementing these strategies, BigCommerce merchants can efficiently identify multi-SKU orders, unlocking valuable insights for inventory management, marketing strategies, and overall business growth.