Video summary

COMPLETE Data Analytics Portfolio Project in 6 EASY Steps | Python + SQL + Power BI

Main summary

Key takeaways

Educational

Main ideas / lessons conveyed (end-to-end workflow)

  • The video demonstrates an advanced, company-standard, end-to-end data analytics project suitable for a portfolio/resume.

  • Real projects start with a business problem statement, then move through:

    1. Python (data cleaning + EDA + feature engineering)
    2. PostgreSQL/SQL (deeper analysis with advanced queries)
    3. Power BI (interactive dashboard with KPI visuals)
    4. Project documentation (project report)
    5. Stakeholder communication (client-ready presentation deck)
    6. Public showcase (GitHub repository + README, plus LinkedIn sharing)

  • It emphasizes practical corporate realities:

    • You rarely get perfect data; you must work with what’s available.
    • Cleaning should be done “properly,” not with simplistic shortcuts.
    • Managers/clients may not read full reports; presentations are more important.
    • Versioned/public work (GitHub) helps recruiters.

Methodology / step-by-step instructions

1) Define the business problem

  • Formulate a problem statement based on what stakeholders actually care about.
  • Example business question used:
    • How to leverage customer shopping data to identify trends, improve engagement, and optimize marketing/product strategies.

2) Data understanding (dataset walkthrough)

  • The dataset structure is described as:
    • One row per customer, representing their latest purchase.
  • Key columns include:
    • customer ID, age, gender, item purchased, category, purchase amount, location, size, color, season, review rating, subscription status, shipping type, discount applied, promo code used, previous purchases, payment method, frequency of purchases.
  • Important limitation discussed:
    • previous purchases and frequency of purchases are counts/labels, not full transaction histories.

3) Python workflow (Jupyter Notebook)

A. Load and inspect data

  • Import pandas and read CSV:
    • pd.read_csv(...)
  • Inspect:
    • df.head() for preview
    • df.info() for row/column counts + data types
    • df.describe() for summary statistics
      • Use include='all' to include categorical stats too
  • Check missing values:
    • Identify nulls (example: review rating has 37 null values)

B. Handle missing values correctly (recommended approach)

  • Consider why not to use mean/overall median:
    • Mean is sensitive to outliers.
    • Overall median can introduce bias by ignoring category differences.
  • Instruction implemented:
    • Impute review rating with the median within each category
  • Steps:
    • Group by category
    • Fill nulls using category-specific median values
    • Verify nulls are resolved afterward

C. Standardize column names (“snake casing”)

  • Problem: mixed uppercase/lowercase and spaces/special characters make code error-prone.
  • Instruction:
    • Convert column names to lowercase
    • Replace spaces with underscores
    • Rename problematic columns (example: purchase amount USD → purchase_amount)
  • Implement with functions like lower() and replace (via renaming steps).

D. Feature engineering

  • Create an age group feature:
    • Use pd.qcut to split ages into 4 equal-sized groups
    • Assign labels: young adult, adult, middle-aged, senior
  • Create numeric purchase frequency days:
    • Map text frequency labels (e.g., weekly/monthly) to days
    • Use a dictionary mapping + .map(...)
  • Remove redundancy:
    • Check whether discount applied equals promo code used across all rows
    • If identical, drop redundant column:
      • Drop promo code used and keep discount applied

4) Move data to SQL (PostgreSQL as the example)

A. Create database and connect

  • Use pgAdmin to:
    • Create a database (example name: customer behavior)
  • Install required packages in Python environment:
    • psycopg2
    • SQLAlchemy
  • Connect using credentials:
    • username, password, host, port, database name
  • Create/Load a table from the pandas DataFrame:
    • Send DataFrame to PostgreSQL as a new table (example: customer)

B. SQL portability note

  • If using other DBs:
    • MySQL: install PyMySQL
    • SQL Server: install pyodbc
  • Core steps remain similar (change credentials + driver).

C. Answer business questions using SQL (examples shown)

The video presents a list of SQL analyses; main concepts include:

  • Revenue by gender

    • Aggregate purchase amount grouped by gender

  • Customers using discounts but still spending above average

    • Filter rows where discount applied = yes
    • Use subquery to compare purchase amount to overall average

  • Top 5 products by highest average review rating

    • Group by item purchased
    • Compute AVG(review rating)
    • Order descending + limit 5
    • Note: casting may be needed to use ROUND properly (double precision → cast to numeric)

  • Average spend comparison: standard vs express shipping

    • Filter shipping type IN ('standard','express')
    • Group by shipping type
    • Compare averages

  • Subscription impact: average spend + total revenue

    • Group by subscription status
    • Compute counts, average purchase amount, and sum revenue
    • (Interpretation: subscribers vs non-subscribers behavior)

  • Products with highest discount reliance (discount rate)

    • Compute percentage of purchases where discount was applied per product:
      • SUM(CASE WHEN discount applied='yes' THEN 1 ELSE 0 END) / COUNT(*) * 100
    • Order by discount rate desc + limit 5

  • Customer segmentation: new vs returning vs loyal

    • Use rules based on previous purchases:
      • previous purchases = 1 → new
      • 2 to 10 → returning
      • > 10 → loyal
    • Implement using a CTE + CASE, then count per segment

  • Top 3 most purchased products within each category

    • Use window functions and ranking:
      • Window rank with ROW_NUMBER() partitioned by category
    • Return rows where rank ≤ 3
    • Rationale:
      • ROW_NUMBER avoids issues with ties that affect RANK/DENSE_RANK behavior when selecting top-N distinct rows

  • Do repeat buyers also subscribe?

    • Repeat buyers defined as previous purchases > 5
    • Group by subscription status after filtering repeat buyers
    • Compare counts for subscribed vs not subscribed

  • Revenue by age group

    • Group by age group
    • Sum purchase amount
    • Order by total revenue desc

