https://cutt.

ly/keT5YbzV

https://cutt.ly/ueo9iQlc
https://cutt.ly/ueo9iQlc
https://cutt.ly/keT5YbzV
https://cutt.ly/4eo9uKJb
https://cutt.ly/neT5KGlH
 Advanced Excel & Power Query
Week 01

• Class - 1:
• Introduction to Data Analytics
• Understanding different Structure of Data and
how to Approach a Dataset
• Class -2:
• Important Excel Functions- Vlookup, Xlookup, Filter,
datediff, if, ifs, sumif, count, counta, countif, unique etc.

Week 02

• Class - 3:
• Conditional Formatting, Removing Duplicates, Number
Formatting. Summarizing & Reporting with Pivot Table.
• Class -4:
• Building Interactive Charts in Excel.
• Learning the Visualization Best Practices.

Week 03
• Class - 5:
• Data Cleaning, Consolidation & Automate workflow/
reporting with Power Query.
• Web Scraping using Power Query.
 Advanced Excel & Power Query
• Class -6:
• Statistical Data Analysis using Excel.
• Dashboard Building Best Practices.

Week 04

• Class - 7:
• Professional-looking Dashboard Building in Excel
(End-to-End Project).
• Explaining the Data Analysis Excel Project Outline.
• Class -8:
• Providing Feedback on Submitted Excel Projects.
By the end of this module, a student will have a clear
understanding of different dataset and how to approach
a dataset/problem; Root-cause Analysis; Ad-hoc and
Statistical Analysis. They will also learn applying different
Statistical Techniques to Draw Inference/Conclusion of
Data. By completing the End-to-End Project on
E-commerce/Sales Data they will enrich their Knowledge
in this Domain and would be able to Demonstrate their
Skillsets through Developing Interactive and Dynamic
Dashboard.
SESSION ON EXCEL:
Session 1: Setting Expectations and Utilizing Course Resources
for Best Outcomes
• How to align with course flow
• Proper use of support sessions
• How to be exceptional.
• Proper use of all course resources.
• Time management tips.
Session 2: Encapsulates the key functions and the practical
application through a case study
• Importance of knowing cell referencing.
• Exploring Text Function (LEFT, MID & RIGHT) and
Problem Solving
• Power of combining RIGHT, LEN & SEARCH
• Use case of INDEX, MATCH, OFFSET functions
Session 3: Enhancing Efficiency: Revising Lookup Functions
and Automating Reports
• Limitations of VLookup
• Benefit of using XLookup
• Comparing between V & X Lookup
• Solving Technical Case study
• Report automation
Session 4: Excel Excellence: Web Scraping to Dashboard
Creation with Pivot Tables & Power Query
• Scraping web data
• Exploring Pivot table
• Exploring some visuals and discussing how to make it
look good.
• Exploring Power Query
• Guidance of building amazing dashboard
 Power BI
Week 01
• Class - 1: Introduction to Power BI & Getting Data in Power BI
• Installing Power BI Desktop.
• Getting familiar with the Power BI User Interface, Features,
Settings, Strength & Weakness.
• Understanding the difference between the Power BI
Desktop vs. Service Features.
• Loading Data in Power BI.
• Import vs Direct Query Mode.
• Checking Quality of Data; Modifying existing and adding
new columns.
• Pivoting, Aggregating, Merging & Appending Data.

• Class -2: Data Modelling in Power BI

• Data Modelling in Power BI.
• Fact Table vs. Dimension Table.
• Star Schema vs. Snowflake Schema.
• Active vs. Inactive Relationship; Downstream Flow.

Week 02
• Class - 1: Getting Started with the Visualization Components
• Analyzing Data using available Visualization Components.
• Calculated Measures.
• Class - 2: DAX (Data Analysis Expression) Measures
• How are DAX Measures different from Calculated Measures
and why are they so useful?
• DAX Measures: Math & Stats, Iterator, Logical, Text.
 Power BI
