DATA ANALYTICS

WITH AI

Complete Course Syllabus • 2026

Python • PostgreSQL • Data Analysis • AI Tools • Excel & Advanced Excel • Power BI • Streamlit

Beginner-friendly | Hands-on practice | Portfolio projects | End-to-end capstone

Course Overview

This course takes learners from programming fundamentals to practical data analytics and business intelligence. Students learn to collect, store, clean, analyse and visualize data, use AI tools responsibly to improve productivity, and present findings through Excel, Power BI and interactive Python applications.

Learning Outcomes

• Write Python programs and work with files and structured data.
• Query relational databases using PostgreSQL and SQL.
• Analyse data first with Core Python and then with NumPy and Pandas.
• Collect permitted public data using web scraping and APIs.
• Build charts, reports, Excel dashboards, Power BI reports and Streamlit apps.
• Use AI tools to support learning, formula creation, SQL/Python debugging, documentation and insight generation while validating outputs.
• Publish projects on GitHub and present a complete end-to-end analytics portfolio.

Course Roadmap

1. Python Programming Fundamentals
2. SQL and PostgreSQL
3. Project 1: Student Management System — Python + PostgreSQL
4. Core Python Data Analysis (no third-party data libraries)
5. Project 2: E-Commerce Sales Analysis using Core Python
6. NumPy, Pandas, Matplotlib and Seaborn
7. Project 3: E-Commerce Exploratory Data Analysis
8. Web Scraping and Data Collection
9. Project 4: Product Data Collection and Analysis
10. Streamlit Application Development
11. Project 5: Interactive Sales Analytics Dashboard
12. Git and GitHub
13. Excel and Advanced Excel with AI Tools
14. Project 6: Excel Sales Analytics Dashboard
15. Project 7: Integrated SQL + Python + Excel Reporting
16. Power BI and DAX
17. Project 8: Power BI Sales Performance Dashboard
18. Project 9: End-to-End E-Commerce Analytics and BI Capstone

Phase 1: Python Programming Fundamentals

Objective: Build the programming foundation required for data analytics.

Topics

• Python introduction, use cases in analytics, installation, VS Code, IDLE and Jupyter Notebook.
• Syntax, comments, indentation, variables, naming rules, keywords and built-in data types.
• Input/output, type conversion, type checking and formatted strings.
• Arithmetic, comparison, logical, assignment, membership and identity operators.
• Conditional statements: if, elif, else and nested conditions.
• Loops: for, while, range(), break, continue, pass and nested loops.
• Data structures: lists, tuples, sets and dictionaries; methods, indexing, slicing and nested structures.
• Functions: parameters, arguments, return values, default/keyword arguments, scope and lambda basics.
• String methods, slicing, searching, replacing, splitting and formatting.
• File handling: text files and CSV files with Python’s built-in csv module.
• Exception handling with try, except, else and finally.
• Modules, imports, debugging and introductory object-oriented programming: classes, objects, constructors and methods.

Practice: 50+ beginner-to-intermediate programs, logic-building exercises, data-structure tasks and file-handling assignments.

Phase 2: SQL and PostgreSQL

Objective: Store, retrieve, filter and analyse relational data.

Topics

• Database concepts, relational tables, schema, records and relationships.
• Install PostgreSQL and pgAdmin; create databases and tables.
• SQL data types, primary keys, foreign keys and constraints.
• CREATE, INSERT, SELECT, UPDATE and DELETE statements.
• Filtering with WHERE, AND, OR, NOT, IN, BETWEEN, LIKE and IS NULL.
• Sorting and limiting results with ORDER BY and LIMIT.
• Aggregate functions: COUNT, SUM, AVG, MIN and MAX; GROUP BY and HAVING.
• INNER, LEFT, RIGHT and FULL OUTER JOIN.
• Subqueries, CASE WHEN, string functions, date functions and NULL handling with COALESCE.
• Views, Common Table Expressions (CTEs), basic indexes and query performance concepts.
• Window functions: ROW_NUMBER, RANK, DENSE_RANK, LAG and LEAD.
• Connect Python to PostgreSQL using psycopg; execute queries and process returned records.

Project 1: Student Management System — Python + PostgreSQL

