Sheets with Google Data Studio (Looker) in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a monthly sales performance report that shows total sales by product category and region. The report should be easy to update and visualize using Google Data Studio.

📊 Data: You have a Google Sheet with sales data including Date, Region, Product Category, Units Sold, and Sales Amount.

🎯 Deliverable: Create a clean summary table in Google Sheets that aggregates total sales by month, product category, and region. Then connect this sheet to Google Data Studio to build a dashboard showing sales trends.

Sample Data

Date	Region	Product Category	Units Sold	Sales Amount
2024-01-05	North	Electronics	10	2500
2024-01-15	South	Furniture	5	1500
2024-02-10	East	Electronics	8	2000
2024-02-20	West	Clothing	15	1200
2024-03-05	North	Furniture	7	2100
2024-03-15	South	Clothing	20	1600
2024-04-01	East	Electronics	12	3000
2024-04-10	West	Furniture	6	1800
2024-05-05	North	Clothing	18	1440
2024-05-15	South	Electronics	9	2250

1

Step 1: Add a new column 'Month' to extract the month and year from the Date column.

=TEXT(A2,"yyyy-MM")

Expected Result

For 2024-01-05, Month shows 2024-01

2

Step 2: Create a summary table with columns: Month, Region, Product Category, Total Sales.

Use a pivot table: Rows = Month, Region, Product Category; Values = SUM of Sales Amount

Expected Result

Pivot table shows total sales grouped by month, region, and product category.

3

Step 3: Filter the pivot table to show only months from January to May 2024.

Apply filter on Month field to include 2024-01 to 2024-05

Expected Result

Pivot table displays sales data only for months Jan to May 2024.

4

Step 4: Name the sheet 'Sales Summary' and ensure it is clean and easy to read.

Format headers bold, adjust column widths, freeze header row

Expected Result

Sheet 'Sales Summary' is ready for connection to Google Data Studio.

5

Step 5: In Google Data Studio, connect to the Google Sheet 'Sales Summary' as a data source.

Use 'Add Data' > Google Sheets > Select 'Sales Summary' sheet

Expected Result

Data source connected and fields available for report building.

6

Step 6: Create a time series chart in Data Studio showing Total Sales over months.

Dimension = Month, Metric = SUM of Total Sales

Expected Result

Chart displays sales trend from January to May 2024.

7

Step 7: Add a filter control in Data Studio to select Region and Product Category.

Add filter controls for Region and Product Category fields

Expected Result

User can filter the sales trend chart by region and product category.

Final Result

Monthly Sales Summary Report

Month    | Region | Product Category | Total Sales
--------------------------------------------------
2024-01  | North  | Electronics      | 2500
2024-01  | South  | Furniture        | 1500
2024-02  | East   | Electronics      | 2000
2024-02  | West   | Clothing         | 1200
2024-03  | North  | Furniture        | 2100
2024-03  | South  | Clothing         | 1600
2024-04  | East   | Electronics      | 3000
2024-04  | West   | Furniture        | 1800
2024-05  | North  | Clothing         | 1440
2024-05  | South  | Electronics      | 2250

[Google Data Studio Dashboard]
- Time series chart: Total Sales by Month
- Filter controls: Region, Product Category

✓Electronics consistently generate high sales across regions.

✓North region leads in Electronics sales in early months.

✓Furniture sales are strong in South and West regions.

✓Clothing sales peak in March and May.

Bonus Challenge

Add a calculated field in Google Sheets to compute average sales per unit for each row, then update the summary table to include average sales per unit by month, region, and product category.

Show Hint

Create a new column with formula =E2/D2 (Sales Amount divided by Units Sold), then use this field in your pivot table with AVERAGE aggregation.