Week 03
• Class - 1: DAX (Data Analysis Expression) Measures
• DAX Measures: CALCULA MMARIZE, FILTER, TOPN, etc.
• Difference between various Time Intelligence DAX
Measures.
• Class - 2: Data Analysis & Report Building in Power BI
• Exploring all the visuals.
• Exploring how tooltips, slicers & filters work?

Week 04
• Class - 1: Bookmark & Edit Interaction
• Taking the most out of Edit Interaction Feature.
• Use Bookmarks for custom-view reports.
• Class - 2: AI Visuals & Custom Visuals
• Kew Influencers, Decomposition Trees, Q&A.
• How to download and work with Custom Visuals.

Week 05
• Class - 1: Customizing Card & KPIs
• Customizing Card & KPIs for effective presentation
of your work.
• Customizing Formats of other Visuals.
• Class - 2: Creating a Professional Looking Power BI Dashboard
• Connecting the dots and creating a complete dashboard
on Healthcare/Hotel Management Data.
• Learning all the best practices.
 Power BI
Week 06
• Class - 1: Publishing the Report in Power BI Service (Web)
• Creating a 60-days free trial Pro Account in
Power BI Service.
• Hosting/Publishing the Report on Power BI Service.
• Getting familiar with different features and limitations
of Power BI Service.
• Class - 2: Advanced Features
• Setting up Row Level Security (RLS).
• How to Incrementally Refresh Data in Power BI
(parameterization).

Week 07
• Class - 1: End-to-End Data Analysis Project with Power BI
• Advanced Retail Customer Analytics [Cohort Analysis,
Customer Churn,
• Customer Lifetime Value, New vs Lost Cust Customers]
• End-to-End Project Solving in Live Class.
• Explaining the Assignment Project Outline & Instructions.
• Class - 2: Providing Feedback on Submitted Power BI Project.
• Resource Sharing & Tricks to become Microsoft Certified
Power BI Data Analyst Associate i.e. passing PL-300 Exam.
 Power BI
• Class - 2: Providing Feedback on Submitted Power BI Project.
• Resource Sharing & Tricks to become Microsoft Certified
Power BI Data Analyst Associate i.e. passing PL-300 Exam.
Working with Database (Import Mode) will be demonstrated
after SQL Part.
By the end of this module, Students would be able to
become an asset to any organization that works with Data
as well as would be able to Pass PL-300 Exam. They would
learn all the best practices to automate static reporting
of a company that would save thousands of hours and
share the reports with different levels of stakeholders
properly; They would learn about the different Licensing
Options of Power BI and when to pick which ones with
practical examples.
SESSION ON POWER BI:
Session 1: Power BI Fundamentals: Setup, Interface, and
Data Operations
• Exploring Power BI Interface
• Knowledge about Time intelligence setting.
• Exploring Data cleaning functions
• Frontend and backend of Power BI
• Adding data from multiple source
Session 2: Understanding Data Modelling in Power BI: Schemas
and Relationships & New KPI Card
• Case Studies
• Database Schemas types
• Data Flow
• Types of Table
• Explore New KPIS card
Session 3: DAX Functions in Power BI: A Comprehensive Guide to
Usage and Implementation
• Discussion about why we need to know DAX for analysis
• Case Studies
• Discussion about Data & Time DAX function
• Importance of using CALCULATE function
• Using of Dynamic Color Code & Sign in DAX
Session 4: Effective Usage of Numeric and Fields Parameters in
Power BI
• When we should use dynamic parameter
• Why it's important
• How Its work
• How to Create
SESSION ON POWER BI:
Session 5: Creating onMicrosoft account to publishing
Power BI Report
• Creating onMicrosoft Account
• Introduction to Microsoft Fabric
• Clamming Trial version
• How to edit
• How to publish
• How to share publishable link
Database Fundamentals & Data Analysis
using SQL (Snowflake)
Week 01
• Class - 1: Fundamentals of DBMS-I
• What is a Relational Database Management System?
• ACID Property.
• How is data stored in a relational database?
• Concept of Normalization.
• OLTP vs. OLAP.
• Database vs. Data Warehouse vs. Data Lake/Lakehouse.
• What is a NoSQL Database and BASE Property?
• Difference between relational and NoSQL Database.
• Which one should you choose in which case?
• For BI Solution, what should you choose to store
your data?
• What is Snowflake and what advantage does it provide?
(Micro-Partitioning)

