Alex Schultz: APIs

The new browser frontier is finally important

One of the things I love about running my little websites is they help me get my head out of the valley environment (or my ass depending on your point of view) since it is a hugely skewed demographic. The users of my sites are not valley types at all. In paper airplanes it's mums, dads, kids and teachers. On cocktail recipes it's a huge wide ranging audience... mainly non-US and over a huge age range but generally skewed towards a lower income demographic.

Trends that are big in the valley sometimes take a decade to bust out of the valley into the broader consciousness; for example RSS or Blogging. Some bust out of the valley seemingly instantly and find their main life outside; for example social networking or online classifieds. It feels like the mobile internet/internet everywhere is an example of the former and has been called out as the next big thing now for almost a decade.

There is a burgeoning microtrend happening across my websites in an area we have been talking about in the tech industry for seemingly eons. I am seeing all kinds of new operating systems appearing on the list of operating systems my users are using to access my site. This is a list that has been essentially 7 entries long for the last 10years. The big 4 in order are: iPhone, Danger Hiptop, Playstation Portable and iPod. These new operating systems are now just shy of 1% of visits to my sites.

That 1% number is pretty important to me... think 1% of internet advertising revenue being $300MM annually (ish) or 1% of goods sold on eBay being $600MM annually (ish). This is important to me since a $30MM market isn't really big enough to make the big guys look at it but this volume of potential is finally proving worth the effort. It's also true that a market at that scale can finally support a significant number of startups without them needing so much VC money it's not worth it.

Maybe I am overhyping the importance of average websites as canaries in the coalmine but I feel these sites are not a tech savvy demo, not designed with mobile in mind and if they are still getting c. 1% of visits from mobile this is a good indicator of a base line of mobile interest.

I have always loved the ideas of mobile internet transforming the world but I never saw how there was enough revenue there to really support a company. Now I see it and I am very excited about seeing what all the clever folks around here do to use the internet everywhere.

mysql copy table

Continuing my chain of blatantly obvious mysql queries that I nonetheless didn't know at one stage :) copying a table isn't an obvious function in mySQL. So here's how to do a mysql copy table

Say you have a table called "tbl_theoriginal" which has the perfect, ideal, wonderful table structure for a new table you want "tbl_thenewone" and you would like to create it the query you would create is as follows:

CREATE tbl_thenewone SELECT * FROM tbl_the original

Simple huh! You can put in any WHERE clause you like and even a limit 1,1 or whatever if you don't want to transfer any content but just the table structure.

mySQL rename table

One of the (few) downsides of  phpMyAdmin (at least the version I use) is that it doesn't support the mySQL rename table function.

It is very simple to use this function through the SQL tab in phpMyAdmin (or any other direct interface with mysql). Say you want to change the name of the table from "tbl_oldtable" to "tbl_newtable" then this would be the code:

RENAME TABLE tbl_oldtable TO tbl_newtable

Simple huh! Right with that duty to my readers and hopefully my natural search traffic... (these mySQL posts tend to do pretty well) I am off to drink more cabernet sauvignon :)

New Site Launch Huge Success

I have been working on a new design for my cocktail recipes site for about 2 months now. The company I selected to do the design were 2 pixel solutions (based in India) who I found through elance. You can see the results of the redesign below.

I have been really pleased with working with the 2 pixel team. They were really good in making the changes I asked for and simplified the design extremely well. I would highly recommend anyone using elance to ask for an outline mock up of the design a company is thinking of creating ahead of time for your site. I certainly benefitted from that. 

Having launched the site yesterday I took a look at the very early results and am over the moon. All the visitor level metrics are looking great. Visitors are spending nearly 25% longer on the site and viewing 30-40% more pages on each visit. I also found a number of features that were broken as I recoded the site (and honestly broke some more myself) so I have some fixing to do but bottom line the new site ROCKS :) and my users (most importantly) seem to love it.

For the rest of today I am working on getting my facebook application up and running again.

A little bit of an update on the Stumble Testing

Using Stumbleupon buttons on my paper airplanes site was an interesting idea that I tried a few weeks back. It seems to be working pretty well. Below you see the graph of traffic I am getting from Stumble over time from the last big stumble spike (following which I added the stumble button).

For November (i.e. not including the spike in october) Stumbleupon now makes up 10% of my site growth and approx. 1% of all site traffic. I have a fair few ideas for how to boost this source of traffic even more and am really keen to give it a go soon!!!

Affiliates more Profitable than Malware

Sunbelt Blog is a really fascinating blog for anyone who works in the internet industry. As an affiliate I find it a really fascinating blog since the kind of people who produce malware and so on also are often affiliates. In the early days of running my own site I promoted a company who produced a software that when installed on someone's computer replaced ads on any site with their own ads. If someone came to my site, used my bandwidth, read my content they wouldn't see my adverts and wouldn't earn me a penny if they had installed any of this company's software. The company was called Gator and is now called Claria and have been through a number of legal cases on this which means what they do is legal although distasteful to me.

Anyway something that is very common in the malware industry is to get someone to install a piece of software that does something to your PC in order to get hold of a pornographic video or some other slightly illicit goody. They may hide the fact this download is making you part of a botnet but in the latest example shown on the sunbelt blog they get you to download google pack. The 2 incredible parts of this is that the Google affiliate program is paying more than malware pays and that what appears to be a relatively experienced naughty group would risk attacking an affiliate program. Usually these programs are on top of their affiliates enough that they would catch this, stop it and would simply not pay the affiliate. I wonder why Google appeared an easy target. Was the payout big enough to be interesting, the conversion to Google pack so great that it was worth doing or does Google not have a great affiliate fraud monitoring system in place yet?

Stumbleupon Traffic Source

