Every analyst has been there – you’ve put a lot of hard work into some insightful analysis for your clients, but they just don’t understand what you’re trying to say. How you visualize your insights can be equally as important as the wonderful data you’ve found. There are numerous wonderful data visualization tools out there, but the one I come back to time and again is good ol’ Microsoft Excel.
This is Part 1 of 3 in a series of some of my favorite tips for visualizing your data in Excel, specifically looking at forecasting, showing lost potential and measuring impact. Today’s post will cover forecasting.
For the purposes of these posts, I’m going to use a fake client, Tara’s Mortuary:
The Problem
One problem that I run into all the time is proving to the client the long-term value of a campaign. For example, say that I am using AdWords’ tablet targeting options to run a tablet-specific campaign for Tara’s Mortuary. I have seen in Google Analytics that tablet users convert well organically for my client, so I am fairly confident that they will convert well for my paid campaigns. However, my campaign is off to a slow start, with a very high Cost-Per-Lead (CPL) and a low Click-Through-Rate (CTR). I have been optimizing however (adding new ads to improve CTR, optimizing landing page to improve CPL), so these numbers are improving a bit. I need to prove to my client that with continued optimizations, we can get these numbers to their targets.
Trend Lines to the Rescue!
To do this, I will use trend lines in Microsoft Excel to forecast when we will hit the client’s targets.
This is how my Excel might be set up. Please note that I added more weeks than I have data for – this is in anticipation of my trend line. Without doing this, my forecasted weeks would not be labeled. Also, notice how I have added a value for the weeks I made optimizations – this is just to mark these dates in Excel. To mark dates in this manner, you will just need to use the highest max value for an axis on the date you want to mark. This may become clearer when you see the chart (below).
I added the drop-down lines for the optimization dates to my chart by using Error Bars. You can also see here the point of adding those Target CPL and Target CTR sets of data. I have also done a fair amount of formatting to this chart. The formatting is fairly intuitive so I won’t dwell on it here – but please ask if you have questions on how to do this.
There are a lot of different ways to add trend lines, but the easiest (in my opinion) is by selecting the line you want to add a trend line to, right-click, and select “Add Trendline…”
It will automatically open up the trend line formatting toolbox. There are quite a few options for trend lines within this toolbox.
For this example, we are going to use a simple linear trend line. However, it is a good idea to research and play around with the other trend line options. Here are some situations in which I use the other trend lines:
- Polynomial: This shows the trend for lines that rise and fall. The “Order” is roughly the number of rises and falls in your data – for example, if your data rises, then falls, then rises again, you will want to set this to 3.
- Moving Average: I use this often when I am looking at daily data, because daily data tends to fluctuate so much within a given week. Setting the “Period” to 7 will give an average over the course of 7 days, providing a nice trend line which has the same growth and decline as your daily data, without showing large rises and falls throughout the week.
As a rule of thumb I always give my trend line a custom name (selected in the screenshot above), and format the line color and style to make sense with the rest of my data. Below is the result!
I Got Your “Wow Factor” Right Here…
Thanks to our trend lines, we can prove to the client that if we continue the campaign through week 19, we will very likely hit our target CTR and CPL. When you do this, you may need to adjust the number of weeks you show based on when your trend line actually crosses the targets. You may not need the target lines or optimization dates at all – I find them helpful but sometimes they can be seen as clutter by the client, depending on how easily your client absorbs information.
What do you think? Have any favorite tips for showing future data to your client? We’d love to hear them!
