Dear O2 – Wiping My Phone Is NOT Acceptable

Dear O2,

Wiping my phone is NOT acceptable when I bring it in to get ‘repaired’!!


Let me bring you up to speed;


  1. My lovely Samsung Galaxy S2 (yes it’s not the S3, it is from my current contract which isn’t due for renewal yet) decided to die on me recently
  2. So I decided not to bother you. Instead I decided to test the phone myself by trying different charging cables and even buying a new battery, aren’t I nice not to bother you like that. But hey, unfortunately none of that worked and I had to bring it down to your shop to ‘fix’. 
  3. I did just that and dropped it off with you. 
  4. Then you kindly updated me every step of the way to tell me how my phone was doing, see the pictures below (these were taken on my iPhone 3gs which I was using as my back up at the time). 




Now here is where it all goes wrong. Very wrong. 

I bring my phone back home and plug it in, very excited to get it back in one piece and all fixed without any issues. Then I turn my phone back on and it is back to the factory settings!! What?! Where has all my stuff gone?!?!?  :-( 

Here is the letter which I received along with my phone… (note, emphasis mine)


Dear Mr M Cropper, 

Thanks for sending us your phone to be fixed. 

Nathan, one of our engineers, has found the problem and fixed it. For quality control, another one of our engineers checked the repair. The problem we sorted out is covered by a 90-day guarantee. 

We’ve also installed the latest O2-approved software as part of our standard repair process. This should improve your phones performance

You can put your SIM back in your phone and start using it straight away.

We try not to lose any of your data (like contacts and text messages) when we fix your phone, but it is not always possible. If you have backed up your personal data to O2 Bluebook, you can log in and download it. Goto

Any questions? Call oiur UK based Customer Services team, you can get the number online at or visit one of our O2 Stores. 



O2 Repair Centre Manager


Now lets get a few things clear here. 


Number 1:

What genius thought went through little Nathan’s mind to decide it was a good idea to wipe all of my data from my phone? You mention in your letter that “If you have backed up your personal data to O2 Bluebook, you can log in and download it” – May I suggest that this is checked before deleting data as part of the “standard repair process”!

Losing my data is NOT acceptable! If you have the automated backup services available then I suggest your use them and don’t rely on customers doing this as most people won’t know about this and/or won’t remember/be bothered to do this theirself. 

If I was told BEFORE sending my phone in to you to ‘fix’ then I could have done this myself. Strangely enough I don’t know about all of the different apps, services and tools available for phones these days. I struggle to keep up with my Facebook timeline let alone things that I would use once every 6 months!

This is not difficult to include in your processes – get this sorted out within your internal processes. In case you haven’t picked up on this yet – Losing my data while ‘fixing’ my phone is NOT acceptable!


Number 2:

“We’ve also installed the latest O2-approved software as part of our standard repair process”

You’re telling me that you wipe all customers phones as part of your ‘standard repair process’??? I sincerely hope not…. 

What I hope this means is that you simply upgrade things without wiping them – although based on recent experiences I wouldn’t put it past you! 


This should improve your phones performance. “

It certainly has improved the performance of my phone that’s for sure! You know why? Because there is nothing installed on it, there are no contacts and all of my photos have been deleted!!! That’s one thing that is correct about this whole scenario so far!


Number 3:

“You can put your SIM back in your phone and start using it straight away.”

No. No I can’t. Because I have to waste a week of my life copying across all my old contacts, sending out requests on social media channels for peoples contact details and downloading the endless apps which were installed on my phone. 


Next steps

The first thing I do is then get back onto your customer service channel on the phone and am ultimately told that there is nothing that can be done. The data is gone. There is nothing which can be done to retrieve it. Again, 😥

So here is how the conversation went with your customer services person named Ralph;

Ralph: I’m sorry that you’ve lost all your data. I can understand this can be upsetting, let me check this for you now.

Ralph: I’m sorry we won’t be able to compensate you by anyhow for the loss of data.

Me: A simply “sorry we lost your data” is not acceptable..

Ralph: Could you confirm me so as how do you want us to compensate you for the loss of data?

Me: 2 months free contract

Ralph: Let me check the best I can do for you.

Ralph: I’ll decrease you line rental by now applying a loyalty discount of £6 to your account for the next 12 months

Me: Can you do any better?

Ralph: I’m sorry that was the best I can do for you now.

Me: Ok, well discount that for now. Can you discount further if I call back tomorrow? This is a huge inconvenience for me and I have been a loyal customer for years..

Ralph: I completely agree with you however that was the best I can do for you and will surely add this to the account notes.

Me: Ok I will take the £6 per month discount for 12 months as compensation

Ralph: Thank you so much for your understanding.

Ralph: I really appreciate that.

Ralph: Is there anything else I can help you with?

Me: That is all thanks

Ralph: You’re welcome.

Ralph: Goodbye and take care

Ralph: Have a nice time.

Me: I think O2 has done enough to ‘help’ this week’

Me: Bye


You mentioned that your customer service team were UK based…Hmmm. Based on what I am reading in the conversation I doubt that very much (and my English is by no means perfect!!). 

Now lets get things clear. I’m sure both Nathan and Ralph are lovely people, my issue is not with them as I’m sure they have done their jobs to the best of their ability. My issue here is that your internal process at O2 is absolute nonsense to actively allow staff to delete customers data from their phones without simply asking them if this is ok!

You took the time (albeit automated messages) to let me know what was happening with my phone – how about getting one of your staff to give me a call (after all you don’t have to pay excessive charges to make these calls!) and simply explain the issue to me and ask me what I would like to do about it. 

When I asked your customer service team on the phone what notes had been left on the system about why my phone was wiped, there was nothing. 

I am sure you are aware of this O2, but people use their phones for so much more than simply making phone calls these days. People run their lives from their phone. Emails. Texts. Photos. Social networks. Events. Calendars. Mobile banking. Note taking. To-do lists. Storing contacts. Do you need me to go on? 

Simply wiping peoples phones without ANY explanation or reasoning is simply NOT acceptable when a loyal customer sends their phone in to be ‘fixed’

Here is my feedback which you will no doubt have received and ignored by now by placing it in the ‘disgruntled customer’ box..




A disgruntled customer


Michael Cropper


Update 31st January 2013

Since writing this post a couple of months ago I have been seeing more and more queries come into this blog post from confused or disgruntled customers. This issue is clearly not just an issue that I have had, but is an apparent problem from many different people. 

Here are some of the things people are searching for to find this blog post;


  • Got my phone back from o2 repair all photos messages gone
  • O2 repair
  • Acceptable time to repair a phone
  • Company wiped your mobile with personal when in for repair
  • Compensation from 02 shop for loosing my memory card
  • Deleting o2 phone data before repair
  • Do o2 repair phones
  • Do o2 stores fix repairs
  • Goto
  • If if send my phone off to be repaired do they wipe it
  • If my i phone gets sent away do they wipe my phone
  • If o2 wipe my phone can i get messages back
  • Is all data wiped before phone repair
  • My o2 text
  • O2 not wiping data
  • O2 phone repair lose data
  • O2 phone sent for repairs
  • O2 repair centre wipe phone
  • O2 repair process
  • O2 repairs additional charges
  • O2 wiped my phone can i get my photos back
  • O2 wont let me upgrade my phone online
  • Phone is being repaired will they wipe it
  • Sending phone off to repair o2
  • When phone sent off for repair, is all internal memory wiped?
  • When phones get sent for repair, do they get wiped?
  • Why does my phone data get wiped when repaired
  • Will a phones internal memory be wiped when it goes for repair
  • www.o2/repairs


I think I speak for everyone who has arrived at this blog post after being confused by what happens during the repair process – Dear O2, Communication solves all problems! Please listen to disgruntled customers and do something about this. 


P.s. O2, if you would like these disgruntled customers going to your website instead of mine, then I’m sure I can help you with that :-)

