OurIndustryIsBroken.com: Tracking site users and traffic with PHP, MySQL and ActionScript

You may remember that FletcherMartin launched OurIndustryIsBroken.com back in April. After receiving some feedback about the site, we tweaked it to enhance the user experience and allow us to track how visitors use the site.

We’re using Google Analytics for high-level analysis such as referrals and search engine traffic. But we wanted to do more robust analysis. You see our conundrum, right? How do you track “pages” and visited “links” within a single Flash movie embedded in a single HTML page?

Simple: you build your own tracking system.

For this project, I used Flash’s LoadVars() object to talk to a PHP middle layer that writes and retrieves data to and from a MySQL database.

LoadVars() allows you to send (and receive) variables from a Flash movie to (or from) a server. You need a server-side script to process that information. Variables and values are sent to the server using either GET or POST. It’s just like submitting and processing a form — and should be treated like any other user-submitted data.

When you pass data from the server back to the movie, you send it as a query string, or an encoded URL (example: ‘car1=honda%20accord&car2=chevrolet%20tahoe’). Once the variable-value pairs are loaded into the movie, you can use ActionScript to determine how the movie handles that data.

The PHP scripts and the database

There are two PHP scripts at work here. One collects the data from the movie, escapes it, and writes it to the database. The other retrieves data from the database, encodes, and returns it to the movie. We’re also using three separate database tables.

Table one contains the questions and answers contained in the movie. Each question and answer corresponds to a key. The next table tracks section visits in the aggregate. One column contains the keys from the questions and answers table. The other column keeps track of the count. I set an initial value of zero for each question-and-answer key in that column. Every time a section is visited, we update the count for that section by one. If we were using MySQL version 4.1 or later, we could have used the ON DUPLICATE KEY clause for INSERT. Instead, we’re using UPDATE syntax and adding one to the value of the count column.

The third table is where we store per-user data. We’re using server-based sessions to track visits by user. With each click, we capture the section being visited, the current session identifier, the user’s IP address and the time of the click. Collecting this user data allows us to do a few things:

• Determine where our visitors are coming from.
• Determine roughly how long people are staying on the site.
• Determine which section(s) the user has visited (and their answers).

That last portion is probably the coolest. At the end of each section of the movie, we return the user to a main menu. When that screen is loaded, a script queries the database to find which sections have been visited in that session.

Once MySQL returns those section keys, we build our query string. In my PHP code, I set each section variable equal to 1 (i.e. ’section1=1&section22=1′). We pull this query string into our Flash movie by once again using LoadVars().

After these variables are loaded, we can use ActionScript to make the movie take a particular action. In this case, I tested to see whether the variable exists (example: if(section1){scroll_mc.gotoAndPlay(23);}). If it does, the movie goes to the scroll movie clip and plays from frame 23. If it doesn’t, the scroll movie clip plays from frame one.

All of this means we have some solid data to analyze both in terms of traffic and — because we’re tracking answers — how well our messaging is being received.

You can see the new tracking system in effect by visiting the site.