انتقل إلى المحتوى

المحتوى الرئيسي لهذه الصفحة باللغة الإنجليزية.ترجمة هذه الصفحة

خرائط الطريق المهنية

Data Analyst roadmap

Data analysts answer business questions with data: which products sell, why customers leave, where money is spent. It is one of the most accessible tech careers, and a great first step towards data science.

الوقت
⏱ 3–4 months
Level
Beginner
Steps
6 steps
Architecture ↓Cheat sheet ↓5 interview Q&As ↓
مشاركة

The roadmap

Click a step to open the full guide.

Best architecture: a modern analytics stack

The usual path from company data to trusted dashboards.

Analytics stack

  1. App DBs, CRM, Excel
  2. Ingest (ELT)
  3. Data warehouse
  4. Transform & model (star schema)
  5. BI dashboards
  6. Decisions

Why it's built this way

  • Load raw data first, transform in the warehouse (ELT), so you can rebuild anything.
  • Model data as a star schema: facts + dimensions.
  • Define metrics once (a semantic layer) so every dashboard agrees.
  • Use row-level security لعام sensitive data.

Data Analyst cheat sheet

The essentials on one screen. Bookmark it for revision.

Excel

Lookup=XLOOKUP(key, keys, values, "Not found")
Conditional sum=SUMIFS(sales, region, "North")
Count by rule=COUNTIFS(status, "Paid")
Clean spaces=TRIM(A2)
Safe divide=IFERROR(A2/B2, 0)

SQL & pandas

SQL groupSELECT region, SUM(sales) FROM t GROUP BY region
SQL rankRANK() OVER (ORDER BY sales DESC)
pandas readdf = pd.read_csv("sales.csv")
pandas groupdf.groupby("region")["sales"].sum()
pandas pivotdf.pivot_table(index="month", columns="region", values="sales", aggfunc="sum")

Improve performance

  • Filter early in SQL; aggregate in the database, not in Excel.
  • Use Excel Tables and Power Query instead of thousands of formulas.
  • In BI tools, model as a star schema and remove unused columns.
  • In pandas, use vectorised operations instead of loops.

Security checklist

  • Share dashboards only with people who should see the data; use row-level security.
  • Mask or remove personal data in shared files.
  • Don't email raw data exports; use access-controlled links.
  • Keep database credentials out of shared workbooks and notebooks.

Most important interview questions

Click a question to see a strong answer. Each step also has its own questions.

1What does a data analyst do?

Collects, cleans and analyses data to answer business questions and builds reports and dashboards that drive decisions.

2Explain the different SQL joins.

INNER returns matches only; LEFT returns all left rows plus matches; FULL returns everything from both; use them to combine related tables.

3How do you ensure your analysis is correct?

Validate data sources, check row counts and totals against known figures, sanity-check outliers, and have someone review the logic.

4Which chart would you use for trends, comparisons and composition?

Line for trends over time, bar for comparing categories, stacked bar (or a simple table) for composition; avoid 3D and too many pie charts.

5What is a KPI? Give examples.

A key metric tied to goals, e.g. revenue, conversion rate, churn rate, average order value, customer acquisition cost.

Related خرائط طريق

6

Data Scientist

6 steps · 6–8 months · Intermediate

Use statistics and machine learning to predict and explain.

View roadmap →
6

PostgreSQL & SQL

6 steps · 4–5 weeks · Beginner

The database most new apps choose.

View roadmap →
6

Python

6 steps · 6–8 weeks · Beginner

The language of AI, data and automation.

View roadmap →
1

Data Analyst · Step 1 of 6 · 2 weeks

Excel and spreadsheets

The tool every business uses.

Excel is still the most used analysis tool in the world. Pivot tables alone answer a huge share of business questions.

Learn to clean data (trimming spaces, fixing dates, removing duplicates) before analysing it.

What you'll learn

  • Formulas and cell references
  • XLOOKUP / VLOOKUP
  • Pivot tables
  • Conditional formatting
  • Charts
  • Data cleaning in Excel

Excel analysis flow

  1. Raw data
  2. Clean
  3. Format as Table
  4. Pivot table
  5. Chart
  6. Insight

Key concepts

