Jimmy Z

The ability to mash two or more concepts into a new product has sparked some really creative ideas that have received a lot of attention in the web 2.0 boom. Recently, toys have seen some thrilling success, as movie brands have been mashed up with traditional toys of the past.

My favorite mashup has been Star Wars mashing with Legos. I’ve always been a huge Star Wars fan, so naturally I get really excited when traditional toys get mashed up with Star Wars themes. I’m kind of jealous of kids now days, although I have a 1366 piece Imperial Star Destroyer sitting in my spare bedroom, waiting to be assembled.

The mashup concept was taken to a new level as Lego Star Wars: the video game was released. I don’t play many video games, but when I visit our in-laws, I usually have a good time playing some games with my brothers-in-law. Lego Star Wars I and II bring together 3 great concepts into an extremely creative, fun game.

My sister sent me a link this morning to a new mashup, Spider Spud. Wow! This one is awesome! I already have Darth Tater and Spud Trooper, but I’ll probably have to get this one too.

I may have just revealed too much of my underlying nerdiness, if I hadn’t done so already with this blog. In case you’re wondering, yes, all of my action figures are cherry.

Jay Pipes, a MySQL employee, gives a lecture at Google on MySQL Optimization. I’ve been in several discussions on how to optimize large queries, especially during my time working on TagJungle*.

This video has opened my eyes to some query practices that I used to do that are sub-optimal, and how to correct those problems.

One of my favorite optimizations that Jay Pipes demonstrates is that you can actually run a query like: SELECT * FROM users WHERE email LIKE '%@gmail.com'; and have MySQL use an index to help perform that query. It’s really quite clever, and I used to think that you were just out of luck with a slow query if you ever wanted this type of information. I would post the solution to this problem here, but the video is beneficial enough to just recommend watching the entire video.

Another big eye opener for me was the concept of Correlated Subqueries (which aren’t a good thing to do). Here’s an example of a correlated subquery:

SELECT name,
(SELECT COUNT(*) FROM Reservation WHERE confirmed<>'N' AND campId=C.campID) AS Confirmed,
(SELECT COUNT(*) FROM Reservation WHERE confirmed='N' AND campId=C.campID) AS Unconfirmed
FROM Camp C

This is bad because each of the subqueries must be executed for each camp record. This makes it so the query doesn’t scale well as the number of camps grow.

To fix this problem, it is suggested to think of these types of queries in sets rather than as procedural operations. The above query could be executed like so:

SELECT C.name, Confirmed, Unconfirmed
FROM Camp C LEFT JOIN
(SELECT campId, COUNT(*) as Confirmed
FROM Reservation WHERE confirmed<>'N'
GROUP BY campId) AS ConfTable
ON C.campID=ConfTable.campId
LEFT JOIN
(SELECT campId, COUNT(*) as Unconfirmed
FROM Reservation
WHERE confirmed='N'
GROUP BY campId) AS UnconfTable
ON C.campID=UnconfTable.campId;

This is provides much faster execution as only 3 lookups need to be made to the db and then just joined together. This will scale up as more records are added to the database.

Here’s the video:

* Disclaimer: I no longer am an employee of Tag Jungle or 42Co.

I was recently introduced to CrazyEgg analytics, a new contender in the lower-end analytics realm. CrazyEgg is all about visualizing your web visitors. It provides a pretty neat click map and heat map overlay.

Click maps are very useful in showing how your visitors use your site. Google Analytics has offered a click map overlay for a long time. Enterprise-grade Omniture has a far superior click-map to that of Google. So what’s so special about CrazyEgg?

CrazyEgg not only tracks the links that users are clicking on, but it logs the x,y coordinate of the click, which is what they use to generate their heatmaps. These heat maps are different from the heatmaps that you may have seen in MarketingSherpa case studies, which actually track eye movement. CrazyEgg heatmaps are only showing clicks.

While these heatmaps don’t give a 100% accurate representation of what visitors look at, I suppose it could give some insights as to positioning on pages, and make better use of expensive page real-estate that lies above the fold.

I’d be interested in finding out how CrazyEgg defines its metrics such as visits, pageviews, etc., and what types of date ranges you can run with your reports. What happens when you change your page? What if certain elements on the page are dynamic and the page isn’t the same every time? Will it appear that the more stationary links such as top-level site navigation are the most popular items? I’m sure the CrazyEgg people have answers for all of these questions, I’m just curious.

