Data Analysis Roadmap

Course Objective:

• Understand the complete data analysis lifecycle, from collecting data to communicating insights.
• Build strong foundations in spreadsheets, statistics, SQL, and Python.
• Clean, transform, validate, and analyze real-world datasets.
• Create meaningful charts, dashboards, and reports using Power BI and Tableau.
• Apply exploratory data analysis (EDA), descriptive statistics, and basic inferential statistics.
• Develop business problem-solving, storytelling, and stakeholder communication skills.
• Complete portfolio-ready projects and prepare for Data Analyst roles.

1

Introduction to Data Analysis

• What is Data Analysis?
• Why organizations use data.
• Data Analyst roles and responsibilities.
• Data Analyst vs Data Scientist vs Data Engineer.
• Types of data: structured and unstructured.
• Qualitative and quantitative data.
• Descriptive, diagnostic, predictive, and prescriptive analytics.
• Data analysis lifecycle.
• Business questions and problem framing.
• Data sources and common file formats.
• CSV, XLSX, JSON, and database tables.
• Ethics, privacy, and responsible data use.

2

Excel & Spreadsheet Fundamentals

• Excel interface, workbooks, worksheets, and cells.
• Data entry, formatting, and number formats.
• Sort and Filter.
• Tables and structured references.
• Relative, absolute, and mixed cell references.
• Basic formulas: SUM, AVERAGE, MIN, MAX, COUNT.
• Logical functions: IF, AND, OR, IFERROR.
• Conditional functions: SUMIF, SUMIFS, COUNTIF, COUNTIFS.
• Text functions: LEFT, RIGHT, MID, TRIM, CONCAT.
• Date and time functions.
• Lookup functions: VLOOKUP, XLOOKUP, INDEX, MATCH.
• Remove duplicates and data validation.
• PivotTables and PivotCharts.
• Conditional formatting.
• Excel charts and basic dashboards.

3

Statistics for Data Analysis

• Population, sample, parameter, and statistic.
• Data types and measurement scales.
• Mean, median, and mode.
• Range, variance, and standard deviation.
• Quartiles, percentiles, and interquartile range.
• Outliers and box plots.
• Probability fundamentals.
• Common probability distributions.
• Sampling methods and sampling bias.
• Normal distribution and z-scores.
• Correlation and covariance.
• Correlation vs causation.
• Confidence intervals.
• Hypothesis testing and p-values.
• Type I and Type II errors.
• A/B testing fundamentals.

4

SQL Fundamentals

• What is a database and DBMS?
• Relational databases, tables, rows, and columns.
• Installing or accessing MySQL / PostgreSQL.
• SELECT and DISTINCT.
• WHERE and filtering operators.
• ORDER BY and LIMIT.
• AND, OR, NOT, IN, BETWEEN, LIKE.
• NULL values and IS NULL.
• Aggregate functions: COUNT, SUM, AVG, MIN, MAX.
• GROUP BY and HAVING.
• CASE expressions.
• Aliases and calculated columns.
• Primary keys and foreign keys.
• Constraints and data types.
• INSERT, UPDATE, and DELETE basics.

5

SQL Joins & Advanced Queries

• INNER JOIN.
• LEFT JOIN and RIGHT JOIN.
• FULL OUTER JOIN concepts.
• SELF JOIN.
• CROSS JOIN.
• Joining multiple tables.
• Subqueries and correlated subqueries.
• Common Table Expressions (CTEs).
• UNION and UNION ALL.
• Window functions.
• ROW_NUMBER, RANK, and DENSE_RANK.
• LAG and LEAD.
• PARTITION BY and window frames.
• Date functions and time-based analysis.
• String and numeric functions.
• Views and temporary tables.
• Query execution plans and indexing basics.
• Writing readable, efficient analytical queries.

6

Python Programming for Data Analysis

• Python installation and environment setup.
• Jupyter Notebook and Google Colab.
• Variables, data types, and type conversion.
• Operators and expressions.
• Strings and string methods.
• Lists, tuples, sets, and dictionaries.
• if, elif, and else statements.
• for and while loops.
• Functions, arguments, and return values.
• List comprehensions.
• Modules and packages.
• Reading and writing CSV and text files.
• Exception handling.
• Basic debugging and code organization.
• Working with dates and times.

7

Python Libraries — NumPy & Pandas

• Python package installation with pip.
• NumPy arrays and array attributes.
• Array creation, indexing, and slicing.
• Vectorized operations and broadcasting.
• Basic statistical operations with NumPy.
• Pandas Series and DataFrames.
• Reading CSV, Excel, JSON, and SQL data.
• Inspecting data with head, info, describe, and shape.
• Selecting rows and columns with loc and iloc.
• Filtering and sorting DataFrames.
• Handling missing values.
• Removing duplicates.
• Changing data types.
• Creating and transforming columns.
• GroupBy and aggregation.
• Merge, join, and concatenate.
• Reshaping with pivot, melt, and crosstab.
• Exporting cleaned datasets.

8

Data Cleaning & Preparation