• Class - 2: Fundamentals of DBMS-II

• Installing MySQL Locally.
• MySQL Workbench UI Tour.
• Concepts of Database, Schema, Table and Fields.
• Types of SQL Commands: DDL, DML, DCL, TCL, DQL.
• What are the Commands under each of these categories
and what does each of these commands do?
• As a Data Analyst & Data Scientist which Commands do
you only need to Master?
• Constraints: Primary Key, Foreign Key, Not Null, Unique,
Check, Default.
• Data Types: DATE, DATETIME, VARCHAR, INT, NUMBER, FLOAT,
Auto Increment.
Database Fundamentals & Data Analysis
using SQL (Snowflake)
Week 02
• Class - 1: Designing a Relational Database in Snowflake &
Bulk Insertion
• Designing and Creating a Database, Schema, Tables.
• Masterclass on DDL, DML, TCL & DQL
Commands/Statements.
• Bulk Insert of Data in Snowflake & MySQL.
• Creating File Format to Bulk Insert Data.
• Error Handling during Bulk Insertion.
• Class - 2: Exploratory Data Analysis using SQL
• Basic Queries: SELECT, FROM, WHERE, LIKE, ILIKE, IN, DISTINCT,
BETWEEN, GROUP BY, ORDER BY, LIMIT, OFFSET, ALIAS.
• Aggregate Functions: COUNT, SUM, AVG, MIN, MAX.
• Difference between WHERE and HAVING Clause.
• Some built-in Functions: EXTRACT, DATE_PART, TO_DATE,
TO_CHAR
• CASTING, SUBSTRING, POSITION, COALESCE, NULLIF
• Removing Duplicates

Week 03
• Class - 1: JOINING, UNION & CASE WHEN
• SQL Join: Left, Right, Inner & Full Join.
• Be aware of Cross Join!
• UNION, UNION ALL.
• SQL Code Order of Execution.
• Wide use of CASE WHEN Statement during data Cleaning,
Analysis & Feature Engineering.
Database Fundamentals & Data Analysis
using SQL (Snowflake)

• Class - 2: Subqueries and Common Table Expressions (CTEs)

• Subqueries in SELECT, FROM and WHERE Clause.
• Common Table Expressions (CTE).
• Between Subqueries and CTE, which one is more efficient
and takes less time?

Week 04
• Class - 1: Window Functions
• Window Functions, the most widely used SQL Commands
used by Data Analysts.
• Window Functions: RANK, DENSE_RANK, ROW_NUMBER,
LEAD, LAG, FIRST VALUE, AGGREGATE WINDOW FUNCTION,
FRAME SPECIFICATION, WINDOW CHAINING.
• Class - 2: RFM Segmentation
• Segmenting Customers based on their Recency,
Frequency & Monetary value using SQL.

