Functory
functory.
All Articles
7 min read
Functory

Generate Weekly Inventory Turnover Report from Carrier Shipment CSVs in Python (Functory)

Small teams often get daily CSV exports from carriers (Shippo, EasyPost, DHL, UPS) and need a tight weekly inventory turnover report for standups. This article shows a practical, single-file Python pattern to parse raw shipment exports, merge them with a simple inventory snapshot, compute SKU-level and aggregate turnover metrics, and produce an Excel summary ready for a weekly meeting. We also show how to package this as a Functory function so non-technical team members can run the report from a web UI or an API.

Long-tail phrases covered here include python inventory turnover report from shipments, automate shipment CSV to inventory turnover, and functory function for shipment reporting—phrases developers search when automating end-to-end lightweight ETL and reporting without a data engineering team.

What this function expects and produces

Input data (explicit):

  • One or more carrier CSV files (UTF-8) with columns: shipment_id (str), order_id (str), sku (str), quantity (int), shipped_at (ISO 8601), carrier (str), warehouse (str). Example filename: ups_shipments_2025-01-07.csv.
  • An inventory snapshot CSV with columns: sku (str), on_hand_start (int), on_hand_end (int), cost_usd (float). Snapshot dates should match the week window. Example filename: inventory_snapshot_2025-01-06.csv.

Transformations performed:

  • Concatenate all shipment exports, normalize SKU case and trim whitespace.
  • Filter shipments inside the week window (week_start to week_end inclusive).
  • Aggregate shipped quantity per sku and warehouse.
  • Join with inventory snapshot to compute beginning_inventory, ending_inventory, average_inventory, cost_of_goods_shipped (COGS approximation = shipped_qty * cost_usd), turnover_rate = COGS / average_inventory, and days_on_hand = 365 / turnover_rate.
  • Produce a multi-sheet Excel report: sku-level sheet and an executive summary sheet with top-10 movers and aggregate turnover numbers.

Output produced:

  • Excel file (XLSX) with sheets: sku_metrics, top_movers, summary. Example path: /tmp/inventory_turnover_2025-01-06_to_2025-01-12.xlsx.
  • Optional JSON summary printed to stdout for CI or agent consumption.

Real-world scenario (concrete inputs and expected outputs)

Scenario: an e-commerce brand receives weekly carrier exports from UPS and USPS. They run a weekly snapshot of warehouse inventory every Monday.

Inputs (concrete):

  • ups_shipments_2025-01-07.csv — 680 rows: columns shipment_id, order_id, sku, quantity, shipped_at, carrier, warehouse.
  • usps_shipments_2025-01-07.csv — 322 rows, same schema.
  • inventory_snapshot_2025-01-06.csv — 420 SKUs, columns sku, on_hand_start, on_hand_end, cost_usd.

Expected output (example rows):

  • sku_metrics sheet row: sku="SKU-12345", beginning=120, received=0, shipped=35, ending=85, avg_inventory=102.5, cogs=35*12.50=437.50, turnover=4.27, days_on_hand=85.6.
  • summary: total_shipped=1002 units, weighted_turnover=3.8, top_movers: SKU-98765 (shipped 180 units).

Example dataset description (fabricated but realistic)

Dataset size and nature:

  • ~1,000 rows of shipment lines across 2 carriers over 7 days. Columns include shipped_at timestamps in UTC, sku, and quantity.
  • Inventory snapshot: 420 SKUs with on_hand_start and on_hand_end integers captured Monday 00:00 and next Monday 00:00, plus cost_usd per SKU.

Problem solved:

Small teams typically copy-paste carrier CSVs into spreadsheets, manually dedupe SKUs, and guess turnover. This script provides a repeatable calculation that is auditable (pandas dataframes), reproducible, and produces an immediately shareable XLSX for weekly standups.

Step-by-step mini workflow

  1. Download carrier CSV exports into a folder /data/shipments/week-2025-01-06/.
  2. Place the weekly inventory snapshot at /data/inventory/inventory_snapshot_2025-01-06.csv.
  3. Run the script (or call the Functory API) with week_start=2025-01-06 and output path.
  4. Script writes /reports/inventory_turnover_2025-01-06_to_2025-01-12.xlsx and prints a JSON summary.
  5. Share the XLSX in the weekly standup and paste the summary into Slack.

Algorithm (high-level)

  1. Load and union all carrier CSVs, normalizing sku strings and converting shipped_at to timestamps.
  2. Filter rows where shipped_at is between week_start and week_end.
  3. Group by sku and warehouse: sum(quantity) => shipped_qty.
  4. Load inventory snapshot; join on sku to get on_hand_start and on_hand_end and cost_usd.
  5. Compute avg_inventory = (on_hand_start + on_hand_end) / 2; COGS = shipped_qty * cost_usd; turnover = COGS / (avg_inventory * cost_usd) => shipped_qty / avg_inventory; days_on_hand = 365 / turnover.
  6. Export detailed sku sheet and top movers summary into an XLSX file.

Working Python example

The snippet below is a compact, runnable example using pandas. It demonstrates the core logic; in production you should pin versions and add error handling.

import pandas as pd
from pathlib import Path
from datetime import datetime, timedelta

