Central Perk

joi, 9 mai 2013

Using Excel to Help Move to Enhanced Campaigns

Posted: 08 May 2013 02:29 AM PDT

A few months ago Google announced Enhanced Campaigns. One of the major changes this brings is that segmenting Search Network campaigns by device is impossible. Tablet is now lumped together with desktop. Bids for mobile can be altered (as a campaign level bid adjustment), and you can turn off mobile advertising all together, but you can't turn off desktop targeting to have a mobile-only campaign.

Your options are campaigns that target desktop and tablets, or campaigns that target desktop, tablet and mobile (with adjusted bids). This means that if you had different versions of campaigns for different devices, when you upgrade you can only keep one active. But if you’ve had separate campaigns they are likely to have grown apart, even if they started as simple duplicates.

So, let's say you’ve decided to keep the desktop campaign but you have tablet and mobile campaigns you want to merge into it. You need to check for differences, then judge whether anything that was only in the mobile or tablet campaigns should be copied into the desktop campaign. But how?

From the title you may have guessed: I suggest using Excel.

Firstly, download your account into AdWords Editor. Then go to the File menu, 'Export spreadsheet (CSV)', and 'Export selected campaigns and ad groups' – select the campaigns you're interested in and click ‘OK’.

Open the CSV you’ve just made in Excel. There will be many columns – what columns you get will depend on what AdWords features your account uses.

Before we do anything, we're going to need to add some more columns. Copy the Campaign column (column A) and paste it into the first empty column (in my case it's column AS but it may be different for you). Rename the first cell 'Campaign Root'.

The idea is that all campaigns that are going to be merged should have the same Campaign Root. If you have a naming convention that gives you names such as "Things – Desktop", "Things – Tablet", "Things – Mobile", "Widgets – Desktop", "Widgets – Mobile", etc, then use find and replace to get rid of " – Desktop", " – Tablet" and " – Mobile" to leave Campaign Root column saying "Things" or "Widgets". If you haven’t got a consistent naming scheme you may have to manually change the campaign names.

Then add the column name "Is Primary". This will say if the campaign is the primary campaign – the one you'll keep when you’ve upgraded. Fill the column with 'Yes' for rows from the primary campaign, and leave them blank for the other campaigns (i.e. the mobile and tablet duplicates).

Then add to the top row the following column titles:

"Is Ad"
"Ad Check"
"Is Keyword"
"KW Check"
"Is Sitelink"
"Sitelink Check"
"Is Campaign Setting"
"Setting Check"
"Ad Group Bid"
"Keyword Bid"
"Primary Bid"
"Bid Adjustment"

Then select the entire worksheet (press Ctrl and A), go to the Insert menu, and click 'Table'.

There will be a dialogue box – click OK.

Your data should now be a table – the column headers have drop down buttons next to them, and everything has probably changed colour (in my case to blue).

Why are we turning this into a Table? Mostly so the columns are named sensibly. The file you create will have different columns depending on what features are in your account – having display campaigns, location extensions, or dynamic search ads will add extra columns. It's easier to keep track of what's happening if you know you're looking at the column of display URLs rather than knowing you're looking at column X (which contains display URLs some of the time). It also makes writing this blog post easier!

Note that [@Column Name] or [@[Column Name]] means the cell of the named column in the current row, while [Column Name] refers to the whole column.

Scroll sideways back to the column 'Is Ad'. In the first cell underneath type in
=AND(NOT(ISBLANK([@Headline])),NOT([@[AdGroup Status]]="Paused"),NOT([@Status]="Paused"))
It should be automatically copied into the entire column.

This formula is true if Headline isn't blank, the ad group isn't paused, and the ad isn't paused – which is to say, it's true if and only if the row is an active ad.

Then go to the first cell under 'Ad Check' write this code:
=IF([@Is Ad],COUNTIFS([Campaign Root],[@Campaign Root],[Ad Group],[@Ad Group],[Headline],[@Headline],[Description Line 1],[@Description Line 1],[Description Line 2],[@Description Line 2],[Display URL],[@Display URL],[Destination URL],[@Destination URL],[Is Ad],[@Is Ad]),"")
The IF means that if the row isn't an ad, then this column is blank.

Then the COUNTIFS checks how many rows have the same campaign root, ad group, ad text, and destination URL as the current row. If the answer is one then the ad isn't in all the campaigns. If the answer is three then the ad is in all three campaigns. If the answer is two then the ad is in two campaigns but not one.

