How to Use Data Overlays in Looker

Seeing your data in a chart is a great first step, but a flat line or a series of bars often lacks the most important thing: context. An overlay chart answers that crucial follow-up question, "Is this number good?" by placing it right next to another dataset for comparison. This article will walk you through several effective methods for creating data overlays in Looker so you can stop looking at isolated metrics and start seeing the full story.

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 Exactly is a Data Overlay in Reporting?

Think of a data overlay like using tracing paper. You have a drawing on one sheet - say, your sales from this year. You then take a see-through sheet with another drawing - your sales from last year - and place it directly on top. Instantly, you can see where the lines overlap, where they diverge, and how they compare.

In data visualization, an overlay superimposes one data series over another within a single chart. This technique is designed to reveal relationships, patterns, and variances between different sets of metrics. It transforms a static chart into a dynamic comparison, giving you the context needed to make informed decisions.

When Is a Data Overlay Useful?

Context is everything in data analysis. A raw number rarely tells a complete story. Is hitting $50,000 in monthly revenue a success? If last month you did $30,000, absolutely. If you were forecasted to hit $100,000, it's a cause for concern. Overlays provide this necessary context visually.

Here are a few common scenarios where data overlays are incredibly powerful:

• Time-Series Comparisons: The most classic use case. Seeing this month's performance against last month's (Month-over-Month), this quarter's against the same quarter last year (Year-over-Year), or even this week versus last week.
• Actuals vs. Goals: Overlaying your actual performance metrics (like revenue, user signups, or leads generated) on top of your forecasted goals or targets. A budget line, for example.
• Correlating Events: Visualizing a performance metric like website traffic and overlaying key events like marketing campaign launch dates, product updates, or PR mentions to see their impact.
• Comparing Segments: Plotting the performance of different customer segments, product categories, or marketing channels on the same chart to see which is leading the pack.

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

Preparing Your Data for an Overlay in Looker

Before you jump into visualization, a little prep work goes a long way. The success of your overlay chart depends on having a clean, consistent foundation. The core principle is that both datasets you want to overlay must share at least one common dimension to be aligned properly.

Most often, this common dimension is time. Whether you're comparing years, months, or days, your data needs a standardized date field to serve as the x-axis for your chart. If you're comparing different segments (like ad channels) over time, the date is still your anchor, but the channel becomes the series you "overlay."

Ensure that the dimensions you plan to use are consistently formatted across all your data sources. Inconsistencies like having 'United States' in one table and 'USA' in another can prevent Looker from aligning the data correctly. Your LookML developers can help build a robust data model that standardizes these values so you don't have to worry about it at the reporting stage.

Method 1: Using Pivot for Easy Time-Series Overlays

The simplest and quickest way to create a year-over-year or similar time-based comparison in Looker is by using the pivot feature. This method works beautifully when you want to compare the same metric across different time periods.

Let's say you want to build a line chart showing this year's sales versus last year's sales, broken down by month.

Step-by-Step Guide to Pivoting for an Overlay:

1. Build Your Base Query: From your Looker Explore, select your primary time dimension and your primary measure. For our example:

• From your time dimension group, select "Month of Year Name." This will give you January, February, March, etc., on your x-axis.
• Select your key metric, like "Total Sales."

At this point, if you run the report, you’ll get the total sales for each month, but all years will be aggregated together - not what we want.

2. Select Your Pivot Dimension: The real magic happens here. Find another time dimension that represents the periods you want to compare - in this case, "Year." Instead of clicking it to add it as a dimension, click the Pivot button next to it.

3. Run and Visualize: Click the "Run" button. Looker will now transform your data table. You'll see "Month of Year" on the left, and separate columns for each year ("2023", "2024", etc.) displaying the total sales for that month and year. Switch to a line chart visualization, and Looker will automatically plot each year as a separate line. Voila - you have a year-over-year overlay!

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

Pros and Cons of the Pivot Method:

• Pros: Extremely fast and intuitive for users without LookML knowledge. It's the go-to method for comparing the same metric over set time period segments.
• Cons: It’s less flexible. It only works well when you're comparing the same measure across different periods or categories. If you want to overlay two entirely different metrics (like Website Sessions and Sales Revenue), you'll need another approach.

Method 2: Merging Results to Overlay Different Metrics

