How to Create a Pivot Table in Google Analytics

A standard Google Analytics report is great for a quick overview, but what happens when you need to answer a more complicated question? Queries like "which traffic sources drive the most conversions on mobile vs. desktop?" can be difficult to answer inside the standard interface. To uncover these deeper insights, you need to use a pivot table. This guide will show you exactly how to get your Google Analytics data into a pivot table to slice, dice, and analyze it effectively.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Use a Pivot Table with Google Analytics Data?

If you've ever felt constrained by the limitations of standard GA reports, you already know why pivot tables are so valuable. Standard reports are pre-configured to answer the most common questions: Which channels drive the most traffic? Which pages are most popular? How are user numbers trending over time?

These are important, but they often lead to more specific, complex questions. A pivot table is the perfect tool for this next level of analysis because it lets you restructure your data on the fly. You can take a flat table of data - like a typical GA export - and reorganize it by dragging and dropping different dimensions and metrics.

This allows you to:

• Summarize Large Datasets: Condense thousands of rows of data into a small, easy-to-read summary table.
• Perform Multi-Dimensional Analysis: Look at your data from several angles at once. For example, you can analyze sessions not just by channel, but by channel and device, or by landing page and country.
• Find Hidden Patterns: Quickly spot trends and relationships that are invisible in standard reports. You might discover that a specific blog post converts exceptionally well for organic search traffic on tablets, an insight that would be nearly impossible to find otherwise.
• Answer Specific Business Questions: Move beyond canned reports and build a custom view that directly answers a strategic question, like "What is the average order value for customers acquired through paid search vs. paid social, broken down by location?"

In short, using your GA data in a pivot table moves you from passively viewing metrics to actively interrogating your data to find actionable insights.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The Core Challenge: GA Doesn't Have a Built-in Pivot Table

Here's the most important thing to understand: Google Analytics does not have a native pivot table feature like you would find in Microsoft Excel or Google Sheets. While GA4's "Explore" section offers powerful tools for building custom reports, including a "free form" exploration that has some similarities, it's not a true pivot table. It doesn't offer the same fluid, drag-and-drop reorganization that spreadsheet users are familiar with.

Therefore, the process always involves getting your data out of Google Analytics and into a tool that supports pivot tables. The most common destinations are Google Sheets or a dedicated business intelligence tool. Below, we'll walk through the two best methods for making this happen.

Method 1: Exporting GA Data to Google Sheets (The Manual Way)

This is the most common and straightforward method. It's free, uses tools you already know, and is perfect for quick, one-off analyses. The big drawback is that it's a manual process, and the data is a static snapshot - it won't update automatically.

Step 1: Get the Right Data in Google Analytics

First, you need to build the foundation of your report within the Google Analytics interface. The key is to add enough dimensions to the report so that you have all the fields you'll need for your pivot table later.

1. Navigate to the report that most closely matches your needs. For example, to analyze traffic behavior, go to Reports > Acquisition > Traffic acquisition.
2. Adjust the date range to match the period you want to analyze.
3. This is the most crucial step: add a secondary dimension. Let’s say you want to see sessions by channel group and device. The 'Session default channel group' is your primary dimension. Click the plus sign (+) next to the primary dimension column header and add 'Device category' as the secondary dimension. GA will now show you a table with rows for every combination (e.g., Organic Search / desktop, Organic Search / mobile, etc.).
4. Your goal is to build a table in the GA UI that has all the columns (dimensions and metrics) you'll possibly want to use in your pivot table.

Quick Tip: Be aware of GA's export limits. When exporting directly from the user interface, you're often limited to the first 5,000 rows. For larger datasets, you'll need to use Method 2.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Export the Data to Google Sheets

Once your report is configured, you can export it.

1. In the top right corner of the report, click the "Share this report" icon (it looks like a box with an arrow pointing out).
2. Select "Download File" and choose "Download CSV." This will download the data to your computer.
3. Open a new Google Sheet.
4. Go to File > Import > Upload and select the CSV file you just downloaded. Leave the import settings as default and click "Import data."

Your Google Sheet will now contain the raw, flat data table from Google Analytics, ready to be pivoted.

Step 3: Create the Pivot Table

Now for the fun part. With your data loaded into Google Sheets, creating a pivot table takes just a few clicks.

