Making Shortstat More Unique

I finally installed Shortstat last week. I really like what Shaun has done but I had to tinker. The stat that I care about is uniques. Hits can go up and down from something as simple as your mom checking out your site. Uniques, on the other hand, tell the truth. Without any further ado, I’ll explain how I, like thousands of others, have modified Shortstat to fullfill my statual desires.

First, I added a column to display ‘Uniques in the Last Week’ beside the column that displays ‘Hits in the Last Week.’ This was the easiest change that I made. The picture above shows the added unique column. In order to get this column, I just had to add the following to the sql statement:

$query = "SELECT COUNT(*) AS 'total', COUNT(DISTINCT remote_ip) as 'uniques' FROM $SI_tables[stats] WHERE dt > $dt_start AND dt <=$dt_stop";

I then modified the table structure of ‘Hits in the Last Week’ a bit by adding a ‘Uniques’ heading and a column right after ‘Hits’ to display the new stat. Just open up functions.php in your favorite editor and replace SI_getWeeksHits with the modified version here. I added php comments in the code to show the additions.

Feeling rather happy about my simple addition, I decided to go for more. What I really wanted was uniques grouped by year and month so that I could see growth or decline over a greater period of time than a week. The only issue was that I have never performed a SQL group by with unix timestamps, Shaun’s method of storing the date of each hit.

I did a bit of research on MySQL.com and quickly found the FROM_UNIXTIME function. The first parameter of the function is the unix timestamp and the second parameter is the format you would like to return.

$sql = "SELECT FROM_UNIXTIME(dt, '%Y') as year, FROM_UNIXTIME(dt, '%M') as month, FROM_UNIXTIME(dt, '%m') as monthnum, COUNT(DISTINCT remote_ip) as total FROM $SI_tables[stats] GROUP BY 1, 3 ORDER BY 1 DESC, 3 DESC";

In SQL, you can use numbers in your group by’s and order by’s. The numbers correspond to the order in which you are selecting. For example, the first item I am selecting is the month and the second selection is the year. A bit more readable version of the above statement is:

SELECT year, month, month number, count of unique remote_ip's FROM stats table GROUP BY year, month number ORDER BY year DESC, month number DESC

The problem is that you can’t group by year and month because you will get a SQL error. You could substitute the first SQL statement with:

$sql = "SELECT FROM_UNIXTIME(dt, '%Y') as year, FROM_UNIXTIME(dt, '%M') as month, FROM_UNIXTIME(dt, '%m'), COUNT(DISTINCT remote_ip) as total FROM $SI_tables[stats] GROUP BY FROM_UNIXTIME(dt, '%Y'), FROM_UNIXTIME(dt, '%m') ORDER BY FROM_UNIXTIME(dt, '%Y') DESC, FROM_UNIXTIME(dt, '%m') DESC";

However, it is plain to see that it is a lot easier to just use the numbers. Enough with the mumbo jumbo. You probably just want to know how to use it. Open up functions.php again in your favorite editor and add the entire function which can be found here.

Now that we have added the function, the only thing left is to call it in index.php and format it like the others. Add the code below to index.php wherever you would like your uniques by month to show up. I added this right below div containing the SI_getWeeksHits function call.

1. <!-- Added by John Nunemaker http://johnnunemaker.com -->
2. <div class="module">
3. <h3>Uniques by Month</h3>
4. <div><?php echo JN_getMonthlyUniques();?></div>
5. </div>
6. <!-- End of Added by John Nunemaker -->

I have only had Shortstat installed for a few weeks so I do not have much data to summarize but I am pretty sure that it works. One glaring improvement would be figuring in the timezone difference, something I have not had much experience with. As always, my goal is to learn and if anyone has a better idea, be sure to let me know.

Also, for anyone interested you can check out my install of shortstats to see all of the code above in action. Just a note, I also have PathStats, created by Jehiah Czebotar, installed. To view it just click the link at the top of my shortstats that says pathstats.

Update: I just realized I ordered things a bit unnaturally in the ‘Uniques by Month’. Instead of ORDER BY 1 DESC, 2 DESC, it should be ORDER BY 1 DESC, 2 ASC. This makes it so the current month is always first in the list. Sorry, I was only using one months data so I didn’t get a chance to recognize this mistake before posting the article. I have updated all of the SQL statements and the corresponding files. Ignore this if you are just reading the article for the first time.

Another Update: Once again I have been too hasty. I believe everything should be straight now. I was ordering by the monthname which is not going to display correctly so I added the month number in the select and order by that instead. I have fixed the sql statements above so if you are reading this now for the first time ignore this.