The Travel Industry Must Be Shaking in Their Boots

Why? Because of the latest news announced by Hotel Chatter in an article titled “Google Now Lets Us Virtually Stalk Hotels Without Ever Actually Visiting Them”

This is where you can now use the Google Street View technology to walk around inside hotels. That’s right, view the receptionists, view the lobby, view the restaurant and see what nice plants they have on display. 



When you enter street view within the hotel listed then you can take a good look around. Take a look at their lovely reception they have at Hotel Lucia;



After having a short walk around the lobby and reception area (which appears to have been bombed with fresh apples – they must have got a discount on these before the photo shoot!!), I decided I want to take a look around the hotel a little more so I go in the lift to floor 5;



Now lets take a look at one of their bedrooms in great detail;



Wow! A full 360 degree view of the bedrooms. You can clearly see what facilities they have in the rooms at a quick glance. Just awesome. 

As you would expect with this kind of new technology, nothing here is quite perfect initially and here is a lovely view of the tripod which took the photos where the algorithm hasn’t quite joined the pictures together in the most elegant way…



While this isn’t perfect at the moment, it is still leaps and bounds ahead of anything else in the travel industry and will likely be the case for quite some time to come. 

One thing to note is that this is still in its early days and has only been done on a small scale – yet. It will happen in the near future though. 


