Alex Schultz: APIs

% Returning Visitors

This is such a cool metric and one which I truly love. How many of the visitors to my site are returning and how many are new visitors. Ideally I love to see the absolute number of returning visitors growing faster than the new visitors to the site simply because that shows the site is getting more sticky and people are enjoying it so much they are coming back of their own volition. As a site owner I feel this is a metric you should keep a close eye on.

The left image shows 2006 April returning visitor rates for my cocktail making site and the right image shows 2007 April returning visitor rates for the same site. In that time I have tripled the site traffic but grown the returning visitor rates 50% faster. This is awesome news and I am really excited to see that the site is getting better and more sticky.

The whole question is how to make that EVEN better and draw more people back to my site. Returning visitors are hugely valuable since they are free traffic and engaged. It's worth investing time and money in increasing this metric even though it's a really hard one to move.

AdSense Dependant and Independant Variables

Oh man this is a head screwer!

For a while now I have been tracking two things which have a strong impact on my adsense earnings for any given adsense impression: How many pages a visitor has seen on my site and what site referred them to my site.

Now I've been treating these variables as independant which means I have tracked them seperately assuming no interplay between the way these two variables impact my adsense clicks. This is moronic the variables couldn't be more strongly tied together if the hypothesis I have for "why" google referred visitors have higher CTRs than non Google:

A google visitor sees adsense ads relevant to the search term they typed in google hence is more likely to click

If the first reason holds true then browsing through my site it is clear that the ads on the "paper blimp" page are v. different from the ads on the paper airplanes homepage. It would then also hold true that:

The further a Google visitor is from their page of entry the less correlated the adsense ads are to their keyword of entry

If this holds true you would expect the drop off in the ctr for a google user to be far greater from the first page viewed to 4th page viewed than for a non google user. The good news is I can test this and see if these variables are independant and whether I should treat them as such but the bad news is I can't kick this off until after eBay Live due to my commitments over the next month.

Fire the AT&T Internet Marketing team

Today I was on my site when I saw the following ad:

It says get "AT&T Yahoo! High speed internet pro" and the animation leading up to this was about digital music streaming and noted that AT&T Yahoo! was the fastest broadband you could get at that price.

The ad took 20secs to load which doesn't really convey that message and on checking the file it was a poorly optimized banner which was hugely bloated and it could have done the same job faster in gif (but it was in flash... which is usually better for this stuff). On watching the loading I noted the URL it was coming from was an AT&T url (not Burstmedia *hug*) and this was where the delay was.

I decided to dig into burstmedia (who served this particular campaign to my site) and take a look at how I was being paid on the ad. I found the stats pretty quickly (Burst have a great interface) and I have shown this advert 71,017 times for which AT&T have paid me $117.89 (thank you very much lol) and it has been clicked on 51 times a 0.07% CTR when banner ads in this placement on my site average 1.52% CTR... (that means my average earning per click on this campaign is >$2, LOVE IT). I wonder if there is someone in the marketing team at AT&T wondering why the CTR is so low or the performance of internet marketing so bad??? I think the total kicker on this one for me is that the campaign has been running for 51days!!!

I hope whoever is running this campaign learns something and if they don't that they never, ever work for me.

Just. One. Word.

Spotted in mr rayne's notes... loved this!

You.
Can.
Only.
Type.
One.
Word.

Not as easy as you might think.
1. where is your cell phone?
desk
2. Your boyfriend/girlfriend?
single

3. Your hair?
slick

4. Your mother?
hugs

5. Your father?
awesome

6. Your favourite thing?
mustang

7. Your dream last night?
none

8. Your favourite drink?
bushmills

9. Your dream car?
mustang

10. The room you're in?
home

12. Your fears?
death

13. What do you want to be in 10 years?
happy

14. Who did you hang out with last night?
no-one

15. What you're not good at?
love

16. Muffins?
blueberry

17. One of your wish list items?
party

18. Where you grew up?
london

19. The last thing you did?
interview

20. What are you wearing?
pyjamas

21. what aren't you wearing?
hat

22. Your pet?
computer

23. Your computer?
pet

24. Your life?
driven

25. Your mood?
chipper

26. Missing?
home

27. What are you thinking about right now?
home

28. Your car?
mustang

29. Your work?
internet

30. Your summer?
travel

31. Your relationship status?
single

32. Your favourite colour?
red

33. When is the last time you laughed?
morning

34. Last time you cried?
march

35. School?
eltham

Cocktail Recommentation Engine: Part 3 - The First Results

The following table is a subset of the output results from the cocktail recommendation engine (be warned some rude words in some of the cocktail names). I have tried to drill the list down to just the best matches so bear in mind there are probably 30% more matches thrown out which appear to have statistical significance but I can find no reason for the relationship (beyond sometimes they are next to each other in the large cocktail A-Z list).

Cocktail One - the cocktail someone is on, Cocktail Two - the cocktail I recommend, Probability - the probability that if someone is on cocktail one and visits another cocktail cocktail two will be that cocktail. There had to be at least 10 relationships in total recorded between cocktail one and another cocktail in my db for a cocktail to make this table.

