Prepare for the course challenge by reviewing terms and definitions in the glossary. Then, demonstrate your knowledge of the importance of sample size, data integrity, and the connection of data to business objectives during the quiz. You will also have an opportunity to apply your skill with data cleaning techniques in both spreadsheets and SQL. Finally, document, report on, and verify your data-cleaning process and results.

Learning Objectives

• Describe statistical measures associated with data integrity including statistical power, hypothesis testing, and margin of error
• Describe strategies that can be used to address insufficient data
• Discuss the importance of sample size with reference to sample bias and random samples
• Describe the relationship between data and related business objectives
• Define data integrity with reference to types and risks
• Describe data cleaning techniques with reference to identifying errors, redundancy, compatibility and continuous monitoring
• Demonstrate an understanding of the use of spreadsheets to clean data
• Describe how SQL can be used to clean large datasets
• Describe the benefits of documenting data cleaning process
• Discuss the elements and importance of data-cleaning reports
• Describe the process involved in verifying the results of cleaning data

Glossary: Terms and definitions

We’ve covered a lot of terms—some of which you may have already known, and some of which are new. To make it easy to remember what a word means, we created this glossary of terms and definitions.

To use the glossary for this course item, click the link below and select “Use Template.”

Link to glossary: Week 6 Glossary

OR

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

Course 4 Week 6 Glossary _ DA terms and definitions

Course challenge

Question 1

Scenario 1, question 1-5

You are a data analyst at a small analytics company. Your company is hosting a project kick-off meeting with a new client, Meer-Kitty Interior Design. The agenda includes reviewing their goals for the year, answering any questions, and discussing their available data.

Before the meeting you review the About Us tab on their website and their business plan, linked below:

Meer-Kitty Interior Design About Us Page.pdf

Meer-Kitty Interior Design Business Plan.pdf

Meer-Kitty Interior Design has two goals. They want to expand their online audience, which means getting their company and brand known by as many people as possible. They also want to launch a line of high-quality indoor paint to be sold in-store and online. You decide to consider the data about indoor paint first.

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

Link to template: Kitty Survey Feedback

OR

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

Kitty Survey Feedback - Meer-Kitty survey feedback

When you refer to the Meer-Kitty survey feedback tab, you are pleased to find that the available data is aligned to the business objective. However, you do some research about confidence level for this type of survey and learn that you need at least 120 unique responses for the survey results to be useful. Therefore, the dataset has two limitations: First, there are only 40 responses; second, a Meer-Kitty superfan, User 588, completed the survey 11 times.

As the survey has too few responses and numerous duplicates that are skewing results, what are your options? Select all that apply.

• Repeat the survey in order to create a new, improved dataset.
• Remove the duplicates from the data and proceed with analysis.
• Locate another dataset about indoor paint.
• Talk with stakeholders and ask for more time.

Explain: With numerous duplicates, the best option is to talk with stakeholders and ask for more time. Then, you can repeat the survey in order to create a new, improved dataset.

Question 2

Scenario 1 continued

During the meeting, you also learn that Meer-Kitty videos are hosted on their website. For each product offered, there is an accompanying video for customers to learn more. So, more views for a video suggests greater consumer interest.

Your goal is to identify which videos are most popular, so Meer-Kitty knows what topics to explore in the future. Unfortunately, Meer-Kitty has just three months of data available because they only recently launched the videos on their site.

Without enough data to identify long-term trends about the video subjects that people prefer, what are your available options? Select all that apply.

• Move ahead with the data you have to determine the top video subjects.
• Talk with Meer-Kitty stakeholders and ask to adjust the objective.
• Watch the videos and use your gut instinct to identify which are most successful.
• Ask to wait for more data and provide Meer-Kitty with an updated timeline.

Explain: Without enough data to identify long-term trends, one option is to talk with stakeholders and ask to adjust the objective. You could also ask to wait for more data and provide an updated timeline.

Question 3

Scenario 1 continued

Now that you’ve identified some limitations with Meer-Kitty’s data, you want to communicate your concerns to stakeholders. In addition to insufficient video trend data, your main concern with the indoor paint survey is that the data isn’t representative of the population as a whole.

Clearly, one particular respondent, the superfan, is overrepresented. What does this situation describe?

A. Confidence level

B. Margin of error

C. Sampling bias

D. Statistical significance

The correct answer is C. Sampling bias. Explain: This situation describes sampling bias. Sampling bias occurs when a sample isn’t representative of the population as a whole.

Question 4

Scenario 1 continued

The stakeholders understand your concerns and agree to repeat the indoor paint survey. In a few weeks, you have a much better dataset with more than 150 responses and no duplicates.

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