What if you want to see if your marketing efforts correlate with sales? You might want to overlay a line representing Marketing Spend and another representing Sales Revenue on the same chart. Since these are two different metrics, a pivot won't work. Instead, you'll use Looker's "Merge Results" feature.

Step-by-Step Guide to Merging Results:

1. Create Your Primary Query: In your Looker Explore, build the first half of your chart. Select your common dimension (e.g., "Created Date") and your first metric (e.g., "Total Revenue"). Run the query to make sure it looks right.

2. Choose 'Merge Results': Click the gear icon in the top right of the Explore section. From the dropdown, select "Merge Results." This saves your current query and opens a window for you to create a second query.

3. Create Your Secondary Query: You will now build your second query that contains the data for your overlay. Choose the same common dimension ("Created Date" is critical here for alignment) and your second metric (e.g., "Marketing Spend"). Run this query as well.

4. Configure the Merge: Looker will now show you both results side-by-side and ask you how to merge them. The most important step is selecting the "Merge Rule" - this is the common dimension that ties everything together. Looker is usually smart enough to guess the correct one (e.g., it will match "Created Date" from the first query with "Created Date" in the second).

5. Visualize the Merged Data: Click "Merge." You'll be taken back to a single data table that now contains both "Total Revenue" and "Marketing Spend" lined up by date. Now you can use the visualization tools. A combo chart is often perfect here. You could show Revenue as columns and Marketing Spend as a line. If the scales are dramatically different (revenue in millions, spend in thousands), you can use the Y-axis settings to place one metric on the left y-axis and the other on the right.

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

Method 3: Creating Custom Dimensions for "Event" Markers

Sometimes you don’t need a full line chart as an overlay. Instead, you just want to mark specific points in time on an existing chart to see their impact. For example, you have a chart of daily website traffic, and you want to see if traffic spiked after your recent "Summer Sale" marketing campaign launch or a major product feature release.

You can achieve this by creating a quick custom field using a case statement, a simple form of if-then logic.

Here’s How You Can Add Event Markers:

1. Start with your base query (e.g., "Daily Sessions" by "Date").
2. Instead of adding another measure, click "Add" next to "Custom Fields".
3. Choose the "Dimension" type.
4. In the formula box, write a case statement that isolates your event date. For a campaign on November 24th, 2023, it would look like this:

(IF(${your_table_name.date_field} = "2023-11-24", "Summer Sale Launch", NULL))

1. Give it a name, for example, "Marketing Events." Save it.
2. This new custom field is now available to use. It will be NULL (empty) for every date except November 24th. In a table, you'll see the text "Summer Sale Launch" appear only on that date's row. In a line chart, you can adjust the settings for this series to appear as a point or marker, giving you a perfect event overlay without cluttering your chart with another line.

Best Practices for Effective Overlay Visualizations

Building an overlay is just the first step. Making it clear, readable, and actionable is what truly matters.

• Keep It Simple: It’s tempting to overlay five or six datasets. Resist the urge. Most overlay charts become confusing after two or three series. Keep it focused on the core comparison you want to make.
• Use a Coherent Color Scheme: Assign distinct, easy-to-distinguish colors to each data series. If you're comparing actuals to goals, use strong and muted versions of the same color to show the relationship.
• Label Everything Clearly: Don't make your audience guess. Use a clear chart title, axis labels, and a legend to explain what each line, bar, and axis represents.
• Check Your Axes: When overlaying metrics with vastly different scales (like Sessions in millions and Conversion Rate in single percentages), use a dual Y-axis. However, be cautious - dual axes can sometimes be misleading if not clearly labeled.
• Leverage Tooltips: Customize the tooltips that appear when a user hovers over the chart. You can include extra information or context that helps them understand the data point without cluttering the visualization itself.

Final Thoughts

Creating data overlays in Looker is an essential skill for anyone looking to graduate from basic reporting to insightful analysis. Whether by pivoting data for time-series comparisons, merging results for different metrics, or adding event markers with custom fields, these techniques add crucial context that helps you and your stakeholders understand the true story behind the numbers.

We know that even with a powerful tool like Looker, the setup process - pivoting tables, merging results, and structuring data - can take precious time away from actual analysis. At Graphed, we built our tool to solve this exact problem. Instead of navigating menus to overlay different metrics, you can simply ask a question like, “Show me my year-over-year revenue by month” or “Create a chart comparing marketing spend to sales since January 1st.” Our AI builds the interactive, real-time dashboard for you in seconds, turning hours of configuration into a simple conversation.