I've reviewed 50+ analyst portfolios this year. 90% have the same problem:
Projects that look like homework, not business work.
Today I'm showing you exactly how I'd approach a real analysis problem from my time at ecommerce company. Not theory. The actual work.
By the end, you'll have a project you can confidently discuss in interviews.
THE PROBLEM
You're an analyst at ShopFast (350 products across electronics, home goods, fashion).
Your VP of Operations walks up:
"We're burning money on dead inventory. Limited warehouse space, some products aren't moving. Tell me: which products should we discount heavily, which should we discontinue, and which are worth keeping?"
This is a real problem. Not "predict Titanic survival." A problem where your analysis directly impacts revenue.
What makes this a good portfolio project:
Solves a business problem (not academic)
Requires multiple skills (SQL, analysis, visualization, business thinking)
Shows you can make recommendations (not just charts)
Common across retail/e-commerce
BEFORE YOU TOUCH DATA
Key questions to ask:
What's our warehouse capacity constraint?
What's our margin structure?
Are there seasonal products?
Do some products drive repeat purchases?
The metrics that matter:
Metric | Why It Matters |
|---|---|
Revenue per SKU | How much money does this make? |
Sell-through Rate | % of inventory actually sold |
Days to Sell | How fast does inventory move? |
Gross Margin % | Profitability, not just revenue |
Return Rate | Quality issues? Wrong fit? |
Reorder Rate | Customer stickiness indicator |
Product Performance Matrix (like BCG Matrix):
Stars — High revenue, high margin, fast-moving → KEEP & INVEST
Cash Cows — Steady revenue, good margin → KEEP
Question Marks — New/unclear performance → MONITOR
Dogs — Low revenue, low margin, slow-moving → DISCOUNT or DISCONTINUE
THE DATA
Using the Instacart dataset (Kaggle).
Why: Real e-commerce transactions. 3M+ orders. 200K+ products. Messy enough to be realistic.
THE ANALYSIS
Step 1: Sanity Checks
-- Total orders?
SELECT COUNT(DISTINCT order_id) FROM orders;
-- Expected: ~3.4M
-- Null check
SELECT
COUNT(*) as total,
COUNT(order_id) as non_null_orders
FROM orders;
Step 2: Create Analysis Table
CREATE TABLE product_performance AS
SELECT
p.product_id,
p.product_name,
d.department,
COUNT(DISTINCT op.order_id) as total_orders,
COUNT(DISTINCT o.user_id) as unique_customers,
ROUND(100.0 * SUM(op.reordered) / COUNT(*), 2) as reorder_rate_pct
FROM products p
LEFT JOIN order_products op ON p.product_id = op.product_id
LEFT JOIN orders o ON op.order_id = o.order_id
LEFT JOIN departments d ON p.department_id = d.department_id
GROUP BY p.product_id, p.product_name, d.department;
Step 3: Revenue Distribution (80/20 Rule)
WITH ranked AS (
SELECT *,
SUM(total_orders * 30) OVER () as total_revenue,
SUM(total_orders * 30) OVER (ORDER BY total_orders DESC) as cumulative
FROM product_performance WHERE total_orders > 0
)
SELECT product_name,
ROUND(100.0 * cumulative / total_revenue, 2) as cumulative_pct
FROM ranked LIMIT 50;
What you'll find:
Top 20% of products → 80%+ of revenue
Bottom 50% → less than 5% of revenue
This tells you where warehouse space should go.
Step 4: Product Segmentation
WITH metrics AS (
SELECT *,
PERCENT_RANK() OVER (ORDER BY total_orders) as orders_pct,
PERCENT_RANK() OVER (ORDER BY reorder_rate_pct) as quality_pct
FROM product_performance WHERE total_orders >= 10
)
SELECT *,
CASE
WHEN orders_pct >= 0.75 AND quality_pct >= 0.60 THEN 'STAR'
WHEN orders_pct >= 0.50 AND quality_pct >= 0.50 THEN 'CASH_COW'
WHEN orders_pct < 0.50 AND quality_pct >= 0.60 THEN 'QUESTION_MARK'
ELSE 'DOG'
END as category
FROM metrics;
Results:
Stars (25%): High volume + high reorder → Best products
Cash Cows (30%): Decent volume + quality → Keep
Question Marks (15%): Low volume, customers love them → Potential
Dogs (30%): Low everything → Discontinuation candidates
THE DASHBOARD
Tab 1: Executive Summary
Key metrics, top 20 products, bottom 20 products, segmentation breakdown
Tab 2: Product Performance
Full list with filters, scatter plot (Orders vs Reorder Rate)
Tab 3: Category Analysis
Department-level metrics, heatmap
Tab 4: Recommendations
Products to discontinue, discount, invest in
Pro tip: Don't make it pretty. Make it useful. Executives care about decisions, not animations.
🏆 THE GOLD: HOW TO PRESENT THIS
This is where freshers fail. They show charts but don't tell anyone what to DO.
Here's how I'd present this:
TO: VP of Operations
FROM: Business Analyst
RE: Product Portfolio Optimization - Q1 2026
SUMMARY
I analyzed 3.4M orders across 49K products to identify optimization opportunities for warehouse space and inventory costs.
KEY FINDINGS
1. Revenue Concentration Risk
80% of revenue comes from 18% of products (8,820 SKUs)
Bottom 40% contributes <3% of revenue
We're wasting warehouse space on low-value inventory
2. Product Portfolio Health
12,250 products (25%) are "Stars"
14,700 products (30%) are "Dogs"
30% of our portfolio is underperforming
3. Category Issues
"Personal Care" has 62% dogs (worst)
"Pantry Staples" has 12% dogs (healthiest)
RECOMMENDATIONS
Immediate (This Month):
Discontinue 2,500 "Dog" products (<5 orders in 90 days)
Warehouse space freed: 15%
Revenue impact: <0.5%
Cost savings: ~$125K/year
Discount 1,800 "Dogs" (5-20 orders) at 30-50% off
Clear stock, don't restock
Estimated revenue: $85K
Strategic (Next Quarter):
Increase stock for top 500 "Stars"
High reorder rate but stock out frequently
Revenue upside: $450K/quarter
Monitor 850 "Question Marks"
Run targeted marketing, decide in 60 days
EXPECTED IMPACT
Warehouse space freed: 15-20%
Inventory costs reduced: $125K/year
Revenue from optimized stock: +$450K/quarter
Net: ~$600K improvement in 6 months
I've created a dashboard that auto-updates weekly. Happy to discuss further.
THIS is how you present analysis. Not "here are some charts." But:
Here's the problem
Here's what I found
Here's what we should do
Here's the expected impact
THE INTERVIEW: GOOD vs BAD
Interviewer: "Walk me through a project on your resume."
❌ BAD ANSWER (What freshers say):
"I did a product analysis using the Instacart dataset. I used SQL to query the data and created visualizations in Excel. I segmented products into different categories based on performance."
✅ GOOD ANSWER (What gets you hired):
"I built a product portfolio optimization framework for an e-commerce company with warehouse space constraints.
The business problem: 50K products, limited capacity, some products were dead weight.
I analyzed 3.4 million orders to segment products into four categories—stars, cash cows, question marks, and dogs—based on order volume and reorder rate (a proxy for product quality).
The key insight: 80% of revenue came from 18% of products, and 30% of the portfolio was underperforming.
My recommendation: discontinue 2,500 worst performers. This would free 15% of warehouse space with less than 0.5% revenue impact—about $125K in annual savings.
I also identified 500 'star' products stocking out frequently. Increasing inventory there could drive an additional $450K per quarter.
I built a dashboard that auto-updates weekly so the ops team can make decisions without re-running the analysis.
What made this realistic: I didn't just analyze—I tied it to business decisions with estimated financial impact."
Follow-up Questions You'll Get:
Q: "How did you decide on 75th percentile for 'stars'?"
A: "I tested 70th, 75th, 80th—looked at distribution. 75th balanced selectivity with having enough products to work with. In production, I'd validate with ops based on warehouse capacity."
Q: "What if a product has low orders but high margin?"
A: "Great question. I'd add margin as a third dimension. Low-volume, high-margin could be 'niche premium'—worth keeping if profitable enough."
Q: "How would you handle seasonal products?"
A: "Time-based filter. Winter jackets only measured Oct-Feb. Calculate 'in-season sell-through' rather than annual metrics."
MAKE IT YOUR OWN
Don't copy this exactly. Variations:
Customer Retention Focus: Which products drive repeat purchases?
Pricing Optimization: Which products can handle price increases?
Inventory Forecasting: Predict which products become "dogs" next quarter
Different Industry: SaaS features, fintech products, edtech courses
FINAL THOUGHTS
Real analyst work:
Start with a business problem
Dig into messy data
Find actionable insights
Make clear recommendations
Estimate financial impact
Not "I predicted Titanic survival with 87% accuracy."
YOUR NEXT STEPS
Download the Instacart dataset
Run through this analysis yourself (don't copy-paste—write the SQL)
Build the dashboard
Practice explaining it aloud like you're talking to a manager
Add to your portfolio with a clear README
Time: 8-12 hours. One weekend.
ROI: A project you can discuss in every analyst interview.
Worth it.
Go build something real.
Ani
P.S. Found this helpful? Forward it to a friend struggling with their analyst portfolio.
RESOURCES:
Instacart Dataset: kaggle.com/c/instacart-market-basket-analysis/data
SQL Practice: mode.com/sql-tutorial