Data analyst
toolkit & roadmap.
Everything a data analyst needs to learn, in the right order.
خريطة تعلّم أدوات محلل البيانات خطوة بخطوة: إيه الأدوات اللي تتعلمها، وبأي ترتيب، وإيه اللي لازم تتقنه في كل أداة.
Time and project estimates from this roadmap. Your pace will depend on practice and prior experience.
Five things every data analyst must learn.
Tools
الأدوات
- Excel
- Power BI
- SQL
- Python
- Tableau
Statistics
الإحصاء
- Descriptive statistics
- Probability & distributions
- Sampling
- Inferential statistics
- Hypothesis testing
- Correlation & regression
- A/B testing
Data
البيانات
- Quantitative: discrete · continuous
- Qualitative: nominal · ordinal
- Structured · semi · unstructured
- Primary · secondary sources
- Data collection
- Data quality & cleaning
- Data privacy & ethics
Business understanding
فهم البيزنس
- Business objectives
- Problem framing
- KPIs & metrics
- Stakeholders
- Business processes
- Domain knowledge
Business insights & data storytelling
الـ Insights وحكي البيانات
- What · So what · Now what
- Root-cause analysis
- Recommendations
- Storytelling arc
- Choosing the right chart
- Dashboards & presentations
What a data analyst
actually does all day.
Ask
Turn a vague business request into a measurable question and a KPI.
Collect
Pull data from files, databases and APIs.
Clean
Fix types, nulls, duplicates and messy text. Usually 60% of the job.
Model
Relate tables in a star schema so numbers add up correctly.
Analyze
Aggregate, compare periods, rank, test hypotheses.
Communicate
Dashboards and a short story a manager can act on.
Your path to practical data skills.
All topics shown across 8 stages.
الأدوات بتجاوب على أسئلة، والمرحلة دي بتعلّمك تسأل السؤال الصح: أهداف البيزنس، الـ KPIs، أنواع الداتا، الإحصاء، و Data Storytelling. دي اللي بتتختبر في الـ Case Study في الإنترفيو.
Illustrative business example
What?
The observation, with the number
Repeat customers fell from 42% to 35% this quarter.
So what?
Why the business should care
Each lost repeat customer costs about 3× a new one to replace, so margin drops.
Now what?
The recommendation and its expected impact
Launch a 30-day win-back offer for lapsed customers; target +4 pts repeat rate.
Business objectives & problem framing
Must- SMART objectives
- Business question → analytical question
- Stakeholder requirements
- Hypothesis-driven analysis
- Issue trees (MECE)
- Defining success up front
KPIs & metrics
Must- Metric vs KPI
- Leading vs lagging
- North-star metric
- KPI trees
- Targets & benchmarks
- Metric definitions
- Ratios, rates & averages traps
KPIs by domain
Must- Sales: revenue, margin, AOV
- Marketing: CAC, ROAS, conversion
- Customer: churn, retention, CLV
- CX: CSAT, NPS, FCR, AHT
- HR: attrition, tenure, absenteeism
- Finance: budget vs actual, variance
Data types & structures
Must- Structured · semi · unstructured
- Nominal · ordinal
- Discrete · continuous
- Interval vs ratio
- Primary vs secondary data
- Granularity
- Fact vs dimension
- Tidy data
Statistics for analysts
Must- Mean · median · mode
- Range · IQR · std dev
- Distributions & skew
- Percentiles & outliers
- Sampling & bias
- Correlation ≠ causation
- Confidence intervals
- Hypothesis tests & p-values
- A/B testing logic
- Simpson’s paradox
Types of analysis
Must- Descriptive · diagnostic
- Predictive · prescriptive
- EDA
- Trend & seasonality
- Segmentation
- Cohort analysis
- Funnel analysis
- Variance analysis
Business insights framework
Must- Observation → insight → action
- What · So what · Now what
- Quantify the impact
- Root-cause (5 Whys)
- Pyramid principle
- Recommendation with owner & date
Data storytelling
Must- Know your audience
- One message per page
- Context → tension → resolution
- Titles that state the finding
- Right chart for the message
- Declutter
- Colour for emphasis only
- Annotations
Data quality
Should- Accuracy
- Completeness
- Consistency
- Timeliness
- Validity
- Uniqueness
- Data dictionary
Analytics lifecycle
Should- Ask · Prepare · Process
- Analyze · Share · Act
- CRISP-DM overview
Data ethics & privacy
Should- Personal data (PII)
- Anonymisation
- Consent & purpose
- Local data-protection laws
- Bias in data & metrics
Milestone project
Pick a business you know (a café, an online store, a call center). Write its objective, build a KPI tree with 8–10 metrics and their definitions, list the data you would need with its type and grain, and present one insight using What / So what / Now what on a single slide.
You’re ready to move on when
- You can turn "sales are bad" into three measurable questions
- You can explain why median is safer than mean for salaries
- You can name the KPIs of at least one industry and how each is calculated
- You can write one insight with a recommendation and an expected impact
Leave for later
- Probability theory and formula proofs
- Advanced statistics such as Bayesian methods
- Memorising KPIs from industries you will not work in
أي شركة شغالة على Excel، وأغلب الإنترفيوهات بتبدأ بتاسك عليه. وهو كمان بيعلّمك المنطق اللي هتستخدمه في باقي الأدوات.
Interface & speed
Must- Ribbon & Backstage
- Quick Access Toolbar
- Keyboard shortcuts
- Manage sheets
- Efficient data entry
- Flash Fill
Formula foundations
Must- Formula syntax
- SUM · AVERAGE · MIN · MAX
- COUNT · COUNTA · COUNTBLANK
- Relative vs absolute $
- Named ranges
- Error checking
Logic & conditional maths
Must- IF · nested IF
- IFS
- AND · OR
- IFERROR · IFNA
- SUMIFS · COUNTIFS · AVERAGEIFS
Lookups
Must- XLOOKUP
- INDEX + MATCH
- VLOOKUP exact & approx
- HLOOKUP
Sort, filter & dynamic arrays
Must- Multi-level sort
- Custom lists
- Advanced Filter
- SORT · SORTBY
- FILTER
- UNIQUE
Text & date functions
Must- LEFT · RIGHT · MID
- LEN · TRIM
- CONCAT · TEXTJOIN
- EDATE · EOMONTH
- WORKDAY · NETWORKDAYS
- DATEDIF · YEARFRAC
Charts that tell the story
Must- Chart anatomy
- Column · bar · line
- Clustered vs stacked
- Waterfall & funnel
- Histogram & Pareto
- Sparklines
- Dynamic charts on Tables
Control & quality
Should- Data validation drop-downs
- Conditional formatting
- Protect sheets & lock cells
- Cell styles
- RANK.EQ · AGGREGATE
Dynamic referencing
Optional- OFFSET
- INDIRECT
- Buttons & hyperlinks
Milestone project
Build an attendance tracker and an inventory invoice from raw lists, then a one-page sales report whose charts update automatically when new rows are added to the Table.
You’re ready to move on when
- You can join two lists with XLOOKUP and summarise them with SUMIFS without searching online
- You can explain when to use A1, $A$1 and A$1
- You can pick the right chart for a comparison, a trend and a share of total
Leave for later
- Memorising every function in the library
- Macros and VBA (stage 2)
- Heavy formatting and decoration
هنا Excel بيتحول لأداة BI. الـ Pivot والـ Power Query والـ Power Pivot هي نفس المحركات اللي جوه Power BI.
Data structure & preparation
Must- Format as Excel Table
- Tidy-data rules
- Cleaning checklist
- Quick EDA
PivotTables
Must- Pivot components
- Build & pivot fields
- Slicers & timelines
- Grouping
- Calculated fields
- Layouts & styles
- Report filter pages
- Pivot charts
Power Query in Excel
Must- Import from files & folders
- Clean & change types
- Merge & append
- Unpivot
- One-click refresh
Power Pivot & the Data Model
Must- Add tables to the model
- Relationships
- Star schema intro
- First DAX measures
Dashboards & storytelling
Must- Storytelling fundamentals
- Types of data
- Choosing chart types
- Dashboard types
- One-page interactive dashboard
What-if & forecasting
Should- Goal Seek
- Scenario Manager
- One- & two-variable data tables
- Forecast Sheet
Descriptive statistics in Excel
Should- Percentiles & quartiles
- Standard deviation
- CORREL
- Analysis ToolPak
Automation with Macros & VBA
Optional- Developer tab
- Record a macro
- Relative references
- Read & clean VBA
Milestone project
Contact-center and marketing-analytics dashboards: three related tables loaded through Power Query, modelled in Power Pivot, summarised with PivotTables and controlled by slicers on one page.
You’re ready to move on when
- You can build a refreshable pipeline: raw file → Power Query → Data Model → Pivot dashboard
- You can relate tables instead of writing VLOOKUPs between them
- You can write three insights under a dashboard, not just show it
Leave for later
- Advanced VBA programming
- Complex 3D or decorative charts
- Building forecasts you cannot explain
Power BI هو أكتر أداة داشبورد مطلوبة في وظايف المحللين. المرحلة دي بتغطي الشغل كله من الداتا الخام لحد تقرير منشور وبيتحدث لوحده.
Ecosystem & setup
Must- Desktop vs Service vs Mobile
- Licenses: Free · Pro · PPU · Premium
- Workspaces
- Report · Data · Model views
- .pbix & .pbit
Get data
Must- Excel · CSV · JSON
- SQL Server · PostgreSQL
- SharePoint
- Web & APIs
- Import vs DirectQuery vs Live
Power Query transformation
Must- Data types
- Split & merge columns
- Replace & fill down
- Remove duplicates
- Pivot / unpivot
- Group by
- Conditional & custom columns
- Append & merge joins
- Query dependencies
Data modeling
Must- Star vs snowflake
- Fact vs dimension
- Primary & foreign keys
- Cardinality
- Cross-filter direction
- Active vs inactive
- Date hierarchies
- Display folders
DAX fundamentals
Must- Measures vs columns
- Row vs filter context
- SUM · AVERAGE · COUNT
- CALCULATE
- ALL · FILTER
- IF · SWITCH
- CALENDAR date table
Time intelligence
Must- TOTALYTD · TOTALQTD
- SAMEPERIODLASTYEAR
- DATEADD
- Period-to-date
Intermediate DAX
Must- ALLEXCEPT · ALLSELECTED
- % of total
- RELATED · RELATEDTABLE
- USERELATIONSHIP
- SUMX · AVERAGEX
- RANKX & Top N
Visualization & interactivity
Must- Choosing the right visual
- Cards & KPIs
- Tables & matrices
- Maps
- Conditional formatting
- Tooltips
- Drill down & drillthrough
- Bookmarks & buttons
- Mobile layout
Power BI Service
Must- Publish
- Workspace roles
- Share & apps
- Subscriptions & alerts
- Scheduled refresh
- Basic RLS
Design & performance
Should- Layout & white space
- Accessibility
- Naming conventions
- Performance Analyzer
- Query folding
- Reduce model size
Incremental refresh & custom visuals
Optional- Incremental refresh
- AppSource visuals
- Ribbon & funnel charts
Milestone project
Five portfolio dashboards: CFO financial performance, B2B sales pipeline, HR attrition, supply chain and contact-center performance. Each with a star schema, a date table and at least ten measures.
You’re ready to move on when
- You can design a star schema from flat Excel files
- You can explain filter context and why CALCULATE changes it
- You can publish a report with scheduled refresh and share it as an app
- You can answer the beginner-to-intermediate interview questions in Module 12
Leave for later
- Every custom visual on AppSource
- Pie charts with more than four slices
- DAX patterns from stage 06 before your model is clean
SQL هي أكتر مهارة مطلوبة في وظايف الداتا. داتا الشركات الحقيقية في قواعد بيانات، واللي يعرف يكتب Query مش بيستنى حد يصدّرله ملف.
Database foundations
Must- Tables, rows, keys
- Relational vs non-relational
- PostgreSQL + pgAdmin
- Databases · schemas · roles
SELECT & filtering
Must- SELECT · AS
- WHERE
- AND · OR · NOT
- BETWEEN · IN
- LIKE · ILIKE
- IS NULL
- DISTINCT
- ORDER BY · LIMIT
Aggregation & grouping
Must- COUNT · SUM · AVG
- GROUP BY
- HAVING vs WHERE
- COUNT(DISTINCT)
- ROUND
Joins
Must- INNER JOIN
- LEFT · RIGHT
- FULL OUTER
- Self join
- 3+ tables
- Fan-out & duplicate traps
- ON vs WHERE
Conditional logic & functions
Must- CASE WHEN
- COALESCE · NULLIF
- TRIM · CONCAT · REPLACE
- SPLIT_PART
- EXTRACT
- DATE_TRUNC
- AGE · intervals
Subqueries & CTEs
Must- Scalar subqueries
- IN · EXISTS
- Correlated subqueries
- Derived tables
- WITH (CTE)
- Multiple CTEs
Window functions
Must- OVER · PARTITION BY
- ROW_NUMBER
- RANK · DENSE_RANK
- NTILE
- LAG · LEAD
- Running totals
- Moving averages
- Frame clauses
Tables, DML & design
Should- Data types
- CREATE TABLE & constraints
- INSERT · UPDATE · DELETE
- Transactions
- Normalization 1NF–3NF
- ERDs
- Views
Advanced grouping
Should- ROLLUP
- CUBE
- GROUPING SETS
- Recursive CTEs
Data-engineering depth
Optional- Indexes & EXPLAIN ANALYZE
- JSONB & arrays
- Full-text search
- PL/pgSQL functions
- Triggers
- Roles & backups
Milestone project
Four capstones: e-commerce cohort and lifetime-value analysis, HR workforce metrics, a P&L with budget vs actuals, and a marketing funnel with channel attribution.
You’re ready to move on when
- You can answer a business question that needs three joins and a GROUP BY
- You can calculate month-over-month growth with LAG
- You can explain the difference between WHERE and HAVING, and between RANK and DENSE_RANK
- You can connect Power BI to your PostgreSQL database
Leave for later
- Database administration and replication
- Writing triggers and stored procedures as a junior analyst
- Memorising syntax differences between every database engine
Python بتكمّل من حيث Excel و Power BI بيقفوا: داتا ضخمة، تقارير متكررة عايز تأتمتها، اختبارات إحصائية، و APIs. وهي بوابتك للـ Machine Learning بعدين.
Python foundations
Must- Anaconda · Jupyter · VS Code
- Variables & data types
- Strings & f-strings
- datetime
- Reading files · pathlib
- try / except
Data structures & control flow
Must- Lists
- Dictionaries
- Tuples & sets
- if / elif / else
- for · while
- Comprehensions
- Functions & lambda
Pandas
Must- Series & DataFrame
- read_csv · read_excel · read_sql
- loc · iloc · query
- groupby · agg
- merge · concat
- pivot_table
- resample · rolling
- to_excel
Data cleaning
Must- Profiling quality
- Missing values
- Duplicates
- to_numeric · to_datetime
- str methods & regex
- Outliers: IQR · z-score
- melt · explode
- Reusable pipelines
Visualization
Must- Chart selection
- Matplotlib
- Seaborn
- Plotly Express
- Time-series charts
Statistics for analysts
Must- Descriptive stats
- Distributions
- Confidence intervals
- t-test · chi-square
- Correlation
- Linear regression
- A/B testing
- Forecasting basics
NumPy
Should- Arrays & vectorization
- Boolean masks
- Statistical functions
- Random & simulation
Databases & APIs
Should- sqlite3
- SQLAlchemy
- pd.read_sql
- REST APIs with requests
- read_html
Automation & reporting
Optional- openpyxl · xlsxwriter
- PDF reports
- Email automation
- Scheduling
- Simple ETL
- Plotly Dash apps
Milestone project
End-to-end capstone: define five business questions, clean a real dataset, run EDA and statistical tests, build a dashboard, generate an automated Excel + PDF report, and publish it on GitHub with a README.
You’re ready to move on when
- You can load, clean, merge and summarise a messy CSV in Pandas
- You can run and explain an A/B test result to a non-technical manager
- You can pull a SQL query into a DataFrame
- Your capstone is public on GitHub
Leave for later
- Object-oriented programming and class design
- Web development frameworks
- Deep learning before you are comfortable with Pandas
المرحلة دي لما تحب تكبر من محلل لـ Senior Power BI Developer أو BI Consultant: تفهم محرك DAX من جوه، موديلات الشركات الكبيرة، الأداء، و AI Agents مع Power BI.
Evaluation context mastery
Must- Context transition
- Expanded tables
- ALLSELECTED in depth
- KEEPFILTERS
- REMOVEFILTERS vs ALL
- CROSSFILTER
- Arbitrary shape filters
Advanced DAX patterns
Must- Fiscal calendars
- Semi-additive balances
- Moving averages
- Weighted averages
- TOPN dynamic
- ABC & Pareto
- Calculation groups
Table functions & variables
Must- VAR · RETURN
- SUMMARIZECOLUMNS
- ADDCOLUMNS · SELECTCOLUMNS
- TREATAS
- RANKX
- Nested iterators
- RFM analysis
Advanced row-level security
Must- Static vs dynamic RLS
- Security tables
- USERPRINCIPALNAME()
- Multi-level security
- Testing RLS
Advanced data modeling
Must- Many-to-many & bridges
- Role-playing dimensions
- SCD Type 2
- Parent-child hierarchies
- Multiple fact granularities
- Composite models
- Aggregation tables
AI agents for Power BI with MCP
Should- What MCP is
- Microsoft Power BI MCP servers
- End-to-end dashboard with Claude
- Governance & data leakage
Performance tuning
Should- DAX Studio
- Storage vs formula engine
- Query plans
- Server timings
- Optimising iterators
Milestone project
Enterprise model with aggregations and dynamic RLS, a fiscal-calendar financial report with calculation groups, an RFM customer segmentation, and a slow dashboard you diagnose and fix in DAX Studio.
You’re ready to move on when
- You can explain context transition with an example
- You can secure one model for many managers with dynamic RLS
- You can find why a visual is slow and fix it
- You can use an AI agent to build a model and still review every measure it writes
Leave for later
- This entire stage, until you have built and published several real reports
- Calculation groups before you understand basic time intelligence
الأدوات بتدخّلك الشورت ليست، لكن طريقة شغلك هي اللي بتخليك تتعيّن وتترقّى: التوثيق، التواصل مع الـ Stakeholders، البورتفوليو، استخدام الـ AI صح، والتحضير للإنترفيو.
Stakeholder communication
Must- Requirements gathering
- Clarifying ad-hoc requests
- Executive summary emails
- Presenting in 2 minutes
- Handling pushback
- Managing deadlines
Documentation
Must- Data dictionary
- Metric definitions sheet
- Assumptions log
- Project README
- Dashboard user guide
Portfolio & personal brand
Must- 3–5 end-to-end projects
- GitHub repositories
- Power BI published reports
- LinkedIn project posts
- Case-study write-ups
Interview preparation
Must- Live SQL tests
- Excel & Power BI tasks
- Take-home case studies
- Product & metric questions
- STAR stories
- CV with quantified impact
Version control
Should- Git basics
- Commit · push · pull
- GitHub README
- Versioning .sql & .py files
AI-assisted analytics
Should- Prompting for SQL · DAX · Pandas
- Verifying AI output
- Copilot in Excel & Power BI
- Never paste private data
Domain knowledge
Should- Pick one industry
- Learn its KPIs & data sources
- Read its annual reports
- Speak its vocabulary
Modern data stack awareness
Optional- Warehouse vs lake vs lakehouse
- BigQuery · Snowflake · Fabric
- ETL vs ELT
- dbt concepts
Other BI tools
Optional- Google Sheets
- Looker Studio
- Tableau basics
Milestone project
Turn your three best projects into a public portfolio: each with the business question, data source, method, dashboard screenshot, three insights and a recommendation. Publish them on GitHub, link them from LinkedIn and your CV.
You’re ready to move on when
- A stranger can understand each project from its README in two minutes
- You can solve a medium SQL question live while explaining your thinking
- Your CV states impact in numbers, not just tool names
Leave for later
- Collecting certificates without projects
- Learning a second BI tool before mastering one
- Building projects on toy datasets with no business question
A realistic timeline
at 6 hours a week.
Use these estimates to plan your learning. Make room for practice alongside the lessons, and check each course page for the current curriculum and duration.
| Stage | Course | Video | Weeks | Outcome |
|---|---|---|---|---|
| 00 | Analyst Foundations | ≈ 6 h | 2 | KPIs, statistics, insight & storytelling frameworks |
| 01 | Excel Fundamentals | ≈ 9 h | 3 | Formulas, lookups, clean charts |
| 02 | Excel for Business Analyst | ≈ 10 h | 3 | Pivot & Power Query dashboards |
| 03 | Power BI: Beginner to Intermediate | 40–45 h | 8 | Published Power BI reports · PL-300 ready |
| 04 | Associate Data Analyst in SQL | 52 h | 10 | Query any database independently |
| 05 | Python for Data Engineering & Data Science | 62 h | 12 | Automated analysis & stats · GitHub capstone |
| 06 | Power BI: Advanced | 35–40 h | 7 | Senior BI developer skills |
| 07 | Professional Practice | Practice | Parallel | Portfolio, documentation, interview readiness |
| Total | ≈216 h | ≈45 | Job-ready after stage 05 + 07; stage 06 for the BI developer path |
Swipe the table to see every column →
Data Science Hub · Practical data education in Arabic. Need help choosing your next step?