Overview

Looker is a modern data platform in Google Cloud that lets you analyze and visualize your data interactively. You can use Looker to do in-depth data analysis, integrate insights across different data sources, build actionable data-driven workflows, and create custom data applications.

In this lab, you learn how to use Looker to analyze and visualize data using Explores, which are data views created by LookML developers. For this lab, an Explore of a simulated ecommerce dataset has been created for you. This dataset contains information on different attributes of orders and website users, such as the order date, items ordered, and the user associated with each order.

You can learn more about Explores in the Looker documentation at Exploring data in Looker.

In Looker, table calculations provide the functionality to create new metrics instantaneously, including from other rows or columns in a data table by leveraging offsets. You can use table calculations and offsets to easily prototype a new metric or to easily and quickly answer one-off questions using available data. Throughout this lab, you will use table calculations and offsets to create new metrics from simulated ecommerce data.

Objectives

In this lab, you learn how to use table calculations and offsets to create new metrics from data in Looker Explores.

• Utilize table calculations to calculate new metrics instantaneously.
• Leverage offsets to calculate new metrics from other rows and columns in a data table.
• Select the appropriate visualization type to best display results.
• Save visualizations to dashboards.
• Open and modify an existing dashboard.

Setup and requirements

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

1. Make sure you signed into Qwiklabs using an incognito window.

2. Note the lab's access time (for example, 2:00:00 hrs) and make sure you can finish in that time block.

Note: There is no pause feature. You can restart the lab if needed, but you will start at the beginning of the lab.

3. When ready, click .

A new panel will appear with the temporary credentials that you must use for this lab.

If you need to pay for the lab, a pop-up will open for you to select your payment method.

4. Note your lab credentials. You will use them to sign in to the Looker instance for this lab.

Note: If you use other credentials, you will get errors or incur charges.

5. Click Open Looker.

Tip: Open Looker in a new tab or a separate window, so you can see both the lab instructions and the Looker instance.

6. Enter the provided username and password.

Important: You must use the credentials from the Connection Details panel on this page. Do not use your Qwiklabs credentials. If you have your own Looker account, do not use it for this lab.

7. Click Log In.

After a successful login, you will see the Looker instance for this lab.

Note: Do not click End Lab unless you are finished with the lab or want to restart it. This clears your work and removes the project.

When you start the lab, it can take a few minutes for the data to populate in the Looker instance. When you see options (e.g. Flights) in the Explore dropdown located on the Looker navigation menu, you may proceed to the first task.

Task 1. Create a pie chart of the percentage of orders by traffic source in the past year

Using the Order Items Explore, you calculate the percentage of orders by traffic source (out of the total number of orders) in the past year, and display the results as a pie chart. Then, you add this visualization to a new dashboard for orders in the past year.

Calculate percentage of orders by traffic source (out of the total number of orders) in the past year

1. On the left-side navigation panel of the Looker User Interface, click Explore.

2. Under E-Commerce Training, click Order Items.

3. Click the arrow next to Order Items.

The available dimensions and measures will be listed in the data panel for Order Items.

4. Under Order Items > Measures, click Order Count.

5. Under Order Items > Dimensions > Created Date, click the filter icon next to Date.

6. From the Filters dropdown list, select "is in the past" and type: 1.

7. Then, select complete years.

8. Under Users > Dimensions, click Traffic Source.

9. Click the checkbox next to Totals on the Data bar menu.

10. Click Run.

11. In the data panel above Distribution Centers, next to Custom Fields, click + Add.

12. Select Table Calculation.

13. Name the new table calculation: % of Total Order

14. Add the following syntax to create the new table calculation:

${order_items.order_count}/${order_items.order_count:total}

15. Select Percent(1) - 123,456.8% as the format.

16. Click Save.

Customize a pie chart to display the results

1. In the data window, click on the settings gear icon () next to Order Count, and select Hide from Visualization.

2. Click the arrow next to Visualization to expand the window.

Once the Visualization window has expanded, you can hover your cursor over the icons to identify the available options.

3. Click the Pie icon.

This option creates a pie chart visualization that you can customize. You can also make the chart larger by collapsing the data window.

4. Click on the settings gear icon for Visualization.

5. Under Plot, for Value Labels, select Labels.

6. Under Plot, for Label Type, select Label - Value.

7. Under Plot, for Start Angle, type: 90

8. Under Series, for Collection, select Vivid.

9. Click on the gear icon for Visualization to close the settings.

Save the visualization to a new dashboard

1. Click on the settings gear icon next to Run (top right of page), and select Save > To an existing Dashboard.

2. Enter a title for the visualization: Percent of Orders By Traffic Source

Leave the default destination as Shared.

3. Click New Dashboard.

4. Enter a title for the new dashboard: Orders in the Past Year

