Analyzing Amazon Affiliate sales data with MySQL workbench and simple SQL queries

This article is a tutorial for taking Amazon Associates (Amazon Affiliate program) export data into MySQL workbench.

Screenshot 2017-01-21 21.58.25

In MySQL workbench, you can easily view and sort your data in some sophisticated ways that Amazon’s own dashboard doesn’t really support.

I wanted to ask questions like:

  • What was my best-selling item for this particular affiliate ID?
  • What items make the highest commission?
  • Which items get returned the most frequently?

Here’s how I dumped my Amazon Affiliate data into MySQL workbench and some of the queries I used to explore it. I’m pretty new to SQL so I’m sure there are even more sophisticated things I could be doing with this data and different ways to do the same thing!

(You will need to use your own .csv data dump to follow along with this tutorial.) 

Step 1: Get a .csv export of your Amazon Affiliate data

Use the Download Reports button from your Amazon Affiliate dashboard to download your account’s data as a csv (comma separated values). Be sure to adjust the date range.

For this analysis, I exported all of 2016’s Amazon Affiliate data. It’ll generate a bunch of files, but to follow this tutorial you’ll want the file that starts with Fee-Earnings-

Screenshot 2017-01-21 20.11.59

This might take a while, especially if you’re exporting an entire year’s worth of data like I did. You can do the next several steps while you wait.

Step 2: Download and install MySQL workbench

You can download the latest version of MySQL Workbench here.

Step 3: Make a new connection

Screenshot 2017-01-21 13.40.15

Step 4: Start your computer’s MySQL service

You may have to manually start your MySQL server.

On a Mac, it’s in Apple menu > System Preferences… > MySQL

On a Windows computer, these steps might help you.

Screenshot 2017-01-21 13.46.20

Step 5: Create a new schema in MySQL Workbench

Right click in the light blue area underneath SCHEMAS in MySQL Workbench. Choose Create Schema…

Screenshot 2017-01-21 13

Give it a name and leave the rest as-is:

Screenshot 2017-01-21 13.50.21

You should now see the new schema in the SCHEMAS section:

Step 6: Prepare your csv data for import

Your csv file’s first line will cause problems on import, so let’s open the csv and remove it. I used Microsoft Excel for removing the first line but you can probably use anything capable of opening, editing, and saving a csv file (including a basic text editor).

You need to get rid of the first line, the one that starts “Fee-Earnings reports…”

Screenshot 2017-01-21 16.29.24

If you’re saving from Excel (and this problem might be limited to just Excel on a Mac), you should save it as a Microsoft Comma Separated. On my Macbook, at least, saving as a normal .csv from Excel will cause it to fail the import in MySQL Workbench.

Screenshot 2017-01-21 16.53.25

If you have any problems importing the csv file in MySQL Workbench, try different csv formats after saving your csv file from Excel (or whatever you use… I’m convinced my import problems were just a result of using Excel on a Mac.)

Step 7: Bring your csv data into MySQL Workbench

Right-click your schema and choose Table Data Import Wizard.

Screenshot 2017-01-21 14

Follow the prompts and choose your cleaned up .csv file. If everything works, you should end up with a screen like this:

Screenshot 2017-01-21 16.54.49

If you run into any import errors, make sure you’ve 1) removed the first line from the file and 2) saved as a Windows CSV (if you’re saving from Excel, possibly just Excel on a Mac. I don’t have Excel on my Windows computer to test this theory).

Follow the prompts to finish importing the data.

Step 8: SQL queries!

Finally, the fun part!

See the little “circle” made of arrows to the right of the SCHEMAS title? Click that and Tables should refresh and get a roll-out arrow. Click that arrow and you’ll see your data in a table. Right click to select the top 1000 and open the editor.

Screenshot 2017-01-21 16.56.41

Here’s what you should have now: an editor pane and a bunch of results below.

mysql_queries_amazon_affiliate_data

Each query starts with a SELECT and ends with a semicolon (“;”). To run just one query, place your editor cursor anywhere inside the query and click the lighting bolt with a cursor icon.

Screenshot 2017-01-21 20.40.39

Here are some queries I developed for analyzing my Amazon Affiliate data. Feel free to steal them, modify them, and use them to analyze your own affiliate sales data.

Select all the items sold for a particular tracking ID

This one’s straightforward: it selects all the items sold under a particular tracking ID. Most of my more sophisticated queries are built on this one, and it’s a good way to narrow down your data if you have multiple IDs.

SELECT * FROM amzn2016_schema.data 
WHERE `Tracking ID` = "yourtrackingidhere-20";

See which items earned the most money (“fees”) for a particular affiliate ID

SQL comments start with a #, so they’re handy for adding notes to your queries. This query selects all the items for a particular ID that earned me $5 or more, with the highest earning items at the top of the list.

#just the most profitable items from that ID
SELECT * FROM amzn2016_schema.data 
WHERE `Tracking ID` = "yourtrackingidhere-20" 
AND `Ad Fees($)` >= 5
ORDER BY `Ad Fees($)` DESC;

