Connected Sheets: Qwik Start - GSP870

Overview

Connected Sheets brings you the power and scale of a BigQuery data warehouse to the familiar context of Google Sheets. With Connected Sheets, you can analyze billions of rows and petabytes of data in Sheets without specialized knowledge of computer languages like SQL.

This makes it easy for anyone, not just data analysts, to apply pivot tables, charts, and formulas to massive datasets and hone in on their most valuable customers or product lines; develop forecasting models, uncover trends, and perform ad hoc analysis with ease.

What you will do

In this lab, you:

• Connect a BigQuery dataset to Google Sheets.

• Use Formulas to find the percentage of taxi trips that included a tip.

• Use Charts to inspect popularity and trends of payment types.

• Use Pivot tables to find out when taxi rides are the most expensive.

• Use Extracts to import raw data from BigQuery to Connected Sheets.

• Use Calculated columns to create a new column from transformations/combinations of existing columns.

• Use Scheduled refresh to set up automatic data refreshes for your analyses.

Setup and requirements

Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources will be made available to you.

This hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that you use to sign in and access Google Cloud for the duration of the lab.

To complete this lab, you need:

• Access to a standard internet browser (Chrome browser recommended).

Note: Use an Incognito or private browser window to run this lab. This prevents any conflicts between your personal account and the Student account, which may cause extra charges incurred to your personal account.

• Time to complete the lab---remember, once you start, you cannot pause a lab.

Note: If you already have your own personal Google Cloud account or project, do not use it for this lab to avoid extra charges to your account.

Task 1. Open Google Sheets

1. Click the Start Lab button. On the left is a panel populated with the temporary credentials that you must use for this lab.

2. In a new incognito window, open the Google Sheets home page.

3. In the Google Sign in page, paste the username from the Connection Details panel, then copy and paste the password.

Note: If you see the Choose an account page, click Use another account.

Note: You must use the credentials from the Connection Details panel. Do not use your Qwiklabs credentials. If you have your own Google Cloud account, do not use it for this lab (avoids incurring charges).

4. Click through the subsequent pages:

• Accept the terms and conditions.

• Do not add recovery options or two-factor authentication (because this is a temporary account).

• Do not sign up for free trials.

After a few moments, you will be at the Google Sheets home page.

5. In your Google Sheets tab, click on the Blank button under Start a New Spreadsheet to create a new sheet. You will connect this sheet to a BigQuery dataset in the following steps.

Task 2. Connect to a BigQuery dataset

The data you will be analyzing will be taxi trips in Chicago. Start by connecting the public Chicago taxi dataset that's available in BigQuery to Google Sheets.

1. Select Data > Data Connectors > Connect to BigQuery.

2. If you see a Connect and Analyze big data in Sheets pop-up, click Get Connected.

3. Select YOUR PROJECT ID > Public datasets > chicago_taxi_trips.

4. Select taxi_trips and click Connect.

After about a minute, you should see a success message. You have just connected a BigQuery dataset to Google Sheets!

Task 3. Formulas

Next you will look at how to use Formulas with Connected Sheets. First, find out how many taxi companies there are in Chicago.

1. Select Function > COUNTUNIQUE and add it to a new sheet.

2. Ensure New Sheet is selected and click Create to add it to a new sheet.

3. Specify the company column by changing the value of your cell at row 1, column A to this:

=COUNTUNIQUE(taxi_trips!company)

Copied!content_copy

4. Click Apply.

Looks like there are 164 taxi companies in Chicago (your results may vary depending on the date the data is accessed).

Next, find the percentage of taxi rides in Chicago that included a tip.

5. Using the COUNTIF function, find the total number of trips that included a tip. Copy and paste this function into the cell at row1, column D:

=COUNTIF(taxi_trips!tips,">0")

Copied!content_copy

6. Click Apply.

7. Now, use the COUNTIF function to find the total number of trips where the fare was greater than 0. Add this function into the cell at row1, column E:

=COUNTIF(taxi_trips!fare,">0")

Copied!content_copy

8. Click Apply.

Click Check my progress to verify the objective.

Use Formulas in Connected sheet

Check my progress

9. Finally, compare the values from the previous two steps. Add this function into the cell at row1, column F:

=D1/E1

Copied!content_copy

Turns out around 38.6% of taxi trips in Chicago included a tip (your results may vary depending on the date the data is accessed).

