Tag Archive: analytics

Finding Buried Treasure with Google Analytics

About once a month, I use Google Analytics reports to find out if any posts are getting a lot of hits from search engines. In Analytics, I click Content, Top Landing Pages, which pulls up this report:

Top Landing Pages Report

Top Landing Pages Report

What the heck?  #5 is a joke post about SQL Server 2010′s features being leaked.  That surprises me, so next, I want to find out why users are searching for this information. I click on that page name, then click Entrance Keywords:

Entrance Keywords Report

Entrance Keywords Report

Innnteresting.  I never would have expected that this post would remain popular long after I wrote it, since it was pretty much a throwaway joke.  I bet you’ve got pages like this too – you casually wrote a blog post to address one specific issue, didn’t really think anything of it at the time, and now there’s hits coming that you didn’t expect.

Now that I know the post has gained popularity, I can make it even more successful.

Helping Search Engines Find Your Buried Treasure

We’re going to make a series of changes in order to improve our search engine rankings. Ideally, we’d be doing this legwork for every post we write, but we don’t usually have that much time, do we? After all, we’ve got day jobs.

First, edit the post’s keywords to include more of the keywords that users were searching for. This will make it rise higher in search engine rankings. If you’re using the excellent All-in-One SEO Plugin for WordPress, edit the post and scroll all the way to the bottom of the page. Fill in each of the All-in-One fields, which are empty by default:

All in One SEO Plugin for WordPress

All in One SEO Plugin for WordPress

The fields are:

  • Title – defaults to empty, which just uses the original blog post title. If you wrote a funny post title that doesn’t really match up to the content, you can override it here. This override only affects how the search engines see your content, so you can be less funny and more accurate here.
  • Keywords – use the search keywords end users used to find your page.
  • Description – a one or two sentence description of the page that will show up as a search engine summary. WordPress uses the first sentence of your blog by default, but you might want to write something more descriptive.  It’ll show up in the Google results like this:
Google Search Results with Tweaked Meta Tags

Google Search Results with Tweaked Meta Tags

Presto! A nice, clean summary sentence instead of the article’s first sentence.

To be even more effective, we can tweak our post content too. I break up every few paragraphs of my posts with a header line. This uses the H3 tag in WordPress. Search engines give more relevance to your headers, so if you want to optimize your post for search engines, try to work keywords into your header lines.

Don’t go overboard. Readers can tell when you’re being slimy. It can be a tough line to walk, but at the end of the day you have to decide whether you want to be liked by search engines or by human beings. This post is a great example – my header lines here all talk about buried treasure, which frankly isn’t going to get me any search engine traffic at all. I’m fine with that, because this post isn’t the cornerstone of my blog. This is just for you, dear reader, and I’ll drive traffic to it another way.

Helping People Find Your Buried Treasure

Do a search in your own blog looking for related posts. You’ve probably written at least a couple of entries that somehow relate back to your newly popular page. Edit those pages, and add links directing people to your treasure.

If it’s a post you’re really proud of, link to it from every single page in your site. On BrentOzar.com, I have a Popular Articles section on the side of my site. It links to several of my most searched-for posts, plus a few posts that I just happen to think are spiffy.

Making the Buried Treasure More Valuable

If people are going wild and crazy over one of your buried treasures, put some elbow grease into it and polish it up. Add pictures, add sample code, and add most importantly, add links. If someone took the time to click on your link in a search result, they’ve probably got a lot of questions. Add a section at the end of the post called Related Reading, and include your own links plus the best you’ve found on the web. If you don’t know any offhand, take a few minutes to search. If you knew enough about the subject to write about it, then you probably know enough to pick out some good links in search that the reader would like. After all, they came to you for help – they may not know the good stuff from the bad right now.

Consider expanding your post to include more information covering the keywords that people were searching for. Let’s say you wrote about truncating SQL Server log files, for example, and people have started searching for how to truncate the log in SQL Server 2008 – which is no longer supported. You could add a paragraph or section to the post explaining that BACKUP LOG WITH TRUNCATE_ONLY no longer works, and include a copy of the error message users get when they try that stunt.