Where has all this come from?

Well actually Google has been interested in this area for quite some time. It was as early as 2010 when Google announced that they would be taking pictures of small local businesses in 30 cities around the world which would initially be for “Right now we’re focusing on businesses like restaurants, cafes, hotels, spas, salons, gyms, and retail stores”

Is this really a surprise that this has come about? I’m surprised it hasn’t happened sooner as the writing has been on the cards for a number of years now. 


Why this would worry the travel industry

Well certainly this isn’t much of a worry for the hotels involved in this scheme, it is great PR and brilliant for their guests. But this is extremely bad for the typical hotel aggregators that are around – and there are an awful lot of these around these days! This industry is full of aggregators and affiliates of affiliates of affiliates who ultimately sell all the same stock at the same prices and offer the same duplicated information about the hotels – where is the added value here? Martin MacDonald did a good post back in July about how affiliate marketing in travel shout be dead and how travel websites need to add real value to their real businesses. 

In reality, most travel aggregator websites simply don’t offer any more value than the next one. There is all the talk about ‘branding’ within the industry, but a brand is more than a nice colour scheme, logo and brand values. Branding is about actually making the website better, actually making the business better, making it stand out from the crowd, doing something uniquely different….e.g. what Google have gone and done. 

With travel accounting for such a large percentage of Google’s traffic / search queries then I am not surprised they are really pushing this area. The figure that springs to mind is that the travel industry accounts for around 10% of searches on Google – I can’t remember (or find!) where I read that figure so don’t quote that as gospel (hey, I may have made it up and convinced myself I read it somewhere! who knows). 

If you look at the recent work Google have done in the travel industry though;

  • Google Hotel finder launched
  • Google Flight Search launched 
  • Google bought Zaggat and integrated their reviews
  • Google bought Frommers and is working on integrating (no doubt!) this into their services
  • Google launches Street View for inside hotels


What is coming next? Well, if I were Google I would start to combine all of these wonderful products into one awesome ‘holiday’ tool which would allow me to easily pick & choose the right hotel based on  the great photos, user reviews and the interactive view of inside the hotel. This choice would be after I have chosen my perfect city to visit after reading the travel guide content provided from Frommers and then I would book a flight to the destination. – All at the click of a few buttons and under one large Google product. 

Then if I were Google, I would then put this awesome “Google Travel” tool at the top of the search results and begin to force it down peoples necks. And ultimately, this would be such as awesome tool that the other usual aggregators can’t compete with this.

What happens after that? Well, users then get used to this nice usability and extra functionality and begin to question why they are using their previous aggregator websites (the ones with the nice branding logo) when everything can be simply done via Google, so the consumer behavior begins to shift and the intermediaries are left wondering how the rug could have been pulled from under them so quickly.


What can be done?

Step up! (no, not the dance film). Step up as a business owner. Google has made a lot of strategic acquisitions over the past few years and has integrated those acquired technologies into new products and services. The writing is on the wall, Google is clear about where they are heading (regardless of the BS they spout out in official PR).

It is time to become the dominant player in the industry instead of letting Google walk all over a whole industry and potentially wiping out an awful lot of businesses over the next few years. It is going to take a lot of effort, an awful lot of effort to do this – but if little is done in the industry then don’t be surprised when Google finally screw everyone over. 


How to Analyse Traffic from Link Building Work

A large part of SEO is all about link building, you don’t need me to tell you that. What is important though is not just the changes in rankings which are happening due to the link building but also the amount of referral traffic which is coming through from this work. 

If your links aren’t generating traffic then I would begin to question the long term success those types of links are having on your website, rankings and brand.

Below outlines a quick and simple step by step process to quickly assess how effective your link building techniques have been in generating traffic to your website. 


