|
|
Excel Hints for PPC |
Posted: 16 Feb 2012 05:05 AM PST Excel is one of the best tools for PPC. Downloading your data into Excel gives much more scope for analysis and complex change than using a browser interface or AdWords Editor. You may have already read Distilled's Excel for SEO or some of PPCHero's Excel tips, but here are my own hints. ConcatenationIf there's one thing you can put into practice from this post, it is this: you can use ampersands instead of CONCATENATE(). A1&A2 is the same as CONCATENATE(A1,A2), except for being far fewer characters and not adding to the oft inevitable nightmare of nested brackets.
WildcardsExcel has three wildcard characters:
Main takeaway: ‘*where*’ will match anything with 'where' in it. You can use this to check text for a single word. Wildcards do not work in all functions (you can't use them in SUBSTITUTE(), for example) but will work in SEARCH(), SUMIF(), SUMIFS(), COUNTIF(), COUNTIFS() and VLOOKUP(). You can use also wildcards in Find and Replace. Checking AdsPretty basic tip: You can check that all the bits of your ads are the right length using LEN(), and then having conditional formatting to highlight where text is too long. More advanced: what if you use dynamic keyword insertion? Then your ad text's actual length can be over 25, as AdWords won't count the '{KeyWord:}' when counting the characters. You can get around this by using: =LEN(A2)-10*COUNTIF(A2,”*{KeyWord:*}*”) You usually would use COUNTIF() on a range of cells, but you can also use it to check just one cell – if A2 uses DKI, then COUNTIF() will return 1, and if it doesn't COUNTIF() will return 0. There are 10 characters in '{KeyWord:}', so if COUNTIF() is 1 the formula gives then length of A2 minus 10. COUNTIF() is case insensitive, so it won't matter if your ads use '{keyword:', '{Keyword:' or '{KeyWord:'. Checking Search Query ReportsSearch query reports are great providers of negative and positive keywords. But it can be difficult to spot trends if people phrase their queries slightly differently. You can use SUMIF() and wildcards to see the performance of all search queries that have an individual word in them. First, download your search query report into Excel – here's an entirely made-up example: Then add a second worksheet, and set up these headings: The 'Word' column is for the word or phrase you're searching the search terms for. Add some words that recur in the search terms. In cell B2, we want to add up all the clicks of search terms containing whatever's in A2. So use the formula =SUMIF(‘Search term report’!A:A,”*”&A2&”*”,’Search term report’!E:E) There are three parameters inside SUMIF():
So the SUMIF() will look at ‘Search term report’!A:A (which is the column of search terms) and see if any match “*”&A2&”*”. So if A2 is 'cheap' then the function will look for search terms that match "*cheap*" – which means any search term that uses the word 'cheap'. It then sums the numbers in ‘Search term report’!E:E (the Clicks column). The formula for Impressions is =SUMIF(‘Search term report’!A:A,”*”&A2&”*”,’Search term report’!F:F) which is the same except that the third parameter is now the Impressions column. In the Cost column the third parameter should be ‘Search term report’!I:I, and in the Conv column the third parameter should be Search term report’!K:K. The CTR, CPC, Cost/conv and Conv rate columns can't be filled in using SUMIF(), as they aren't sums – calculate these from the other columns. Then copy the formulae in Row 2 and paste them downwards. In this example, searches with the word 'cheap' get good CTR and bad cost/conv, suggesting that 'cheap' should either be added as a negative keyword or effort needs to be spend improving their performance. It also shows there's a lot of traffic for 'blue' which converts well, so separating 'blue widgets' into their own ad group might web a good idea. What words should you check? Look at your search queries and see what keeps coming up. You might want to check for words suggesting an informational search (like 'why', 'how', 'what', 'where' or 'which'). Or you can check for people searching for websites with words like 'www' or 'com'. Note that the CTR calculated is likely to be higher than the actual CTR of all searches containing your word – some queries are collated under 'Other Search Queries', and if a search query had a click it's less likely to be one of those. So it may be better to focus on the cost and conversion metrics. Download the Sumif Example sheet here Any More?I hope you've found these tips useful – there's so much depth to Excel that it's easy to miss the things you can do with it. Please share your own tips in the comments! © SEOptimise - Download our free business guide to blogging whitepaper and sign-up for the SEOptimise monthly newsletter. Excel Hints for PPC Related posts: |
You are subscribed to email updates from SEOptimise » blog To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
Google Inc., 20 West Kinzie, Chicago IL USA 60610 |
You probably have very little privacy at all, giving it up a long time ago.
If you've got a charge card, the card company already knows what you do, where you go, how you spend your money, what your debt is like. If you use a cell phone or a computer, someone upstream already has access to where you go, what you buy, what you type, and on and on.
No, you don't really have a privacy.
What you care about, I'm guessing, is being surprised. You don't want to be surprised to discover that the card company is sending you gift certificates for VD testing because you've been staying at hourly motels. You don't want to be surprised that a site you've never visited seems to know an awful lot about your buying habits.
As computers get ever better at triangulating our interests and our actions, prepare to be surprised more often. It's not clear to me whether the never-ending series of little snooping surprises will eventually wear us out and we'll give up caring, or whether one day we'll sit up and demand that the surprises stop.
But privacy? Too late to worry about that.
[You're getting this note because you subscribed to Seth Godin's blog.]
Don't want to get this email anymore? Click the link below to unsubscribe.
Your requested content delivery powered by FeedBlitz, LLC, 9 Thoreau Way, Sudbury, MA 01776, USA. +1.978.776.9498 |
Hi, What tools do you use to check backlinks to your sites? Since we cannot use Google, Alexa or Yahoo! Site Explorer anymore, I want to share with you a couple of sites I find to be good at showing backlinks. If you know of any other free backlinks tool that is not in my list yet, please, share it in comments below the article. Your input is much appreciated. Thank you for your time!
|
Mish's Global Economic Trend Analysis |
About That "Increase" in Spain's January Car Sales Posted: 16 Feb 2012 09:25 PM PST Earlier today I posted some numbers on European Car Sales. Here they are again.
My friend Bran (who lives in Spain) writes ... Hello MishCar sales fall 13% in the first two weeks of February Courtesy of Google Translate (from a link sent by Bran), please consider Car sales fall 13% in the first two weeks of February Car registrations stood at 22,505 units in the first two weeks of February, representing a fall of 12.9% over the same period last yearI was struggling to believe that increase in Spain. However, I reported the numbers as given. The facts are now in: That increase was an "accounting mirage". In reality, Spain failed to beat 1993 car sales. Mike "Mish" Shedlock http://globaleconomicanalysis.blogspot.com Click Here To Scroll Thru My Recent Post List Mike "Mish" Shedlock is a registered investment advisor representative for SitkaPacific Capital Management. Sitka Pacific is an asset management firm whose goal is strong performance and low volatility, regardless of market direction. Visit http://www.sitkapacific.com/account_management.html to learn more about wealth management and capital preservation strategies of Sitka Pacific. |
Posted: 16 Feb 2012 12:50 PM PST John Mauldin posted an extraordinary interview by Kate Welling of Dr. Lacy Hunt, the chief economist of Hoisington Investment Management. Dr. Lacy Hunt correctly identifies fractional reserve lending as the culprit behind the massive rise in debt. Hunt also explains why government spending cannot help, why Europe is in worse shape than the US, why a US recession is coming, and why Ben Bernanke is an exceptionally poor student of the great depression. The entire PDF is a lengthy 29 pages, but well worth a read in entirety. Here are some pertinent snips from "Face the Music". Face The MusicDebt Stimulates Until The Collapse Lacy Hunt makes a stunningly good case why adding on more Keynesian stimulus is doomed to failure. Should Bernanke actually succeed at creating inflation, interest on the national debt would crucify us all. Please note the references to 100% backed money. Recently, there has been a number of seriously misguided articles on how the gold standard failed to prevent depressions prior to 1929. Such articles fail to point out that fractional reserve lending which allows extending more credit and making more loans than there is money is the culprit. The solution is 100% gold-backed money and the end of fractional reserve lending. Fisher and Hunt have this correct, as do the Austrian economists. Unfortunately, in their inflation predictions, most of the Austrian economists only consider money supply and not the collapse in credit and the value of that credit on the books of banks. This led to galling bragging by Keynesian economists who are likewise clueless about what is really happening and why. Simply put, what cannot be paid back won't, debt will collapse back to a more sustainable level, and benefit promises that people expect will be reneged on, just as is happening in Europe today. This process is the debt deflation cycle I have talked about at length for years. Mike "Mish" Shedlock http://globaleconomicanalysis.blogspot.com Click Here To Scroll Thru My Recent Post List Mike "Mish" Shedlock is a registered investment advisor representative for SitkaPacific Capital Management. Sitka Pacific is an asset management firm whose goal is strong performance and low volatility, regardless of market direction. Visit http://www.sitkapacific.com/account_management.html to learn more about wealth management and capital preservation strategies of Sitka Pacific. |
European Car Sales Plunge: Portugal -47.4%, France -20.7%, Italy -16.9% Posted: 16 Feb 2012 08:33 AM PST Please consider auto sales numbers from a Google Translation of the article Portugal leads drop in car sales in Europe.
Central and Eastern Europe had booming sales, with Romania an increase of 86.4%, and Latvia (+44.8%) and Hungary (+43.9%). Those are interesting Eastern European numbers but they are statistically meaningless compared to the declines in France, Italy, and elsewhere. Renault Leads European Car-Sales Drop With Fiat, Peugeot as Growth Stalls Bloomberg reports Renault Leads European Car-Sales Drop With Fiat, Peugeot as Growth Stalls Renault SA (RNO), Fiat SpA (F) and PSA Peugeot Citroen (UG) led the biggest decline in European car sales since June as consumers balked at making big purchases after the region's economy shrunk.Buckle up for a massive recession in Europe because one is coming. Mike "Mish" Shedlock http://globaleconomicanalysis.blogspot.com Click Here To Scroll Thru My Recent Post List Mike "Mish" Shedlock is a registered investment advisor representative for SitkaPacific Capital Management. Sitka Pacific is an asset management firm whose goal is strong performance and low volatility, regardless of market direction. Visit http://www.sitkapacific.com/account_management.html to learn more about wealth management and capital preservation strategies of Sitka Pacific. |
Posted: 16 Feb 2012 07:20 AM PST Housing starts are near three-year highs, but that is in comparison to extremely weak numbers for the past few years. Builders began just 430,900 single-family homes last year, the fewest on records dating back a half-century. Moreover, home prices are still dropping. Yet, signs of a bottom in construction, not prices, may be at hand. Yahoo! Finance reports US housing starts rise modestly to start new year The Commerce Department said Thursday that builders broke ground on a seasonally adjusted annual rate of 699,000 homes in January. That's up 1.5 percent from December and nearly matches November's three-year high for starts.Perspective on Sentiment Ian Shepherdson, chief U.S. economist at High Frequency Economics said "The new home sales numbers have not yet responded but builders seem confident that if they build, buyers will come." I suggest, if builders were confident they would be building, not yapping about confidence. Perspective on Starts Calculated Risk has a pair of charts that will add a bit of perspective on the rise in starts. This is one of the charts. click on chart for sharper image Tentative Signs of a Bottom in Construction The charts do suggest bottoming action in construction. If so, housing will add to, rather than subtract from GDP. However, these are very depressed levels, size of houses has dropped, and overhead supply of REOs and foreclosures will put a damper on prices for years to come. Mike "Mish" Shedlock http://globaleconomicanalysis.blogspot.com Click Here To Scroll Thru My Recent Post List Mike "Mish" Shedlock is a registered investment advisor representative for SitkaPacific Capital Management. Sitka Pacific is an asset management firm whose goal is strong performance and low volatility, regardless of market direction. Visit http://www.sitkapacific.com/account_management.html to learn more about wealth management and capital preservation strategies of Sitka Pacific. |
You are subscribed to email updates from Mish's Global Economic Trend Analysis To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
Google Inc., 20 West Kinzie, Chicago IL USA 60610 |