Large Adwords Excel reports and Pivot Table question

seachto7

Hi all,

Maybe some of you running Excel reports might be able to help here. I've not had to do many pivot table reports before, and they are increasingly becoming popular in search reports I find.
One thing I would like to try and analyze over, say 6 months, or a year, would be the rise and fall of average cpcs.
I know I can see it in adwords on a graph, but I am trying to create a weekly or monthly pivot table where I could see the avg cpc trend.

Is this possible?

Do you use pivot tables much? I was looking at some demos, but none are specifically search marketing related, so don't really apply to what I am trying to do.

seachto7

I *think* I may have come across some articles that *might* answer my query, but would be interested to hear what pivot table reports you would use in general for SEM reports?

EDIT: nope I haven't. I suppose my two main queries are, how could I create a keyword report and analyze avg cpc and CTR trens over a year for example using a pivot table.

The Apprentice

To be honest im in bed on the laptop and if i start typing i,ll botch something and get into an argument and ragequit the world..
I have searched profusely on this and this i find will do most what u require..
Variables need to be changed but i think its what your after.
Interested to hear what possible blue4ever or link8r have in the warchest ..


http://www.seomoz.org/blog/how-to-make-awesome-ranking-charts

seachto7

Thanks for the link The Apprentice. I'll get stuck into it....

The Apprentice

Cheers- let us know how you get on so we can do something similar in the future should the need arise.

seachto7

Had a mess around with it there, and it seems to be along the lines of what I am looking for, but I need to play around with it some more.

seachto7

So. I had an interview in the UK the other day, and a lot of it concerned pivot tables. Not my strong point, but I was shown some really useful formulas. Maybe it could be good to get them together on a thread here to offer advice on some useful pivot table reports.

For example, creating bulk ad copy with dynamic pricing, or working out the true average cpcs as opposed to the pivot table creating "sum of average cpc".

The Apprentice

Seachto my man .. this might help you out also..

http://searchengineland.com/using-regression-analysis-to-estimate-incremental-revenue-opportunities-148639?utm_campaign=wall&utm_source=socialflow&utm_medium=facebook

seachto7

Cheers, there was also some "concatenation" involved...

YankIE

For quick time trend analysis - have you tried using the dimensions tab within adwords?

It allows you to group data in time buckets in tabular form (like by day, by week, etc).

For more thorough adwords data analyses, I do use pivot tables quite a bit.

One tip: be sure to consider weighting issues when collapsing data in a pivot table. For example, if you export adgroup level, daily CPC's, but you do a pivot table for overall account CPC by month, your excel pivot won't be accurate (the monthly CPC wouldn't be a weighted average, allowing for different volumes of clicks by adgroup and by day). This tripped me up in my early days of doing this... Better to let Adwords do the weighted averaging- and export the proper time segments that you want to evaluate in excel.

seachto7

Yes, I know I could manipulate data in Adwords, but say I wanted to do a keyword report for 2012. That would be a lot of Excel rows.

If I wanted to see trends in the rise/fall of the average position, would you be able to tell me what I should put into the pane on the right hand side?

For example, what should I put in the

Report Filter
Source Labels
Row Labels
Values

Once I get the hang of this type of report, everything else will be easier. I find it frustrating trying to figure out how to do it currently!

Any advice would be much appreciated. Might help others on here too....