PPC & Excel: The Perfect Marriage

Microsoft Excel (Windows)
Image via Wikipedia

Excel is undoubtedly the most important program I use in conjunction with my PPC work. It’s the Swiss Army Knife of online tools where I build my keyword lists, write my ad copy, prep campaigns and ad groups for uploading, and the center of my analysis of historical data that my campaigns have produced. I have to assume that others use it as often as I do for these and other tasks, so I thought this would be a good time to go over what I consider the most useful tools to use.  If you’d like to see even more, check out Josh Dreller’s excellent series at Search Engine Land, called How to Excel at Excel

Concatenation When I first learned to use this tool several years ago I asked where had it been all my life! This allows you to combine the information in two cells together into one additional cell.

=CONCATENATE(A1,B1)

To automatically include a space between the words in each cell, you can include a space using quote marks between the two cell numbers, so the equation would look like =CONCATENATE(A1,” “,B1). This is best used to combine two groups of words for keyword generation

Character Counting If you’ve done anything in search you know that the character limits in search engine ads are brutally short. That’s why you have the LEN counter to help you:

=LEN(A1)

This will tell you how many characters are in the designated cell. I usually set my ads up in three rows, each with a len equation, so I can play with any of them as needed.

Trimming There have been numerous times where I’ve spent much more time than it should have taken trying to format the ideal piece of ad copy to fit within character guidelines only to later find out I had an extra space or useless character taking up room. Using the trim equation will remove all of them and leave only one space between words, and remove any unneeded spaces at the beginning or end of the cell.

=TRIM(A1)

If you duplicated my character counting example in Excel, you’ll notice that the second description line (I Love Writing PPC Ads. Don’t You?) is actually 34 characters, with an extra space at the end. If you put the trim equation in C3 for A3, the LEN count in B3 will drop to 34 characters instead of the listed 35. Try it!

Left/Right Trimming Instead of removing all floating spaces regardless of position, this formula removes the dictated number of characters from either the left or right side of the cell. This is handy when editing URLs, where part of the cell stays constant, but other parts need to be removed.

=RIGHT(A1,2)

=LEFT(A1,2)

The formula will remove the number of characters stated after the comma starting on either the right or left side of the cell, depending on which formula you use. For example, if you have a URL that looks like http://www.yoursite.com?pid=13, and you wanted to remove everything after “.com”, you would write the formula to look like RIGHT(A!,7). I count 7 characters, which includes the question mark (?) and the equals sign (=).

Word Counting Word counting is good as an analysis tool to see how many words within a keyword string perform best.

=LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))+1

This will return only a number. For example, if your keyword was “White car”, then this formula would return a value of “2”. If your keyword is “New Used Car”, then the formula would return a value of “3”.

VLOOKUPs Vlookups–short for vertical lookups–are probably one of the most powerful analysis tools, since they allow you to compare data for the same source cell to different entries within a table of data. In search, this is usually comparing a keyword’s performance over a given length of time.

=VLOOKUP(A2,$F$3:$J$247,3,)

There are some very important issues you need to be aware of when using these formulas:

  • The first entry (A2) is your source cell, or most commonly your keyword. If you have the same keyword in multiple match types, you’ll need to concatenate the keyword and the match type so the two are in a single cell, otherwise you won’t get correct data. Be sure to concatenate your data the same way over all data periods.
  • Do not forget to include the dollar signs ($) in the table field, which is the group of numbers between the first and second commas ($F$3:$J$247). If you forget the dollar sign, your data table will move down with each new Vlookup. The dollar signs keep the data table static.
  • The number between the second and third commas is the column number in the data table that you want to retrieve the information from. This includes the column with your source data! Count by starting with the column your source data in as column 1, and count over the number of columns until you come to the data you want to see.
  • Don’t forget the last comma after the column number. The help section states that this comma is used to lookup a range in the value, but in all honesty I have no clue what that means. All I know is that it works well with the comma and doesn’t work well without the comma.
  • Be sure to use Excel’s formula help section to understand how to best use this formula for your specific data.

Proper Statements It’s always good to have proper punctuation! This formula will capitalize the first letter of each word in a cell, which I think is the most attractive formatting for ad copy text.

=PROPER(A1)

