1. Organize data to begin analysis
1.1. 4 phases of analysis
1.1.1. organize data
1.1.2. format and adjust data
1.1.3. get input from others
1.1.4. transform data
1.2. sorting
1.2.1. when you arrange data into a meaningful order to make it easier to understand, analyze and visualize
1.2.1.1. spreadsheets
1.2.1.1.1. sort sheet
1.3. filtering
2. Format and adjust data
2.1. incorrectly formatted data can
2.1.1. lead to mistakes
2.1.2. take time to fix
2.2. import data
2.2.1. spreadsheets
2.2.1.1. IMPORTRANGE(spreadsheet_url, range_string)
2.2.2. SQL
2.2.2.1. INSERT INTO
2.3. combine data
2.3.1. spreadsheets
2.3.1.1. CONCATENATE(item 1, item 2)
2.3.1.1.1. a function that joins together two or more text strings
2.3.2. SQL
2.3.2.1. CONCAT
2.3.2.1.1. Concatenate strings to create new text strings
2.3.2.2. CONCAT_WS
2.3.2.2.1. Concatenate two or more strings together with a separator between each string
2.3.2.3. ||
2.3.2.3.1. Concatenate two or more strings together with the || operator
2.4. best practices for searching online
2.4.1. thinking skills
2.4.2. data analytics terms
2.4.2.1. use the right words
2.4.3. basic knowledge of tools
3. Aggregate data for analysis
3.1. data aggregation
3.1.1. the process of gathering data from multiple sources in order to combine it into a single summarized collection
3.1.1.1. identify trends
3.1.1.2. make comparisons
3.2. VLOOKUP functionality
3.2.1. use cases
3.2.1.1. Populating data in a spreadsheet
3.2.1.2. Merging data from one spreadsheet with data in another
3.2.2. syntax
3.2.3. absolute references
3.3. JOINS
3.3.1. INNER JOIN: a function that returns records with matching values in both tables
3.3.2. LEFT JOIN: a function that returns all the records from the left table (first mentioned) and only the matching records from the right table (second mentioned)
3.3.3. RIGHT JOIN: a function that returns all records from the right table (second mentioned) and only the matching records from the left table (first mentioned).
3.3.4. OUTER JOIN: a function that combines the RIGHT JOIN and LEFT JOIN to return all matching records in both tables.
3.4. Subqueries
3.4.1. Outer query/select
3.4.2. Inner query
4. Perform data calculations
4.1. Data calculations
4.1.1. Functions
4.1.1.1. COUNTIF(range,'value')
4.1.1.1.1. COUNTIFS
4.1.1.2. SUMIF(range,criteria/condition,[sum_range])
4.1.1.2.1. SUMIFS
4.1.1.3. SUMPRODUCT(array1, [array2]...)
4.1.2. Summary table
4.1.2.1. a table used to summarize statistical information about data
4.2. Pivot tables
4.2.1. calculated field
4.2.1.1. a new field within a pivot table that carries out certain calculations based on the values of other fields
4.3. SQL calculations
4.4. Data validation process
4.4.1. checking and rechecking the quality of your data so that it is complete, accurate, secure and consistent
4.4.2. types of data validation
4.4.2.1. 1) data type
4.4.2.1.1. purpose: check that the data matches the data type defined for a field
4.4.2.1.2. example: data values for school grades 1-12 must be a numeric data type
4.4.2.1.3. limitations: the data value 13 would pass the data type validation but would be unacceptable value
4.4.2.2. 2) data range
4.4.2.3. 3) data constraints
4.4.2.4. 4) data consistency
4.4.2.5. 5) data structure
4.4.2.6. 6) code validation
4.4.2.6.1. Purpose: Check that the application code systematically performs any of the previously mentioned validations during user data input.
4.5. SQL and temporary tables
4.5.1. temporary table
4.5.1.1. a database table that is created and exists temporarily on a database server
4.5.1.2. WITH clause
4.5.1.2.1. a type of temporary table that you can query from multiple times
4.5.1.3. SELECT INTO statements
4.5.1.4. CREATE TABLE statements
4.5.1.5. CREATE TEMP TABLE statements
4.5.2. multiple table variations
4.5.3. use connected sheets with BigQuery