• Design student, course, marks and attendance tables.
• Build a menu-driven application to create, view, update and delete records.
• Connect Python to PostgreSQL and validate user input.
• Generate reports for total marks, percentages, grades, attendance and toppers.
• Submit source code, database schema, SQL script and README documentation.

Phase 3: Data Analysis Using Core Python Only

Objective: Understand data-analysis logic before using specialist libraries.

Restriction for this phase: use Core Python only. Do not use NumPy, Pandas, Matplotlib or Seaborn.

Topics

• Understand the analytics process, business questions and dataset structure.
• Read CSV data using the built-in csv module; inspect records and fields.
• Detect missing, invalid and duplicate records using loops and conditions.
• Clean data and build reusable functions for validation and transformation.
• Calculate totals, averages, percentages, growth rates and summary statistics.
• Group records with dictionaries; filter and sort records.
• Find top products, highest-value orders and category/region summaries.
• Analyse dates and monthly trends using suitable Core Python logic.
• Generate text-based reports and export cleaned data or results to CSV.

Project 2: E-Commerce Sales Analysis Using Core Python

• Use an order dataset containing order ID, date, product, category, quantity, price, customer and region.
• Load and inspect the CSV; validate fields and clean inconsistent rows.
• Calculate revenue, order counts, average order value and monthly sales.
• Identify best-selling products, high-value orders, top customers and regional/category performance.
• Generate a summary report and export results to CSV without third-party data-analysis libraries.

Phase 4: NumPy, Pandas, Matplotlib and Seaborn

Objective: Use industry-standard Python libraries for efficient data analysis and visualization.

NumPy

• Arrays, dimensions, shape, data types, indexing, slicing and reshaping.
• Vectorized operations, broadcasting, mathematical functions, aggregations, sorting and filtering.
• Basic numerical statistics and comparison of Python lists with NumPy arrays.

Pandas

• Series and DataFrames; import CSV and Excel files.
• Inspect data, select rows/columns, filter, sort and create or modify columns.
• Convert data types; handle missing values and duplicates.
• String and date-time operations; groupby and aggregation.
• Merge, join and concatenate datasets; pivot tables and cross-tabulations.
• Exploratory Data Analysis (EDA), data quality checks and exporting results.

Matplotlib

• Figures and axes; line, bar, horizontal bar, pie, histogram and scatter charts.
• Titles, labels, legends, annotations, axis formatting and chart layouts.

Seaborn

• Count plots, bar plots, distributions, box plots and outlier analysis.
• Scatter/regression plots, pair plots, heatmaps, correlation and category comparisons.

Project 3: E-Commerce EDA and Visualization

• Import and clean e-commerce data with Pandas; use NumPy for numerical calculations.
• Analyse revenue, quantity, orders, product/category performance, customers and regions.
• Calculate monthly growth and average order value.
• Build charts with Matplotlib and Seaborn and explain the business findings in a Jupyter Notebook.

Phase 5: Data Collection and Web Scraping

Objective: Collect permitted online data and prepare it for analysis.

Topics

• Data collection concepts, HTML structure and webpage elements.
• HTTP requests and responses; use requests and BeautifulSoup.
• Extract text, links, product names, prices, ratings and categories where publicly available.
• Pagination, multiple pages, missing fields, inconsistent formats and duplicate removal.
• Save collected information to CSV and prepare it for Python analysis.
• Introduction to APIs, JSON and structured data retrieval.
• Responsible scraping: website terms, robots.txt, rate limits, privacy and access restrictions.

Project 4: Product Data Collection and Analysis

• Collect permitted product information from a suitable public source or practice dataset.
• Clean product names, prices, ratings and categories; remove duplicates.
• Analyse price ranges, category summaries and highest/lowest-priced products.
• Export a clean dataset and create a short analytical report.

Phase 6: Streamlit Application Development

Objective: Turn Python analysis into an interactive browser-based application.

Topics