5. Click OK.

6. Click Save to Dashboard.

7. View the dashboard by clicking on the provided hyperlink titled Orders in the Past Year. When you have finished viewing, click Cancel.

The colors of your visualization may differ from those shown in the image.

Click Check my progress to verify the objective. Create a pie chart of the percentage of orders by traffic source in the past year.

Task 2. Create a map of the percentage of orders by US State in the past year

In this task, you calculate the percentage of orders by US State (out of the total number of orders) in the past year, and then display the results as a map. Then, you add this visualization to the dashboard you created in the previous task.

Calculate percentage of orders by US state (out of the total number of orders) in the past year

1. On the left-side navigation panel of the Looker User Interface, click Explore.

2. Under E-Commerce Training, click Order Items.

3. Click the arrow next to Order Items.

The available dimensions and measures will be listed in the data panel for Order Items.

4. Under Order Items > Measures, click Order Count.

5. Under Order Items > Dimensions > Created Date, click the filter icon next to Date.

6. From the Filters dropdown list, select "is in the past" and type: 1.

7. Then, select complete years.

8. Under Users > Dimensions, click the filter icon next to Country.

9. From the Filters dropdown list, select "is equal to" and type: USA

10. Under Users > Dimensions, click State.

11. Click the checkbox next to Totals on the Data bar menu.

12. Click Run.

13. In the data panel above Order Items, next to Custom Fields, click + Add.

14. Select Table Calculation.

15. Name the new table calculation: % of Total Order

16. Add the following syntax to create the new table calculation:

${order_items.order_count}/${order_items.order_count:total}

17. Select Percent(1) - 123,456.8% as the format.

18. Click Save.

Customize a map of the results

1. In the data window, click on the settings gear icon next to Order Count, and select Hide from Visualization.

2. Click the arrow next to Visualization to expand the window.

Once the Visualization window has expanded, you can hover your cursor over the icons to identify the available options.

3. Click the Map icon.

This option creates a map visualization that you can customize. You can also make the visualization larger by collapsing the data window.

4. Click on the settings gear icon for Visualization.

5. Under Value, enable the toggle bar for Reverse Color Scale.

6. Click on the gear icon for Visualization to close the settings.

Save the visualization to an existing dashboard

1. Click on the settings gear icon next to Run, and select Save > To an existing Dashboard.

2. Enter a title for the visualization: Percent of Orders By US State

3. Select the dashboard you previously created: Orders in the Past Year.

4. Click Save to Dashboard.

5. View the final dashboard by clicking on the provided hyperlink titled Orders in the Past Year.

The dashboard for Orders in the Past Year will contain the two visualizations that you added in the first two tasks.

Note: You may need to refresh your browser to see the graph.

The colors of your visualizations may differ from those shown in the image.

Click Check my progress to verify the objective. Create a map of the percentage of orders by US State in the past year.

Task 3. Create a column chart of the percent change in number of items returned month over month in the year 2020

In this task, you use an offset function to calculate the percent change in number of items returned month over month in the year 2020 and display the results as a column chart. Then, you add this visualization to a new dashboard for orders and users in 2020.

Calculate the percent change in number of items returned month over month in the year 2020

1. On the left-side navigation panel of the Looker User Interface, click Explore.

2. Under E-Commerce Training, click Order Items.

3. Click the arrow next to Order Items.

The available dimensions and measures will be listed in the data panel for Order Items.

4. Under Order Items > Measures, click Order Item Count.

5. Under Order Items > Dimensions > Returned Date, click Month.

6. Under Order Items > Dimensions > Returned Date, click the filter icon next to Date.

7. From the Filters dropdown list, select "is in the year" and type: 2020

8. Click Run.

Note: Do not sort the results. The table calculations will use the default sorting of the dates (descending order beginning with the most recent date).

If you sort the results, the table calculation values may not be accurately generated.

9. In the data panel above Distribution Centers, next to Custom Fields, click + Add.

10. Select Table Calculation.

11. Name the new table calculation: Items Returned Last Month

12. Add the following syntax to create the new table calculation:

offset(${order_items.order_item_count}, 1)

13. Click Save.

14. In the data panel above Order Items, next to Custom Fields, click + Add.

15. Select Table Calculation.

16. Name the new table calculation: Diff From Last Month

17. Add the following syntax to create the new table calculation:

${order_items.order_item_count}/${items_returned_last_month} - 1

18. Select Percent(1) - 123,456.8% as the format.

19. Click Save.

Customize a column chart to display the results

1. In the data window, click on the settings gear icon next to Order Item Count, and select Hide from Visualization.

2. In the data window, click on the settings gear icon next to Items Returned Last Month, and select Hide from Visualization.

3. Click the arrow next to Visualization to expand the window.

