Build Your 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. So why should you build an Excel Pivot Table?

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.

Up next 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 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
Latest posts Revisiting GOOG, GE, NE, IYR from 2007 The Ye Old Blog List Motorola: Then and Now EWM Redux Testing for mean reversion with Python & developing simple VIX system - Talaikis unsorted - Tadas Talaikis Blog Steps to calculate centroids in cluster using K-means clustering algorithm - Data Science Central Basics of Statistical Mean Reversion Testing - QuantStart Algorithmic trading in less than 100 lines of Python code - O’Reilly Media Interpreting Machine Learning Models Microsoft the AI Powerhouse Investing in the S&P500 still beats AI Trading Microsoft makes a push to simplify machine learning | TechCrunch 10 Great Articles On Python Development — Hacker Noon Introduction to Keras Democratising Machine learning with H2O — Towards Data Science Getting started with Python datatable | Kaggle Phone Addiction Machine Learning Making Pesto Tastier 5 Dangerous Things You Should Let Your Kids Do The Pyschology of Writing Investing in 2019 and beyond TensorFlow and High Level APIs Driving Marketing Performance with H2O Driverless AI Machine Learning and Data Munging in H2O Driverless AI with datatable Making AI Happen Without Getting Fired Latest Musings from a Traveling Sales Engineer The Night before H2O World 2019 Why Forex Trading is Frustrating Functional Programming in Python Automatic Feature Engineering with Driverless AI Ray Dalio's Pure Alpha Fund