How to Create a Pivot Table in Google Analytics with AI

Trying to make sense of your Google Analytics data can feel like looking for a needle in a haystack. A pivot table is one of the best ways to organize that data, helping you uncover valuable insights by summarizing huge datasets into a clean, digestible format. This article will show you how to create pivot tables with your GA4 data, covering the classic spreadsheet method and a much faster, more powerful AI approach.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What Exactly is a Pivot Table?

Think of a pivot table as a data exploration tool. It allows you to take a large, flat table of information - like an export from Google Analytics - and quickly reorganize it to see the relationships between different data points. Instead of scrolling through thousands of rows, you can "pivot" the data to create a summary view based on the questions you want to answer.

For example, let's say you have GA data showing sessions, landing pages, traffic sources, countries, and device types. With a pivot table, you could easily answer questions like:

• Which traffic sources (e.g., Google Organic, Facebook Ads) send the most mobile traffic to specific landing pages?
• How do conversion rates for a specific campaign compare across different countries?
• What are the top 10 blog posts for users coming from the United States versus Canada?

These are tough questions to answer just by looking at standard GA reports. A pivot table cuts through the noise and organizes the data to show you exactly what you need.

The Classic Method: Exporting GA Data to a Spreadsheet

Years ago, Google's Universal Analytics had a pivot table feature built directly into its interface. Unfortunately, that function disappeared with Google Analytics 4. Now, the most common method for creating a pivot table is to export your data from GA4 and build it in a spreadsheet application like Google Sheets or Microsoft Excel.

While it works, this process can be slow and tedious. Here’s how it’s done.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 1: Create and Export Your Report from GA4

First, you need to pull the raw data you want to analyze. For this example, let's say we want to analyze which traffic sources are driving sessions to our top landing pages.

1. Log into your Google Analytics 4 property.
2. In the left-hand menu, navigate to Reports > Engagement > Pages and screens.
3. By default, this report shows you views by "Page title." Click the small dropdown arrow next to "Page title" and change the primary dimension to "Landing page + query string."
4. Now, to add a second dimension for our traffic source, click the small blue "+" icon next to the primary dimension and select Session acquisition > Session source / medium.
5. You now have a table showing your site's landing pages and the specific sources that brought users to those pages. In the top right corner of the report, find the "Share this report" icon (a box with an arrow pointing out) and click it.
6. Select Download File and choose "Download CSV." This will save the data to your computer.

You’ve just exported the raw ingredient for your pivot table. Now it’s time to shape it in Google Sheets.

Step 2: Create a Pivot Table in Google Sheets

Once you have your CSV file, open it in Google Sheets (or Excel - the process is very similar).

