How to Build a Profit Margin Tracking System

Knowing your overall margin is not enough. I built a profit margin tracking system that calculates margins per project and per client in real-time. You see exactly which clients are profitable and which ones are eating your margins before the month ends.

Margin data at the project level changes how you price and prioritize work.

What You Need Before Starting

• Python 3.8+
• Time tracking with hourly rates
• Project cost data
• Revenue per project/client

Step 1: Define Cost Structure

import sqlite3
from datetime import datetime

def init_margin_db(db_path="margins.db"):
    conn = sqlite3.connect(db_path)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS project_financials (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            project_id TEXT,
            client_id TEXT,
            revenue REAL DEFAULT 0,
            labor_cost REAL DEFAULT 0,
            tool_cost REAL DEFAULT 0,
            other_cost REAL DEFAULT 0,
            month TEXT
        )
    """)
    conn.commit()
    return conn

LABOR_RATES = {
    "senior_dev": {"hourly_cost": 75, "hourly_bill": 150},
    "designer": {"hourly_cost": 50, "hourly_bill": 120},
    "junior_dev": {"hourly_cost": 35, "hourly_bill": 100}
}

Step 2: Calculate Project Margins

def calculate_project_margin(conn, project_id, month):
    row = conn.execute("""
        SELECT revenue, labor_cost, tool_cost, other_cost
        FROM project_financials WHERE project_id=? AND month=?
    """, (project_id, month)).fetchone()

    if not row:
        return None

    revenue, labor, tools, other = row
    total_cost = labor + tools + other
    profit = revenue - total_cost
    margin = (profit / revenue * 100) if revenue > 0 else 0

    return {
        "revenue": revenue, "total_cost": total_cost,
        "profit": profit, "margin": round(margin, 1),
        "labor_pct": round(labor / revenue * 100, 1) if revenue else 0
    }

Step 3: Track Client-Level Margins

def client_margin_report(conn, client_id):
    rows = conn.execute("""
        SELECT month, SUM(revenue) as rev, SUM(labor_cost + tool_cost + other_cost) as cost
        FROM project_financials WHERE client_id=?
        GROUP BY month ORDER BY month DESC LIMIT 12
    """, (client_id,)).fetchall()

    months = []
    for month, rev, cost in rows:
        profit = rev - cost
        margin = round(profit / rev * 100, 1) if rev else 0
        months.append({"month": month, "revenue": rev, "cost": cost, "profit": profit, "margin": margin})

    return months

Step 4: Alert on Low Margins

def check_margin_alerts(conn, month, threshold=20):
    rows = conn.execute("""
        SELECT project_id, client_id, revenue, labor_cost + tool_cost + other_cost as total_cost
        FROM project_financials WHERE month=? AND revenue > 0
    """, (month,)).fetchall()

    alerts = []
    for proj, client, rev, cost in rows:
        margin = (rev - cost) / rev * 100
        if margin < threshold:
            alerts.append({"project": proj, "client": client, "margin": round(margin, 1),
                         "revenue": rev, "cost": cost})

    return sorted(alerts, key=lambda x: x["margin"])

Step 5: Generate the Margin Dashboard

def margin_dashboard(conn, month):
    rows = conn.execute("""
        SELECT client_id, SUM(revenue) as rev,
               SUM(labor_cost + tool_cost + other_cost) as cost
        FROM project_financials WHERE month=?
        GROUP BY client_id ORDER BY SUM(revenue) DESC
    """, (month,)).fetchall()

    report = f"# Margin Dashboard - {month}\n\n"
    report += "| Client | Revenue | Cost | Profit | Margin |\n|---|---|---|---|---|\n"

    for client, rev, cost in rows:
        profit = rev - cost
        margin = round(profit / rev * 100, 1) if rev else 0
        flag = " !!!" if margin < 20 else ""
        report += f"| {client} | ${rev:,.0f} | ${cost:,.0f} | ${profit:,.0f} | {margin}%{flag} |\n"

    return report

What to Build Next

Add predictive margin warnings that look at current month burn rate and project whether you will hit target margin by month end. Catching a margin problem at day 15 gives you time to adjust. Catching it at day 30 is just accounting.

Related Reading

• AI-Powered Reporting That Actually Gets Read - margin reports that drive decisions
• Building Your First Automation: A Complete Guide - automation fundamentals
• The Feedback Loop That Powers Everything - margin tracking as feedback