let's get visual, visual! turning auction insights into actionable data - heroconf 2016
TRANSCRIPT
Let’s Get Visual, Visual!Turning Auction Insights Into Actionable Data
Maddie Cary | @MaddieMarketerDirector of Paid Search | Point It
• Director of Paid Search at Point It
• Oversee $35M+ in Yearly Media
• Global SEM Program Lead• 20 Account Managers• 30 Languages• 60 Markets
• 2015 US Search Award “Young Search Professional”
@MaddieMarketer
WHO AM I?
@MaddieMarketer
Hi Jim! I did some searches for our keywords and saw quite a few competitors alongside our ads
What does our competitive landscape look like in PPC?
Are we missing opportunities?
How has our performance been impacted by our competitors?
Can you send me a report?
Jim gets an email from his boss:
@MaddieMarketer
SO EASYI’ll just pull an
Auction Insights Report from AdWords & send
a screenshot of the tableDONE!
Jim’s reaction
@MaddieMarketer
Uh, Jim…WTF
does this table mean?
The boss’ reaction?
@MaddieMarketer
Oh, she doesn’t understand the metrics!
Impression ShareHow often a competitor received an impression as a proportion of auctions in which you were in
Overlap RateHow often a competitor received an impression
when you did
Position Above RateHow often a competitor was shown above you
Top of Page RateHow often a competitor was shown at the
top of page
Outranking ShareHow often you outranked a competitor
@MaddieMarketer
Get it together, JimI know what the metrics mean.
I need your help to understand the
TRENDS,OPPORTUNITIES,
& STORY
Not quite…
@MaddieMarketer
Stop looking at competitor data like this
@MaddieMarketer
Or like this!
@MaddieMarketer
RAW AUCTION DATA VISUAL TRENDS ACTIONABLE INSIGHTS
@MaddieMarketer
Jim knows what he has to do
I need to go Olivia Newton-John
on this thang
@MaddieMarketer
LET’S GET
VISUAL, VISUAL
How competitive are the auctions you’re in?
@MaddieMarketer
• PIVOTTo get Avg Impr Share & Avg CPCper competitor
• =COUNTIFTo count number of times competitor shows up in the auction over your given time period
DATA NEEDED TOOLS & FORMULAS
• SEGMENTED BYTIME (Month, Quarter)
AUCTIONINSIGHTS REPORT
CAMPAIGN REPORT
@MaddieMarketer
Quarter Avg Impr Share Number of Competitors Q1 2015 86% 8Q2 2015 84% 8Q3 2015 85% 7Q4 2015 93% 4Q1 2016 87% 8
Capitalized on opportunity to grow impression share when fewer competitors in auction
Quarter Avg CPC Number of Competitors Q1 2015 $4.89 8Q2 2015 $3.42 8Q3 2015 $3.12 7Q4 2015 $2.10 4Q1 2016 $3.54 8
@MaddieMarketer
Lower CPCs during quarter with fewer competitors +
maintained lower costs when auction returned to 8
@MaddieMarketer
CRUSHED IT
Which competitors are consistently in auction?
@MaddieMarketer
• =COUNTIFTo count number of days competitors showed in auction over X total days
• =AVERAGEIFTo get Avg Impr Share per competitor over X total days
• =IF(AND(Conditional logic to define your parameters for when data indicates competitor is a bigger threat
DATA NEEDED TOOLS & FORMULAS
• LONGER TIME RANGE: • Last 30 days• Last 60 days• Last 90 days
• SEGMENTED BYTIME (Days)
AUCTIONINSIGHTS REPORT
FORMULASS
@MaddieMarketer
Day Display url domain Impr. share Avg. position Overlap rate Position above rateTop of page rateOutranking share1/1/2016 Competitor 1 83.87% 1 83.87% 0.00% 96.15% 100.00%1/2/2016 Competitor 1 47.37% 2.2 49.54% 0.00% 64.81% 95.61%1/3/2016 Competitor 2 50.66% 2.2 52.38% 1.30% 74.03% 96.05%1/4/2016 Competitor 2 51.59% 2.2 53.74% 1.27% 59.26% 92.99%1/5/2016 Competitor 2 44.03% 2.2 44.16% 0.00% 70.00% 96.86%1/6/2016 Competitor 2 39.31% 2.6 39.58% 0.00% 70.18% 99.31%1/7/2016 Competitor 3 44.44% 2.2 44.44% 0.00% 75.00% 100.00%1/8/2016 Competitor 3 37.58% 2.3 37.58% 0.00% 66.07% 100.00%1/9/2016 Competitor 3 43.75% 2.1 43.75% 0.00% 61.22% 100.00%
1/11/2016 Competitor 3 52.74% 2.1 53.10% 0.00% 70.13% 99.32%1/12/2016 Competitor 3 48.65% 2.7 49.51% 0.00% 55.65% 86.97%1/13/2016 Competitor 3 51.41% 2.7 52.49% 0.00% 55.26% 85.79% Days In Auction
=COUNTIF(range,criteria)
Competitor Frequency
= (Days In Auction/Report Range)
Report Range
= 90 days
@MaddieMarketer
Competitor URL Days In Auction Competitor Frequency Average Impr Share Threat?You 90 100% 89% - Competitor 1 90 100% 34% Take ActionCompetitor 2 84 93% 16% MonitorCompetitor 3 5 6% 16% MonitorCompetitor 4 5 6% 14% MonitorCompetitor 5 56 62% 13% Monitor
Conditional Formatting
Conditional Logic Formula
=IF(AND
EXAMPLE LOGIC:Comp Frequency > 50%ANDAvg Imp Share > 25%
IF TRUE = “Take Action”IF FALSE = “Monitor”
@MaddieMarketer
LET ME HEAR THAT DATATALK
Where are you most competitive (and not)?
@MaddieMarketer
• PIVOTTo get Avg Impr Share & Avg Posper campaign
DATA NEEDED TOOLS & FORMULAS
• Pull by campaign and then consolidated together
AUCTIONINSIGHTS REPORT
Campaign Avg Impr Share Avg PosBranded 95% 1.1Shoes 83% 3.4Pants 80% 2.6Shirts 68% 4.3Socks 54% 5.5Belts 47% 6.7Scarves 36% 5.4
@MaddieMarketer
Could be worth bid testing in campaigns with lower Avg Impr Share & higher
Avg Positions
@MaddieMarketer
CAN’THANDLE
THISPPC
MUSCLE
Where are you missing opportunity?
@MaddieMarketer
• PIVOTTo get Avg Impr Share trendsby device & time
DATA NEEDED TOOLS & FORMULAS
AUCTIONINSIGHTS REPORT
• SEGMENTED BYDEVICE & TIME
@MaddieMarketer
Some of your competitors aren’t bidding on mobile, which
may mean Impr Share opportunity for you!
Competitor Computers Mobile Tablet You 100% 98% 100%Competitor 1 31% 49% 24%Competitor 2 16% 12%Competitor 3 14% 16%Competitor 4 10% 16%Competitor 5 12% 14% 11%TOTAL 36% 55% 44%
@MaddieMarketer
AUCTIONGOT
NOTHIN’ONME
How can you go from REACTIVE to PROACTIVE?
@MaddieMarketer
AUCTION INSIGHTSCRIPT
DATA NEEDED TOOLS & FORMULAS
• SEGMENTED BYTIME
AUCTIONINSIGHTS REPORT
CAMPAIGN REPORT
@MaddieMarketer
• Copy/paste BrainLabs script into Google Sheet• Pull segmented Campaign report• Pull segmented Auction Insight report• Throw both into Google Sheet• Generates competitor charts alongside your trending CPC data
YOU
Can see where CPCs spiked when competitors’ Impr Share rises
@MaddieMarketer
GO O.N.J. ON THIS THANGUse visual graphs &
tables to
• IDENTIFY TRENDS• FIND OPPORTUNITIES
• TELL THE STORY!
PPC competition still make you feel like this?
Check out my session recap blog post with templates for all the visuals I showed today!
bit.ly/lets-get-visual-visual
@MaddieMarketer
THANK YOU
Maddie Cary | @MaddieMarketerDirector of Paid Search | Point It