Using the formula will turn a sentence like “this is a ppc headline copy” into “This Is A Ppc Headline Copy”. After the cells have been PROPER-tized, copy it and right click on an empty cell. Select “Paste Special” and then “paste values” to turn the text from a formula result into editable text. Then you can make the minor adjustments like capitalizing all of “PPC” in the example, and maybe making the “A” lowercase for proper formatting and grammar.

Reblog this post [with Zemanta]

17 Comments

  1. Twitter Comment


    RT @nowsourcing PPC & Excel: The Perfect Marriage|@NowSourcing.Com [link to post]

    – Posted using Chat Catcher

  2. Great post, Brian. When it comes to manipulating text, I think you hit the nail on the head with the most useful tools. Here’s a couple of shortcuts that I like:

    A shortcut for concatenate is =A1&” “&B1.

    A shortcut instead of typing the $’s is F4. Hitting the F4 key toggles the dollar signs around your cell reference. Best practice is to include the $ signs in front of both letter and number.

    Another tool I love is the Text to Columns feature. Let’s say you have a list of keywords all separated by a comma and you want to separate them into individual cells. Text to Columns will do that for you.
    .-= Mike Campbell´s last blog ..Twitter is dying and what to do about it. =-.

  3. Nice article – I’m a big fan of Excel and find it incredibly more productive for analysing and managing PPC campaigns that the web interfaces. LEN and TRIM are great for preparing ads in Excel, but I tend to use ampersands ‘&’ instead of CONCATENATE – I find it serves the same purpose but without the typing.
    .-= Alan Mitchell´s last blog ..One Keyword per Ad Group: Pros & Cons =-.

  4. Twitter Comment


    Great article by @nowsourcing on using Excel to manage PPC campaigns [link to post]

    – Posted using Chat Catcher

  5. Interesting post Don. Would it be possible to see your excel-file? Maybe it would be possible for me to create a simple PHP-script for you to use instead.
    .-= Stefan´s last blog ..How to Research Valuable Keywords =-.

  6. Twitter Comment


    PPC & Excel: The Perfect Marriage | @NowSourcing.Com [link to post]

    – Posted using Chat Catcher

  7. Twitter Comment


    RT @MichDe PPC & Excel: The Perfect Marriage | @NowSourcing.Com [link to post]

    – Posted using Chat Catcher

  8. Thanks for sharing such a great information. 🙂

  9. Very nicely written, Don!

    I always love learning new things about Excel and I second you when you say that it is a very powerful analytical tool!

    I retweeted your post!

    -Amit
    .-= Amit´s last blog ..Experience Europe =-.

  10. Twitter Comment


    #PPC & Excel: The Perfect Marriage: [link to post]

    – Posted using Chat Catcher

  11. Thanks for the useful advice. I am always appreciative of useful techniques if I am not using tools like Speed PPC.
    .-= Rob Metras´s last blog ..Trust Agents =-.

  12. Twitter Comment


    RT @Szetela #PPC & Excel: The Perfect Marriage: [link to post]

    – Posted using Chat Catcher

  13. Twitter Comment


    RT @Szetela: #PPC & Excel: The Perfect Marriage: [link to post]

    – Posted using Chat Catcher

  14. Twitter Comment


    RT @RBeale: RT @Szetela #PPC & Excel: The Perfect Marriage: [link to post]

    – Posted using Chat Catcher

  15. Twitter Comment


    Exceling on excel of PPC: The Perfect Marriage [link to post]

    – Posted using Chat Catcher

  16. I also use an excel file and a sheet for every campaign I run. Concatenate and Len functions are a must in Adwords but I had no idea about the Proper Statements function and I must admit I was doing that by hand, editing each word and wasting some precious time 🙂
    .-= raphael´s last blog ..Mistakes Commonly Made when you’re Trying to Build Rapports =-.

Trackbacks/Pingbacks

  1. Mich De (michde) 's status on Sunday, 23-Aug-09 09:36:33 UTC - Identi.ca - [...] PPC & Excel: The Perfect Marriage | @NowSourcing.Com http://nowsourcing.com/2009/08/20/ppc-excel-perfect-marriage/ [...]
  2. SearchCap: The Day In Search, August 24, 2009 - [...] PPC & Excel: The Perfect Marriage, nowsourcing.com [...]