12th April 2016

#AndyTalks 030 – Segment your AdWords data!

A late night Skype call last night doesn’t stop me getting excited to go on a course today.

I have to do some reports before I head off, but I’ll give an AdWords tip first.


Morning, snaps! I’m kind of tired because I was up late last night on a call with a client until after midnight.

I’m pretty excited because I’m going on a course today. I’ve been telling the kids, “I’m going to school, daddy’s going to school.” And I’m all excited because I’m going to learn something because I want to learn.

So far, I’ve been on a Facebook course, a LinkedIn course, a Twitter course, an editing with the YouTube editor course. Each of them have been a day long and heavily subsidized by the local enterprise office, forty euros per day, brilliant, I mean probably 200, if they weren’t subsidized. That’s a great deal so I’m always doing these courses every year.

This one today, the last one is editing graphics. Not graphic design, I ain’t going near that. If you’ve got an image, what to do with the image, so that you can upload it on a blog post or YouTube thumbnail or a Facebook blog post, the image that attracts people’s eyes.

I’ve been creating videos, I’ve been creating content, but I’ve got to sell the click now. We’re creating good content, but if nobody reads it, has it really been created? To me, it has because if I don’t get it out on paper, it kind of gets rammed up in my head and I really need to just get it out, but to leverage … My goal is to help more people and to help people more, then I should get it seen by more people, so I’ve got to sell the click. I’ve got to have a good title as a headline and a good image. If you look through your Facebook feed, the ones you’re going to click on have images or videos.

The course is in about 45 minutes. It’s local to me and I have some reports to get out in the morning, but first, one quick tip.

This is looking at the last 30 day stats for an AdWords account I’ve been managing. In the last 30 days, 484 clicks, 325 euros, and a nice click-through rate of 34.6%. Now, when you click on segment, you can segment your data by say, device, which is very handy. You can then scroll to the bottom and click on this little plus to show, on computers, mobiles and tablets, the different stats.

We can see we’re performing differently on the different devices. In fact, mobile is 265 of our clicks. This segment drop down is very handy. Look at all the different things we can segment by. There’s a good one here, conversions. You can segment by conversion name or conversion category. Conversion category might be something like leads or versus sales. Another good one I like to segment by, Top Vs. Other. Was the ad in top ad positions above your listings or was it in other.

Have a look at these particular campaigns of the last 30 days. Google search, top hat position, we are getting all of our traffic from top and hardly any impressions on …

Oh, I said side there. It’s not side anymore. Google got rid of sides ads, you’re either above or below now, so other would be below when your Google.

This account doesn’t have conversion tracking set up. It’s not a problem because I’m tracking the calls and the emails that get sent. I just never got around to setting up in AdWords, don’t care honestly. It’s not the important part, I’ll do it soon, in the next couple of weeks, probably.

Right, but the client I was dealing with last night, that did have conversion tracking set up, so we could see when we segmented by conversion name, where you could see that mobile conversion or this kind of conversion, we could see the numbers of conversions in the last 30 days. Then when we segmented by device, we were able to see that a lot of those conversions were coming from mobiles and the cost per lead was a lot better on the mobiles, it was a lot lower. On a mobile, what kind of conversions were we getting? Were we getting phone calls or are we getting people filling in the form and which form was it?

In the interface, the problem is, you can only segment by device and then find all of your stats by mobile or tablets and computers, or you segment by conversion name. You can’t segment by both at the same time, one or the other. Here’s the trick and it’s quite simple. Use this to beat people who just use the AdWords interface.

Go to the “download” button, add a segment, “conversion name”, add another segment, “device”, then you can add a third segment, “top versus other”. When you download the data, you can actually segment by up to three dimensions. This account doesn’t have conversion tracking in place. I’m going to remove that dimension because it wouldn’t make sense. We’re now segmenting by “device” and “top versus other”. Another one I like to segment by is the actual day, but I’m not going to bother in this case. Now, I’m just going to download this.

Here’s the data. I’m just going to remove this top row which is like a heading. I’m going to remove the bottom row, the bottom two rows, which are titles. Click here to select all, double click here to best fit. See now how I’ve got “device” and with a “top versus other”, campaigns, and then all of the stats.

… Do immediately, I just select all of the columns, go to inserts, pivot title. Click OK and open this thing here, and over here is a field list. All the fields, the columns that were in the Excel spreadsheets, I’m going to have “device” added to here, “top versus other” added underneath. For this example, I’ll just take the clicks and add it to the values.

… With this by default, which is rubbish. I don’t want to count all the clicks. I’m going to right click on that, click on “value field settings”, change that to “some”. Honestly, I don’t really like this layout with the pivot table, so I right click somewhere in it, “pivot table options”, “display”. [inaudible 00:06:46] choose. Classic pivot table layout, and click OK.

I’ve got some blanks here, I’ll get rid of those. This isn’t a massive table, so I’ll just grab ‘top versus other’ and I’ll just drop it here. Oops.

“Google top” should come before “other”. I think we’ll put “mobile” at the top of this list and now …

Oops, this wasn’t a great example. We can see that “Google search, top.” That’s where our mobile traffic was coming from. A better example would’ve had data in here, but this particular account is so skewed, everything being in top hat position, that’s why the CTR is so high.

Too difficult, really? Okay, you might’ve come across stuff there, that you’ve not done before, pivot tables, maybe. You’ve never downloaded your AdWords data before into Excel, oh you should do that, big, big insights.

When I managed that team of AdWords specialist, actually I was their analyst for a while, first. Then, I led a team of 35 AdWords specialists, spending 125,000 Euros a day on AdWords. The handful of people, the best guys and gals at the call phase, we’re the ones who were best at Excel as well. Not only does Excel allow you to download the data, pivot table it, slice and dice it, turn the data like a Rubix Cube until you get an insight, but Excel allows you then to capitalize on the insight you’ve got, to attack the weak underbelly that you might’ve found. I’ve got about 10 minutes left to run a report, it’s a bit of a more complicated one, so I better go do that. Good luck!

Click Here to Leave a Comment Below

Leave a Reply: