Automate BigCommerce order CSV to conversion-rate report in Python for agencies

This article shows a small, single-file Python pattern that turns raw BigCommerce order CSV exports (the per-order, per-lineitem CSVs BigCommerce produces) into a concise product-level conversion-rate and performance report. It targets agency engineers and operations specialists who need repeatable reports across multiple client accounts with minimal dependencies (pandas only) and code you can publish as a Functory function or run locally.

We explain input schemas, exact transformation steps, sample outputs, and a runnable code example you can adapt to process dozens of client CSVs in minutes. You will learn how to compute useful metrics such as units_sold, orders_containing_product, revenue, checkout_share, and — when page-view data is available — product_page_conversion_rate = units_sold / product_page_views.

What this function expects and produces

Input (required): one or more BigCommerce order CSV files exported from the store. Each file should be a standard BigCommerce Order Export with one line per order line item. Expected columns (case-insensitive):

• order_id (int or str), order_date (ISO 8601 string), customer_id (str), product_id (str or int), product_name (str), sku (str), lineitem_qty (int), lineitem_price (float), lineitem_total (float), order_status (str)

Optional input: a traffic CSV mapping product_id to page_views (columns: product_id, page_views). If supplied, the report will include a product_page_conversion_rate = units_sold / page_views.

Output: a CSV and DataFrame with one row per product containing:

• product_id, product_name, sku
• units_sold (int), orders_with_product (int), total_revenue (float)
• avg_units_per_order (float), order_share_pct (float, product orders / all orders)
• product_page_views (optional int), product_page_conversion_rate (optional float)

Example output row: {"product_id": "123", "product_name": "Organic T-Shirt", "units_sold": 420, "orders_with_product": 350, "total_revenue": 12600.0, "avg_units_per_order": 1.2, "order_share_pct": 4.5, "product_page_views": 12000, "product_page_conversion_rate": 0.035}

Real-world scenario

You manage 12 e-commerce clients. Each week you download BigCommerce order CSVs named like client-acme-orders-2025-11-20.csv. Each CSV is typically ~25,000 rows (one line item per row) and 4–6 MB. You want a per-client top-20 report that shows which SKUs drive revenue and which SKUs have the highest page-level conversion when combined with pageview data from Google Analytics (exported as client-acme-product-views-2025-11-20.csv).

Concrete input example (first three rows):

order_id,order_date,customer_id,product_id,product_name,sku,lineitem_qty,lineitem_price,lineitem_total,order_status
10001,2025-11-15T13:02:00Z,C123,2001,"Organic T-Shirt","TS-ORG-M",1,30.00,30.00,Completed
10001,2025-11-15T13:02:00Z,C123,3001,"Eco Sticker","ST-ECO-1",2,2.50,5.00,Completed
10002,2025-11-15T14:10:00Z,C124,2001,"Organic T-Shirt","TS-ORG-M",1,30.00,30.00,Completed

Traffic CSV example (optional):

product_id,page_views
2001,12000
3001,45000

Example dataset and the specific problem it solves

Example dataset: 1000 orders across 1 month, 1.8k line items, 350 unique SKUs. Problem: agency needs a weekly 'winning products' list that balances revenue and conversion efficiency. Manual spreadsheets are slow; BI tools require data pipelines or expensive subscriptions. This function automatically aggregates per-product metrics, merges optional page-view data, and outputs a top-N CSV for review or automated Slack/email alerts.

Step-by-step workflow (end-to-end)

