Understanding Data Types
Before you begin analyzing the data, it's helpful to review the four most common
data types you'll encounter as an analyst:
Numerical Data: Data that consists of numbers that can be measured,
counted, or used in mathematical calculations.
o Examples: Quantity, price, height, age.
Text Data (or String Data): Data that consists of letters, words, sentences,
or a mix of letters and numbers used as an identifier, not for calculation.
o Examples: Product name, customer address, notes, ID numbers (like
a phone number or social security number, which you don't perform
math on).
Date Data: Data that represents a specific point in time, such as a date or
a timestamp. This data type is crucial for tracking trends and events over
time.
o Examples: Order date, shipping date, birth date.
Categorical Data: Data that can be divided into groups or categories.
This data often represents labels, names, or discrete categories.
o Examples: Product color (red, blue, green), customer loyalty status
(Gold, Silver, Bronze), or a rating that uses words (Excellent, Good,
Poor).
People analytics is the practice of collecting and analyzing data on the
people who make up a company’s workforce in order to gain insights to improve
how the company operates.
[Link] Examples of BA in
real life (HBR)
The six phases of data analysis
The data analysis process helps analysts break down business problems into a
series of manageable tasks:
In the ask phase, you’ll work to understand the challenge to be solved or the
question to be answered. It will likely be assigned to you by stakeholders. As this
is the ask phase, you’ll ask many questions to help you along the way.
Next, in the prepare phase, you’ll find and collect the data you'll need to
answer your questions. You’ll identify data sources, gather data, and verify that it
is accurate and useful for answering your questions.
The process phase is when you will clean and organize your data. Tasks you
perform here include removing any inconsistencies; filling in missing values; and,
in many cases, changing the data to a format that's easier to work with.
Essentially, you’re ensuring the data is ready before you begin analysis.
The analyze phase is when you do the necessary data analysis to uncover
answers and solutions. Depending on the situation and the data, this could
involve tasks such as calculating averages or counting items in categories so you
can examine trends and patterns.
Next comes the share phase, when you present your findings to decision-
makers through a report, presentation, or data visualizations. As part of the
share phase, you decide which medium you want to use to share your findings
and select the data to include. Tools for presenting data visually include charts
made in Google Sheets and Tableau.
Last is the act phase, in which you and others in the company put the data
insights into action. This could mean implementing a new business strategy,
making changes to a website, or any other action that solves the initial problem.
DELL EMC's data analysis process
EMC Corporation's data analytics process is cyclical with six steps:
1. Discovery
2. Pre-processing data
3. Model planning
4. Model building
5. Communicate results
6. Operationalize
SAS's iterative process
An iterative data analysis process was created by a company called SAS, a
leading data analytics solutions provider. It can be used to produce repeatable,
reliable, and predictive results:
1. Ask
2. Prepare
3. Explore
4. Model
5. Implement
6. Act
7. Evaluate
The SAS model emphasizes the cyclical nature of their model by visualizing it as
an infinity symbol. Its process has seven steps, many of which mirror the other
models, like ask, prepare, model, and act. But this process is also a little
different; it includes a step after the act phase designed to help analysts
evaluate their solutions and potentially return to the ask phase again.
Big data analytics process
Authors Thomas Erl, Wajid Khattak, and Paul Buhler proposed a big data analytics
process in their book, Big Data Fundamentals: Concepts, Drivers &
Techniques. Their process suggests phases divided into nine steps:
1. Business case evaluation
2. Data identification
3. Data acquisition and filtering
4. Data extraction
5. Data validation and cleaning
6. Data aggregation and representation
7. Data analysis
8. Data visualization
9. Utilization of analysis results
10. Analytical skills: Qualities and characteristics associated with using facts to
solve problems
11. Analytical thinking: The process of identifying and defining a problem, then
solving it by using data in an organized, step-by-step manner
12. Context: The condition in which something exists or happens
13. Data: A collection of facts
14. Data analysis: The collection, transformation, and organization of data in
order to draw conclusions, make predictions, and drive informed decision-making
15. Data analyst: Someone who collects, transforms, and organizes data in order
to draw conclusions, make predictions, and drive informed decision-making
16. Data analytics: The science of data
17. Data design: How information is organized
18. Data-driven decision-making: Using facts to guide business strategy
19. Data ecosystem: The various elements that interact with one another in
order to produce, manage, store, organize, analyze, and share data
20. Data science: A field of study that uses raw data to create new ways of
modeling and understanding the unknown
21. Data strategy: The management of the people, processes, and tools used in
data analysis
22. Data visualization: The graphical representation of data
23. Dataset: A collection of data that can be manipulated or analyzed as one unit
24. Gap analysis: A method for examining and evaluating the current state of a
process in order to identify opportunities for improvement in the future
25. Root cause: The reason why a problem occurs
26. Technical mindset: The ability to break things down into smaller steps or
pieces and work with them in an orderly and logical way
MODULE 2
Terms and definitions for Course 1, Module 2
Database: A collection of data stored in a computer system
Formula: A set of instructions used to perform a calculation using the data
in a spreadsheet
Function: A preset command that automatically performs a specified
process or task using the data in a spreadsheet
Query: A request for data or information from a database
Query language: A computer programming language used to
communicate with a database
Stakeholders: People who invest time and resources into a project and are
interested in its outcome
Structured Query Language: A computer programming language used to
communicate with a database
Spreadsheet: A digital worksheet
SQL: (Refer to Structured Query Language)
SQL MATTER
Comments
Some tables aren’t designed with descriptive enough naming
conventions. In the example, field1 was the column for a
customer’s last name, but you wouldn’t know it by the name. A
better name would have been something such as last_name. In
these cases, you can place comments alongside your SQL to
help you remember what the name represents. Comments are
text placed between certain characters, /* and */, or after two
dashes --) as shown below.
Comments can also be added outside of a statement as well as
within a statement. You can use this flexibility to provide an
overall description of what you are going to do, step-by-step
notes about how you achieve it, and why you set different
parameters/conditions.
Example of a query with comments
Here is an example of how comments could be written in
BigQuery:
In the above example, a comment has been added before the
SQL statement to explain what the query does. Additionally, a
comment has been added next to each of the column names to
describe the column and its use. Two dashes -- are generally
supported. So it is best to use -- and be consistent with it. You
can use # in place of -- in the above query, but # is not
recognized in all SQL versions; for example, MySQL doesn’t
recognize #. You can also place comments between /* and */ if
the database you are using supports it.
As you develop your skills professionally, depending on the SQL
database you use, you can pick the appropriate comment
delimiting symbols you prefer and stick with those as a
consistent style. As your queries become more and more
complex, the practice of adding helpful comments will save you
a lot of time and energy to understand queries that you may
have written months or years prior.
Aliases
You can also make it easier on yourself by assigning a new
name or alias to the column or table names to make them
easier to work with (and avoid the need for comments). This is
done with a SQL AS clause. In the example below, aliases are
used for both a table name and a column. Within the database,
the table is called actual_table_name and the column in that
table is called actual_column_name. They are aliased as
my_table_alias and my_column_alias, respectively. These
aliases are good for the duration of the query only. An alias
doesn’t change the actual name of a column or table in the
database.
Example of a query with aliases
Putting SQL to work as a data analyst
Imagine you are a data analyst for a small business and your
manager asks you for some employee data. You decide to write
a query with SQL to get what you need from the database.
You want to pull all the columns: empID, firstName,
lastName, jobCode, and salary. Because you know the
database isn’t that big, instead of entering each column name
in the SELECT clause, you use SELECT *. This will select all
the columns from the Employee table in the FROM clause.
Now, you can get more specific about the data you want from
the Employee table. If you want all the data about employees
working in the 'SFI' job code, you can use a WHERE clause to
filter out the data based on this additional requirement.
Here, you use:
A portion of the resulting data returned from the SQL query
might look like this:
empID firstName lastName jobCode salary
0002 Homer Simpson SFI 15000
0003 Marge Simpson SFI 30000
0034 Bart Simpson SFI 25000
0067 Lisa Simpson SFI 38000
empID firstName lastName jobCode salary
0088 Ned Flanders SFI 42000
0076 Barney Gumble SFI 32000
Suppose you notice a large salary range for the 'SFI' job code.
You might like to flag all employees in all departments with
lower salaries for your manager. Because interns are also
included in the table and they have salaries less than $30,000,
you want to make sure your results give you only the full time
employees with salaries that are $30,000 or less. In other
words, you want to exclude interns with the 'INT' job code who
also earn less than $30,000. The AND clause enables you to
test for both conditions. You create a SQL query similar to
below, where <> means "does not equal":
The resulting data from the SQL query might look like the
following (interns with the job code INT aren't returned):
empID firstName lastName jobCode salary
0002 Homer Simpson SFI 15000
0003 Marge Simpson SFI 30000
0034 Bart Simpson SFI 25000
0108 Edna Krabappel TUL 18000
0099 Moe Szyslak ANA 28000
empID firstName lastName jobCode salary
DATA VISUALISATION RESOURCES:
You can start exploring Tableau from the How-to Video
resources. Tableau Public is free, easy to use, and full of helpful
information. The Resources page is a one-stop-shop for how-to
videos, examples, and datasets for you to practice with. To
explore what other data analysts are sharing on Tableau, visit
the Viz of the Day page where you will find beautiful visuals
ranging from an overview of the Lighthouses of Greece to
Who’s Talking in Popular Films.
Check out the Python Graph Gallery to discover a vast
collection of visualizations made with Python.
IMPORT functions in Google Sheets
The IMPORTRANGE function
In Google Sheets, the IMPORTRANGE function can import all or part of a
dataset from another Google Sheet.
To use this function, you need two pieces of information:
1. The URL of the Google Sheet from which you’ll import data.
2. The name of the sheet and the range of cells you want to import
into your Google Sheet.
Once you have this information, open the Google Sheet into which you
want to import data and select the cell into which the first cell of data
should be copied. Enter = to indicate you will enter a function, then
complete the IMPORTRANGE function with the URL and range you
identified in the following manner: =IMPORTRANGE("URL",
"sheet_name!cell_range"). Note that an exclamation point separates
the sheet name and the cell range in the second part of this function.
An example of this function is:
=IMPORTRANGE("[Link]
"sheet1!A1:F13")
Note: This URL is for syntax purposes only. It is not meant to be entered
into your own spreadsheet.
Once you’ve completed the function, a box will pop up to prompt you to
allow access to the Google Sheet from which you’re importing data. You
must allow access to the spreadsheet containing the data the first time
you import it into Google Sheets. Replace it with a spreadsheet’s URL that
you have created so you can control access by selecting the Allow access
button.
Refer to the Google Help Center's IMPORTRANGE page for more
information about the syntax. You’ll also learn more about this later in the
program.
The IMPORTHTML function
Importing HTML tables is a basic method to extract data from public web
pages. This process is often called “scraping.” Web scraping made easy
introduces how to do this with Google Sheets or Microsoft Excel.
In Google Sheets, you can use the IMPORTHTML function to import the
data from an HTML table (or list) on a web page. This function is similar to
the IMPORTRANGE function. Refer to the Google Help Center's
IMPORTHTML page for more information about the syntax.
The IMPORTDATA function
Sometimes data displayed on the web is in the form of a comma- or tab-
delimited file.
You can use the IMPORTDATA function in a Google Sheet to import data
into a Google Sheet. This function is similar to the IMPORTRANGE
function. Refer to Google Help Center's IMPORTDATA page for more
information and the syntax.
public datasets
Google Cloud Public Datasets
Dataset Search
Kaggle
BigQuery
Global Health Observatory data
1000 Genomes
UNICEF State of the World’s Children
CPS Labor Force Statistics
The Stanford Open Policing Project
SQL Cheatsheet