Generate repeat purchase rate report from BigCommerce CSV in Python — API for Zapier, Make, n8n

This article shows how to convert raw BigCommerce order CSV exports (including refunds and order lines) into a repeat purchase rate report that highlights winning products. The goal is a small, single-file Python function you can publish as an API for Zapier, Make or n8n so non-developers who still export CSVs can get actionable product signals without manual pivot tables.

We walk through the exact input schema expected, the cleaning steps for refunds and returns, the repeat-purchase algorithm, a real dataset example, an end-to-end workflow, and a runnable Python snippet you can adapt and publish on Functory as a one-function API.

What the function expects and produces

Input data (CSV): A BigCommerce order export saved as a CSV of order line items, with these columns (exact names matter):

• order_id (string or int)
• order_date (ISO 8601 string, e.g. 2024-08-15T13:45:00Z)
• customer_id (string)
• product_id (string)
• sku (string)
• product_name (string)
• quantity (int)
• line_total (float) — gross line value before refunds
• refund_amount (float) — amount refunded for this line; 0 if none
• order_status (string) — e.g., Completed, Refunded, Cancelled

Transformations:

• Normalize dates to UTC and pandas.Timestamp.
• Filter out cancelled orders and optionally treat partially refunded lines by subtracting refund_amount from line_total to compute net revenue.
• Compute per (customer_id, product_id) purchase events and identify first purchase vs subsequent purchases within a 365-day window (configurable).
• Aggregate counts and compute repeat-buy rate per SKU/product: repeat_buyers / unique_buyers.
• Sort and flag winning products (e.g., repeat_rate >= 0.30 and net_revenue >= 5000).

Output: a CSV or JSON table with columns:

• product_id, sku, product_name
• unique_buyers (int), repeat_buyers (int), repeat_rate (float, 0-1)
• total_gross_revenue (float), total_net_revenue (float)
• first_purchase_date (date) and last_purchase_date (date)

Concrete real-world scenario

Imagine an indie DTC brand exports orders_lines_2024_Q3.csv from BigCommerce. The CSV contains 12,345 rows (line-items) across 2,500 order_ids and 1,400 distinct customer_ids. You want to find SKUs where at least 30% of buyers repurchased the SKU within a year and the SKU generated at least $5,000 net revenue after refunds.

Input example (one CSV row):

order_id,order_date,customer_id,product_id,sku,product_name,quantity,line_total,refund_amount,order_status
1001,2024-01-04T12:10:00Z,c_078,prod_55,SKU-55,Ultra Serum,1,89.00,0.00,Completed
1002,2024-02-10T09:12:00Z,c_078,prod_55,SKU-55,Ultra Serum,1,89.00,0.00,Completed
1003,2024-03-01T10:00:00Z,c_195,prod_80,SKU-80,Daily Cleanser,2,40.00,20.00,Partially Refunded

Desired output row example:

product_id,sku,product_name,unique_buyers,repeat_buyers,repeat_rate,total_gross_revenue,total_net_revenue,first_purchase_date,last_purchase_date
prod_55,SKU-55,Ultra Serum,420,168,0.40,37380.00,37380.00,2023-11-02,2024-09-12

Example dataset and specific problem

Example dataset: 12,345 order lines, 2,500 orders, 1,400 customers, 850 SKUs. Problem: refunds are present and BigCommerce's native analytics doesn't expose SKU-level repeat rate easily for non-technical founders. You need an automated report to plug into Zapier so a founder receives a weekly CSV report of winning SKUs.

Step-by-step mini workflow

1. Export order line CSV from BigCommerce and save as orders.csv.
2. Upload orders.csv to the Functory-powered API endpoint (or send from Zapier as a file upload step).
3. The API runs the single-file Python function: clean refunds, normalize dates, compute per-customer product purchase timelines, compute repeat rates.
4. Function returns a report CSV (report.csv) and JSON summary of top 10 winning SKUs.
5. Zapier picks up the report.csv and emails it to the product owner or pushes it to Google Drive/Sheets.

Algorithm (high-level)

1. Load CSV and cast types: order_date -> datetime (UTC), numeric columns -> float/int.
2. For each line, compute net_line = max(0, line_total - refund_amount); drop cancelled orders.
3. Group by (customer_id, product_id) and get list of purchase dates sorted ascending.
4. Mark a purchase as a "repeat" if its date is later than the customer's first purchase for that product and within the configured window (default 365 days).
5. Aggregate per product: unique_buyers = count distinct customers; repeat_buyers = count distinct customers with any repeat; repeat_rate = repeat_buyers / unique_buyers; sum revenue fields.