Pivot table
Drag-and-drop summary by categories.
XLOOKUP
Find a value in one table and return a matching value from another.
Absolute reference
$A$1 stays fixed when you copy a formula.

Step by step

  1. 1.1

    Formulas

    SUM, AVERAGE, IF, COUNTIFS, SUMIFS, TEXT and dates.

  2. 1.2

    Lookups

    XLOOKUP to bring data from one table to another.

  3. 1.3

    Pivot tables

    Summarise by month, region and product in seconds.

  4. 1.4

    Clean data

    TRIM, remove duplicates, fix dates, split columns.

Notes

  • XLOOKUP replaces VLOOKUP in modern Excel.

Tips & tricks

  • Use keyboard shortcuts; they double your speed.
  • Turn ranges into Tables so formulas grow with data.

Interview questions

VLOOKUP vs XLOOKUP?

XLOOKUP can look left or right, defaults to exact match, handles missing values and doesn't break when columns are inserted. VLOOKUP only looks right using a column number.

The quick way to study this

Practise pivot tables on a sales dataset until you can answer any 'by month, by region' question in a minute.

2

Data Analyst · Step 2 of 6 · 3 weeks

SQL

The most important skill for analysts.

SQL lets you query data where it lives, in company databases. Most analyst interviews include a SQL test.

Window functions (ranking, running totals, period-over-period) are what separate beginners from strong analysts.

What you'll learn

  • SELECT, WHERE, ORDER BY
  • GROUP BY and aggregates
  • JOINs
  • Subqueries and CTEs
  • Window functions
  • Date functions

SQL execution order

  1. FROM/JOIN
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

Key concepts

JOIN
Combine rows from tables using a shared key.
Window function
Calculations across rows without collapsing them.

Step by step

  1. 2.1

    Basics

    SELECT, WHERE, ORDER BY, LIMIT.

  2. 2.2

    Aggregates

    GROUP BY with COUNT/SUM/AVG; HAVING for group filters.

  3. 2.3

    Joins

    Combine customers, orders and products.

  4. 2.4

    Window functions

    Rankings, running totals, month-over-month changes.

Notes

  • Read queries in this order: FROM, WHERE, GROUP BY, SELECT, ORDER BY.

Tips & tricks

  • Solve two SQL practice problems every day.
  • Always check row counts after a JOIN.

Interview questions

How do you calculate month-over-month growth in SQL?

Aggregate by month in a CTE, then use LAG(revenue) OVER (ORDER BY month) and compute (revenue - prev) / prev.

How do you find duplicate records?

SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

The quick way to study this

Learn the basics in a week, then spend two weeks on daily practice problems.

PostgreSQL roadmap →
3

Data Analyst · Step 3 of 6 · 2 weeks

Statistics for analysis

Make sound conclusions from data.

Statistics keeps you from fooling yourself and others. Know when a difference is real and when it is noise.

A/B testing is common in product companies; understand sample size and significance.

What you'll learn

  • Averages, median, spread
  • Distributions
  • Correlation vs causation
  • Sampling
  • A/B testing basics
  • Confidence intervals

A/B test

  1. Hypothesis
  2. Split users randomly
  3. Run until sample size
  4. Compare metric
  5. Decide

Key concepts

Median
The middle value; robust to outliers.
Statistical significance
Evidence a difference is unlikely to be random chance.
Correlation ≠ causation
Two things moving together doesn't mean one causes the other.

Step by step

  1. 3.1

    Describe data

    Mean, median, spread, percentiles.

  2. 3.2

    Compare groups

    Is the difference real or noise?

  3. 3.3

    A/B tests

    Sample size, significance and practical impact.

Notes

  • Use the median when data has outliers, such as salaries or prices.

Tips & tricks

  • Always ask: compared to what?

Interview questions

Mean vs median: when to use which?

Use the median for skewed data with outliers (salaries, house prices); the mean when data is roughly symmetric.

The quick way to study this

Learn the core ideas with visual examples, then apply each to a dataset you know.

4

Data Analyst · Step 4 of 6 · 3 weeks

Dashboards with Power BI or Tableau

Show data so people act on it.

Dashboards turn analysis into something managers use every day. Pick one tool (Power BI is popular in India and in Microsoft-heavy companies) and go deep.