I haven’t yet set up CrazyEgg analytics on this site, because their free version is way too restrictive. It only allows 4 pages to be tracked. If I had an eCommerce store, I would probably pay for an account.

My sister alerted me of a new Internet game that is gaining popularity among kids called “Club Penguin.” In a sense, it’s a virtual world much like Second Life, except instead of being whatever you want, you’re constrained to being a penguin.

Within Club Penguin, you can buy items, such as pets, clothing, food, etc, and get jobs to work for money. You can send chat messages to the people who are in your virtual world. You can throw snowballs, dance, etc. You can add a particular penguin to your buddy list and send that penguin emails.

It has won lots of “awards” for being Kids Safe, but I still don’t trust it. Even though there is live moderation and message filtering, it’s the same game where people try to break the controls that are constraining them. Someone is going to find a way to infiltrate the system.

I decided to take a look at the security behind the application itself. I found that Club Penguin was sending messages over port 6112 in plain-text. This was surprising to me as I though there would at least be some encryption involved.

I did some simple packet sniffing on port 6112 to see what I could discover about the messaging protocol being used. At first, it looked a bit cryptic, but later discovered that there were some simple codes being used such as np, rp, sp, and sm which represented “new player,” “remove player,” “move player,” and “send message.” There is a user id tied to each of these actions, and it gives coordinates on the current map.

Anything that is said can be easily read in plain text. For example, I sent out the following message, “anyone hear me?” and the transmission that I was able to sniff was: %xt%m%sm%18%23348762%anyone hear me?%.

What does this mean? Any predator in your neighborhood can just tap in and listen to port 6112 and see who is playing Club Penguin in the neighborhood. He/she can watch everything that goes on, spoof the Club Penguin server, and send un-moderated, un-filtered messages to your child.

Scary!

My advice is to watch what your children are doing online and become informed of potential dangers. There are people who would like to exploit this game. Several visitors have arrived at this post searching for “how to hack club penguins database,”  “club penguin packet sniffers,” “club penguin 6112,” and “clubpenguin mail spoofing.” I’ll let you be the judge.

[Update: I've closed comments because of vandalism by people not mature enough to leave respectful comments]

In my Information Architecture class, we’re doing MySQL query optimization, and are required to import data from an Excel spreadsheet into the appropriate MySQL database schema. Many people are trying to use NaviCat to do the import of the data, but are having some problems.

I propose a better way to go about doing this is exporting the data as delimited text files, and importing the data with MySQL’s LOAD DATA INFILE.  I first came upon this mysql functionality as I was working on building an International geocoder. I was importing millions of records in tab-delimited format from the National Geospacial Intelligence Agency.

I first tried importing all of this data with a php script, doing a few million insert statements. This was very slow, as I could only get 100 or so rows to insert per second. I remember it would have taken something like 16 hours to get all of the records inserted into the database.  It just wasn’t feasible.

MySQL’s LOAD DATA INFILE command handles this stuff with ease. You just point it to your csv file, define the data a little bit, and let it go. I was able to import all of the data from the geospacial tab-delimited file in minutes rather than hours.

Yesterday, Ben Robison, Ben Swanson, and I competed in the final round of the Omniture Web Analytics Competition held by BYU’s eBusiness Center. We were given access to analytics data inside of SiteCatalyst for CostumeCraze.com. We then were required to dive into the data, define Key Performance Indicators, find an area of the site that needs improving, and make recommendations based on analytic data findings. The grand prize was $5,000 to the winning team.

We worked really, really hard on this. In just the last few days before the finals, we spent at least 13 hours together as a team, and 10 or more hours individually putting together reports, screen shots, and trying to make our recommendations bullet-proof.

The competition went well. We had practiced hard, and we had anticipated and prepared for many of the questions that we were asked. My team was awesome. Both Bens contributed a ton of insight into the whole process.

We only got to see one other presentation during the finals, since we were the 3rd of 4 to present. The presentation that we saw was really good. I wish I could have seen the other two, because I heard those were really good also and focused on areas that we hadn’t even considered in our research/presentation.

Anyways, we live in the Information Era, and the web and web analytics are becoming increasingly important. I’m glad we had this experience, and I now feel confident enough to do SiteCatalyst implementations and analysis for anyone who needs it.