Under "Is Keyword" type
=AND(NOT(ISBLANK([@Keyword])),NOT([@AdGroup Status]="paused"),NOT([@Status]="paused"))
This is true if the row is a keyword that's not paused or in a paused ad group. Note that this covers negative keywords as well as positive ones.

Under “KW Check” type
=IF([@Is Keyword],COUNTIFS([Campaign Root],[@Campaign Root],[Ad Group],""&[@Ad Group],[Keyword],[@Keyword],[Criterion Type],[@Criterion Type],[Is Keyword],[@Is Keyword]),"")

If the row isn’t a keyword, it's blank: if it is, then it's the count of how many duplicate campaigns the keyword is in.

Why do we need “”&[@Ad Group] rather than just [@Ad Group]? It's a bit complicated. The ad group could be blank, if the keyword is a campaign level negative. But there is an oddity of the COUNTIFS function – an empty criteria will be treated as 0 (a number), while the empty cells in the criteria range are treated as "" (a string). Using ""&[@Ad Group] as the criteria means that if there is no ad group the function will see [@Ad Group] as "" and count the number of rows where [Ad Group] is blank (rather than treating the [@Ad Group] as 0 and looking for 0s in the [Ad Group] column).

Under "Is Sitelink" type
=NOT(ISBLANK([@Link Text]))
This just checks that there's link text (unenhanced sitelinks can't be paused so there's no need to check the status).

Under "SL Check" type
=IF([@Is Sitelink],COUNTIFS([Campaign Root],[@Campaign Root],[Link Text],[@Link Text],[Destination URL],[@Destination URL]),"")
This counts the number of duplicate campaigns the sitelink is in.

Under "Is Campaign Setting" type
=OR(NOT(ISBLANK([@Campaign Daily Budget])),NOT(ISBLANK([@ID])))
Some rows contain the campaign's budget, language, ad schedule, and networks. Others contain the locations targeted.

And under "Setting Check" type
=IF([@Is Campaign Setting],COUNTIFS([Campaign Root],""&[@Campaign Root],[Languages],""&[@Languages],[Ad Schedule],""&[@Ad Schedule],[Networks],""&[@Networks],[ID],""&[@ID],[Location],""&[@Location]),"")
This counts the number of duplicate campaigns with the same language, location, ad schedule, and networks settings.

Note: COUNTIFS only works if strings are under 255 characters, and if you have a complicated ad schedule it may be over this limit. In that case the formula may return 0 and you'll have to check manually whether the settings in duplicate campaigns are the same or not.

That's all the formulae! Now you can use these new columns to filter – if a Check column shows a number less than the number of duplicate campaigns, then the row is an item that is not the same in all of the duplicate campaigns.

For example, if you have three versions of each campaign (desktop, mobile and tablet) then any Check column saying two or less shows something that is different between the campaigns: if you went to the KW Check column and filter for 1s and 2s, you'll get a list of keywords that aren’t the same in all versions of the campaign. You can order by keyword to make it clearer which campaigns a keyword is in and which it isn’t in.

You'll have to decide for yourself what to do with these. Why is the keyword not running on all devices? If it's because it performed badly on something you'll have to weigh up that against losing the keyword on the device it does work on. Similarly, compare sitelinks and ads – you can keep separate mobile ads and sitelinks, but you can't have tablet-only versions – and campaign settings.

If you want a look at my example spreadsheet, download it here.

Have you been using any Excel tricks or other tools to help you upgrade to enhanced campaigns? Let us know in the comments!

Seth's Blog : Who do you know?

Who do you know?

Let's define "know" as... you're connected with them, in real life, by email or through a direct relationship online.

It might be someone in a different state, religious, atheist, straight, gay, in a developing country, a lawyer, a politician, struggling to pay the bills, ill, recovered, in recovery, a dedicated athlete, a computer programmer, angry at the system, an insider, an inventor, from a very different political stance, a pilot, unemployed, a millionaire, an inventor, a tax cheat, a gun owner, a rabble rowser or an adult without a driver's license.

Can you see them? Understand them? Ask them about what it's like to be them? Would you miss them if they were gone?

Sixty years ago, TV news changed everything, because it introduced us to ideas and places outside of our personal experience. Today, like it or not, despite the fact that we continue to segregate the places we choose to live by politics and race, the online social network is anti-gerrymandered. Connect with enough people and you can't help but bump into something outside your worldview.

