Automate Everything w/ Bash, Linux & Command Line
  1. Keyword Research in an Excel Spreadsheet

    I’ve written about plenty of the keyword research tools I’ve written and about keyword research ideas/strategies I’ve had on this blog. I’ve written about it so much because it’s really the foundation of search marketing strategy. Without understanding how your audience is looking for your site, you have no real hope of being able to get your website in front of them.

    The tools I’ve written about here have all been really raw. They aren’t polished with features and none of them could be used by anyone who isn’t a programmer. That’s a shame, but again, most of the tools I write are purpose built for the way I work, for my workflow. If they work for me then I’ve been content with the result.

    Why Can’t People Use the Tools I Write?

    Even though my tools have been usable for me, I always end up having a conversation like this with peers, coworkers, etc…

    Wow, that’s a really efficient way to do X. How can I use it?
    Do you use Mac or Linux?

    Yes, I have a Mac!
    Cool. I emailed you the script. Just save it somewhere, set it as executable and then running from a terminal.

    What’s “a terminal”?
    It’s the command line.

    What’s the “command line”?
    Shit, this isn’t going to go well…

    Even worse yet is that the majority of people still use Windows.

    So even though my tools have worked for me, they rarely work for others. I’d be lying if I said I didn’t constantly wish there was a way to quickly write apps for the desktop that would be cross-platform and super efficient to produce and share.

    More Usable Keyword Research Tool

    One day I read about a new piece of software called IronSpread. It promised to bring the Python programming language to Windows users, directly within Microsoft Excel. My first reaction was, WOAH, shit… that would be amazing. At that time I hadn’t ever used Python for anything but I knew it is a powerful language.

    Just a couple weeks ago I finally got around to giving IronSpread a try. I haven’t been programming much lately which is why it’s been a long time since my last post. I found that IronSpread is now Data Nitro and it’s still actively being worked on. They offer a free 30 day trial and it works great with Excel 2013.

    I’ve used it both on my Windows 7 machine at work, running Excel 2013 and on my Ubuntu machines running VMware, Windows 8 and Excel 2013. It’s worked perfectly in both those scenarios. However, I tried getting it setup in a Windows 8 VM on Virtualbox with absolute fail being the end result.

    I’m very novice a writing Python scripts but within 5 hours I was able to build a super useful keyword research tool that integrates perfectly with Microsoft Excel. Finally, I can write tools that I can share with the masses.

    The Demo

    Here’s the script that I came up with first. It’s best described by this video.

    The Python Script

    I admit that there’s probably a bit of polishing that could be done to improve the script, but I’m novice and this was my first try. It works pretty well though.

    import json
    import urllib2
    import re
    import string
    from time import sleep
    startTerm = Cell("Iterative Suggest","A1").value
    lastSeedTerm = startTerm
    while True:
        seedTerm = Cell("Iterative Suggest", "A1").value
        if seedTerm != lastSeedTerm:
            urlEncodedQuery     = re.sub(' ', '+', seedTerm)
            # give feedback signaling that it's working
            Cell("C1").color            = "yellow"
            Cell("C1").value            = "...working..."
            Cell("C1").alignment    = 'center'
            Cell("C1").font.bold    = True
            # this appends each number iteration.
            for iteration in range(1,10):
                seedTermIteration   = urlEncodedQuery + "+" + str(iteration)
                googleSuggestURL    = '' + seedTermIteration
                data = json.load(urllib2.urlopen(googleSuggestURL))
                for suggestion in data[1]:
                    suggestion = re.sub('<[^<]+?>', '', suggestion[0])
                    last_cell_in_col("A", "Iterative Suggest").offset(1,0).value = suggestion
                lastSeedTerm = seedTerm
            # this prepends each number iteration.
            for iteration in range(1,10):
                seedTermIteration   = str(iteration) + "+" + urlEncodedQuery
                googleSuggestURL    = '' + seedTermIteration
                data = json.load(urllib2.urlopen(googleSuggestURL))
                for suggestion in data[1]:
                    suggestion = re.sub('<[^<]+?>', '', suggestion[0])
                    last_cell_in_col("A", "Iterative Suggest").offset(1,0).value = suggestion
                lastSeedTerm = seedTerm
            # this appends each letter iteration.
            for iteration in string.lowercase[:26]:
                seedTermIteration   = urlEncodedQuery + "+" + iteration
                googleSuggestURL    = '' + seedTermIteration
                data = json.load(urllib2.urlopen(googleSuggestURL))
                for suggestion in data[1]:
                    suggestion = re.sub('<[^<]+?>', '', suggestion[0])
                    last_cell_in_col("A", "Iterative Suggest").offset(1,0).value = suggestion
                lastSeedTerm = seedTerm
            # this prepends each letter iteration.
            for iteration in string.lowercase[:26]:
                seedTermIteration   = iteration + "+" + urlEncodedQuery
                googleSuggestURL    = '' + seedTermIteration
                data = json.load(urllib2.urlopen(googleSuggestURL))
                for suggestion in data[1]:
                    suggestion = re.sub('<[^<]+?>', '', suggestion[0])
                    last_cell_in_col("A", "Iterative Suggest").offset(1,0).value = suggestion
                lastSeedTerm = seedTerm
            # give feedback signaling that it's done
            Cell("C1").color            = "aqua"
            Cell("C1").value            = "done!"
            Cell("C1").font.bold    = True
            Cell("C1").alignment    = 'center'

    That’s it. Now you’ll have Google Suggest keyword research integration right in Microsoft Excel after you give Data Nitro a try.

    Let me know what questions you have in the comments.

  2. Pinterest Pin It Button/Bookmarklet Hybrid

    AKA - Pinterest “Pin It” Button Implementation Revisit

    I wrote a post about a year ago about how to install the Pinterest Pin It button. Why would I have to do that? Well, because the code generated in the developer docs was incomplete, confusing and led to many many broken implementations on sites. I know that’s true because of how popular that post was.

    Read the old post here.

    Pin It - A Year Later

    Well, I’m writing this post because many of the same issues still exist. They have done a better job of making sure that people understand how to install the code, but they’ve still allowed it to be easy for people to install it incorrectly on their sites. The biggest problem is when the media parameter isn’t tagged properly in the button href.

    The original version would just fail if an image wasn’t specified. The new version allows you to pin the page, with a broken image. How much sense does that make?

    The Pin It Bookmarklet Works…

    What makes all this worse is that the Pinterest bookmarklet that they’ve had since the very beginning has more functionality in it than the button does. Ever notice that? It’s really mind-boggling.

    Why wouldn’t they just mimic the functionality of the bookmarklet with the Pin It button? When you use the bookmarklet it gives you a gallery of images for you to choose from to use in your pin. That makes the bookmarklet usable on many different types of pages with no configuration required. Whereas the button has to have one image hard coded to it. This makes zero sense.

    So I thought… Why not just rewrite the button functionality so that it calls the Javascript from the bookmarklet instead of the code the button would normally run? Would that work? YES IT DOES!

    The Pin It Button - Pin It Bookmarklet Hybrid

    The implementation is really simple. Just start out by adding the following code, as recommended by Pinterest, on the page where you want the button to appear. The one change is that I’ve added an ID to a surrounding <span> tag.

    <span id="pin-it"><a href="//"><img src="//" alt="image" /></a></span>

    Then, just add the following script to your page. Make sure you have the jQuery library on the page as well.

    <script type="text/javascript">
        jQuery.getScript('//', function() {
            jQuery('#pin-it a').attr('href', '#');
            jQuery('#pin-it a').attr('onclick', "javascript:void((function(d){e=d.createElement('script');e.setAttribute('type','text/javascript');e.setAttribute('charset','UTF-8');e.setAttribute('src','//'+Math.random()*99999999);d.body.appendChild(e);}(document)));");

    That’s it. You should now get the bookmarklet action when the button is clicked, which will show a gallery of images on the page to choose from to use in your pin.

    Here’s an example implemented right here: image

    Anyway… Happy pinning!

  3. Big Data, Privacy & Ad Targeting

    I think it’s important to talk about my perspective and potential bias. I’m a digital marketer. It’s what I do. But, sometimes I feel like Harvey Dent.

    {Image Source}

    I use cookie based tracking data every day for website optimization, ad targeting and business intelligence. There’s no way to deny how powerful an understanding of the information available, even with a vanilla install of Google Analytics, can be to your business. There are often very enlightening stories about what website users want, need or don’t understand that will help you improve your product. The structure, quantity, organization and analysis of this data is getting better at a fanatic pace. I feel like the big gap between how this data is used and how the general population speculates about it’s use is just too wide.

    Or, Popular Are Privacy Concerns Right On Target?

    I continually ask myself that question. As a consumer, I quit using Facebook. It wasn’t all about privacy but it had a role in my decision. But Facebook isn’t the only offender. Not by a long shot.

    Recently there have been a few time where I’ve really had to stop, think and question my perspective on ethics surrounding online tracking technology. A couple of those prompted this post.

    De-Anonymizing Tracking Data

    What does that mean? Well, all the tracking data provided in common analytics packages like Google Analytics is anonymous until the visitor fills out a form and tells you who they are. This level of privacy is by design.

    There are companies springing up that seek to change that. They pool data together from sites across their network partners that track the behavior of a visitor. When that visitor identifies him/herself on site A, then all the partner sites will know who that anonymous visitor was without the visitor actually telling site B, C or D.

    Here’s the post I read that explains this much more effectively than I can. It’s creepy and I think it’s unethical.

    It isn’t just new emerging businesses or technologies that can be creepy/evil. In fact, the bigger data players like Google have the ability to make these connections now. But are they?

    When Ads Are Too Personal

    Until now, the news of my wife and I separating hasn’t been public. The decision was made about 1 month ago. My communication of that with friends, family and co-workers has been limited to word of mouth, GTalk and SMS.

    Imagine my surprise when I saw this ad at the top of my GMail account last night…

    If you cannot imagine - I was creeped the fuck out.

    After the shock of it wore off I started to think about how it was possible for this ad to be so spot on. To be honest, I have no way of knowing. And because I don’t know I’m creeped out. I don’t like it. Instead of ads being relevant, useful and persuasive, this ad gave me chills (not the good kind).

    So, how? One can only speculate. Perhaps the add was just a coincidence. Maybe GTalk conversations are monitored for content based targeting just like GMail messages are. There could be any number of reasons.

    The point of this post is that it doesn’t matter how tracking and targeting works online. I believe people prefer ads that are relevant to their interests, but they need to understand how and why ads can be so accurate at times. They also need to have the tools necessary to opt-in or out of this targeting. Without the transparency people will be left to speculate and become more upset about how their privacy is being abused online.

  4. IBM Model M Keyboard Collection - On Sale

    It’s time. I’m going to part ways with my small collection of buckling spring IBM Model M keyboards (the original clicky keyboard).

    Why? Well, because I have too many keyboards and I have an upcoming project and want to replenish my PayPal account. I’m going to list them on eBay but I thought I’d list them here just because. So, here they are…

    IBM Model M, Part No 1391401 - Produced on April 8, 1988

    Bid on this keyboard

    This is considered a “white” label IBM Model M keyboard and is fully functional. It has very low yellowing and types very nicely. I’ve used it as my main keyboard for the past two plus years and it’s been very reliable. It has the curly style removable cable and is in great condition. The feet on the back are also in great condition, which is more rare because they’re known to be a weak point. I don’t use them so I haven’t added any wear during my time using it.

    They buyer of this keyboard is going to get a very well taken care of keyboard that is very likely to out last the machine he/she will use it on.

    Here are a couple of images:

    An image of the label - part number and production date.

    I will post back a link to the auction as soon as it’s up. If you’re interested in purchasing it outside of the auction then please comment below or reach out to me on Google+ and we’ll see if we can work out a deal.

    IBM Model M, Part No 52G9685 - Produced on January 4, 1996

    Bid on this keyboard

    This keyboard is also technically a IBM Model M, but in the later years these keyboards were produced by Lexmark for IBM, as you’ll see in the images of the back label below. With that said, this keyboard works very well. There are three main differences between this keyboard and the older “white” label Model Ms:

    1. The key caps are not removable. Most of the time this isn’t a big deal, in fact it makes the caps much more difficult to lose. It also makes them a lot harder to clean.
    2. The cable isn’t detachable. This really only becomes a problem if the cable it’s self breaks.
    3. While the keypress is still very clicky, it’s also a bit more soft. This is either appealing or not. It really just depends on your personal typing preference.

    While this keyboard is a bit less rare than the other two I’m selling, it is bound to have fewer keystrokes and is almost a decade newer. It’s a very reliable keyboard and all the keys feel consistent type nicely. I’ve used this keyboard for the past year and a half at work. The one thing that’s wrong with it is that one of the feet is broken. It never bothered me because as I mentioned above, I don’t use the feet.

    Here are a couple pictures:

    Again, I’ll post a link to it once it’s up on eBay.

    IBM Model M, Part No 1391401 - Produced on June 7, 1987

    Bid on this keyboard

    This is the final IBM Model M I have to offer. It’s the oldest one and share many of the same features of the first one posted above. It’s considered a “white” label and is almost as old as I am…

    It has the curly detachable cable that’s in great shape. It has fully removable key caps for easy cleaning. It has strong buckling spring keys that are very clicky. It’s exterior is in great condition. The only blemish on this one is that the enter key on the number pad isn’t balanced properly because one of the support pieces is missing. It’s very likely that it can be repaired later, but even in it’s current state it can be used. Other than that, this keyboard is in great shape despite it being 25 years old!

    I haven’t used this one much at all. For the past six months or so it has just been sitting in my closet. It’s time to go. This keyboard was meant to be used.

    Here are a couple images:

    Once again, I’ll post the link when it’s up on eBay. If you’d like to reach out and make an offer ahead of time please feel free to contact me on Google+. There’s a link in the left navigation of this site.

    Please let me know if you have any questions about any of the keyboards listed here.