When I attended the LinkLove Boston conference this year, one of the sessions that stuck in my mind was Rhea Drysdale‘s presentation and her Link Building Strategies Spreadsheet (that she based on Jon Cooper‘s great list of almost 200 link building strategies but with added meta data such as time & resources requirements as well as value of links). As soon as I saw the spreadsheet, my initial thought was that “Someone should just create this sort of filtering functionality on the original post — It should be pretty easy to do with jQuery”. Well, when I got back home from the conference I decided to give it a shot over the Easter weekend.
This was the first time I’ve given something significant back to the community, and it felt good. There were some questions on how I technically did this, so here are all the details (I’ve tried unsuccessfully to keep them as non technical as possible ).
- Technology Used: jQuery + HTML/CSS + Regex + MS-Excel + Set Theory
- Time to Build: 4 hours + 2 hours to integrate with WordPress
- Key Learnings:
- You can produce valuable functionality using only client side code
- Using Excel as a HTML code generator can yield impressive results
- The Excel VLOOKUP function is AWESOME (I can’t believe I never used it before)
- Finally figured out how to lock cell references in excel when copy/pasting formulas (use: $ in front of row/col values – I’ve been searching for this answer for years. Can’t believe it took me so long to figure it out.)
- Developers can use their coding skills for link and relationship building
- LinkLove is a great conference – Highly recommended
- Hustle: Important to be timely with your creations
When I undertook this project I had a few self imposed restrictions:
- Must work & run on PointBlankSEO.com. This means that it must run using the existing infrastructure (WordPress)
- Must be easy to install. I assumed that Jon knew nothing about coding and so ideally I would just send him a text file with instructions to “copy and paste this into your post”
- Must have stakeholder buy-in. This meant that both Rhea from Outspoken Media and Jon from PointBlankSEO.com would need to see this as valuable (And I certainly did not want to step on any toes).
Database and server side code seemed out of the question –100% client side code seemed like the obvious answer. Since all the content I needed was already on the page in HTML, it would just be a matter to Hide/Show the “Strategies’ depending on the filter criteria.
There would be 3 different filters: One for Link Value, One for Resource Dependencies, and one for Time Required to Implement.
Step 1: Create the Filtering Form
This was easy enough. A simple HTML
Step 2: Create the Filters to Show/Hide the “Strategies”
Link Value Filter
This seemed like the easiest one to implement. All I needed was to have each strategy wrapped in a DIV tag that had a CLASS of “High”, “Moderate”, or “Low”, etc… When someone would click on a checkbox, I would use jQuery to just Show or Hide all DIVs with that specific class. Easy.
This also didn’t seem too complicated. I would create another DIV tag wrapper and I would set multiple classes (one for each dependency) and would turn the div on/off depending on if it had a CLASS named with that dependency. In order to avoid weird situations where an item is shown when it should not, I first SHOW all tactics with selected dependencies and then HIDE all tactics with dependencies that are NOT selected
Time to Execute Filter
This filter confused me at first, but I finally figured it out. I wanted it to work a little differently than in Rhea’s spreadsheet. ie: If you say you have 8 weeks to complete your strategy, then you want to display all tactics that take LESS THAN 8 weeks (as opposed to all tactics that take EXACTLY 8 weeks).
I did this by wrapping each tactic with another div tag with a multiple classes, one for each possible week value that this tactic should be visible for. e.g.: For a tactic that takes 4-8 weeks to implement I would include the following classes: 4weeks, 6weeks, 8weeks, 12weeks, and moreweeks. This means that this div would be visible if the time period available to execute was 4,6,8,12, or more weeks.
In order to make sure that everything was properly in sync, the logic involved first hiding ALL time ranges, and then showing the tactics what were marked as valid for the currently selected time frame.
The example HTML:
And the jQuery to filter on this:
All Three Filters in Action
The combined HTML code for each item, wrapped in the 3 filter DIVs, looks something like this:
These three filters were AND filters, meaning that an Item must match ALL the filter criteria in order to be visible. Although you could do this sort of thing in code I decided to take the simpler approach and to just use individual nested DIV tags for each filter.
Step 3: Generate the code
Now I knew exactly how to filter the original post with jQuery. One problem however: Jon’s post wasn’t formatted anything like I needed it to be. Additionally, Jon’s post didn’t have any of the additional details related to Time, Dependencies, and Link Value that were included in Rhea’s spreadsheet.
I decided that I would use Rhea’s spreadsheet to generate a duplicate of Jon’s post, but with the HTML formatting that I needed. Visually, it would look almost identical to the original, but under the hood I would have all my filtering DIV tags properly nested. This would have been a lot easier if Rhea’s spreadsheet had all of Jon’s Tactic Descriptions, but it didn’t.
Import Descriptions from PointBlankSEO.com into Rhea’s Spreadsheet
Option 1: I first considered doing it manually but quickly dismissed that thought: There are about 200 items in the original post AND Rhea’s spreadsheet doesn’t have the items listed in the same order as Jon has in his post.
Option 2: Use Regex and Excel to do the heavy lifting for me
Download the HTML source code from the original post
Use Regex Search & Replace to strip out all the junk that I wasn’t interested in, and leave only the “Tactic” heading and description separated by a custom character (in my case a “pipe” character: |):
I then copy/pasted this into excel and did a “Text to Columns” in order to have 1 column with the tactic title and the second column with the HTML description:
I then used the awesome excel function VLOOKUP to matchup each of Rhea’s tactic titles with the titles this new sheet and import the description into the original spreadsheet. (I also used VLOOKUP to simplify some of my HTML Class Name generation)
Now I just needed to create a new column on the original spreadsheet and create a formula that would generate me the HTML that I needed. Here is the formula I used:
This produces an output of this:
Create an RSS feed
If your blog is run on any of the popular Content Management Systems, you’ll already have an RSS feed. If you don’t, create one. If you do, burn it at Feedburner.com so you can get statistics on your subscribers.
For link building, it’s simple. There are sites out there that will scrape your content (stealing it without permission). When they do, make sure you get a link back by 1) including links to other pages on your site in your posts and 2) installing the RSS footer plugin for WordPress (adds a link to your blog after every post).
Step 4: Get WordPress to Play Nice
Now, theoretically, it was just a matter to select all cells in this column, and copy paste them into an HTML document, test it locally, add my jQuery filtering code, and send it off to Jon! That worked fine locally on my machine, but once we tried to get this working on WordPress I ran into some issues. In the end (and to make a long story short) we ended up installing the WordPress Raw HTML plugin and everything started to work more-or-less well.
Step 5: Send it off to the Stakeholders and hope they like it!
Turns out this was the least of my worries. Both Rhea and Jon loved it, and it actually turned into a pretty cool story of how the SEO community “open sourced” the original blog post to build on and create something better:
If you haven’t already checked them out: