Once you have been blogging for a while, you will have accumulated a fairly long list of blog posts. Hopefully you've been mixing your topics. If so, you might have a mix of localism posts, market reports, and answering frequently asked questions for your audience.
How well are your posts performing?
If you monitor your stats page after you post, you'll be able to know. The stats page will show you how many views, comments, and likes on your posts. Ideally Google will pick up on some of your posts as well and over time some of these posts may take off more than others. What were the topics that did better than others? That would be a useful thing to know.
Sorting Your Stats
If you click a column heading on your stats page, it will sort the page by that metric. So if you click the views heading, your posts with the most views will come to the top. How many views do you have on your most viewed post? (leave a comment below and let us know!)
Exporting Allows a More Comprehensive Review
If you are a data geek like me, you really love to dive in and analyze data to learn from it. Exporting your data from ActiveRain is easy to do. On your stats page, at the top right of the chart, you can click on the "Export Results" link and download a CSV file. (CSV stands for "comma separated values" which basically means it is a format that you can open in your spreadsheet app like Excel).
Formatting Your Raw File to Make It Readable
Once you open your file in your spreadsheet, it will at first be hard to read. You can adjust your column widths as necessary to be able to read the data. I also often adjust the font and font size settings.
Excel and other spreadsheet apps have a tool to take a block of data like we see above and make it easier to review by commands like sorting and filtering. The command in Excel is "Create Table". If your any cell inside the data is selected, you can then press Ctrl+T (for Windows, Command+T for Mac) to convert to a table. In addition, tables by default are styled with alternate row shading which makes the data more readable as well.
A Comma Caveat
As mentioned above, a CSV export type is where excel splits the received data into cells based on the commas in the received data. One problem with this file type becomes obvious if you have commas in your data. Take a post title that says Themes, Signatures, and Profile Pages (like you see in my post below)
Excel will split this title up into separate cells instead of keeping the title in a single cell. This is a pretty easy fix once you have opened the Excel file. I'll show you how to fix the row of data in the video at the bottom of this post.
What You Can Do With Tables
By clicking the button in the header cell of any column, you can sort the data by that column (such as to show the posts with the most views). You can also create a filtered list where it shows only posts with a certain keyword. See my example below, filtered by the word "profile"
Conditional Formatting Helps Visualize Magnitude
Seeing a large set of numbers can be hard sometimes to comprehend magnitude of scale. Excel has conditional formatting preset rules to help quickly visualize data sets. In the below snapshot, I did conditional formatting on my views column for my posts. The "Data Bars" rule will show bars of varying lengths to illustrate the value. The longer bars quickly pop out as posts to take note of. You can also use the "Color Scales" formatting. Magnitude is represented by differing colors (in this case, red for lower performing and green for higher performing)
Saving Your File
Remember how we started off with a CSV file. Since we have done all this work in formatting our file, we'll need to use the "Save As" command and change the file type from CSV to Excel (.xlsx) format. This way all of our formatting will be retained (which a CSV type cannot store).
Making the URLs Clickable
Now that you have a master list of all your posts, it would be helpful to be able to click on the URL and jump straight to the post. Initially, the URL cells are not clickable. If you double click any URL cell to go into "editing mode" and then select the URL text, you can jump over to a browser window and paste to go to the page. Not ideal, but that gets the job done.
For the more advanced Excel geeks out there, you can insert a new column and use the hyperlink formula in this format
=HYPERLINK(B2, "Link")
This will make every row in the whole list show a link that you can click to get to the post. You can use the text "Link" to make the column nice and narrow (compared to seeing the long URL). Hide the B column afterwards, leaving just the C column visible.
And if there any super ActiveRain Data geeks (raises hand), you can go further and use the formula that shows the specific post ID number as the link text
=HYPERLINK(B2,TEXTAFTER(TEXTBEFORE(B2,"/",5),"/",4))
So instead of saying "Link" it will show 5792456 as an example link text. This formula parses the URL text between the 4th and 5th slash characters to get the post number. Okay, there's probably about 0.3% of you reading this that will go that far, but I thought it was a neat twist. 🤣 My first post on the site in 2010 was 1,796,177 and my last one was 5,792,456. That's nearly 4 million posts (by us) added to the site in those 13 years! #datageek
What We Have Learned Today
Ok, so I took us beyond the case of the ActiveRain stats to dive into a little more Excel training. But it was a good case study. You learned about some neat features like Tables and Conditional formatting.
Instead of stats data, perhaps you could imagine downloading the CSV transactions for the past year from your checking account. You could turn that into a table. You can then sort and filter just in the same way. For example, you could filter all your transactions to show just a particular utility company or other payee.
The How To Video
In case you learn better by watching than reading, or if any of the above wasn't clear enough, here's the how to video.
(pro tip: after video starts, press F key to view in full screen)
Comments (26)Subscribe to CommentsComment