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

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 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

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