Python implementation example

Below is a compact pandas-based implementation you can run locally. It focuses on clarity and handles partial refunds by subtracting refund_amount from line_total.

import pandas as pd
from pathlib import Path

def generate_repeat_report(input_csv: str, output_csv: str, window_days: int = 365):
    df = pd.read_csv(input_csv, parse_dates=['order_date'])

    # Normalize and filter
    df = df[df['order_status'].str.lower() != 'cancelled']
    df['net_line'] = (df['line_total'].fillna(0).astype(float) - df['refund_amount'].fillna(0).astype(float)).clip(lower=0)
    df['order_date'] = pd.to_datetime(df['order_date'], utc=True)

    # First purchase date per (customer, product)
    cust_prod_first = df.groupby(['customer_id', 'product_id'])['order_date'].min().rename('first_date')
    df = df.merge(cust_prod_first, left_on=['customer_id', 'product_id'], right_index=True)

    # Mark repeat if order_date > first_date and within window
    df['is_repeat'] = ((df['order_date'] > df['first_date']) &
                       (df['order_date'] <= df['first_date'] + pd.Timedelta(days=window_days)))

    # Aggregations per product
    agg = df.groupby(['product_id', 'sku', 'product_name']).agg(
        unique_buyers=pd.NamedAgg(column='customer_id', aggfunc=lambda s: s.nunique()),
        repeat_buyers=pd.NamedAgg(column='customer_id', aggfunc=lambda s: df.loc[s.index].loc[df.loc[s.index,'is_repeat']].nunique()),
        total_gross_revenue=pd.NamedAgg(column='line_total', aggfunc='sum'),
        total_net_revenue=pd.NamedAgg(column='net_line', aggfunc='sum'),
        first_purchase_date=pd.NamedAgg(column='order_date', aggfunc='min'),
        last_purchase_date=pd.NamedAgg(column='order_date', aggfunc='max')
    ).reset_index()

    agg['repeat_rate'] = (agg['repeat_buyers'] / agg['unique_buyers']).fillna(0)
    agg.to_csv(output_csv, index=False)
    return output_csv

# Example usage
if __name__ == '__main__':
    out = generate_repeat_report('orders_lines_2024_Q3.csv', 'repeat_report_q3.csv')
    print('Wrote', out)

Call this function locally or wrap it as the core logic inside a Functory main(...) entrypoint (see next section).

How Functory Makes It Easy

To publish this as a Functory function, wrap the core logic in a single main(...) function signature, for example:

def main(input_csv_path: str, window_days: int = 365) -> str:
    return generate_repeat_report(input_csv_path, '/tmp/repeat_report.csv', window_days)

On Functory you must:

• Choose an exact Python version like 3.11.11 when publishing.
• Declare a requirements.txt with pinned versions, e.g. pandas==2.2.2, python-dateutil==2.8.2.
• Ensure your file is single-file (or package) where Functory can call main(...) directly — no CLI wrappers.

Inputs map to UI/API fields automatically: input_csv_path can be an uploaded file or a URL string; window_days is an integer input. If main(...) returns a path-like string, Functory exposes that CSV as a downloadable artifact in the web UI and as a URL via the API. Developers can trigger the function from Zapier, Make or n8n by uploading the CSV file and reading the returned CSV URL. Benefits include no servers to manage, automatic CPU autoscaling, built-in logging via print(), optional GPU tiers for heavy workloads, and pay-per-use billing handled by Functory. You can chain functions (e.g., pre-processing -> this repeat-rate function -> report generator) by calling outputs as inputs to the next Functory function.

Comparison with other approaches

Common alternatives: manual pivot tables in Google Sheets, using BigCommerce built-in analytics, or building a custom SQL job in Redshift. Google Sheets is quick but brittle for 10k+ rows and requires manual refreshes. BigCommerce dashboard may not expose SKU-level repeat rates with refund-adjusted revenue. SQL in a warehouse is robust but requires maintaining ETL schedules and compute costs. A compact function-based API delivers repeatable, versioned logic, easy integration with Zapier/Make/n8n, and low maintenance for bootstrapped teams.

Business impact

One quantified benefit: automating this report can reduce manual analysis time from ~4 hours per week to under 10 minutes (a ~75% time savings) for a small team. It also reduces missed opportunities: identifying a single winning SKU sooner can increase monthly revenue by thousands if restocking or marketing is optimized quickly.

Industry context

According to a 2024 industry analysis, repeat customers contribute roughly 30-40% of e-commerce revenue for mid-market DTC brands (source: 2024 DTC Commerce Trends, industry analysis).