Sankey Diagram for Google Sheets — How to Create a Sankey Chart Using Google Ads Data?

Data is a double-edge sword. On the one side, data is extremely valuable and allows you to learn how to improve ads and more effectively connect with your customers. The not-so-nice side of data is that it is incredibly difficult and time-consuming to extract actionable info from it.

The real problem is that your Google Ads account is producing a sea of data at all times. Staying afloat in these rough waters is not easy. If you aren’t careful, you can easily drown in it.

Oh, and did we mention that the data is constantly changing? Thus, you need to be swift in your ability to analyze the information. Otherwise, you’ll be drawing conclusions from data that is well past its shelf-life.

All of the pressures of managing and analyzing campaign data leads to what is known as analysis paralysis. This is essentially a case where the data becomes so overwhelming that it is almost impossible to make any sort of decision.

To overcome the risk of data paralysis, you need to use charts and visualizations.

When you turn raw data into a visual format, you process the information much faster and the insights become easier to obtain.

In this how-to guide, we’ll look at how to import data from Google Ads directly into a spreadsheet and prepare it for a Sankey diagram in Google Sheets.

This is a useful chart that you can use to see and better understand your campaign data and make insight-driven decisions regarding your PPC strategy.

Let’s get started.

Why It Is Important To Chart Your Data

It’s simple: charting your data is the most efficient way to understand the story behind the numbers. Your brain will seriously thank you because visuals are its preferred delivery method.

But, it’s not just about reducing headaches and preserving your mental health. Your brain also processes visual data 60,000 times faster than text or numbers.

Thus, taking raw data and presenting it in a visual model should be an essential step in your analysis process.

If you’re analyzing data in a spreadsheet, you’re wasting valuable time that could be spent on other tasks.

Charting data is particularly important for large or complex data sets. If you were to analyze it by hand, it could take you hours. However, once you chart the data, you can physically see what’s happening.

As soon as you see it, you get it.

Additionally, charts are universal. We’re all familiar with them and can interpret the insights being depicted. They are effective tools when you need to communicate results to others that are less familiar with your campaign data.

Here are some additional ways that PPC charts can benefit your organization:

• Discover correlations and other relationships between campaign metrics and dimensions.
• Compare data from past and current periods to see trends over time.
• Know how frequently these trends and patterns occur so that you can prepare and react ahead of any changing conditions.
• Examine the market as a whole to see what opportunities and risks exist, how target audiences are behaving, what strategies competitors are using and more.
• Understand what areas can be left alone and what areas need your direct attention. This is also known as risk and rewards analysis.
• React quicker to market changes and be the first to capitalize on the fresh opportunities.

What Is A Sankey Chart?

When you’re ready to visualize your data, you have a lot of different chart options available. Often, you need to think about the data that you want to model, its purpose, and how to best display the information visually.

One option is a Sankey chart.

A Sankey diagram depicts a flow from one set of values to another. The two items being connected are referred to as “nodes.” The connections are labeled as “links.”

To give you an idea of what this looks like, here are some examples of Sankey charts:

This chart shows how the impression share is broken down by device type and match type in the same chart.

Let’s look second example:

In the second example, we can see each device type broken down by gender and age demographics.

To reiterate, there are two main components to a Sankey chart:

1. Nodes: The vertical bars in these examples are the nodes. The size of the node reflects its value, similar to a bar graph.
2. Links: The horizontal flow lines that connect nodes are the links. The color of the link matches the node. The wider the link, the higher its value.

There are multiple data structures that can be expressed with a Sankey diagram. Two of the most common are:

1. Evolution: The nodes reflect 2 or more periods or stages. The links between the stages allow you to see how the nodes have evolved over the different states.
2. Source to end: Total amounts are reflected on the left side of the chart as nodes. The flow of the links show where that amount goes and ultimately ends up. Some source to end Sankeys will also have an intermediate step in the middle of the chart.

How To Import/Export Data From Google Ads To Google Sheets

Building Google Ads reports in Google Sheets is incredibly useful and will allow you to utilize charting options, such as the Sankey diagram.

However, before you can begin charting your PPC data, you first need to export it from Google Ads and then import it into Google Sheets.

This section will provide step-by-step instructions on how to do this.

Step 1: Log into your Google Ads account.

Step 2: (Optional) If you manage multiple accounts, select the one that you want to export data from.

Step 3: Click on the “Reports” link at the top of the screen. Then choose the “Custom” menu choice.

Step 4: From the resulting drop-down menu, select “Table.”

Step 5: If you followed along so far, you should be looking at a blank report.

Step 6: Go ahead and give your report a name.

Step 7: You’ll notice along the left side, you have different measures, dimensions and other details that you can plot. You can simply drag-and-drop the data that you want to analyze into the blank space.

Step 8: You can apply filters to the data by clicks on a column header.

Step 9: The next step is to download the data as an Excel .csv file. Be sure to give the file an appropriate name so you can find it later.

Step 10: You have two options to import the data into Google Sheets. First, you could open the file you saved in Excel and then copy-paste the report into Google Sheets. Alternatively, you can click on “File” in Google Sheets and then select Import. From there, simply upload the file and the tool will do the rest.