See which items were your most frequently returned

Here’s something Amazon doesn’t make easy to figure out in their dashboard: which items you sold that got returned, and in what quantities.

This query tells you the names and tracking IDs of the items that had return counts, with the highest at the top.

#most returned items
SELECT `Tracking ID`, `Name`, floor(SUM(`Returns`)) as `Return count` 
FROM amzn2016_schema.data
GROUP BY `Name`
ORDER BY `Return count` DESC;

See which items earn $15 or more per sale

What items earned you the largest commissions? This query will tell you.

You can change the 15 to whatever you want your bottom to be – for me, earning $15 on a single sale is super awesome so I wanted a list of just the items that earn $15 or more per sale. Who knows, I might find a new product to write about by digging around this list!

#items with ad fees over $15
SELECT * FROM amzn2016_schema.data 
WHERE `Ad Fees($)` >= 15 
AND `Items Shipped` = 1 
ORDER BY `Ad Fees($)` DESC;

I set it to only return items that have 1 item shipped, otherwise I get groups of items and artificially inflated Ad Fees as a result. However, since some items have only been purchased in sets of multiples, it’s a good idea to change this number to a 2, 3, or more (or remove the line altogether) to see those items, too. 

Sure enough, I found some items that earned me some large commissions – $44.93, $39.75, and $36 off of these single items! These aren’t items I blog about, but… maybe they should be now that I know about them! :)

mysql_query_most_profitable_amazon_items

Find your most expensive item sold through Amazon Affiliate program

That got me wondering: what’s the most expensive item someone has bought through one of my Amazon Affiliate links?

This query made it easy to figure that out:

#most expensive item sold
set @maxPrice = (select MAX(`Price($)`) FROM amzn2016_schema.data);
SELECT * FROM amzn2016_schema.data WHERE `Price($)` = @maxPrice;

My most expensive item sold is one of those sweet Wacom tablet computers! Cool!

most_expensive_item_amazon_affiliate_2016_my_data

Notice how the ad fees (my earnings from selling this item) are capped at just $25, despite its high sales price? That’s the nature of Amazon Affiliate program – some items, even though they sell for a lot, don’t always earn a proportional amount in ad fees. (Some items that cost much less than this tablet computer actually bring in higher ad fees.)

See earnings and items sold, by affiliate ID

Here’s another fun bit of data, especially if you have multiple sites or multiple IDs for a single site and want to compare them all. This query outputs each affiliate ID, how many items it sold total, and how much money it earned.

#all IDs and how much they made, grouped by ID
SELECT `Tracking ID`, floor(SUM(`Ad Fees($)`))
as `Fees earned`, COUNT(*) as `Total items sold`
FROM amzn2016_schema.data 
GROUP BY `Tracking ID` 
ORDER BY `Fees earned` DESC;

Find your best-selling items and how much they earned (collectively)

If you’ve ever wanted to know what your best-selling items are across all your Amazon Affiliate IDs and how much you earned from those items, here you go:

#best-selling items by tracking id with fees earned as a total 
SELECT `Tracking ID`, `Name`, 
COUNT(`Name`) AS `number sold`, 
floor(SUM(`Ad Fees($)`)) AS `earnings`
FROM amzn2016_schema.data
GROUP BY `Name`
ORDER BY `earnings` DESC;

That’s it for now! I hope you found these queries useful for analyzing your Amazon Affiliate sales data.

 

What to change in MaxCDN settings after you change web hosts

I just moved one of my blogs to a new host (yay!). This blog uses MaxCDN for its content delivery, and moving the blog to a new host messed up the site’s styles and it took me a while (plus some back and forth with support) to get everything fixed because MaxCDN was still referencing the old host.

maxcdn_logo

In case I ever do this again, here’s what needed to be done to move my WordPress blog to a new host with MaxCDN as my content delivery network.

Step 1: Add a CNAME record to your new host

My new host has CPanel (Digital Ocean, by contrast, had a Networking tab with a link to Domains and their records were accessible through there). If you have CPanel, click on Simple DNS Zone Editor.

Add a new CNAME record. It’ll probably look something like this:

Name
cdn1.yoursitename.com.

Record
cdn1.yourbusinessname.netdna-cdn.com

(Your CPanel might add the . at the end of name for you, and it might autocomplete for you if you just type the subdomain portion and then tab out of the field.)

MaxCDN has good documentation on updating CNAME records for a variety of hosts, too.

Note: I use a custom domain in MaxCDN because I don’t want it to use the default “business name” URL that MaxCDn gives you.

Step 2: Update the Origin IP over in MaxCDN’s settings for your pull zone

Go to Zones > Pull Zones > Settings and get into that particular pull zone’s settings. At the bottom is Origin Information. Check the checkbox and enter the IP address for your new host. Click Update button to save.

Step 3: Whitelist your new IP

Go into your Account > look under the API section > click Manage > add your new IP as a whitelist IP.