I am hoping that as I get more data this table will grow more accurate and I will have fewer "wtf?" moments looking at the list. I also want to add two more probabilities to the calculation:

1. Probability if they visit 1 they will like 2
2. Probability if I display 2 on 1 they will click on it

Anyway for now enjoy looking at the cocktail relationships and expect more data soon.

Cocktail One Cocktail two Prob. The Refresher Tom Collins 0.76 throat cramp Toxic Waste 0.67 Raunchy island sex Red Headed Slut 0.5 three wise men Three Wisemen 0.5 Tomate Momisette 0.43 Wicked one Screwdriver 0.35 Amaretto Rose Amaretto Dream 0.33 Purple Haze Radiation 0.33 Strawberry shortcake StrawBerry Shortcake 0.33 Toxic Waste Titanic Sinkers 0.32 martini spiller Niks Shot of Aussie 0.32 The Orville three wise men 0.31 3 Headed Parrot A-Bomb 0.3 BABY GUINNESS Baby Guinness 0.3 Flaming Shooter Flatliner 0.29 tequila slammer!!!!!!!!!!! TEQUILA SUICIDE ! ! ! ! ! ! ! ! ! ! ! 0.28 see you at the hospital Tequila Sunrise 0.28 Absolut Nut Absolut Quaalude 0.27 Sex on the beach Sex On The Beach 0.27 suicide surfer on acid 0.26 Tom Collins Orange Fizz 0.24 Kick in the Willy martini spiller 0.24 Slippery Dick Slippery Nipple 0.24 Multiple Orgasm Multiple Screaming Orgasm 0.24 Absolut Stress absolutly mistafying 0.22 death cock sucking cowboy 0.22 A strawberry with milk A-Bomb 0.22 Cheeky Vimto Layered cheeky Vimto 0.21 Tequila Sunrise throat cramp 0.21 Sex On The Beach Sex on the beach 0.19 Screwdriver Bay Breeze 0.19 Arsonist awamawama ding dong 0.19 Absinthe Drinker Absinthe Minded 0.19 nipple twister Nymphomaniac 0.19 Jolly Green Giant Flaming Lamborghini 0.17 Flatliner Flaming Lamborghini 0.17 Zipper Zombie 0.17 Absolut Nut Level 28 0.17 Absinthe Drinker Royal Absinthe Fizz 0.17 Long island ice tea Long Island Iced tea 0.16 Slippery Dick cock sucking cowboy 0.14 blowjob Blow Job 0.14 Blow Job Clit Licking Cowgirl 0.14 Wet Pussy Wet Dream 0.13 Pina Colada Pina Colada (Virgin) 0.13 Screaming Orgasm Sex on the beach 0.13 Orgasm Cocktail Screaming Orgasm 0.13 B52 Blow Job 0.13 Slippery Nipple cock sucking cowboy 0.13 Sex on the beach (shooter) Sex on the beach 0.13 Black Russian Black Russian - proper 0.13 Brain Damage brain haemorage 0.12 Alabama Slamma Alabama Slammer 0.12 cock sucking cowboy Clit Licking Cowgirl 0.12 Mai Tai Mojito 0.12 Sex on the beach Slippery Nipple 0.12 Pornstar Purple rain 0.12 Pornstar Quick Fuck 0.12 Baby Guinness Bad Babysitter 0.11 Arsonist red , white & Blue 0.11 Baby Guinness A-Bomb 0.11 Baby Guinness Billy Badass 0.11 Screwdriver Wicked one 0.11 Angel's Kiss B52 0.1 Blow Job blowjob 0.1 Clit Licking Cowgirl A-Bomb 0.1 Clit Licking Cowgirl cock sucking cowboy 0.1 Flaming Lamborghini Deep Throat 0.09 A-Bomb Abbey cocktail 0.09 Sex On The Beach Sex on the beach (shooter) 0.09 Blue Lagoon blue lagoon special 0.08 Slippery Nipple Sex on the beach 0.08 Clit Licking Cowgirl Orgasm Cocktail 0.08 Clit Licking Cowgirl Deep Throat 0.08 Screaming Orgasm Sex On The Beach 0.08 B52 Baby Guinness 0.08 Screaming Orgasm Blow Job 0.07 Blow Job Blue Lagoon 0.07

Huge steaming piles of data (Project Cocktail Part 2)

Today has been quite challenging in project cocktail. The main issue is working out how much information to store and what granularity to summarize it on. Data is pouring in rapidly, I have defined over 5000 directional cocktail relationships and probably 10% of my cocktails within one day have at least one relationship I would define as relevant (I shall work on statistical significance fairly heavily later on).

The issue is that every row in my database is currently taking up 46bytes and I am adding c. 7.5k rows a day. The index on the database is then adding another 25% to this and there is little I can do here since I am required to create a primary key. Therefore daily I am creating 0.4MB of data in unsummarized form. The machine I am using has 100MB of storage and so I could store up to 250 days of raw (unsummarized) data or 2.17million rows (handy cos right now my cocktail DB could theoretically create 2.17million different directional relationships with different likelihood factors).