1. Click on any cell inside your dataset.
2. Go to the nav menu and click Insert > Pivot Table.
3. Google Sheets will automatically select your data range and ask whether you want to create the pivot table in a new sheet or the existing one. "New sheet" is usually the best choice. Click "Create."
4. You'll now see a blank pivot table and the Pivot table editor on the right-hand side. This editor is where you build your report.

Let's use our previous example: we want to compare traffic source performance across different devices.

• Under the "Rows" section of the editor, click "Add" and select Session default channel group. You’ll see a list of your traffic channels appear in rows.
• Under "Columns," click "Add" and select Device category. Your table will now have columns for desktop, mobile, and tablet.
• Under "Values," click "Add" and select Sessions. It will automatically be summarized by 'SUM.' Your pivot table now shows you the total number of sessions for each channel, broken down by device type.
• Add another metric to the "Values" section, like Conversions, to see which combinations are most effective.

The beauty of this is that you can instantly "pivot" the view by dragging 'Device category' from Columns to Rows. Now you have a list of devices, with the traffic channels nested underneath. This flexibility is what makes pivot tables so powerful for finding insights.

Method 2: Using Looker Studio for a Live Pivot Table

If you need to create a pivot table report that stays up-to-date or if you’re working with more than 5,000 rows of data, the manual export method becomes too tedious. The better alternative is to use Looker Studio (formerly Google Data Studio), Google's free data visualization tool.

The big advantage here is that Looker Studio connects directly to your Google Analytics property via the API. This creates a live data connection, meaning your pivot table will automatically refresh with the latest information, and you bypass the row limits of manual UI exports.

Step 1: Connect Google Analytics as a Data Source

1. Go to lookerstudio.google.com and start a new "Blank Report."
2. You'll be prompted to add data to the report. Search for and select the "Google Analytics" connector.
3. Authorize the connection to your Google Account. Then, select the Account and GA4 Property you want to pull data from.
4. Click "Add" in the bottom right corner to add this data source to your report.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Add and Configure a Pivot Table

With your data source connected, you can now add the pivot table visualization.

1. In the Looker Studio menu, navigate to Insert > Pivot Table.
2. Your cursor will change to a crosshair. Click and drag on the report canvas to draw the outline of your table.
3. With the blank pivot table selected, a configuration panel will appear on the right. This works just like the Google Sheets pivot table editor.
4. Drag dimensions you want to use into the "Row dimension" and "Column dimension" sections. For our example, put Session default channel group in the rows and Device category in the columns.
5. Drag the metrics you want to analyze, like Sessions and Conversions, into the "Metric" section.

Your interactive, live-updating pivot table is now ready. You can add date range filters, custom calculations, and other charts to build a full-fledged dashboard that you can share with your team.

Practical Tips for Better Pivot Table Analysis

Regardless of which method you choose, a few best practices will help you get the most out of your analysis.

• Start Simple. Begin with a single row dimension and one metric. Once you have a basic table, gradually add more dimensions and metrics. This prevents you from getting overwhelmed by the data.
• Ask Specific Questions. Your pivot table is a tool to answer a question. Before you start, formulate that question clearly. Instead of "look at traffic sources," try "investigate conversion rates for new users arriving from paid search vs. organic search on our key landing pages."
• Use Calculated Fields. Both Google Sheets and Looker Studio allow you to create your own metrics. This is incredibly useful for calculating rates and ratios. For example, you can create a 'Conversion Rate' metric by making a calculated field for SUM(Conversions) / SUM(Sessions).
• Visualize Your Findings. Once you've created a pivot table that reveals an interesting insight, create a bar or line chart from that summarized data. Visuals make it much easier to communicate your findings to others.

Final Thoughts

Creating a pivot table with your Google Analytics data is a proven way to move beyond surface-level metrics and uncover the deeper insights that drive smart decisions. By either exporting your data to Google Sheets for a quick analysis or building an automated dashboard in Looker Studio, you can unlock a new level of understanding about how users behave and what truly drives performance.

We know that even with tools like Looker Studio, the process of connecting data sources, configuring reports, and finding an actual insight can still be slow and manual. At Graphed, we've designed a platform to eliminate this friction entirely. Instead of building pivot tables yourself, you can simply ask a question in plain English like, "show me conversions by channel and device for last quarter as a table," and our AI data analyst builds the report instantly. We connect directly to your Google Analytics account, so your data is always live, and you can get from question to insight in seconds, not hours.