Overview

This is a Challenge lab where you must complete a series of tasks within a limited time period. Instead of following step-by-step instructions, you are presented with general objectives. An automated scoring system (shown on this page) will provide feedback on whether you have completed each task correctly.

You must complete the tasks within the time period to score 100% on the challenge.

This lab does not teach GCP concepts. Instead, it is a test of your Data Engineering skills. This lab is only recommended for students who have BigQuery skills.

Topics tested

• Pulling a data table from BigQuery into Google Sheets using BigQuery Connected Sheets.

• Extracting a subset of that data and preparing it for data analysis.

• Performing a data analysis process on the extracted data to derive an answer to a data-related question.

• Create a basic visualization in the Google Sheets workbook.

• Share the entire workbook with others.

Setup

For each lab, you get a new Google Cloud project and set of resources for a fixed time at no cost.

1. Sign in to Qwiklabs using an incognito window.

2. Note the lab's access time (for example, 1:15:00), and make sure you can finish within that time.
   There is no pause feature. You can restart if needed, but you have to start at the beginning.

3. When ready, click Start lab.

4. Note your lab credentials (Username and Password). You will use them to sign in to the Google Cloud Console.

5. Click Open Google Console.

6. Click Use another account and copy/paste credentials for this lab into the prompts.
   If you use other credentials, you'll receive errors or incur charges.

7. Accept the terms and skip the recovery resource page.

Your Challenge: Scenario

You work as a marketing strategist at Starcell Solar Technologies.

Background

A newly-ratified United States federal solar energy grant is now available to homeowners with homes built before 1960 and annual incomes below 60,000 USD.

Starcell and other solar energy companies aim to reach and engage with qualified homeowners about the grant.

Challenge

Your product marketing director has tasked you with identifying the top 100 US counties with the highest number of homeowners who could benefit from this grant, so that Starcell can use this information to plan where to geographically focus its marketing campaigns about the grant.

The data you need to access (from the 2018 US Census Community Survey) is available in BigQuery, but since you do not know how to work with data in BigQuery directly, you will use the BigQuery Connected Sheets feature available in Google Sheets to pull that data into Sheets to work with it there for the data analysis.

Task 1. Open a new Google Sheet.

In this first task, you log into Google Workspace in this lab environment using the provided credentials and then open a new Google Sheet.

1. To open Sheets, right-click this provided link for Open Google Sheets, and select the option to open the link in a new incognito window.

2. To sign into Google Workspace, use the credentials (username and password) provided on the current lab page.

Be sure to:

• Accept the terms and conditions.
• Do not add recovery options or two-factor authentication (because this is a temporary account).
• Exit the Welcome to Google Sheets window.

Note: Be sure to log into Google Workspace using the provided lab credentials. If you use your personal Google Cloud account, you may incur charges when connecting to BigQuery and other Google Cloud resources.

3. On the Sheets main page, click the + (plus sign) for Blank spreadsheet.

Proceed to the next task.

Task 2. Use BigQuery Connected Sheets to pull a data table into Google Sheets

Before you can answer the product marketing director's data question, you must first get access to the data you need to analyze. You must:

• Open the Qwiklabs lab environment version of Google Sheets.

• Use BigQuery Connected Sheets to access the censustract2018_5yr_top10000_housingunits data table, contained within the project name, and public_sector dataset.

Proceed to the next task.

Click Check my progress to verify the objective. Pull a data table into Google Sheets using BigQuery Connected Sheets

Task 3. Extracting and filtering the most useful data to prepare for the data analysis

Now that you've accessed the 2018 US Census Survey data and pulled it into Google Sheets, you must extract only the four most relevant data columns necessary for answering the solar-grant related data question (The top 100 US counties by occupied homeowners).

Additionally, as you remember, this new grant is specifically targeting homeowners with homes built in 1960 or earlier, and with household incomes of $60,000 USD or less, so you will need to add two filter criteria using the Extract editor as well. You must:

Note: In the 2018 US Census survey data, geographic location is catalogued using geographic identifiers, or GEOIDs. For more information on GEOIDs, review this US Census Bureau article about them.

• Create a data extract using the BigQuery Connected Sheets interface

• Use the Extract editor to select the four most useful data columns needed for the data analysis

• Use the Extract editor to select and configure the two most useful filter criteria

Proceed to the next task.

Click Check my progress to verify the objective. Extract and filter the most useful data

Task 4. Performing the data analysis

At this point, you have performed all of the prep work in order to perform the actual data analysis. To perform the data analysis, you must:

• Select and configure sort criteria using the Extract editor to show a descending display of owner_occupied_housing_units.

• Define a LEFT function in a column you create called geo_id_county to separate out the first five numbers from the top 100 geo_ids.

• Create a new sheet in the workbook and reference both the owner_occupied_housing_units and geo_id_county data there.

Proceed to the final task.

Click Check my progress to verify the objective. Perform the data analysis

Task 5. Creating a visualization and sharing the workbook with others

You have compiled an answer to the product marketing director's data-related question about the new solar grant. Great work! Only two tasks remain. You must:

• Create a basic column chart using the reference data from the previous task.

• Configure the sharing criteria in order to share the whole workbook with the product marketing director for editor-level access.

Note: When you share a workbook in the real world, you would click the Send button to share it with others, but do not share the document in this lab environment. Just configure the sharing parameters and then stop.

Click Check my progress to verify the objective. Create a visualization and share the workbook with others

Congratulations!

You have successfully used Google Sheets and BigQuery Connected Sheets to analyze and evaluate data to answer a data-related question.

Manual Last Updated September 23, 2024

Lab Last Tested September 23, 2024

End your lab

When you have completed your lab, click End Lab. Qwiklabs removes the resources you’ve used and cleans the account for you.

You will be given an opportunity to rate the lab experience. Select the applicable number of stars, type a comment, and then click Submit.

The number of stars indicates the following:

• 1 star = Very dissatisfied
• 2 stars = Dissatisfied
• 3 stars = Neutral
• 4 stars = Satisfied
• 5 stars = Very satisfied

You can close the dialog box if you don't want to provide feedback.

For feedback, suggestions, or corrections, please use the Support tab.

Copyright 2022 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.