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.
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).
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:
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 typechoose 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.
http://analyticalmarketer.io/wp-content/uploads/2019/05/Screen-Shot-2019-05-26-at-5.54.52-PM.png15183250mmehttp://analyticalmarketer.io/wp-content/uploads/2018/10/AnalyticalMarketer.io-data.pngmme2019-05-26 16:11:272019-06-17 14:03:35Google Sheets charts for visualizing marketing data
Click on the different category headings to find out more. You can also change some of your preferences. Note that blocking some types of cookies may impact your experience on our websites and the services we are able to offer.
Essential Website Cookies
These cookies are strictly necessary to provide you with services available through our website and to use some of its features.
Because these cookies are strictly necessary to deliver the website, you cannot refuse them without impacting how our site functions. You can block or delete them by changing your browser settings and force blocking all cookies on this website.
Other external services
We also use different external services like Google Webfonts, Google Maps and external Video providers. Since these providers may collect personal data like your IP address we allow you to block them here. Please be aware that this might heavily reduce the functionality and appearance of our site. Changes will take effect once you reload the page.
Google Webfont Settings:
Google Map Settings:
Vimeo and Youtube video embeds: