Data Analytics

Course details

Skills & Tools

Use SQL, Excel, and Tableau to extract, analyze, and illustrate real‐world data.

Production Standard

Create data visualizations and dashboards to present and communicate important findings.

The Big Picture

Use descriptive statistical analysis to make informed, effective decisions.

Curriculum

Unit 1: Data in Excel

The Value of Data

• Explain the value of data
• Describe the analytics workflow
• Use mean, median, mode to describe data and find outliers

Prepare Data in Excel

• Describe best practices in data cleaning and collection to ensure the best results from data analysis
• Use complex nested logical functions [IF, OR, and AND] to further manipulate data sets
• Manipulate data formats to gain insights on how to analyze data

Clean Data in Excel

• Clean a large messy datasets by removing duplicate rows and performing text manipulations
• Transform and rearrange columns and rows to structure data for analysis
• Manipulate data formats to gain insights on how to analyze data

Dynamic Data Referencing

• Use data functions [VLOOKUP and HLOOKUP] to manipulate data sets
• Use data functions [INDEX and MATCH] to look up values in other tables
• Reconcile data values by joining and matching

Dynamic Data Aggregation

• Summarize data using the pivot tables
• Use excel aggregation commands ['Min', 'Max', 'Sum', 'Average', 'Count', 'Frequency' to accomplish "count distinct" ] and their conditional variants ['COUNTIF', 'COUNTUNIQUE', 'COUNTA', 'COUNTIFS', 'COUNTBLANKS'] to summarize data sets

Conditionally Formatting and Aggregations

• Derive insights from data by highlighting cells based on conditionals using the following excel functions ['Greater Than', 'Less Than', 'Between', 'Equal To', 'Text That Contains']
• Create clear charts that improve clarity, avoid unnecessary embellishment, provide quick take aways and facilitate secondary understanding from closer look at chart.
• Select appropriate chart combinations in the context of problem
• Describe color theory and how it applies to data visualization

Unit 2: Data in SQL

The Value of Databases and SQL

• Use database schema to design appropriate queries
• Explain differences between relational databases (tabular data storage) and document-based databases(key-value pairs)
• Collect data using standard sql commands [Create, Update, Delete, Truncate, Drop]
• Collect data using standard sql commands [Select, From]

Query Large Databases

• Use advanced SQL commands [where, groupby, having, orderby, limit] to filter data
• Use joins to create relationships between tables to obtain data
• Use SQL boolean operators [AND and OR] and SQL conditional operators [=,!=,>,<,IN and BETWEEN] to obtain filtered data

Data Aggregation in SQL

• Create relationships between tables and data points including has_many and many_to_many with join tables using Joins ['full', and 'union']
• Use sql conditional operators [=,!=,>,<,IN and BETWEEN] and Null functions['is Null', ' is not Null' and 'NVL' ] to create boolean statements
• Use sql mathematical functions [ABS, SIGN, MOD, FLOOR, CEILING, ROUND, SQRT] to clean dataMore Data Aggregation in SQL
• Use aggregation commands ['Min', 'Max', 'Sum', 'Average', 'Count', 'Count Distinct'] to summarize data sets
• Use aggregation methods to determine trends from data

More Data Aggregation in SQL

• Use aggregation commands ['Min', 'Max', 'Sum', 'Average', 'Count', 'Count Distinct'] to summarize data sets
• Use aggregation methods to determine trends from data

Efficient and Dynamic Queries

• Use CASE statements to structure data and create new attributes
• Use \"\"WITH AS (\"\" to combine subqueries into one query
• Present analysis results and describe stakeholder implications and insights

Present Analysis Results

• Provide appropriate context for datasets
• Appropriately describe analysis techniques
• Present and describe stakeholder implications and insights

Unit 3: Communication and Dashboard Design

Statistics to Validate Analysis

• Describe the value of descriptive and summary statistics in understanding a dataset
• Create basic statistical measures to better understand the range, average, and variance within a dataset
• Present the most salient statistics in order to provide context to your audience
• Explain the importance of segmentation

Statistics to Validate Analysis II

• Describe the value of inferential statistics and predictive analysis
• Review linear regression and Ordinary Least Squares (OLS)
• Use sample data to make predictions about a larger population

Dashboard Design

• Use scatter plots and bar graphs to visualize data
• Apply the best practices to build a dashboard
• Demonstrate good visual design without overloading their dashboard with complexity

Track Metrics with Dashboards

• Use bubble graphs to visualize data
• Apply the best practices to build a dashboard
• Contextualize data analysis by creating Tableau dashboards [includes charts + conditional formatting] with supporting information specific to the dataset

Effective Presentation with Data

• Display geocoded information in Tableau
• Provide real-world context for basis of analysis
• Provide localized context for implications of findings
• Deliver short, effective presentations

Flexible Session

• Focus on a topic selected by the instructor/class in order to provide deeper insight into a specific area of data analysis

Flexible Session

• Focus on a topic selected by the instructor/class in order to provide deeper insight into a specific area of data analysis

Final Presentations

• Gain feedback from peers, instructor, and guest panelists that will identify strengths and areas for improvement