As many people have noticed stumbleupon has become a pretty good source of organic traffic. For me stumbleupon is 10-20% of the growth of my paper airplanes site at the moment and I know I am by no means the most popular paper airplanes site on stumbleupon right now (btw that wireframe rocks... a future project for me too I think).

I may not be the top paper airplanes site in stumbleupon and it certainly isn't my biggest source of traffic but it is now a significant enough contributor with lots of potential that I want to spend some time on it. The main issue as many people have seen is that traffic from stumbleupon is very bumpy indeed:

What is interesting though is though each bump is adding up to hundreds of visitors (sometimes near 1000) they are interspersed with long periods of zero (or v. low) traffic. The last spike you can see was followed by some level of sustained traffic, not huge but a significant part of my daily unique visitor growth. The main reason for this growth seems to have been adding a stumbleupon button to every page of my site.

I guess I will see how this sustains but for now things are looking pretty good for a new sustained source of traffic to paperairplanes.co.uk. Why don't you give it a go too?

Squeezing out Efficiencies

The growth of my cocktail recipe website has been truly scary this year. I am into my second year of >100% year on year growth in the site, I have doubled in volume since January and if anything growth is accelerating.

 The plan of course is to keep growing and doing well. I am currently at 2/3 of the bandwidth limit my web hosting company allow me which although cool is very scary when you look at last year's traffic distribution numbers:

The above December to October ratio is typical, I have seen it for the past 7yrs every year with December having roughly 3x the traffic of October (you can see the huge growth in last year's graph from Jan to Dec as well). In the peak day of december (new year's eve) I expect to be serving about 4cocktails a second (30% above my current recorded intraday peak) with an intraday peak of 7 cocktails a second. My code/databases can stand this but I am not a professional coder and want to be sure so I am working on making my code more efficient at the moment, adding more caching, planning (finally) to migrate to a very professional hosting package and place all images/css on a content delivery network like Amazon S3 (because whatever happens my current bandwidth is screwed).

The main point of this note is to encourage you guys to check out your code. I just recoded the displaycocktail.php script on my site (which accounts for 20% of bandwidth and >50% of files served). It turns out this script (which has grown organically and been coded in chunks since 2001) is horribly inefficient. I reduced the lines of code in the script by 55% to just 304 including all HTML and the weight of the page produced by 21% which will reduce my bandwidth usage by 4-5% which is cool and also has halved the SQL queries per page load on that page.

Efficiency is dull but I am hugely glad I did this and am looking forward to running through the rest of the site spring cleaning in Fall. If nothing else it will allow me to have a much smaller, easier codebase to maintain which is definitely good news!!!

Code Performance is Important

The puzzles that a fair few geeky companies post on their jobs pages to try and vet/encourage great coders to complete them and apply for jobs are quite cool (I definitely don't fit in the great coder bucket). Recently I noticed a fair few of them are focussed on code performance and so thought I would share my experience of code performance harming my site.

The graph above is the exit rate for users visiting my display cocktail pages on my cocktail site (according to Google analytics which has pretty graphics but some data issues) against time. The initial decrease in exit rate is awesome and primarily due to implementing the cocktail recommendation engine. Suddenly in mid July there is a massive jump in exits, this coincided with a process I was running for my facebook app really screwing up and slowing down every query run on my database :(. I shut that query down (although didn't realize what was happening for a month) and (for the moment) have given up on my facebook app to concentrate on the main site. After shutting down the process performance and exit rates improved instantly.

I don't monitor the performance of the scripts on my site anywhere near enough. Last weekend I built tracking to monitor the ranking of cocktails on my site weekly, pull out fast moving cocktails and so on. I have started logging keyword searches on my site too as of last week. This weekend a key focus for me is producing a performance dashboard summarized by hour of day and day of year. I am going to start monitoring the efficiency of all changes I make and see where I need to make improvements.

Cocktailmaking.co.uk has grown 3x since last year. The site also gets 25% of it's visits in December with 10% of those on New Years' Eve. I need to make the site much much more efficient before that date comes or my servers will be a smoking mess this year and I'll capture none of the revenue that I should.

Code efficiency is really really important.

Google Analytics Regular Expressions

I was using Google analytics regular expressions on Saturday to try and understand how my cocktail making relationship engine had worked out. As I was using the regular expressions I noticed that there were very few resources to help you get them right for Google Analytics so in case you are interested here are my tips.

First the regular expression variables supported by google analytics:

.  match any single character

*  match zero or more of the previous items

+  match one or more of the previous items

?  match zero or one of the previous items

()  remember contents of parenthesis as item

[]  match one item in this list

-  create a range in a list

|  or  ^ match to the beginning of the field

$ match to the end of the field

\  escape any of the above

Some real examples:

If you are looking for the page index2.php then your regular expression should be "index2\.php" you want to escape the "." with the / since that will make the regular expression run faster as Google will now only look for the "." character and not "any character" which is the special meaning of ".".

I have a regular expression "displaycocktail.php" within all my cocktail recipe pages. For the test group I was passing ?test=test on the end of that URL to google analytics and for the control group ?test=control. A couple of examples of urls showing up would be:

http://www.cocktailmaking.co.uk/displaycocktail.php/241-Slippery-Nipple?test=test

http://www.cocktailmaking.co.uk/displaycocktail.php/241-Slippery-Nipple?test=control

http://www.cocktailmaking.co.uk/displaycocktail.php/435-Blue-Lagoon?test=test

http://www.cocktailmaking.co.uk/displaycocktail.php/435-Blue-Lagoon?test=control

If I wanted to see just the control group I would use the reg exp "displaycocktail\.php/.*test=control" where the ".*" means match any number of characters at this point in the regexp.

Hopefully this (esp. the working examples) are useful for you to get started.