Google Data Analytics Professional Certificate Answers - Coursera

Course Challenge Dataset - Scenario 2

Now, it’s time to process the data. As you know, this step involves finding and eliminating errors and inaccuracies that can get in the way of your results. While cleaning the data, you notice that information about Splashtastic is missing for Store Number 15 in Row 16. Which of the following would be an appropriate course of action?

Delete the row with the missing data point.
- Replace the row with the average values of the other data points.
- Sort the spreadsheet so the row with missing data is at the bottom.

Investigate previous projects and see how this was dealt with there.

Scenario 1 continued

Once you’ve found the missing information, you analyze your dataset.

During analysis, you create a new column F. At the top of the column, you add: Average Percentage of Total Sales - Splashtastic. What is this column label called?

A title
- A reference

An attribute

A headline

Scenario 1 continued

Next, you determine the average total daily sales over the past 12 months at all stores. The entire range of cells that contain these sales are E2:E39. The correct syntax is =AVERAGE(E2:E39).

True

False

Scenario 1 continued

Fill in the blank: You’ve reached the share phase of the data analysis process. One of the things that you can do in this phase is to prepare a _____ about Splashtastic’s sales and practice your presentation.

prediction
- finding
- record

slideshow

Scenario 2, questions 6-10

You’ve been working for the nonprofit National Dental Society (NDS) as a junior data analyst for about two months. The mission of the NDS is to help its members advance the oral health of their patients. NDS members include dentists, hygienists, and dental office support staff.

The NDS is passionate about patient health. Part of this involves automatically scheduling follow-up appointments after crown replacement, emergency dental surgery, and extraction procedures. NDS believes the follow-up is an important step to ensure patient recovery and minimize infection.

Unfortunately, many patients don’t show up for these appointments, so the NDS wants to create a campaign to help its members learn how to encourage their patients to take follow-up appointments seriously. If successful, this will help the NDS achieve its mission of advancing the oral health of all patients.

Your supervisor has just sent you an email saying that you’re doing very well on the team, and he wants to give you some additional responsibility. He describes the issue of many missed follow-up appointments. You are tasked with analyzing data about this problem and presenting your findings using data visualizations.

An NDS member with three dental offices in Colorado offers to share its data on missed appointments. So, your supervisor uses a database query to access the dataset from the dental group. The query instructs the database to retrieve all patient information from the member’s three dental offices, located in zip code 81137.

The table is dental_data_table, and the column name is zip_code. You write the following query, but get an error. What statement will correct the problem?

SELECT * FROM dental_data_table WHERE zip code = 81137

zip_code = 81137
- WHERE_zip code = 81137

WHERE zip_code = 81137

WHERE 81137

Scenario 2 continued

The dataset your supervisor retrieved and imported into a spreadsheet includes a list of patients, their demographic information, dental procedure types, and whether they attended their follow-up appointment. To use the dataset for this scenario, click the link below and select “Use Template.”

Link to template: Course Challenge - Scenario 2

If you don’t have a Google account, you can download the template directly from the attachment below.

Course Challenge Dataset - Scenario 1 - Scenario 1_ Pharmacy Data - Part 1

The patient demographic information includes data such as age, gender, and home address. When examining the geographic data, you notice that all the patients live in the same zip code.

Fill in the blank: The fact that the dataset includes people who all live in the same zip code might get in the way of ______.

fairness

accuracy
spreadsheet formulas or functions
data visualization

Scenario 2 continued

As you’re reviewing the dataset, you notice that there are a disproportionate number of senior citizens. So, you investigate further and find out that this zip code represents a rural community in Colorado with about 800 residents. In addition, there’s a large assisted-living facility in the area. Nearly 300 of the residents in the 81137 zip code live in the facility.

You recognize that’s a sizable number, so you want to find out if age has an effect on a patient’s likelihood to attend a follow-up dental appointment. You analyze the data, and your analysis reveals that older people tend to miss follow-ups more than younger people.

So, you do some research online and discover that people over the age 60 are 50% more likely to miss dentist appointments. Sometimes this is because they’re on a fixed income. Also, many senior citizens lack transportation to get to and from appointments.

With this new knowledge, you write an email to your supervisor expressing your concerns about the dataset. He agrees with your concerns, but he’s also impressed with what you’ve learned and thinks your findings could be very important to the project. He asks you to change the business task. Now, the NDS campaign will be about educating dental offices on the challenges faced by senior citizens and finding ways to help them access quality dental care.

Fill in the blank: Changing the business task involves defining a new _____.

gap analysis plan
- graphical representation of the data

question or problem to be solved

data-cleaning strategy

Scenario 2 continued

You continue with your analysis. In the end, your findings support what you discovered during your online research: As people get older, they’re less likely to attend follow-up dental visits.

But you’re not done yet. You know that data should be combined with human insights in order to lead to true data-driven decision-making. So, your next step is to share this information with people who are familiar with the problem professionally. They’ll help verify the results of your data analysis.

Fill in the blank: The people who are familiar with a problem and help verify the results of data analysis are _____.

customers
- data scientists
- stakeholders

subject-matter experts

Scenario 2 continued

The subject-matter experts are impressed by your analysis. The team agrees to move to the next step: data visualization. You know it’s important that stakeholders at NDS can quickly and easily understand that older people are less likely to attend important follow-up dental appointments than younger people. This will help them create an effective campaign for members.

It’s time to create your presentation to stakeholders. It will include a data visualization that demonstrates the lifetime trend of people being less likely to attend follow-up appointments as they get older.

Why would a line chart be the most effective in representing this?

Line charts are effective in displaying points in series.

Line charts arrange data values into rows.
Line charts represent data values as proportionally sized wedges.
Line charts arrange data values into columns.

Scenario 1, question 1-5

You’ve just started a new job as a data analyst. You’re working for a midsized pharmacy chain with 38 stores in the American Southwest. Your supervisor shares a new data analysis project with you.She explains that the pharmacy is considering discontinuing a bubble bath product called Splashtastic. Your supervisor wants you to analyze sales data and determine what percentage of each store’s total daily sales come from that product. Then, you’ll present your findings to leadership.You know that it's important to follow each step of the data analysis process: ask, prepare, process, analyze, share, and act. So, you begin by defining the problem and making sure you fully understand stakeholder expectations.One of the questions you ask is where to find the dataset you’ll be working with. Your supervisor explains that the company database has all the information you need. Next, you continue to the prepare step. You access the database and write a query to retrieve data about Splashtastic. You notice that there are only 38 rows of data, representing the company’s 38 stores. In addition, your dataset contains five columns: Store Number, Average Daily Customers, Average Daily Splashtastic Sales (Units), Average Daily Splashtastic Sales (Dollars), and Average Total Daily Sales (All Products).

You know that spreadsheets work well for processing and analyzing a small dataset, like the one you’re using. To get the data from the database into a spreadsheet, what should you do?

Email a copy of the dataset to your company email address.
- Use Tableau to convert the data into a spreadsheet.

Download the data as a .CSV file, then import it into a spreadsheet.

Copy and paste the data into a spreadsheet.

Scenario 1 continued

Link to template: Course Challenge - Scenario 1

If you don’t have a Google account, you can download the template directly from the attachment below.

Course Challenge Dataset - Scenario 2

Sort the spreadsheet so the row with missing data is at the bottom.

Ask a colleague on your team how they've handled similar issues in the past.

Delete the row with the missing data point.

Replace the row with the average values of the other data points.

Scenario 1 continued

Once you’ve found the missing information, you analyze your dataset. During analysis, you create a new column F. At the top of the column, you add the attribute Average Percentage of Total Sales - Splashtastic.

Fill in the blank: An attribute is a _______ or quality of data used to label a column.

number
- headline
- response

characteristic

Scenario 1 continued

Next, you determine the average total daily sales over the past 12 months at all stores. The entire range of cells that contain these sales are E2:E39. Identify the correct way to write your function.

=AVERAGE(E2+E39)
- =AVERAGE(E2,E39)

=AVERAGE(E2:E39)

=AVERAGE(E2-E39)

Scenario 1 continued

You’ve reached the share phase of the data analysis process. It involves which of the following? Select all that apply.

Present your findings about Splashtastic to stakeholders.

Prepare a slideshow about Splashtastic’s sales and practice your presentation.

Create a data visualization to highlight the Splashtastic sales insights you've discovered.

Stop selling Splashtastic because it doesn't represent a large percentage of total sales.

Scenario 2, questions 6-10

The table is dental_data_table, and the column name is zip_code. You have written the following query, but received an error when it ran.

SELECT * FROM dental_data_table WHERE dental_data_table = 81137

Given the objective of the query, where is the mistake in this query?

SELECT, FROM, and WHERE should not be capitalized.
- In line 2, dental_data_table should be replaced with zip_code 81137.
- The third line should be WHERE = 81137

In line 3, dental_data_table should be replaced with zip_code.

Scenario 2 continued

Link to template: Course Challenge - Scenario 2

If you don’t have a Google account, you can download the template directly from the attachment below.

Course Challenge Dataset - Scenario 1 - Scenario 1_ Pharmacy Data - Part 1

The patient demographic information includes data such as age, gender, and home address. When examining the geographic data, you notice that all the patients live in the same zip code.

Fill in the blank: The fact that the dataset includes people who all live in the same zip code might get in the way of ______.

fairness

accuracy
spreadsheet formulas or functions
data visualization

Scenario 2 continued

The business task has changed. What is the nature of that change?

Creating a graphical representation of the data
- Using a database instead of a spreadsheet
- Conducting a gap analysis

Defining the new question or problem to be solved

Scenario 2 continued

You continue with your analysis. In the end, your findings support what you discovered during your online research: As people get older, they’re less likely to attend follow-up dental visits.

Fill in the blank: The people who are familiar with a problem and help verify the results of data analysis are _____.

stakeholders

subject-matter experts

customers
data scientists

Scenario 2 continued

Which type of chart will be most effective?

A doughnut chart
- A table
- A pie chart

A line chart

Scenario 1 continued

Link to template: Course Challenge - Scenario 1

If you don’t have a Google account, you can download the template directly from the attachment below.

Course Challenge Dataset - Scenario 2

Now, it’s time to process the data. As you know, this step involves finding and eliminating errors and inaccuracies that can get in the way of your results. While cleaning the data, you notice there’s missing data in one of the rows. What might you do to fix this problem? Select all that apply.

Ask a colleague on your team how they've handled similar issues in the past

Sort the spreadsheet so the row with missing data is at the bottom
- Delete the row with the missing data point

Ask you supervisor for guidance

Scenario 1 continued

Next, you determine the average total daily sales over the past 12 months at all stores. The entire range of cells that contain these sales are E2:E39. To do this, you use a function. You input =AVE(E2:E39), but this returns an error. What is the correct command?

=AVERAGE(E2:E39)

=AVERAGE(E2+E39)
=AVERAGE(E2,E29)
=AVERAGE(E2-E39)

Scenario 2, questions 6-10

The table is dental_data_table, and the column name is zip_code. You write the following query.

SELECT * FROM dental_data_table WHERE zip code = 81137

This query is incorrect. How could it be fixed?

In line 3, replace zip code with zip_code

Decapitalize SELECT, FROM, and WHERE
Rewrite line 3 as WHERE_zip code = 81137
Rewrite line 3 as zip_code = 81137

Scenario 2 continued

Link to template: Course Challenge - Scenario 2

If you don’t have a Google account, you can download the template directly from the attachment below.

Course 2 Scenario 1 Email from Supervisor.pdf

The patient demographic information includes data such as age, gender, and home address. You review the demographic data, paying particular attention to geography. What geographic aspect of the data may negatively impact fairness?

The patients all live in the same city.
- The patients all live in houses.
- The patients all live in the same country.

The patients all live in the same zip code.

Scenario 2 continued

You continue with your analysis. In the end, your findings support what you discovered during your online research: As people get older, they’re less likely to attend follow-up dental visits.

Fill in the blank: Subject matter experts are people who are familiar with a problem. They can help by identifying inconsistencies in the analysis, _____, and validating the choices being made.

redefining the business problem

offering insights into the business problem

creating a presentation with the data
collecting data relevant to the business problem

Scenario 2 continued

Fill in the blank: The type of chart that would be most effective in visualizing this is a _____.

bar chart
- pie chart
- doughnut chart

line chart

Course 2 – Ask Questions to Make Data-Driven Decisions

Week 1 – Effective questions

In structured thinking, why would a data analyst organize the available information?

To ask SMART questions

To recognize the current problem or situation

To summarize results using data visualizations
To consult with subject matter experts

A local internet service provider is expecting an increase in the number of people streaming online entertainment. Their data analyst uses data to estimate the required bandwidth necessary to service its customers. This is an example of which problem type?

Discovering connections
- Identifying themes

Making predictions

Spotting something unusual

Fill in the blank: The question, “How could we improve our website to simplify the returns process for our online customers?” is _____-oriented.

action

bias
passive
data

Structured thinking involves which of the following processes? Select all that apply.

Revealing gaps and opportunities
Recognizing the current problem or situation
Organizing available information
Asking SMART questions

A data analyst creates data visualizations and a slideshow. Which phase of the data analysis process does this describe?

Prepare
Act
Share
Process

A recycling center that sponsors a podcast about saving the environment is an example of what strategy?

Defining the problem to be solved
Making recommendations
Staying on budget
Trying to reach a target audience

A data analyst is working for a local power company. Recently, many new apartments have been built in the community, so the company wants to determine how much electricity it needs to produce for the new residents in the future. A data analyst uses data to help the company make a more informed forecast. This is an example of which problem type?

Spotting something unusual
Discovering connections
Making predictions
Identifying themes

