Once your PPC account scales to a certain level, using a spreadsheet program like Microsoft Excel can really speed up the process of analyzing stats and making mass changes to your account. Both Google and Bing allow for exports of account data via Excel-compatible CSV documents. In many situations you can download some campaigns, ads, or keywords, make your necessary changes to the account elements, then re-upload the same document to apply your changes to the account.
Using formulas and functions in Excel can really speed up these bulk changes. Instead of changing each cell individually or doing a lot of copy-and-paste, you can use Excel’s functionality to do all the hard work for you. Here are a few of my favorite hacks that I use when managing my own PPC campaigns.
The concatenate formula has probably saved me hours, if not days, of PPC management time over the years. It looks like this: =CONCATENATE(A1,” “,B1). In this example, the value in cell A1 comes first, then is followed by a space (the ” “), and then the value in B1 is appended at the end. This is really handy if you have a group of keywords that have slight variations, such as a change in city name or plurality of a certain word. Here’s an example in action:
Now, I can just copy the formula to the other cells to come up with other combinations of the seed keywords:
Note: if you want one of the values to remain static as you copy the formula down the column (in this case, the keyword “lawn”), just add the $ character in front of both the column letter and the row number in the formula.
If you want a specific character or text string to appear instead of a cell value, just add it in surrounded by quotes. In my example, I added a space between each keyword by adding ” “. But I could have easily done away with Column A altogether and made my formula =CONCATENATE(“lawn “,B2,” “,C2).
This formula is great for coming up with extensive keyword lists and variations of text ads. But remember – if you want to copy your concatenated results over to another spreadsheet, you’ll need to copy as usual, but do a “Paste Special” of values-only so that you don’t copy over a formula with no references. You can Paste Special by right-clicking where you want to paste, click “Paste Special,” then select “values” from the option window.
This one may be the simplest formula on the list, but extremely handy if you’re editing text ads in an Excel document. LEN counts the number of characters in a specific table cell. This is a novelty for most Excel users, but for PPC managers that need to deal with text ad character limits, it can help you make sure that your ad text adheres to Google or Microsoft guidelines.
Using LEN is easy. The formula is just =LEN(A1), with A1 representing whichever cell you want to count. Here’s an example:
Speaking of text ads, sometimes you need to make adjustments to capitalization to either make your text ad stand out more or reduce your capitalization to adhere to ad guidelines. These capitalization functions can make your job easier.
These formulas are pretty straightforward. =LOWER(A2) would take all of the text characters in A2 and make them lowercase. =UPPER(A2) would make them all uppercase. And =PROPER(A2) would take all of the text characters and capitalize the first letter of every word, like this:
Like any formula, the results can be a little sloppy if used injudiciously. But PROPER could really save you some time if you need to transform ad text to a more attention-grabbing, capitalized format.
There are plenty of other handy Excel formulas for PPC, but these five should be a good start in increasing your productivity. Do your own research, and get creative with the tools you have. A good PPC manager knows how to leverage software to work smarter, not harder.