Nov/09

20

Create 100’s of Unique Geo-Location Ad Creative in Minutes

There is a fantastic formula in Excel which I find myself using quite often when creating ads for Geo-Targeted campaigns.  Its called: Substitute

SUBSTITUTE(text,old_text,new_text)

To demonstrate how it works, here is an example.  Keep in mind that your campaign structure will most likely be different than how it is outlined below.  You will need to take this strategy and contour it to fit your needs.  The concept is what is most important here.

excel1

To instantly create unique ads specific to the State and City follow these instructions:

  1. Write the creative and use “XXXX” to signify the State and “YYYY” to signify the city.
  2. Select Cells C1 through E10 >> CTRL D
    1. This copies the creative down for each adgroup.
  3. In Cell H1 write the following formula Result: “New York Cleaning Srvc.”
    1. =Substitute(C1,”XXXX”,A1)
    2. Result:New York Cleaning Srvc.
  4. In Cell I1 write the following formula
    1. =D1
    2. Result:Home, Office, or Vehicle.”
  5. In Cell J1 write nbso the following formula
    1. =Substitute(E1,”YYYY”,B1)
    2. Result:Servicing the Manhattan area.”
  6. Select Cells H1 through J10 >> CTRL D
    1. This copies the formulas down for each adgroup.
  7. Select Columns H,I,J >> Right Click >> Copy
  8. Select Column C >> Right Click >> Paste Special >> Paste Values >> OK
    1. This converts the formulas you wrote to text.

Here is the result:

excel2

Positives: It may be hard to see the value in using this method to create ads for 10 adgroups, but when you have campaigns which work on Towns within Cities within States, you can have hundreds of Adgroups.  At that point you will see the value in creating ads in this method as this only takes a few minutes to complete.

Negatives: Once you create ads for all of your adgroups, you will find that you have exceeded the character limits in a few locations.  These will need to be manually adjusted to make them fit.  Try removing a period: “area.” To “area”, abbreviating a word: “North” to “N”, or rewrite the entire line of text.

If you feel you are fed-up with the ambiguity of Google’s quality score, here is a great article outlining some of the things the FTC is going after Google about. We hope that in the least we will get a glimpse beneath Google”s Shroud of secrecy surrounding their Quality Score rating system.
Jump over to ClickZ for the article:http://www.clickz.com/clickz/column/2086712/ftc-regulation-google

· · · · · ·

2 comments

  • keithl · Sunday, December 13, 2009 at 7:37 pm

    Thanks for posting this. I’m keeping my fingers crossed to land a new client. If I do I will be creating a lot of local ads for them. This will come in very handy

  • Dave · Tuesday, April 5, 2011 at 6:06 pm

    Thanks Bud! This is exactly the kinda thing I was looking for. I got it in 2 seconds! Now it’s off to the thesarus and the drop down lists to figure it out!

Leave a Reply

<<

>>

Copyright © 1999 - 2016 Engine Ready, Inc. All Rights Reserved.