Data Analytics

You can manipulate messy spreadsheet tables, build dynamic pivot models, and construct executive KPI dashboards using advanced lookup formulas.

1. Excel fundamentals: formulas, functions and formatting90 min
   • SUMIFS
   • COUNTIFS
   • named ranges
   • absolute cell references
2. Pivot tables, pivot charts and slicers90 min
   • calculated fields
   • report filter pages
   • timeline slicers
   • pivot cache
3. VLOOKUP, HLOOKUP, INDEX-MATCH and XLOOKUP2 hrs
   • exact match lookups
   • approximate matching
   • two-way lookups with INDEX-MATCH
   • handling #N/A errors with IFERROR
4. Conditional formatting and data validation90 min
   • custom formula rules
   • drop-down lists
   • input message validation
   • error alerts
5. Dashboard design in Excel: KPI cards, charts and storytelling2 hrs
   • form controls
   • dynamic chart ranges
   • sparklines
   • executive grid layout

Hands-on project: Build an executive financial model in Excel using XLOOKUP, dynamic pivot tables, and conditional formatting to highlight quarterly budget variances.

You can interpret descriptive metrics, compute confidence intervals, and execute hypothesis tests to validate business experiments.

1. Descriptive statistics: mean, median, standard deviation90 min
   • variance
   • percentiles
   • skewness
   • interquartile range
2. Distributions: normal, binomial and Poisson90 min
   • Z-scores
   • central limit theorem
   • probability density functions
   • probability mass functions
3. Sampling, confidence intervals and margin of error2 hrs
   • standard error
   • 95% confidence bounds
   • sample size calculation
   • sampling bias
4. Hypothesis testing: t-tests, chi-square and ANOVA2 hrs
   • null hypothesis
   • p-values
   • alpha thresholds
   • degrees of freedom
5. Correlation, regression and predictive interpretation90 min
   • Pearson r
   • Spearman rank
   • ordinary least squares
   • R-squared
6. A/B testing fundamentals for product and marketing analytics2 hrs
   • conversion rate metrics
   • statistical power
   • sample ratio mismatch
   • guardrail metrics

Hands-on project: Analyze a simulated A/B testing dataset using Python and scipy.stats to calculate p-values, check statistical significance, and recommend deployment.

You can write complex PostgreSQL queries featuring multi-table joins, CTEs, and window functions to extract business insights.

1. SQL fundamentals: SELECT, WHERE, GROUP BY, ORDER BY90 min
   • SELECT DISTINCT
   • HAVING clause
   • LIKE operator
   • COALESCE function
2. JOINs: INNER, LEFT, RIGHT, FULL and CROSS2 hrs
   • ON vs WHERE clauses
   • handling fanout duplicates
   • self joins
   • anti joins
3. Subqueries, CTEs and the WITH clause2 hrs
   • scalar subqueries
   • correlated subqueries
   • recursive CTEs
   • query readability
4. Window functions: ROW_NUMBER, RANK, LAG, LEAD, running totals2 hrs
   • OVER clause
   • PARTITION BY
   • ROWS BETWEEN framing
   • LEAD/LAG offsets
5. Aggregate functions and time-series queries90 min
   • DATE_TRUNC
   • EXTRACT
   • interval arithmetic
   • conditional aggregation with FILTER
6. Real-world datasets: e-commerce orders, HR, financial transactions2 hrs
   • schema inspection
   • primary/foreign key constraints
   • index utilization
   • query execution plans

Hands-on project: Write an optimized PostgreSQL script using CTEs and window functions to calculate rolling 12-month customer lifetime value and churn rates.

You can ingest, clean, and analyze structured datasets programmatically using Python, Pandas, and NumPy.

1. Python basics: variables, control flow, functions and list comprehensions90 min
   • dictionaries
   • lambda functions
   • error handling with try-except
   • modules
2. NumPy for numerical computing90 min
   • ndarrays
   • vectorized operations
   • broadcasting
   • masked arrays
3. Pandas: DataFrames, Series, merge, groupby, reshape and datetime handling2 hrs
   • pd.read_csv
   • DataFrame.merge
   • groupby aggregation
   • pd.to_datetime
4. Data cleaning: handling nulls, duplicates and type mismatches90 min
   • fillna
   • dropna
   • drop_duplicates
   • astype conversion
5. Exploratory data analysis (EDA) with Matplotlib and Seaborn2 hrs
   • plt.subplots
   • sns.histplot
   • sns.boxplot
   • correlation heatmaps