• Why data quality matters.
• Identify missing, invalid, and inconsistent values.
• Handle missing data with appropriate methods.
• Remove duplicates and resolve conflicting records.
• Standardize text, dates, units, and categories.
• Detect outliers and investigate their causes.
• Validate ranges, formats, and business rules.
• Combine data from multiple sources.
• Data type conversion and parsing.
• Reshape data from wide to long format.
• Document cleaning decisions.
• Build repeatable cleaning workflows.
• Data quality checks and validation reports.

9

Exploratory Data Analysis (EDA)

• What is EDA and why it matters?
• Understand dataset structure and context.
• Univariate analysis.
• Bivariate and multivariate analysis.
• Frequency tables and summary statistics.
• Distribution analysis and skewness.
• Outlier investigation.
• Correlation analysis.
• Segmenting and comparing groups.
• Time-based patterns and seasonality.
• Feature relationships and data leakage awareness.
• Forming and testing analytical questions.
• Summarize findings and limitations.

10

Data Visualization with Matplotlib & Seaborn

• Principles of effective data visualization.
• Choosing charts for the analytical question.
• Line charts and time-series plots.
• Bar charts and grouped bars.
• Histograms and density plots.
• Scatter plots and bubble charts.
• Box plots and violin plots.
• Heatmaps and correlation matrices.
• Labels, titles, legends, and annotations.
• Subplots and chart layout.
• Color, accessibility, and avoiding misleading charts.
• Saving and exporting figures.
• Presenting a data story with visuals.

11

Power BI Fundamentals

• Introduction to Business Intelligence.
• Power BI Desktop interface.
• Importing Excel, CSV, database, and web data.
• Power Query Editor.
• Data types and column profiling.
• Filtering, splitting, merging, and appending queries.
• Data model and relationships.
• Star schema fundamentals.
• Calculated columns and measures.
• Core visuals, slicers, and filters.
• Formatting report pages.
• Power BI Service and workspaces.
• Publishing and sharing reports.
• Refresh schedules and gateway concepts.

12

DAX & Power BI Data Modeling

• DAX fundamentals and formula syntax.
• Calculated columns vs measures.
• Row context and filter context.
• CALCULATE and filter functions.
• SUMX, AVERAGEX, COUNTROWS, DISTINCTCOUNT.
• RELATED and lookup concepts.
• Date tables and time intelligence.
• Year-to-date and prior-period comparisons.
• Relationships: one-to-many and many-to-many concepts.
• Fact and dimension tables.
• Model performance and measure organization.
• Drill-through, tooltips, bookmarks, and interactions.
• Row-level security basics.
• Dashboard usability and accessibility.

13

Tableau Fundamentals

• Introduction to Tableau and its interface.
• Connecting to files and databases.
• Dimensions and measures.
• Discrete and continuous fields.
• Building worksheets and dashboards.
• Filters, sorting, and groups.
• Calculated fields and table calculations.
• Parameters and sets.
• Maps and geographic visualization.
• Dashboard actions and interactivity.
• Story points and presenting insights.
• Publishing to Tableau Public / Server concepts.
• Performance and dashboard design practices.

14

Business Intelligence & KPI Reporting

• Translate business needs into analytical questions.
• Understand KPIs, metrics, and targets.
• Sales, marketing, finance, and operations metrics.
• Revenue, profit, margin, growth, and conversion rates.
• Customer retention, churn, and cohort concepts.
• Funnel and campaign analysis.
• Operational efficiency and service-level metrics.
• Build KPI scorecards and management dashboards.
• Set metric definitions and reporting rules.
• Automate recurring reports where appropriate.
• Explain trends, drivers, and exceptions.

15

Advanced Analytics & Business Statistics

• Sampling distributions and standard error.
• Confidence intervals in practice.
• Choosing and interpreting hypothesis tests.
• Comparing groups and proportions.
• Chi-square test concepts.
• Correlation and simple linear regression.
• Multiple regression fundamentals.
• Regression assumptions and residuals.
• Confounding variables and bias.
• A/B test design and interpretation.
• Effect size vs statistical significance.
• Practical limitations and communicating uncertainty.

16

Time Series & Forecasting Basics

• Datetime indexing and resampling.
• Trend, seasonality, and noise.
• Moving averages and rolling windows.
• Period-over-period and year-over-year analysis.
• Decomposition concepts.
• Train-test split for time series.
• Baseline forecasts and error metrics.
• Forecasting limitations and uncertainty.
• Communicating forecast assumptions.

17

Data Warehousing & ETL Fundamentals

• OLTP vs OLAP.
• Data warehouse and data mart concepts.
• ETL vs ELT.
• Extracting data from files, APIs, and databases.
• Transformations and loading strategies.
• Fact tables and dimension tables.
• Star and snowflake schemas.
• Slowly changing dimensions concepts.
• Data pipelines and orchestration basics.
• Data validation and lineage.
• Introduction to cloud data platforms.
• Warehouse tools overview: BigQuery, Snowflake, and Redshift.

18

APIs, Web Data & Automation