Step 1: Download all referral traffic from Google Analytics

Go to Google Analytics –> Traffic Sources –> Sources –> Referrals



Then view the maximum amount of rows, 500 by using the filter at the bottom of the screen. 



Then export all of this data into a CSV file which can easily be opened later in Excel. Click on Export –> CSV at the top of the screen as shown in the screenshot below




Step 2 – Combine Links Data with Rererral traffic

The next step is to open up the file you have just download and re-save this as a normal Excel file as we will be adding in some more tabs, data and look-ups which standard CSV files aren’t designed to handle. 

Once you have done this do the following;


  1. Rename the first tab to ‘Google Analytics Referral Data’
  2. Create a second tab called ‘Links Built’
This will help when doing the cool excel magic a little later. 
Now you need to simply add into the tab ‘Links Built’ (you guessed it!) all of the links you have built! 
Below is a screenshot of some of the referral traffic to my website;


Below is a screenshot of some of the links which I have built to my site by shamelessly self promoting my content;



The links in the above screenshot are just a small sample I have scraped together for this blog post, I don’t actually keep track of this for my own blog – I have got much better things to do with my life :-) For people building links in competitive industries and on larger websites then you will likely have lists which go into the hundreds or thousands, so this method can really save some time for you. 

So now you have all of the link and referral data within one Excel file which you can then do some cool Excel magic on. 


Step 3 – Do Cool Excel Magic

Now you want to find out how many visitors the link building has resulted in. As mentioned previously, if you are just building links for the SEO value and not for traffic is this really going to be a good long term SEO strategy…? 

The next step here is to add an extra column next to the list of referral traffic to see if this referral traffic was from the effort you put into link building;



If you want to copy and paste the formula then it is;


 =IF(COUNTIF(‘Links Built’!$A$2:$A$100,CONCATENATE(“*”, A8, “*”))>0,”Yes”,”No”)


The formula may look a little scary but all it is essentially saying is: “See if this domain in A8 is contained within the list of links which I have built”. For a full guide on what this means take a look through the blog post explaining this in detail, How to VLOOKUP Using Partial Match

The different parts of the formula are saying;

  • Count the range of cells, A2:A100 if….
  • The cell contains a partial match on cell A8
  • If there is a match, then put “Yes” in the cell
  • If not, then put “No” in the cell


Now simply drag this formula down through to all of your referral traffic to see if this was from a link which was built or not. 

Note, this check is only looking at the domain name and not page specific. So if you built a link on which drove 0 visits and you got an organic link on which drove 100 visits then all of this traffic would be attributed towards the link building work which you have done – which isn’t correct in this example. 

By default Google Analytics doesn’t display the full referral path, but only domain name. It is possible to set up an additional profile within Google Analytics, to get the full URL for referrals then follow the guide in the link. One thing to note is that Google Analytics profiles only show data from the date they were set up, so you cannot see historical data with this method. Although if you get it set up now then you can begin to get more accurate data in the future. 

Second note, the formula described above can also be run as a VLOOKUP if you need to pull the data back into this tab by simply editing the formula as follows (although this method will only bring back the first occurrence of the domain name mentioned, unless you have the full referral path within your analytics profile);


=VLOOKUP(CONCATENATE(“*”, A8, “*”), ‘Links Built’!$A$2:$A$100, 1, FALSE)


Step 4 – Analyse the Data

Now you will have ended up with a list of Yes/No’s which will tell you if you have built the link for this traffic source. Below is  an example of how this can look after you have filtered by all of the “Yes”;



As you can see from  three domains listed above, these have driven over 1000 visits to my blog in a short period of time. Are these good for SEO? Well traditionally you may argue that the links are no followed so they don’t offer any value. Personally I would rather have a no followed link which drives actual traffic and real people to my website instead of a followed link which doesn’t drive any traffic at all. 

I would suggest running reports like this on a regular basis to continually assess if the work you are doing is actually driving real users to your website and not just building links for the pure PageRank benefit of the link. If all of your links have driven 0 traffic to your website in the past X months then I would begin to ask yourself if what you are doing is going to be having real long term results for your website. 

If you also track the type of website where you have been building links such as, guest blog post, infographics, directories etc. then you can quickly assess which type of links are or aren’t driving traffic to your website which can help gain further insights into what is working from a traffic point. 


How to VLOOKUP Using Partial Match

