Everything you need to go from raw tables to decision-ready dashboards. This roadmap brings together the three skills that define a working data analyst: SQL for asking questions of data, BigQuery for doing it at scale in the cloud, and Power BI for turning the answers into reports people actually use.
I recommend you bookmark this page, as I will be adding new articles every day.
Andrei
SQL
- Introduction to SQL: https://datalad.co.uk/sql-basics-asking-a-database-for-what-you-want/
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
- Intermediate SQL: https://datalad.co.uk/sql-querying-and-aggregation-filtering-grouping-and-summarising-data/
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
- Joining Data in SQL: https://datalad.co.uk/sql-joins-set-operations-and-subqueries-combining-data-across-tables/
- Data Manipulation in SQL
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
- Window Functions
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
- User-Defined Functions
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
- Exploratory Data Analysis: https://datalad.co.uk/exploratory-data-analysis/
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
- Relational Databases in SQL
- Database Design
- Time Series in SQL Server
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
- Functions for Manipulating Data in SQL Server
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
- Transactions and Error Handling
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
- Writing Functions and Stored Procedures
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
- Building and Optimising Triggers
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
BigQuery SQL
- A Practical Guide to Standard SQL in BigQuery: https://datalad.co.uk/a-practical-guide-to-standard-sql-in-bigquery/
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
- Aggregates and Joins in BigQuery: https://datalad.co.uk/aggregates-and-joins-in-bigquery-count-sum-avg-and-joins/
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
- Writing Cleaner BigQuery SQL with Common Table Expressions: https://datalad.co.uk/writing-cleaner-bigquery-sql-with-common-table-expressions/
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
- BigQuery Subqueries and Nested Queries: https://datalad.co.uk/bigquery-subqueries-and-nested-queries/
- Code along: https://datalad.co.uk/bigquery-subqueries-and-nested-queries-10-code-along-examples/
- Cheatsheet: https://datalad.co.uk/bigquery-subqueries-and-nested-queries-cheatsheet/
- Feynman Technique:
- Video:
- BigQuery Scalar Subqueries: https://datalad.co.uk/bigquery-scalar-subqueries/
- Code along: https://datalad.co.uk/bigquery-scalar-subqueries-10-code-along-examples/
- Cheatsheet:
- Feynman: https://datalad.co.uk/scalar-subqueries-in-bigquery-explained-simply/
- Video:
- BigQuery Correlated Subqueries: https://datalad.co.uk/bigquery-correlated-subqueries/
- Code along: https://datalad.co.uk/bigquery-correlated-subqueries-10-code-along-examples/
- Cheatsheet:
- Feynman Technique:
- Video:
- EXISTS and NOT EXISTS in BigQuery: https://datalad.co.uk/exists-and-not-exists-in-bigquery-testing-for-related-records/
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
- Semi-Joins and Anti-Joins in BigQuery: https://datalad.co.uk/semi-joins-and-anti-joins-in-bigquery/
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
- Understanding Window Functions in BigQuery: https://datalad.co.uk/understanding-window-functions-in-bigquery/
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
- Running Totals and Moving Averages in BigQuery: https://datalad.co.uk/running-totals-and-moving-averages-in-bigquery-sum-over-and-avg-over/
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
- Flattening Nested Data in BigQuery with UNNEST: https://datalad.co.uk/flattening-nested-data-in-bigquery-with-unnest/
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
- User-Defined Functions in BigQuery: https://datalad.co.uk/user-defined-functions-in-bigquery/
- Code along: https://datalad.co.uk/user-defined-functions-in-bigquery-10-code-along-examples/
- Cheatsheet: https://datalad.co.uk/bigquery-udfs-cheatsheet/
- Feynman: https://datalad.co.uk/user-defined-functions-in-bigquery-feynman-technique/
- Video: none yet
- Speeding Up BigQuery with Materialized Views: https://datalad.co.uk/speeding-up-bigquery-with-materialized-views/
- Code along:
- Cheatsheet:
- Feynman Technique:
- Video:
GA4 reporting in BigQuery
- Build a Session-Based GA4 Traffic Acquisition Report: https://datalad.co.uk/build-a-session-based-ga4-traffic-acquisition-report-in-bigquery/
- Why the GA4 Landing Page Report Falls Short: https://datalad.co.uk/why-the-ga4-landing-page-report-falls-short-and-how-to-rebuild-it-in-bigquery/
- Build a GA4 Page Performance Report: https://datalad.co.uk/build-a-ga4-page-performance-report-in-bigquery/
- Build a GA4 Tech (Device, OS, Browser) Performance Report: https://datalad.co.uk/build-a-ga4-tech-device-os-and-browser-performance-report-in-bigquery/
- Build a GA4 Item and Product Performance Report: https://datalad.co.uk/build-a-ga4-item-and-product-performance-report-in-bigquery/
- Build a GA4 Ecommerce Transactions Performance Report: https://datalad.co.uk/build-a-ga4-ecommerce-transactions-performance-report-in-bigquery/
- Backstitching Your User ID to GA4’s Client ID: https://datalad.co.uk/backstitching-your-user-id-to-ga4s-client-id-in-bigquery-for-cross-device-analysis/
- Flattening the GA4 BigQuery Export Schema for Relational Databases
- Customising the GA4 Engaged Session Definition in BigQuery
- Building a GA4 User Retention Cohort Table in BigQuery and Power BI
- Joining Your CRM and GA4 Event Data in BigQuery
- Joining Search Query and SEO Data with GA4 in BigQuery
- Building a GA4 Attribution Model Comparison Tool in BigQuery and Looker Studio
- Building a GA4 Funnel Exploration Table in BigQuery
- Creating Default and Custom Channel Groupings in BigQuery
Power BI
- Introduction to Power BI
- Introduction to DAX in Power BI
- Data Visualization in Power BI
- Data Preparation in Power BI
- Data Transformation in Power BI
- Data Modelling in Power BI
- Intermediate Data Modelling in Power BI
- DAX Functions in Power BI
- Intermediate DAX in Power BI
- Exploratory Data Analysis in Power BI
- Trend Analysis in Power BI
- Reports in Power BI
- Report Design in Power BI
- Data Connections in Power BI
- Deploying and Maintaining Assets in Power BI
See you soon,
Andrei.
