Data is a double-edged sword. On the one side, data is extremely valuable and allows you to learn issues in your existing processes to improve them. The not-so-nice side of data is that it is incredibly difficult and time-consuming to extract actionable info from it. In this how-to guide, you will learn How to create Sankey Diagram in Google Sheets to better understand how data helps you make insight-driven decisions regarding your business strategy.
Let’s learn how you can create Sankey Diagram in Google Sheets using your data.
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.
Definition: 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 reiterate, there are two main components to a Sankey chart:
There are multiple data structures that can be expressed with a Sankey diagram. Two of the most common are:
We assume 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 Researchers, Data Analysts, Students, and everyone else 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. What more is that you do not need any coding to draw complex visualizations as you can save your time and cost by visualizing your data using beautiful and intuitive visualizations like Sankey Diagram in few clicks.
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 data in Excel, there is a ChartExpo plugin that works with this tool as well and there is a complete guide for creating Sankey Chart in Excel 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 Extensions. Clicking this will create a drop-down with all of your loaded add-ons and extensions.
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: Once clicked, you will see list of available charts. Select “Sankey Chart” from the list of charts, 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 Sankey chart that is ready to be included in your reports.
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.
To give you an idea of how Sankey Visualization looks like, here are some examples of Sankey Diagram:
In the above example, we can see the energy flow in each phase from production source to end usage.
In the above example, we can see each device type broken down by gender and age demographics.
This chart shows how the impression share is broken down by device type and match type in the same 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.
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:
The real problem is that your business processes produce 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 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.
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 charts can benefit your organization:
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:
Now you can use this data to create Sankey Diagram in Google Sheets.
Data is complicated in its raw form and spreadsheets only minimize this complexity slightly. For certain data sets, there is no better option than to visualize the information with a visualization e.g. Sankey diagram that will tell you the whole story in data.
A Sankey chart is effective at breaking down processes and showing the evolution of your complex metrics.
With the help of this how-to guide, you can effortlessly create Sankey Diagram in Google Sheets.
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.
We will help your ad reach the right person, at the right time
Related articles