Describe the key difference between the problem types of categorizing things and identifying themes.

Categorizing things involves determining how items are different from each other. Identifying themes brings different items back together in a single group.
Categorizing things involves assigning grades to items. Identifying themes involves creating new classifications for items.
Categorizing things involves taking inventory of items. Identifying themes deals with creating labels for items.
Categorizing things involves assigning items to categories. Identifying themes takes those categories a step further, grouping them into broader themes.

Which of the following examples are leading questions? Select all that apply.

What do you enjoy most about our service?
How did you learn about our company?
In what ways did our product meet your needs?
How satisfied were you with our customer representative?

The question, “Why don’t our employees complete their timesheets each Friday by noon?” is not action-oriented. Which of the following questions are action-oriented and more likely to lead to change? Select all that apply.

What functionalities would make our timesheet web page more user-friendly?
What features could we add to our calendar app as a weekly timesheet reminder to employees?
How could we simplify the time-keeping process for our employees?
Why don’t employees prioritize filling out their timesheets by noon on Fridays?

On a customer service questionnaire, a data analyst asks, “If you could contact our customer service department via chat, how much valuable time would that save you?” Why is this question unfair?

It is closed-ended
It uses slang words that not everyone can understand
It is vague
It makes assumptions

Shuffle Q/A

Organizing available information and revealing gaps and opportunities are part of what process?

Identifying connections between two or more things
Categorizing things
Using structured thinking
Applying the SMART methodology

The share phase of the data analysis process typically involves which of the following activities? Select all that apply.

Summarizing results using data visualizations
Communicating findings
Creating a slideshow to present to stakeholders
Putting analysis into action to solve a problem

A company wants to make more informed decisions regarding next year’s business strategy. An analyst uses data to help identify how things will likely work out in the future. This is an example of which problem type?

Making predictions
Spotting something unusual
Identifying themes
Discovering connections

Fill in the blank: Categorizing things involves assigning items to categories, whereas _____ takes those categories a step further, grouping them into broader classifications.

Making predictions
Finding patterns
Discovering connections
Identifying themes

Questions that make assumptions often involve concepts that are formed without evidence. An example of this is an idea that is accepted as true without proof.

True
False

A garden center wants to attract more customers. A data analyst in the marketing department suggests advertising in popular landscaping magazines. This is an example of what practice?

Reaching your target audience
Collecting customer information
Monitoring social media feedback
Developing a data analytics case study

Categorizing things involves assigning items to categories. Identifying themes takes those categories a step further, grouping them into broader themes or classifications.

True
False

Which of the following examples are closed-ended questions? Select all that apply.

Is math your favorite subject?
What grade did you get on the math test?
How old are you?
What are your thoughts about math?

The question, “How could we improve our website to simplify the returns process for our online customers?” is action-oriented.

True
False

Which of the following questions make assumptions? Select all that apply.

Keeping employees engaged is important, isn’t it?
Wouldn’t you agree that product A is better than product B?
Did you get through to customer service?
It must be frustrating waiting on hold for so long, right?

Structured thinking involves recognizing the current problem or situation you’re facing and identifying your options.

True
False

Which of the following examples are leading questions? Select all that apply.

How satisfied were you with our customer representative?
What do you enjoy most about our service?
In what ways did our product meet your needs?
How did you learn about our company?

On a customer service questionnaire, a data analyst asks, “If you could contact our customer service department via chat, how much valuable time would that save you?” Why is this question unfair?

It is closed-ended
It uses slang words that not everyone can understand
It is vague
It makes assumptions

Fill in the blank: To apply structured thinking, a data analyst should ______ the available information in order to reveal gaps and opportunities and recognize the current problem or situation.

organize
communicate
share
record

A national chain of sporting goods stores advertises during popular sporting television broadcasts. This is an example of the company doing what?

Reaching its target audience
Demonstrating its support for a sports team
Defining the problem to be solved
Monitoring social feedback

In data analysis, categorizing things involves which of the following?

Creating new classifications for items and assigning grades to items
Assigning items to categories
Taking an inventory of items
Determining how items are different from each other

The question, “Why was the Monday afternoon yoga class successful?” is not measurable. Which of the following questions presents a measurable way to learn about the yoga class?

Why do people like taking yoga classes on Mondays?
How many customers responded to our recent half-price yoga promotion?
Is yoga a great way to stretch and strengthen your body?
Do yoga instructors seem more energetic at the beginning of the week?

Why should a data analyst only ask fair questions?

Unfair questions do not have answers.
Unfair questions can provide data that is misleading.
Fair questions are biased.
Fair questions do not offend people.

In the share step of the data analysis process, a data analyst summarizes their results using data visualizations and creates a slideshow to present to stakeholders. What else might they do in this step?

Collect data.
Communicate findings.
Organize the available information
Shred paper files.

If a cooking supply store wants to attract more customers, where can they advertise to better reach their target audience? Select all that apply.

On TV during the season finale of The Best Chef in the Universe
At a bus stop near a local culinary school
On a podcast for foodies
In a magazine all about advertising

Making predictions is one of the six data analytics problem types. How does data factor into such problem types?

The data informs the predictions.
The data confirms the decisions.
The data are the predictions.
The predictions validate the data.

Which of the following examples are closed-ended questions? Select all that apply.

How tall are you?
What did you think about the article that I sent you?
What is your opinion of the new movie?
Have you taken this class before?

What is the defining characteristic of measurable questions?

They are questions that have numbers in them.
Their answers are numbers that can be interpreted qualitatively.
They are questions that use numbers as categories.
Their answers are numbers that can be interpreted mathematically.

Fill in the blank: “How many people filled out the survey?” is an example of a question that is _____ in the context of data analysis.

categorical
symbolic
measureable
qualitative

Week 2 – Data-driven decisions

An analyst is working with data from two school programs. They discover that the data is measured differently across programs and this may impact how they can work with the data. What does this example describe?

Data-inspired decision-making
- Data-driven decision-making

The limitations of working with data

Data that cannot be analyzed

A retail store runs a special sale with the goal of increasing sales over the holiday season. They use the increase in sales over the same month last year as a starting point. What type of goal is this an example of?

Metric goal

Theoretical goal
Finite goal
Conceptual goal

A data analyst assesses how well their company’s marketing campaign is performing. They apply a formula that compares the cost of the campaign and its net profit. What does this formula measure?

The return on investment

Total revenue
The average cost
Total cost

Which of the following statements describes an algorithm?

A process or set of rules to be followed for a specific task
A method for recognizing the current problem or situation and identifying the options
A tool that enables data analysts to spot something unusual
A technique for focusing on a single topic or a few closely related ideas

Fill in the blank: If a data analyst is measuring qualities and characteristics, they are considering _____ data.

quantitative
unbiased
cleaned
qualitative

In data analytics, reports use live, incoming data from multiple datasets; dashboards use static collections of data.

True
False

A pivot table is a data-summarization tool used in data processing. Which of the following tasks can pivot tables perform? Select all that apply.

Group data
Clean data
Calculate totals from data
Reorganize data

A metric is a single, quantifiable type of data that can be used for what task?

Setting and evaluating goals
Defining a problem type
Cleaning data
Sorting and filtering data

Which of the following options describes a metric goal? Select all that apply.

Evaluated using metrics
Indefinite
Measurable
Based on theory

Fill in the blank: Return on investment compares the _____ of an investment to the net profit gained from that investment.

success
purpose
cost
timing

Fill in the blank: A data analyst is using data to address a large-scale problem. This type of analysis would most likely require _____. Select all that apply.

small data
data that reflects change over time
data represented by a limited number of metrics
big data

Shuffle Q/A

Fill in the blank: In data analytics, qualitative data _____. Select all that apply.

is always time bound
measures qualities and characteristics
is subjective
measures numerical facts

Fill in the blank: A _____ is a data-summarization tool used to sort, reorganize, group, count, total, or average data.

report
dashboard
function
pivot table

Fill in the blank: A _____ goal is measurable and evaluated using single, quantifiable data.

metric
finite
conceptual
benchmark

Describe the main differences between big and small data.

Small data is typically stored and organized in databases. Big data is typically stored and organized in spreadsheets.
Small data is less useful to data analysts. Big data is more useful to data analysts.
Small data is specific and concerns a short time period. Big data is less specific and concerns a longer time period.
Small data has been cleaned and sorted. Big data has not yet been cleaned or sorted.

In data analytics, a pattern is defined as a process or set of rules to be followed for a specific task.

True
False

In data analytics, quantitative data measures qualities and characteristics.

True
False

In data analytics, reports use data that doesn’t change once it’s been recorded. Which of the following terms describes this type of data?

Comprehensive
Real-time
Monitored
Static

Which data-summarization tool do data analysts use to sort, reorganize, group, count, total, or average data?

A function
A pivot table
A dashboard
A report

A metric is a specific type of data that companies use to identify a problem domain.

True
False

Fill in the blank: A metric goal is a _____ goal set by a company that is evaluated using metrics.

finite
theoretical
conceptual
measurable

A data analyst is using data from a short time period to solve a problem related to someone’s day-to-day decisions. They are most likely working with small data.

True
False

If a data analyst compares the cost of an investment to the net profit of that investment over a period of time, they’re analyzing the investment scope.

True
False

What is an example of using a metric? Select all that apply.

Using column headers to sort and filter data
Using annual profit targets to set and evaluate goals
Using key performance indicators, such as click-through rates, to measure revenue
Using a pie chart to visualize data

Fill in the blank: In data analytics, a process or set of rules to be followed for a specific task is _____.

a pattern
a domain
an algorithm
a value

Fill in the blank: Return on investment compares the cost of an investment to the _____ of that investment.

purpose
timing
net profit
future success

Week 3 – More spreadsheet basics

What calculations can you carry out within a spreadsheet? Select all that apply.

Minimum

Maximum

Copying

Average

What are some of the ways that data analysts can gather data? Select all that apply.

Use data received from a colleague

Use data they collect themselves

Use data from open source locations

Use restricted data from the government

You sum the entries in cells F3 through F200 in your spreadsheet. What is the correct function for this?

=SUM(F3+F200)
- =SUM(F3;F200)
- =SUM(F3,F200)

=SUM(F3:F200)

What are some of the causes of bias in data analytics? Select all that apply.

Cultural differences

Social norms

Multiple perspectives

Serving an agenda

Fill in the blank: In spreadsheets, data analysts begin _____ with an equal sign (=).

cells
numbers
formulas
charts

Fill in the blank: The labels that describe the type of data contained in each column of a spreadsheet are called _____.

assignments
attributes
allowances
aspects

Which of the following tasks might be performed using spreadsheets?

Maintain information about accounts
Write a sales pitch
Develop communication skills
Land a new client

Formulas are created by the user, whereas functions are preset commands in spreadsheets.

True
False

In the function =MAX(B5:B15), what does B5:B15 represent?

Observation
Column
Attribute
Range

What is the correct spreadsheet formula for multiplying cell H2 times cell H5?

=H2/H5
=H2^H5
=H2*H5
=H2xH5

To avoid bias when collecting data, a data analyst should keep what in mind?

Context
Opinion
Stakeholders
Graphs

A data analyst might use descriptive column headers in order to achieve what goal?

Add context to their data
Protect the spreadsheet
Alphabetize the spreadsheet data
Filter the data

Shuffle Q/A

To determine an organization’s annual budget, a data analyst might use a slideshow.

True
False

Which of the following are ways that data analysts can add context to their data? Select all that apply.

Use descriptive column headers
Consider where the data came from
Create reports for stakeholders
Ask questions about the data

In spreadsheets, formulas and functions end with an equal sign (=).

True
False

A data analyst could use spreadsheets to achieve which of the following tasks?

Motivate employees
Write reports
Build code for a new app
Predict next quarter’s sales

In the function =MAX(G3:G13), what does G3:G13 represent?

an attribute
an observation
The range
a table

What is the correct spreadsheet formula for multiplying cell D5 times cell D7?

=D5xD7
=D5^D7
=D5*D7
=D5/D7

Fill in the blank: A data analyst considers which organization created, collected, or funded a dataset in order to understand its _____.

structure
detail
length
context

Which of the following statements accurately describe formulas and functions? Select all that apply.

Formulas are instructions that perform specific calculations.
Formulas may only be used once per spreadsheet column.
Functions are preset commands that perform calculations.
Formulas and functions assist data analysts in calculations, both simple and complex.

In the function =MAX(B5:B15), what does B5:B15 represent?

Attribute
Column
Observation
Range

What is the correct spreadsheet formula for multiplying cell H2 times cell H5?

=H2*H5
=H2/H5
=H2xH5
=H2^H5

Both formulas and functions in spreadsheets begin with what symbol?

