Day of the Week Reports
Posted on: September 11, 2012
Managing a great Adwords campaign by hitting your target conversion cost or a click through rate doesn’t mean that you have squeezed the most out of it. Even with a campaign that is performing great, chances are you are there is still room to optimise to not only gain you more leads but also save you a ton of money.
You have your weekly data and you are proud of it. Your CPA and conversion numbers look good, quality scores are fine, you are testing a few more ad variances and all seems rosy.
I was recently going through a my favourite Day of the Week report and found a client an extra $3k per month that they could either save, or reinvest. What would you do with an extra 3k per month to play simply by understanding a bit of data? So lets go:
First pick a good solid date range. If you get a fair few conversions, 1 month worth of data should be fine. If you are selling a high ticket item and only receive a hand full per month then you can look at a a few months worth of data to make sure its statistically relevant.
Once you have selected your date range pick the type of report you want to analyse. Will you be looking at conversions on a campaign level, adgroup level or keyword level? I will be using campaign level reporting but you can use whatever you feel will be most beneficial.
Now hit the little download report icon and a window should open up.
Now this is where things get a little interesting. under the Excel .csv you will see a Add Segment Link like this:
Once you have clicked that you will be able to select from a dropdown menu. Select ‘Day of Week’ in the time section like this:
Download the report and then open it in Excel so make sense of this data.
For the purpose of this exercise I am only interested in conversions so this is the metric I will be analysing. You can choose to use, clicks, CTR, conversion rate whatever.. the steps will be the same.
Once you have opened your document in excel get rid of any unwanted columns and only have the ones you are interested in. This makes it easier on the eye and less confusing when trying to build the reports. If we are doing conversions the most important columns to keep are; Day of week, Campaign and Conversions.
In your excel tabs visit the ‘Insert’ tab in the top menu. You want to select ‘PivotTable’ and then ‘PivotChart’. It will ask you to select the data range. You want to select all the headlines and all the data except the totals. So select everything up until and including the final Day, which in my case is a Saturday:
It doesn’t matter if you choose to view it on a new page or the existing one. Click Ok.
Nothing much will happen but on the right hand side of the screen you will be the PivotTable field list. Check the boxes you want to compare (ie Day of Week, Campaign, Conv.) like the below:
You will start seeing some data populate on the table. Here is the info you want to analyse to work out what days work better than others and where there are inefficiencies. If you find that a Monday performs much better than a Thursday then use that information to amend your bid adjustments for certain days using Google’s Ad schedule setting option.
You can then run another even more detailed report for Hour of the Day. This works the same way as the above however you want to download the Hour of the Day report as opposed to a Day of The Week report.
Have fun with this, learn about what is happening and take your adwords management and efficiency to another level.
William Bakhos is an SEO and Adwords professional and has been implementing best practice online marketing initiatives since 2004. Along with consulting to National and International companies and digital teams, William is the founder of the Web Marketers Crew, a Sydney based optimisation and online strategy company.