For any interested pay per click advertisers, ppc affiliates or mildly interested Internet marketing aficionados – here’s a neat little macro for excel which generates a list of keyword permutations based on 2 lists of data (columns A and B).
Check out the screencast I made to see how you might benefit from it and how to set it up.
Here is the code itself -
Sub MyConcatenate()
Dim rng As Range
Dim LastRow As Long
Dim I As Long
LastRow = Range(“A” & Rows.Count).End(xlUp).Row
Set rng = Range(“B1″)
While rng.Value “”
Range(“C1″).Offset(I).Resize(LastRow) = “=A1 & CHAR(32) &” & rng.Address
Set rng = rng.Offset(1)
I = I + LastRow
Wend
End Sub
There’s a wide variety of uses for this, especially when generating long geotargeted lists of keywords, or when you have a long list of “keyword + {attribute}.” They’ll be grouped somewhat logically the way the macro is written so you can simply just generate some dynamic URLs using the keywords immediately after generating them if you’re sending traffic to a dynamic template.
It doesn’t name the adgroups for you but if you’re brazen enough and the keywords are similar enough you have 2,000 keywords per adgroup and {KeyWord:Is Your Friend}.
Cheers!
Jeff
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
I wanted to share an analogy with anyone who might be trying to more firmly grasp some of the elementary concepts of Omniture’s SiteCatalyst analytics software. I’ll outline the meaning and intended use of:
- eVars - (s.eVarN) aka “custom commerce variables”
- s.props – (s.propN) aka “custom insight variables”
- custom events – (s.events) – “custom events”
Imagine there’s a bustling marketplace. You have many entrances, many shoppers, many browsers and in all likelihood a few shoplifters. If you were trying to better measure various metrics such as the ratio of those who enter the market to those who purchase, or even how many times people pass through a certain entrance you’d have quite a job to do. You’d have to be so many places at once with your eyes on more then you can possibly keep track of.
Cast a wide net and tag every entrance with java script tags! Custom insight variables or s.props will count whatever pass through them. Perhaps you’d like to find out the use frequency of the N entrances to the market. Let’s use
s.prop1=”entrance 1″ each time someone passes through entrance 1, and use s.prop1=”entrance 2″ each time someone goes into entrance 2. Keep in mind that although the values we’re passing through the custom insight variable vary, the actual tag (s.prop1) will stay the same for purposes of continuity. We can go into the SiteCatalyst admin and name s.prop1 “entrance frequency.”
So now you know where people are coming in…that’s nice. Say you want to know how many people go into store X in one month. Every time someone passes through the entrance point of store X you trigger a custom event, say s.events=”event1″ Now each time a shopper passes into the store the tally of event1 which will probably be set to “counter” if you’re not aggregating a monetary value the event count will increment upward by 1.
An astute reader will now say, “Jeff, what is this? You can just use a custom insight variable, why are you being all fancy using this custom event stuff eh??” Excellent point I’ll reply! I could use an s.prop and each time someone passes through the store entrance pass through the store name. So what’s the fundamental difference?
Well, when someone leaves the store it will also count them when you use a prop, whereas with the custom event you can serialize the event count so that you only increment the event once per “session” or visit. Is it better to know the number of times something passed through a gate or the # of unique people who went into the store that day? Both have a certain value (and that was a rhetorical question) but as we’ll see next, the custom event’s utility reaches further beyond that of the s.prop in the case of measuring commercial activities.
Okay, so we have the bustling marketplace, lots of people entering here and there, going into stores and we know from where they enter and which stores are most popular. The whole point of going there is to get them buying!!!! Spend your money with meeeeee says the marketplace’s collective wisdom.
Which entrance leads to the most revenue? Which store generates the most revenue? Let’s think about this…if we pass revenue into the s.prop, wait – you can’t do that it just counts the frequency of what gets placed into it, and how would you know what entrance they came into? If we set the custom event to “currency” (an advanced setting in the sitecatalyst administrative console) we can pass the revenue of each store into a custom event, but you’d have to use multiple custom events for each store. How can you get the data in 1 report.
Well first, to save face with any knowledgeable readers Omniture SiteCatalyst uses the s.products=”" tag to count revenue and product data so the custom event set to currency isn’t necessarily applicable here.
With that said, we can use the “custom commerce variable” aka the eVar (s.eVarN=”something”). A key feature of the eVar is it’s use of persistent cookies. Each eVar has its own unique attribution settings and you can control:
- How long the persistent cookie will persist for.
- Whether credit will be given to the first or last touched (first or last what, right? Whatever you put into the tag s.eVarN=”whatever is in here”).
I don’t think any cookie expiration length is typical, but attribution to the last item touched is. There is a great debate regarding the accuracy of this but in my view (without getting into great detail) I think it’s the best measurement.
So let’s set our eVar to persist for 1 day. When someone comes into the mall we have an entrance eVar, when someone enters a store we have a store eVar.
Someone comes into the mall -
s.eVar3=”west gate entrance”
Someone comes into Macy’s -
s.eVar4=”Macy’s”
At day’s end you can run an eVar report for each custom commerce variable against revenue and you’ll see which entrance generated the most revenue and which store generated the most revenue.
In all fairness in this strange example we could use the store name as the products variable in the s.products string and eliminate the need for the eVar4 report but that’s too tangential to this discussion.
Another cool thing about the custom insight variable is you can correlate them with other traffic variables. So if I wanted to know which articles were most popular in my international news section of my news content site, each time a page was viewed in that section, the article name would be passed through the prop. Check it out:
s.channel=”international news”
s.propN=”9 out of 10 politicians at home and abroad are corrupt: public easily forgets”
You could then correlate channel and prop and see how popular articles were within the section.
If you need consulting shoot me an email at JeffJames.vib@gmail.com or catch me on LinkedIn:
Jeff
http://www.google.com/search?q=cars
Ooohhps!!! 
)
As we collect more data we can make more rational, performance based decisions. This we all agree upon. But how often do we change our ways once we have better data? Often times ingrained habits take precedence over actionable information solely due to the fact that it’s a habit. Further, if a large group of people who have the incentive to mirror each other’s actions are faced with the above dilemma – how long until they act?
First consider the power of habit. Some say if you do something for 21 days, it’s a habit. What if an industry has been doing something for 21+ years? Individual inertia is much easier to create or halt than industry inertia. A much larger machine, or network of machines cannot be replaced as quickly as a personal computer.
So, just how much money is wasted on advertising? How many times will people quantify “engagement,” and “interaction” in an attempt to justify purchasing media. Probably numerous times, no…many more times yet.
So is performance marketing the future? Is the Internet, (the best context we have for behavioral ad targeting and data collection) going to revolutionize the way companies spend advertising money? Yes. It’s the only way things can ultimately play out, with a few caveats.
The scaling of the above will be proportionate to our ability to segment, target, and re target groups of users. There needs to be many more definitions of “user” that exist already. Emo, or young adult, senior citizen, etc……this isn’t enough to let performance marketing overtake banners and the current waste we see now. One day, the musician from your Myspace page will follow you as you surf the net to be sure you’re aware of the new CD coming out on the 11th. Cookies will be placed on your PC once you log into your social network and they’ll get paid based on their ability to target effectively.
The keys to success are data and the ability to analyze and act upon it…most people won’t be able to and this will reduce efficiency & publisher profits will prevail for a while longer, but something will breakthrough, or the government will intervene (as if most often the case). As for now – I very much doubt your neighbors knows people have cookies on his browser waiting to meet Mr. Pixel over at Amazon.com! Long way to go folks.
I bet he wants Chinese food! Based on some future metric that one day will be collected from his sniffing cellphone which regresses day/time with food consumed. Monday night at 7pm , really high R^2!
What’s your fortune?
Cheers!
Jeff
Blogged with Flock
Tags: targetedadvertising, internet economics, performance marketing
The great late economist Adam Smith wrote that:
Essentially, when the correct and natural incentive exists in an environment where agents act rationally, on average there will be a net benefit extended to all agents seemingly out of no where. Thus giving rise to the phrase, “the invisible hand.”
Google provides a stage for rational agents to exert actions designed to produce some selfish effect – search engine optimization leads to expected higher rankings for themselves.
As it becomes more competitive to rank for keywords that have a critical amount of search volume, it becomes proportionately more difficult to rank for those keywords because the requisite inputs (resources) to achieve them become more costly.
Thus, those ranking for substantial keywords will probably be relevant, otherwise they wouldn’t have expended the necessary effort to begin with. If they were a function, and they spat out value less than they took in, how could they survive any longer? Are there web sites that have no social or economic purpose for the words they currently rank for? How much more perfect would our economy be if the most efficient businesses had top rankings?
That’d be a better matching between producers and sellers, no?
Google saying that paid links are bad is simply a discouraging statement for webmasters who are faced with various monetization strategies. Also, there are only really 10 results in Google (or any other search engine for that matter) that do matter.
As the economic value of those spots becomes priced into the cost of attainment, there won’t be anyone there who can’t realize value equal to the effort expended/required adjusted for some profit margin.
Okay – even the skeptical empiricist would find the above a bit theoretical…but I wonder how far we are from “efficient searches – and just how much value is lost, or yet to be realized due to bad search results?
Blogged with Flock
Tags: paidlinks, adam smith, internet economics
And our lives are becoming more fragmented by the minute. Empirical data as part of the decision making process is becoming less and less important. People who live and function come to rely solely on the experiential information of others. Tools for mass collaboration which have certain brand allure are actually pretty mundane when their functions are analyzed.
Digg.com – See what other people think is popular in 3 minutes. I know I know, you can vote too. There are over 300 million people in the US, and only 2 routinely have a chance to lead the nation. The short tail will always prevail in the arenas of power…which Digg isn’t.
RSS - You’re too lazy to go to someone’s web site so you read their content through a feed. Nothing inherently wrong with that…but come on…CPM people!
Facebook - Going to meet people? No one has time! Let’s pretend we’re living and enjoying a well rounded routine one a web site that started off as a portal for teenagers!
I’m not knocking the above, but this trend, if the momentum is sustained will result a future unlike any we’ve ever seen. Will you get your exercise by RSS in 10 years. Does Richard Simmons have a Feedburner account? I really don’t know and don’t profess to.
Back to the more philosophical point -if the trend sustains, the only physical reality we’ll have will consist of our home, office and commute. Will we grow so apathetic and passive about reality that current events will have no more meaning than some Bush bashing article that’s been “Digged” 1674 times?
I’m getting sleepy – wait…can that be outsourced or digitally enhanced?
If you’ve ever read the Allegory of the Cave, by Plato. Those gentlemen in the cave will always be there…knowledge has brought the sunlight, but technology, despite its benefits can keep us out of focus and in the dark. Unless you have one of those 24 inch monitors of course.
Cheers!
~Jeff
Welcome – Let’s get ready to demystify the marvelous term that has many companies signing bloated Internet marketing/consulting contracts and keeps agencies promising some pretty high flown results:
“Leveraging Social Media” – Jargon
Utilizing Web 2.0 social sites (Digg, Reddit, Blogs, YouTube, Twitter) where user interaction, collaboration, and sharing are at the core of the site’s purpose, to drive web site traffic, amplify brand awareness, and in some cases – increase sales.
drum roll please….. 
“Leveraging Social Media” – Distilled
Paying bloggers and people who play around on those aforementioned sites money to mention and link to you! Of course, don’t let Google know about it!
She is one happy businesswoman!
GV2MFQLKT89W3C22
Valid until the ball drops on New Year’s eve. Well, $70 since there’s a $5 non refundable activation charge. The platform is bulky…the clicks are relatively cheap, and conversion can be high – if you plan on spending a minimum amount I suggest you utilize adCenter’s Quick Launch team…which is essentially their human version of Yahoo Search Marketing’s 3rd party campaign converter, aka bring your Google AdWords account to the party.
If you utilize the quick launch team, be sure you like your account after 45 days because if you don’t…well – frustration awaits! They don’t call it “busy” season for no reason!
Cheers!
Jeff
Since when is it good to be old? Well, if you’re a business, all else equal – age means staying power. You’re either a monopolist or you’ve managed to stay competitive through evolution. Let’s look at this logo:
So they’re honest, have integrity to boot (exploiting subtleties is at the heart of branding and product differentiation.), and to no great surprise – reliability follows. The key takeaway from that logo is the fact that they’ve been around for almost a century. That’s not bad for a business.
Let’s take this one step further. If businesses value the time they’ve been in business and truly see themselves as a going concern, why not publicize it even more? Why not have a clock outside any customer/employee touch point on or off line and display a counter telling how long the company has been “insert what they do” for customers?
Would it strike you as being odd if a dry cleaner had a digital sign displaying the # of jackets they’ve serviced? Would a mechanic tallying the total number of cars he’s fixed each day at day’s end be unnerving? Would an airline be more reputable if they stated explicitly, XYZ# flights and counting? Would Maytag be redflagged by consumers if they let people know how many more units they’ve sold over the lower priced brands?
I don’t think so – but it’s not practiced, this means 1 of 2 things:
1) It’s a bad idea.
2) It’s too hard to count.
I’m of the belief that it’s a good idea and brings transparency to any marketplace. On any resume you need to list the length of time you’ve worked somewhere – how is this any different from what I’ve stated above?
Google is proud to tell you that its Gmail service is offering more storage each time you login. This isn’t a clock, but a numerical value that offers legitimacy and demonstrates value.
Kayak is glad to tell you that 15,085 “kayakers” are hunting for $20 flights. If 15,085 people leaped off a bridge would you follow? Only if there’s a kayak at the bottom to get back to land.
The ultimate point of this post is to spur a bit of thought – is there anyway you can show your customers, whoever they are, that you’re a “great” business? Also, for the consumer – just what’s behind a number?
UPDATE -
Also…take one glance around on the Internet and all you’ll see are # of times Dugg, this HitTail widget shows which keywords delivered the last X # of visitors to the site…how’s that for transparency? The funnier thing is – HitTail, the premium version, is $99 dollars per MONTH!!!!!
I nearly fell off my chair reading this:
“Long tail graph of search keywords” (provided in both the free and premium versions)
My Answer to You – Frequency Distribution!
Come on man….$999 per year for the Organic Search Keywords Report Google Analytics will give you for Free…unlimited traffic too and reports can be automated and exported to CSV and PDF (more too I think) Geeeez. Well, he does have a PR background – SpinTail might be more appropriate all else equal.
Cheers!