5) Power BI dashboard build

A. Connect Power BI to the SQL database

  • Create a blank report
  • Get Data → connect to PostgreSQL (or other DB types)
  • Provide:
    • server/host, port
    • database name (customer behavior)
  • Load table(s) and verify columns.

B. Create measures (core KPIs)

  • number of customers = count(customer ID)
    • (The video notes distinct count isn’t needed because each row is a customer)
  • average purchase amount = AVERAGE(purchase amount)
  • average review rating = AVERAGE(review rating)

C. Visuals and formatting instructions (high-level)

  • Use a card visual (multi-card layout) for KPI display:
    • Configure formatting: alignment, font size, shapes, colors, glow/shadow
    • Correct misleading units:
      • Show 3.9K instead of an incorrect 4K
    • Set currency formatting to USD
  • Add a donut chart for subscription distribution:
    • Values: count of customer IDs by subscription status
    • Show percent of total, update labels and title
    • Remove legend to reduce clutter
  • Add charts:
    • Revenue by category (clustered column chart using purchase amount aggregated)
    • Sales by category (clustered column chart using count(customer ID))
    • Revenue by age group
    • Sales by age group
  • Add slicers for interactivity (filtering dashboard):
    • Subscription status slicer (button)
    • Gender slicer (button)
    • Category slicer (vertical button)
    • Shipping type slicer (list)

D. Layout guidance

  • Adjust canvas size in Power BI (custom height/width)
  • Use shapes/containers to improve aesthetics and spacing
  • Align/distribute elements horizontally
  • Ensure visuals don’t render behind shapes (grouping/positioning fixes)

E. Save the dashboard

  • Save as “customer behavior dashboard”.

6) Create project report and presentation deck

A. Project report (documentation)

  • Write step-by-step what you did (e.g., loaded data, handled nulls, created new columns, ran SQL, etc.)
  • Insert screenshots of code/outputs
  • Add business recommendations
  • Key lesson:
    • Corporate stakeholders often won’t read the full report; it’s mainly documentation.

B. Stakeholder-ready presentation (recommended)

  • Use an AI tool called Gamma to generate a professional presentation:
    • Create new → import file → upload report
    • Choose presentation + layout (minimal/concise)
    • Generate in seconds
    • Edit/tweak if needed
  • Outcome:
    • Produces charts/slides using provided screenshots and appears polished for stakeholders.

7) Publish on GitHub + marketing (portfolio visibility)

A. Create GitHub repository

  • New repository → name it (example: customer behavior analysis)
  • Add README
  • Optionally add license (example: MIT)
  • Upload project files and commit changes

B. Build README (using ChatGPT option)

  • README is the “cover page” of the GitHub repo.
  • Suggestion:
    • Generate a professional README via ChatGPT using a prompt covering:
      • dataset loading
      • EDA & cleaning
      • running SQL queries
      • building Power BI dashboard
  • Use sections like: Overview, Dataset, Tools, Dashboard, Results.

C. Share on LinkedIn for recruiter visibility

  • Export presentation to PDF
  • Create LinkedIn post:
    • Add document (PDF)
    • Include title like “customer behavior data analysis”
    • Share GitHub link/PPT
    • Tag the creator (example: @Amlan) for increased reach

Main concepts highlighted (what to learn from the project)

  • Portfolio differentiation: one project that covers the whole pipeline (Python → SQL → Power BI → reporting/presentation → GitHub).
  • Data cleaning correctness matters:
    • Example: category-wise median imputation reduces bias.
  • Schema consistency reduces errors:
    • Snake casing avoids quoting/formatting issues in Python and SQL.
  • Feature engineering improves analytical power:
    • age grouping and numeric conversion of frequency.
  • SQL depth:
    • subqueries, conditional aggregation (CASE), CTEs, window functions.
  • Dashboard interactivity:
    • slicers + charts built around KPIs and segmentation.
  • Communication strategy:
    • report = documentation (nice to have)
    • presentation = what stakeholders actually use
  • Distribution strategy:
    • GitHub + LinkedIn sharing makes work visible to recruiters.

Speakers / sources featured

  • Speaker (primary): The video’s narrator/instructor, referred to by name Amlan (spoken in the subtitles: “but Amlan, we don’t know…”).
  • Tools mentioned as sources: Gamma (AI presentation generator), ChatGPT (for README generation).
  • Software platforms mentioned: Python, Jupyter Notebook, PostgreSQL/pgAdmin, MySQL/MS SQL Server (alternatives), Power BI, GitHub, LinkedIn.

Original video