• What is an API?
• HTTP methods and status codes.
• JSON data structures.
• Making API requests with Python requests.
• Query parameters and headers.
• Authentication concepts and secret safety.
• Pagination and rate limits.
• Handling errors and retries.
• Reading data from public datasets.
• Automating repetitive data preparation.
• Scheduling scripts and documenting workflows.
• Responsible web data collection and terms of use.

19

Git, GitHub & Professional Workflow

• Version control fundamentals.
• Git init, status, add, commit, and log.
• Branches and merging.
• GitHub repositories and README files.
• Organizing notebooks, scripts, data, and reports.
• Using .gitignore and protecting credentials.
• Documenting project assumptions and methods.
• Reproducible analysis and environment notes.
• Peer review and collaboration basics.

20

Data Storytelling & Communication

• Identify the audience and decision needed.
• Structure an insight-led presentation.
• Explain findings in plain language.
• Separate observations, interpretations, and recommendations.
• Show context, comparisons, and uncertainty.
• Choose clear charts and annotations.
• Write concise executive summaries.
• Present limitations and data quality concerns.
• Handle stakeholder questions.
• Communicate actionable findings without overstating evidence.

21

Data Governance, Privacy & Quality

• Data governance fundamentals.
• Data ownership and stewardship.
• Data quality dimensions.
• Metadata, dictionaries, and documentation.
• Access control and least-privilege principles.
• Personal and sensitive data awareness.
• Data minimization and safe handling.
• Consent, retention, and organizational policies.
• Bias, representativeness, and fairness considerations.
• Audit trails and reproducible reporting.

22

Portfolio Project 1 — Excel Sales Analysis

• Choose a sales dataset and define business questions.
• Inspect, clean, and validate the data.
• Use formulas and PivotTables for summaries.
• Analyze sales by product, region, and month.
• Calculate revenue, growth, and contribution metrics.
• Create charts and an interactive Excel dashboard.
• Write key findings and limitations.
• Publish a clear project README and screenshots.

23

Portfolio Project 2 — SQL Business Analysis

• Design or load a small relational dataset.
• Define business questions and metrics.
• Write filtering, aggregation, and join queries.
• Use CTEs and window functions.
• Analyze customers, products, orders, and trends.
• Check for duplicates and inconsistent records.
• Export query results for visualization.
• Document query logic and findings.

24

Portfolio Project 3 — Python EDA

• Select a public dataset with a clear question.
• Load and inspect data using Pandas.
• Clean missing values and inconsistent fields.
• Perform descriptive statistics and EDA.
• Create visualizations using Matplotlib and Seaborn.
• Test relationships and segment patterns.
• Summarize insights, caveats, and next steps.
• Publish a reproducible notebook on GitHub.

25

Portfolio Project 4 — Power BI / Tableau Dashboard

• Translate stakeholder needs into dashboard requirements.
• Prepare and model the data.
• Define measures and KPIs.
• Build overview and drill-down pages.
• Add filters, slicers, and interactions.
• Apply accessible and consistent design.
• Validate totals against source data.
• Publish dashboard screenshots or a public link where appropriate.
• Present a short insight-led walkthrough.

26

Capstone Project — End-to-End Analytics

• Select a realistic business or public-interest problem.
• Define scope, stakeholders, and measurable questions.
• Collect data from suitable sources.
• Clean, validate, and document the data.
• Use SQL and/or Python for analysis.
• Apply appropriate statistical methods.
• Build a dashboard or visual report.
• Prepare an executive summary and presentation.
• Discuss limitations, privacy, and assumptions.
• Deliver a reproducible portfolio package.

27

Interview & Career Preparation

• Data Analyst role requirements and job descriptions.
• Excel interview questions and exercises.
• SQL query challenges and case studies.
• Python and Pandas coding practice.
• Statistics and analytical reasoning questions.
• Dashboard critique and visualization questions.
• Business case and metric interpretation.
• Explain projects using problem, process, and impact.
• Resume and LinkedIn project presentation.
• Mock interviews and communication practice.
• Build a learning plan for continued development.

Skills You'll Gain:

• Ability to frame business questions and follow a complete data analysis workflow.
• Excel skills for cleaning, analysis, PivotTables, and reporting.
• SQL skills for querying relational data and writing analytical queries.
• Python skills using NumPy, Pandas, Matplotlib, and Seaborn.
• Understanding of descriptive statistics, hypothesis testing, and analytical uncertainty.
• Ability to clean, validate, transform, and explore datasets.
• Power BI or Tableau dashboard development skills.
• Ability to define KPIs, communicate insights, and create data stories.
• Experience completing portfolio projects and documenting work with GitHub.
• Foundational knowledge of data warehousing, ETL, APIs, and automation.
• Preparation for entry-level Data Analyst, BI Analyst, and Reporting Analyst roles.

Duration:

Typically 24 to 32 weeks with regular practice. The timeline depends on prior experience, study hours, and project depth.

Certification:

Course completion certificate, if offered by the training provider. Optional external credentials may include Microsoft Power BI, Tableau, or data analytics certificates. Verify current exam and credential requirements before enrolling.