Task 4. Charts

What forms of payments are people using for their taxi rides? How has revenue from mobile payments changed over time?

Try viewing this information with Charts.

1. Return to the taxi_trips tab by clicking on it at the bottom of your Google Sheets page.

2. Click on the Chart button. Ensure New Sheet is selected and click Create.

3. In the Chart editor window, under Chart type, select Pie chart.

4. Various columns of the data are listed to the right. Drag payment_type to the Label field. Then drag fare into the Value field and click Apply.

5. The value of Cash payments slightly edges out the value of Credit Card payments.

6. Under Value > Fare, change Sum to Count. Click Apply.

7. Now, the Cash transactions significantly outnumber Credit Card transactions, implying that the latter has a higher average value.

Next, find out how mobile payments have changed over time by using a line chart.

8. Return to the taxi_trips tab by selecting it at the bottom of your Google Sheets page.

9. Select the Chart button. Ensure New Sheet is selected and click Create.

10. Click on the Chart Type dropdown and select the first option under Line.

11. Drag trip_start_timestamp to the X-axis field.

12. Check the Group by option and select Year-Month from the dropdown list.

13. Drag fare into the Series field.

14. Click Apply.

The overall revenue peaked in 2015. But, how have mobile payments changed over time?

15. Under Filter click Add > payment_type.

16. Select the Showing all items status dropdown.

17. Click on the Filter by Condition dropdown and select Text contains from the list.

18. Input mobile in the Value field.

19. Click OK.

20. Click Apply to generate a new line chart.

From the chart, you should see that mobile payments have been on a general upward trend. What other observations can you make from the graph?

Click Check my progress to verify the objective.

Use Charts

Check my progress

Task 5. Pivot tables

At which time of day are there the highest amount of taxi rides? In the following section, you will analyze this using pivot tables.

1. Return to the taxi_trips tab by selecting it at the bottom of your Google Sheets page.

2. Click on the Pivot table button.

3. Ensure New sheet is selected and click Create.

4. Drag trip_start_timestamp into the Rows field.

5. Choose Hour for the Group By option.

6. Drag fare into the Values field.

7. Select COUNTA for the Summarize by option.

8. Click Apply.

You should now see a table that lists the number of rides per hour of the day (military time).

At which time of day do you see the most taxi rides?

Next, break it down by day of the week.

9. Drag trip_start_timestamp to the Columns field.

10. Select Day of the week under the Group by option.

11. Click Apply.

12. Select the data range B3:H26 and select Format > Number > Number.

13. Click on the decrease decimal place button twice to make the data easier to read.

Now, apply some conditional formatting.

14. Select all your data cells by clicking on the top left cell (first value for Sunday) and then shift + clicking on the bottom right cell (last value for Saturday).

15. With all your cells selected, click Format > Conditional formatting.

16. Select Color scale.

17. Select the colors under Preview and choose White to Green. This will make the high values green and the low values white.

18. Click Done.

19. Close the Conditional Formatting window by clicking the x.

20. Now, observe that peak periods on weekends are in the early morning and peak periods on weekdays are around the start and end of typical office hours.

What about the most expensive times for taxis?

21. In the Values field, change the Summarize by option to Average.

22. Click Apply.

Turns out that Monday early morning taxi fares are the most expensive!

Feel free to explore other combinations of data using pivot table and see what other insights you can uncover!

Click Check my progress to verify the objective.

Use Pivot tables

Check my progress

Task 6. Using Extract

Sometimes, you may find that you are only working with a small subset of the dataset. You may also want to take a closer look at the raw data itself. In such cases you might find it easier to import a subset of the dataset from BigQuery into Connected Sheets.

By default, Connected Sheets shows a preview of 500 rows of raw data in BigQuery. To import more data into Connected Sheets, you can use Extract.

For this example you will extract 25000 rows of data from the columns trip_start_timestamp, fare, tips, and tolls, ordered by latest trip first.

1. Return to the taxi_trips tab by selecting it at the bottom of your Google Sheets page.

2. Click on the Extract button.

3. Ensure New sheet is selected and click Create.

4. In the Extract editor window, click Edit under the Columns section and select the columns trip_start_timestamp, fare, tips, and tolls. Click outside the dropdown box to continue.

5. Click Add under the Sort section and select trip_start_timestamp. Click on Desc to toggle between ascending and descending order.