Week 05
• Class - 1: Cohort Analysis & Churn Rate Calculation
• Cohort Analysis using Transactional Data of Customers
• Customer Lifetime Value, Retention, and Churn Rate
Calculation
Database Fundamentals & Data Analysis
using SQL (Snowflake)
• Class - 2: Views, Materialized Views and Project
• Difference between Views and Materialized Views and
when to use which one?
• Connecting Power BI with Snowflake Data Warehouse and
Working with Import and Direct Query Mode.
• Explaining End-to-end Project on Database Creation,
Data Insertion, Data Cleaning & Data Analytics Project
Outline and Instructions.
By the end of this module, Students would be able to
confidently perform any sort of Data Analysis and Reporting
for different Departments in any Organizations. They would
be well-equipped to understand any complex queries;
validating data and help any Business by generating
important KPIs. They would also be able to help Businesses
by Performing Complex Analyses like Cohort and RFM
Segmentation. They would understand the importance of
VIEWS, Stored Procedures, Triggers which would help them
when they would be working on BI (Power BI/Tableau, etc.)
Tools and Databases as Data Source.
SESSION ON SQL:
Session 1: Foundations of DBMS: Database Creation, Bulk
Insertion Vs SIngle Insertion, SQL Command Type,
Normalization, Constraints, ACID property , SQL VS
NoSQL & Data Types.
• What is DBMS
• SQL Constraints
• ACID Property
• Normalization
• Difference between SQL & NO SQL
• Types of SQL Command
• Exploring Data Types
• How to create Database, table, insert data
Session 2: Mastering SQL: GROUP BY, ORDER BY, HAVING, and
WHERE Clauses.
• Exploring Group BY
• Exploring ORDER BY
• LIMIT()
• Using of WHERE
• Using of HAVING
• WHERE VS HAVING
Session 3: SQL JOINS Demystified: Inner, Outer, Left, Right, and
More
• Why JOIN Is Important & how it works
• Describing type of SQL JOINS
• INNER, OUTER, LEFT, RIGHT
• Use case of SELF JOIN
• Case Studies on proper use of JOINS
SESSION ON SQL:
Session 4: SQL JOINS Demystified: Inner, Outer, Left, Right, and
More
• Why JOIN Is Important & how it works
• Describing type of SQL JOINS
• INNER, OUTER, LEFT, RIGHT
• Use case of SELF JOIN
• Case Studies on proper use of JOINS

Session 5 & 6: Mustering Advanced SQL: SUBQUERY, CTE, VIEW,

WINDOW FUNCTION, DATE FUNCTION & AGGREGATE
FUNCTIONS.
• Exploring Data-Time functions.
• Exploring Aggregate functions
• Exploring SubQuery, How many ways to write subqueries &
problem solving using subquery
• What is CTE, How It works. & problem solving using subquery.
• What is VIEW, why it's important & how to use
• Types of Window function: ROW_NUMBER, RANK, &
DENSE_RANK
• How Window functions works.
• Case Studies on Window function
• Interview question solve with window functions
Python for Data Analysis & Classical ML
Week 01
• Class - 1: Basics of Python Programming Language
• Introduction to Python Programming Language.
• Working on Google Colab.
• Python Data Types: Numeric, Strings, Boolean, List,
Dictionary, Tuple, Set, None.
• Getting Familiar with Pandas, Numpy and Matplotlib
• Class - 2: Data Wrangling with pandas and numpy
• Importing Data in Python using pandas.
• Data Wrangling using pandas and numpy.
• Visualization using matplotlib.

Week 02
• Class - 1: Visualization with matplotlib, seaborn & plotly
• Visualizing Data with matplotlib library
• Visualizing Data with seaborn library
• Class - 2: Hypothesis Testing using Python
• Parametric vs Non-parametric Tests.
• When to perform which one.
• Assumption Checking.
• Basics of Hypothesis Testing.
• T-test, Z-test, Chi-square Test, ANOVA.
• A/B Testing.
Python for Data Analysis & Classical ML
Week 03
• Class - 1: Concepts & Terms used in Machine Learning
• Basic Concepts & Underlying Theories required to
Understand ML.
• Machine Learning Project Lifecycle and Practice
Implication in Business.
• Class - 2: Outlier Detection & Data Cleaning in Python
• IQR & Boxplot Method to Detect Outliers.
• Standardization and Z-score of Data to normalize.
• Imputing Missing Values.
• Different Encoding Techniques

