Phrase Match Excel Formula + AdWords Editor for PPC

For a few days now I’ve seen hits for searches like this “phrase match excel.” I’m afraid that if you’re looking to for this you really need to get with the times.

1) Unless you’re editing through Google AdWords itself, there’s absolutely no need to use keywords actually wrapped in quotes – “paid search consultant” for example, if it was a phrase match keyword. AdWords editor made using the actual AdWords interface almost completely obsolete. The match type attribute can be selected through the keyword editing pane with keywords selected based on almost any attribute including:

  • adgroup name
  • campaign name
  • max CPC
  • min CPC
  • clicks/impressions/average position absolute and ranked
  • status

Go download AdWords editor here if you don’t have it yet. You can edit all of the keywords in your AdWords account at once if you were so inclined. Not that it would be very logical in most cases.

2) If you still want the formula here it is along with a few other useful excel maneuvers for the pay per click practitioner.

”    paid search consultant    ”    =CONCATENATE(B5,C5,D5)

If the first quote is in B5, the keyword is in C5 and the end quote is in D5. There’s a suite of macros for excel called ASAP Utilities you should have as well. It’s great for sorting huge lists and cleaning up data very quickly.  Here are a few great features:

  • Delete all empty rows
  • Delete all duplicates (easy for Excel 2007 users however)
  • Variety of =Trim() functions to delete leading, trailing and excessive spaces, so duplicates are truly duplicates.
  • Delete every Nth row.
  • Many =upper(), =lower(), =proper() type functions dealing with capitalization.
  • Great conditional select and delete options.
  • Hundreds of other uses I’ve never used and probably never will.

I believe it’s still free and is a great add-in for Excel. In any event – if you’re still editing your AdWords account through the interface…well, you must be nuts! (in most cases…Placement targeted campaigns {researching sites}, longitude and latitude geotargeting, preferred CPC, conversion optimizer settings, day parting have to be done in your account).

With something called a “custom view” in AdWords editor you can actually implement basic rules based bid management since you can select keywords based on nearly any attribute along with 2 performance metrics (conversions, CPA, CPC, Impressions, position, others too I believe). Keep a notepad file of the changes you make and which groups of keywords if affected. Here’s an example –

Advanced search/custom view in AE (adwords editor)

– Select the 10 highest spending keywords in your account. Search, sort by CPA, use an advanced bid adjustment and increase or decrease by a % amount. You can implement ceilings and floors so if you’re doing this over hundreds of words you don’t end up with inactive keywords or really high bids.

– Try keywords that were in the top 50 of all your keywords for impressions and sort by CTR (click through rate). If they’re all broad match you might want to check that you’re CTR is good and your minimum bids are $.10 or below. Maybe you’re running heavy on impressions if your keywords are tangential to your core product/service.

– Find all keywords with over 1,000 impressions and have an average CPC of over $X.00.

– Find keywords whose average position is less than, less than or equal to, equal to, greater than, greater than or equal to some value. CPCs do go up – check out to see if you’re falling.

Another cool thing is that you can copy your custom view and paste it into excel. You don’t get the performance stats but at least you have your list and campaign data right there if you’d like to make any organizational changes to your PPC account.

Cheers!

Jeff

Advertisements

paid search expert, sitecatalyst/omniture consultant, professional search engine optimization

Posted in ppc + excel, ppc bulk edit, PPC Essentials, ppc tutorial
3 comments on “Phrase Match Excel Formula + AdWords Editor for PPC
  1. Rob Kingston says:

    ASAP Utilities looks good. I’ll have to give it a burl at work tomorrow…

    Btw, instead of using Concatenate, I found a more elegant formula:

    =”[“&A2&”]”

    If you need to use phrase match and don’t want to use concatenate, replace the “[]” with CHAR(34). i.e.

    =CHAR(34)&A2&CHAR(34)

    Like

  2. Thanks Rob,

    that formula is much more elegant than the concatenation, and faster.

    Just what I needed 😉

    Like

  3. Jeff James says:

    Jeremy,

    Thanks for stopping by.

    Jeff

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Expert Omniture SiteCatalyst Consultant
jeffrey james - analytics consultant
Try Me - 30 Minute Troubleshooting or Strategy Discussion
View Jeffrey James's profile on LinkedIn - Omniture Consultant, SiteCatalyst Implementation Consultant and Consulting
    Specialties:
  • Omniture SiteCatalyst Implementation/Reporting
  • PPC Management - large scale campaigns and scripts
  • Big-site Technical SEO Strategy
  • Google Analytics (and Premium) Consulting
>> Click to Email
%d bloggers like this: