This is the third lesson from the Google Sheets for marketers mini course. In this chapter you’ll learn how to implement and customize charts to visualize your insights. As the famous saying goes “a picture is worth a thousand words.” Visualizing data will be useful in itself as it makes finding actionable insights in huge data sets a lot easier. Furthermore it is essential when you want to define and communicate next step recommendations based on the data to other stakeholders or your clients.
What you’ll learn:
Regular charts, combo charts with two axis, chart trendlines, SPARKLINE, custom change indicators, interactive charts.
Visualizing Budget Utilization
As for the other lessons this workbook contains all exercise data (please make a copy to work a long).
As an easy start you are going to visualize the 2018 budgets vs actual costs for the three advertising channels: Facebook Ads, Google Ads and Twitter Ads.
Select cells A1 to C4 in sheet Solution – Charts. Now choose Insert → Chart in the top menu.
You’ll get a simple column chart comparing budget utilization for the different channels. If you want to you can customize it further by clicking on the tab Customize on the right side in the Chart editor. E.g. click on one of the red bars and choose another color than red in the dropdown menu in the chart editor on the right side.
This is basically how it works for all elements of the chart. Click on it and customize the style on the right side. However we won’t focus on these fundamentals in the following, but will rather do some little bit more interesting stuff in this lesson (if you want to learn the fundamentals, check out Google Sheets help).
Btw. if you ever wondered, which chart you should choose in general have a look at this awesome infographic from A. Abela.
Average Order Value vs. Conversions
As we learned in the past lessons the Facebook Ads performance has been quite bad due to low Average Order Values and the low number of Conversion. As such we want to visualize this to show the development over time and to potentially communicate this to other stakeholders. You are going to use a so called combination chart for this. In a combinations chart you can display two series using different formats (e.g. bar chart and line) as well as two axis.
First insert a pivot table by clicking into any cell in the Worksheet – Raw Data tab and choosing Data → Pivot table from the top menu. Select Existing Sheet and write ‘Solution – Charts’!A21 into the empty box before clicking Create.
Choose the following settings in the pivot table editor:
Rows: Month (Deselect Show totals)
Values: Conversions, Average Order Value
Filters: Channel and only select Facebook Ads
(If you are not sure what to do here or need a recap check out my first lesson on pivot tables)
Select cells A21 to C60 and insert a chart via the top menu (Insert → Chart). Now on the right side in the first dropdown menu of the chart editor under Chart type choose Combo chart. It is the third item in the first row.
Delete Month as a series by clicking on the three dots next to Month on the right side and choosing Remove. Instead choose Month as the X-Axis by clicking in Add X-axis above Series and selecting the range A21:A60. Click OK.
You have your first combo chart! However Average Order Value is a little bit hard to read as the left axis shows a number (not currency) for both Conversion and Average Order Value.
As such click on the red line. On the right side below Axis choose Right axis instead of Left axis. This will add another vertical axis on the right side showing currency values.
While it’s quite clear to see that there is a strong drop in Average Order Value, it’s unfortunately not as clear for Conversions.
As such click on one of the blue bars add a trendline by checking the box next to Trendline on the right side to visualize it better.
You now have a ready chart, which you could use to visualize your insight that Facebook Ad performance is going down. As such it is ready to be copied into a slide deck, email or wherever you need it.
Comparing Facebook Ads year-over-year performance
Sometimes you don’t want to build a full chart, but rather want a quick visualization of the data in Google Sheets itself. E.g. in our case we quickly want to compare the year-over-year performance of the Facebook Ads. Write the following in the respective cells:
2016 in cell A14
2017 in cell A15
2018 in cell A16
2019 in cell A17
Seasonality in B13
In cells B14 to B17 we will use the SPARKLINE formula to visualize number of Conversions over the months in the respective years. Sparklines are mini-charts which exist in a cell itself. They are perfect to visualize trends or seasonality. Write the following formulas:
=SPARKLINE(B22:B32) in cell B14
=SPARKLINE(B33:B44) in cell B15
=SPARKLINE(B45:B56) in cell B16
=SPARKLINE(B57:B60) in cell B17
We also want to visualize the trend in year over year growth in the most recent month April. As such write the following in the respective cells to calculate year over growth for April of each month:
YoY April in C13
=B36/B24-1 in C15
=B48/B36-1 in C16
=B60/B48-1 in C17
Select cells C15 to C17 and in the top menu click on Format → Number → More Formats → Custom number formats. In the popup paste the following into the box and click Apply afterwards:
[color50]0% ▲;[red]-0% ▼;[color40]0% ▬
What this does is it will apply the colour 50 (=green) and the symbol ▲ to all cells with a positive value, the colour red and the symbol ▼ to all cells with a negative value and finally the colour 40 (=yellow) and the symbol ▬ to all cells with 0% as value. Those symbols are actually interchangeable with whatever symbols you like. E.g. you could also use ↑ , ↓ and ↔ instead. Same goes for the colours.
Building an interactive chart
In the last lesson you built a small tool for comparing the performance metrics for two advertising channels. We’ll use that to build a dynamically controllable chart.
Write the following formulas in the respective cells:
=B8 in cell B10
=C8 in cell C10
=B9 in cell B11
=C9 in cell C11
=A9&” for “&B8&” is “&round((B9/C9),1)&” times the “&A9&” for “&C8 in cell A11
Even though it looks fancy the last formula does nothing else besides linking different cells and text (everything between two quote marks is considered text) with each other. Select cells A11 to C11 and change the Text color to white.
Next select cells A10 to C11 and insert a chart via the top menu. Switch rows / columns by checking the box next to it in on the right side in the chart editor.
Now go to the Customize tab on the right side of the Chart editor and open Legend. Choose Top in the drop down menu for Position.
Now try choosing something different in cells A9, B8 and C8. The chart including the legend will update instantly and dynamically! Obviously styling and formatting could be improved. However you get the idea and can imagine how interactive dashboards could be build with these techniques.
We will actually use some of these to build an interactive dynamic performance reporting dashboard (which you can share directly with clients or other stakeholders) at the end of this course in lesson 5.
