Build Blog Traffic Using Excel and Data Mining

This past Saturday, I posted about using data mining to look for patterns in your blog traffic. I wrote that you can use something called an Excel Pivot Chart report to get a better feel for how your readers are interacting with your site. What I should’ve written was that you can use an Excel Pivot Table report, the chart is optional. Why should you build an Excel Pivot Table? Simple, it’s how you start to build blog traffic!

Building a report is a great way to see trends in your readership and its easy to do. Once you see things happening on your site you can start asking questions like, what type of content drives the most traffic and on what days?” The Excel Pivot Table report won’t be able to answer that question but it can answer the question, what’s my busiest day, or what’s my optimal post per day quantity, and what’s my most popular category.”

Interested? Here’s how you do it in 5 easy steps.

Step 1 – Gather Data

If you use Google Adwords, or another site statisitc monitor, download your visitor data. You can choose what ever time frame you like, a good rule of thumb is about 2 months worth of data. You’ll need to get the number of hits and the date of the hits. Next, add this information to an Excel spreadsheet and add the following columns: Weekday, Number of Posts, and Category.

Step 2 – Transform the Data

Go back between the dates of your data download and fill in the columns for Weekday, make sure to match the date with the right weekday. Next, fill in the Number of Posts column with, you guessed it, the number of posts you did that day.

Step 3 – Create a Category

When you get to the step of data mining your traffic, you’ll want to know what content drives your traffic and on what days. Adding a key of categories will help you accomplish that. I entered the number 1” if the post that day was about Forex, 2″ if it was for stocks, etc. You get the point. If you posted more than one post on any given day and it was about more than one topic, you can add a second or third category column. You get as detailed as you want, its really up to you.

Step 4 – Build the Excel Pivot Table

Once you have all your information, you can build the table. Go to Data > Pivot Table and follow the instructions. You can place the table in your existing worksheet or a new one (I usually go for a new worksheet). Select your data range to include the Weekday, # of Visitors, Posts Per Day, and Category.

Once you did that you’ll see your new worksheet with a little floating menu system. You can drag and drop the fields into your new table. Drag the # of Visitors into the Drop Data Items area, drag the Weekday field into the Drop Column Fields area, drag Posts per Day to the Drop Row Fields area, and lastly drag the Category field to the Drop Page Fields area.

Step 5 – Format the Table

Use Excel’s auto format function in the Pivot Table wizard to select the style of table you’d like to see. When your all done, your spreadsheet should look something like this Blog Data example.

The first step before Data Mining your blog traffic is done! You can easily see what your busiest day of the week is, what’s a good # of posts per day (this is great from an efficiency standpoint), and what’s your most popular category. Just doing this simple Excel exercise can help you identify the ways to build more traffic to your website, which could yield financial benefits if you’re using Adsense or some other monetizing method.

As always, if you have a question please leave me a comment.

Search Engine Optimization (SEO) and Data Mining

I posted about the power of Data Mining when analyzing your blog’s traffic and how to maximize your Google Adword advertising relative to your Adsense earnings, but I forgot to mention one critical thing! Search Engine Optimization (SEO)!

SEO is just a process to organize your blog, or website, in such a way that you’ll end up at the top when ever an Internet user searches for something that is relative to your site. If you advertise your blog using a Pay Per Click method, like Google Adwords, then being ranked at the top of searches is really important as Ms. Danielle points out!

It won’t come as a shock to readers of this blog that Data Mining can really help with your SEO! Techniques like associative analysis and cluster data mining are great ways to discover who’s clicking what on your site. Associative analysis is used to estimate the probability of whether a person will purchase a product given that they own a particular product or group of products.

Cluster data mining, on the other hand, can identify the profile or group of customers that are associated with a particular type of Web site [via Data Mining and Business Productivity, by Stephan Kudyba]. These two techniques are critical if you want to maximize any e-business!

Now here’s the caveat, before you can start data mining your site, you spend a few months gathering website statistics and data. However, this doesn’t preclude your ability to start optimizing your website for better web searching. Here are a 5 tips that I’ve been using that have had a great traffic impact in my blog’s short life.

5 SEO Tips:

  1. Write valuable content or offer a valuable service. I can’t stress this enough;
  2. If you run a blog, spend considerable time selecting the right categories, those help search engines effectively index your site. Over time I’ve modified my category list to create relevant descriptions for my blog posts;
  3. Create a Crawl List and XML sitemap for Google. Doing this let’s the Google spider index your site easier and faster;
  4. Use Google Webmaster tool to manage your sitemap and clean out old URLs;
  5. Try to keep the size of your content on your site under 30k so your site can load in under 8 seconds for 56.6k modems. This helps your page load under 8 seconds.

Update: I now use a great Python package called PySEOAnalyzer to review how the content on my blog is working. It’s open source and can be downloaded here.

Blog Traffic Analysis Using Data Mining

Yes, you read correctly. You can data mine your blog’s traffic using a simple web statistics data collector like Google Adwords. I’m doing it right now for Neural Market Trends and I’m finding out some very interesting information.

I found out that:

  • Tuesday’s are my busiest days.
  • The optimal amount of posts per day should be 2.
  • The most popular post category happens to be my posts about Forex and YALE.

A lot of the above information I gleaned from a Google Adwords data dump that I put into an Excel Pivot Chart report. There’s no neural net magic behind that and you could do this quite easily yourself. Its when I built a neural net and mined the data that I discovered some unique relationships. These relationships should help me tweak my content to better serve my readers.

One of the things I learned from this little exercise is that I have a selective group of readers on Saturdays. Welcome! 🙂