In the short term I am going to do nothing more than summarize on a month basis and depreciate the value of prior months over time but in the long term I want to know if recommendations should differ significantly by various user related variables and hence I want to store those variables so that in the future I can have a significant dataset to query and work out the impact of those variables. My target is to make this engine slightly scary at predicting what cocktail you might want to see next and to hit the scary threshold will take a little more than the #1 most likely relationship to deliver. So all things considered I have 0.5TB of data storage going spare right now in 5 mySQL databases... let's go for the big data :) and see what comes out!!!

Tomorrow I will be helping move 100 rowing boats so that my new boatclub can undergo an awesome renovation over the next 3wks which means I will have to take a break (and let the data gather into an even higher volume). Hopefully Sunday I will be able to do some analysis and maybe even build a mock up that shows what the output could start to look like. For now goodnight!

Recommendation Engine: Project Cocktail Part 1

Ok so how does one build a recommendation engine?

For the last year I have been tweaking improving and revising my cocktail recipes website with the prime goal of driving more traffic to the site. I have certainly acheived some of that with >1500 visitors daily on most days at the moment (still not huge but really massive growth). What is noticeable though compared to my paper airplanes site is a distinct lack of loyalty in my user base. With paperairplanes.co.uk ~32% of the visits daily are from returning users but with cocktailmaking.co.uk only ~9% are.

I need to make the site better and so my first attempt is to produce a cocktail recommendation engine. Phase one of the project just released to the site and I wanted to chat about that a little bit here.

I am logging every chain of two cocktails viewed by a visitor to my site within 2hrs. My thinking is that time spent and number of clicks browsing both degrade the correlation between cocktails in a user's browse path and so two cocktails viewed back to back in a short time period are likely to be very closely related. If I observe this interaction over millions of such pairs (data I can gather in just a few months) the most correlated cocktail combos should stand out from the mix at least for the top 10% of cocktails viewed on my site. That is my thinking and I will start to share the results on here as the data gathering proceeds.

The wonders of having a website with enough traffic to make analysis interesting :)

SELECT ... CASE expressions rock!!!

CASE expressions are a huge timesaving SQL feature that I have just recently fallen in love with. The specific example was trying to find how the activity of a class of users has declined over a period of twelve months. Without CASE this would have required writing and executing 12 different queries and then joining them into a table either by joining all those queries together or by pasting them into (in this case) excel. That's hard enough but what about 24hrs in a day for day parting analysis across 100000 keywords (far too big for excel <2007 that one ;) )

CASE selects each of those different queries I described into a column using a clause something like:

CASE month WHEN 2006M10 THEN revenue ELSE 0 END

For a table something like:

month	userid	revenue
2006M10	alex	$500
2006M10	beth	$402
2006M11	alex	$220
2006M11	beth	$170
2006M12	alex	$52
2006M12	beth	$50
2007M01	beth	$1000

 The select may look like:

SELECT

      sum( CASE month WHEN 2006M10 THEN revenue ELSE 0 END) as oct06,

      sum( CASE month WHEN 2006M11 THEN revenue ELSE 0 END) as nov06,

      sum( CASE month WHEN 2006M12 THEN revenue ELSE 0 END) as dec06,

FROM

      tbl_userrevenue

WHERE

      userid IN ('alex', 'beth')

 

and the output would look like:

oct06	nov06	dec06
$902	$390	$102

 

Which in my mind is a pretty cool tool for analyzing anything where you want to look at discrete time intervals or users classes or almost anything else individually.

Make Your Users Feel Awesome by Looking Good

The last month for me has been heaven and hell. I have seen my mum and dad, seen a couple of friends from home, visited a geek fest in Austin, raced in San Diego, seen all my eBay friends and even been whale watching in Big Sur having driven the highway 1 down from Santa Cruz. On the other hand my workload has been high (by my standards), I have been on at least 7 flights in 3wks - 4 of them long haul and am entering a huge, high stress period of uncertainty at work. All of this has led me to

a. start bleeding from the nose and getting headaches ;)

b. think about what experiences make me feel awesome

For me it's really clear that the experiences that make me feel awesome are those that either make me look good or do something that really pleases me and makes me feel good. For this post I am gonna focus on the looking good!

Google maps is the example I want to use for looking good. Just a few weeks ago I installed it on my blackberry, I use it rarely due to the high bandwidth costs but on two occassions it has been awesome! The most recent occassion was while at a regatta and searching for a particular mexican restaurant in San Diego called the californian coyote. We knew the name but had no idea where it was and neither did anyone close by. Under a minute later using a combination of searching Google and using Google maps I had the address and a route there. When at the restaurant everyone celebrated the fact I got them there and I felt awesome.

Google maps made me feel awesome by looking good and I am gonna keep using them because of that! Now how can I take that learning to my websites and quickly!!!