Continuing with my dashboard series that began on Search Engine Land and continued over to Marketing Land when the entire analytics column migrated to Marketing Land, this post is going to talk interactive charts. When you’re creating a dashboard, real estate is minimal, so you find ways to consolidate your data and allow the dashboard user to manipulate a single chart different ways.
Excel offers a panoply of interactive controls that make this possible. I’ll be the first to admit that working with interactive controls were really intimidating to me when I first started. They seemed to have a lot of moving parts. But once you understand how they work and learn a couple basic functions, they’re actually quite intuitive to use.
The Chart We’ll Be Creating
Here is the chart you’ll learn how to create in the video below:
Functions You Need
Here are a few functions I use on a regular basis with interactive charts:
VLOOKUP: This is arguably the Swiss army knife of Excel for marketers. It allows you to pull values from a data set by matching one common data point from another data set. You can learn more from this Excel resource by Distilled.
INDEX: The INDEX function is a more flexible version of VLOOKUP. You can learn more about how to use it here.
IF: The IF function allows you to apply Boolean logic to your data. You can learn how to use this powerful function in marketing here.
I only used the INDEX and IF functions to create the chart pictured above.
Example Uses
There are so many ways you can use Excel’s interactive charts with analytics data. For example, you can set it up to only show traffic (or revenue or whatever) from a particular:
year
country
search engine
medium
social network
platform (mobile, tablet, desktop)
You get the idea. Basically, any way that you can segment your data in your analytics software, you can segment in Excel charts or tables using interactive controls.
Excel File To Download
If you want to download the Excel file I use in the demo, you can get it here. I have it broken into three sheets:
Raw Data
Calculated Data
Chart
Feel free to poke around a bit. I highlighted cells with formulas in them as visual cues. But the idea behind the chart is you use an IF statement to match the metric chosen from the drop-down. If it matches, you pull in the data from the Raw Data sheet; if it doesn’t, you use NA(), which fills the cells with the #N/A error, which Excel ignores when charting. It will (hopefully) make sense when you watch the video below.
Data Prep
If you want to use the Google Analytics (GA) custom report I used for this tutorial you can get it here. Just make sure you’re logged in to GA in the the browser in which you open the link, or you’ll get a nasty 404 error.
Caveat: The format of GA’s Month of Year dimension is jacked up because they don’t use separators, like a dash or forward slash, to separate month and year. So you’ll need to use this hack of the DATE function in Excel to clean it up and make it useful. Then I just used custom number formatting to format the date (mmm yy). You’ll also be able to see the formula in the Excel demo file download.
Comments