AdWords
5K members online now
5K members online now
Improve your AdWords performance and boost your ROI, CTR, and Quality Score
Guide Me
star_border
Reply

Conditional Formatting

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I am trying to make use of conditional formatting in my daily analysis. I would love to hear what type of conditional formatting people use in their ppc analysis and why.

1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Adam M
September 2015

Re: Conditional Formatting

Participant ✭ ✭ ☆
# 2
Participant ✭ ✭ ☆
Hey Adam,

I like to set up an Excel spreadsheet to look at Time frame A versus Time frame B from the past. Then I create a column that shows the % difference.

So for instance, if Time Frame A had 5 conversions and Time Frame B had 5 conversions, the % change column would show "-50%".

I'll do this at the keyword level, so it's a pretty big spreadsheet. In order to zone in on the keywords I need to focus on, I'll use conditional formatting in the percent change column to highlight cells in red if they are below 0%. I also set up conditional formatting to highlight cells in green if they are above 0%.

I don't just check the conversion column, though. I set up %change columns for impressions, clicks, position, etc. This way, when I'm looking at the red cells in the conversion % change column, I can easily glance at the other metrics to see if they are also flagged as red.

This is helpful because you can see correlations at a glance. For instance, if you see that position is highlighted in red because it became a higher number than before and conversions are highlighted in red because they decreased, it could be that you need to improve your position.

If you see that conversions, impressions, and clicks decreased but position is the same then there is likely something going on with search volume. Possibly other changes were made such as dayparting, etc, that have caused your ad to show less.

I wrote a post about a long time ago about the Excel sheet that I just described. It's a little better structured as far as the steps go, so it might be helpful: http://www.ppchero.com/analyzing-your-ppc-account/

Hope this helps!

View solution in original post

Marked as Best Answer.
Solution
Accepted by topic author Adam M
September 2015

Re: Conditional Formatting

Participant ✭ ✭ ☆
# 2
Participant ✭ ✭ ☆
Hey Adam,

I like to set up an Excel spreadsheet to look at Time frame A versus Time frame B from the past. Then I create a column that shows the % difference.

So for instance, if Time Frame A had 5 conversions and Time Frame B had 5 conversions, the % change column would show "-50%".

I'll do this at the keyword level, so it's a pretty big spreadsheet. In order to zone in on the keywords I need to focus on, I'll use conditional formatting in the percent change column to highlight cells in red if they are below 0%. I also set up conditional formatting to highlight cells in green if they are above 0%.

I don't just check the conversion column, though. I set up %change columns for impressions, clicks, position, etc. This way, when I'm looking at the red cells in the conversion % change column, I can easily glance at the other metrics to see if they are also flagged as red.

This is helpful because you can see correlations at a glance. For instance, if you see that position is highlighted in red because it became a higher number than before and conversions are highlighted in red because they decreased, it could be that you need to improve your position.

If you see that conversions, impressions, and clicks decreased but position is the same then there is likely something going on with search volume. Possibly other changes were made such as dayparting, etc, that have caused your ad to show less.

I wrote a post about a long time ago about the Excel sheet that I just described. It's a little better structured as far as the steps go, so it might be helpful: http://www.ppchero.com/analyzing-your-ppc-account/

Hope this helps!

Re: Conditional Formatting

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭
Thank you for that answer. I really appreciate that. Are there any times when you believe that green for positive and red for negative just won't cut it? Do you specifically use a 3 or more color gradients for any type of analysis?

Re: Conditional Formatting

Participant ✭ ✭ ☆
# 4
Participant ✭ ✭ ☆
Happy to discuss - and I'd love to hear the types of analysis you're using them for, as well! Sometimes I add in a third color. Generally I use colors stoplight-style (green = good, yellow = meh, red = yikes). Yellow generally indicates if there was very little change +/-. So I might set -10% or less to red, -10% through +10% as yellow, and 10&+ to green.

It depends a little bit on the situation and the goal. If I had a growth goal, or made specific steps toward growth then I would be especially interested in keywords that remained flat. If campaign KPIs took a nosedive, I'd be less worried about things that were flat than things that dropped off.

Re: Conditional Formatting

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭
With respect to daily, weekly or monthly analysis, I generally create light red cells for any kpi that has a negative change or % change from the earlier time frame. Then I'll take my performance metric of choice and sort by smallest to largest, so I can focus my attention to the biggest losers.