6. Real-world EDA projects: sales, customer behaviour and marketing data2 hrs
   • outlier detection with IQR
   • missing value imputation
   • feature grouping
   • exporting processed parquet

Hands-on project: Build an automated Python script using Pandas to ingest multi-source vendor CSV extracts, validate referential integrity, and output cleaned staging records.

You can design clear, narrative-driven visual reports that communicate complex quantitative insights to non-technical stakeholders.

1. Principles of effective data visualization90 min
   • pre-attentive attributes
   • cognitive load reduction
   • chart junk elimination
   • visual hierarchy
2. Choosing the right chart for the right insight90 min
   • comparison charts
   • composition charts
   • distribution plots
   • relationship analysis
3. Storytelling with data: structure, annotation and narrative2 hrs
   • context setting
   • hero metric placement
   • callout annotations
   • conclusion framing
4. Advanced charts: waterfall, Sankey, scatter matrix, heatmap2 hrs
   • cumulative variance
   • flow mapping
   • multivariate correlation
   • matrix density
5. Building presentation-ready reports in Python and Excel2 hrs
   • matplotlib custom styling
   • pdf export settings
   • executive deck layout
   • typography standards

Hands-on project: Design a 5-slide visual report in Python summarizing executive sales metrics using gestalt principles and targeted color annotations.

You can connect enterprise data sources, model star schemas, write DAX measures, and publish interactive dashboards to Power BI Service.

1. Power BI Desktop: connecting to Excel, SQL and cloud sources90 min
   • DirectQuery
   • Import mode
   • gateway configuration
   • data source credentials
2. Power Query: data transformation and M language basics2 hrs
   • unpivoting columns
   • custom column creation
   • merging queries
   • applied steps debugging
3. Data modelling: relationships, star schema and calculated columns2 hrs
   • dimension tables
   • fact tables
   • one-to-many filtering
   • active vs inactive relationships
4. DAX fundamentals: measures, CALCULATE, FILTER, time intelligence2 hrs
   • SUMX
   • TOTALYTD
   • SAMEPERIODLASTYEAR
   • context transition
5. Interactive report and dashboard design90 min
   • cross-filtering behavior
   • bookmark actions
   • drill-through targets
   • responsive mobile layout
6. Publishing to Power BI Service and sharing with stakeholders90 min
   • workspace publishing
   • scheduled data refresh
   • row-level security (RLS)
   • app sharing links

Hands-on project: Build a supply chain operations dashboard in Power BI Desktop featuring star-schema relationships and custom DAX measures for YTD fulfilment rates.

You can construct interactive workbooks in Tableau using calculated fields, level-of-detail expressions, and published story points.

1. Tableau Desktop: connecting to data sources90 min
   • extract generation
   • data interpreter
   • logical vs physical tables
   • cross-database joins
2. Calculated fields, parameters and table calculations2 hrs
   • IF-THEN logic
   • string manipulation
   • parameter controls
   • PERCENT OF TOTAL
3. Chart types: bar, line, scatter, map, treemap and heat map2 hrs
   • dual-axis charts
   • geographic role assignment
   • filled maps
   • density marks
4. Dashboard design and interactivity: actions, filters and tooltips2 hrs
   • filter actions
   • highlight actions
   • viz in tooltip
   • device designer
5. Tableau story points for narrative presentations90 min
   • story point sequencing
   • caption management
   • narrative flow
   • export to PowerPoint
6. Publishing to Tableau Public and Tableau Server90 min
   • workbook packaging
   • extract refresh schedules
   • permission management
   • public embedding

Hands-on project: Publish an executive SaaS revenue storyboard to Tableau Public featuring dynamic parameter controls and LOD churn calculations.

You can solve complex domain-specific analytics challenges across sales, marketing, finance, and operations.

1. Sales and revenue analysis project2 hrs
   • pipeline conversion
   • sales velocity
   • quota attainment
   • discount impact
2. Customer segmentation and cohort analysis2 hrs
   • RFM scoring
   • K-means clustering in Python
   • retention matrix
   • behavioral segmentation
3. Marketing attribution and campaign analytics2 hrs
   • first-touch attribution
   • last-touch attribution
   • CPA calculation
   • ROAS evaluation
4. Financial reporting and budget variance analysis2 hrs
   • actual vs budget
   • EBITDA calculation
   • burn rate modelling
   • opex breakdown
