The motivational quote encourages us to “think outside the box”… But for once, I want you to think behind the box. Have you ever thought about the cogs, bots, artificial intelligence and other algorithms that are getting in motion every time someone hits the “search” button? The computing forces in motion are staggering and the amount of data that is crunched and produced is even more. Lines and lines of data, with in each of them a little piece of insight that could make all the difference in your marketing strategy. If only… If only you could overcome the spreadsheet blindness and see them.
Several of my posts in State of Digital are about visionary topics: the rise of AI, predictive modelling applied to individuals, digital transformation and the lessons that search engines could give to other businesses… This month, I was keen to be more tactical, and explore a little bit further the capabilities of the beloved Excel when it comes to making sense of large amount of data.
Picture that emotion
Advertising is all about creating an emotional bond between a brand and consumers. And since the infancy of our discipline, both marketers and agencies have been trying to visualize this connection. Focus groups, vox pop, surveys, research… They all had a go at it. But personally I never got fully satisfied by them. As a matter of fact, I don’t believe that people give you access to their core beliefs when prompted over the phone or in the street.
Then Social Media arose. And you must admit that they are simply great for that corollary use. After all, what are these platforms but the depository of our intimate sentiment?
So obviously you can have a quick peek at Facebook and look up for the fan pages for your brands. That is straightforward as people are overtly expressing their sentiment towards the brand through these pages. They revive dormant products and re-energize old brands. They also virtually stone others to death.
But what if you are interested in emotions more deeply engrained in consumer minds? And looking at visualizing them? Personally I used to visit photo sites like Flickr or Instagram and run a query on a given brand. Type Bailey’s and you will get hundreds of pictures showing up. The resulting mosaic is fantastically enriching. You will of course see people sipping their favorite liquor but also loads of pictures of dogs and cats named after that brand. What a better proof of brand advocacy than to name your beloved pet after a trademark? Or to tattoo the swoosh on your hip or a Harley Davidson eagle on your shoulder?
Think outside the (search) box
This digression meant to make a point: we can find consumer insights everywhere. You just need to be a bit creative to see them. I remember discovering Bing Ads Intelligence tool, and having one of these ah-ah moments.
This tool is by essence a brilliant search marketing tool that enables search marketers to make more informed choices when creating a campaign: based on historical and forecasted data from Bing search queries, it provides you for any keyword with traffic volumes, demographical and geographical information about the searchers, even indicative CPC for the different positions in the auction… And it is free!
If you have not downloaded it yet, I would strongly recommend you do. Even if you are not working in search marketing. In fact, I should say “especially if you are not working in search”.
This little freeware can indeed help a lot of marketers out there. As a matter of fact, in the current economic climate, when costs are cut to their bare minimum, can you afford to research what your audience’s actual demographic profile is and where they live? Are your pockets deep enough to run a regular research to audit your brand awareness against this audience?
Search engines are for finding
Digital expert and author John Battelle once qualified search engines as the database of intents. With over than 28m unique users in the UK alone, Bing offers you a statistically relevant sample of these intentions. So why not use the Bing Ads Intelligence to run your own piece of research? It provides you access to actual logs, so you can use them as proxy for your consumer intents. How many consumer have searched for your brand in the last month, and how many have for your competitors? That will provide you with a good indication of your brand awareness. Did they search on a PC or from a mobile device? Have queries increased after your latest local TV campaign? Was your regional billboard campaign efficient? Where are visitors searching from? London, Liverpool, outside the UK?
A lot of these questions can be answered and visualized by pressing a button in Excel. Three actually.
Three Excel features to make your data click
The first click should be on Bing Ads Intelligence. As said, you just have to enter a word (a brand for instance) and choose what you want to know: demographics, device usage… And since you are in Excel you can rapidly turn the data into a compelling visualization like this:
Now knowing more about who searched, my second click would be on another Excel free tool, PowerMap. If you are running Office 365, this is a native feature in your ribbon. If you are using an earlier version, you can download that add-on for free. Power Map is a 3D visualization add-on for Excel for mapping, exploring, and interacting with geographical and temporal data, enabling people to discover and share new insights. It allows Excel to automatically chart the search data to see where the customers who are searching for you are located, where your revenue is generated, where prospects are congregating, etc. I particularly like that if you have your data points for several periods, you can turn your map into a little video which will illustrate the evolution of your chosen KPI over time. The brilliant thing in this feature? Excel does all the work for you. Leveraging Bing technology, it recognizes the geo-information present in your spreadsheet and plots the relevant data points on a map accordingly.Of course, neither tool will ever replace a full professional research or extensive monitoring application, but these are valuable indicators and visualizations for a superior desk research. Personally I find these free tools simply brilliant for those who are always looking for innovative ways to increase cost-efficiently their agility, identify new opportunities and niches.
Who, where and now when?
My third click would be to dig into the question of time and create a day parting heat map using the conditional formatting of Excel to rapidly identify when is my marketing sweet spot. How do you do that? Conditional formatting is a brilliant function that allows you to automatically change the format of a cell, based on values and parameters that you dictate. So let’s say that you have a search account report that you downloaded from Bing Ads with Hour of Day as the unit of time. It would originally look something like this (painful to watch you would reckon):
Highlight each column individually and use conditional formatting. We then see a picture of account performance by time of day, and a heat map emerge. I’ve left the Clicks, Impressions, Cost, and Converted Clicks columns untouched right now, as I prefer to display those with a different formatting. The red vs. green distinction is a little too arbitrary for these numbers, so I go with a bar graph to show them instead.
Now this chart tells us a story. It portrays the ebb and flow of our daily traffic, and it clearly shows us both where we can pull back on our bids with day-parting modifiers – early morning from midnight to 4 AM – and where to be more aggressive. We have a real lost opportunity here starting at 4 PM to 8 PM to increase our bid modifier and gather even more traffic. Our Average Position and Average CPC metrics let us know exactly when our competitors are ramping up their spend, and the hours in which we should do the same… until about 10 PM, when our conversion rate drops again.
This report takes less than five minutes to pull, and no custom modifications were made to these rules aside from highlighting each column individually and selecting the right formatting. Day-parting is the easy and obvious use for this kind of analysis, but it can also assist in ad reviews, geographic performance… really, in any case that leaves you staring at a spreadsheet for hours on end, mining for insights.
To go further
So you’ve manipulated and made your data more digestible. It has worked and you have identified some interesting data points but now you want to tie this in with the rest of your company’s data sources and tell a story. That is when tools like Power BI become handy. It indeed allows you to connect your data with a wide variety of data sources, from local on-premises databases to Excel worksheets to cloud services. Currently, over 59 different cloud services such as Facebook and Marketo have specific connectors, and you can connect to generic sources through XML, CSV, text, and ODBC. Data.gov for instance is a great base of public domain data set that you can mash with your own.
So what can this mean? How about comparing the demographic profile of your audience with the latest census? Identify the correlation between your customer engagement and the weather variations? There is so much data out there, it is really just about letting your creativity unleash the power of insights. Or when data becomes clear, and actionable.