Good dashboards answer a few questions clearly. Start with the decision the dashboard should support.

What you'll learn

  • Connecting data sources
  • Data modelling
  • DAX or calculated fields
  • Choosing the right chart
  • Dashboard design
  • Sharing and refresh

Star schema

Sales (facts)
  • Date
  • منتج
  • Store
  • Customer

Key concepts

Star schema
One fact table (sales) linked to dimension tables (date, product, store).
Measure
A calculation that responds to filters, like Total Sales.
KPI
Key performance indicator: the few numbers that matter most.

Step by step

  1. 4.1

    Connect data

    Import from Excel, SQL or CSV; set refresh.

  2. 4.2

    Model

    Fact and dimension tables with relationships (star schema).

  3. 4.3

    Measures

    DAX or calculated fields for totals, % change, YTD.

  4. 4.4

    Design

    Key numbers on top, trends next, details last.

Notes

  • Fewer charts, clearer titles, consistent colours.

Tips & tricks

  • Put the most important number top-left.
  • Write chart titles as findings: 'Sales fell 12% in March'.

Interview questions

How do you design a good dashboard?

Start from the decision it supports, show 3–5 KPIs at the top, trends and breakdowns below, consistent colours, clear titles that state findings, and filters only where needed.

The quick way to study this

Rebuild a public dashboard you like, then make your own on a new dataset.

Practice project

A sales dashboard with filters by region, product and month.

5

Data Analyst · Step 5 of 6 · 3 weeks

Python for analysis

pandas and charts for bigger or repeat work.

Python handles data too big for Excel and automates repeated analysis. pandas is the key library.

AI assistants are excellent at writing pandas code; your job is to know what to ask and check the result.

What you'll learn

  • Python basics
  • pandas DataFrames
  • Cleaning and reshaping data
  • matplotlib / seaborn charts
  • Jupyter notebooks
  • Automating reports

Excel vs pandas

Excel

  • Quick, visual
  • Up to ~1M rows
  • Manual repeats

pandas

  • Code, repeatable
  • Millions of rows
  • Automate reports

Key concepts

DataFrame
A table in Python (rows and columns).
groupby
pandas' pivot-table equivalent.

Step by step

  1. 5.1

    pandas basics

    Read CSV/Excel, select, filter, sort.

  2. 5.2

    Group and pivot

    groupby().agg() and pivot_table.

  3. 5.3

    Charts

    Line, bar and histogram with matplotlib/seaborn.

  4. 5.4

    Automate

    Turn a weekly Excel task into a script.

Notes

  • groupby + agg is the pandas version of a pivot table.

Tips & tricks

  • Keep notebooks tidy: one question per section, conclusions in text.

Interview questions

How do you handle missing values?

Understand why they're missing, then drop, fill (median, mode, forward-fill for time series) or flag them, and document the choice.

The quick way to study this

Redo one Excel analysis in pandas to learn the equivalents.

Python roadmap →
6

Data Analyst · Step 6 of 6 · 2 weeks

Portfolio and job search

Prove you can answer business questions.

Show three projects, each answering a clear business question with a recommendation. Publish dashboards and notebooks online.

In interviews, explain your reasoning, not just results.

What you'll learn

  • Case-study write-ups
  • Storytelling with data
  • Interview SQL and case questions
  • LinkedIn and GitHub presence

Analysis to action

  1. Question
  2. البيانات
  3. Analysis
  4. Insight
  5. Recommendation
  6. Decision

Key concepts

Data storytelling
Presenting data so people understand and act.

Step by step

  1. 6.1

    Pick business questions

    Sales, churn, marketing ROI, operations.

  2. 6.2

    Tell the story

    Finding → evidence → recommendation.

  3. 6.3

    Publish

    Dashboards and notebooks online with short write-ups.

Notes

  • Recruiters skim: put the business result first in every project.

Tips & tricks

  • Use datasets from Indian e-commerce, cricket or public government data to stand out.

Interview questions

Sales dropped 15% last month. How would you investigate?

Confirm the data is correct, then break it down by region, product, channel and customer segment, compare with seasonality and last year, check external events and pricing, and present the main drivers with a recommendation.

The quick way to study this

Polish your best two projects and write them up this week.

شارك هذه الصفحة