5. Operations and supply chain analytics2 hrs
   • lead time variance
   • inventory turnover
   • safety stock calculation
   • order fulfilment rate
6. Presenting findings to a non-technical business audience90 min
   • executive summary structuring
   • handling executive objections
   • recommendation framing
   • follow-up tracking

Hands-on project: Deliver a multi-departmental business performance review deck incorporating marketing attribution models and variance analysis.

You can take a messy real-world extract and produce a documented, analysis-ready dataset.

1. Profiling a dataset before you trust it90 min
   • pandas .info() and .describe()
   • Null and cardinality audit
   • Range and outlier checks
2. Deduplication and record linkage90 min
   • drop_duplicates(subset, keep)
   • Fuzzy name matching
   • Choosing a surviving record
3. Missing data strategies and their bias90 min
   • Listwise deletion
   • Mean vs forward fill
   • Documenting the choice
4. Outliers, types and reproducible cleaning90 min
   • IQR and z-score rules
   • astype and to_datetime
   • Idempotent cleaning scripts

Hands-on project: Clean a supplied 500k-row sales extract: resolve duplicate customer records, reconcile three date formats, document every missing-value decision in a data dictionary, and prove row counts before and after.

You can design a star schema and build a BI model that answers questions the source tables cannot.

1. Star schema, facts and dimensions90 min
   • Grain declaration
   • Conformed dimensions
   • Fact table types
2. Slowly changing dimensions90 min
   • Type 1 vs Type 2
   • Surrogate keys
   • Effective date ranges
3. Relationships and filter direction90 min
   • One-to-many cardinality
   • Bi-directional pitfalls
   • Role-playing date tables
4. DAX measures that survive slicing90 min
   • CALCULATE and FILTER
   • Time intelligence functions
   • Measure vs calculated column

Hands-on project: Model the cleaned sales extract as a star schema with one fact and four conformed dimensions, implement it in Power BI with a date table, and add three DAX measures including a year-over-year comparison.

You can design an A/B test, compute the sample size it needs, and report a result with its uncertainty.

1. Hypothesis, power and sample size90 min
   • Null and alternative
   • Minimum detectable effect
   • Power analysis
2. Running a valid A/B test90 min
   • Randomisation unit
   • Guardrail metrics
   • Stopping rules and peeking
3. Significance, intervals and effect size90 min
   • p-value interpretation
   • Confidence intervals
   • Practical vs statistical
4. Common traps that invalidate results90 min
   • Simpson's paradox
   • Multiple comparisons
   • Survivorship bias

Hands-on project: Given a conversion-rate hypothesis, calculate the required sample size for 80% power, analyse a supplied result set with a two-proportion test, and write a one-page recommendation stating the confidence interval and what would change your mind.

You can put an analysis under version control and schedule it so it refreshes without you.

1. Git for analysts90 min
   • Branch and pull request flow
   • Reviewing a .sql diff
   • Why notebooks break merges
2. Transformations as code with dbt90 min
   • models and ref()
   • schema tests
   • Incremental models
3. Scheduled refresh and gateways90 min
   • Power BI dataset refresh
   • Credential and gateway setup
   • Failure notifications
4. Data quality monitoring90 min
   • Row-count and freshness checks
   • Alert thresholds
   • Owning a broken dashboard

Hands-on project: Move the cleaning and modelling steps into a Git-tracked dbt project with tests on primary keys and accepted values, schedule a daily Power BI refresh, and add an alert that fires when row counts drop more than 10%.

You can execute an end-to-end analytics workflow from raw data ingestion to dashboard publication and present your portfolio to hiring managers.

1. End-to-end business analytics capstone: collect, clean, analyse, visualize and present3 hrs
   • problem scoping
   • ETL pipeline construction
   • dashboard validation
   • final presentation deck
2. LinkedIn, resume and portfolio building for data analyst roles2 hrs
   • bullet point formulation
   • GitHub README design
   • Tableau Public profile optimization
   • keyword alignment
3. Mock technical interviews: SQL, Python and case study rounds2 hrs
   • LeetCode Easy/Medium SQL
   • Pandas live coding
   • business metric estimation
   • whiteboard case breakdown
4. HR round preparation and salary negotiation guidance90 min
   • behavioral questions
   • compensation benchmarking
   • offer evaluation
   • notice period strategy

Hands-on project: Build and deploy a complete capstone analytics portfolio including a GitHub repository, PostgreSQL schema, and interactive Power BI executive dashboard.