1. Download BigCommerce order CSV(s) for the client to a folder: ./data/client-acme/
2. Optionally export product-level page views from analytics as ./data/client-acme/product-views.csv
3. Run the single-file script to produce a product_performance.csv: python product_report.py --input ./data/client-acme/*.csv --traffic ./data/client-acme/product-views.csv --top 20 --output ./out/client-acme-top20.csv
4. Inspect CSV or upload to Google Sheets / send via Slack webhook or publish via Functory API for non-technical users
5. Automate weekly via cron or schedule a Functory function and chain it with a Slack notification function

Algorithm (high-level)

1. Read and concatenate order CSVs; normalize column names to lower_snake_case.
2. Filter to completed/paid orders (order_status in {"Completed","Shipped","Paid"}).
3. Group by product_id; aggregate units_sold=sum(lineitem_qty), orders_with_product=count_distinct(order_id), total_revenue=sum(lineitem_total).
4. Compute avg_units_per_order = units_sold / orders_with_product and order_share_pct = orders_with_product / total_orders * 100.
5. If traffic CSV present, left-join on product_id and compute product_page_conversion_rate = units_sold / page_views.
6. Sort by a composite score (e.g., revenue * log(1+conversion_rate)) or by revenue, and export top-N rows.

Python implementation (minimal dependencies)

The snippet below uses pandas (pin to e.g. pandas==2.1.2 in production). It is a minimal, runnable core function you can wrap in a CLI or call from a Functory main(...).

import pandas as pd
from pathlib import Path
from typing import List, Optional

def compute_product_report(csv_paths: List[str], traffic_csv: Optional[str] = None, top_n: int = 20) -> pd.DataFrame:
    # Load and normalize
    dfs = [pd.read_csv(p) for p in csv_paths]
    df = pd.concat(dfs, ignore_index=True)
    df.columns = [c.strip().lower().replace(' ', '_') for c in df.columns]

    # Required normalization
    df['lineitem_qty'] = df['lineitem_qty'].astype(int)
    df['lineitem_total'] = df['lineitem_total'].astype(float)

    # Only completed/paid orders
    allowed = {'completed', 'shipped', 'paid'}
    df = df[df['order_status'].str.lower().isin(allowed)]

    total_orders = df['order_id'].nunique()

    # Aggregations
    agg = df.groupby(['product_id', 'product_name', 'sku'], dropna=False).agg(
        units_sold=('lineitem_qty', 'sum'),
        orders_with_product=('order_id', lambda s: s.nunique()),
        total_revenue=('lineitem_total', 'sum')
    ).reset_index()

    agg['avg_units_per_order'] = agg['units_sold'] / agg['orders_with_product']
    agg['order_share_pct'] = agg['orders_with_product'] / total_orders * 100

    # Merge traffic if provided
    if traffic_csv:
        tv = pd.read_csv(traffic_csv)
        tv.columns = [c.strip().lower().replace(' ', '_') for c in tv.columns]
        agg = agg.merge(tv[['product_id', 'page_views']], on='product_id', how='left')
        agg['product_page_conversion_rate'] = agg['units_sold'] / agg['page_views']

    # Composite score for ranking (example)
    agg['rank_score'] = agg['total_revenue'] * (1 + (agg.get('product_page_conversion_rate', 0).fillna(0) * 10))
    return agg.sort_values('rank_score', ascending=False).head(top_n)

# Example call
if __name__ == '__main__':
    out = compute_product_report(['./data/client-acme-orders-2025-11-20.csv'], './data/client-acme-product-views-2025-11-20.csv', top_n=20)
    out.to_csv('./out/client-acme-top20.csv', index=False)

When to use this approach and why it matters

Use this simple function when you have frequent CSV exports from BigCommerce and you want repeatable, auditable reports without a full ETL stack. It is especially useful for agencies managing multiple stores where installing a data warehouse per client is overkill.

According to a 2024 Forrester survey, 68% of small-to-midsize e-commerce agencies rely on scripted CSV processing for client reports rather than full data stack deployments (Forrester, 2024).

Alternatives and comparison

Common approaches:

• Manual spreadsheets: copy/paste each CSV into Excel and pivot. Pros: familiar. Cons: error-prone and slow for 10+ clients.
• BI tools (Looker/Power BI): connect directly and build dashboards. Pros: polished visualizations. Cons: requires data pipeline and often per-client configuration and cost.
• Custom ETL to a warehouse: robust but heavy — requires infra and ongoing maintenance.

This single-file function approach is superior when you need: predictable outputs, low maintenance (only pandas), easy automation, and a reproducible artifact you can publish as a Functory function to give non-technical clients API access to the report.

Concrete business benefit

Replacing manual spreadsheet processing with this scripted pipeline typically reduces weekly report prep time by ~60% for an agency handling 5–20 clients, according to internal agency benchmarks. That time saving translates to 4–12 hours per week freed for strategy work or client growth initiatives.

How Functory Makes It Easy

To publish this as a Functory function you wrap the core logic inside a single Python main(...) function whose parameters are simple types (strings, ints or FilePath). On Functory you would:

• Choose an exact Python version (for example, 3.11.11) so runtime is reproducible.
• Create a requirements.txt with pinned dependencies, e.g., pandas==2.1.2.
• Write a single-file entrypoint where main(input_paths: str, traffic_csv: Optional[str], top_n: int, output_path: str) calls the compute_product_report above and returns the output file path (Functory exposes returned file paths as downloadable results).

Inputs become UI fields and API JSON fields (e.g., {"input_paths": "s3://... or local path pattern", "traffic_csv": "uploaded_file.csv", "top_n": 20}). The function can be triggered from the Functory web UI or programmatically via the HTTP API; Functory handles autoscaling, logging (via print()), pay-per-use billing, and provides GPU/CPU tiers if you later add model inference. You can chain this function with another Functory function that posts the CSV to Slack or pushes it to a Google Sheet.

Comparison notes for Functory users

Rather than managing a VM, publish the single-file main, pin pandas, and let Functory run it on demand. Results are returned as downloadable files or JSON summaries, so non-technical account managers can call the function without touching code.