The question is: now that we know these people, will we listen to them in an effort to understand? Tom Friedman famously wrote that there's never been a war between two nations that had McDonald's franchises in them. I wonder if we're going to develop a new sense of mass, one where it's harder than ever to demonize a group that contains your friends, even if they're merely online friends. Or, are we going to get better at hating people we know, at de-personalizing our experiences...

When they're no longer faceless strangers, is it more difficult to hate them?

• Email to a friend •

miercuri, 8 mai 2013

Mish's Global Economic Trend Analysis

Hugely Negative Real Interest Rates Fuel Yet Another Housing Bubble; A Word About "Inflation" and Treasury Yields

Posted: 08 May 2013 12:20 PM PDT

It's easy to spot a Fed-sponsored housing bubble if you look in the right places. The best place to start is an analysis of price inflation as measured by the BLS as compared to a CPI-variant that takes actual housing prices into consideration instead of rent.

This is a followup to my post Dissecting the Fed-Sponsored Housing Bubble; HPI-CPI Revisited; Real Housing Prices; Price Inflation Higher than Fed Admits.

Data for the following charts is courtesy of Lender Processing Services(LPS), Specifically the LPS Home Price Index (HPI).

The charts were produced by Doug Short at Advisor Perspectives. Anecdotes on the charts in light blue are by me.

Background

The CPI does not track home prices per se, rather the CPI uses a concept called "Owners' Equivalent Rent" (OER) as a proxy for home prices.

The BLS determines OER from a measure of actual rental prices and also by asking homeowners the question "If someone were to rent your home today, how much do you think it would rent for monthly, unfurnished and without utilities?"

If you find that preposterous, I am sure you are not the only one. Regardless, rental prices are simply not a valid measure of home prices.

OER Weighting in CPI

click on any chart for sharper image

OER is now at 24.041% of CPI, which still rounds to 24.0%, but the other housing wedge is now an even 17.0%, down from 17.1% in the previous version.

The rest of the charts show various effects if one substitutes actual home prices as measured by the HPI in the data.

Two Inflation Indexes

click on any chart for sharper image

As measured by the CPI, price inflation is 1.47% annualized. As measured by HPI-substitution, price inflation is a much higher 3.33%. The Fed would have you believe everything is under control. Of course they said the same thing in 2005.

Real Interest Rates

Real interest rates are the difference between the Fed Funds rate and measures of inflation. The chart shows real interest rates as measured by the CPI vs real interest rates as measured by HPI-CPI. As measured by the CPI, real rates are -1.83%. As measured by the HPI-CPI real interest rates are-3.18%. For comparison purposes, real interest rate were -4.86 in mid-2004. The housing bubble burst one year later.

Fed Misses the Obvious

The above chart shows how much home prices as measured by HPI diverged from OER. And here we go again.

A Word About "Inflation" and Treasury Yields

This post is about "price inflation". It does not change my views on credit which I believe is headed for another bust. It also shows how hard it is to actually measure prices.

It's easy for the Fed to say everything is under control when it ignores everything important: housing, energy prices, education, and massive bubbles in the stock market, and junk bond market.

What about treasuries?

When the stock market and junk bond market bubbles burst, the Fed is as likely as not to go further out on the yield curve to suppress rates. Look for the Fed to keep doing the same thing over and over and over again.

Fools never learn as noted in a recap of the Fed Uncertainty Principle written April 3, 2008 before the Bernanke Fed started slashing rates in the Global Financial Crisis.

Fed Uncertainty Principle:The fed, by its very existence, has completely distorted the market via self reinforcing observer/participant feedback loops. Thus, it is fatally flawed logic to suggest the Fed is simply following the market, therefore the market is to blame for the Fed's actions. There would not be a Fed in a free market, and by implication there would not be observer/participant feedback loops either.

Corollary Number One:
The Fed has no idea where interest rates should be. Only a free market does. The Fed will be disingenuous about what it knows (nothing of use) and doesn't know (much more than it wants to admit), particularly in times of economic stress.

Corollary Number Two: The government/quasi-government body most responsible for creating this mess (the Fed), will attempt a big power grab, purportedly to fix whatever problems it creates. The bigger the mess it creates, the more power it will attempt to grab. Over time this leads to dangerously concentrated power into the hands of those who have already proven they do not know what they are doing.

