Have you ever poured your heart and soul into a blog post only to see it completely tank?
It doesn't generate leads and barely sees any views, while that silly GIFs article that took you half the time and energy goes viral. Doesn't that drive you crazy?
To gain a better understanding of their audience's interests -- and ensure that their content is actually resonating -- smart marketers are turning to the performance numbers of existing content to inform their strategy.
Numbers like post views and leads generated from past blog articles can reveal a great deal about which topics are hot ... and which topics simply aren't worth the energy.
To help you get started with analyzing the performance of your blog content, we've come up with a step-by-step guide. If you want to follow along with the screenshots below, click here to download the Excel spreadsheet. (When you're done, your Excel spreadsheet should look like this -- but don't cheat!)
How to Use Excel to Run a Blog Content Analysis
Step 1: Export your data into Excel.
Depending on the software platform you use for your blog, you should have a way to export some of the details of your content. At the very least, this will normally include views, the URL, author, and publish date. In addition to this, most lead generation software will allow you to export lead data just the same.
If you are a HubSpot customer, we've outlined how to export data in your HubSpot software.
To start, you'll go to your Blog Dashboard and click on the Published link on the upper right side. Then, you'll scroll to the bottom and click on Export. This will export a list of all your blog posts.
In order to pull in your blog post leads, you'll need to run an Attribution Report based on Referrer and Last Interaction. This post can walk you through how to do that.
After you run the report, you will see a link to export it on the right.
Once you export both views, you'll need to combine them into one spreadsheet. You will see on the tabs at the bottom that one says "Blog Post Export" and the other says "Attribution Report Export."
Here is the "Blog Post Export" tab:
Here is the "Attribution Report Export" tab:
Step 2: Clean up the data.
Before you begin your analysis, you'll need to start by cleaning up the data. (You may not have to do this every time, but we'll walk through what you need to do ... just in case.)
In the screenshots above, you'll notice that the URLs look different -- one starts with http://blog.biglytics.com/ and the other starts with the rest of the URL string. If you are trying to combine data from both of these tabs, Excel will not be able to recognize that they are part of the same post. Let's walk through how to fix this.
On the Attribution Report Export tab, you'll highlight column A. Once highlighted, you'll click on Ctrl-F or Command-F to make the "Find" box appear. Under "Find what:" you'll type in "http://blog.biglytics.com/." (Make sure to include every character including the final /.)
From here, you'll need to click on "Replace." You will then see a new line pop up that says, "Replace with:." You'll want to leave that box empty, and click on "Replace All."
You will see the "http://blog.biglytics.com/" disappear in your first column, and Excel will tell you how many times it replaced it. At this point, your URLs on both tabs should match.
Sometimes when you export dates it will show you the date and time that an action took place. Many times we do not want to include the time information -- just the date. Additionally, if some columns have the date with the time and others don't and you need to match them to each other, Excel will not recognize that they are the same.
In this case, you have to remove the time from the date cell.
To start, you'll highlight the column that has the date and time. After that, you'll click on "Data," then "Text to Columns." The following screen will pop-up, and you'll need to select "Delimited" and click "Next >."
The next screen will ask you to choose how you want the cells broken up. In this case, you will need to uncheck "Tab" and check "Space." This will tell Excel that every time there is a space in the cell, you should add it to a new cell. In the screen below, the vertical lines demonstrate how the cell will be broken up. To finalize this change, you'll click "Finish."
You will see below that column I is now broken up into columns I, J, and K. However, column I with the date now has 0:00 instead of the exact times.
To fix this, you will need to highlight the column with the date and 0:00. Under "Home," you will see a drop-down that says "General." Under that drop-down you'll select "Date" instead of "General."
Your dates will now appear as dates, and the 0:00 will be removed. At this point, all of the dates in your spreadsheet should look the same.
In this example we used the "Text to Column" feature with the last column in the spreadsheet. If you did this same action in an earlier column -- such as column A -- you would need to add additional columns. If you skip this step, Excel will separate the singular cell into three and override other data you have.
Step 3: Find out how many leads were generated by each URL.
Now that your data is clean, you can begin your analysis. To get started, you'll need to combine the two tabs so that you have your lead numbers in the "Blog Post Export" tab.
To do this, insert a column between "Views" and "CTA Clicks" (in column E below), and title it "Leads." Now you can use the VLOOKUP formula to pull the lead counts from the other tab. Instead of memorizing the VLOOKUP function, you can use the "Formula Builder" -- indicated by the arrow below.
When the "Formula Builder" pops up, you'll then have an opportunity to search for the function you want to use. In this case, it would be VLOOKUP. The "Formula Builder" will then walk you through what belongs in each part of the formula.
In this case, your look-up value would be the URL. (The look-up value is the piece of data that you have in both spreadsheets that you can use to pull in additional data.)
Next, you'll click on the H2 cell.
Your "table array" is the table where you are pulling the additional data from. You'll click on the "Attribution Report Export" tab and highlight columns A and B.
Your "column index number" is the column where you want to pull data back from. In order to pull the number of leads from column two, you'll simply need to type the number "2."
For the "range lookup," put "FALSE." This means that you want to pull back an exact match (which is your URL). And when you are done, you'll hit "Enter."
At this point, you should see the number of leads for the first blog post, http://blog.biglytics.com/what-is-vlookup.
Finally, you'll need to copy the formula down to the rest of the posts by clicking on the bottom right corner of the first cell and dragging it to the bottom of cell E31.
Step 4: Find out how many leads were generated each month.
Once you have the number of leads generated from each blog post, you'll want to find out how many leads were generated each month. To do this, you'll need to create a "PivotTable."
To start, you'll highlight all of your data. Under the "Data" tab, click on "PivotTable." (You may need to place it in a "New Worksheet." If so, you'll simply select that option and click "OK.")
If you are using a Mac, Excel will pre-populate some of the data in the "PivotTable" for you. If you are using a PC, Excel will not do this. For all those Mac users out there, you'll need to remove the pre-populated data by unchecking the field names.
The next step? Deciding what data to show.
Because you want to see the number of leads generated per month, you'll need to drag "Publish" into your "Row Labels." Once completed, you'll drag "Leads" into the "Values." (By default it may count the number of times a lead appears instead of summing it up.)
From here, you'll need to click on the "i" with the circle around it under "Values" in the "PivotTable Builder." Then, scroll up to find "Sum," and click on "OK." This will Sum up the number of leads associated with each URL.
These actions will give you the number of leads associated with each day, but you'll want to look at this data by month instead of by day.
To fix this, you'll right click on any date in your table, select "Group and Outline," and choose "Group."
At this point, you'll be able to choose how to organize your data -- "Days" will be selected, but you can change that to "Months," and select "OK."
Once you've completed those steps, you should have a table that shows the number of leads generated in each month.
Step 5: Find out how many leads an author generated.
With the number of leads generated each month established, you'll want to focus on uncovering how many leads a specific author generated. This is a helpful number to determine, as it can be used to gauge the performance of a guest blogger or new team member.
To do this, you'll drag the Field name "Author" into the "Report Filter" tab in the "PivotTable Builder."
In the actual table, you'll click on the drop-down next to "All." This will display a list of all your authors.
From here, you'll select the author whose performance you want to analyze. For the sake of this example, we'll choose Harry Potter (because who wouldn't want to know how many leads he generated?)
Once you click on his name, you'll see a list of the leads that his blog posts generated. (You can do this for any author in the filter.)
Step 6: Find out how many leads were generated by specific types of posts.
What if you want to figure out which topics performs well or which keywords generate the most leads?
It's easy.
Let's say you want to see how many leads were generated by posts beginning with "How to."
You'll simply open your "Formula Builder" and search for the SUMIF formula. Here are the details you'll need to fill in:
- For range, you'll highlight the cells in the URL column, H2:H31 -- this will tell the formula that you want to analyze how many times "how-to" appears in the URL.
- For criteria, use "*how-to*" -- The asterisks on either side of how-to will indicate that text can appear before or after the "how-to" in the URL.
- Then, you'll need to tell Excel what to sum up if the criteria matches. In this case you'll want to sum up column E (which has the leads numbers).
- When you are done, click "Enter."
You'll then see the number of leads generated by "How to" posts in the cell you selected.
Step 7: Find out which month you had the most top posts in.
But wait, what about determining which month had the best performance in the last six months?
Again, it's quite easy.
To get started, you'll need to head back to "Sheet1" where the "PivotTable" is.
Once you're there, you'll remove the "Author" from the "Report" filter, and add in "Name" for the "Column Label." This will then show you what blog posts were published in January-June.
Table looks a little hard to read?
No problem.
All you'll need to do is drag and drop "Name" from "Row Labels" to "Column Labels" and "Publish" from "Column Labels" to "Row Labels."
If you make this small change, you'll find that your data will be much easier to read (and you will see the total leads generated in each month at the bottom of the table).
What other content analysis questions do you have? Let us know in the comment section below.