Link to template: Kitty Survey Feedback

OR

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

Kitty Survey Feedback - New Meer-Kitty survey feedback

If you are using the template, please refer to the New Meer-Kitty survey feedback tab located at the bottom of the page. You notice that questions 4 and 5 are dependent on the respondent’s answer to question 3. So, you need to determine how many people answered Yes to question 3, then compare that to responses to questions 4 and 5. That way, you will know if questions 4 and 5 have any nulls.

You decide to use a spreadsheet tool that changes how cells appear when they contain the word Yes. When using this tool, what is the word Yes?

A. The value in a conditional formatting rule

B. The value in a CONCATENATE range

C. The value in a VLOOKUP statement

D. The value in the COUNTA range

The correct answer is A. The value in a conditional formatting rule. Explain: To change how cells appear when they meet a certain value, use conditional formatting.

Question 5

Scenario 1, continued

You have finished cleaning the data to ensure it is complete, correct, and relevant to the problem you’re trying to solve. Then, you complete the verification and reporting processes to share the details of your data-cleaning effort with your team.

Your team notes one aspect of data cleaning that would help improve the dataset. They point out that the new survey also has a new question in Column G: “What are your favorite indoor paint colors?” This was a free-response question, so respondents typed in their answers. Some people included multiple different colors of paint. In order to determine which colors are most popular, it will be necessary to put each color in its own cell.

You use a spreadsheet function to divide the text strings in Column G around the commas and put each fragment into a new, separate cell. In this example, what are the commas called?

A. Delimiters

B. Partitions

C. Substrings

D. MIDs

The correct answer is A. Delimiters. Explain: The commas are delimiters, which are characters that indicate the beginning or end of a data item.

Question 6

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 B.Spoke Market Research Job Description.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:

C4 S2 Email from Recruiter.pdf

You arrive 15 minutes early for your interview. Soon, you are escorted into a conference room, where you meet Jodie Choi, the data science lead. After welcoming you, the behavioral interview begins.

For your first question, your interviewer wants to learn about your experience with spreadsheets. She says: Sometimes the team needs data that is stored in different spreadsheets. So, we use a spreadsheet function to find the information we need.

There is a spreadsheet function that searches for a value in the first column of a given range and returns the value of a specified cell in the row in which it is found. It is called SEARCH.

A. True

B. False

It is false statement. Explain: The VLOOKUP function searches for a certain value in a column to return a corresponding piece of information.

Question 7

Scenario 2, continued

Next, your interviewer wants to know more about your understanding of tools that work in both spreadsheets and SQL. She explains that the data her team receives from customer surveys sometimes has many duplicate entries.

She says: Spreadsheets have a great tool for that called remove duplicates. In SQL, you can include DISTINCT to do the same thing. In which part of the SQL statement do you include DISTINCT?

A. The UPDATE statement

B. The FROM statement

C. The SELECT statement

D. The WHERE statement

The correct answer is C. The SELECT statement. Explain: To remove duplicates in SQL, include DISTINCT in your SELECT statement.

Question 8

Scenario 2, continued

Now, your interviewer explains that the data team usually works with very large amounts of customer survey data. After receiving the data, they import it into a SQL table. But sometimes, the new dataset imports incorrectly and they need to change the format.

She asks: What function would you use to convert data in a SQL table from one datatype to another?

A. CHANGE

B. CAST

C. COALESCE

D. CONVERSE

The correct answer is B. CAST. Explain: The CAST function is used to convert data in a SQL table from one datatype to another.

Question 9

Scenario 2, continued

Next, your interviewer explains that one of their clients is an online retailer that has a vast inventory. She has a list of items by name, color, and size. Then, she has another list of the price of each item by size, as a larger item sometimes costs more. The client needs one list of all items by name, color, size, and price.

She then asks: If you were to use the CONCAT function to complete this task, what would it enable you to do?

A. Create a unique key to tell products apart

B. Search for and return missing products in inventory

C. Clean the product identifier text strings

D. Create a new product database table

The correct answer is A. Create a unique key to tell products apart. Explain: Using the CONCAT function to combine each string into a single text string would enable you to create a unique key. You can use the key to tell products apart and count them more easily.

Question 10

Scenario 2, continued

For your final question, your interviewer explains that her team often comes across data with extra leading or trailing spaces.

She asks: Which SQL function enables you to eliminate those extra spaces for consistency?

A. SUBSTR

B. TRIM

C. LEN

D. LENGTH

The correct answer is B. TRIM. Explain: To eliminate extra spaces for consistency, use the TRIM function.