How to Build Automated Reporting for Ecommerce Stores

pip install requests pandas openpyxl

import requests
import pandas as pd
from datetime import datetime, timedelta

class ShopifyClient:
    """Simple Shopify Admin API client."""

    def __init__(self, shop_url, api_token, api_version="2024-01"):
        self.base_url = f"https://{shop_url}/admin/api/{api_version}"
        self.headers = {
            "X-Shopify-Access-Token": api_token,
            "Content-Type": "application/json",
        }

    def get(self, endpoint, params=None):
        """Fetch data from a Shopify API endpoint with pagination."""
        all_results = []
        url = f"{self.base_url}/{endpoint}.json"

        while url:
            response = requests.get(url, headers=self.headers, params=params, timeout=30)
            response.raise_for_status()
            data = response.json()

            # Extract the resource name from endpoint
            resource = endpoint.split("/")[-1]
            all_results.extend(data.get(resource, []))

            # Handle pagination via Link header
            link = response.headers.get("Link", "")
            url = None
            if 'rel="next"' in link:
                for part in link.split(","):
                    if 'rel="next"' in part:
                        url = part.split(";")[0].strip(" <>")
            params = None  # Only use params on first request

        return all_results

def fetch_orders(client, days_back=30):
    """Fetch recent orders and return as clean DataFrame."""
    since = (datetime.now() - timedelta(days=days_back)).strftime("%Y-%m-%dT00:00:00Z")

    raw_orders = client.get("orders", params={
        "created_at_min": since,
        "status": "any",
        "limit": 250,
    })

    records = []
    for order in raw_orders:
        records.append({
            "order_id": order["id"],
            "order_number": order["order_number"],
            "created_at": order["created_at"],
            "total_price": float(order["total_price"]),
            "subtotal_price": float(order.get("subtotal_price", 0)),
            "total_discounts": float(order.get("total_discounts", 0)),
            "total_tax": float(order.get("total_tax", 0)),
            "financial_status": order["financial_status"],
            "fulfillment_status": order.get("fulfillment_status", "unfulfilled"),
            "items_count": len(order.get("line_items", [])),
            "customer_id": order.get("customer", {}).get("id"),
            "currency": order["currency"],
        })

    df = pd.DataFrame(records)
    df["created_at"] = pd.to_datetime(df["created_at"])
    df["date"] = df["created_at"].dt.date

    print(f"Fetched {len(df)} orders from last {days_back} days")
    return df

def calculate_kpis(orders_df):
    """Calculate key ecommerce KPIs from order data."""
    total_orders = len(orders_df)
    total_revenue = orders_df["total_price"].sum()
    avg_order_value = orders_df["total_price"].mean()
    total_discounts = orders_df["total_discounts"].sum()

    # Refund rate
    refunded = orders_df[orders_df["financial_status"] == "refunded"]
    refund_rate = len(refunded) / total_orders * 100 if total_orders > 0 else 0

    # Fulfillment rate
    fulfilled = orders_df[orders_df["fulfillment_status"] == "fulfilled"]
    fulfillment_rate = len(fulfilled) / total_orders * 100 if total_orders > 0 else 0

    # Unique customers
    unique_customers = orders_df["customer_id"].nunique()

    # Repeat customer rate
    customer_orders = orders_df.groupby("customer_id").size()
    repeat_customers = (customer_orders > 1).sum()
    repeat_rate = repeat_customers / unique_customers * 100 if unique_customers > 0 else 0

    return {
        "Total Orders": total_orders,
        "Total Revenue": round(total_revenue, 2),
        "Average Order Value": round(avg_order_value, 2),
        "Total Discounts Given": round(total_discounts, 2),
        "Refund Rate": f"{refund_rate:.1f}%",
        "Fulfillment Rate": f"{fulfillment_rate:.1f}%",
        "Unique Customers": unique_customers,
        "Repeat Customer Rate": f"{repeat_rate:.1f}%",
    }

def daily_revenue_summary(orders_df):
    """Aggregate revenue by day."""
    daily = (
        orders_df.groupby("date")
        .agg(
            orders=("order_id", "count"),
            revenue=("total_price", "sum"),
            avg_order_value=("total_price", "mean"),
            discounts=("total_discounts", "sum"),
        )
        .reset_index()
        .sort_values("date")
    )
    daily["revenue"] = daily["revenue"].round(2)
    daily["avg_order_value"] = daily["avg_order_value"].round(2)
    return daily

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

def generate_ecommerce_report(kpis, daily_data, output_path):
    """Generate formatted Excel report with KPIs and daily breakdown."""
    wb = Workbook()
    header_font = Font(bold=True, color="FFFFFF", size=11)
    header_fill = PatternFill(start_color="2F5496", end_color="2F5496", fill_type="solid")
    currency_fmt = "£#,##0.00"

    # --- KPI Summary Sheet ---
    ws = wb.active
    ws.title = "KPI Summary"
    ws.merge_cells("A1:B1")
    ws["A1"] = f"Ecommerce KPIs — {datetime.now().strftime('%B %Y')}"
    ws["A1"].font = Font(bold=True, size=16)

    row = 3
    for metric, value in kpis.items():
        ws.cell(row=row, column=1, value=metric).font = Font(bold=True)
        cell = ws.cell(row=row, column=2, value=value)
        if isinstance(value, float):
            cell.number_format = currency_fmt
        row += 1

    ws.column_dimensions["A"].width = 25
    ws.column_dimensions["B"].width = 18

    # --- Daily Revenue Sheet ---
    ws2 = wb.create_sheet("Daily Revenue")
    headers = ["Date", "Orders", "Revenue", "Avg Order Value", "Discounts"]

    for col, h in enumerate(headers, 1):
        cell = ws2.cell(row=1, column=col, value=h)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal="center")

    for i, row_data in daily_data.iterrows():
        r = i + 2
        ws2.cell(row=r, column=1, value=str(row_data["date"]))
        ws2.cell(row=r, column=2, value=row_data["orders"])
        ws2.cell(row=r, column=3, value=row_data["revenue"]).number_format = currency_fmt
        ws2.cell(row=r, column=4, value=row_data["avg_order_value"]).number_format = currency_fmt
        ws2.cell(row=r, column=5, value=row_data["discounts"]).number_format = currency_fmt

    for col in ws2.columns:
        max_len = max(len(str(c.value or "")) for c in col)
        ws2.column_dimensions[col[0].column_letter].width = max_len + 4

    wb.save(output_path)
    print(f"Report saved: {output_path}")

import os

def run_ecommerce_report():
    """Full pipeline: fetch → aggregate → report."""
    client = ShopifyClient(
        shop_url=os.environ["SHOPIFY_STORE"],
        api_token=os.environ["SHOPIFY_API_TOKEN"],
    )

    # Fetch
    orders = fetch_orders(client, days_back=30)

    # Aggregate
    kpis = calculate_kpis(orders)
    daily = daily_revenue_summary(orders)

    # Report
    timestamp = datetime.now().strftime("%Y%m%d")
    generate_ecommerce_report(kpis, daily, f"ecommerce_report_{timestamp}.xlsx")

    # Print KPIs
    for metric, value in kpis.items():
        print(f"  {metric}: {value}")

if __name__ == "__main__":
    run_ecommerce_report()

Fetched 312 orders from last 30 days
  Total Orders: 312
  Total Revenue: 39420.50
  Average Order Value: 126.35
  Total Discounts Given: 4280.00
  Refund Rate: 3.2%
  Fulfillment Rate: 89.1%
  Unique Customers: 245
  Repeat Customer Rate: 18.4%
Report saved: ecommerce_report_20260330.xlsx