You may also need to whitelist your own IP address, if you get problems with cURL requests failing when you try to clear CDN cache.

Step 4: Update your WP caching plugin

You may need to reconfigure your WP cache plugin. I use W3 Total Cache which, for reasons unbeknownst to me, likes to replace my entry for “Replace site’s hostname with:” with the word “Array” instead of the URL I give it.

For reference, “Replace site’s hostname with:” should be followed by your cdn url, like cdn1.yoursite.com.

Step 5: Purge all caches and check your site

When you’ve done all of the above, purge your CDN cache and your WP cache via your caching plugin.

It might also help to flush your local DNS. I’m on Windows and I do that in a command prompt with ipconfig /flushdns

Open a Chrome incognito tab and load your site – if your styles and images load, you’re good to go. If your site looks incomplete, look in the console for an error message. I found many of them (like 502 bad gateway) to be covered in MaxCDN’s documentation.

More tools for debugging DNS, caching issues

If you’re having problems, try these tools:

Did it propagate yet? Whatsmydns.net is a great way to check propagation and see what is actually getting served when you try to hit your CDN’s URL.

If you are using a custom domain with MaxCDN like I am, then putting that custom domain into whatsmydns should yield the actual “business” domain in the results list. In other words, if you search for cdn1.yourcustomdomain.com and you get responses of cdn1.yourbusiness.netdna-cdn.com, you’ve got it set up correctly.

What’s your site’s IP? In a command prompt / Terminal window, ping yoursite.com to get its IP address.

Is your CDN URL responding? In a command prompt / Terminal window, ping cdn1.yourcustomdomain.com and see if you get anything. If it can’t find your host, this could indicate an error with your CNAME record with your new hosting service.

Are you seeing stale or current stuff in your browser? I use Chrome incognito because each window starts with a fresh cache and no cookies. CTRL SHIFT N opens up a new incognito window.

You may also want to flush your DNS in between tests. ipconfig /flushdns does this in Windows.

If all else fails, email MaxCDN’s customer support. Even on a US holiday, I got a response within 20 minutes and they helped me get things working again.

 

Tutorial: Easily move a WordPress site to a new host with minimal downtime using UpdraftPlus

Moving a WordPress site from one host to another with minimal of downtime doesn’t have to be a huge hassle, and it’s easy to do it yourself even if you aren’t a web developer. Here is the process I use to move a WordPress site to a new host, with about 10 minutes or less of actual downtime (and because of caching, many visitors during the migration may not even see the outage).

I like this method because:

  • It’s easy
  • You don’t have to mess around in MySQL
  • It’s free
  • It’s maybe 10 minutes of downtime for your site, depending how fast you can upload your backup and how much you have to do to get your caching plugin/CDN (if you have them) on board with the new IP address

Before you begin, make sure you have:

  • A WordPress site on your current host
  • Access to your new hosting account (preferably with CPanel and phpMyAdmin to get the most out of this guide)
  • Access to your domain’s DNS records (yoursite.com may be registered with your current host, or a separate registrar)
  • Nameservers for your new host (they usually look like ns1.newhost.com)
  • FTP access to old host and new host via your choice of FTP software (I use Filezilla) *optional* – you can do the same stuff through your host’s CPanel File Manager if they have it
  • About an hour of time to dedicate to reading this guide and the actual migration

Step 1: Install UpdraftPlus plugin on your site

updraft_plus