If you have ever used the VLOOKUP function within Excel before and tried the “Approximate Match” type then you will have realised that this is about as useful as a chocolate tea cup. Below shows how you can easily and quickly perform a VLOOKUP using a partial match on the look up value. 

For those of you who just want the answer quickly then here is the formula to VLOOKUP on a partial match;


=VLOOKUP(CONCATENATE(“*”, A2, “*”), ‘Tab2’!$A$2:$A$100, 1, FALSE)


So what does all of that mean? 

The VLOOKUP formula is described as


=VLOOKUP({lookup this value or string}, {within this range of data}, {bring back this column number}, {TRUE (approximate match) or FALSE (exact match)}


So as an example if you have the following data to play around with;


Tab 1

Cell A2: “Jim”


Tab 2

Cell A2: “Jim Bob”


Then if you run the formula “=VLOOKUP(A2,  ‘Tab2’!$A$2:$A$100), 1, FALSE)” which is aiming to find the string “Jim” within the range of data in the second tab with an exact match. Since this doesn’t exist within the range of data in its exact form then this will return “#N/A” as the result since it cannot be found. 

If you try the exact same method but use a partial match instead then you could get any number of results returned depending on the size of the range of data you are working with. I am not going to cover the details about why the partial match on VLOOKUPs using “True” as the match type is a waste of time in this blog post, just trust me – it is awful, I have never found a reason why this would exist (I’m sure they may be some reason, I just haven’t ever found a use for it!). 


VLOOKUP Partial Match

So if you want to look up a partial match which doesn’t use the “Approximate Match” type then you need to do a bit of clever excel magic. 

In this instance the * character represents a wildcard which means that when Excel is looking up the value it uses the lookup for a partial match yet still follows the strict criteria of the “Exact Match” type of the VLOOKUP formula. 

Let me explain that in a little more detail. 

So here is the original formula again;


=VLOOKUP(CONCATENATE(“*”, A2, “*”), ‘Tab2’!$A$2:$A$100, 1, FALSE)


What this formula is saying is as follows;

  • CONCATENATE(“*”, A2, “*”): Add an * at the start and end of the value which is being looked up, in this case cell A2
  • =VLOOKUP(…{above formula}…): Look up this value
  • ‘Tab2’!$A$2:$A$100: Lookup the original value within this range of data
  • 1: Bring back this column of data when the item is found
  • FALSE: Still use the “Exact Match” type as part of the VLOOKUP function


So it really is as simple as that to look up a partial match on a row of data 


Alternative Uses

Alternative uses when you want to perform a partial match look up could be if you wanted to count the number of times where a certain piece of text was occurring within a range of data as follows;


Tab 1

A2: Jim Bob

A3: Jimmy

A4: Bob

A5: Jimmy Mallet


Then if you performed the following formula on the above range of data;


=IF(COUNTIF(A2:A5,CONCATENATE(“*”, “Jim”, “*”))>0,”Yes”,”No”)


Then this would count the number of cells which contain the word “Jim” within the range of data, in this case 3. In the example above it simply outputs a “Yes” or a “No” if there is at least one occurrence of the word within the range of data, although if you would like the exact figure then you can simply strip that part of the formula out which would become;


=COUNTIF(A1:A4,CONCATENATE(“*”, “Jim”, “*”))


A few nice quick Excel tips to help with looking up values in Excel using partial matches. 

Taking the Google Analytics Individual Qualification Exam

I completed the Google Analytics Individual Qualification today which now means that I know how to do even more cool things with Google Analytics. But how did I find the exam and studying for the exam? 


Studying for the Exam

Google provides a nice handful of guides at their Conversion University, in total around 2.5 hours worth of video slide shows if you watch them all the way through (and I suggest you do!). If you are studying for the exam then it is definitely worth watching the presentations all the way through and listening to the audio that accompanies them since not everything is contained on the slides.

Try not to skip over any of the slides, even if you think you already know about a certain item. I had to stop my self from doing this and I am glad that I did watch them all since even with some of the basic areas it is good to get a refresher on areas that you may already know about but you will be surprised about the little nuggets of information which you will pick up during this process – small items that you would probably know if someone told you but not if you just got asked outright. 

One thing I can say about the studying though is that the Conversion University content is rather limited when it comes down to the details about Google Analytics. If you don’t use Google Analytics on a regular basis for a variety of different and complex tasks then if you attempt the exam from a standing start then you are going to have difficulty with the questions. 

