I’m sure there are some people who love the process of monthly reporting. I’m not one of them.
I get it- they’re important. Statistics, data, information about information- it’s all useful stuff. The stuff that makes a business run. But there’s something about staring at a spreadsheet at the end of each month that’s just depressing.
For years, we’ve used a CSV generator to get the basic info we need for our monthend reports. But it’s not perfect. There was a lot of manual data entry and lookups- stuff that should have been automated long ago.
And it’s my job to do things like that- but the idea of spending a few hours on a project that would not enhance the product for our users at all seemed selfish. I know some large companies have people, or even whole groups, dedicated to making their own tools easier to use for their own employees, but we just aren’t big enough.
So, last week I decided to look into automating the process a bit more. And it wasn’t a cakewalk. Getting our site counts, individual user counts, and related data was easy. But getting new info – particularly the hit count for the homepage of just relevant sites was a bit of a challenge.
The hit count data is stored in a different table than the rest- so a whole new set of queries were created and installed in the original process. The trickiest part was getting just the homepage- when the database was first designed, they simply added a string ‘[home]’ to the front of the homepage for a site, instead of using a nice boolean flag. Easy enough- the SQL just grabs a substring of the first 6 characters and matches it against ‘[home]’. But there’s also the possibility that an admin has updated the title of their homepage at some point in the past month. So there might be 2 or 3 or even 10 rows that match that criteria. Now we have to create a loop over them and add up this count for each using a temporary ‘total’ variable. Though now that I typed that, I think we might be able to use SUM(count) for the query (assuming we’ve already snagged the proper rows only)- I wonder which one is faster?
Either way, the project is finished and now the initial report listings can be generated on a region by region basis with one click. There’s still some work that has to be manual (checking on trouble listings, adding comments for losses/wins/etc), but it should make for a much quicker report process.