Log into your WordPress dashboard (http://yoursite.com/wp-admin) and install the free UpdraftPlus plugin.

This plugin is awesome and I recommend it for use outside of just moving your WordPress site to a new host. Here’s why:

  • The backup files do actually work (this plugin has saved my ass a couple times now)
  • You can use it to make a manual backup of your site at any time
  • You can set it up to create automatic backups and put them on the cloud storage service of your choice (personally, I back up to Google Drive)

Step 2: Use UpdraftPlus to make a backup of your site

Use Backup Now to start the backup process.

move_wordpress_site_new_host_updraft_step1

I like to do this right before I’m ready to start the migration process, so the backups are as fresh as can be.

Step 3: Download your backup files

Go back to your UpdraftPlus plugin page and go to the Existing Backups tab. Find today’s date and click each of the buttons (database, plugins, themes, uploads, others).

move_wp_to_new_host_easy_no_downtime_save_backups_updraft

Updraft will prepare each backup file for you (there are 5 total). Wait for Updraft to prepare the files, then click Download to your computer for each one.

move_wp_new_host_easy_download_each_backup_file

You’ll get 5 compressed files:

save_backup_files

Step 4: Set up an account with your new host and install WordPress there

If you haven’t done so yet, sign up for an account at your new host.

I’m digging SiteGround as my host these days, and I’ve already moved several of my sites to SiteGround (including my top money-making blog).

I use the StartUp package for my up-and-coming sites, and the GoGeek plan for my top performers. You can upgrade your plan at any time as a site grows. I especially like the GoGeek plan because they throw in SSL for free (or at least they did for my first year) and because it has a separate staging environment for testing stuff on a copy of the site before pushing it live.

Next, install WordPress on your new host. Many modern hosts (including SiteGround and BlueHost) have an easy one-click install for WordPress nowadays – look in the CPanel or just the dashboard in general once you’re logged in.

Don’t worry about picking a login/password you want to use in the long run, your Updraft backups will replace whatever you choose during setup with whatever your existing site already has. Do write down whatever name/password you choose here, you’ll need it to access your new WP install until you overwrite it with your backups.

successful_wp_install

It’ll probably tell you the installation was successful and you can go see it at the following url, but that link won’t work because you haven’t updated your domain’s nameservers yet.

Step 5: Change your domain’s nameservers

I do this in dynadot.com’s domain manager because that’s where my domain is managed, but your domain may be attached to your old hosting. In any case, change its two nameservers from ns1.oldhost.com and ns2.oldhost.com to ns1.newhost.com and ns2.newhost.com (or similar).

It should propagate fairly quickly (check it here: https://www.whatsmydns.net/) but it may take a while to see the change on your machine. One way to speed it up (on Windows, anyway) is to open a command prompt (cmd) and type ipconfig /flushdns.

Load your site again (in an Incognito window in Chrome or after clearing browser cache) and you should now see your new WP install.

Step 6: Install UpdraftPlus on your new blog and restore backups

Now that you have dashboard access to your new WordPress installation, install UpdraftPlus and click Restore.

updraftplus_restore

Drag your 5 files here and wait for them to upload.

updraft_restoring_backups

When those are done uploading, click Restore.

restore

Follow the prompts until you’re force to re-log in to your site. It should now look exactly like it used to on your old host, but you can confirm that it’s actually on your new host by pinging it in a command prompt or Terminal window (ping yoursite.com). If the IP address returned matches your new host’s, you’re good to go.

Extra step for CDN users:

I’m on MaxCDN, but regardless of what CDN you use (if you use one) there will probably be some additional setup steps to make sure your existing CDN account references your new IP and host.

I went through this process for MaxCDN and documented it here: http://www.tilcode.com/what-to-change-in-maxcdn-settings-after-you-change-web-hosts/

Step 7: You may need to do some other setup on your new host

Leave your old host active for a little while while you confirm everything’s working on your new site over the next couple days.

You may need to move the following separately:

  • Email accounts. If you had email accounts set up at your old host, take note that they don’t come with the Updraft migration and you’ll have to recreate them (and redo any redirects) on your new host.
  • Favicon: if your site had a custom favicon sitting in your site’s root directory, you might need to copy it from your old host and upload it to your new one
  • Google Analytics .html file: If you put any .html files for analytics tracking (Google Analytics is the one I always have to move manually) in your old site’s root folder, you will have to copy them to your new host
  • Robots.txt and anything else sitting in root (this will vary by site)
  • Images or other media in dedicated folders: Anything that’s part of your site but not part of WordPress will have to be manually moved. For me, this is sometimes a site logo or images on the site that I keep in a separate images folder, not uploaded to WordPress’s file manager.

If you’re afraid of losing anything off your old site, take the time now to download a copy of its entire directory off your old host, before you shut down your old hosting account. That way, if you find something missing later on, at least you can dig around the old files and maybe find it.

Blogging for bucks: Year 2 report – growing to greatness

This report is for Year 2 of my “blogging for bucks” endeavor in which I turn my love of research and writing into a profitable hobby. Read about the first year here (it’s a much longer and more detailed post than this one and covers all the “how to get started” stuff).

When I started blogging for affiliate bucks in summer 2013 I said all of the following:

  • “I’m starting too late”
  • “All the good ideas are taken”
  • “The Internet doesn’t need another blogger”
  • “The people who actually make money off this know tricks and secrets I’ll never know”
  • “I’m going to shill products I don’t give a shit about, and it’s going to be boring writing about them.”

Then I went ahead and did it anyway.

Turns out, none of the above was true. 2015 was an excellent year, bringing in more than twice 2014’s haul.

pile-of-candy

I made $12,600 this year from the Amazon Affiliate program and Google Adsense. Just like in 2014 (my first full year blogging), nearly half of that money was made in the last three months of the year (I love you, holiday shopping season). It wasn’t all for me, though: the IRS took about a quarter of it as their cut in tax season (ow).

2015 Amazon Earnings screenshot
(my earnings are the bottom right number)

2015_amazon_affiliate_earnings

I still firmly believe that this is a viable make-money-online strategy that anyone with the willingness to learn, research, and write can do. It’s not too late to start. It’s not even very labor intensive, as evidenced by this “coasting year” still making a decent profit. Most of the work you’ll do is upfront when you set up a new site and load it with your (well-written) content.

To be frank, I ran out of blogging steam for most of 2015. I didn’t write but a handful of articles and I didn’t start any new blogs until December. I was busy with my brand new job and some major life changes.

But if 2014 was the year of sowing, then 2015 was the year of reaping. As the blogs aged, more people found them – and they liked what they found.

2015 Blogging Year in Review

January 

I started my first full-time programming job in January 2015. This was a career change many years in the making and I didn’t have any spare brain cells for my blogs after work each day.

The sites coasted through January – I added no new content and I didn’t promote anything.

But sales didn’t drop with the conclusion of the US holiday season, curiously enough: January 2015 brought in almost as much ($739) as December 1014 ($819). My guess: people go buy themselves the stuff they didn’t find under the tree.

February and March

Sales dropped these months, reaching their lowest point of the year in March ($386). The holiday gravy train had to end somewhere, but I don’t think this was some flaw in my blogs – I think people just don’t buy that much in these months, or they don’t buy that much stuff in the niches my sites are in. Traffic more or less held steady, though, across all sites.

April, May, June, July, August, September

In April sales began to climb again, peaking in August with $1,166! I continued my do-nothing approach and added very little new content during these 6 months (I added 1-2 articles to each of my top-performing niche sites). I put some minimal effort into updating existing content to keep it fresh, though. On Gizmo Blog, this meant keeping up with manufacturer updates to products and industry news.

I think the moral of the story here is to not “give up” on a site. Leave it up, sites are cheap. Even my worst performing sites making $1-2 a month pays for their own domain by the end of the year, and all the while they’re aging like a fine wine (in Google’s eyes).

It’s okay to keep adding content at a snail’s pace, updating here and there, tweaking things along the way. Maybe if you want to make five or six digit earnings every month then you need to add content weekly or daily or whatever, but I definitely didn’t add anything meaningful to my already-established blogs and I was rewarded with a decent “side income” for my complete lack of effort.

October, November, December

Here they are: the three months that make 48% of the year’s income. November was the top-performing month, bringing in $2,076, but December was almost as good, bringing in just $30 less than November.

In honor of the shopping season, I did a few things to help buyers find me:

  • Added a 2015 holiday buyer’s guide and top sellers list for each niche site
  • Tweeted those lists (regularly) from each site’s associated Twitter account

Honestly, I’m amazed that a collection of neglected sites did so well. Imagine what they could’ve done if I’d been adding content all year long!

Year in summary (a pie chart!)

As the chart illustrates, Gizmo Blog accounts for a full 66% of my Amazon Affiliate earnings, and Craft Blog the next 20%. That means a full 86% of my earnings come from just two sites. Better diversifying my Amazon Affiliate income is one of my goals for 2016 – I don’t like having too many eggs in any one blog basket.

2015_amazon_affiliate_pie_chart

“Other” includes this blog, TILCode, which made a grand total of $7 off Amazon Affiliate earnings in 2015 :D (Thank you, whoever bought a book or two through this site.)

What about SEO techniques?!

I chatted with a number of people about blogging for side income in 2015 and the most common question they asked me was, “what kind of SEO do you do?”

The short answer is, I don’t do any.

I pick a product, research it (sometimes I buy it and use it myself), basically obsess over it, and tell people why it’s better than its competitors in a 1500-2500 word review.

I pick products I like myself – stuff I’d buy if I had unlimited funds, or stuff I already own. That’s it! People who want to know this information – people on the cusp of making a purchase – find me through Google and go through my site to Amazon to make their purchase, which I then get a cut of at no extra cost to the shopper.

There are a lot of techniques out there that promises results (paid links, WordPress plugins, content strategies, backlink strategies) and I can’t vouch for any of them. I mean, maybe there’s something out there that would turn my $2,000 months into $5,000 months, but at what cost? Hundreds of dollars in bought backlinks that Google would eventually sniff out and knock me down in the search results for? I’ve been content to build my sites myself and let the natural links come in at their own pace. YMMV.

2016 goals

  • Better diversification – 86% my Amazon Affiliate income comes from just two of my sites, so I want to get that down to 50% by EOY by growing income from other sites.
  • Alternative revenue source – virtually all of my passive online income comes from Amazon Affiliate program. It’s a great program but if it ever goes away, I’ll be pretty sad and won’t have anything to back it up.
  • $18k in earnings – that’d be nice!
  • Set up my blogs as a legal business entity, as per my financial adviser’s advice and because it’ll make tax season easier and more in line with what the IRS expects from a “side business”
  • Optimize sites – load times are bad (>6 seconds in some cases), they’re mostly on shared hosts, I get a lot of “Error Establishing Database Connection” problems (my VPS seems to get overloaded with one site in particular) – there’s just a lot of technical performance things that could be better
  • Actually add more new content to sites

And that’s it for 2015! Onwards to 2016, where I hope to do a better job of adding content and optimizing the sites for even better earnings.

Blogging for bucks: Year 1 report, mistakes made, lessons learned

Today I’m going to talk about my blogs and how they did this year. Since this is the first post in what I hope will become an annual (or even more regular) series, I’m going to share a long-winded history of my blogs, too, so that you can learn what I did and what my first year of blogging was like.

Just to set expectations, I didn’t make a mint in 2014. I made around $3500 this year off Amazon Affiliate and Google Adsense combined. Nearly half of that amount was made in the last three months of the year. To put the amount in perspective, we spent about that same amount on food in 2014 (for two adults). Or, it’s 10 payments on a car leased for $350 a month. Hey, I’ll take it! I know there are SEO wizards making enough to buy a new Lamborghini every day with their blogs, and that’s awesome, but that didn’t happen to me (yet :D).

However, everything I did is 100% doable by you if you’d like to try out blogging for a little side income!

Backstory

I’ve been putting content online since about 1997. My early efforts were what you’d expect from a 13 year old: a Tamagotchi fan site on Tripod, an AOL site dedicated to my dog, my Sailor Moon fan art. It was so rewarding to share stuff I cared about and find (small) audiences for it! I even made some really good friends through my sites and artwork.

Monetizing the content I made, however, never really occurred to me. I suppose I might have said, “Who would pay me for this? The Internet is full of free stuff!”

Sharing stuff online became nearly everyone’s hobby as the Internet’s popularity exploded and sharing stuff became easier and easier (and this is awesome – no matter how obscure a thing is, 99.9% of the time I can Google it and find someone talking about it).

Nonetheless, in 2013 I found inspiration in the works of bloggers like Young House Love (now retired?) and Smart Passive Income (written by a smart guy who makes a lot of money online by telling others how to make money online).

The formula couldn’t be simpler: build a site on a profitable topic, get traffic, earn money through affiliate sales. Sounds good to me!

First Blogs

April 2011: House Blog

A few years ago I bought a house that needed a lot of work done. I thought it would be fun to write about it and document the projects. (It was.) I bought a domain and put a WordPress blog on it.

House Blog was all over the place in terms of content, and it certainly wasn’t set up to make money. No ads or affiliate links. I was giving my posts “clever” titles, not SEO-friendly titles. And I certainly wasn’t writing with revenue in mind.

House Blog just sat there, getting a new post whenever I felt like it (maybe once a month) but it had a nice little trickle of 20-30 visitors a day.

The blog was 2 years old when I got interested in “passive income” from blogging. I discovered the Amazon Affiliate program through a couple other blogs I followed. I wanted to see if it would work for me, so I re-wrote a couple of my older House Blog articles (and wrote a few new ones) to include Amazon Affiliate links to appropriate products.

Within a week, I had my first sale! :O

I made about 30 cents off it (lol). But that was enough to convince me the formula worked and I could scale it from here.

I decided to start a new blog (and keep House Blog, of course). This one would be more focused. Rather than try to be an all-over-the-place home renovation blog (which was seriously hard to generate content for: how often do you replace a toilet?), I’d choose fewer topics and discuss them in excruciating detail (which I enjoy).

July 2013: Craft Blog

Actually, what happened was I started a whole bunch of blogs, all centered around different topics. (Sigh)

Craft Blog is the only one of that batch that has made any money to this day, so I’ll just talk about it. (Lesson: don’t run out and buy 5 domains the second you have a few ideas.)

Craft Blog’s domain was purchased in the first week of July 2013. I put some lorem ipsum content up while I enthusiastically spent an entire weekend customizing the theme (another mistake: it’s not necessary to spend hours – or days – customizing the theme until you have steady traffic). On the bright side, I learned a lot about WordPress and CSS during those early days of obsessing over the site’s design.

Setting up a new WordPress site, customizing it, and planning its articles gave me a much-needed creative outlet. I wasn’t challenged in my day job, so having this site to look forward to at the end of the day was very exciting and motivating.

A few days later, a visitor came to the site! Oh no! I had spent all my time customizing the site’s design. The only content on the site was placeholder junk!

I loved working on Craft Blog, so banging out a few pages of content that fit the site’s niche was easy and fun. For my “monetization” articles, I reviewed stuff I either owned or had used, and I made recommendations based on my own personal wishlist and research. (If there’s one thing I love more than buying new toys, it’s researching those new toys for weeks prior.) I added Amazon links to help visitors find the stuff I was talking about.

Of course, I had lots of ideas for articles that weren’t Amazon-oriented, and I wrote those, too. I love writing tutorials, so I also put some nice Photoshop and Etsy tutorials on the site. End result: the site looked better for the 1-2 visitors it was getting a day.

By August, I had 12 good articles. Some were long (1500 words), some shorter (500 words), but all were nice original content I wrote myself. There was very little traffic at this time, maybe 25 visitors the whole month.

I did a Pinterest blitz around this time, hoping to “go viral” there, but that never happened. I don’t enjoy Pinterest, so I was pretty quick to let that part of my blog marketing slide. In fact, I let most of my marketing efforts slide. I might be allergic to self-promotion. I found it too difficult to do a lot of the stuff the experts say you should do to market your blog, and I was content, at least for now, to just write useful content and post it on the site.

By September 2013, I was up to 22 articles on Craft Blog. I only had 147 visitors total that month (about 3 months into the site’s life), and no sales, but I was enjoying the project so much I just kept going through October when I decided to start a new site and let Craft Blog coast for a while.

October 2013: Disney Ride Blog

In September 2013 I went to Disneyland. It was great! I caught a bad cold when I returned home, though, and had nothing to do but lay in bed and think about how much fun I had at Disneyland. I decided to start a blog about my favorite Disney ride. It would just be a nice place to collect all the history, legends, and secrets of the ride in one place. I bought a domain and wrote a couple short articles about the ride and what I knew about it.

Within a few days, the site was getting traffic.

Whoa, what?!

In its first 30 days, Disney Ride Blog got more traffic than Craft Blog had had in its entire 4-month life. This inspired an obsession with the site, which I worked on regularly for the next two months. By January 2014, the site had its first 100 visitor day!

The site was a traffic monster, but it was making almost no money (literally pennies a day, if anything).

I think the site stood out because of a lack of competition. Lesson: if no one else is in a niche, maybe that’s because there’s no money in it? :D

Unfortunately, while Disney Ride Blog was my strongest traffic site almost until the end of 2014, it has barely made a dollar. I love the site and it’s easily one of my favorite hobbies, but it just eats up bandwidth and brings in nothing. I’ve experimented with ad placement, Affiliate links, etc, but it’s stubbornly unprofitable.

Fortunately, Craft Blog got a few clicks and sales before the end of 2013, which gave me some ideas as to what to do next for making a “money” blog.

2013 blog income: $17.16

Yup, $17.16 for three sites over the whole year. Rollin’ in it.

2014 in Review

January 2014: Gizmo Blog

Six months into this “blogging for bucks” endeavor I had been writing content for three blogs fairly regularly and seen traffic grow accordingly, but between Adsense and Amazon Affiliate I had made a grand total of about $17 for the entire year. I think a lot of people would have called it quits at this point, because that’s a pretty embarrassing return on investment.

Not me! Haha, I bought a new domain in January, this one for a particular category of “smart home” gadgets I was very interested in.

Gizmo Blog would be the culmination of all my learning thus far: niche topic that not many people were covering in great detail yet with a focus on customers on the verge of making a purchase (and Amazon Affiliate links to guide them to the product’s Amazon page).

With one comparison article (including a comparison chart and about 1500 words of original written content), the site had its first visitors from Google and its first Amazon link click within 7 days (!!!).

January 2014 blog income: $8.66

Hey, that’s nearly half of what I made over half of last year!

I added a few more articles to Gizmo Blog cover the basics and then decided to let it sit. In the meantime, I did some footwork: I went to hardware stores and even an open house to see the products in person, since it would be impractical for me to install all of them into my own home. This helped me write smarter “hands on” reviews. It helps that I genuinely find the technology interesting – I can’t imagine writing a site like this without loving the thing you’re writing about.

February 2014: Double digit earnings!

February went better: as traffic grew (1660 visitors across all sites!), so did clicks to Amazon. I had my first double-digit earnings month. I didn’t do anything special or get any links, I just added a few new (long) articles to each site. From humble beginnings…

February 2014 blog income: $35.63

May 2014: First $100 month

Three important things happened between February and May 2014:

Thing 1: One of the manufacturers of a product I reviewed on Gizmo Blog tweeted a link to the review! This brought in a surge of traffic (58 in a day, woohoo!) and seemed to legitimize the site a bit in the eyes of Google because from this point on, traffic kept climbing – sometimes doubling with each passing month.

Thing 2: In May 2014, someone linked to one of my reviews on a forum, which is like a gift that just keeps giving because it not only sends regular traffic, it counts as a quality backlink in Google’s ranking algorithm.

Thing 3: I quit my day job and focused all my time on blogging and growing my web developer skills.

May 2014 blog income: $115.15

During the spring I also made a better effort at marketing the blogs. I created a Facebook, Twitter, Pinterest, and Google+ presence for all of them and re-tweeted / re-posted content to them regularly (1-2 times a week) for a while.

I don’t know how to make something “go viral” and I don’t enjoy spending a lot of time on social media doing what feels like an elaborate “look at me” routine, so again, I let marketing efforts fade out after a while.

Traffic grew steadily across all sites, which was great. Disney Ride Blog remained by far my strongest traffic-puller, but my weakest earner.

June 2014: My host complains

My long-time web host (Lunarpages, since 2005!) served me with a ticket and a complaint that my sites were consuming too many resources. This started my mad scramble to optimize my sites. Ultimately, I added W3 Total Cache to my sites which took a load off the servers (for a while – see December 2014). Without caching, each of my sites was re-loading all of its resources every time users navigated around the site.

At the time, I was surprised that WordPress didn’t come with optimizations built-in. It’s up to the user to add things like caching, lazy loading of images, minification of CSS and JS, automatic backups, and security measures like limits to login attempts.

Lesson: optimization becomes very important once your site(s) are bringing in around 50+ visitors a day.

October 2014: Holiday season begins

The summer was very good by my beginner standards: about $140 a month on average for June, July, August, and September. At least, until October raised the bar.

I added zero content between July and September, thanks to attending the full time Code Fellows Dev Accelerator and having negative free time for blogging.

Yet traffic continued to grow! The best part was, for the first time, my blogs were truly earning “passively” since I had done absolutely nothing on them. October really turned things around – nearly $600 in earnings, plus I’d done nothing on the sites in months.

October 2014 blog income: $594.19!

This sudden uptick in earnings inspired me to put those new web dev skills to use and optimize all the sites for mobile, which may explain at least some of the traffic increase that I saw in November and December. Disney Ride Blog and Gizmo Blog had been virtually unusable on mobile, now they work great on phones and tablets.

December 2014: Shopping season ends, host complains again

If October was great, then November was spectacular: $870 in earnings! I’ve read from other bloggers that the last three months of the year are the best for Amazon Affiliates and ecommerce sites, and I believe it. What a great way to end the year!

rue Color Image

However, I expect the earnings to fall back to their summer levels (if not lower) as soon as Christmas comes and goes.

Alas, my host complained again in December. They don’t have a problem with my bandwidth usage (that’s “unlimited”!) but they do have a problem with CPU resources being used, some of which are used every time someone visits one of my sites. With average daily visitors now around 3,000 for my sites combined, I was hitting it too hard for them.

Unfortunately, traffic peaked at the same time some bot network attacked many of my sites (or maybe the bots were always there, but weren’t causing any trouble until traffic reached a certain point). From the logs, it looks like at least one bot was trying to get in through wp-admin, and another seemed to be hitting the comments functionality of several of my sites.

I played wack-a-mole for 5 days trying to solve or at lease reduce the effect of the various attacks across my many sites. I banned IPs, tweaked cache settings on my WordPress sites, added login attempt limiting plugins, turned off plugins, turned on and off themes… no one thing was causing the high CPU usage. Lunarpages doesn’t offer great tools for debugging and the update time is slow – as much as a day before I can see if what I did had any effect.

It was time for something drastic.

I moved Gizmo Blog off Lunarpages and onto DigitalOcean, a scalable VPS that is probably more appropriate for a site that continues to grow in traffic every month.

digitalocean

I’ve been with DigitalOcean less than a week and while the process of moving the site over and locking it down security-wise took some time, the end result seems to be an immediate reduction in traffic to my stressed-out primary host. You can see the drop in traffic on December 16th- that’s the day Gizmo Blog left Lunarpages for greener pastures.

gizmo_blog_moved

It was probably inevitable, because this is how Gizmo Blog’s traffic is trending:

gizmo_blog_traffic

To Lunarpages‘s credit, they did not kick me out or shut down my blogs in response to my unintentionally high CPU use. In researching this CPU problem over the past week, I’ve found many bloggers who did get shut down by their hosts for this sort of problem.

To address my WordPress CPU usage problems, I used a combination of:

  • W3 Total Cache and WP Super Cache (I think I like Super Cache better so far)
  • BJ Lazy Load so image-heavy posts load images on an as-needed basis
  • WP smush.it for negligible image optimization (I already use save for web on all images, your mileage may vary)
  • Simple Firewall for better login security and other firewall features
  • WP Clone for moving Gizmo Blog to a new host with minimal pain
  • WP Optimize for clearing unused revisions out of the database
  • Disable Comments an emergency measure I deployed to help reduce the load on the server
  • WP Maintenance Mode another emergency measure I used to shut down Disney Ride Blog while I experiencing CPU overages (unprofitable sites are the first to go in times of need :P)
  • GTMetrix and its WordPress plugin for monitoring site load time and performance
  • CloudFlare free version on my highest traffic sites (how CloudFlare works)

It’s too soon to say if this is the end of the CPU overages saga, and I suspect it’s not. I’m currently using three hosts for my sites (Lunarpages, BlueHost, and DigitalOcean), all of which have been satisfactory in terms of what I expect from them (in other words, I don’t expect world class speed and performance out of a host charging me $5/month for shared hosting). Craft Blog will most likely be moving onto its own VPS in the near future and I may try someone other than DigitalOcean and see how they compare.

What’s next?

I don’t think I’ll start any new blogs this year. Maintaining individual blogs has become time consuming. Every update to WordPress has to be deployed individually to each site, and problems like CPU overages and hacker attacks often have to be debugged on all sites.

As for the ones I have, I plan to add content, continue to optimize the sites, move them to more suitable hosts, and just keep ’em growing. I’m eager to see where earnings fall to in the first months of the year, and whether traffic can continue to grow on its own during times where I don’t add content regularly.

If you (TILCode reader!) have enjoyed this break from coding talk, let me know in the comments and I’ll share blog updates more regularly. I’m not sure the web needs yet another site on how to blog for bucks, but I could be wrong. :) Hope you enjoyed this massive first installment!