I have been working with Google Analytics on a daily basis for a number of years now and covering a wide ranges of requests about the data you can extract and how to see certain information etc, along with installing and customising the code which is placed on websites and some of the questions got me thinking a bit about how things can be achieved. 

What it doesn’t contain within the learning documents is information around some of the more complex questions that you are sometimes asked about while working with Google Analytics and the only real way to learn about this is by actually doing the work on a regular basis. 

If you are looking to complete the exam from a standing start then I suggest reading every resource you can find about Google Analytics and some of the more detailed information, insights and technical setup options. Some good sources for this can be the book Advanced Web Metrics from Brian Clifton. Another good resource is Avinash Kaushik’s analytics blog


Taking the Exam

For the exam you have 90 minutes to complete the 70 questions. While I can’t give away the questions that were on the exam (no cheating!) I can say that they cover a very wide range of topics which are not always included within the Conversion University learning material. Bizarrely one area which I was quite surprised about with the Google Analytics exam were the large amount of questions related to Google AdWords

There is a completely separate Google AdWords exam which can be completed so it seems a little strange why there was a large amount of the questions were related to AdWords. That said, Google Analytics and Google AdWords are quite closely linked in terms of larger eCommerce websites who require revenue data to be tracked accurately. 

While it may seem like a long time 90 minutes to answer 70 multiple choice questions, this can actually be quite tight especially when you need to review certain answers that you are unsure about along with thinking through some of the more complex and oddly worded questions. 



So that is my experience of the Google Analytics Individual Qualification. It is definitely worth completing if you use Google Analytics on a regular basis as you will not only learn something new by completing it you will also have the proof that you can use it proficiently. 


Book Series Showing in Search Results

While browsing around the web today I noticed something that I have never seen before which is that Google is showing book series as direct answers within the search results, and (as you would expect with Google’s usual evil ways) this is above all of the other natural search results. 

Below is a screenshot from for the search term “Harry Potter Books”



As you can see some (but not all!) of the Harry Potter books have been listed directly there. Is this one of the first major pushes/tests from Google around using their knowledge graph changes in a different way? 

When you click on the “Show Details” button you get the following information which clearly shows that Google realise this method is by no means perfect;



Then whenever you click on either “Yes” or “No” you simply get presented with a message in its place which says “Thank you for your feedback.”

When searching for “Twilight Books” I get a slightly different piece of information displayed along with places where you can actually buy the books, which may suggest this is an implementation of the paid inclusion shopping feeds, who knows. 



I have certainly never seen this before, but I always just go straight to Amazon and search for books on there so I may be completely behind the times on this one as I was in an earlier post about how I thought it was new to see images within a Google instant search :-) 


Thanks for the Swagga Trip Advisor!

A while ago I spotted a glaring error on Trip Advisor’s Bangkok Hotels page as outlined in a post I did about how Trip Advisor was assuming I could speak Thai. All of my blog posts are promoted on Twitter to my followers and since the title of the blog post contained the phrase “Trip Advisor” they spotted this as they are monitoring social mentions for their brand online. 

All large brands should be monitoring social media mentions for brand terms as an absolute minimum – if you aren’t then shame on you, you are missing an enormous opportunity!

So they thanked me for spotted the error etc. and offered to send me some of their nice Trip Advisor swagga as a bit of a ‘thanks for spotting’ which was a nice gesture. 

Hat’s off to Trip Advisor as they have actually got the error fixed, which is more than can be said with other large companies who would ‘take the feedback on board’. 

Here is a nice picture of what they sent me through which includes a cap, couple of bottle openers, couple of pens (with moving airplanes on the top which circle the globe!) and a document wallet – all branded.



While I doubt I will be wearing the cap any time soon (I like to think I have a little more style than that – no offence Trip Advisor! :-) ) it is a nice gift from them and I can certainly use the beer bottle openers!

Thanks again Trip Advisor! :-) 


Google Testing Images in Instant Search

Spotted a nice little test that Google is running at the moment whereby images are being listed in instant search as seen below;



And when adding the next letter;



This is not something that I have seen before and couldn’t get any images to display when checking on another computer. It will be interesting if this does roll out on a wider scale as this puts more emphasis towards thinking about multiple types of content on a website and not just the basic text.