This is how my epic Twitter RT FAQ post got started. Originally, it was just a short post explaining what RT meant on Twitter because people kept asking me. Over time, I added more and more questions and answers because they were showing up in the search logs, and next thing you know, I’ve got a monster post going. The drawback is that my regular readers don’t see these added entries on such an old blog post, but frankly, I don’t write that material for my regular readers. I just keep adding to that post in order to help people who keep searching for Twitter information.

Rather than adding more and more to old posts, you can also write new posts to cover new information, and build links between your existing pages and your new ones. This comes back to the Related Reading topic we discussed earlier.

Sometimes, I find that I end up writing my Related Reading posts first! I start working on a post, and I realize I need some supporting material. That’s exactly what this post is doing here – it’s going to be link in tomorrow’s post. Stay tuned!

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

PASS Session on Analytics by Donald Farmer (#sqlpass)

George Box said, “Essentially, all models are wrong, but some are useful.”  Donald illustrated this point with a brief history lesson.  Early models of the universe said the sun revolved around the earth, and with that model, marvelous things were possible in architecture, celestial navigation and science.  Even though the model was wrong, it was still extremely useful.  Data modeling holds to that same statement: your models will never be right, but your key is to figure out which ones are useful enough.

Donald holds four things to be important: data mining must be actionable, innovative, trustworthy and seamless.  I think the innovative part is what keeps data mining in the back closet: data mining gives companies an edge, and when they get good at it, they don’t necessarily show it off for fear of losing their newfound competitive advantage.

He noted that last year, Microsoft’s BI lifecycle charts had steps for integration, reporting and analytics.  The new ones include steps for data entry, because that’s also a part of the process.  This points to how Excel is being integrated into the process because end users have data of their own that they weren’t necessarily integrating into the data mining lifecycle.  The IT team might not be able to integrate it in time, and the user wants to go go go.  The users want to take our historical data in the data warehouse, toss in some new data (like maybe about a new ad campaign) and make predictions about what’s going to happen.

He explained that clustering is the science of finding bad data by looking for outliers.  For example, your income data might fall into a model like this:

  • Young people have low income
  • Middle-aged people have high income
  • Older people have low income as they go into retirement

If Britney Spears comes in to apply for a loan, her data might be an outlier.  In data mining, you need to figure out if it’s valid data or not.  Malcolm Gladwell’s recent book Outliers wasn’t mentioned in the speech, but for a pop science version of data mining, take a look at it.

Donald showed how to use analytics to predict outliers in a web form and explained that you don’t have to hard-code the rules to find outliers.  For example, you don’t have to hard-code ages and income ranges.  That’s helpful in case your business changes dramatically – like if you merge with a bigger company with more customers, or move geographic ranges – you don’t have to recode your hard-coded business rules.  The database engine just uses the analytics to determine the rules.

In SSIS, you can add a data mining query step which is essentially making a prediction.  It calls out to an SSAS mining model to guess what your missing values are (or to create additional values) as part of the data flow.  You might have a sales promotion to be emailed to customers aged 25-35, but not all of your source data has the customer’s age.  A data mining task in SSIS could fill those gaps in your data.

He demoed the Excel Table Analysis Tools with the passenger manifest from the Titanic.  Very funny.  Later on, after the session, he happened to run across me and some other DBAs sitting in a hallway with our Macbooks open.  One question led to another, and next thing you know, he had his laptop open and we were data mining the Titanic survivors to see if older women were more likely to survive than younger women.  (As it turns out, the answer was no.)

Jeremiah Peschka of Facility9.com summed it up when he turned to me during the session and said that he wanted to go find out as much as he possibly could about data mining.  I’m fall into that same cluster, so to speak – data mining isn’t my “job” either, but it has so many cool benefits that I have to figure out how to integrate it into my workflow.  That was essentially the message in Donald Farmer’s session: predictive analytics works best when it’s an integral part of our daily jobs, built into the tools we use every day.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts