How to Transform Google Sheets Data to Display Metrics in Rows and Weeks in Columns in Looker Studio

Data Studio Data Studio Q&A
, ,
1

Hello Looker Studio Community,

I’m working on visualizing some data from Google Sheets, and I need help with formatting it in Google Looker Studio. Here’s the situation:

Data in Google Sheets:

I have data structured as follows in Google Sheets:

Week Sales Impressions Clicks

2023-W01 1000 50,000 2,500
2023-W02 1200 45,000 2,800
2023-W03 900 48,000 2,400

Desired Format in Google Looker Studio:

I want to display this data in Google Looker Studio in the following format, where the metrics (Sales, Impressions, Clicks) are shown in rows, and the weeks (2023-W01, 2023-W02, 2023-W03) are displayed in columns:

Metric 2023-W01 2023-W02 2023-W03

Sales 1000 1200 900
Impressions 50,000 45,000 48,000
Clicks 2,500 2,800 2,400

What I’ve Tried:

I’ve connected my Google Sheets data to Looker Studio and set up a Pivot Table, but I am struggling with how to format the data such that:

  • The metrics (Sales, Impressions, Clicks) are displayed as rows.
  • The weeks (2023-W01, 2023-W02, 2023-W03) are displayed as columns.

I’ve tried adding the week in the Columns section and the metrics in the Rows section, but the output isn’t formatted as I want it and Row Dimension does not take more than two dimentions.

Questions:

  1. How can I set up my pivot table to correctly display the metrics (Sales, Impressions, Clicks) in rows and the weeks in columns?
  2. Is there a way to dynamically create the structure where the metrics and weeks are aligned this way using calculated fields or any specific configuration?

Any help or tips on how to format my data correctly in Looker Studio would be greatly appreciated!

Thank you in advance!

2

@jamshed

  1. Pivot table is correctly representing your numbers. Your data is one dimensional hence the given layout.
  2. Yes and no. Your example is table and not a pivot table meaning you need to transpose your data first and create a table in looker studio. However, you will need to add each week as separate column which is probably not best idea..
  3. You can introduce new dimension to your data but this wont also produce result you require as its going to correctly pivot your data and show all in one row for your dimension.

I suggest try changing your habit from scrolling horizontally to pivotally.
I understand it may be challenging at start but BI are bit different from Spreadsheets.

Regards
Arkady Zagdan