It also puts more emphasis towards image optimisation such as making sure the file name is descriptive, the ALT text is also descriptive and the surrounding text is talking about the actual image and even image sitemaps to ensure search engines can understand things better. 

I hope this does get rolled out as it certainly can be a bigger argument towards the naysayers about why it is so important to have a multi-content marketing strategy in place and not just providing the real basic text information. 


How I Built in a Weekend

Whilst working in the SEO industry there are time when certain tools would make your life easier and you just can’t quite find a tool that does the job that you need. This is one occasion where I was looking for a simple sitemap generator and all of the tools that I could find were either limiting the number of URLs which could be contained to a really small number or didn’t allow me to tell the tool what the URLs actually were. 

So that I why I built over a weekend. I’m sure there will be sitemap tools which can achieve a similar result out there that I simply have found but my patience was wearing thin searching :-) 

Was it difficult? Not really. It was just working through some basic logic to build in exactly what I needed. Below explains how I built the tool. 


What is it built with?

Simple Sitemap Generator is built on a Java platform running on an Apache Tomcat web server. Why? Because I know Java. The exact same task could be achieved using any programming language you choose if you require. My referred method of developing websites is using the Integrated Development Environment (IDE) called NetBeans.

Some hardcore programmers always prefer not to use these types of tools as they can get their self tied in knots sometimes which require a deeper understanding to untangle – so if you only use these tools you may find it difficult to figure out what is wrong. Personally, I prefer to make my life as simple as possible – why make things more difficult than they have to be to achieve the task in hand?



How does it work?

Quite simply really, the list of URLs are parsed using a Java program behind the scenes which separates all URLs by the new line character. The other items including the change frequency, last modified and the priority are also picked up from the main form then used in the program.

The program ultimately just runs through each of the URLs within the list (up to a maximum of 50,000 URLs due to this limitation within XML sitemaps) and wraps the correct tags around each item based on the latest XML sitemap specification. 

Below is a simple diagram about how the program uses the data which has been entered on the form so you can see how the logic works in the program. I have excluded any of the Java code so it is a little easier to understand for the non-technical people.

(click on image for a larger view)



Then the sitemap is complete! So it is just about displaying that nicely to the user. 


How did you design the logo?

I am not a big fan of designing anything and I am very poor at doing so. My preferred method of developing logos and nice graphics is using Microsoft Word combined with Paint.Net to achieve a few nicer effects if needed.

Why do I use these tools? Because using the more advanced tools are way beyond my skill set and I don’t have the time or desire to try and master these. The basics serve my purposes for the time being but not to say that I may not learn in the future – just not in the near future. 


Why didn’t you build in a website scraper? 

A lot of other sitemap generator tools have built in website scrapers and can identify all URLs on your website easily, although these are always limited by the number of URLs they can crawl. There are several reasons why I didn’t build in a web scraper to the tool;

The first reason being that by having a website that crawls the whole of a website leaves the tool open to abuse by people wanting to attack certain websites by making the tool send thousands of requests towards a certain website. This is more commonly known as a Denial of Service (DOS) attack. This amount of requests can bring websites to their knees or totally offline.

If I built in a scraper function into the tool then it would be very simple for someone to enter in “” into the scraper tool and press ‘go’ and continue doing the same in endless tabs in their browser. The result from which would be thousands of requests going to There is always ways to get around this type of abuse but this requires more time to build into the tool. 

The second reason why I didn’t build a web scraper into the tool is because there are already really good tools out there that can do this for you, namely Xenu Link Sleuth. Why re-invent the wheel? 

I primarily built this tool for myself as I will find it useful as I work on a lot of different websites, so it makes my life simpler. I can quickly identify all URLs on a website using Xenu so I didn’t need to go re-designing this as I can simply use a combination of tools to achieve the task which works out quicker. 

The third reason why I didn’t do this is because the actual server overheads to crawl an awful lot of URLs to scrape a website, then parse all of the information to use in the sitemap is an awful lot and since there will likely be very little income from the tool (advertising makes pennies!) then this would purely be a loss making exercise for me and that doesn’t sound like too much fun. 


Why can’t you have different priorities for different URLs?

Because I didn’t build this in as (in my opinion – I’m sure there will be people with other opinions on this!) there is very little value in changing this from 0.1, 0.4, or 1.0. The aim of the tool is to quickly build an XML sitemap from a list of URLs so you can tell search engines about content they may not already be aware of. If you want to quickly tell them about content then why would you set a lower priority for content? 