Step 11: When your imported data first appears in your spreadsheet, you may have to remove the first two lines at the top, as shown in the image below.

You have successfully imported your Google Ads data into Google Sheets. Your spreadsheet should now look something like this:

How To Draw A Sankey Chart In Google Sheets With ChartExpo

Now that we’ve got our data finalized in Google Sheets, the next step is to turn the raw and difficult-to-work-with spreadsheet into an engaging and helpful Sankey diagram.

PPCexpo offers advertisers a Google Sheets add-on called ChartExpo. This tool gives you far more charting options than the normal selection that is pre-loaded in Google Sheets.

If you haven’t already installed this add-on, you can visit the ChartExpo page in the Google Workplace Marketplace.

It’s worth mentioning that, If you prefer to work with your PPC data in Excel, there is a ChartExpo plugin that works with this tool as well.

Once you have the ChartExpo add-on installed, here are the steps to begin using it within Google Sheets:

Step 1: In the top toolbar of Google Sheets, you should see an option for “Document add-ons.” Clicking this will create a drop-down with all of your loaded add-ons.

Step 2: Hover over the “ChartExpo™” choice and then press “Open” to get started.

Step 3: When the ChartExpo add-on loads, it will appear along the right side of your spreadsheet. You can press the large, circled plus sign to begin creating a new chart.

Step 4: Select “Sankey Chart” from the collection of chart categories, as shown below.

(Optional) If this is your first time using ChartExpo, take a minute or two to explore all of the other charting options available with this tool.

Step 5: Select which columns to use for the Sankey levels, as well as the metrics that you want to measure.

Step 6: Once you’ve arranged the data into the right options in the ChartExpo interface, press “Create Chart” at the bottom of the menu.

The resulting chart should look something like this:

Step 7: You may notice that the chart looks a little dull and is lacking certain elements, like a title, level headings, colors and more. Click on the “Edit chart” icon in the top menu, as shown in the following image:

Step 8: To add a title to the chart, click on the edit pencil next to “Chart Header” at the top of the diagram.

You can change the name of each level as:

Step 8: After clicking the “Edit Chart” icon, you’ll enter editing mode. You’ll notice pencil icons next to each chart component. Clicking on these will allow you to edit each individual node color, header, etc.

For example, the following screenshot shows you the editing properties for the first level of nodes.

Step 10: When you’re finished editing your Sankey diagram in Google Sheets, exit the editing mode. Now, you have a polished PPC Sankey chart that is ready to be included in your reports.

When Should You Use A Sankey Chart

Every chart has its own advantages and purpose. As mentioned, you need to think about what chart to use for each set of data. Let’s further explore the times when it is best to use a Sankey diagram to chart your Google Sheets data.

• You can use the Sankey chart to visually express a complex process, while focusing on a single metric, aspect or resource. For instance, if your team is making a decision about ad spend or even how time is being managed in the group, a Sankey chart is a great way to show the breakdown of this information.
• One of the coolest features of the Sankey diagram is the multiple levels that you can view the same data from. You can get a high-level view, as well as a more zoomed-in view of the data in one chart. Interactive elements will allow people to drill down as deep into the data that they want or need.
• When you want to measure dominant contributors in your campaigns, the Sankey chart makes these top-performers stand out. This helps you identify areas with the largest opportunities.

When Should You Avoid A Sankey Chart

Along with knowing when to use a Sankey diagram, you should also know when it is not applicable or helpful for charting your data.

• Sankey charts can first appear overwhelming, especially if you aren’t familiar with this type of visualization. Audiences may not be as comfortable with Sankey diagrams as they are with bar, line, pie or scatter plot charts.
• Since not everyone is used to seeing a Sankey chart, you may have to spend some time explaining what is being shown. This is particularly true if your Sankey chart involves many different nodes and levels.
• Sometimes, the links between nodes become so crossed that it is difficult to compare flows, especially when their values are very similar (the widths are the same).

Things To Consider Before Making A Sankey Chart

To be absolutely certain that a Sankey chart is the best option for your data, there are a few things that you should ask yourself:

• Are there any other options in the ChartExpo library of charts that better depict the information?
• Do you want the audience to perform a certain action, or are you just trying to tell the story behind your data?
• Does your audience have enough experience with PPC charts to make sense of a complex Sankey diagram?
• Charts are designed to make a fact very evident. What fact does your Sankey chart best display to viewers? Is this what you intended?

Wrap Up

PPC data is complicated and spreadsheets only minimize this complexity slightly. For certain data sets, there is no better option than to visualize the information with a Sankey diagram.

A Sankey chart is effective at breaking down processes and showing the evolution of your PPC metrics.

You can effortlessly create Sankey diagrams in Google Sheets thanks to the ChartExpo plugin. This also gives you access to many other charts designed specifically for PPC marketers.

Charting data not only helps you understand the stories behind your data, but it also means you can create more engaging reports to pass your findings on to stakeholders, team members and clients.