def generate_inventory_turnover_report(shipments_dir: str, inventory_snapshot_csv: str, week_start: str, output_xlsx: str):
    week_start_dt = datetime.fromisoformat(week_start)
    week_end_dt = week_start_dt + timedelta(days=6, hours=23, minutes=59, seconds=59)

    # Read all CSVs in shipments_dir
    paths = list(Path(shipments_dir).glob('*.csv'))
    df_list = []
    for p in paths:
        df = pd.read_csv(p)
        df['sku'] = df['sku'].astype(str).str.strip().str.upper()
        df['shipped_at'] = pd.to_datetime(df['shipped_at'], utc=True)
        df_list.append(df)
    shipments = pd.concat(df_list, ignore_index=True)

    # Filter by week
    shipments = shipments[(shipments['shipped_at'] >= pd.Timestamp(week_start_dt, tz='UTC')) & (shipments['shipped_at'] <= pd.Timestamp(week_end_dt, tz='UTC'))]

    # Aggregate shipped quantity by SKU
    shipped = shipments.groupby('sku', as_index=False)['quantity'].sum().rename(columns={'quantity': 'shipped_qty'})

    # Load inventory snapshot (must have on_hand_start/on_hand_end/cost_usd)
    inv = pd.read_csv(inventory_snapshot_csv)
    inv['sku'] = inv['sku'].astype(str).str.strip().str.upper()

    # Join and compute metrics
    metrics = inv.merge(shipped, on='sku', how='left').fillna({'shipped_qty': 0})
    metrics['avg_inventory'] = (metrics['on_hand_start'] + metrics['on_hand_end']) / 2
    metrics['cogs'] = metrics['shipped_qty'] * metrics['cost_usd']
    # Safe turnover: avoid division by zero
    metrics['turnover_rate'] = metrics.apply(lambda r: (r['shipped_qty'] / r['avg_inventory']) if r['avg_inventory'] > 0 else float('nan'), axis=1)
    metrics['days_on_hand'] = metrics['turnover_rate'].apply(lambda t: 365.0 / t if t and t > 0 else float('nan'))

    # Write Excel with multiple sheets
    with pd.ExcelWriter(output_xlsx, engine='openpyxl') as writer:
        metrics.sort_values('shipped_qty', ascending=False).to_excel(writer, sheet_name='sku_metrics', index=False)
        metrics.nlargest(10, 'shipped_qty')[['sku','shipped_qty']].to_excel(writer, sheet_name='top_movers', index=False)
        summary = pd.DataFrame([{
            'week_start': week_start, 'total_shipped': int(metrics['shipped_qty'].sum()),
            'weighted_turnover': float((metrics['cogs'].sum() / (metrics['avg_inventory'] * metrics['cost_usd']).sum()) if metrics['avg_inventory'].sum() > 0 else float('nan'))
        }])
        summary.to_excel(writer, sheet_name='summary', index=False)

    print({'output_xlsx': output_xlsx, 'total_shipped': int(metrics['shipped_qty'].sum())})

# Example call
if __name__ == '__main__':
    generate_inventory_turnover_report('data/shipments/week-2025-01-06', 'data/inventory/inventory_snapshot_2025-01-06.csv', '2025-01-06', '/tmp/inventory_turnover_2025-01-06_to_2025-01-12.xlsx')

How Functory Makes It Easy

On Functory you wrap the core logic above in a single main(...) function whose typed parameters become the web UI and API inputs. For this use case you would provide parameters like shipments_dir: FilePath or multiple FilePath params for uploaded CSVs, inventory_snapshot_csv: FilePath, week_start: str, and output_xlsx: str (or return the path). Functory will run your function in an isolated environment.

Concretely you would:

  • Choose an exact Python version (for example 3.11.11) when creating the Functory function.
  • Declare a requirements.txt where every dependency is pinned, e.g. pandas==2.2.0, openpyxl==3.1.1.
  • Structure the file so Functory can call main(shipments_zip: FilePath, inventory_snapshot_csv: FilePath, week_start: str) -> str directly. If you return a path string, Functory exposes the XLSX as a downloadable artifact.

Inputs are exposed as JSON or uploaded files in the Functory UI; running the function prints logs via print() which Functory captures. You get automatic cloud execution (CPU tiers for pandas), autoscaling, and pay-per-use billing—no servers to manage. You can also chain this function: pre-processing Functory function uploads cleaned CSVs → this reporting function → a notification function that posts the XLSX URL to Slack or stores it in S3.

Alternatives and why the function approach is better

Common alternatives are manual spreadsheets, ad-hoc Jupyter notebooks, or scheduling a lightweight ETL job on a VPS. Spreadsheets are error-prone for SKU normalization and hard to reproduce. Notebooks are great for exploration but brittle to run for every week. Hosting a scheduled ETL requires DevOps and costs for small teams.

The single-file function approach (and Functory deployment) gives reproducibility (one source file), low ops (no infra to maintain), and a simple API for automation or non-technical users. It also makes it easy to iterate: pin versions, update the calculation, and re-run consistently.

Business impact

Automating this step reduces manual spreadsheet work and reconciliation. A practical estimate: small teams spend ~3 hours per week compiling shipment and inventory numbers; this function can cut that to ~30–60 minutes (roughly a 67–83% reduction), freeing time for product and operations work. Less time reconciling also reduces stockout risks and improves reorder timing.

Industry context

According to a 2024 Gartner report, roughly 68% of SMBs still use spreadsheets as their primary inventory reporting tool, increasing operational risk and manual effort (Gartner, 2024).

Conclusion: You now have a concrete pattern to transform raw shipment exports into a weekly inventory turnover report suitable for small teams and freelancers. Next steps: add inbound receipt parsing to improve average inventory accuracy, or wrap the XLSX publishing to Slack/S3. Try packaging the script as a Functory function and publish a private version for your team—then iterate on KPIs (turnover, days on hand) each week and measure the time saved.

Thanks for reading.