Troubleshooting Lookup Functions in Excel
It’s easy to get discouraged from using Excel when we run into errors. This post guides you as you troubleshoot your Lookup functions so you don’t refrain from using them!
A few months ago I wrote a guest post for Portent Interactive about the data discrepancies between Google AdWords and Analytics. It’s still something I deal with every day when trying to analyze and make account decisions and I wanted to share this post with our readers.
In a touching speech by President Jack Nicholson, he asks alien invaders “Why can’t we work out our differences? Why can’t we work things out? Little people, why can’t we all just get along?” Although things didn’t end so well for him, this is often how I feel about the relationship between AdWords and Analytics. They both provide excellent information that is extremely valuable to anyone managing a PPC account, so why oh why can’t they just get along.
Last week I was hit head on with the data discrepancies between the two interfaces when I went to implement ad scheduling for an E-Commerce client. I wanted to analyze hourly performance to determine if there were times throughout the day when we could either reduce bids or pause ads to better utilize our budget. Sounds easy enough right?
I first downloaded conversions by hour in AdWords. Below is a screenshot of the results:
The account is spending the majority of its budget at 12am, at a cost per conversion of $465.89. This amount seems high, especially compared with other hours of the day, but I wanted to know the value of these conversions before I made any decisions. I pulled revenue data by hour from analytics to calculate ROI and this is where things got interesting:
The rows highlighted in yellow are hours in which AdWords recorded conversions, but no revenue was generated. The row highlighted in blue is when AdWords recorded no conversions, but the account brought in almost $300.
This discrepancy is due to the difference in how AdWords and Analytics attribute conversions. AdWords assigns conversions using first click attribution whereas Analytics uses last click attribution. For example, lets say a person clicked on one of our PPC ads, viewed our products, went to a competing site to compare prices, then came back to make a purchase by typing our website into the search bar. AdWords would attribute the conversion to the time the PPC ad was clicked. Analytics however would attribute it as a direct goal conversion.
This is where I had to look at the whole picture and start making some decisions. We were spending a lot of money at 12am, but I didn’t want to completely turn off ads at this time. Some of those clicks were turning into conversions at some point throughout the day. I just wasn’t able to tie this information to revenue Analytics.
Ultimately I decided to decrease bids during hours with a high cost per conversion, whether revenue was generated or not. I only turned off ads during times when no conversions or revenue was reported. I also decreased bids for any hours where revenue was less than cost. Bids were left at 100% for all other hours of the day.
Even though AdWords and Analytics data will never match up, it is important to look at both before making account decisions. Using just one or the other only shows you a part of account performance. Maybe one day these two will learn to get along but until then we will have to use the data available and our best judgment when making changes to our PPC accounts.
Browse By Category
In December 2017, Google released a set of statistics about the Christmas shopping season. Use these insights in AdWords to make this your best December ever.
Google's First Page Bid Estimates can't be trusted. Adjust your bid strategy based on average position and not using this metric.
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
Organization is key to success in the PPC industry. Here is a guide to organizing your space, time, resources, and workload for efficient PPC management.
Proper Work-Life Balance Is Something Easily Forgotten. Here Is A Reminder That It's Okay To Have A Life Outside Of Work & Constantly Checking Performance.