Equal sign (=)
Colon (:)
Hyphen (-)
Bracket ([)

Fill in the blank: By negatively influencing data collection, ____ can have a detrimental effect on analysis.

objectivity
bias
partiality
filtering

Attributes are used in spreadsheets for what purpose?

Analyze the data in a row
Insert data into each column
Add a new column
Label the data in each column

To determine an organization’s annual budget, a data analyst might use a slideshow.

True
False

Which of the following statements describes a key difference between formulas and functions?

Formulas contain words and numbers, and functions contain numbers only.
Formulas span two or more cells, and functions exist in only one cell.
Formulas are used in graphs, and functions are not.
Formulas are written by the user, and functions are already defined.

What do data analysts use to label the type of data contained in each column in a spreadsheet?

Tables
Menus
Attributes
Headings

In the function =MAX(A1:A12), what does A1:A12 represent?

The range
The operator
The maximum
The formula

Fill in the blank: Putting data into context helps data analysts eliminate _____.

labels
intolerance
bias
fairness

Week 4 – Always remember the stakeholder

Fill in the blank: Your data analytics team is working on a project for the marketing department. The person most likely to be the _____ stakeholder is the vice president of marketing.

primary

necessary
secondary
project

To communicate clearly with stakeholders and team members, there are four key questions data analysts ask themselves. One of the questions is: What does my audience already know? Identify the remaining three questions. Select all that apply.

What does my audience need to know?

How can I communicate effectively to my audience?

Why are stakeholders and team members important?

Who is my audience?

You accept a new project from a high level stakeholder. After beginning the project, you find that you aren’t sure what you are supposed to do. How do you handle this?

Determine the objectives that make the most sense and work towards those.

Set up a meeting with the stakeholder to discuss the specific objectives they wanted.

Ask a member of your team what was done on the last project and do the same.
Perform the standard analysis and present its insights.

A data analyst collects a large amount of data for their project to ensure that the data represents a diverse set of perspectives. What element of data collection does this describe?

Sample size

Statistical significance
Visualization
Data cleaning

When leading a meeting, it is important to respect your team members’ time. What are some ways of doing this? Select all that apply.

Pay attention to what others are saying

Arrive to the meeting on time

Discuss work that does not impact the attendees.

Be prepared to talk about your work

What are some of the “don’ts” when attending a meeting?

Don't dominate the conversation.

Don't show up unprepared.

Don’t arrive late.

Don’t arrive early.

Your manager assigns you a project task, and you don’t understand the point of the project. What questions can you ask them to determine the objective? Select all that apply.

What is their end goal?

What do you have to do for this task?
- What is the story they want to tell?

What is the big picture?

A data analyst starts a new project for the operations team at their company. They take a few hours at the beginning of the project to identify their stakeholders. The secondary stakeholders are most likely which of the following people? Select all that apply.

The data analyst
The project manager
The president of the company
The vice president of operations

A data analyst is researching the buying behavior of people who shop at a company’s retail store and those who might shop there in the future. During the analysis, it will be important to stay in communication with the people who most often interact with these shoppers. They are members of the executive team.

True
False

There are four key questions data analysts ask themselves: Who is my audience? What do they already know? What do they need to know? And how can I communicate effectively with them? These questions enable data analysts to achieve what goal?

Understand who is managing the data
Communicate clearly with stakeholders and team members
Identify primary and secondary stakeholders
Complete data analysis projects on time

Data analysts pay attention to sample size in order to achieve what goals? Select all that apply.

To fully understand the scope of the analytics project
To avoid a small sample size leading to inaccurate judgements
To make sure the data represents a diverse set of perspectives
To make sure a few unusual responses don’t skew results

A data analyst receives an email from the vice president of marketing. The vice president is upset because the report they want from the analyst is late. Select the best course of action.

The analyst should call the vice president and ask them how important it really is to their marketing efforts.
The analyst should send the report immediately, even if it’s not completely finished. This will make the vice president happy.
The analyst should respond saying they understand the vice president’s concerns, provide a status update, and let the vice president know when to expect the completed report.
The analyst should apologize for the delay and inform the vice president that the marketing managers caused the delay.

Arriving at meetings prepared is an important part of creating a professional work environment. This involves which of the following actions? Select all that apply.

Bringing materials to take notes with
Considering what questions you may be asked so you’re prepared to answer
Reading the meeting agenda ahead of time
Bringing a laptop to keep an eye on emails

A data analyst joins an online meeting on time. After reviewing the agenda, they see that their project comes at the very end. They’re extremely busy and can use this time to stay on top of their current projects. How should they proceed?

Mute themselves and turn off the camera, then continue working on other tasks until their project is mentioned.
Tell the participants that they’re having technical trouble, then leave the meeting to continue working on other tasks.
Politely let the presenter know they’re going to leave the meeting and rejoin toward the end.
Stay focused and attentive during the entire meeting. Even though some items on the agenda don’t affect their projects, they could still learn something or have something to contribute.

Your data analytics team has been working on a project for a few weeks. You’re almost done, when your supervisor suddenly changes the business task. Everyone has to start all over again. You announce to the team that you’re going to say something to the supervisor about how unreasonable this is. What’s the best next step?

Insist that the entire data analytics team complain to your supervisor.
Go see your supervisor face-to-face and tell them why you’re so upset.
Write a polite, but strongly worded email to your supervisor.
Take a few minutes to calm down, then ask your colleagues to share their perspectives so you can work together to determine the best next step.

Shuffle Q/A

A data analyst is researching the buying behavior of people who shop at a company’s retail store and those who might shop there in the future. During the analysis, it will be important to stay in communication with the team that most often interacts with these shoppers. What is the name of this team?

Data science team
Project management team
Executive team
Customer-facing team

You receive an angry email from a colleague on the marketing team. The marketing colleague believes you have taken credit for their work. You do not believe this is true. Select the best course of action.

Delete the email. It’s best not to create any additional conflict.
Reply to the email, asking if they can schedule a time to talk about this in person in order to allow both of you to share your perspectives.
Walk over to the marketing colleague’s cubicle, and tell them you strongly disagree.
Forward the email to the marketing director with an equally angry note.

A data analyst has been invited to a meeting. They review the agenda and notice that their data analysis project is one of the topics that will be discussed. How can they prepare for an effective meeting? Select all that apply.

Bring materials for taking notes.
Plan to arrive on time.
Think about what project updates they should share.
Create and share a revised agenda that includes many more details about their project.

Which of the following steps are key to leading a professional online meeting? Select all that apply.

Maintaining control of the meeting by keeping everyone else on mute.
Sitting in a quiet area that’s free of distractions
Making sure your technology is working properly before starting the meeting
Keeping an eye on your inbox during the meeting in case of an important email

A team member has asked you to take on a task, and you don’t understand the point of the project. It seems like it will be a waste of your time. The best course of action would be to politely explain your concerns and decline the project.

True
False

Fill in the blank: A data analytics team is working on a project to measure the success of a company’s new financial strategy. The vice president of finance is most likely to be the _____.

project manager
analyst
primary stakeholder
secondary stakeholder

At an online marketplace, the _____ includes anyone in an organization who interacts with current or potential shoppers.

executive team
data science team
project management team
customer-facing team

There are four key questions data analysts ask themselves: Who is my audience? What do they already know? What do they need to know? And how can I communicate effectively with them? These questions enable data analysts to identify the person in charge of managing the data.

True
False

A data analyst has been invited to a meeting. They review the agenda and notice that their data analysis project is one of the topics that will be discussed. They plan to arrive on time and have a pen and paper to take notes. But they do not spend time considering project updates they could share or questions they may be asked. This is appropriate because they’re not the one running the meeting.

True
False

A data analytics team is working on a project to measure the success of a company’s new financial strategy. Select the person most likely to be the primary stakeholder for this project.

The project manager
The data analyst
The vice president of finance
The director of analytics

To communicate clearly with stakeholders and team members, there are four key questions data analysts ask themselves. One of them is: What does my audience need to know? Identify the remaining three questions. Select all that apply.

Why are stakeholders and team members important?
Who is my audience?
How can I communicate effectively to my audience?
What does my audience already know?

Conflict is a natural part of working on a team. What are some ways to help shift a situation from problematic to productive? Select all that apply.

Take a moment to check your emotions before engaging in an argument.
Ask for a conversation to help you better understand the big picture.
Reframe the question by asking, “How can I help?”
Identify the person who caused the issue so they can take responsibility.

Data analysts focus on statistical significance to make sure they have enough data so that a few unusual responses don’t skew results

True
False

A data analyst feels overworked. They often stay late to finish work, and have started missing deadlines. Their supervisor emails them another project to complete, and this causes the analyst even more stress. How should they handle this situation?

Accept the new project right away and hope to not miss another deadline.
Wait a few minutes to think it over, then respond with a meeting request to discuss this project and the general workload.
Walk into the supervisor’s office and tell them to give the project to someone else.
Respond immediately, letting the supervisor know the expectations at this company are unreasonable.

When participating in an online meeting, it’s okay to keep your inbox open in another browser window. Participants won’t be distracted because they can’t see it, and you might receive a very important message.

True
False

Course challenge

Scenario 1, questions 1-5

You’ve just started a job as a data analyst at a small software company that provides data analytics and business intelligence solutions. Your supervisor asks you to kick off a project with a new client, Athena’s Story, a feminist bookstore. They have four existing locations, and the fifth shop has just opened in your community.

Athena’s Story wants to produce a campaign to generate excitement for an upcoming celebration and introduce the bookstore to the community. They share some data with your team to help make the event as successful as possible.

Your task is to review the assignment and the available data, then present your approach to your supervisor. Click the link below to access the email from your supervisor:

Then, review the email, and the Customer Survey and Historical Sales datasets.

To use the templates for the datasets, click the links below and select “Use Template.”

Links to templates: Customer Survey and Historical Sales

If you don't have a Google account, you can download the CSV files directly from the attachments below.

HistoricalSales - HistoricalSales

After reading the email, you notice that the acronym WHM appears in multiple places. You look it up online, and the most common result is web host manager. That doesn’t seem right to you, as it doesn’t fit the context of a feminist bookstore. Still, you should assume it’s correct and continue with the project.

True

False

Scenario 1 continued

Now that you know WHM stands for Women’s History Month, you continue reviewing the datasets. You notice that the Customer Survey dataset contains both qualitative and quantitative data.

To use the template for the dataset, click the link below and select “Use Template.”

Link to template: Customer Survey

If you don't have a Google account, you can download the CSV file directly from the attachment below.

The qualitative data includes information from which columns? Select all that apply.

Column E (Survey Q5: What do you like most about Athena's Story?)

Column B (Survey Q2: If answered "Yes" to Q1, how do you plan to celebrate?)

Column D (Survey Q4: If answered "Yes" to Q3, how many books do you typically purchase during March?)

Column F (Survey Q6: What types of books would you like to see more of at Athena's Story?)

Scenario 1 continued

Next, you review the customer feedback in column F of the Customer Survey dataset.

To use the template for the dataset, click the link below and select “Use Template.”

Link to template: Customer Survey

If you don't have a Google account, you can download the CSV file directly from the attachment below.

HistoricalSales - HistoricalSales

The attribute of column F is, “Survey Q6: What types of books would you like to see more of at Athena's Story?” In order to verify that children’s literature and feminist zines are among the most popular genres, you create a visualization. This will help you clearly identify which genres are most likely to sell well during the Women’s History Month campaign.

Your visualization looks like this:

Pie chart categories: -Feminist science fiction 4.8% -Books about women 2.4% -Women's journals 2.4% -Feminist literary criticism 2.4% -Children's literature 15.5% -Women's history books 2.4% -Biographies of inspiration 20.2% -Feminist fiction 26.2% -Feminist zines 14.3% -Feminist poetry 4.6% -Feminist novels 3.6%

Pie chart categories: Feminist science fiction 4.8% Books about women 2.4% Women's journals 2.4% Feminist literary criticism 2.4% Children's literature 15.5% Women's history books 2.4% Biographies of inspiration 20.2% Feminist fiction 26.2% Feminist zines 14.3% Feminist poetry 4.6% Feminist novels 3.6%

Fill in the blank: The visualization you create demonstrates the percentages of each book genre that make up the total number of survey responses. It’s called a _____ chart.

bubble

doughnut
area

Now that you’ve confirmed that children’s literature and feminist zines are among the most requested book genres, you review the Historical Sales dataset.

To use the template for the dataset, click the link below and select “Use Template.”

Link to template: Historical Sales

If you don't have a Google account, you can download the CSV file directly from the attachment below.

Junior Data Analyst Job Description.pdf

You’re pleased to see that the dataset contains data that’s specific to children’s literature and feminist zines. This will provide you with the information you need to make data-inspired decisions. In addition, the children’s literature and feminist zines metrics will help you organize and analyze the data about each genre in order to determine if they’re likely to be profitable.

Next, you calculate the total sales over 52 weeks for feminist zines. You type =CALCULATE(E2-E53) but get an error. What is the correct syntax?

=MAX(E2:E53)
- =COUNT(E2:E53)

=SUM(E2:E53)

=CALC(E2:E53)

Scenario 1 continued

After familiarizing yourself with the project and available data, you present your approach to your supervisor. You provide a scope of work, which includes important details, a schedule, and information on how you plan to prepare and validate the data. You also share some of your initial results and the pie chart you created.

In addition, you identify the problem type, or domain, for the data analysis project. You decide that the historical sales data can be used to provide insights into the types of books that will sell best during Women’s History Month this coming year. This will also enable you to determine if Athena’s Story should begin selling more children’s literature and feminist zines.

Using historical data to make informed decisions about how things may be in the future is an example of spotting something unusual.

True

False

Scenario 2, questions 6-10

You’ve completed this program and are now interviewing for your first junior data analyst position. You’re hoping to be hired by an event planning company, Patel Events Plus. Access the job description below:

Human Resources Director Email.pdf

So far, you’ve successfully completed the first round of interviews with the human resources manager and director of data and strategy. Now, the vice president of data and strategy wants to learn more about your approach to managing projects and clients. Access the email you receive from the human resources director below:

Patel Event Plus Org Chart.pdf

You arrive Thursday at 1:45 PM for your 2 PM interview. Soon, you’re taken into the office of Mila Aronowicz, vice president of data and strategy. After welcoming you, she begins the behavioral interview.

First, she hands you a copy of Patel Events Plus’s organizational chart. Access the chart below:

Patel Events Plus dataset

As you’ve learned in this course, stakeholders are people who invest time, interest, and resources into the projects you’ll be working on as a data analyst. Let’s say you’re working on a project involving data and strategy.

Based on what you find in the organizational chart, who should be considered the primary stakeholder for projects involving data and strategy?

Director
- Project manager
- Chief executive officer

Vice president

Scenario 2 continued

Next, the vice president wants to understand your knowledge about asking effective questions. Consider and respond to the following question. Select all that apply.

Let’s say we just completed a big event for a client and wanted to find out if they were satisfied with their experience. Provide some examples of measurable questions that you could include in the customer feedback survey. Select all that apply.

Would you recommend Patel Events Plus to a colleague or friend? Yes or no?

Why did you enjoy the event planned by Patel Events Plus?

On a scale from 1 to 5, with 1 being not at all likely and 5 being very likely, how likely are you to recommend Patel Events Plus?

How would you describe your event experience?

Scenario 2 continued

Now, the vice president presents a situation having to do with resolving challenges and meeting stakeholder expectations. Consider and respond to the following question.

You’re working on a rush project, and you discover your dataset is not clean. Even though it has numerous nulls, redundant data, and other issues, the primary stakeholder insists that you move ahead and use it anyway. The project timeline is so tight that there simply isn’t enough time for cleaning. How would you handle that situation?

Contact the stakeholder’s boss to let them know about the issue and ask for help managing the stakeholder’s expectations.
- The stakeholder is in charge. It's best to do as they say and use the unclean dataset.
- Clean the data as quickly as you can. It’s not perfect, but it’s better than it was before, and this way you can meet the deadline.

Communicate the situation to your supervisor and ask for advice on how to handle the situation with the stakeholder.

Scenario 2 continued

Your next interview question deals with sharing information with stakeholders. Consider and respond to the following question. Select all that apply.

Let’s say you’ve designed a dashboard to give stakeholders easy, automatic access to data about an upcoming event. Describe the benefits of using a dashboard. Select all that apply.

Dashboards offer live monitoring of incoming data.

Dashboards enable stakeholders to interact with the data.

Dashboards are easy to design and understand.
Dashboards present pre-cleaned, historical data.

Scenario 2 continued

Your final behavioral interview question involves using metrics to answer business questions. Your interviewer hands you a copy of a Patel Events dataset.

To use the template for this dataset, click the link below and select “Use Template.”

Link to template: Patel Events Data

If you don't have a Google account, you can download the CSV file directly from the attachment below.

Course 2 Scenario 1 Email from Supervisor.pdf

Then, she asks: Recently, Patel Events Plus purchased a new venue for our events. If we asked you to calculate the return on investment of this purchase, the metrics to consider would be the cost of the investment and what else?

Net profit in 2019

Average event revenues
2019 events held at new venue
Purchase date

Scenario 1, questions 1-5

Your task is to review the assignment and the available data, then present your approach to your supervisor. Click the link below to access the email from your supervisor:

Then, review the email, and the Customer Survey and Historical Sales datasets.

To use the templates for the datasets, click the links below and select “Use Template.”

Links to templates: Customer Survey and Historical Sales

If you don't have a Google account, you can download the CSV files directly from the attachments below.

HistoricalSales - HistoricalSales

After reading the email, you notice that the acronym WHM appears in multiple places. You look it up online, and the most common result is web host manager. That doesn’t seem right to you, as it doesn’t fit the context of a feminist bookstore. You email your supervisor to ask. When writing your email, what do you do to ensure it sounds professional? Select all that apply.

Respect your supervisor’s time by writing an email that’s short and to the point.

Use a polite greeting and closing.

Read your email aloud before sending to catch any typos or grammatical errors and to ensure the communication is clear.

Write a clear subject line that gets a fast response so you can keep working: “WHM? NEED TO KNOW WHAT THAT IS RIGHT AWAY.”

Scenario 1 continued

Now that you know WHM stands for Women’s History Month, you continue reviewing the datasets. You notice that the Customer Survey dataset contains both qualitative and quantitative data.

To use the template for the dataset, click the link below and select “Use Template.”

Link to template: Customer Survey

If you don't have a Google account, you can download the CSV file directly from the attachment below.

Junior Data Analyst Job Description.pdf

The quantitative data includes information from which columns? Select all that apply.

Column D (Survey Q4: If answered "Yes" to Q3, how many books do you typically purchase during March?)

Column C (Survey Q3: Do you purchase feminist books in honor of WHM, either for yourself or as a gift for someone else?)

Column E (Survey Q5: What do you like most about Athena's Story?)

Column A (Survey Q1: Do you plan to celebrate WHM?)

Scenario 2, questions 6-10

Human Resources Director Email.pdf

Patel Event Plus Org Chart.pdf

First, she hands you a copy of Patel Events Plus’s organizational chart. Access the chart below:

C3 Scenario 1_Client Email .pdf

Based on what you find in the organizational chart, which individuals are considered the secondary stakeholders? Select all that apply.

Project manager, analytics

Data analytics coordinator

Director, data analytics
Chief executive officer

Scenario 2 continued

Next, the vice president wants to understand your knowledge about asking effective questions. Consider and respond to the following question. Select all that apply.

How would you rate your overall experience — poor, average, above average, or excellent?

Why did our event options and features create a successful event?

Was this your first time using Patel Events Plus to plan your event? Yes or no?

Did you experience any problems with your event? Yes or no?

Now, the vice president presents a situation having to do with resolving challenges and meeting stakeholder expectations. Consider and respond to the following question. Select all that apply.

You’re working with a dataset that the data analytics coordinator should have cleaned, but it turns out that it wasn’t. Your supervisor thought the dataset was ready for use, but you discover nulls, redundant data, and other issues. The project is due in less than two weeks. Which of the following options would be an appropriate approach? Select all that apply.

Proceed with the project using the available data. You don’t want to get the associate data analyst in trouble, and you don’t want to miss your deadline.
- Email the data analytics coordinator to ask if the two of you can work together to clean the data, as the project is on a tight timeline.

Provide your supervisor with a proposed revised timeline. Politely explain that you need some additional time to clean the data.

Email your supervisor and the data analytics coordinator to communicate about the issue. Ask if you can meet to come up with a solution.

Scenario 2 continued

Your next interview question deals with sharing information with stakeholders. Consider and respond to the following question. Select all that apply.

Let’s say you’ve created a report to present stakeholders with information about an upcoming event. Describe the benefits of using a report. Select all that apply.

Reports enable stakeholders to interact with the data.
- Reports offer live monitoring of incoming data.

Reports reflect data that’s already been cleaned and sorted.

Reports provide a snapshot of high-level, historical data.

Course 3 – Prepare Data for Exploration

Week 1 – Data types and structures

A data analyst is preparing an annual report for company executives and decides to use internal data. Why do they choose to use internal data? Select all that apply.

Internal data is easier to collect.

Internal data is less likely to need cleaning.

Internal data is more reliable.

Internal data is less vulnerable to biased collection.

A data analyst is reviewing data that has been organized into a table format. What type of data is in the table?

Unstructured data
- Internal data
- External data

Structured data

A data analyst is reviewing a spreadsheet. They find that the columns contain the data variables. What data format does this describe?

Tall data

Wide data

Short data
Narrow data

A data analyst at a book publisher is working on an urgent report for executives. They are using only historical data. What is the most likely reason for choosing to analyze only historical data?

The project has a very short time frame
The data is unknown
There is plenty of time to research historical data
The data is constantly changing

Which of the following are examples of discrete data? Select all that apply.

Box office returns
Movie running time
Movie budget
Number of actors in movie

Which of the following questions collects nominal qualitative data?

Is this your first time dining at this restaurant?
How many people do you usually dine with?
How many times have you dined at this restaurant?
On a scale of 1-10, how would you rate your service today?

Why is internal data considered more reliable and easier to collect than external data?

Internal data circumvents privacy restrictions.
Internal data comes from people you know.
Internal data has much larger sample sizes.
Internal data lives within a company’s own systems.

A social media post is an example of structured data.

True
False

Fill in the blank: A Boolean data type can have _____ possible values.

three
10
two
infinite

The following is a selection from a spreadsheet:

What kind of data format does it contain?

Short
Wide
Narrow
Long

A data analyst is working in a spreadsheet application. They use Save As to change the file type from .XLS to .CSV. This is an example of a data transformation.

True
False

Shuffle Q/A

A data analyst is working on an urgent traffic study. As a result of the short time frame, which type of data are they most likely to use?

Theoretical
Historical
Personal
Unclean

Nominal qualitative data has a set order or scale.

True
False

Internal data is more reliable because it’s clean.

True
False

Structured data is likely to be found in which of the following formats? Select all that apply.

Audio file
Digital photo
Spreadsheet
Table

A Boolean data type must have a numeric value.

True
False

In long data, separate columns contain the values and the context for the values, respectively. What does each column contain in wide data?

A specific constraint
A specific data type
A unique data variable
A unique format

Fill in the blank: Data transformation enables data analysts to change the _____ of the data.

value
structure
accuracy
meaning

Continuous data is measured and has a limited number of values.

True
False

Which of the following values are examples of a Boolean data type? Select all that apply.

True or false
Yes, no, or unsure
Yes or no
One, two, or three

If you have a short time frame for data collection and need an answer immediately, you likely will have to use historical data.

True
False

Which of the following is an example of continuous data?

Leading actors in movie
Box office returns
Movie run time
Movie budget

Which of the following questions collect nominal qualitative data? Select all that apply.

How likely are you to recommend this restaurant to a friend?
Is this your first time dining at this restaurant?
Have you heard of our frequent diner program?
Did anyone recommend our restaurant to you today?

Data transformation can change the structure of the data. An example of this is taking data stored in one format and converting it to another.

True
False

Which of the following is a benefit of internal data?

Internal data is less vulnerable to biased collection.
Internal data is the only data relevant to the problem.
Internal data is less likely to need cleaning.
Internal data is more reliable and easier to collect.

Week 2 – Bias, credibility, privacy, ethics, and access

Which of the following best describes data bias?

It is a preference in the data in favor of or against a person, group, or thing.

It is a measure of how closely the data represent the population.
It refers to how consistent the data is over time as new data is added.
It is the tendency for the data to remain accurate for longer.

In data ethics, consent gives an individual the right to know the answers to which of the following questions? Select all that apply.

How will my data be used?

How long will my data be stored?

Why am I being forced to share my data?

Why is my data being collected?

An individual who provides their data has the right to know and understand all of the data-processing activities and algorithms used on that data. This concept refers to which aspect of data ethics?

Ownership
- Currency

Transaction transparency

Consent

A company collects and analyzes user data. As part of this process, they preserve each data subject’s information and activity for all data transactions. What data ethics concept does this describe?

Consent

Privacy

Transformation
Transparency

Fill in the blank: A preference in favor of or against a person, group of people, or thing is called _____. It is an error in data analytics that can systematically skew results in a certain direction.

data collection
data interoperability
data bias
data anonymization

Which type of bias is the tendency to always construe ambiguous situations in a positive or negative way?

Observer
Confirmation
Sampling
Interpretation

Which of the following are qualities of unreliable data? Select all that apply.

Biased
Inaccurate
Vetted
Incomplete

Fill in the blank: Data _____ refers to well-founded standards of right and wrong that dictate how data is collected, shared, and used.

ethics
privacy
credibility
anonymization

Ownership is a key issue in data ethics. Who owns data?

The organization that invests time and money collecting, processing, and analyzing the data
The government that passes data-protection legislation
The individual who originally generates the data
The law enforcement agencies that enforce data protection laws

An employer accesses an employee’s credit report without their consent. This is not a violation of the employee’s privacy because they work at the company.

True
False

What is the process of protecting people’s private or sensitive data by eliminating identifying information?

Data governance
Data design
Data ethics
Data anonymization

A key aspect of open data is free access to people’s personal information.

True
False

Shuffle Q/A

A clinic surveys a group of male and female patients about their experience with physical therapy. The survey does not include people with disabilities. Is the survey data biased?

Yes
No

A university surveys its student-athletes about their experience in college sports. The survey only includes student-athletes with scholarships. What type of bias is this an example of?

Interpretation bias
Observer bias
Confirmation bias
Sampling bias

An individual who provides their data has the right to know and understand all of the data-processing activities and algorithms used on that data. This is called ownership.

True
False

The right to inspect, update, or correct your own data is part of which aspect of data ethics?

Data openness
Data ownership
Data consent
Data privacy

Interoperability is key to open data’s success. Which of the following is an example of interoperability?

A website charges a fee to access a database
An analyst removes all personally identifiable information from a database
Different databases use common formats and terminology
A company restricts the use of a database to its own employees

Which of the following situations are examples of bias? Select all that apply.

A researcher who surveys a sample group that is representative of the population
A scholar who only reads sources that support their argument
A dancing competition judge who is a close friend of the dancer who wins the competition
A daycare that won’t hire men for childcare positions

Which of the following “C’s” describe qualities of good data? Select all that apply.

Comprehensive
Cited
Current
Consequential

If a company uses your personal data as part of a financial transaction, you should be made aware of the nature and scale of the transaction. What concept of data ethics does this refer to?

Privacy
Currency
Ownership
Consent

Data anonymization applies to both text and images.

True
False

The government of a large city collects data on the quality of the city’s infrastructure. Any business, nonprofit organization, or person can access the government’s databases and re-use or redistribute the data. Is this an example of open data?

Yes
No

Which of the following are types of data bias often encountered in data analytics? Select all that apply.

Observer bias
Interpretation bias
Educational bias
Confirmation bias

In general, the usefulness of data decreases as time passes.

True
False

Ownership is a key issue in data ethics. Who owns data?

The law enforcement agencies that enforce data protection laws
The organization that invests time and money collecting, processing, and analyzing the data
The individual who originally generates the data
The government that passes data-protection legislation

Which of the following are commonly used methods for anonymizing data? Select all that apply.

Masking
Hashing
Deleting
Blanking

Week 3 – Databases: Where data lives

Which of the following properties describe primary keys in a relational database? Select all that apply.

They are used to ensure data in a specific column is unique.

They refer to another primary key in a different table.
- There can be multiple primary keys in a table.

There can only be one primary key in a table.

What do metadata repositories do to make it simpler and quicker to use multiple data sources for analysis? Select all that apply.

Keep metadata in a common structure

Keep the metadata in an accessible form

Store the related data assets

Describe where data came from

Which type of metadata is used to indicate where a digital asset or piece of information originated from?

Structural

Administrative

Descriptive
General

What is the process that data analysts use to ensure the formal management of their company’s data assets?

Data integrity
- Data aggregation
- Data mapping

Data governance

What are some of the reasons for open data initiatives? Select all that apply.

To educate citizens about local issues

To make government activities more transparent

To increase protection of proprietary data

To give people ways to provide feedback to the government

A nonprofit has a list of their many donors. They want to send a mailing to donors who live within 100 miles of the nonprofit’s headquarters. How could they use the column distance_to_hq to only display the donors that meet those conditions?

Filter out distances smaller than 50 miles.

Filter out distance greater than 100 miles.

Sort numerically in ascending order.
Sort numerically in descending order.

In the following piece of SQL code, what does the asterisk (*) represent?

SELECT * FROM customers

Include all columns.

Include all tables.
Include the first column.
Include specified conditions.

You are working with a database table that contains customer data. The company column lists the company affiliated with each customer. You want to find customers from the company Riotur.

You write the SQL query below.

SELECT * FROM Customer

What code would be added to return only customers affiliated with the company Riotur?

company = ‘Riotur’

WHERE company = ‘Riotur’

JOIN company = ‘Riotur’
IN company = ‘Riotur’

Primary and foreign keys are two connected identifiers within separate tables. These tables exist in what kind of database?

Metadata
Primary
Relational
Normalized

When working with data from an external source, what can metadata help data analysts do? Select all that apply.

Ensure data is clean and reliable
Combine data from more than one source
Understand the contents of a database
Choose which analyses to run

3.Think about data as a student at a high school. In this metaphor, which of the following are examples of metadata? Select all that apply.

Student’s ID number
Student’s enrollment date
Classes the student is enrolled in
Grades the student earns

Fill in the blank: Data _____ is the process of ensuring the formal management of a company’s data assets.

aggregation
integrity
mapping
governance

In what circumstance might a data analyst choose not to use external data in their analysis?

The data represents diverse perspectives
The data is too thorough
The data is free for anyone to access
The data cannot be confirmed to be reliable

A nonprofit maintains a list of how many laptops they provide to each school in the county. In the table, there is a column called number_of_laptops. A data analyst wants to determine which schools were given the fewest laptops. How should they sort the data to return these schools first?

Sort alphabetically in ascending order
Sort numerically in descending order
Sort alphabetically in descending order
Sort numerically in ascending order

7.When writing a query, it's necessary for the name of the dataset to be inside two backticks in order for the query to run properly.

True
False

You are working with a database table that contains customer data. The city column lists the city where each customer is located. You want to find out which customers are located in Berlin.

You write the SQL query below. Add a WHERE clause that will return only customers located in Berlin.

How many customers are located in Berlin?

Shuffle Q/A

Relational databases contain a series of tables connected to form relationships. Which two types of fields exist in two connected tables?

Star and snowflake schemas
Descriptive and structural metadata
Internal and external data
Primary and foreign keys

Data analysts use metadata for what tasks? Select all that apply.

To combine data from more than one source
To perform data analyses
To interpret the contents of a database
To evaluate the quality of data

Think about data as driving a taxi cab. In this metaphor, which of the following are examples of metadata? Select all that apply.

Company that owns the taxi
License plate number
Make and model of the taxi cab
Passengers the taxi picks up

Fill in the blank: Data governance is the process of ensuring that a company’s _____ are managed in a formal manner.

business tasks
data engineers
data assets
business strategies

What are some key benefits of using external data? Select all that apply.

External data is always reliable.
External data is free to use.
External data has broad reach.
External data can provide industry-level perspectives.

A data analyst reviews a national database of movie theater showings. They want to find the first movies shown in San Francisco in 2001. How can they organize the data to return the first 10 movies shown at the top of their list? Select all that apply.

Filter out showings not in 2001
Sort by date in descending order
Sort by date in ascending order
Filter out showings outside of San Francisco

You are working with a database table that contains customer data. The state column lists the state where each customer is located. The state names are abbreviated. You want to find out which customers are located in the state of Florida (FL).

You write the SQL query below. Add a WHERE clause that will return only customers located in FL.

How many customers are located in FL?

Structural metadata indicates how a piece of data is organized and whether it’s part of one or more than one data collection.

True
False

Relational databases illustrate relationships between tables. Which fields represent the connection between these tables? Select all that apply.

Foreign keys
External keys
Primary keys
Secondary keys

When writing a query, you must remove the two backticks around the name of the dataset in order for the query to run properly.

True
False

You are working with a database table that contains customer data. The first_name column lists the first name of each customer. You are only interested in customers with the first name Mark.

You write the SQL query below. Add a WHERE clause that will return only customers named Mark.

How many customers are named Mark?

Metadata is data about data. What kinds of information can metadata offer about a particular dataset? Select all that apply.

How to combine the data with another dataset
Which analyses to perform on the data
If the data is clean and reliable
What kinds of data it contains

A data analyst reviews a database of Wisconsin car sales to find the last car models sold in Milwaukee in 2019. How can they sort and filter the data to return the last five cars sold at the top of their list? Select all that apply.

Filter out sales outside of Milwaukee
Filter out sales not in 2019
Sort by sale date in descending order
Sort by sale date in ascending order

When writing a query, the name of the dataset can either be inside two backticks, or not, and the query will still run properly.

True
False

A data analyst chooses not to use external data because it represents diverse perspectives. This is an appropriate decision when working with external data.

True
False

Week 4 – Organizing and protecting your data

A data analyst has been tasked with a new project and has started to collect data from multiple sources. The analyst will be working with multiple team members on this project and needs to create a naming convention to allow projects files to be located efficiently. What should the analyst include in each file's name? Select all that apply.

Content

Collaborators

Version number

Creation date

Your boss assigns you a new multi-phase project and you create a naming convention for all of your files. With this project lasting years and incorporating multiple analysts it’s crucial that you create data explaining how your naming conventions are structured. What is this data called?

Named convention
- Labeled data

Metadata

Descriptive data

A data analyst creates a file that lists people who donated to their organization’s fund drive. An effective name for the file is FundDriveDonors_20210216_V01.

True

False

You have just started a new project and have created a naming convention for all of your files. Once the data has been collected you start foldering. What does the foldering process allow you to do?

Organize your files into subfolders

Sort your files by name
Organize your files in the filing cabinet
Organize your files into the cloud

A data analyst deletes an old project’s files from their active project folder. A few months later, they have to review the work that they completed on this project but cannot find the older project files. What should the data analyst have done?

Archive the project

Email the project
Print the project
Delete the project

As a data analyst, folder organization is key to being efficient at your job. A common practice is to lay out your folders with broad topics at the top with more specific topics at the bottom. What’s the name of this approach?

Bottom to top
- Left to right
- Heterarchy

Hierarchy

To reduce clutter, a data analyst hides cells that contain long, complex formulas. The hidden cells allow the data analyst to protect their formulas and hide the data from other users with access to the spreadsheet.

True

False

Fill in the blank: File-naming conventions are _____ that describe a file's content, creation date, or version.

general attributes
frequent suggestions
consistent guidelines
common verifications

A data analytics team uses data about data to indicate consistent naming conventions for a project. What type of data is involved in this scenario?

Metadata
Aggregated data
Long data
Big data

Data analysts use naming conventions to help them identify or locate a file. Which of the following is an example of an effective file name?

Elementary_Students_20090221_V03
Sept_ElemtaryStudents_V1
ElementarySchoolStudents_EnrollingSeptember2021_PlusRisingMiddleSchool_FJPSKVND
Elem_9

Data analysts use a process called encryption to organize folders into subfolders.

True
False

A data analyst completes a project. They move project files to another location to keep them separate from their current work. This is an example of what process?

Duplicating files
Destroying files
Archiving files
Renaming files

6.Data analysts create hierarchies to organize their folders. How are folder hierarchies structured?

Broad topics at the top, then more specific topics below
Broad topics at the right, then more specific topics at the left
Specific topics at the top, then more broad topics below
Broad topics at the left, then more specific topics at the right

Using encryption to protect data is an example of what?

Data validation
Data integrity
Data ethics
Data security

To reduce clutter, a data analyst hides cells that contain long, complex formulas. To view the formulas again, the analyst will need to adjust the spreadsheet sharing or encryption settings.

True
False

Shuffle Q/A

A data analyst is working with a file from a customer satisfaction survey. The survey was sent to anyone who became a customer between April and June, 2020. Which of the following is an effective name for the file?

April_May_June_2020_Responses_to_New_Customer_Survey_ANALYSISDATA_928310
NewCustomerSurvey_2020-6-20_V03
Survey_Responses
Apr-June2020_CustSurvey_V

Foldering may be used by data analysts to organize folders into what?

Databases
Subfolders
Versions
Tables

Data analysts use archiving to separate current from past work. It also cuts down on clutter.

True
False

Fill in the blank: Data analysts create _____ to structure their folders.

scales
sequences
ladders
hierarchies

A data analyst wants to ensure only people on their analytics team can access, edit, and download a spreadsheet. They can use which of the following tools? Select all that apply.

Sharing permissions
Templates
Filtering
Encryption

A data analyst wants to share spreadsheet tab A with their team. They’re still working with tabs B and C, and they don’t want their team members to access them yet. Hiding tabs B and C will protect them from being accessed.

True
False

A data analytics team labels its files to indicate their content, creation date, and version number. The team is using what data organization tool?

File-naming verifications
File-naming conventions
File-naming attributes
File-naming references

To align file naming and storage practices, it’s useful to develop metadata practices with your data analytics team.

True
False

What process do data analysts use to keep project-related files together and organize them into subfolders?

Foldering
Encrypting
Editing
Naming

A data analyst completes a project. They move project files to another location to keep them separate from their current work. This is an example of what process?

Renaming files
Archiving files
Destroying files
Duplicating files

A data analyst adds sharing permissions to limit who can edit the data contained within a file. This is an example of what?

Data validation
Data integrity
Data security
Data ethics

What aspects of a file do file-naming conventions typically describe? Select all that apply.

Creation date
Content
Version number
Collaborators

Fill in the blank: A data analytics team uses _____ to indicate consistent naming conventions for a project. This is an example of using data about data.

folder hierarchies
classifications
metadata
version control

A data analyst creates a file that lists people who donated to their organization’s fund drive. An effective name for the file is FundDriveDonors_20210216_V01.

True
False

Data analysts use archiving to separate current from past work. What does this process involve?

Using secure data-erase software to destroy old files
Reviewing current data files to confirm they’ve been cleaned
Moving files from completed projects to another location
Reorganizing and renaming current files

Data analysts create hierarchies to organize their folders. They do this by structuring folders by specific topics at the top, then more broadly below.

True
False

Course challenge

Scenario 1, questions 1-5

You’ve been working at a data analytics consulting company for the past six months. Your team helps restaurants use their data to better understand customer preferences and identify opportunities to become more profitable.

To do this, your team analyzes customer feedback to improve restaurant performance. You use data to help restaurants make better staffing decisions and drive customer loyalty. Your analysis can even track the number of times a customer requests a new dish or ingredient in order to revise restaurant menus.

Currently, you’re working with a vegetarian sandwich restaurant called Garden. The owner wants to make food deliveries more efficient and profitable. To accomplish this goal, your team will use delivery data to better understand when orders leave Garden, when they get to the customer, and overall customer satisfaction with the orders.

Before project kickoff, you attend a discovery session with the vice president of customer experience at Garden. He shares information to help your team better understand the business and project objectives. As a follow-up, he sends you an email with datasets.

Click below to read the email:

Course 3 Final Challenge Data Sets - Customer survey data (1)

And click below to access the datasets:

Course 3 Final Challenge Data Sets - Delivery times_distance (1)

CustomerSurveyData - Customer survey data

Reviewing the data enables you to describe how you will use it to achieve your client’s goals. First, you notice that all of the data is first-party data, which means that it was collected from outside sources.

True

False

Scenario 1 continued

Next, you review the customer satisfaction survey data. To use the template for the customer satisfaction survey data, click the link below and select “Use Template.”

Link to template: Customer Satisfaction Survey data

If you don’t have a Google account, download the CSV file directly from the attachment below.

DeliveryTimes_DistanceData - Delivery times_distance

You notice that the data in column E is an example of Boolean data. Why did you come to this conclusion?

It has each subject in multiple rows.
- It is qualitative data with a set order or scale.
- It is organized in a certain format, such as rows and columns.

It has only two possible values.

Scenario 1 continued

Now, you review the data on delivery times and the distance of customers from the restaurant.

To use the template for the dataset, click the link below and select “Use Template.”

Link to template: Delivery Times/Distance

If you don’t have a Google account, download the CSV file directly from the attachment below.

The data in column D is an example of nominal data.

True

False

Scenario 2 continued

Consider and respond to the following question. Select all that apply.

Our data analytics team often uses both internal and external data. Describe the difference between the two.

External data came from a company’s own systems. Internal data came from the organization.
- External data is often generated from within the company. Internal data is generated outside the organization.

Internal data came from a company’s own systems. External data comes from outside the organization.

Internal data is often generated from within the company. External data is generated outside the organization.

Scenario 2 continued

For your final question, your interviewer explains that Sewati Financial Services needs its clients’ trust, and this is an important responsibility for the data analytics team.

He asks you to identify which data analytics practice involves preserving a data subject’s information and activity any time a data transaction occurs.

Bias
- Encryption

Data privacy

Sharing permissions

Scenario 1, questions 1-5

Reviewing the data enables you to describe how you will use it to achieve your client’s goals. First, you notice that all of the data was collected by Garden employees using their own resources. What type of data does this describe?

Third-party data
First-party data
Nominal data
Qualitative data

Scenario 1 continued

The next thing you review is the file containing pictures of sandwich deliveries over a period of 30 days. This is unstructured data, which means what?

It’s objective and measures facts.

It’s not organized in an easily identifiable manner.

It’s organized in a certain format.
It’s collected by a group directly from its audience and then sold.

Scenario 1 continued

Next, you review the customer satisfaction survey data. To use the template for the customer satisfaction survey data, click the link below and select “Use Template.”

Link to template: Customer Satisfaction Survey data

If you don’t have a Google account, download the CSV file directly from the attachment below.

The question in column E asks, “Was your order accurate? Please respond yes or no.” The responses listed in column E are an example of Boolean data.

True
False

Scenario 1 continued

Now, you review the data on delivery times and the distance of customers from the restaurant.

To use the template for the dataset, click the link below and select “Use Template.”

Link to template: Delivery Times/Distance

If you don’t have a Google account, download the CSV file directly from the attachment below.

The data in column E shows the duration of deliveries from Garden to customers. What type of data is this? Select all that apply.

Continuous data
Quantitative data
Qualitative data
Discrete data

Scenario 1 continued

The next thing you review is the file containing pictures of sandwich deliveries over a period of 30 days. This is an example of structured data.

True
False

Scenario 1 continued

Now that you’re familiar with the data, you want to build trust with the team at Garden. You decide to impress them by taking the initiative to reach out to your social media followers. You explain that Garden is a new client, and you show them the pictures of Garden’s sandwich deliveries from the client file. Then, you ask them if they have any photos of sandwich deliveries that you can evaluate.

This is an example of going above and beyond expectations and a great way to build trust.

True
False

Scenario 2, questions 6-10

You’ve completed this program and are interviewing for a junior data scientist position at a company called Sewati Financial Services.

So far, you’ve successfully completed the first interview with a recruiter. They arrange your second interview with the team at Sewati Financial Services.

You arrive 15 minutes early for your interview. Soon, you are escorted into a conference room, where you meet Kai Harvey, the senior manager of strategy. After welcoming you, he begins the behavioral interview.

Consider and respond to the following question. Select all that apply.

Our data analytics team often surveys clients to get their feedback. If you were on the team, how would you ensure the process does not cause potential bias?

Make sure the wording of the survey question does not encourage a specific response from participants.
Include clients with disabilities in the survey sample.
Give participants enough time to answer each survey question.
Instruct participants to share their name and contact information.

Scenario 2 continued

Consider and respond to the following question. Select all that apply.

Our data analytics team often uses external data. Where can you access useful external data?

A public database
An open-data website
Sewati Financial Services database in the cloud
Sewati Financial Services website

Scenario 2 continued

Consider and respond to the following question. Select all that apply.

Our analysts often work within the same spreadsheet, but for different purposes. What tools would you use in such a situation?

Freeze the header rows
Sort the data to make it easier to understand, analyze, and visualize
Filter to show only the data that meets a specific criteria
Encrypt the spreadsheet so only you can access it

Scenario 2 continued

Next, your interviewer wants to better understand your knowledge of basic SQL commands. He asks: How would you write a query that retrieves only data about people who work in Boise from the Clients table in our database?

Scenario 2 continued

For your final question, your interviewer explains that Sewati Financial Services cares about data privacy. The company needs its clients’ trust, and this is an important responsibility for the data analytics team.

He asks: What does data privacy involve? Select all that apply.

Encryption and sharing permissions
Preserving a data subject’s information and activity any time a data transaction occurs
Putting privacy measures in place to protect people’s data
A person’s legal right to their data

Shuffle Q/A

Scenario 1, questions 1-5

Click below to read the email:

And click below to access the datasets:

Reviewing the data enables you to describe how you will use it to achieve your client’s goals. First, you notice that all of the data is first-party data. What does this mean?

It’s data that was collected from outside sources.
It’s data that was collected by Garden employees using the company’s own resources.
It’s a type of data that’s categorized without a set order.
It’s subjective data that measures qualities and characteristics.

Scenario 1 continued

Next, you review the customer satisfaction survey data. To use the template for the customer satisfaction survey data, click the link below and select “Use Template.”

Link to template: Customer Satisfaction Survey data

If you don’t have a Google account, download the CSV file directly from the attachment below.

You notice that the data in column E is an example of Boolean data. Why did you come to this conclusion?

It has each subject in multiple rows.
It is qualitative data with a set order or scale.
It has only two possible values.
It is organized in a certain format, such as rows and columns.

Scenario 1 continued

Now, you review the data on delivery times and the distance of customers from the restaurant.

To use the template for the dataset, click the link below and select “Use Template.”

Link to template: Delivery Times/Distance

If you don’t have a Google account, download the CSV file directly from the attachment below.

Fill in the blank: The data in column E is an example of _____ data. Select all that apply.

continuous
qualitative
discrete
quantitative

Scenario 1 continued

The next thing you review is the file containing pictures of sandwich deliveries over a period of 30 days. What type of data is this?

Ordinal
Unstructured
Discrete
Relational

Scenario 1 continued

Now that you’re familiar with the data, you want to build trust with the team at Garden.

What actions should you take when working with their data? Select all that apply.

Keep the data safe by implementing data-security measures, such as password protection and user permissions.
Share the client’s data with other delivery restaurants to compare performance.
Post on social media that you’re working with Garden and would like feedback from any of your contacts who have ordered there before.
Organize the data using effective naming conventions.

Scenario 2, questions 6-10

You’ve completed this program and are interviewing for a junior data scientist position at a company called Sewati Financial Services.

Click below to review the job description:

So far, you’ve successfully completed the first interview with a recruiter. They arrange your second interview with the team at Sewati Financial Services.

Click below to read the email from the human resources director:

Consider and respond to the following question. Select all that apply.

Our data analytics team often surveys clients to get their feedback. If you were on the team, how would you ensure the sample is representative of the population as a whole?

Only include participants who can answer survey questions in a timely manner.
Make sure the sample is chosen at random.
Include clients with disabilities in the survey sample.
Use a randomized sample of the population that includes all genders.

Scenario 2 continued

Next, your interviewer wants to better understand your knowledge of basic SQL commands. He asks: How would you write a query that retrieves only data about people who joined our firm in 2019 from the Clients table in our database?

Scenario 2 continued

For your final question, your interviewer explains that Sewati Financial Services cares about its clients’ trust, and this is an important responsibility for the data analytics team. They do this by:

protecting clients from unauthorized access to their private data

ensuring freedom from inappropriate use of client data

getting consent to use someone’s data

He asks: Which data analytics practice does this describe?

Encryption
Bias
Data privacy
Sharing permissions

Scenario 1 continued

Next, you review the customer satisfaction survey data. To use the template for the customer satisfaction survey data, click the link below and select “Use Template.”

Link to template: Customer Satisfaction Survey data

If you don’t have a Google account, download the CSV file directly from the attachment below.

The question in column E asks, “Was your order accurate? Please respond yes or no.” What kind of data is this?

Second-party data
Ordinal data
Clean data
Boolean data

Scenario 1 continued

The next thing you review is the file containing pictures of sandwich deliveries over a period of 30 days. This is unstructured data, which means what?

It’s organized in a certain format.
It’s not organized in an easily identifiable manner.
It’s objective and measures facts.
It’s collected by a group directly from its audience and then sold.

Scenario 2 continued

Consider and respond to the following question. Select all that apply.

Our data analytics team often uses external data. Where can you locate useful external data?

Other financial businesses
Sewati Financial Solutions marketing department
Government sources
A professional finance association

Scenario 2 continued

Consider and respond to the following question.

Our analysts often work within the same spreadsheet, but for different purposes. How could filtering help in this situation?

Filtering enables you to highlight the header row
Filtering enables you to sort the data in a meaningful order
Filtering simplifies a spreadsheet by only showing you the information you need
Filtering encrypts the spreadsheet so only you can access it

Course 4 – Process Data from Dirty to Clean

Week 1 – The importance of integrity

Fill in the blank: As a data analyst, you need to verify that your data is _____ to ensure your analysis and conclusions are accurate.

complete and valid

private and valid
manipulated and replicated
manipulated and valid

A data analyst is given a dataset for analysis. It includes data only about the total population of every country in the previous 20 years. Based on the available data, an analyst would have the full picture and be able to determine the reasons behind a certain country's population increase from 2016 to 2017.

True

False

A data analyst is given a dataset for analysis. To use the template for this dataset, click the link below and select “Use Template.”

Link to template: June 2014 Invoices

If you don’t have a Google account, download the CSV file directly from the attachment below.

June 2014 Invoices - Sheet1

C4 B.Spoke Market Research Job Description.pdf

The analyst notices a limitation with the data in rows 8 and 9. What is the limitation?

Row 8 and row 9 show the wrong currency.
- Row 9 needs more data.

Row 9 is a duplicate of row 8.

Row 8 is not in the correct format.

A data analyst is working on a project about the global supply chain. They have a dataset with lots of relevant data from Europe and Asia. However, they decide to generate new data that represents all continents. What type of insufficient data does this scenario describe?

Data from only one source
- Data that’s outdated
- Data that keeps updating

Data that’s geographically limited

In the data analysis process, how does a sample relate to a population?

A sample is a duplicate selection of data that is taken from the population.
- A sample is an average of all the data that represents the population.
- A sample is an ideal example taken from a population.

A sample is a part of a population that is representative of the population.

A restaurant wants to gather data about a new dish by giving out free samples and asking for feedback. Who should the restaurant give samples to?

Diners who spend the most money on their meal

All diners

Selecting diners at random
Diners who are willing to pay for the samples

Fill in the blank: If a data analyst is using data that has been _____, the data will lack integrity and the analysis will be faulty.

wide
compromised
public
clean

A financial analyst imports a dataset to their computer from a storage device. As it’s being imported, the connection is interrupted, which compromises the data. Which of the following processes caused the compromise?

Data analysis
Data gathering
Data manipulation
Data transfer

A data analyst is given a dataset for analysis. It includes data about the total population of every country in the previous 20 years. Based on the available data, an analyst would be able to determine the reasons behind a certain country's population increase from 2016 to 2017.

True
False

A data analyst is given a dataset for analysis. To use the template for this dataset, click the link below and select “Use Template.”

Link to template: June 2014 Invoices

If you don’t have a Google account, download the CSV file directly from the attachment below.

Which of the following has duplicate data?

Data for Symteco on 2/21/2014
Data for Symteco on 5/20/2014
Data for Valando on 2/18/2014
Data for Valando on 1/1/2014

A data analyst at a nonprofit organization is working with a dataset about a summer fundraiser. Although they have a lot of useful data by the end of the month, they recognize that the data is insufficient. So, they decide to wait until the end of the season to begin working with the dataset. Which type of insufficient data does this example describe?

Outdated data
Data from only one source
Geographically limited data
Data that keeps updating

When gathering data through a survey, companies can save money by surveying 100% of a population.

True
False

7.Fill in the blank: Sampling bias in data collection happens when a sample isn’t representative of _____.

the population as a whole
a dataset about the population
a subset of the population
the population most affected by the data

Data and business objectives might not align for a number of reasons. Which of the following issues can prevent alignment? Select all that apply.

Sampling bias
Data integrity
Data visualization
Insufficient data

Shuffle Q/A

Which of the following conditions are necessary to ensure data integrity? Select all that apply.

Privacy
Completeness
Statistical power
Accuracy

What is one potential problem associated with data manipulation that analysts must be aware of?

Data manipulation can separate a dataset among different locations.
Data manipulation can help organize a dataset.
Data manipulation can introduce errors.
Data manipulation can make a dataset easier to read.

As a data analyst, you are working for a national pizza restaurant chain. You have a dataset with monthly order totals for each branch over the past year. With only this data, what questions can you answer?

Which region had the highest sales over the last two years?
Which branch will be the most profitable over the next year?
What was the most popular item on the menu?
Which branch had the most orders in the last month of last year?

A data analyst is given a dataset for analysis. To use the template for this dataset, click the link below and select “Use Template.”

Link to template: June 2014 Invoices

If you don’t have a Google account, download the CSV file directly from the attachment below.

June 2014 Invoices - Sheet1

The data analyst is asked to find the average estimate for Symteco over the past three years. What limitation of the data makes this impossible?

The data uses the wrong currency.
The data is all from a single year.
The data does not include Symteco.
The data does not include estimates.

A data analyst at a software company wants to learn more about industry competitors. Because the software industry has more mergers than any other field, the companies and their products are constantly evolving. The analyst has a dataset from three years ago, and they notice that many of the companies and products in the dataset have changed. What makes the analyst decide that the data is insufficient, so they should generate fresh data instead?

It is outdated data.
It is geographically limited data.
It is data that keeps updating.
It is data from only one source.

A restaurant gathers data about a new dish by providing free samples to parties of six or more diners. What does this scenario describe?

Random sampling
Unbiased sampling
Geographically limited sampling
Sampling bias

Which of the following processes helps ensure a close alignment of data and business objectives?

Completing data replication
Transferring data multiple times
Maintaining data integrity
Having data update automatically during analysis

What can jeopardize data integrity throughout its lifecycle? Select all that apply.

Insufficient data
Human error
Malware
System failures

A healthcare company keeps copies of their data at several locations across the country. The data becomes compromised because each location creates a copy of the original at different times of day. Which of the following processes caused the compromise?

Data gathering
Data manipulation
Data transfer
Data replication

A data analyst is given a dataset for analysis. It includes data about the total population of every country in the previous 20 years. Which of the following questions would the analyst need more data to address?

Which country had the smallest population in 2017?
Which country had the greatest population in 2015?
What was the reason for the population increase in a certain country?
What was the population of a certain country in 2020?

A data analyst is given a dataset for analysis. To use the template for this dataset, click the link below and select “Use Template.”

Link to template: June 2014 Invoices

If you don’t have a Google account, download the CSV file directly from the attachment below.

June 2014 Invoices - Sheet1

Which of the following are limitations of this dataset?

Identifying the most profitable clients between January and November of 2014
Identifying the least profitable clients between January and November of 2014
Identifying the worst paying client between March and December of 2014
Identifying the best paying client between January and November of 2014

A car manufacturer wants to learn more about the brand preferences of electric car owners. There are millions of electric car owners in the world. Who should the company survey?

A sample of all electric car owners
The entire population of electric car owners
A sample of car owners who have owned more than one electric car
A sample of car owners who most recently bought an electric car

A candy manufacturer finds an even distribution of sales across all age ranges of customers who purchase their products. The manufacturer decides to conduct a survey to learn more about its customer base. Due to age requirements, they can only send the survey to customers who are 21 years or older. This scenario can be described as what?

Down sampling bias
Sampling bias
Unbiased sampling
Upsampling bias

What best describes a sample size?

A subset of the population between the 25th and 50th percentile
A random subset of the population
A subset that is representative of the population as a whole
A subset of the population excluding outliers

Fill in the blank: In order to have a strong and thorough analysis, a data analyst must verify _____.

data replication
data manipulation
data engineering
data integrity

Fill in the blank: _____ is the process of changing data to make it more organized and easier to read.

Data transfer
Data manipulation
Data gathering
Data replication

You are working for a global technology company. You have a dataset with the company’s total cell phone sales by country from 2015 to present. Based on the data you have, what questions are you able to answer?

What was the effect on sales when a new phone model was launched?
What was the effect on sales when new phone features were introduced?
What countries have the most cell phone sales in the past three years?
What are the mean cell phone sales for each country since 2010?

A data analyst, working for a publishing company, gathers a dataset which includes all books sold in the United Kingdom over the last three years. However, they decide to generate new data that represents global book sales. What type of insufficient data does this scenario describe?

Data that keeps updating
Data that is outdated
Data that is geographically limited
Data from only one source

A company is trying to learn more about their customer base. They would like to conduct a survey to understand why their customers chose their brand. How should the company survey its customers?

Conduct a survey of customers who purchased a different brand
Conduct a survey of customers that live in high-income areas
Conduct a survey with a representative sample of their customer population
Conduct a survey with customers who have purchased more than five products

Sometimes during analysis, an analyst discovers that it’s necessary to adjust the business objective. When this happens, the analyst should take the initiative to do so without involving others in order to be respectful of their time.

True
False

A car dealership gathers data about their entire customer population. They decide to conduct a survey to understand why their customers chose their dealership. They send out an email to all customers who have purchased more than two vehicles in the past five years. What does this scenario describe?

Unbiased sampling
Geographically limited sampling
Random sampling
Sampling bias

A data analyst needs to migrate data from a server located at their company's headquarters to a remote site. This can lead to what type of data integrity issue?

Data replication
Data cleaning
Data transfer
Data manipulation

As a data analyst, you work with data about the life expectancy of sea turtles in the Coral Triangle. The dataset contains an estimated birthdate and deathdate for all tracked sea turtles. With the data you have, what questions are you able to answer?

What is the median age a sea turtle has lived in the Coral Triangle?
Where is the most prevalent location sea turtles are being hatched in the Coral Triangle?
What is the largest sea turtle ever recorded?
Is the sea turtle population increasing throughout the world?

A clothing manufacturer wants to learn more about why their consumers have purchased the brand’s products. How should this manufacturer conduct their survey?

Send the survey to a representative sample of their customers
Send the survey to customers who have purchased more than one product
Send the survey to their least frequent customers
Send the survey to random people who buy clothes

Week 2 – Sparkling-clean data

Fill in the blank: Conditional formatting is a spreadsheet tool that changes how _____ appear when values meet a specific condition.

charts
- filters

cells

queries

For a function to work properly, data analysts must follow each function’s predetermined structure. What is this structure called?

Validation

Syntax

Algorithm
Summary

An analyst is cleaning a new dataset. They want to make sure the data contained from cell B2 through cell B100 does not contain a number smaller than 10. Which COUNTIF function syntax can be used to answer this question?

=COUNTIF(B2:B100,"<9")

=COUNTIF(B2:B100,”>=10”)

=COUNTIF(B2:B100,>50)
=COUNTIF(B2:B200, ”<=50”)

VLOOKUP searches for a value in a row in order to return a corresponding piece of information.

True

False

To evaluate how well two or more data sources work together, data analysts use data mapping.

True

False

As part of the data-cleaning process, a data analyst creates a rule to highlight any empty cells in a bright blue color. This is an example of data visualization.

True
False

2.A data analyst at a nonprofit organization is working with the following spreadsheet, which contains member name data in column C. They want to divide this data using the underscore as a delimiter, so that first names are stored in one column and last names in another. Which tool should the analyst use?

Conditional formatting
Pivot table
SPLIT function
MID function

3.Fill in the blank: When describing a SUM function, the _____ is =SUM(value 1 through value 2).

syntax
standard
structure
script

4.You are working with the following selection of a spreadsheet:

In order to extract the five-digit postal code from Burlington, MA, what is the correct function?

=RIGHT(B3,5)
=RIGHT(5,B3)
=LEFT(5,B3)
=LEFT(B3,5)

A data analyst in a human resources department is working with the following selection of a spreadsheet:

They want to create employee identification numbers (IDs) in column D. The IDs should include the year hired plus the last four digits of the employee’s Social Security Number (SS#). What function will create the ID 20093208 for the employee in row 5?

=CONCATENATE(A5!B5)
=CONCATENATE(A5*B5)
=CONCATENATE(A5+B5)
=CONCATENATE(A5,B5)

A data analyst at an e-commerce company is working with a spreadsheet containing last month's sales. The most expensive product their company sells costs $49.99, so they want to quickly confirm that all of the data in the Sales column is $49.99 or less. What function can they use?

SUMIF
COUNTIF
COUNT
SUM

A data analyst wants to search for a certain value in a column, then return a corresponding piece of information. Which function should they use?

VALUE
VLOOKUP
MATCH
FIND

A data analyst needs to combine two datasets. Each dataset comes from a different system, and the systems store data in different ways. What can the data analyst do to ensure the data is compatible?

Use a data visualization
Map the data
Apply a data structure
Merge the data

Shuffle Q/A

In their spreadsheet, a data analyst makes cells stand out for more efficient analysis. What spreadsheet tool is used to do this?

Cell filtering
Conditional ranking
Conditional formatting
Cell querying

A data analyst uses the SPLIT function to divide a text string around a specified character and put each fragment into a new, separate cell. What is the specified character separating each item called?

Unit
Delimiter
Partition
Substring

A data analyst is using a function in a spreadsheet. For the function to work correctly, they follow the function’s syntax. What does this entail?

It is the function’s name and placement.
It is how the function can be used in a program.
It is the function’s required information and its proper placement.
It is the purpose of the function and its use.

In a spreadsheet, what is the correct function for extracting the first two characters of the string located in cell A7?

=LEFT(A7,2)
=LEFT(2,A7)
=RIGHT(A7,2)
=RIGHT(2,A7)

Fill in the blank: In a spreadsheet, the function VLOOKUP is used to _____ information in a column based on a specified data value.

return
replace
transform
delete

What describes syntax?

It is the function’s required information and its proper placement.
It is how the function can be used in a program.
It is the purpose of the function and its use.
It is the function’s name and placement.

A data analyst in a human resources department is working with the following selection of a spreadsheet:

They want to create employee identification numbers (IDs) in column D. The IDs should include the last four digits of the employee’s Social Security Number(SS#) plus the year hired. What function will create the ID 19392020 for the employee in row 4?

=CONCATENATE(B4+A4)
=CONCATENATE(B4,A4)
=CONCATENATE(A4+B4)
=CONCATENATE(A4!B4)

An analyst is cleaning a new dataset. They want to determine how many of the cells in column F have a value of 0. However, they only want rows 7 to 120 to be considered. Which COUNTIF function syntax can be used to answer this question?

=COUNTIF(F2:F1250, 0)
=COUNTIF(F7:F120, =0)
=COUNTIF(F7:F120,”0″)
=COUNTIF(F7:F120,”=0”)

A data analyst needs to combine two datasets. Each dataset comes from a different system, and the systems store data in different ways. What can the data analyst do to ensure the data is compatible prior to analyzing the data?

Use a data visualization
Map the data
Spot check for null values
Apply a data structure

A data analyst is working on a spreadsheet in which one of the columns contains name data. This data is formatted as lastname_firstname. The analyst splits this data at the underscore so that each piece—firstname and lastname—are contained in their own columns.

In this context, what is the underscore acting as?

Partition
Delimiter
Substring
MID function

A data analyst is using a function in a spreadsheet. When they input the function, they follow a predetermined structure that includes all required information for the function and its proper placement. What aspect of a function does this describe?

The specified value of the function
The syntax of the function
The length of the function
The number of characters in the function

You are working with the following selection of a spreadsheet:

In order to extract the five-digit postal code from Brandon, FL, what is the correct function?

=RIGHT(5,B4)
=RIGHT(B4,5)
=LEFT(B4,5)
=LEFT(5,B4)

A data analyst in a human resources department is working with the following selection of a spreadsheet:

=CONCATENATE(B5,A5)
=CONCATENATE(A5!B5)
=CONCATENATE(A5+B5)
=CONCATENATE(B5+A5)

Before analyzing a dataset, an analyst maps the data. What is the reason for doing this?

The analyst wants to know what attributes the data has.
The analyst thinks the dataset might have some null values.
The dataset has no visualizations.
The dataset contains data from different sources.

A data analyst suspects that there are many blank cells in their spreadsheet corresponding to missing information. What spreadsheet tool can they use to identify only those cells containing the null values?

Conditional ranking
Conditional formatting
Cell querying
Cell filtering

A data analyst is working on a spreadsheet in which one of the columns is name data. This data is formatted as lastname, firstname. The analyst chooses to divide this data into two new columns, one containing the firstname data and the other containing the lastname data. What spreadsheet tool would they use to do this?

The MID function
The SPLIT function
Substring formatting
Conditional formatting

Fill in the blank: The function _____ is used to return information in a column that contains a specified value.

VALUE
MATCH
VLOOKUP
FIND

In a spreadsheet, what function would you use to extract the last three characters of the string located in row 4, column C?

=RIGHT(3,C4)
=LEFT(C4,3)
=LEFT(3,C4)
=RIGHT(C4,3)

Week 3 – Cleaning data with SQL

In which of the following situations would a data analyst use spreadsheets instead of SQL? Select all that apply.

When working with a small dataset

When visually inspecting data

When using a language to interact with multiple database programs
When working with a dataset with more than 1,000,000 rows

In SQL databases, what data type is the value 78.99 an example of?

Integer
- String
- Boolean

Float

Fill in the blank: Data analysts usually use _____ to deal with very large datasets.

web browsers
spreadsheets
SQL
word processors

2.What are some of the benefits of using SQL for analysis? Select all that apply.

SQL interacts with database programs.
SQL tracks changes across a team.
SQL has built-in functionalities.
SQL can pull information from different database sources.

A data analyst creates many new tables in their company’s database. When the project is complete, the analyst wants to remove the tables so they don’t clutter the database. What SQL commands can they use to delete the tables?

CREATE TABLE IF NOT EXISTS
DROP TABLE IF EXISTS
UPDATE
INSERT INTO

You are working with a database table that contains invoice data. The table includes columns for invoice_id and customer_id. You want to remove duplicate entries for customer ID and sort the results by invoice ID.

You write the SQL query below. Add a DISTINCT clause that will remove duplicate entries from the customer_id column.

NOTE: The three dots (...) indicate where to add the clause.

What customer ID number appears in row 12 of your query result?

You are working with a database table that contains customer data. The table includes columns about customer location such as city, state, country, and postal_code. You want to check for postal codes that are greater than 7 characters long.

You write the SQL query below. Add a LENGTH function that will return any postal codes that are greater than 7 characters long.

What is the last name of the customer that appears in row 10 of your query result?

Rocha
Brooks
Hughes
Ramos

A data analyst is cleaning transportation data for a ride-share company. The analyst converts the data on ride duration from text strings to floats. What does this scenario describe?

Visualizing
Processing
Calculating
Typecasting

The CAST function can be used to convert the DATE datatype to the DATETIME datatype.

True
False

Fill in the blank: The _____ function can be used to return non-null values in a list.

TRIM
COALESCE
CAST
CONCAT

You are working with a database table that contains employee data. The table includes columns about employee location such as city, state, country, and postal_code. You want to retrieve the first 3 characters of each postal code. You decide to use the SUBSTR function to retrieve the first 3 characters of each postal code, and use the AS command to store the result in a new column called new_postal_code.

You write the SQL query below. Add a statement to your SQL query that will retrieve the first 3 characters of each postal code and store the result in a new column as new_postal_code.

NOTE: The three dots (...) indicate where to add the statement.

What employee ID number appears in row 5 of your query result?

NOTE: The query index starts at 1 not 0.

Shuffle Q/A

Why do data analysts choose to work with SQL? Select all that apply.

SQL can handle huge amounts of data.
SQL is a powerful software program.
SQL is a well-known standard in the professional community.
SQL is a programming language that can also create web apps.

A team of data analysts is working on a large project that will take months to complete and contains a huge amount of data. They need to document their process and communicate with multiple databases. The team decides to use a SQL server as the main analysis tool for this project and SQL for the queries. What makes this the most efficient tool? Select all that apply.

SQL efficiently handles large amounts of data.
SQL records queries and changes throughout a project.
SQL contains commands that build visualizations.
SQL allows you to connect to multiple databases.

Fill in the blank: _____ refers to the process of converting data from one type to another.

Formatting
Cleaning
Typecasting
Querying

A data analyst is working with product sales data. They import new data into a database. The database recognizes the data for product price as text strings. What SQL function can the analyst use to convert text strings to floats?

LENGTH
TRIM
SUBSTR
CAST

Fill in the blank: The _____ function can be used to join strings to create a new column.

CAST
COALESCE
TRIM
CONCAT

As a data analyst, you are working on a quick project containing a small amount of data. As the data was emailed to you, there is no need to query the data. What tool should you use to perform your analysis?

Spreadsheet
SQL
word process
CSV

A data analyst has added a massive table to their database on accident and needs to remove the table. What command can the analyst use to correct their mistake?

DROP TABLE IF NOT EXISTS
INSERT INTO
REMOVE TABLE IF EXISTS
DROP TABLE IF EXISTS

You are working with a database table that contains invoice data. The table includes a column for customer_id. You want to remove duplicate entries for customer_id and get a count of total customers in the database.

You write the SQL query below. Add a DISTINCT clause that will remove duplicate entries from the customer_id column.

NOTE: The three dots (...) indicate where to add the clause.

What is the total number of customers in the database?

In SQL databases, what data type refers to a number that does not contain a decimal?

String
Integer
Boolean
Float

After joining multiple tables you find your data contains a significant amount of null values. What function can you use to return only the non-null values in a list ?

CAST
COALESCE
TRIM
CONCAT

You are working with a database table that contains customer data. The table includes columns about customer location such as city, state, and country. The state names are abbreviated. You want to retrieve the first 2 letters of each state name. You decide to use the SUBSTR function to retrieve the first 2 letters of each state name, and use the AS command to store the result in a new column called new_state.

You write the SQL query below. Add a statement to your SQL query that will retrieve the first 2 letters of each state name and store the result in a new column as new_state.

NOTE: The three dots (...) indicate where to add the statement.

NOTE: SUBSTR takes in three arguments being column, starting_index, ending_index

What customer ID number is in row 9 of your query result?

NOTE: The query index starts at 1 not 0.

A junior data analyst joins a new company. The analyst learns that SQL is heavily utilized within the organization. Why would the organization choose to invest in SQL? Select all that apply.

SQL is a programming language that can also create web apps.
SQL can handle huge amounts of data.
SQL is a powerful software program.
SQL is a well-known standard in the professional community.

You are working with a database table that contains invoice data. The table includes columns for invoice_id and billing_state. You want to remove duplicate entries for billing state and sort the results by invoice ID.

You write the SQL query below. Add a DISTINCT clause that will remove duplicate entries from the billing_state column.

NOTE: The three dots (...) indicate where to add the clause.

What billing state appears in row 17 of your query result?

NOTE: The query index starts at 1 not 0.

You are working with a database table that contains customer data. The table includes columns about customer location such as city, state, country, and postal_code. You want to check for city names that are greater than 9 characters long.

You write the SQL query below. Add a LENGTH function that will return any city names that are greater than 9 characters long.

What is the first name of the customer that is in row 7 of your query result?

NOTE: The query index starts at 1 not 0.

Diego
Kara
Julia
Roberto

In SQL databases, what data type refers to a number that contains a decimal?

Boolean
Float
Integer
String

You’re working with a dataset that contains a float column with a significant amount of decimal places. This level of granularity is not needed for your current analysis. How can you convert the data in the float column to be integer data?

CAST
COALESCE
TRIM
CONCAT

What SQL function lets you add strings together to create new text strings that can be used as unique keys?

CAST
COALESCE
TRIM
CONCAT

What are some of the benefits of using SQL for analysis? Select all that apply.

SQL interacts with database programs.
SQL has better user management than spreadsheets.
SQL can pull information from different database sources.
SQL tracks changes across a team.

A data analyst is managing a database of customer information for a retail store. What SQL command can the analyst use to add a new customer to the database?

UPDATE
CREATE TABLE IF NOT EXISTS
DROP TABLE IF EXISTS
INSERT INTO

In SQL databases, True/False values refers to what data type?

String
Float
Integer
Boolean

A data analyst is tasked with identifying what orders are still in transit. The current list of orders contains trillions of rows. What is the best tool for the analyst to use?

Spreadsheets
CSV
SQL
Word processor

Your manager tasks you with analyzing a dataset and visually inspecting the data. Upon initial inspection you realize that this is a small dataset. What tool should you use to analyze the data?

CSV
Spreadsheet
SQL
Word processor

A data analyst creates a database to store information on the company's customer data. When completing the initial import the analyst notices that they forgot to add a few customers into the table. What command can the analyst use to add these missed customers?

ADD
APPEND
INSERT INTO
DROP

You are working with a database table that contains customer data. The table includes columns about customer location such as city, state, country, and postal_code. You want to find what state names are greater than 3 characters.

You write the SQL query below. Add a LENGTH function that will return any state names that are greater than 3 characters long.

What state is in row 1 of your query result?

NOTE: The query index starts at 1 not 0.

India
Chile
Dublin
Ireland

In SQL databases, what function can be used to convert data from one datatype to another?

CAST
LENGTH
TRIM
SUBSTR

After a company merger, a data analyst receives a dataset with billions of rows of data. They need to leverage this data to identify insights for upper management. What tool would be most efficient for the analyst to use?

Spreadsheet
Word processor
SQL
CSV

You are working with a database table that contains customer data. The table includes columns about customer location such as city, state, country, and postal_code. The state names are abbreviated. You want to check for state names that are greater than 2 characters long.

You write the SQL query below. Add a LENGTH function that will return any state names that are greater than 2 characters long.

What country is in row 1 of your query result?

NOTE: The query index starts at 1 not 0.

Ireland
India
France
Chile

You are working with a database table that contains employee data. The table includes columns about employee location such as city, state, country, and postal_code. You use the SUBSTR function to retrieve the first 3 characters of each last_name, and use the AS command to store the result in a new column called new_last_name.

You write the SQL query below. Add a statement to your SQL query that will retrieve the first 3 characters of each last_name and store the result in a new column as new_last_name.

NOTE: The three dots (...) indicate where to add the statement.

NOTE: SUBSTR takes in three arguments being column, starting_index, ending_index

What employee ID number is in row 8 of your query result?

NOTE: The query index starts at 1 not 0.

Week 4 – Verify and report on your cleaning results

What is involved in seeing the big picture when verifying data cleaning? Select all that apply.

Consider the reporting

Consider the data

Consider the business problem

Consider the goal

Fill in the blank: A data analyst uses the CASE statement to consider one or more _____, then return a value.

identifications

conditions

changes
fields

A data analyst uses a changelog to record how the data evolves while cleaning their data. What data cleaning best practice does this describe?

Examination
- Disclosure
- Illumination

Documentation

Verification and reporting come directly before the data-cleaning process.

True
False

What is the first step in the verification process?

Compare cleaned data with the original, uncleaned dataset and compare it to what is there now
Create a chronological list of modifications made to the data
Determine the quality of the data
Inform others of your data-cleaning effort

Which of the following functions automatically remove extra spaces when cleaning data?

SNIP
REMOVE
TRIM
CLEAR

What tool can a data analyst use to figure out how many identical errors occur in a dataset?

CASE
COUNTA
CONFIRM
COUNT

Fill in the blank: A data analyst uses the CASE statement to consider one or more _____, then returns a value.

additions
conditions
identifications
changes

What is the process of tracking changes, additions, deletions, and errors during data cleaning?

Recording
Observation
Cataloging
Documentation

Fill in the blank: While cleaning data, a data analyst can use a changelog to keep a chronological list of changes they make. They can refer to it during the _____ period if there are errors or questions.

presenting
verification
documentation
visualization

Reviewing version history is an effective way to view a changelog in SQL.

True
False

Shuffle Q/A

In what step of the data-cleaning process do you find mistakes before you begin analyzing the data?

Confirming
Publishing
Verifying
Processing

During the data cleaning process you find a significant amount of data that contains irrelevant spaces. Which function do you use to remove leading, trailing, or repeated spaces?

CUT
DELETE
TRIM
TIDY

A data analyst is checking for errors in a dataset. They want to determine how many times the name of a country is in the dataset using a pivot table. What function can they use to find this count?

COUNTA
CHECK
COUNT
CASE

You’re writing the below SQL query and need to change “World Wide Web” to “www”. What function would you use to accomplish this task?

SELECT

_____

WHEN ‘World Wide Web’ THEN ‘www’

END AS some_column

FROM

some_table

THEN
CASE
ELSE
WHEN

What should a data analyst actively track throughout the data cleaning process?

Additions, changes, and queries
Errors, deletions, and notes
Changes, resolutions, and deletions
Errors, additions, and deletions

A data analyst is in the verification process and needs to verify the modifications that they have made to the data. What could the analyst reference to find the changes they made throughout data cleaning?

Changelog
Notepad
Spreadsheet
Metadata

A data analyst commits a query to the repository as a new and improved query. Then, they specify the changes they made and why they made them. This scenario is part of what process?

Reporting data
Visualizing data
Communicating with stakeholders
Creating a changelog

The data collected for an analysis project has just been cleaned. What are the next steps for a data analyst? Select all that apply.

Reporting
Certification
Validation
Verification

As a data analyst, you will need to keep the big picture in mind throughout any project when verifying data cleaning. What must the analyst do to take a big picture view of the project? Select all that apply.

Consider the data
Consider the goal
Consider the business problem
Consider the reporting

During the verification process, you find that you missed a few leading spaces during data cleaning. What function can you use to eliminate these spaces?

TRIM
TIDY
CUT
CROP

Which SQL tool considers one or more conditions, then returns a value as soon as a condition is met?

THEN
WHEN
CASE
ELSE

Fill in the blank: Documentation is the process of tracking _____ during data cleaning. Select all that apply.

additions
deletions
changes
inactivity

Fill in the blank: A changelog contains a _____ list of modifications made to a project.

random
approximate
chronological
synchronized

You start a complex project that will take more than a year to complete. You need to document modifications made to your queries throughout the project. What is the correct way to store these modifications?

Creating a changelog
Creating a notepad
Visualizing data
Creating a spreadsheet

Fill in the blank: A process to confirm that a data-cleaning effort was well-executed and the resulting data is accurate and reliable is known as _____.

verification
publishing
manipulation
processing

A data analyst is in the verification step. They consider the business problem, the goal, and the data involved in their analytics project. What scenario does this describe?

Reporting on the data
Considering the stakeholders
Seeing the big picture
Visualizing the data

During data cleaning, you find an error in a username where the ID number was accidentally joined to the user’s last name. You need to figure out if this username has been entered incorrectly more than once in your datasett. If you use a pivot table, what function can you use to determine the number of times this error occurs in your dataset?

CASE
COUNT
COUNTA
CHECK

You’re working with a dataset that contains categorical variables. You notice that some of the strings are misspelled or are not capitalized. What function can you use to fix these errors when a condition is met?

ELSE
CASE
WHEN
THEN

A data analyst uses a changelog while cleaning data. What process does a changelog support?

Illumination
Examination
Disclosure
Documentation

A changelog is essential for storing chronological modifications made during the data cleaning process. When will an analyst refer to the information in the changelog to certify data integrity?

Documentation
Verification
Presenting
Visualization

Fill in the blank: As a data analyst, you should always create a _____ to track your additions, deletions, errors, and changes to a query.

notepad
database
changelog
spreadsheet

Fill in the blank: TRIM is a function that removes _____ spaces in data. Select all that apply.

repeated
trailing
leading
inner

While verifying cleaned data, a data analyst encounters a misspelled name. Which function can they use to determine the number of misspelled occurrences in the dataset?

CASE
CHECK
CHECK
COUNTA

At what point during the analysis process does a data analyst use a changelog?

While cleaning the data
While visualizing the data
While gathering the data
While reporting the data

Your manager points out an error in a product ID number in your dataset. The Product IDs can be numbers like 42 or text like "CAD-425". Using a pivot table, what function can you use to find how many times this error occurs in the dataset?

COUNT
CHECK
COUNTA
CASE

While reviewing your coworker’s data cleaning process, you find a few cases of trailing spaces in the data. What function can you use to remove these spaces?

REMOVE TRAILING
DELETE
CUT
TRIM

Which of the following queries considers one or more conditions and returns a value as soon as that condition is met?

SELECT * WHEN CASE COLUMN = VARIABLE
SELECT * CASE IF COLUMN = VARIABLE
SELECT * CASE WHEN COLUMN = VARIABLE
SELECT * IF CASE COLUMN = VARIABLE

Course challenge

Scenario 2, questions 6-10

You’ve completed this program and are interviewing for a junior data scientist position. The job is at B.Spoke Market Research, a company that analyzes market conditions using customer surveys and other research methods. The detailed job description can be found below:

C4 S2 Email from Recruiter.pdf

So far, you’ve had a phone interview with a recruiter and you’ve secured a second interview with the B.Spoke team. The recruiter’s email can be found below: