Data Analyst Portfolio · 5 Projects · Hinglish

Data Analyst Portfolio Mein 5 Must-Have Projects

SECTION 01Project 1: Sales / Revenue Analysis

Problem statement: "Pichhle 12 mahine ka sales decline kyun ho raha hai — kaunse products, regions, aur segments responsible hain?"

Dataset:

• Kaggle: "E-Commerce Sales Dataset" ya "Superstore Sales Data".
• Ya synthetic data — Excel/CSV mein banao.
• Columns: Date, Product, Category, Region, Sales, Profit, Quantity.

SQL queries (10–15):

• Monthly revenue trend — GROUP BY month.
• Top 10 products by revenue — ORDER BY + LIMIT.
• Revenue by region — GROUP BY region.
• YoY / MoM growth — window functions LAG.
• Profit margin by category — SUM, division.

Dashboard (Power BI/Tableau):

• KPI cards: Total revenue, profit %, growth %.
• Line chart: Monthly revenue trend.
• Bar chart: Top 10 products.
• Map: Region-wise sales.
• Filter: Category, year, region.

Insights (3–5):

• "Q3 mein revenue 22% gira — Electronics category ka contribution highest drop."
• "North region mein profit margin 18% — sabse zyada."
• "Top 3 products total revenue ka 45% contribute karte hain."

Recommendations:

• Q3 mein Electronics ka inventory optimize karo.
• North region mein expansion.
• Top 3 products pe marketing budget badhao.

SECTION 02Project 2: Customer Churn Analysis

Problem statement: "Kaunse customers churn kar rahe hain, aur unhe retain karne ke liye kya karna chahiye?"

Dataset:

• Kaggle: "Telco Customer Churn" ya "Bank Customer Churn".
• Columns: CustomerID, Tenure, MonthlyCharges, Contract, Churn, Services.

SQL queries (10–15):

• Churn rate overall — SUM(CASE WHEN churn='Yes').
• Churn by contract type — GROUP BY contract.
• Churn by tenure bucket — CASE WHEN buckets.
• Churn by monthly charges range — CASE WHEN.
• Cohort retention — window functions + running totals.

Dashboard (Tableau/Power BI):

• KPI: Churn rate, average tenure, ARPU.
• Bar chart: Churn by contract type.
• Line chart: Cohort retention curve.
• Heatmap: Churn by segment × tenure.
• Filter: Contract, services, region.

Insights (3–5):

• "Month-to-month contract wale customers ka churn rate 42% — highest."
• "First 6 months mein 55% churn hota hai."
• "High monthly charges (>₹2000) mein churn 35% zyada."

Recommendations:

• Month-to-month users ko annual plan pe shift karne ke liye discount do.
• First 6 months mein retention offer.
• High-charge customers ke liye premium support.

SECTION 03Project 3: Marketing Campaign ROI

Problem statement: "Marketing budget ka sabse zyada ROI kaunsa channel de raha hai, aur kahan se cut karein?"

Dataset:

• Kaggle: "Marketing Campaign Data" ya "Ad Campaign Performance".
• Ya Google Ads / Facebook Ads sample data (public).
• Columns: Campaign, Channel, Impressions, Clicks, Cost, Conversions.

SQL queries (10–15):

• CTR by channel — clicks/impressions.
• CPC by channel — cost/clicks.
• ROAS by campaign — revenue/cost.
• Conversion funnel — impressions → clicks → conversions.
• Best-performing campaigns — ORDER BY ROAS.

Dashboard:

• KPI cards: Total spend, revenue, ROAS, CTR.
• Funnel chart: Impressions → Clicks → Conversions.
• Bar chart: ROAS by channel.
• Scatter: Cost vs Revenue.
• Filter: Channel, campaign, date range.

Insights (3–5):

• "Email channel ka ROAS 5.2× — sabse highest."
• "Social media ads mein CTR 1.8% — sabse low."
• "Top 3 campaigns total revenue ka 55% de rahe hain."

Recommendations:

• Email budget 2× karo — highest ROAS.
• Social media ads optimize karo ya cut karo.
• Top 3 campaigns ki creative strategy duplicate karo.

SECTION 04Project 4: BFSI / Banking Analytics

Problem statement: "Kaunse loan segments mein default risk highest hai, aur NPA trend kya keh raha hai?"

Dataset:

• Kaggle: "Loan Default Prediction" ya "Credit Risk Dataset".
• Ya RBI ka public data — banking statistics.
• Columns: LoanID, Amount, Tenure, Interest, Default status, Segment.

SQL queries (10–15):

• Default rate by segment — GROUP BY segment.
• Average loan amount by default status.
• NPA trend (monthly / quarterly).
• Risk buckets — CASE WHEN by amount + tenure.
• Top default cases — ORDER BY + filtering.

Dashboard:

• KPI: Total loan book, default %, NPA %.
• Trend chart: NPA over time.
• Bar chart: Default by segment.
• Heatmap: Risk × amount.
• Filter: Segment, tenure, region.

Insights (3–5):

• "Unsecured loans mein default rate 8.4% — secured se 3× zyada."
• "Loan tenure >60 months mein NPA sabse high."
• "Q2 mein NPA 2.3% se 3.1% badha."

Recommendations:

• Unsecured loan approval criteria tighten karo.
• Long-tenure loans ke liye additional collateral.
• Q2 ke high-risk segments review karo.

SECTION 05Project 5: HR / Employee Analytics

Problem statement: "Kaunse departments aur tenure mein attrition highest hai, aur retention kaise improve karein?"

Dataset:

• Kaggle: "HR Analytics: Employee Attrition" ya "IBM HR Dataset".
• Columns: EmployeeID, Department, Tenure, Salary, Attrition, Performance.

SQL queries (10–15):

• Attrition rate by department — GROUP BY.
• Attrition by tenure bucket — CASE WHEN.
• Average salary by attrition status.
• Attrition by performance rating.
• Gender-wise attrition — diversity insight.

Dashboard:

• KPI: Total employees, attrition %, avg tenure.
• Bar chart: Attrition by department.
• Line chart: Attrition by tenure.
• Pie chart: Gender / age distribution.
• Filter: Department, salary band, performance.

Insights (3–5):

• "Sales department mein attrition 22% — highest."
• "1–3 saal tenure mein attrition 35% zyada."
• "Low salary band mein attrition 2× high."

Recommendations:

• Sales team ke liye retention bonus introduce karo.
• 1–3 saal wale employees ke liye career path clarity do.
• Low salary band ka compensation review.

SECTION 06Har Project Ka Common Structure

Ye 6 cheezein har project mein hone chahiye:

• 1. Problem statement: Clear business question — 2 lines mein.
• 2. Data source: Kaggle link, ya synthetic — credit do.
• 3. SQL analysis file: 10–15 queries with comments.
• 4. Dashboard: Power BI / Tableau with 4–5 visuals.
• 5. Insights (3–5): Numbers ke saath — kya nikla.
• 6. Recommendations (3): Business actions — kya karna chahiye.

GitHub README template:

• Title + 1-line description.
• Problem statement.
• Tools used (SQL, Power BI, etc.).
• Dashboard screenshots.
• Key insights (bullet points).
• Recommendations.
• How to run / reproduce.

LinkedIn post template:

• Hook line: "Sales 22% kaise gira? Is analysis mein pata chala..."
• Key insight + screenshot.
• Link to GitHub project.
• 3–5 hashtags: #DataAnalytics #SQL #PowerBI #Portfolio

SECTION 07Test Yourself — 5 Must-Have Projects

SECTION 08Frequently Asked Questions

SECTION 09Related Reads