1. Open a new Google Sheet and go to File > Import > Upload. Select the CSV file you just downloaded from Google Analytics.
2. Your data will now be displayed in the sheet. It might look messy, as GA exports often include summary rows and other unneeded text at the top and bottom. Delete any rows that are not part of the main data table (i.e., any row that isn't a header or a data row).
3. Click anywhere inside your dataset. Then, go to the top menu and select Insert > Pivot table.
4. Google Sheets will ask whether to create the pivot table on a new sheet or an existing sheet. A new sheet is usually cleaner, so click "Create."

Step 3: Configure Your Pivot Table

You'll now see a blank pivot table and a "Pivot table editor" sidebar. This is where you tell the spreadsheet how to organize your data. The editor has four main sections:

• Rows: The dimension you want to list vertically.
• Columns: The dimension you want to list horizontally.
• Values: The metric you want to measure (e.g., Sessions, Conversions, Users).
• Filters: Used to narrow down your data (e.g., only show data for a specific country or device).

Let's build the pivot table to answer our original question: "Which traffic sources drive sessions to our top landing pages?"

1. In the Pivot table editor, click "Add" next to Rows and select "Landing page + query string."
2. Next, click "Add" next to Columns and select "Session source / medium."
3. Finally, click "Add" next to Values and select "Sessions." Make sure it's set to "Summarize by: SUM."

Instantly, your blank table will populate with the data. You now have a clear view showing each landing page as a row, each traffic source as a column, and the total number of sessions at the intersection of each. This makes it incredibly easy to see, for example, that google / organic is driving the most traffic to your homepage, while facebook.com / referral is the top source for a specific blog post.

The Problem with this Manual Method

As you can see, the classic method gets the job done. However, it comes with a few significant drawbacks, especially for teams who need to make decisions quickly.

• It's a time-consuming manual process. Downloading CSVs, cleaning them up, and building reports takes time. If you do this every week for a marketing meeting, you're losing hours to what is essentially data entry.
• The data is instantly stale. The moment you export your data from GA, your pivot table becomes a static snapshot. It doesn't update automatically. If you want the latest numbers tomorrow, you have to repeat the entire process from scratch.
• It's not very accessible. If you aren't comfortable with spreadsheet functions, the pivot table editor can feel intimidating. For non-technical team members, learning how to build these can feel like a chore, creating a bottleneck where only one "data person" can generate these reports.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

The Modern Solution: Creating Live Pivot Tables with AI

Instead of manually exporting static data, you can now use AI-powered analytics tools to connect directly to your Google Analytics account and build these reports for you. This approach eliminates nearly all the manual work and ensures you're always looking at live, up-to-the-minute data.

The core difference is how you interact with the data. Instead of being a spreadsheet operator, you become a manager who simply asks questions in plain English. The AI does the heavy lifting of querying the data and visualizing it for you, often in seconds.

Step 1: Connect Your Google Analytics Account

Modern AI tools are built for ease of use. You typically start by connecting your GA4 property with a few clicks using a simple OAuth connection. There are no API keys to track down or complex integrations to configure. The tool then syncs your historical data, giving the AI full context without you having to upload any files.

Step 2: Ask for a Report in Simple English

This is where the magic happens. Instead of using the multi-step pivot table editor, you just type what you want to see. Your request can be simple and conversational. For our previous example, you might ask:

“Show me a pivot table of sessions by landing page and session source/medium for the last 30 days.”

The AI understands your intent, pulls the necessary data directly from your connected GA account, and generates the pivot table you requested as an interactive chart or table on your dashboard.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 3: Analyze and Drill Down with Follow-Up Questions

The real power of this approach is in the follow-up. Unlike a static spreadsheet, an AI-generated report is fully interactive and modifiable through conversation. Once the table is created, you can continue exploring the data with more questions:

You: "Okay, now only show me traffic from the United States."

_The AI updates the table instantly, filtering out all other countries._

You: "Change the rows to show the device category instead."

_The AI re-pivots the data, replacing landing pages with "Desktop," "Mobile," and "Tablet."_

You: "Add Total Conversions to the values."

_A new column for conversion totals appears, giving your session data more business context._

This conversational, iterative process of analysis is a game-changer. It transforms data analysis from a tedious reporting task into a quick, intuitive exploration. You can follow your curiosity and drill down into insights without ever touching a spreadsheet again.

Why the AI Method is a Better Approach

For most marketing teams, founders, and anyone who isn't a dedicated data scientist, using AI to build GA reports is simply more efficient.

• Go from question to insight in seconds, not hours. There’s no exporting, no cleaning data, no building reports. Your entire reporting workflow is compressed into a 30-second conversation.
• Your data is always live. The reports are connected directly to the GA API, which means your dashboards and pivot tables are always pulling the latest information automatically. No more basing decisions on last Monday's CSV export.
• Anyone can use it. Data analysis is no longer limited to people with advanced Excel skills. If you can type a question, you can get answers. It democratizes access to insights across your entire team.

Final Thoughts

So, you don't have to be a spreadsheet expert to analyze Google Analytics data with pivot tables anymore. While exporting data to Google Sheets or Excel is a reliable method, it’s a manual process that produces static reports. Shifting to an AI-powered approach frees you from the drudgery of reporting and lets you focus on what really matters: acting on insights gained from your data.

At Graphed, we built our tool around this exact idea. Instead of forcing you to become an analytics pro, we bring the pro to you in the form of an AI analyst. Just connect your Google Analytics account, ask questions in plain English, and watch as our AI instantly builds live dashboards and reports for you. It's the simplest way to get the answers you need from your marketing data without any of the manual work.