How to Pivot in Looker

Pivoting data is a fundamental skill for transforming long lists of records into insightful, summarized reports. If you've ever used a pivot table in Excel or Google Sheets, you’ll be right at home with Looker’s powerful pivot feature. This article will show you exactly how to pivot data within the Looker Explore interface to easily compare values across different categories and create more effective visualizations.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is a Pivot Table in a Looker Context?

Pivoting is the process of converting row data into columns. Instead of having a long, vertical list of data, a pivot rearranges it into a wider, more compact format that’s easier to read and compare. It's one of the most effective ways to summarize information and uncover trends that might be hidden in raw data.

Imagine you have a dataset of t-shirt sales with three columns: Sale Date, T-Shirt Color, and Quantity Sold. Your raw data table might look like this:

• Jan 1 | Blue | 10
• Jan 1 | Red | 15
• Jan 2 | Blue | 8
• Jan 2 | Red | 12

This format is fine, but it’s hard to quickly compare the sales of blue vs. red shirts day by day. By pivoting on the T-Shirt Color dimension, you can transform the table into this structure:

• Sale Date | Blue | Red
• Jan 1 | 10 | 15
• Jan 2 | 8 | 12

See the difference? The values from the 'T-Shirt Color' row ('Blue' and 'Red') have become their own columns. This immediately makes it easier to track the daily sales performance of each color side-by-side. In Looker, pivoting allows you to do this more easily without downloading anything to CSV.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Reasons to Use Pivoting in Looker

When working with vast data series, pivoting is invaluable whether you do it in Excel or an advanced tool like Looker. Pivoting offers major advantages over non-pivoted layouts, including:

• Better Side-by-Side Comparison: It's about arranging and grouping relevant sections of your raw data in a way that helps you interpret the information faster, facilitating analysis without distractions. Pivoted layouts make comparisons between various segments straightforward, whether it's comparing sale volumes by regions or evaluating customer satisfaction scores over specific periods.
• Enhanced Visualization: Certain visualizations, like side-by-side or stacked/grouped bar and column chart styles in Looker, benefit from properly pivoted table arrangements. These visualizations often work best with pivoted data rather than unpivoted versions.
• Simplified Timeline or Time-Series Analysis: Pivoting dimensions based on time intervals, such as months or quarters, allows for a quick overview of trends, business growth over periods, and recognizing data patterns that repeat over time. This provides critical information for stakeholders to plan ahead or make informed decisions.

Step-By-Step Guide to Creating a Looker Pivot Data Table

Let's walk through the steps of pivoting data within Looker's Explore mode. Suppose you are an e-commerce platform with data that includes order creation time, date, and product purchases. Our goal is to have a month-by-month overview in 2024 to compare revenue between two selected user statuses from the US and the UK.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 1: Enter the Explore Screen

The process begins in the "Explores" view of Looker. Explores are designed for creating custom queries and different visualization styles by interacting directly with a database. Select the desired source, such as Orders, to start your exploration.

Step 2: Select Your Desired Dimensions

Choose the categorical variables to group and examine numerical metrics. In the field picker menu, select:

• 'Created Month' as a new dimension under the 'Orders View.' Apply a filter to include only months within 2024.
• 'Total Sale Price' as a measure. Measures represent sums, so group them differently from dimensions.
• Additional dimensions such as 'User Status' and 'User Country' from the 'Users' category view.

Step 3: Define the Pivot

Hover over 'User Status,' click the gear icon, and select "Pivot." This action converts selected field values into columns, allowing for straightforward comparisons.

Step 4: Execute and Refine

Click the 'Run' button. Looker generates the layout, and you can refine the query as needed to produce the desired report. Adjust the results until the report fully addresses your research issue.

Pro Tips for Working with Pivots in Looker

Manage Column Numbers

Looker restricts pivoted tables to a maximum of 200 columns. For simplicity, filter dimensions before pivoting to manage included values effectively.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Create Calculated Metrics

Utilize Table Calculations to create ad hoc measurements directly within an Explore session, without editing LookML definitions. For example, calculate month-to-month growth as "The revenue this compared to the prior month" to provide additional insights from available columns, like 'YoY sales value growth.'

Final Thoughts

Pivoting data in Looker is a straightforward yet powerful way to reshape your reports for clear comparisons and deeper analysis. By turning values from a single column into individual columns, you can quickly spot trends, summarize complex datasets, and prepare your information for compelling data visualizations.

While tools like Looker provide extensive capabilities, they often come with a steep learning curve. If you'd prefer getting answers without the report-building busywork, consider Graphed. Our AI data analyst allows marketers and sales teams to connect their scattered data sources and receive real-time dashboards and reports by simply asking questions in plain English. Describe your report needs - like "show me revenue by campaign from Facebook Ads and Google Ads, and tell me which channel has the best ROI" - and Graphed does the heavy lifting, saving you hours of effort.