While it may be interesting to build into the tool a way to prioritise URLs based on their importance, there are no plans to do this in the near future. If you want to begin doing things like this then I suggest you build a custom XML sitemap generator which is more integrated into your content management system / database so that it can be continually upgraded. 



How did you choose the font and colour scheme?

As you know already that I created the logo in Microsoft Word, well you may notice the font from another post I did a while ago about the 200 signals in Google ranking algorithm (and yes, that image was also created in Word). Why the font? Because I like it. Simple as that. 

Why the colour scheme? For the same reason, I like that basic green colour in Word for colouring sections of text in (I usually use this for ticking off items on a to-do list or similar) so it seemed like a nice choice and I think it works quite well. 

How about the main navigation colour scheme? Well I actually just pulled this whole navigation from another website I have developed as I wanted to quickly create a navigation menu and there was little value in creating one from scratch. So this was more of a quick and dirty approach which achieves the aim of being a navigation menu. 


How did you get the XML sitemap to look pretty?

If you view the sitemap for the actual website, then you can see the sitemap is styled all nicely as is seen below;



Isn’t an XML sitemap supposed to look like a normal XML document though? Well usually yes, but it is possible to style up XML sitemaps so they look nice. This is using an XML Stylesheet which is achieved by adding a line of code to the top of the XML Sitemap as follows;


<?xml-stylesheet type=”text/xsl” href=””?>


This line of code is pulling in the stylesheet information from a separate stylesheet file which is making the XML document look a little nicer. I will be doing another post about how to create these as they are reasonably straight forward to implement and can make your XML sitemap a little more user friendly and they also have other SEO benefits such as being able to easily ping all of the URLs to ensure they are working etc. 



So there is a bit of information about how I built in a weekend. Quite simple really, it was just about allowing basic data to be entered onto a form then parsing the results and outputting to a nice format which is in line with the latest XML sitemap specifications.

I always encourage people to give something a go and try and solve a solution to a problem yourself as it really isn’t that difficult. The added bonus that it is fun doing so too!

This tool has certainly made my life easier and will continue to do so. I hope it can be of some use to you as well. If you do find it useful then please share :-) 


How to Count the Number of Occurrences of Text in a Column in Excel

Often when working with large data sets in Excel you want to quickly identify a list of all duplicate pieces of content within a row or column. With a nice little formula and a bit of filtering this is quickly achievable. This is achieved with a simple =COUNTIF() formula as is shown below;



So what does all that mean? All the formula is saying is “Count the number of times the adjacent cell occurs in the whole range”. So in the example of cell B3, the formula is saying “Count the number of times the word ‘Orange’ appears within the range A2 to A8”. 

Why are there dollar $ signs wrapped around the first part of the formula? This is simply telling Excel that this range is fixed. By default when you drag a formula down, all of the corresponding parts of the formula are also dragged down too, so if the dollar sign wasn’t included then then you drag down the list the results may be different as shown in another similar example below. As you can see, the results for what you would want to show the same answer are actually different – one cell says there are two occurrences of ‘Oranges’ while the other says that there is only one;



The next step that you have counted the number of occurrences of text in a range is to filter this data out by selecting the whole of Row 1 then clicking on Data > Filter as shown below;



This will then allow you to filter by showing all rows that have a number greater than 1. If you click on the small drop down arrow, then on Number Filters, then on Greater Than then you will be able to enter in data as shown below;



Once you click this you will be presented with an input box where you can type the number 1 into this box then press OK so that the applied filter will show just the data you require;



Now that this filter has been applied correctly you will see all the rows which have multiple occurrences within the whole column as shown below;



The next and final step is to simply copy and paste all of the filtered data from column A into a new tab on the spreadsheet. From there you can then select the whole of column A in the new tab and select Data > Remove Duplicates from the Excel menu as shown below;



This will now leave you with a final list of unique items which occur multiple times in a range of data. Quite simple really and it can have many practical uses;



This type of work can be really useful when you need to find out what specific pages on a website have duplicate content on them or other similar tasks such as duplicate title tags or meta descriptions etc. There are endless ways that this can be used so this should provide as a starting point to guide you on some of the more useful Excel functions and opportunities.