Data Analytics

The Data Framework
• Explain the value of data.
• Describe the data framework and how it’s used by analysts.
• Write a specific and testable question given a scenario.

Finding the Right Data
• Describe the data sources available for analysis.
• Evaluate data sets and their variables.
• Determine if a data set can be used to solve a business problem.

Cleaning Your Data
• Use the Filter feature to spot check for problematic data.
• Handle missing data based on industry norms.
• Use conditional formatting to identify duplicates and extreme values within a data set.
• Use Find and Replace to fix issues/errors that are easily identifiable.
• Select a data cleaning strategy based on a given scenario.

Organizing Data With Functions
• Use VLOOKUP and HLOOKUP.
• Use INDEX MATCH.
• Distinguish between the three functions.

Introduction to Data Analytics
• Outline goals, expectations, and logistics.
• Identify the skills and mindset of a successful data analyst.
• Discuss the discipline of data analytics, including topics such as data formats and data ethics.

Data Cleaning and Formulas
• Apply data cleaning best practices, including working with NULLs.
• Conduct exploratory analyses.
• Experiment with common Excel formulas.

Referencing and Lookups
• Build relationships between cells in Excel.
• Manipulate data sets using VLOOKUP.
• Look up values in other tables using INDEX and MATCH.

Aggregating Data With PivotTables
• Apply Excel aggregation functions to data sets.
• Use PivotTables to summarize data.
• Identify common problems and solutions for PivotTables
• Modify data structures with regular tables for efficient workbooks.

Communicating With Excel
• Identify the appropriate visualization types for the data set at hand.
• Create analytics visuals such as bar charts, pie charts, line graphs, histograms, and scatterplots.
• Explore data using conditional formatting for categorization and analysis.

Project: Apply what you’ve learned in Excel and present your process, findings, and challenges to the class, giving and receiving peer-to-peer feedback.

Introduction to SQL
• Navigate a relational database.
• Practice writing and executing SQL queries, including SELECT, FROM, WHERE, and DISTINCT SELECT.
• Work with logical and comparison operators in SQL.

Grouping in SQL
• Work with CASE to handle multiple conditions.
• Practice writing aggregate functions: MIN, MAX, SUM, AVG, and COUNT.
• Use advanced SQL commands such as GROUP BY and HAVING to group and filter data.

Combining Data With JOINs and UNIONs
• Combine data from multiple sources using INNER and LEFT JOINs.
• Compile data using UNION and UNION ALL.
• Compare use cases for JOINs and UNIONs.

Advanced JOINs and NULLs
• Practice the concepts and syntax of advanced JOINs such as EXCEPT, FULL, and OUTER.
• Handle NULLs in SQL
• Practice query optimization techniques.

Subqueries in SQL
• Construct subqueries for multi-step operations.
• Identify subquery use cases for various business scenarios.
• Practice common table expressions (CTEs) with SELECT statements.

Functions in SQL
• Apply string, math, and date functions in SQL to prepare and analyze data.
• Practice writing SQL queries with advanced functions to solve business problems.

Project: Apply what you’ve learned in SQL and present your process, findings, and challenges to the class, giving and receiving peer-to-peer feedback.

Introduction to SQL
• Navigate a relational database.
• Practice writing and executing SQL queries, including SELECT, FROM, WHERE, and DISTINCT SELECT.
• Work with logical and comparison operators in SQL.

Grouping in SQL
• Work with CASE to handle multiple conditions.
• Practice writing aggregate functions: MIN, MAX, SUM, AVG, and COUNT.
• Use advanced SQL commands such as GROUP BY and HAVING to group and filter data.

Combining Data With JOINs and UNIONs
• Combine data from multiple sources using INNER and LEFT JOINs.
• Compile data using UNION and UNION ALL.
• Compare use cases for JOINs and UNIONs.

Advanced JOINs and NULLs
• Practice the concepts and syntax of advanced JOINs such as EXCEPT, FULL, and OUTER.
• Handle NULLs in SQL
• Practice query optimization techniques.

Subqueries in SQL
• Construct subqueries for multi-step operations.
• Identify subquery use cases for various business scenarios.
• Practice common table expressions (CTEs) with SELECT statements.

Functions in SQL
• Apply string, math, and date functions in SQL to prepare and analyze data.
• Practice writing SQL queries with advanced functions to solve business problems.

Project: Apply what you’ve learned in SQL and present your process, findings, and challenges to the class, giving and receiving peer-to-peer feedback.