Week 04
• Class - 1: End to End Supervised ML Project
• Statistical Approach with “statsmodels” library.
• ML Approach with “scikit-learn” library.
• Class - 2: Clustering for Personal Marketing Strategy/Product
Development
• Customer Personality Analysis using Clustering
(Unsupervised ML)
• Presenting Clustering Outputs in a way that can be
utilized generating business values.
Python for Data Analysis & Classical ML
By the end of this module, Students will have a fundamental
knowledge of Python Programming Language and all the
necessary Libraries used for Data Analysis and Machine
Learning. They would be able to analyze and Visualize any
dataset including Marketing Campaign Data. They will have
fundamental and business knowledge of ML Algorithms
and how their methodologies can impact business-decision
making. They would know how to present their findings
appropriately to their Stakeholders.
SESSION ON PYTHON:
Session 1: Setting Environment for Python and exploring
numpy, panda
• Setting Google Colab
• Connect data source
• Embed with google drive
• Exploring Panda
• Exploring Numpy
Session 2: Exploring Basic Data visualization technique
• Exploring Basic Seaborn library
• Exploring Basic Matplotlib library
• Exploring Basic Plotly library
Session 3: Revising Hypothesis Testing
• Parametric vs Non-parametric Tests.
• When to perform which one.
• Assumption Checking.
• Basics of Hypothesis Testing.
• T-test.
• A/B Testing.
Session 4: Exploring Machine Learning technique
• Types of Machine Learning algorithm
• Regression
• K Means Clustering
 Statistics with R for Research
Week 01
• Class - 1: Basics of R Programming Language
• Introduction to R Programming Language.
• Installing R & Rstudio.
• Basics of R Programming.
• Vector, Matrix, Factor, Data Frame, List.
• Selection of Elements in Vector, Matrix, Factor,
Data Frame, List.
• Nominal and Ordinal Categorical Variables/Factor.
• Class - 2: Data Wrangling & Visualization using dplyr,
ggplot2 packages
• Importing Data in R.
• Data Wrangling using dplyr.
• Visualization using ggplot2.

Week 02
• Class - 1: Major Characteristics of Data
• Statistics: Statistics is used in almost any field; What
concepts only require doing your job successfully as a
Data Analyst and Data Scientist?
• Measures of Central Tendency [Mean, Median, Mode], and
Location [Quartile, Decile, Percentile].
• Measures of Dispersion [Range, Standard Deviation,
Variance, Coefficient of Variation].
• Measures of Shape Characteristics: Skewness, Kurtosis.
• Normal Distribution as a Standard and Central Limit
Theorem.
 Statistics with R for Research

• Class - 2: Survey Data Analysis using R & How to write a

Research Paper using Rmarkdown
• How to Analyze a Survey/Research Data using R
• What is causality and what is the difference between
Correlation and Regression Analysis?
• Performing Correlation and Regression Analysis; Also,
creating publication standard tables and charts.
• Interpreting the Results Properly
• Difference between Linear and Logistic Regression.
• Interpreting the results of Logistic Regression (Odds Ratio).
• How to Write a Research Paper with Publication Standards
Plots and Tables.
Using the knowledge gained in this module, students would
be able to generate Reproducible Research Work which
would help them in their Higher Studies. By learning the
different steps of a Research Project and various Important
Statistical Methodologies students/professionals would be
largely benefitted in Higher Studies and Development
Projects.
 Job Preparation
Week 01
• Class - 1:
• CV Making & How to Write a Cover Letter; Portfolio Building.
• Interview Skill Development & How to effectively Apply
for a Position.
• Class - 2:
• Guidance on Freelancing Career,
• Mock Interview & Roadmap for Future Ahead.
SESSION FOR GETTING JOB POST:
Session 1:
• Understanding the difference Between Data Science,
Data Analytics & Data Engineering
Session 2:
• Creating Linkedin Account, tips on how to turn on job
alerts and get job posts regularly.
Session 3:
• Understanding The Responsibility of SImilar job types
( Data Analyst, BI Analyst, Business Analyst etc)
Session 4:
• Importance of using Glassdoor & how to know the
company well.

SESSION FOR CRACK JOB & JOB INTERVIEW:

Session 1:
• How to build a Resume which creates an impression on HR.
Session 2:
• What is Portfolio, Why It's Important to Get a job.
Session 3:
• Tips on How to Crack Technical Interview and its time
management.
Session 4:
• Tips on How to Crack HR Interview and create a good
impression about you on them.