Corollary Number Three:
Don't expect the Fed to learn from past mistakes. Instead, expect the Fed to repeat them with bigger and bigger doses of exactly what created the initial problem.

Corollary Number Four:
The Fed simply does not care whether its actions are illegal or not. The Fed is operating under the principle that it's easier to get forgiveness than permission. And forgiveness is just another means to the desired power grab it is seeking.

I don't care that much for treasuries here as I see no value, but that does not make them a good short. One look at Japan shows central bank sponsored low interest rate paradigms can last a lot longer than most think.

Mike "Mish" Shedlock
http://globaleconomicanalysis.blogspot.com

Self-Serving Recommendation of the Day: Visa Asks Spain to Lower Limit on Cash Transactions

Posted: 08 May 2013 12:22 AM PDT

Spain's underground economy is reportedly 19% of GDP. Is it? Who knows? Whatever it is, Visa has its eyes on transaction fees while holding a carrot in front of the Spanish government regarding more taxable income.

Via Mish-modified translation from Libre Mercado, please consider Visa recommends Spain further limit the use of cash transactions.

The black market economic activity is beyond the control of the Treasury, and is one of the major objectives of the government during the current crisis to try to raise tax revenues. In 2010, Spain decreed the obligation to report all transactions greater than $ 3,000. In 2012 a ban was placed on cash payments in excess of 2,500 euros.

Visa Europe now recommends that the Government of Mariano Rajoy further restrict the use of cash to combat the underground economy and, thus, increase tax collection. Visa suggests a measure similar to that adopted in Italy, where the limit is set at 1,000 euros.

Why Does the Black Market Exist?

Step back for a second and ponder why there is a black market. Is it because taxes are too low? Of course not.

The black market exists in size because people are fed up with government confiscation of their hard-earned money that the government then goes on to waste on ridiculous pet projects and to bail out banks that are in trouble.

There will always be some fraud and corruption, but 19% of GDP, if true, is one hell of a black market. Rather than crack down on the taxes purportedly lost, I suggest eliminating the economic conditions that created such a sizable black market in the first place. The place to start is with an overhaul of the preposterously high VAT scheme.

Mike "Mish" Shedlock
http://globaleconomicanalysis.blogspot.com

Damn Cool Pics

Disappearing Friend Photo Prank [Video]
Leipziger Buchmesse 2013 Cosplay
100-Year-Old Oklahoma Time Capsule Opened
How to Make Scrambled Eggs inside Their Shell
California is Doomed! [Infographic]

Disappearing Friend Photo Prank [Video]

Posted: 08 May 2013 06:56 PM PDT

Following their imaginary friend photo prank, Youtube pranksters Rahat Hussein and Jack Vale are back with a new one, confusing people at a shopping mall after asking them to take a picture of them.

Leipziger Buchmesse 2013 Cosplay

Posted: 08 May 2013 12:34 PM PDT

Nice cosplay costumes and a lot of pretty girls at the Leipziger Buchmesse 2013, Germany.

100-Year-Old Oklahoma Time Capsule Opened

Posted: 08 May 2013 12:08 PM PDT

This time capsule was buried under the First Lutheran Church of Oklahoma City 100 years ago, in 1913. Now the time capsule is opened. All these items were inside it.

How to Make Scrambled Eggs inside Their Shell

Posted: 08 May 2013 11:06 AM PDT

Got to try this. Scrambled eggs inside their shell.

California is Doomed! [Infographic]

Posted: 08 May 2013 10:29 AM PDT

Problems run amok at all levels in the government – local, state, and federal. Years of outrageous healthcare benefits, pensions and salaries have made municipalities so broke that they cannot afford to pay for police, firefighters or even garbage collectors. It's been voted the worst state to do business by CEOs for 7 years now. It is evident, California is doomed. Are your projections as bleak as ours? Check out our infographic and tell us what you think!

Click on Image to Enlarge.

Via Staff

joi, 9 mai 2013

Using Excel to Help Move to Enhanced Campaigns

Using Excel to Help Move to Enhanced Campaigns

Seth's Blog : Who do you know?

Who do you know?

More Recent Articles

miercuri, 8 mai 2013

Mish's Global Economic Trend Analysis

Mish's Global Economic Trend Analysis

Damn Cool Pics

Damn Cool Pics

Pagini

Persoane interesate

Arhivă blog

joi, 9 mai 2013

More Recent Articles

miercuri, 8 mai 2013

Pagini

Persoane interesate

Arhivă blog

Subscribe to