Once the Visualization window has expanded, you can hover your cursor over the icons to identify the available options.

4. Click the Column icon.

This option creates a column chart that you can customize. You can also make the visualization larger by collapsing the data window.

5. Click on the settings gear icon for Visualization.

6. Under Y, for Axis Name, type: Difference From Previous Month

7. Click on the gear icon for Visualization to close the settings.

Save the visualization to a new dashboard

1. Click on the settings gear icon next to Run (top right of page), and select Save > To an existing Dashboard.

2. Enter a title for the visualization: Monthly Percent Change in Items Returned

Leave the default destination as Shared.

3. Click New Dashboard.

4. Enter a title for the new dashboard: Orders and Users in 2020

5. Click OK.

6. Click Save to Dashboard.

7. View the dashboard by clicking on the provided hyperlink titled Orders and Users in 2020. When you have finished viewing, click Cancel.

The color of your visualizations may differ from those shown in the image.

Click Check my progress to verify the objective. Create a column chart of the percent change in number of items returned month over month in the year 2020.

Task 4. Create a column chart of the percent change in number of users created week over week for May 15, 2020 to September 15, 2020

In this task, you use an offset function to calculate the percent change in number of users created week over week for May 15, 2020 to September 15, 2020 and display the results as a column chart. Then, you add this visualization to the dashboard you created in the previous task.

Calculate the percent change in the number of users created week over week for May 15, 2020 to September 15, 2020

1. On the left-side navigation panel of the Looker User Interface, click Explore.

2. Under E-Commerce Training, click Order Items.

3. Click the arrow next to Users.

The available dimensions and measures will be listed in the data panel for Users.

4. Under Users > Measures, click Count.

5. Under Users > Dimensions > Created Date, click Week.

6. Under Users > Dimensions > Created Date, click the filter icon next to Date.

7. From the Filters dropdown list, select "is in range" and type: 5/15/2020 and 9/16/2020.

8. Click Run.

Note: Do not sort the results. The table calculations will use the default sorting of the dates (descending order beginning with the most recent date).

If you sort the results, the table calculation values may not be accurately generated.

9. In the data panel above Distribution Centers, next to Custom Fields, click + Add.

10. Select Table Calculation.

11. Name the new table calculation: Users Last Week

12. Add the following syntax to create the new table calculation:

offset(${users.count}, 1)

13. Click Save.

14. In the data panel above Order Items, next to Custom Fields, click + Add.

15. Select Table Calculation.

16. Name the new table calculation: Diff From Last Week

17. Add the following syntax to create the new table calculation:

${users.count}/${users_last_week} - 1

18. Select Percent(1) - 123,456.8% as the format.

19. Click Save.

Customize a column chart to display the results

1. In the data window, click on the settings gear icon next to Users Count, and select Hide from Visualization.

2. In the data window, click on the settings gear icon next to Users Last Week, and select Hide from Visualization.

3. Click the arrow next to Visualization to expand the window.

Once the Visualization window has expanded, you can hover your cursor over the icons to identify the available options.

4. Click the Column icon.

This option creates a column chart that you can customize. You can also make the visualization larger by collapsing the data window.

5. Click on the settings gear icon for Visualization.

6. Under X, for Time Label Format, type: %m-%d

7. Under Y, for Axis Name, type: Difference From Previous Week

8. Click on the gear icon for Visualization to close the settings.

Save the visualization to an existing dashboard

1. Click on the settings gear icon next to Run, and select Save > To an existing Dashboard.

2. Enter a title for the visualization: Weekly Percent Change in Users

3. Select the dashboard you previously created: Orders and Users in 2020.

4. Click Save to Dashboard.

5. View the final dashboard by clicking on the provided hyperlink titled Orders and Users in 2020.

The dashboard for Orders and Users in 2020 will contain the two visualizations that you added in the last two tasks.

Note: You may need to refresh your browser to see the graph.

The colors of your visualization may differ from those shown in the image.

Click Check my progress to verify the objective. Create a column chart of the percent change in number of users created week over week for May 15, 2020 to September 15, 2020.

Task 5. Open and edit an existing dashboard

1. On the left-side navigation panel of the Looker User Interface, click Folders.

2. Click Shared folders.

3. Click the Orders and Users in 2020 dashboard.

4. Click on the dashboard actions icon (three vertically stacked dots ).

5. Click Edit dashboard.

6. Resize the tile for Monthly Percent Change in Items Returned by clicking in the bottom right corner of the tile and dragging the cursor incrementally across the dashboard.

7. Increase the size of the tile until you see the x-axis label for December.

8. Repeat the previous step to resize the tile for Weekly Percent Change in Users.

9. Increase the size of the tile until you see the x-axis label for Sep 7.

10. Click Save.

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.