• Install Streamlit, understand app structure and run apps locally.
• Display text, tables, metrics and dataframes.
• CSV file upload, dataset preview and data validation.
• Sidebar navigation, columns, containers and page layout.
• Widgets: selectbox, multiselect, slider, date input, buttons and filters.
• Display Matplotlib and Seaborn charts, KPIs and analytical summaries.
• Organize code into reusable modules; manage requirements.txt and dependencies.
• Use secrets safely; never commit passwords or API keys to GitHub.
• Deploy through Streamlit Community Cloud using a GitHub repository.
• Debug application errors, dependency problems and deployment failures.

Project 5: Interactive Sales Data Analytics Dashboard

• Upload and preview a sales CSV file.
• Display total revenue, order count, quantity and other relevant KPIs.
• Filter by date, product, category and region.
• Show monthly trends, category comparisons and top-performing products.
• Display regional analysis, cleaned records and downloadable filtered data.
• Deploy the application and document the public app link.

Phase 7: Git and GitHub

Objective: Use version control and create a professional project portfolio.

Topics

• Git concepts, installation and configuration.
• Repositories, working directory, staging area and commits.
• Commands: git init, git status, git add, git commit, git log, git clone, git pull and git push.
• Connect local projects to GitHub; branches, merging and conflict basics.
• Use .gitignore and keep credentials, secrets and private data out of repositories.
• Write README files with project purpose, setup steps, screenshots, dataset notes and results.
• Upload scripts, notebooks, SQL files and documentation; connect GitHub to Streamlit deployment.

Phase 8: Excel, Advanced Excel and AI Tools

Objective: Use Excel for data preparation, analysis, reporting and productivity—with AI-assisted workflows.

A. Excel Fundamentals

• Workbook and worksheet navigation, cell references, data entry and formatting.
• Number, date, currency and percentage formats; tables, named ranges and freeze panes.
• Sort, filter, remove duplicates, data validation and conditional formatting.
• Import and export CSV files; identify blank, inconsistent and incorrectly typed values.

B. Formulas and Functions

• SUM, AVERAGE, MIN, MAX, COUNT and COUNTA.
• IF, nested IF, AND, OR, IFERROR and SWITCH where appropriate.
• SUMIF, SUMIFS, COUNTIF and COUNTIFS.
• VLOOKUP, XLOOKUP, INDEX and MATCH.
• LEFT, RIGHT, MID, TRIM, CLEAN, CONCAT and text-cleaning techniques.
• DATE, YEAR, MONTH, TODAY and date-based calculations.
• Absolute and relative references; percentage change, growth and variance calculations.

C. Advanced Excel

• Advanced lookup formulas and multi-condition lookups.
• Dynamic array functions: FILTER, SORT, UNIQUE and related functions where supported by the Excel version.
• Nested formulas, error handling and formula auditing.
• Advanced conditional formatting and data validation lists.
• PivotTables, PivotCharts, grouping, calculated summaries and slicers.
• Dashboard layout, KPI cards, interactive filters and chart selection.
• Power Query: import, clean, transform, append and merge data; refresh queries.
• Data Model and basic Power Pivot concepts where available.
• What-if analysis with Goal Seek and Scenario Manager; basic forecasting concepts.
• Protecting worksheets, print setup, report formatting and presenting findings.

D. AI Tools for Excel and Data Analytics

• Use AI assistants such as ChatGPT or Microsoft Copilot when available to explain formulas and suggest formula patterns.
• Generate and debug Excel formulas from clearly described business requirements.
• Ask AI to explain PivotTable results, suggest chart types and draft dashboard layouts.
• Use AI to create sample datasets, data dictionaries, test cases and practice questions.
• Use AI to help write, explain and troubleshoot Power Query M steps and basic VBA concepts when relevant.
• Use AI to interpret SQL queries, explain Python errors and document analysis workflows.
• Prompting basics: provide column names, sample rows, desired output and constraints.
• Verification skills: test generated formulas against sample records, check calculations, validate claims and inspect edge cases.
• Data privacy: do not upload confidential customer, financial or employer data to unapproved AI tools.
• Responsible use: AI output is a draft, not a substitute for understanding, validation or business judgment.
• Tool availability and features may depend on subscription, organization policy and product version.

Project 6: Excel Sales Analytics Dashboard

