Profit Margin Analysis
- 132 installs
- 41 repo stars
- Updated March 13, 2026
- finsilabs/awesome-ecommerce-skills
Analyze gross and net profit margins by product, category, channel, and customer segment with cost attribution and trend visualization.
About
Breaks down profitability across SKUs, categories, channels, and segments by attributing fulfillment, marketing, and overhead costs. A developer uses it to find which products actually make or lose money and compare margins across sales channels.
- Margin breakdown by product, category, channel, and customer segment
- Cost attribution surfaces SKUs that lose money after overhead
Profit Margin Analysis by the numbers
- 132 all-time installs (skills.sh)
- Ranked #758 of 2,064 Data Science & ML skills by installs in the Skillselion catalog
- Data as of Aug 3, 2026 (Skillselion catalog sync)
npx skills add https://github.com/finsilabs/awesome-ecommerce-skills --skill profit-margin-analysisAdd your badge
Show developers this skill is listed on Skillselion. Paste this into your README.
| Installs | 132 |
|---|---|
| repo stars | ★ 41 |
| Last updated | March 13, 2026 |
| Repository | finsilabs/awesome-ecommerce-skills ↗ |
What it does
Analyze gross and net profit margins by product, category, channel, and customer segment with cost attribution and trend visualization.
Files
Profit Margin Analysis
Overview
Profit margin analysis identifies exactly where your business makes and loses money — broken down by products, categories, channels, and customer segments. A product that accounts for 40% of revenue might contribute only 10% of gross profit — or actually lose money once fulfillment, marketing, and overhead are factored in.
This skill guides you through building a clear margin hierarchy using your existing platform tools and profit analytics apps, without requiring a data warehouse.
When to Use This Skill
- When needing to identify which products, categories, or SKUs are most and least profitable
- When comparing margin performance across sales channels (website vs. Amazon vs. wholesale)
- When making pricing decisions and needing to understand the impact on margin
- When rationalizing the product catalog to eliminate low-margin SKUs
- When understanding how marketing spend affects channel-level profitability
- When benchmarking margins against industry peers or investor expectations
Core Instructions
Step 1: Establish your margin hierarchy
Before pulling any data, define the margin levels you will track. Use this waterfall consistently across all analyses:
Gross Revenue (selling price × units sold)
- Discounts & coupons applied
- Returns & refunds
= Net Revenue
- Product COGS (landed cost: purchase price + freight + duties)
= Gross Profit
Gross Margin % = Gross Profit / Net Revenue × 100
- Outbound shipping (actual label cost or estimate)
- Payment processing fees (~2.9% + $0.30 for Stripe/Shopify Payments)
- Marketplace fees (Amazon referral + FBA fees; eBay final value fees)
- Packaging materials
= Fulfillment-Adjusted Gross Profit
Fulfillment-Adjusted Margin %
- Direct marketing spend (attributed to this channel/product)
= Contribution Margin
Contribution Margin % = Contribution Margin / Net Revenue × 100
- Allocated overhead (warehouse, software, headcount allocation)
= Net Operating Profit
Net Margin %Why contribution margin matters most for ecommerce decisions: Gross margin ignores fulfillment and marketing costs that are often the biggest swing factors. Contribution margin per unit is the number that actually governs whether it makes sense to sell more of a product or scale a channel.
Step 2: Enter cost data into your platform
Accurate margin analysis starts with accurate cost data. Enter landed cost (not just purchase price) for every product.
---
Shopify
1. Go to Products → [Product] → Variants 2. For each variant, enter the Cost per item — this should be your fully landed cost:
- Supplier invoice price per unit
- + Inbound freight per unit (divide total freight by total units in the shipment)
- + Customs duties per unit
- + Inspection/prep fees per unit (if applicable)
3. Once costs are entered, go to Analytics → Reports → Profit by product — Shopify calculates gross profit per product automatically 4. Go to Analytics → Reports → Profit by channel — see gross margin by sales channel
Limitations of Shopify's built-in profit reports:
- Shows gross profit only (revenue minus COGS)
- Does not include fulfillment costs, payment fees, or marketing spend
- For full contribution margin analysis, use a profit analytics app
Recommended Shopify apps for complete margin analysis:
- BeProfit: Adds shipping cost tracking (connects to ShipStation, EasyPost), ad spend integration (Meta, Google, TikTok), and per-order contribution margin
- Lifetimely: Focuses on CLV and cohort profitability alongside contribution margin by channel
- TrueProfit: Real-time profit dashboard with all cost layers; connects to 20+ ad platforms and shipping carriers
BeProfit setup for contribution margin: 1. Install BeProfit from the Shopify App Store 2. Go to BeProfit → Settings → Costs — verify product costs are imported from Shopify 3. Go to BeProfit → Integrations → Shipping — connect ShipStation or Shippo to pull actual label costs per order 4. Go to BeProfit → Integrations → Marketing — connect Meta, Google, TikTok for ad spend allocation 5. Go to BeProfit → Reports → Products — view gross margin, fulfillment-adjusted margin, and contribution margin per product
---
WooCommerce
1. Install the Cost of Goods plugin (WooCommerce extension, $79/yr) to add a cost field to each product 2. Enter landed cost per product/variant (same methodology as Shopify above) 3. Metorik pulls this cost data and shows gross profit per product at Metorik → Products → Profitability 4. For fulfillment cost tracking: export ShipStation or Shippo costs monthly as CSV; match to WooCommerce orders by order ID in Google Sheets or with Metorik's CSV import
---
BigCommerce
1. Go to Products → [Product] → Pricing → Cost price — enter landed cost per product 2. Go to Analytics → Merchandising → Products — view gross margin % per product with cost entered 3. Install Glew.io (App Marketplace) for contribution margin analysis that includes ad spend and fulfillment costs
---
Step 3: Run margin analysis by dimension
By product and SKU:
In your platform analytics or profit app, sort products by contribution margin % ascending to find your least profitable SKUs:
- Shopify: BeProfit → Reports → Products → sort by Net Profit % ascending
- WooCommerce: Metorik → Products → sort by Gross Profit ascending
- BigCommerce: Glew.io → Products → sort by Margin % ascending
By channel:
Compare the same product sold on different channels:
| Metric | Your Website | Amazon FBA | Wholesale |
|---|---|---|---|
| Gross Revenue per unit | $39.99 | $39.99 | $20.00 (your price to retailer) |
| Payment/marketplace fees | 3.2% | 15% referral + $4.50 FBA | 0% (invoice-based) |
| Outbound shipping | $5.00 | Included in FBA fee | Bulk freight (lower per unit) |
| Gross Margin % | 62% | 38% | 35% |
| Contribution Margin % | 48% | 25% | 28% |
This shows that despite Amazon's lower margin %, the channel may still be valuable for volume and brand visibility — but you need the numbers to decide.
By category:
Group your products into categories and compare average contribution margin % across them:
- Which categories contribute the most gross profit in absolute dollars?
- Which categories have the highest margin % (most efficient products)?
- Which categories have high revenue but low margin (volume products that may be diluting overall profitability)?
Step 4: Use industry benchmarks to contextualize your analysis
| Business Type | Gross Margin | Contribution Margin | Net Margin |
|---|---|---|---|
| Branded DTC (consumables) | 55–75% | 30–50% | 5–20% |
| Branded DTC (apparel) | 55–70% | 25–45% | 3–15% |
| Electronics reseller | 10–25% | 5–15% | 1–5% |
| Amazon FBA reseller | 15–35% | 5–20% | 2–8% |
| Subscription box | 40–60% | 20–40% | 5–15% |
| Wholesale / B2B | 20–40% | 15–30% | 3–12% |
If your gross margin is significantly below these ranges, investigate: 1. Are COGS entered correctly (including all landed cost components)? 2. Are you selling with excessive discounts? 3. Is your product pricing too low for your cost structure?
Step 5: Identify and act on margin improvement opportunities
Once you have margin data by SKU, prioritize improvements:
SKU profitability tiering:
- Stars: High margin + high volume → protect and grow; prioritize in ad spend and inventory
- Workhorses: Lower margin + high volume → margin improvement projects (negotiate supplier cost, reduce returns, optimize shipping)
- Niche: High margin + low volume → grow with targeted marketing; can sustain higher CAC
- Dogs: Low margin + low volume → candidates for discontinuation or price increase
Quick wins for margin improvement: 1. Raise prices on low-margin, low-elasticity SKUs — test a 10–15% price increase on products where demand is not highly price-sensitive 2. Reduce free shipping threshold — move from free shipping on all orders to free shipping above $75 or $100; saves 3–6% of revenue on small orders 3. Negotiate COGS with suppliers — a 5% COGS reduction improves gross margin by 5 percentage points on any product with 50%+ margins 4. Address high-return SKUs — a product with 20% return rate has ~20% of its revenue consumed by reverse logistics; improve descriptions, sizing guides, or photos
Best Practices
- Start with contribution margin, not gross margin — gross margin ignores fulfillment and marketing costs that are often the biggest swing factors in ecommerce profitability
- Reconcile cost data monthly — COGS changes due to supplier price changes, freight market fluctuations, and currency movements; update costs at least monthly
- Use a 13-month rolling trend view — looking at margin over 13 months shows seasonality and year-over-year changes simultaneously
- Track margin per order, not just per unit — a product with high unit margin may have low order-level margin if it is frequently ordered alone with flat-rate free shipping
- Build margin sensitivity models — show how margin changes with a 10% price increase, a 5% COGS reduction, or a $2 shipping cost change to make analysis directly actionable
- Flag negative-margin SKUs immediately — any product with negative contribution margin should be investigated within the week it is identified; every sale destroys value
Common Pitfalls
| Problem | Solution |
|---|---|
| Using purchase price instead of landed cost as COGS | Purchase price alone understates COGS by 15–40% for imported goods; always include freight, duties, and prep costs in the cost per item field |
| Not attributing variable marketing to products | If you run SKU-level ad campaigns, that marketing spend is a direct cost of those sales; exclude it from overhead and attribute it to the relevant SKUs |
| Comparing Amazon vs. website margins without normalizing fulfillment | Amazon FBA gross margins look lower than DTC margins because FBA fees are large; normalize to contribution margin (net of fulfillment) for fair comparisons |
| Analyzing margin without volume context | A 60% gross margin product doing $500/month is less important than a 35% margin product doing $500,000/month; always show margin % alongside absolute profit contribution |
| Historical margin changes when COGS is updated | Use point-in-time cost records in your profit analytics app; BeProfit and Lifetimely track cost history so historical margins are not retroactively recalculated |
Related Skills
- @cost-allocation-analysis
- @unit-economics-tracking
- @financial-analytics-dashboard
- @marketing-spend-analysis
- @sales-reporting-dashboard
{
"context": "Tests whether the agent correctly implements cost attribution calculations including dimensional weight shipping using the carrier-standard divisor, billable weight selection, landed cost (beyond invoice price), payment processing rates, Amazon settlement fee filtering, and documentation of assumptions.",
"type": "weighted_checklist",
"checklist": [
{
"name": "Dimensional weight divisor 139",
"max_score": 14,
"description": "The shipping estimation function uses 139 as the divisor in the dimensional weight formula: (length × width × height) / 139"
},
{
"name": "Billable weight = max(actual, dim)",
"max_score": 10,
"description": "The shipping function selects the billable weight as max(actual_weight_lbs, dim_weight_lbs), not just dimensional or just actual weight"
},
{
"name": "Landed cost components",
"max_score": 12,
"description": "The landed cost function includes at least freight and duties (in addition to invoice/purchase price) — not just the invoice price alone"
},
{
"name": "Payment rate ~2.9% + $0.30",
"max_score": 12,
"description": "The Stripe/payment processing fee function applies approximately 2.9% of the transaction amount plus a flat $0.30 fee (within ±0.2% of 2.9%)"
},
{
"name": "Amazon fee filter: amount < 0",
"max_score": 14,
"description": "The Amazon settlement aggregation function filters for fee line items using amount < 0 (fees are negative in settlement data), not amount > 0 or abs(amount)"
},
{
"name": "FBA fee breakdown",
"max_score": 10,
"description": "The Amazon fee function separately tracks at least two fee types (e.g., Commission and FBAPerUnitFulfillmentFee) rather than aggregating all fees into a single total"
},
{
"name": "ASSUMPTIONS constant documented",
"max_score": 12,
"description": "The file contains an ASSUMPTIONS string (or equivalent module-level docstring/comment block) that documents at least two cost attribution decisions made in the code"
},
{
"name": "Weighted average cost approach",
"max_score": 8,
"description": "The code uses or documents the use of weighted average cost (or FIFO) as the COGS methodology, rather than just the most recent purchase price"
},
{
"name": "Return rate in margin",
"max_score": 8,
"description": "At least one function or calculation accounts for product return rate when computing realized margin (e.g., adjusts revenue or margin by a return rate factor)"
}
]
}
SKU Cost Attribution Module for an Ecommerce Analytics Platform
Problem / Feature Description
A boutique analytics consultancy is building a Python-based cost attribution module for an ecommerce client that sells on both their own website (via Stripe) and Amazon FBA. The client imports goods from overseas suppliers and ships them in custom-sized boxes. Until now, the team has been using the supplier invoice price as the product cost and ignoring most variable costs, which is causing their reported margins to look far higher than actual bank account reality.
The consultancy wants a clean Python module that correctly computes fully-loaded per-unit costs: the true product cost (not just the invoice), shipping costs for each order line item based on package dimensions and zone, payment processing fees for direct-channel orders, and Amazon fees parsed from a settlement report. All cost attribution decisions should be documented in the module so another analyst can review and reproduce the work.
You are implementing the core calculations. The client ships packages via UPS and FedEx ground, and uses Stripe for all web orders.
Output Specification
Produce a Python file named cost_attribution.py containing:
1. A function that calculates landed cost per unit given invoice price and additional cost components (freight, duties, inspection, prep) 2. A function that estimates outbound shipping cost per shipment given package dimensions (in inches), weight (in ounces), destination zone, and a carrier rate table dictionary 3. A function that calculates the Stripe payment processing fee for a transaction amount 4. A function that extracts and aggregates Amazon fees from a list of settlement line items (each item is a dict with at least sku, fee_type, and amount keys) 5. A brief ASSUMPTIONS string constant at the top of the file documenting the cost attribution decisions embedded in the code
Include a short if __name__ == "__main__" block with one worked example of each function so the output can be verified by running python cost_attribution.py.
Input Files
The following sample Amazon settlement data is provided as a reference for what the settlement line items look like. Extract it before beginning.
=============== FILE: inputs/sample_settlement.json =============== [ {"sku": "WIDGET-RED-L", "fee_type": "Commission", "amount": -4.50}, {"sku": "WIDGET-RED-L", "fee_type": "FBAPerUnitFulfillmentFee", "amount": -3.22}, {"sku": "WIDGET-RED-L", "fee_type": "Principal", "amount": 29.99}, {"sku": "WIDGET-BLUE-M", "fee_type": "Commission", "amount": -3.90}, {"sku": "WIDGET-BLUE-M", "fee_type": "FBAPerUnitFulfillmentFee", "amount": -2.98}, {"sku": "WIDGET-BLUE-M", "fee_type": "Principal", "amount": 25.99}, {"sku": "WIDGET-RED-L", "fee_type": "VariableClosingFee", "amount": -1.80}, {"sku": "WIDGET-BLUE-M", "fee_type": "VariableClosingFee", "amount": -1.80} ]
{
"context": "Tests whether the agent prioritizes contribution margin as the primary metric, applies four-tier product segmentation with the correct dimension labels, uses a 13-month rolling window for trend analysis, compares channels/products on contribution (not gross) margin, flags negative-margin SKUs, builds a sensitivity model with the specified scenarios, ranks improvement opportunities by potential dollar gain (not just margin gap), and shows both percentage and absolute dollar metrics together.",
"type": "weighted_checklist",
"checklist": [
{
"name": "Contribution margin primary metric",
"max_score": 10,
"description": "The analysis uses contribution margin (after direct marketing costs) as the primary profitability metric in the tier classification and improvement ranking — not just gross margin"
},
{
"name": "4-tier segmentation labels",
"max_score": 12,
"description": "The tier report uses exactly four tiers corresponding to high/low margin × high/low volume combinations; labels match Stars, Workhorses, Niche, and Dogs (or equivalent descriptions of all four quadrants)"
},
{
"name": "Trend window: 13 months",
"max_score": 10,
"description": "The trend report covers 13 months of data (current month plus the 12 prior months), not just 12 months or an arbitrary window"
},
{
"name": "Year-over-year comparison",
"max_score": 8,
"description": "The trend data includes a year-over-year (same month prior year) comparison column or annotation — not just month-over-month"
},
{
"name": "Negative margin flag printed",
"max_score": 10,
"description": "The script prints to stdout the identities of any SKUs whose contribution margin is negative over the most recent 30-day equivalent window"
},
{
"name": "Sensitivity: 3 scenarios",
"max_score": 12,
"description": "The sensitivity table includes all three scenarios: a 10% price increase, a 5% COGS reduction, AND a $2 shipping cost reduction — not just one or two"
},
{
"name": "Improvement: ranked by dollar gain",
"max_score": 10,
"description": "The improvement opportunities CSV is sorted by potential absolute profit gain in dollars (descending), not by margin gap percentage"
},
{
"name": "Priority classification: high/medium/low",
"max_score": 8,
"description": "The improvement opportunities CSV includes a priority column with 'high', 'medium', or 'low' values based on the size of potential profit gain"
},
{
"name": "Absolute + relative metrics together",
"max_score": 10,
"description": "At least one output file (tier_report or improvement_opportunities) shows both a margin percentage column and an absolute dollar profit column side by side for the same SKU"
},
{
"name": "Methodology documented",
"max_score": 10,
"description": "The analysis_notes.md file documents at least two methodology or assumption choices (e.g., how tiers were defined, what cost base was used for contribution margin, seasonality handling)"
}
]
}
Margin Analysis Report for Catalog Rationalization
Problem / Feature Description
An ecommerce operator running a subscription-box business wants to rationalize their product catalog before the next buying season. They have two years of monthly SKU-level profitability data (net revenue, gross profit, direct marketing, fulfillment costs, overhead allocation) and are struggling to separate the signal from the noise: some SKUs look great in their peak season but are terrible in the off-season, and their product manager keeps pointing at gross margin % without considering how much each SKU actually contributes in absolute dollars.
The leadership team needs a comprehensive analysis that identifies where to focus improvement efforts, surfaces products they should consider eliminating, shows how margin has moved over time in a way that accounts for seasonality, and gives them concrete levers to pull (pricing, COGS reduction, shipping costs) with estimated impact. The analysis should be in a format they can review offline — a Python script plus output files.
You are producing the analysis. The input data is provided below.
Output Specification
Produce:
1. margin_analysis.py — a Python script that, when run with python margin_analysis.py, reads inputs/sku_data.json and produces all output files 2. outputs/tier_report.csv — products classified into profitability groupings based on their margin level and revenue volume (include the classification label in the output) 3. outputs/trend_report.csv — monthly margin trend data covering the full available history in a way that lets you see year-over-year movement; include both percentage and absolute dollar columns 4. outputs/improvement_opportunities.csv — ranked list of SKUs that are underperforming, including both their current margin % and the absolute dollar potential gain if they reached the catalog average; include a priority classification 5. outputs/sensitivity_table.csv — a table showing for each SKU how their margin changes under three different cost/price scenarios of your choosing that would be actionable for pricing and procurement decisions 6. outputs/analysis_notes.md — brief documentation of the methodology and any assumptions made in the analysis
The script should also print to stdout any SKUs that are losing money on a per-sale basis when all variable costs are included, based on recent performance.
Input Files
The following SKU profitability data is provided. Extract it before beginning.
=============== FILE: inputs/sku_data.json =============== { "skus": [ { "sku": "BOX-CLASSIC-M", "product_name": "Classic Box Medium", "category": "subscription", "monthly_data": [ {"period": "2024-01", "units_sold": 420, "net_revenue": 12600, "product_cost": 5040, "inbound_freight": 378, "outbound_shipping": 1260, "payment_fees": 365, "marketplace_fees": 0, "packaging_cost": 210, "direct_marketing": 2520, "overhead_allocation": 630, "returns": 252}, {"period": "2024-02", "units_sold": 395, "net_revenue": 11850, "product_cost": 4740, "inbound_freight": 355, "outbound_shipping": 1185, "payment_fees": 344, "marketplace_fees": 0, "packaging_cost": 198, "direct_marketing": 2370, "overhead_allocation": 593, "returns": 237}, {"period": "2024-03", "units_sold": 410, "net_revenue": 12300, "product_cost": 4920, "inbound_freight": 369, "outbound_shipping": 1230, "payment_fees": 357, "marketplace_fees": 0, "packaging_cost": 205, "direct_marketing": 2460, "overhead_allocation": 615, "returns": 246}, {"period": "2024-04", "units_sold": 430, "net_revenue": 12900, "product_cost": 5160, "inbound_freight": 387, "outbound_shipping": 1290, "payment_fees": 374, "marketplace_fees": 0, "packaging_cost": 215, "direct_marketing": 2580, "overhead_allocation": 645, "returns": 258}, {"period": "2024-05", "units_sold": 460, "net_revenue": 13800, "product_cost": 5520, "inbound_freight": 414, "outbound_shipping": 1380, "payment_fees": 400, "marketplace_fees": 0, "packaging_cost": 230, "direct_marketing": 2760, "overhead_allocation": 690, "returns": 276}, {"period": "2024-06", "units_sold": 480, "net_revenue": 14400, "product_cost": 5760, "inbound_freight": 432, "outbound_shipping": 1440, "payment_fees": 418, "marketplace_fees": 0, "packaging_cost": 240, "direct_marketing": 2880, "overhead_allocation": 720, "returns": 288}, {"period": "2024-07", "units_sold": 455, "net_revenue": 13650, "product_cost": 5460, "inbound_freight": 410, "outbound_shipping": 1365, "payment_fees": 396, "marketplace_fees": 0, "packaging_cost": 228, "direct_marketing": 2730, "overhead_allocation": 683, "returns": 273}, {"period": "2024-08", "units_sold": 445, "net_revenue": 13350, "product_cost": 5340, "inbound_freight": 400, "outbound_shipping": 1335, "payment_fees": 387, "marketplace_fees": 0, "packaging_cost": 223, "direct_marketing": 2670, "overhead_allocation": 668, "returns": 267}, {"period": "2024-09", "units_sold": 470, "net_revenue": 14100, "product_cost": 5640, "inbound_freight": 423, "outbound_shipping": 1410, "payment_fees": 409, "marketplace_fees": 0, "packaging_cost": 235, "direct_marketing": 2820, "overhead_allocation": 705, "returns": 282}, {"period": "2024-10", "units_sold": 510, "net_revenue": 15300, "product_cost": 6120, "inbound_freight": 459, "outbound_shipping": 1530, "payment_fees": 444, "marketplace_fees": 0, "packaging_cost": 255, "direct_marketing": 3060, "overhead_allocation": 765, "returns": 306}, {"period": "2024-11", "units_sold": 620, "net_revenue": 18600, "product_cost": 7440, "inbound_freight": 558, "outbound_shipping": 1860, "payment_fees": 539, "marketplace_fees": 0, "packaging_cost": 310, "direct_marketing": 3720, "overhead_allocation": 930, "returns": 372}, {"period": "2024-12", "units_sold": 710, "net_revenue": 21300, "product_cost": 8520, "inbound_freight": 639, "outbound_shipping": 2130, "payment_fees": 618, "marketplace_fees": 0, "packaging_cost": 355, "direct_marketing": 4260, "overhead_allocation": 1065, "returns": 426}, {"period": "2025-01", "units_sold": 430, "net_revenue": 12900, "product_cost": 5160, "inbound_freight": 387, "outbound_shipping": 1290, "payment_fees": 374, "marketplace_fees": 0, "packaging_cost": 215, "direct_marketing": 2580, "overhead_allocation": 645, "returns": 258}, {"period": "2025-02", "units_sold": 415, "net_revenue": 12450, "product_cost": 4980, "inbound_freight": 374, "outbound_shipping": 1245, "payment_fees": 361, "marketplace_fees": 0, "packaging_cost": 208, "direct_marketing": 2490, "overhead_allocation": 623, "returns": 249} ] }, { "sku": "BOX-PREMIUM-L", "product_name": "Premium Box Large", "category": "subscription", "monthly_data": [ {"period": "2024-01", "units_sold": 180, "net_revenue": 9000, "product_cost": 2700, "inbound_freight": 270, "outbound_shipping": 900, "payment_fees": 261, "marketplace_fees": 0, "packaging_cost": 180, "direct_marketing": 900, "overhead_allocation": 450, "returns": 90}, {"period": "2024-02", "units_sold": 175, "net_revenue": 8750, "product_cost": 2625, "inbound_freight": 263, "outbound_shipping": 875, "payment_fees": 254, "marketplace_fees": 0, "packaging_cost": 175, "direct_marketing": 875, "overhead_allocation": 438, "returns": 88}, {"period": "2024-03", "units_sold": 190, "net_revenue": 9500, "product_cost": 2850, "inbound_freight": 285, "outbound_shipping": 950, "payment_fees": 276, "marketplace_fees": 0, "packaging_cost": 190, "direct_marketing": 950, "overhead_allocation": 475, "returns": 95}, {"period": "2024-04", "units_sold": 200, "net_revenue": 10000, "product_cost": 3000, "inbound_freight": 300, "outbound_shipping": 1000, "payment_fees": 290, "marketplace_fees": 0, "packaging_cost": 200, "direct_marketing": 1000, "overhead_allocation": 500, "returns": 100}, {"period": "2024-05", "units_sold": 210, "net_revenue": 10500, "product_cost": 3150, "inbound_freight": 315, "outbound_shipping": 1050, "payment_fees": 305, "marketplace_fees": 0, "packaging_cost": 210, "direct_marketing": 1050, "overhead_allocation": 525, "returns": 105}, {"period": "2024-06", "units_sold": 220, "net_revenue": 11000, "product_cost": 3300, "inbound_freight": 330, "outbound_shipping": 1100, "payment_fees": 319, "marketplace_fees": 0, "packaging_cost": 220, "direct_marketing": 1100, "overhead_allocation": 550, "returns": 110}, {"period": "2024-07", "units_sold": 215, "net_revenue": 10750, "product_cost": 3225, "inbound_freight": 323, "outbound_shipping": 1075, "payment_fees": 312, "marketplace_fees": 0, "packaging_cost": 215, "direct_marketing": 1075, "overhead_allocation": 538, "returns": 108}, {"period": "2024-08", "units_sold": 205, "net_revenue": 10250, "product_cost": 3075, "inbound_freight": 308, "outbound_shipping": 1025, "payment_fees": 297, "marketplace_fees": 0, "packaging_cost": 205, "direct_marketing": 1025, "overhead_allocation": 513, "returns": 103}, {"period": "2024-09", "units_sold": 225, "net_revenue": 11250, "product_cost": 3375, "inbound_freight": 338, "outbound_shipping": 1125, "payment_fees": 326, "marketplace_fees": 0, "packaging_cost": 225, "direct_marketing": 1125, "overhead_allocation": 563, "returns": 113}, {"period": "2024-10", "units_sold": 250, "net_revenue": 12500, "product_cost": 3750, "inbound_freight": 375, "outbound_shipping": 1250, "payment_fees": 363, "marketplace_fees": 0, "packaging_cost": 250, "direct_marketing": 1250, "overhead_allocation": 625, "returns": 125}, {"period": "2024-11", "units_sold": 310, "net_revenue": 15500, "product_cost": 4650, "inbound_freight": 465, "outbound_shipping": 1550, "payment_fees": 450, "marketplace_fees": 0, "packaging_cost": 310, "direct_marketing": 1550, "overhead_allocation": 775, "returns": 155}, {"period": "2024-12", "units_sold": 370, "net_revenue": 18500, "product_cost": 5550, "inbound_freight": 555, "outbound_shipping": 1850, "payment_fees": 537, "marketplace_fees": 0, "packaging_cost": 370, "direct_marketing": 1850, "overhead_allocation": 925, "returns": 185}, {"period": "2025-01", "units_sold": 195, "net_revenue": 9750, "product_cost": 2925, "inbound_freight": 293, "outbound_shipping": 975, "payment_fees": 283, "marketplace_fees": 0, "packaging_cost": 195, "direct_marketing": 975, "overhead_allocation": 488, "returns": 98}, {"period": "2025-02", "units_sold": 188, "net_revenue": 9400, "product_cost": 2820, "inbound_freight": 282, "outbound_shipping": 940, "payment_fees": 273, "marketplace_fees": 0, "packaging_cost": 188, "direct_marketing": 940, "overhead_allocation": 470, "returns": 94} ] }, { "sku": "ADD-ON-SNACK", "product_name": "Snack Add-on Pack", "category": "add-on", "monthly_data": [ {"period": "2024-01", "units_sold": 95, "net_revenue": 950, "product_cost": 570, "inbound_freight": 95, "outbound_shipping": 285, "payment_fees": 28, "marketplace_fees": 0, "packaging_cost": 48, "direct_marketing": 475, "overhead_allocation": 95, "returns": 19}, {"period": "2024-02", "units_sold": 88, "net_revenue": 880, "product_cost": 528, "inbound_freight": 88, "outbound_shipping": 264, "payment_fees": 26, "marketplace_fees": 0, "packaging_cost": 44, "direct_marketing": 440, "overhead_allocation": 88, "returns": 18}, {"period": "2024-03", "units_sold": 102, "net_revenue": 1020, "product_cost": 612, "inbound_freight": 102, "outbound_shipping": 306, "payment_fees": 30, "marketplace_fees": 0, "packaging_cost": 51, "direct_marketing": 510, "overhead_allocation": 102, "returns": 20}, {"period": "2024-04", "units_sold": 98, "net_revenue": 980, "product_cost": 588, "inbound_freight": 98, "outbound_shipping": 294, "payment_fees": 28, "marketplace_fees": 0, "packaging_cost": 49, "direct_marketing": 490, "overhead_allocation": 98, "returns": 20}, {"period": "2024-05", "units_sold": 110, "net_revenue": 1100, "product_cost": 660, "inbound_freight": 110, "outbound_shipping": 330, "payment_fees": 32, "marketplace_fees": 0, "packaging_cost": 55, "direct_marketing": 550, "overhead_allocation": 110, "returns": 22}, {"period": "2024-06", "units_sold": 115, "net_revenue": 1150, "product_cost": 690, "inbound_freight": 115, "outbound_shipping": 345, "payment_fees": 33, "marketplace_fees": 0, "packaging_cost": 58, "direct_marketing": 575, "overhead_allocation": 115, "returns": 23}, {"period": "2024-07", "units_sold": 108, "net_revenue": 1080, "product_cost": 648, "inbound_freight": 108, "outbound_shipping": 324, "payment_fees": 31, "marketplace_fees": 0, "packaging_cost": 54, "direct_marketing": 540, "overhead_allocation": 108, "returns": 22}, {"period": "2024-08", "units_sold": 105, "net_revenue": 1050, "product_cost": 630, "inbound_freight": 105, "outbound_shipping": 315, "payment_fees": 30, "marketplace_fees": 0, "packaging_cost": 53, "direct_marketing": 525, "overhead_allocation": 105, "returns": 21}, {"period": "2024-09", "units_sold": 112, "net_revenue": 1120, "product_cost": 672, "inbound_freight": 112, "outbound_shipping": 336, "payment_fees": 32, "marketplace_fees": 0, "packaging_cost": 56, "direct_marketing": 560, "overhead_allocation": 112, "returns": 22}, {"period": "2024-10", "units_sold": 120, "net_revenue": 1200, "product_cost": 720, "inbound_freight": 120, "outbound_shipping": 360, "payment_fees": 35, "marketplace_fees": 0, "packaging_cost": 60, "direct_marketing": 600, "overhead_allocation": 120, "returns": 24}, {"period": "2024-11", "units_sold": 145, "net_revenue": 1450, "product_cost": 870, "inbound_freight": 145, "outbound_shipping": 435, "payment_fees": 42, "marketplace_fees": 0, "packaging_cost": 73, "direct_marketing": 725, "overhead_allocation": 145, "returns": 29}, {"period": "2024-12", "units_sold": 165, "net_revenue": 1650, "product_cost": 990, "inbound_freight": 165, "outbound_shipping": 495, "payment_fees": 48, "marketplace_fees": 0, "packaging_cost": 83, "direct_marketing": 825, "overhead_allocation": 165, "returns": 33}, {"period": "2025-01", "units_sold": 92, "net_revenue": 920, "product_cost": 552, "inbound_freight": 92, "outbound_shipping": 276, "payment_fees": 27, "marketplace_fees": 0, "packaging_cost": 46, "direct_marketing": 460, "overhead_allocation": 92, "returns": 18}, {"period": "2025-02", "units_sold": 85, "net_revenue": 850, "product_cost": 510, "inbound_freight": 85, "outbound_shipping": 255, "payment_fees": 25, "marketplace_fees": 0, "packaging_cost": 43, "direct_marketing": 425, "overhead_allocation": 85, "returns": 17} ] } ] }
{
"context": "Tests whether the agent implements the correct SQL schema for SKU-level profitability tracking, including the specific column types, generated computed columns, composite primary key, a margin analysis view with division-by-zero protection, and the correct four-level margin hierarchy.",
"type": "weighted_checklist",
"checklist": [
{
"name": "period_id VARCHAR(7)",
"max_score": 8,
"description": "The period identifier column is defined as VARCHAR(7) (not DATE, INTEGER, or other type)"
},
{
"name": "GENERATED ALWAYS AS STORED",
"max_score": 12,
"description": "At least two derived monetary columns (e.g., net_revenue, gross_profit, cogs_total) use GENERATED ALWAYS AS (...) STORED syntax rather than being computed only in a view or application layer"
},
{
"name": "Composite PRIMARY KEY",
"max_score": 10,
"description": "The fact table has a composite primary key on (period_id, sku, channel) — all three columns together"
},
{
"name": "Separate margin view",
"max_score": 8,
"description": "A CREATE VIEW statement is present that exposes computed margin percentages (not just the base table)"
},
{
"name": "NULLIF division guard",
"max_score": 8,
"description": "Margin percentage calculations in the view use NULLIF(net_revenue, 0) or equivalent to avoid division-by-zero errors"
},
{
"name": "Gross margin level",
"max_score": 10,
"description": "Schema or view includes gross profit = net_revenue - product cost - inbound freight (or equivalent) as a distinct margin level"
},
{
"name": "Fulfillment-adjusted margin level",
"max_score": 12,
"description": "Schema or view includes a fulfillment-adjusted profit level that subtracts outbound shipping, payment fees, marketplace fees, and packaging from gross profit"
},
{
"name": "Contribution margin level",
"max_score": 12,
"description": "Schema or view includes a contribution margin level that subtracts direct marketing spend from fulfillment-adjusted profit"
},
{
"name": "Net operating profit level",
"max_score": 10,
"description": "Schema or view includes a net operating profit level that subtracts overhead allocation from contribution margin"
},
{
"name": "Net revenue formula",
"max_score": 10,
"description": "Net revenue is computed as gross_revenue minus discounts minus returns (all three components), not just gross_revenue minus COGS"
}
]
}
Profitability Database Setup for a DTC Brand
Problem / Feature Description
A direct-to-consumer apparel brand has been tracking sales and costs in spreadsheets across several people and has finally decided to migrate everything into a proper database. They sell across three channels — their own website, Amazon, and a wholesale portal — and carry roughly 80 SKUs across four product categories.
The finance team needs a SQL schema that lets them load monthly sales and cost data per SKU per channel and immediately query things like "what is our gross margin by category this month?" or "how has profitability per sale changed over the last year, after accounting for all variable costs?". The schema should handle all the relevant cost components — from product cost through to marketing and overhead — and should make it easy to run margin queries without repeating the margin formulas everywhere.
The head of finance has asked you to design and implement the database schema, including any views or derived columns that will simplify downstream analysis. The schema should reflect the full cost waterfall used in ecommerce profitability analysis, covering every layer of cost from gross revenue all the way down to profit after overhead.
Output Specification
Produce a single SQL file named profitability_schema.sql containing:
- The main fact table definition with all required columns
- A view (or views) that compute margin percentages for use in analysis queries
- Two or three example
SELECTqueries demonstrating how a user would query gross margin by category and profitability by channel for a given period
The SQL should run without errors on PostgreSQL 15+. Include brief inline comments explaining what each section does.
{
"name": "finsi/profit-margin-analysis",
"version": "0.1.0",
"summary": "Analyze gross and net profit margins by product, category, channel, and customer segment with cost attribution, benchmarking, and trend visualization",
"skills": {
"profit-margin-analysis": {
"path": "SKILL.md"
}
}
}
Related skills
Microsoft FoundryDeploy, evaluate, and continuously improve Microsoft Foundry agents from a single agent interface.490k1.3k
Ai Research ReproductionOrchestrate trustworthy, auditable reproduction of deep learning repositories directly from their READMEs.176k511Run TrainSafely execute selected deep learning training commands with standardized evidence capture.176k511Explore RunSafely run isolated exploratory experiments with clear recording and conservative selection before committing changes.176k511Paper Context ResolverFetch precise reproduction-critical details like dataset splits, preprocessing steps, or evaluation protocols from the original academic paper when the repo README leav141k511Repo Intake And PlanScan unfamiliar AI research repositories and receive a minimal, trustworthy reproduction target before investing significant time.140k511