Don’t you hate it when you are building a big SEO spreadsheet and you need a heap of tabs for each segment of a clients website? One of my analysts, James, found this great ninja trick to automatically create as many tabs of a sheet as you need.
Below is the code.
Dim cell As Range
Dim WSNew As Worksheet
For Each cell In Sheets(“Sheet1″).Range(“A2:A100″).SpecialCells(xlCellTypeConstants)
Set WSNew = Worksheets.Add
On Error Resume Next
WSNew.Name = cell.Value
If Err.Number > 0 Then
MsgBox “Change the name of : ” & WSNew.Name & ” manually”
On Error GoTo 0
Simply place the list of tabs names in the first tab in A2, add the above macro into the Visual Basic editor and then hit run.
I know this has saved us heaps of time on very large SEO projects. Hope it helps you out.
For those of you analysts and SEM’s out there who don’t currently use multi-channel funnels you are missing out of a huge opportunity for your clients. Multi-channel funnels can improve your ROI from search by over 100%. Here is a bit of a guide on how to do analysis on multi-channel funnels for Search Engine Marketing.
The first thing to do is to find the multi channel funnels report within Google Analytics. I would hope you know where this report is… but here are the directions. Please ensure you are in the new version of Google Analytics and navigate down to the ‘Conversions’ section and then into ‘Multi Channel Funnels’.
The report we will be working with today is the ‘Assisted Conversions’ report. See below.
There are two key types of conversions that we need to identify here. One is a single visit conversion and next is a conversion that happens over a number of visits. To do this we use the advanced conversion segments at the top of this page.
To work out how many times SEM was either the final conversion in a chain, or created a conversion on a single visit you just need to look at the report unfiltered and then grab the ‘Last Click’ conversion number and value.
Next to work out how much SEM contributes to conversions that are more than one visit long we need to use the advanced segment ‘Path Length > 1′. Select this segment and hit apply and you are presented with a whole new set of numbers. The numbers you are interested about in this report as the ‘Assisted Conversions’ and ‘Assisted conversion value’ and the ‘First click conversions’ and ‘First click conversion value’.
These numbers are the number of times (and value) that SEM was involved in a conversion that was more than 1 visit long. These are the upper funnel conversions and even though they may not trigger the conversion they attract users into the funnel and this is all important.
The basic report shown above simply groups all SEM traffic under paid search, however with a simple click on ‘Other > Traffic sources > Keyword’ you then open up one of the most valuable reports within Google Analytics. Now you know which keywords are driving conversions at the final stages of the conversion funnel, and which words are critical at the top end of the funnel.
I use a methodology to weight First clicks & Assisted clicks differently from Last clicks. I have found that this produces the best results for my clients. I export this report from Google Analytics, Adjust it so that it marries up with the Adwords report and then I can easily determine the real CPA and real ROI for all keywords.
There are also heaps of other reports within this area of Google Analytics so please let me know how you are using these reports.
Great info about the Australian open from our favorite Twitter users!
I know this sounds like the most boring blog title ever, but I’m sure many analysts have had this problem before and wanted to find the answer to this annoying question. So here goes…
Don’t you hate it when you have to analyse a list of URLs but the data you have received has dynamic URLs for the same page which you just want to treat as one page. Simple, just used the Excel remove duplicates function right? Wrong. The strings are unique so Excel won’t trim them down for you.
Lets say you want to remove all of the characters after the ‘?’ there is actually a way you can do this in Excel which makes use of the ‘LEFT’ function. Below is the code that you copy into the cell next to the cell you want to shorten the string. What this function does is search through the string for the ‘?’ character and the deletes everything past this point.
Now you have a beautiful list of URL’s ,many of which will now be the same. You can now use the trusty remove duplicates function and you now have your shortened list of URLs that you need to analyse.
Yesterday Google launched Google plus for businesses in Australia and across the globe. This is very exciting for many in the US where Google plus has had a very high uptake rate, but it is less exciting for me in Australia as we still have a very shallow penetration of Google Plus.
If you want to setup a Google Plus page for your business in Australia head over here. It only takes about 2 minutes to setup and then you can interact in Google Plus as your business, much the same way as you can in Facebook acting as a page and not an individual.
Time will tell how much value Google Plus will be for businesses in Australia and across the world, but it could be one of those slow burns that you just have to keep adding value to slowly.
Check us out over at https://plus.google.com/s/fenton%20stephens
Well most of the bugs are out of the new Multi-Channel funnels in Google Analytics so I thought it was time for me to write a post about it and share some of my expeiences. First of all I have to say that the new features, which has finally made its way across from Adwords is a fantastic addition to Google Analytics. We now have a very clear view of what areas of our marketing efforts are actually contributing to conversions.
First of all I will explain what a conversion path is. The most simple way to explain this is just to use the basic chart out of Google Analytics. As you can see, all of the interactions leading up to the conversion are now stored within Google analytics and even if the source/medium/campaign does not get the last interaction conversion it is now awarded the assisted conversion. This means that many of the traffic acquisition funnels are now being heralded for all of the new customers they are introducing to the brand, which were previously missed.
This leads to some very powerful analyses to see which conversion parts are interacting with other conversion paths. This leads to one of my favourite charts, the humble Venn diagram. As you can see below, it is now very easy to see which conversion paths interact with others.
As you can see in this example, there is a very big overlap between paid and organic search. The area of organic which is mutually exclusive to paid is a very important area as these are most likely the punters that don’t trust ads and need to be acquired via SEO.
Some other interesting analyses are the impact of social media on customer acquisition which has now been made a great deal easier. It is still not perfect, but you can now get a much better idea of how you social efforts are impacting your bottom line by selecting social media along with other key converting channels like SEM & SEO.
There are still a few issues with filtering and multi-channel analytics but I’m sure that these will be sorted out over the coming weeks.
So the question I pose to you all is what insights have you found for your clients by performing multi-channel analysis in Google Analytics?
There is an interesting new campaign by Australia Post hitting the market called Zing. Zing is a replacement word for anything sent by Australia Post. I love the creative and the ads are quite interesting, I’m just not a huge fan of the online execution.
The call to action on the TV ad is interesting – ‘Search Zing’ – however the major concern for an external strategist is that yes they have number 1 ad position for this term which has very little competition (for now) but hey do not have the #1 organic term. The bleed from this would be quite large.
One of the biggest shake ups in internet history happened this week with ICANN approving the new top level domain decision. What this means is that now businesses can apply to create new domain extensions such as .com & .net etc. The application process costs $185,000 which means that it will only be available to very large organisations.
This is great news for large businesses so they can stand out from the crowd. Cyber-squatting will be much less of an issue for example;
It is quite easy to cybersquat something like http://www.appl3.com but it is impossible to emulate http://www.genuine.apple
What are your thoughts on these latest changes? Do you think they will give huge brands a distinct advantage over small to medium brands? Time will tell.
Check out this cool infographic – a great summary of the changes.
Fantastic news for SEOs out there with the news that Google are going to be integrating these fantastic tools. Google Webmaster tools provides some amazing insights for SEOs and with this data now being integrated into Analytics it ill be much easier to track keyword position performance.
More info here.
If you haven’t got Webmaster tools setup on your website its a very simple process. Just go over to WebMasterTools and get up your account.