• Import a sales dataset and clean it using Excel tools and Power Query.
• Calculate revenue, profit, quantity, order count and percentage growth.
• Analyse sales by product, category, region and month.
• Build PivotTables, PivotCharts, slicers and KPI summaries.
• Create a clean one-page management dashboard.
• Use an AI assistant to help explain formulas and suggest improvements, then verify every result.

Project 7: Integrated SQL + Python + Excel Reporting System

• Store orders, customers and product records in PostgreSQL.
• Write SQL joins, aggregations, CTEs and window-function queries.
• Retrieve data from PostgreSQL through Python and clean it using Pandas.
• Calculate KPIs and export clean datasets and summary tables to Excel.
• Create PivotTables, charts and a management dashboard.
• Document the data flow, SQL scripts, Python code, workbook and business insights.

Phase 9: Power BI and Business Intelligence

Objective: Build interactive reports and business dashboards.

Power BI Fundamentals and Power Query

• Power BI Desktop interface, reports, visuals and data connections.
• Import Excel, CSV and database data; profile and transform data types.
• Remove duplicates, replace values, split/merge columns and create conditional/custom columns.
• Append and merge queries; refresh data sources.

Data Modeling and DAX

• Fact and dimension tables, relationships and star-schema fundamentals.
• One-to-many relationships, filter direction and date tables.
• Calculated columns versus measures.
• DAX functions: SUM, COUNT, DISTINCTCOUNT, AVERAGE, DIVIDE, IF, SWITCH, CALCULATE, FILTER and SUMX.
• Basic time intelligence, month-over-month and year-over-year analysis.

Report Design and Publishing

• KPI cards, bar charts, line charts, tables, matrices and combo charts.
• Slicers, drill-down, drill-through, tooltips and conditional formatting.
• Consistent layouts, accessible colours, titles and report navigation.
• Publish to Power BI Service; understand workspaces, sharing permissions and refresh concepts.

Project 8: Power BI Sales Performance Dashboard

• Transform the sales dataset using Power Query.
• Build a suitable model and create DAX measures.
• Analyse sales, profit, order volume, products, regions and monthly performance.
• Create interactive report pages and explain key findings in a business presentation.

Phase 10: End-to-End Capstone Project

Objective: Integrate data collection, database skills, Python analysis, reporting and deployment.

Project 9: E-Commerce End-to-End Data Analytics and Business Intelligence System

Business goal: create a complete analytics workflow that converts raw e-commerce data into reliable, useful business insights.

Project Modules

• Module 1 — Business problem: define scope, questions, KPIs and a data dictionary.
• Module 2 — Data collection: use a permitted public source, API or prepared dataset; document data source and limitations.
• Module 3 — PostgreSQL: design orders, customers, products and category tables; load and validate data.
• Module 4 — Data quality: detect missing values, duplicates, invalid dates and inconsistent prices or quantities.
• Module 5 — Python analysis: use Pandas and NumPy for cleaning, EDA and calculations; visualize findings with Matplotlib and Seaborn.
• Module 6 — SQL analytics: create sales summaries, monthly trends, rankings, reusable views and window-function queries.
• Module 7 — Excel: create cleaned reports, PivotTables, charts and an executive dashboard; use AI tools for assistance and verify results.
• Module 8 — Power BI: create a data model, DAX measures and interactive report pages.
• Module 9 — Streamlit: build an interactive app with filters, KPI metrics, charts and downloadable reports.
• Module 10 — GitHub and deployment: organize files, write documentation and publish the app where suitable.
• Module 11 — Presentation: explain the business problem, methodology, findings, limitations and recommendations.

Final Capstone Deliverables

• PostgreSQL database design and SQL scripts.
• Python scripts and Jupyter Notebook.
• Cleaned dataset and data dictionary.
• Excel workbook and management dashboard.
• Power BI report.
• Streamlit application and deployment link.
• GitHub repository with README and setup instructions.
• Project report, screenshots, presentation and interview explanation.

Project Summary

Recommended Learning Method

• Learn each topic from fundamentals, then complete guided practice exercises.
• Complete the relevant project before moving to the next phase.
• Keep project folders, code, datasets and documentation organized.
• Use AI tools as learning assistants; independently test code, formulas, SQL queries and insights.
• Build a GitHub portfolio and practise explaining each project in interviews.

END OF SYLLABUS