6. Under Row limit, leave 25000 as it is to import 25000 rows.

7. Click Apply.

That's it! You have just extracted thousands of rows of raw data from BigQuery into Connected Sheets!

Click Check my progress to verify the objective.

Using Extract

Check my progress

Task 7. Calculated columns

Calculated columns allows you to add new columns that are transformations or combinations of existing columns. You will create a calculated column that calculates tip percentage.

1. Return to the taxi_trips tab by selecting it at the bottom of your Google Sheets page.

2. Click on the Calculated columns button.

On the right you can see the columns of your dataset and the functions available. You can also click on the question mark in the description to see examples of calculated columns.

3. Enter tip_percentage into the Calculated column name field.

4. Then copy and paste the following formula into the formula field:

=IF(fare>0,tips/fare*100,0)

Copied!content_copy

5. Click Add.

6. Click Apply.

Now you can see the percentage of the fare that was tipped under the tip_percentage column.

Click Check my progress to verify the objective.

Calculated columns

Check my progress

Task 8. Refresh all / Scheduled refresh

By default, all the analyses that you do on Connected Sheets remain unchanged until you decide to refresh it. This means that even if the data in BigQuery changes, your charts and tables will not change unexpectedly.

Now you'll see how you can update your analyses to the latest data, or schedule regular updates.

1. To update a chart or table, select it then click the Refresh button.

You can also click the Refresh options button, located beside the name of your dataset, followed by Refresh all to update all Connected Sheets analyzes to the latest data.

3. To schedule a refresh, click on Schedule refresh near the bottom of the Refresh options sidebar.

4. Finally, choose your desired frequency and time for the automatic data refreshes.

5. Click Save.

---

Solution of Lab

Task 1. In a new incognito window, open the Google Sheets home page

In your Google Sheets tab, click on the Blank button under Start a New Spreadsheet to create a new sheet. You will connect this sheet to a BigQuery dataset in the following steps.

Task 2. Connect to a BigQuery dataset

1. Select Data > Data Connectors > Connect to BigQuery

2. Select YOUR PROJECT ID > Public datasets > chicago_taxi_trips > taxi_trips and click Connect

Task 3. Formulas

1. Select Function > COUNTUNIQUE and add it to a new sheet

2. row 1, column A to this:

=COUNTUNIQUE(taxi_trips!company)

3. Click Apply

4. row1, column D

=COUNTIF(taxi_trips!tips,">0")

4. Click Apply

5. row1, column E:

=COUNTIF(taxi_trips!fare,">0")

6. Click Apply

7. row1, column F

=D1/E1

Task 4. Charts

1. Return to the taxi_trips tab Click Chart button. Ensure New Sheet > Create

2. Under Chart type, select Pie chart

3. Label field > payment_type Then Value field > fare

4. Under Value > Fare, change Sum to Count Click Apply

---

1. Return to the taxi_trips tab Click Chart button. Ensure New Sheet > Create

2. Click on the Chart Type select Line

3. X-axis field > trip_start_timestamp then Group > Year-Month and Series field > fare

4. Under Filter click Add > payment_type and Select the Showing all items

5. Click on the Filter by Condition select Text contains from the list

6. In the Value field type mobile click ok then Apply

Task 5. Pivot tables

1. Return to the taxi_trips tab Click Pivot table button. Ensure New Sheet > Create

2. In the Rows field > trip_start_timestamp and Hour for the Group By option

3. Values field > fare and Select COUNTA for the Summarize by option

4. Columns field > trip_start_timestamp and Select Day of the week under the Group by option

5. Click Apply

---

1. select Format > Number > Number select all (first value for Sunday) to (last value for Saturday)

2. click Format > Conditional formatting Select Color scale under Preview and choose White to Green Done

Task 6. Using Extract

1. Return to the taxi_trips tab Click Extract button. Ensure New Sheet > Create

2. In the Extract editor window, click Edit under the Columns section and select the columns trip_start_timestamp, fare, tips, and tolls

3. Click Add under the Sort > select trip_start_timestamp. Click on Desc

4. Under Row limit, leave 25000 as it is to import 25000 rows then Click Apply

Task 7. Calculated columns

1. Return to the taxi_trips tab and Click on the Calculated columns button

2. Calculated column name > tip_percentage

3. Copy and paste the following formula into the formula field:

=IF(fare>0,tips/fare*100,0)

4. Click Add and Click Apply