Data Analyst To The Rescue!

Every once in a while, it comes in handy to have been a data analyst.

Warning, if that last sentence seems boring to you, please stop reading now. This blog post is ridiculously geeky and has no place whatsoever on a phone sex blog.

Last night, I had re-motivated myself to do the SEO (search engine optimization) stuff for my new blog location. The point is for more people to find my blog via search engines. I know others have had fantastic results with driving traffic, and I'm proud of the blog, and sometimes people become callers after reading it, so it's worth it to me to SEO the blog.

I was taking data which existed (blog title, categories, the first 160 letters of the post as a description, etc), and pasting them into the appropriate fields in Word Press to populate the "meta" fields from my theme, and the "SEO" fields from my plug-in, just in case they were used differently.

I realized (1) I had only done about 20 blog entries out of 321 by copying and pasting, and (2) I was ready to gouge out my eyeballs from the boredom and tedium.

Thankfully, at that point, I remembered that WordPress has a MySQL back end, and although I almost never worked with MySQL (I did SQL server shops), and almost never done databases via a web interface, I guessed I could muddle way through it.

Sure enough, I figured out how to back up the database (the critical first step), and then understood the data structure pretty quickly. And may I just say for the record, wp_postmeta is a damn fine, dead sexy table. Seriously. Hooray for smart relational database design which allows infinite flexibility. I think I got a little heart-thumpy when I saw it.

I did the post titles and descriptions easily, although I had to look up INSERT INTO syntax, but hell, I did that when I was working with SQL every day, because I could never be bothered to learn syntax order beyond SELECT... FROM... WHERE... ORDER BY. Lazy? Or efficient? It depends on which productivity studies you read, but I choose to believe the latter.

Then came bugger #1: turn categories into tags. Categories and tags are both stored relationally, and if the same term is both a category and a tag, it's tracked via the incredibly crafty wp_term_taxonomy table, which practically had me swooning with delight.

So first I had to insert taxonomies for all my categories which had no corresponding tag, then link up the tables to figure out the blog post -> category -> tag relationship where none yet existed, and insert those. I was pretty proud of myself for knocking those out. Hoooray for WHERE ... NOT IN, amirite?

Then came bugger #2, which I had to sleep before I could do, because data analysis makes me hellaciously dizzy (not worse than all the damn copying and pasting, though): turning categories into keywords. The categories are stored relationally, but the keywords are stored as a concatenated comma delimited string, so I had to smush them.

And this is where I probably would have fired myself two years ago... I didn't have permission to do a temp table on the server, and didn't want to mess with my database permissions, and my SQL wasn't working right iterating through a dumb CURSOR, so I did the kludge-iest solution ever:

• took the list of blog posts and categories and turned them into a pivot table, so each line had a blog post ID, and all of its category terms across 98 columns
• copied the pivot table into Notepad to get raw text without table/cell delimiters
• copied the raw text  into Word to replace tabs with commas
• stripped out repetitious ", , " strings, which gave me a blog ID and its comma-delimited keyword string
• used that blog ID and the keyword string to craft INSERT statements for each blog entry
• ran the inserts on the server

Such a hack. Such an ugly, ugly hack. I can't decide if I'm horrified or delighted with myself. I hope nobody who ever worked for me ever finds this... although, if they did, they'd probably say "She always did say, if it's a one-time job, just frikkin' get it done. If you have to do it twice, then you're allowed to automate. At least she practices what she preaches."

So now I'm that much closer to announcing the new blog and asking everyone to move locations. I need to clean up the links on the "Where to Start" page, and create the other pages, but the SEO stuff was the hard part, and it's done.

Whew.

Now it's time to stop boring my readers to tears, and return to my sexy!