How to Create a Heat Map in Looker Studio

Building a heat map in Looker Studio isn't quite as simple as clicking a single chart option, but it's a powerful and entirely achievable way to visualize patterns in your data. With just a few clever tweaks to a standard pivot table, you can create a compelling visual that shows you exactly when your customers are most active or when your sales are peaking. This guide will walk you through the entire process, step-by-step, transforming a plain table into an insightful heat map.

What Exactly Is a Heat Map (And Why Should You Bother?)

A heat map is a data visualization that uses a spectrum of color to represent the intensity or magnitude of a value. Think of it like a weather map from the evening news, where darker shades of red might indicate higher temperatures. In business analytics, we swap temperature for metrics like user sessions, revenue, conversion rates, or email open rates.

The primary benefit of a heat map is its ability to make complex data instantly understandable. Instead of scanning rows and columns of numbers, you can see 'hot spots' and 'cold spots' at a single glance. It’s perfect for answering questions like:

• User Engagement: On which days and at which hours are users most active on our website?
• Sales Performance: Which product categories sell best in which regions?
• Campaign Timing: When are our email campaigns getting the highest engagement?

By mapping out a metric across two different dimensions (like 'Day of Week' and 'Hour'), you can quickly identify trends, patterns, and anomalies that might have otherwise gone unnoticed.

The Foundation: Using a Pivot Table as Your Canvas

Since Looker Studio lacks a built-in "Heat Map" chart type, our strategy is to use a pivot table as the structural foundation and then use conditional formatting to paint the colors. This method gives you a grid layout perfect for a heat map.

A standard table lists data in rows and columns, but a pivot table allows you to create a matrix using two different dimensions - one set as your rows and another as your columns. The intersection of each row and column then displays your chosen metric. This grid is the canvas we'll need to color in.

Step 1: Get Your Data Ready

Before you jump into Looker Studio, make sure your dataset is structured properly for a heat map. You'll need:

• A Row Dimension: This will be the vertical axis of your heat map. For a lot of web analytics, 'Day of week' works great here.
• A Column Dimension: This forms the horizontal axis. 'Hour of day' is a perfect partner for the 'Day of week' dimension.
• A Metric: This is the numerical value that will determine the color intensity of each cell. Examples include 'Sessions,' 'Active Users,' 'Conversions,' or 'Total Revenue.'

For this tutorial, we will use a classic engagement heat map built with Google Analytics 4 data to visualize user sessions. Our goal is to see which days and hours our website is busiest.

• Row Dimension: Day of week name
• Column Dimension: Hour
• Metric: Sessions

Step 2: Building Your Heat Map in Looker Studio

With your data source ready, you can now start building the visualization. Follow these steps carefully to go from a blank canvas to a fully functional heat map.

1. Create a New Report and Add a Pivot Table

Start by either creating a new Looker Studio report or opening an existing one. Connect your data source (in this case, our Google Analytics 4 property).

Next, find the Chart menu. Go to Insert > Pivot table and click to add it to your report canvas. It will likely show some default dimensions and metrics, which we will change next.

2. Configure the Dimensions and Metric

With the pivot table selected, the property panel will appear on the right side of the screen. Under the Setup tab, you need to configure your data field.

1. Row Dimension: Drag the 'Day of week name' field from your Available Fields and drop it into the 'Row dimension' area. Looker Studio may not sort this correctly by default (e.g., it may sort alphabetically starting with Friday). We’ll address this later.
2. Column Dimension: Drag the 'Hour' field into the 'Column dimension' area. This will create columns for each hour of the day (0-23).
3. Metric: Finally, drag 'Sessions' (or your chosen metric) into the 'Metric' section.

At this point, you'll have a functional pivot table. It shows the data correctly, but it's just a sea of numbers. It’s hard to pull quick insights from it... yet! Now comes the fun part: adding the color.

3. Apply Conditional Formatting for the Heat Map Effect

The "heat" in our map comes from conditional formatting. This tells Looker Studio to change a cell’s background color based on the value inside it.

1. Click on your pivot table to select it.
2. In the property panel on the right, switch from the Setup tab to the Style tab.
3. Scroll down until you see the 'Conditional formatting' section and click Add.
4. This opens the rule editor. Here’s how to configure it:

Click 'Save.' Boom! Your grid of numbers should now have a colored background. The cells with the highest number of sessions will be the darkest, and the cells with the fewest will be the lightest. You are now seeing a proper heat map.

Step 3: Fine-Tuning Your Heat Map

Now that the core visual is built, you can apply some finishing touches to make it cleaner and easier to read.

1. Cleaning Up the Table Appearance

Back in the Style menu for your pivot table, you can make a few cosmetic adjustments to get a cleaner look:

• Hide the numbers: Sometimes, the numbers on top of the colors can make the chart feel cluttered. If you want a purely visual heat map, go to the Table fonts section within Style, and change the font color to be transparent or match the cell background. This makes the text "disappear."
• Remove Table Headers: Under the Table headers section, you can uncheck the 'Show header' box if you prefer a minimalist look, but often it helps to keep them for context.
• Adjust Missing Data & Empty Cells: By default, missing data shows up as 'null' or empty. You can change this to display '0' or show nothing at all under the Missing data dropdown in the style panel.

2. Curing Sorting Headaches

A very common problem is that dimensions like 'Day of week name' will sort alphabetically instead of chronologically (i.e., Friday, Monday, Saturday...). This makes your heat map illogical to read. The same thing can happen with 'Month name.' Fixing this requires creating a new calculated field.

Here’s the fast way to fix it:

1. Go to Resource > Manage added data sources.
2. Click Edit on your GA4 data source.
3. Click Add a Field in the top corner.
4. Give your new field a name, like 'Day of Week Sorter.'
5. Use a CASE formula to assign a number to each day of the week. Remember, Google Analytics treats Sunday as day 0 or 1 in some contexts. The official Weekday field numbers Sunday as 1. For Day of week with names, an alphabetizing system is often best, but for Weekday name the below CASE works.

CASE
  WHEN Day of week = 1 THEN '1 - Sunday'
  WHEN Day of week = 2 THEN '2 - Monday'
  WHEN Day of week = 3 THEN '3 - Tuesday'
  WHEN Day of week = 4 THEN '4 - Wednesday'
  WHEN Day of week = 5 THEN '5 - Thursday'
  WHEN Day of week = 6 THEN '6 - Friday'
  WHEN Day of week = 7 THEN '7 - Saturday'
  ELSE 'Unknown'
END

1. Click Save. Now, go back to your chart configuration and use your new 'Day of Week Sorter' field as the Row Dimension. It will sort correctly! Then, just edit the field's display name on the chart to hide the numbers.

Similarly, for Hours, make sure the field you’re using is a numerical one (Hour), not a text field, so it sorts '1, 2, 3…' instead of '1, 10, 11, 2...'.

Final Thoughts

By using a pivot table and conditional formatting, you can easily create insightful, easy-to-read heat maps right inside Looker Studio. This technique allows you to transform rows of raw numbers into a clear visual story, helping you spot critical patterns in user behavior, sales cycles, or campaign performance without sinking hours into complex analysis.

While this manual process in Looker Studio is effective, we know it involves several steps, from configuring pivot tables and sorting custom fields to styling colors. This is exactly an area where we designed Graphed to help. Instead of clicking through setup panels, you can just ask our AI to do the work with a simple prompt like, "Show me a heat map of website sessions by day and hour for the last three months." We connect to your data, interpret your request, and build the real-time, interactive heat map instantly, complete with correct sorting and clear visualization, getting you from question to insight in seconds, not minutes.