Data Management and Cleansing For Your Data Diarrhoea!
Yes, you read that right…and yes it exists…and I have a cure!
Who wants to talk about Big Data? It’s been overused and lost its meaning.
Why would you want to work with huge tables of data when you can create insightful small data groups and pull insights from those?
This is more about data cleansing & analysis techniques and data management tools than it is about the reports shown below. You’ll be able to use these techniques on numerous other data sets and achieve the same outcomes.
Data Basics
The basics
Data is one of the most essential assets an organisation can own, in fact it should really have its own line item in the balance sheet. We all optimize our businesses and accounts based on the data we collect and how well we’re able to read it.
Simple data
As simple as this data format is, it can still be highly valuable. Combine enough simple data tables and you’ll start creating complex data tables.
An example of simple data; owning your clients’ email addresses or knowing your industry seasonality trends. Or what days of the week and times of the day you get more conversions.
Complex data
Complex data is the most valuable type of data an organisation can own. To effectively manage complex data though quite often requires the use of a CRM system – but more on that another day.
An example of complex data; knowing which of your customers spend the most, how often they repeat purchases, as well as the channels most effective to reach those people.
Data Management and Analysis Tools
Starting with Google Sheets, you can use any number of scripts (like this one) to pull your data into Google Sheets and integrate tools like Google’s BigQuery to draw insights from the data. That’s all good and well, but we’ve all used Google Sheets and know how inconvenient it can be at times.
That’s why Excel is still one of my favourite tools.
Analysing competitor activity
Google doesn’t like you to know too much about what your competitors are doing so only provide the Auction Insights report. This report is not high-quality data, it’s quite boring as it doesn’t actually tell you anything. But download this report, add a segment for Day and Device and we can make some magic happen.
NOTE: if you take too large a time frame (more than 5 weeks or so depending on data volumes) and segment by day, Google will sample the data.
The final export will not be pretty, so now it’s time to perform some data cleansing.
- Start by replacing all “<10%” with “5%” (we’ll assume that an Impression Share of <10% could be 5%).
- Next, we’ll add a new column to the end of the table and call it “Occurrences”.
- Use the formula =COUNTIF(range, criteria) and send this all the way down to the end. This will allow us to later filter out competitors who seldom appear in the same SERPs as you.
- Next up, create a Pivot Table of all the data using the following layout:
Filter = Devices; Occurrences;
Columns = Display URL Domain;
Row = Day;
Values = Sum of Impr. Share.
- Apply a filter on the Occurrences so that only the 6 highest numbers remain. As previously mentioned, we’re now filtering out competitors who seldom appear in the same results as you. This data cleansing will also allow you to generate a Small Data set with far more meaning to you and it’ll be easier to draw insights from.
- The final step involves generating a chart from the Pivot. Your filters should still be applied so you should end up with a good looking graphic representation of your competitor landscape by device. Add any custom styling and then paste it in your reports. Quick and easy!
NOTE: you can include as many competitors in the chart as you’d like, but things tend to get messy the more you include.
Analysing times of day and week
I’m hoping you’ve all used this, but if not, do this one right away!
Everyone knows that you can get time of day data and day of week data in the Dimensions tab. But how does that tell you whether your campaigns are performing better on a Monday lunch time, or a Thursday morning. Is there any point in running your campaigns on a Sunday morning on mobile devices?
Luckily it is possible to pull account data by Time of Day and Day of Week and compare this by device, navigate to your Campaigns tab, download the report for the last 28 days, adding a segment for Time of Day, Day of Week and Device.
NOTE, it is very important to have the same number of days of the week or your data will be skewed and you’ll lose all data quality.
Create a Pivot Table from the export (making sure not to include the Totals rows at the bottom of the export). The format you use is kind of up to you and what data you’re comparing but this is the format I’ve used to compare each device alongside the others:
Columns = Device; Day of week;
Rows = Hour of day;
Value = Conversions or whichever other metric you are testing.
The resulting table is decent enough, but if you’re like me, a bit of colour helps identify a pattern quickly and accurately. Highlight the similar data you are analysing and in the top Excel menu, select Conditional Formatting > Colour Scales > and chose the appropriate layout. As higher values are more important, I chose green to highlight these values. Do not highlight the Total columns and rows since these numbers are so much higher than the rest of the cells, all other cells will look like “bad performers”. Rather select these rows and columns separately and repeat the above steps.
How to use data
Having data such as your clients’ email addresses, is almost as insignificant as you think it is, but can be a very important optimization element. Email addresses could be used for EDMs or they can be uploaded as a customer match list in AdWords or Facebook. You could then apply bid modifiers to either re-target these existing clients or neg the list to ensure that you’re only targeting new customers.
Time of Day, Day of Week and Device can easily be used to optimize accounts towards the most profitable times per device. Being able to compare competitor activity by device could allow you to sneak into a tough or highly competitive market on a low competition medium. Impression volumes may be low, but so are the costs while this is improving your ROI.
Data preparation
Preparing your data correctly can also make a massive difference in whether or not you’re able to identify patterns.
For instance, take the ToD/DoW by device report above and you’ll quickly pick up that computers are the primary device during office hours, while mobile devices get higher usage in the evenings, peaking on Saturday and Sunday mornings. Would you have been able to identify that trend without conditional formatting? I doubt it.
Looking at the Competitor report by day as opposed to week you’ll quickly be able to identify which competitors push harder on a particular day of the week. You’ll also notice that your conversions are down on the same days and your CPCs are up. Does this mean that your competitor is stealing your sales and this is now an unprofitable day to compete? Or are you going to push even harder and get your customers back? You may also notice that you’ve recently started losing Impression Share, pull the report without filtering for Occurrences and investigate whether a new competitor has entered the arena.
The saying “Data, data everywhere and not a Byte to drink”, as funny as it is, is sadly true. Don’t just throw around numbers and tables, they mean nothing to anyone but their creator (and quite often not even them!). Rather create a graphical representation of it in a format which is easy to draw insights from. If you can’t look at the representation and see a pattern then you’re doing it wrong.
Get in bed with your data, get comfortable with it, clean it, care for it, and then you’ll finally cure your case of data diarrhoea!
Want to read more? Take a look at some more insights from our Google AdWords gurus and follow us on LinkedIn today!
