Take My Money – Fix Your Mobile Website!

Friday, February 3rd, 2012

I was extremely lazy about holiday shopping this year.  Don’t get me wrong, I’m always lazy about holiday shopping but this year I had an unusual number of gifts to ship which meant that by the time I got around to thinking about shopping (December 17^th), I had very few purchasing options.

I became one of the hoards of shoppers who needed to do everything online. It’s almost cliché to point out that mobile shopping, or m-commerce, is becoming larger and larger. The research site eMarkerter has a great graph that shows exactly HOW large m-commerce is getting, projecting $11.6 billion in spend for 2012, up 73.1% over 2011.

So as I sat there eagerly ready to contribute my portion of those billions I ran into the same problem over and over again— subpar website experiences.

They were creative, they were good, but they were not optimal for viewing on a mobile device.

Since SEM is well, my job, I saw a great opportunity to dig in further to something that is already affecting search.

Google announced late last year that mobile site optimization would become a factor in quality scores for campaigns that were targeting mobile devices. It makes sense!  If a consumer is just going to get frustrated by your website anyway, why would Google reward you with a top spot in the paid search results?

Google put together the site www.howtogomo.com which has a lot of ways you can test your website for mobile usability, design tips, and even build your own. For now, I zeroed in on five of their ten mobile top practices!

1. 1.       Keep It Quick

This is for the impatient among us – which is all of us. Personally, if my little status bar takes more than three seconds I’m already hitting the back button and going to another site.

The Tip to Implement: compress images, use bullet points, and make it easy for your page to load

1. 2.       Simplify Navigation

Your mobile site is the place for your most succinct writing. Only put on the page what you really feel is important because here people will get lost in the details.

The Tip to Implement: Google’s recommendation is 7 links or fewer so make them count! Adding a search box for a complex site is ideal!

1. 3.       Be Thumb Friendly

Two words: Fat fingers. High on the list of annoying things about mobile sites is clicking a too small link and ending up somewhere I didn’t want to go because my fingers were too large!

Tip to Implement: Use large centered buttons and pad them!

1. 4.       Design for Visibility

SEER doesn’t give design tips. We won’t tell you what colors to use or which general images work best, but we can let you know that how you use your space is important for conversions. Do make use of size and color for the button that leads to your call to action. Don’t ask people to fill out long forms, mobile is not the place to get their entire consumer profile, and Do make sure that they are not having to surf it, scroll it, pause it, click it (Technologic!) in order to read your page. They may leave first!

Tip to Implement: Make it easy for your customers to read and eliminate the need to pinch and zoom to view content.

1. 5.       Make it Easy to Convert

This is important for all websites but where could it be more important than PPC? If someone comes to your site through a PPC ad tell them what you need them to do and allow them to complete this step in as few actions as possible. You can make it even easier on your customer by trying a function like click to call!

Tip to Implement: Make it as easy as possible for consumers to complete your desired call to action.

Now that you have some of our tips here is a great example from Einstein Healthcare Network in Philadelphia that has all of these tips in action:

• In just three lines of text you know the benefits of FastCare.
• There is a clear call to action (Call now for Hours Directions or services!)
• There is only one large button to press, and you know exactly where it will take you!

This page was created in less than 30 minutes using Google’s Mobile Landing Page site templates (http://www.google.com/sites/help/intl/en/mobile-landing-pages/mlpb.html) and has seen a 30% call rate since implementation.

With so many tips and tools it doesn’t have to be time intensive to revamp your mobile site for success. When you are all done fixing it up, learn about Creating a Mobile Profile in Google Analytics.

Posted in business thoughts, ecommerce, google, mobile, PPC, tools | No Comments »

Build Links with Tynt Publisher Tools

Monday, January 23rd, 2012

Here at SEER, we are fortunate to have some awesome clients who continually produce great content that is worthy of sharing. In a SEO dream world, every time someone copies a portion of this content, they’d attribute the source with a followed link back to our client’s site. However, this often isn’t the case. That’s where Tynt Publisher Tools come into play–to help capture some of these lost linking opportunities.

What is Tynt?

Tynt’s website states that for every user who clicks on a “share this” button, there are 90 users sharing through copy and paste. Tynt is a free tool that aims to leverage this fact by automatically inserting a link back to the site whenever a user copies and pastes an image or text.

How does it work?

You can install Tynt by adding a snippet of javascript to your site’s source code. If you’re working with a WordPress site, there are also two plugins available.  Once the code is in place, Tynt’s attribution link will appear whenever someone pastes your content into an email or adds it to a website. It is then up to the user to decide if they would like to keep this link in place, or easily strip it out by deleting it.

Results

Three months ago, we added Tynt to a client’s blog. Now whenever someone pastes their content, an added link reads “More from [Client Name]: URL.”  In the past 30 days alone (Dec. 24 – Jan. 22), Tynt monitored 778,154 total page views, 41,196 image copies, and 391,039 word copies, which lead to a whopping 4,614 new links.

There are some important things to note about this data. First, only 1.07% of image and text copies generated a link, meaning that 99% of users either pulled this link out before republishing online or shared the content through email.

Second, the majority of these links are nofollowed. In this case, three of the top sites where this content was shared were Facebook, Pinterest, and Twitter, and while it is great to have visibility on these platforms, the links pass little to no SEO value.

Lastly, very few of these links are high quality. Tynt separates the links into three categories: Gold for sites the search engines will find, Silver for sites behind logins or of low quality, and Bronze for sites that won’t impact SEO, but may refer traffic. Of the 4,614 links Tynt contributed, only 25 qualified as valuable Gold links. While this is small fraction of the links Tynt reports, I don’t know a single SEO who wouldn’t gladly take 25 easy links.

Additional Benefits

Tynt has its own analytics suite that provides some interesting metrics. For example, you can see which images and features generated the most shares, broken down by the top 28 images and top 20 posts. You can also discover which regions generated the most copy and pastes. This information can then be used to plan and create new content that caters to user preferences and these geographic areas.

In conclusion, Tynt is a low maintenance tool that quietly works to drive SEO value to your site. It requires little effort once it is set up. If you have a client with engaging content, we recommend testing Tynt for a month to see if it can benefit as an easy supplement to your current linkbuilding efforts.

If anyone has any experiences with Tynt or questions about the tool, feel free to leave a comment below or reach me on Twitter at @alliebrown89.

Huge thanks to our resident Analytics junkie @RachaelGerson for introducing me to this tool! 

Posted in linkbuilding, tools | 19 Comments »

10 Steps to Creating a WordPress Blog

Friday, January 6th, 2012

Hey there! My name’s Ryan and I am another new member to the SEER team. I’ve learned so much since starting at SEER but I thought for my first blog post I would cover something I have some background in, WordPress. I am helping to set up SEER’s upcoming blog on SEO for college students and I thought I would share the checklist I go through every time I start a new site on WordPress. If anyone’s New Years resolution was to finally start blogging then hopefully this post will give you a few ideas and tips or possibly save you a headache down the road.

The great thing about WordPress is that it really does simplify every step of creating and running a site. I was in a meeting with SEER’s developer Chris Le and half the time I thought he was speaking a foreign language when talking about advanced site design. I know some coding basics but thanks to WordPress and the plug-ins and themes created around it I’ve built more than a dozen sites ranging from your basic blog all the way to e-commerce and password protected membership sites. Although WordPress is a great CMS (Content Management System) that will simplify running your blog it doesn’t mean you won’t run into any issues along the way. For SEO purposes and your readers experience it’s important to get your initial setup as close to “optimal” as possible. So without further ado, here is my 10 step checklist to creating your WordPress blog.

NOTE: I am not going to cover how to install WordPress. If you have a hosting company they will almost always have tutorials or customer support on installation.

1.)  Layout: Draw up a layout of how you would like your site to look. Brainstorm the major categories you are going to cover. Research other blogs that you enjoy reading to help you get a feel for how you want your site to look. One big decision is whether you want to have more than one navigation bar.

•  Tim Ferriss’s site Four Hour Blog covers a wide array of topics, way too many to fit across his primary navigation bar. He uses his primary navbar to cover the basics and then a site wide sidebar to cover the topics he frequently writes about.

•  This navigation bar was taken from a successful affiliate site. They are using their targeted keywords across the navigation bar to maximize the link juice passed across each page. From an SEO perspective, their can be a benefit to limiting the links you have on your navigation bar and each page in general. This sites news section uses a sidebar but it is not used site wide.

2.) Choose a theme:  Many themes are available for free and some great ones are out there for purchase. If I had one piece of advice for choosing a theme it would be to keep it clean. You don’t need a million widgets, images or menu bars on your pages. If you are providing educational or entertaining information that is what the readers will be there for. If you are researching other WordPress blogs while planning your design then at the bottom you can often see what theme they are using.

• If you just want to use one of the many free ones available then in your WordPress dashboard go: Appearance —> Themes —> Install Themes —> Search

3.) Get a Logo/Header: One of the things I love the most about WordPress is outside of a theme, a logo is the only thing you have to spend extra time or money on. Unless you are great with photoshop, I recommend hiring someone from ELance or a forum to design a logo for you. I know I have gotten really nicelogos for as little as $20.00. Make sure you get the pixel specifications from your theme correct and that the logo will mesh with your sites theme and color scheme. Also tell your designer that you want at least one edit as part of the agreement.

4.) Set your permalinks structure: I’ve seen some debate on what the best permalink structure is but you definitely don’t want to leave it on the default. Below is my recommendation but feel free to do some more research.

• In your dashboard go to Settings->Permalinks. Change the URL to a custom structure, and use /%category/%postname%/ as this will allow for SEO friendly URLs like yoursite.com/SEO/links/ as opposed to: yoursite.com/?p=001.

• Change the Category Base to ./ then click save.  Once you do that, only the . will remain there.  If you leave it as is, your SEO category URL will be yoursite.com/seo/links/ when you want it to be yoursite.com/seo/

5.) Create your primary pages: Now that you’ve set your permalinks it’s time to decide on what will be included in your primary navigation bar and any site wide sidebars. This is a good time to breakout the Google keyword tool and run through some searches on what topics you will be covering the most. Test your assumptions and try to come up with at least five primary keywords for each category. Create these pages (even if you don’t have all your content ready) and organize your navigation bar. On your blog you might want a secondary navigation bar that expands on the topics you will write posts on.

I always recommend creating the following pages which are considered fundamental on a legal and ethical level:

• Privacy Policy: A privacy policy is where you disclose if you have cookies on your site, what your doing with e-mail you collect and more. Here is a free template but I recommend doing more research specific to your site.
• Terms & Conditions: This describes how users are allowed to use your website. These vary based on what you include on your site (software, free downloads, etc.) so research what other people in your industry, topic are using.
• About/Contact: It builds user trust to know who runs the site and if they have a way to get in touch with them. This doesn’t need to be long just a short bio and an e-mail or contact form will suffice.
• Disclosure: The FTC requires bloggers to disclose if they are getting paid or compensated in anyway for the content on their blog. Many use a disclosure page to go over what content is and is not sponsored or affiliate related.

6.) Set your home page: The WordPress default is to show your latest posts on the front page. If you’d like to have any kind of landing page for home and a blog section than do the following.

• Settings – > Reading: Choose a static page then select your desired home page from the drop down menu. Right below it you can select the page where you would like your blog posts to show up.

7.) Set your navigation menus and widgets: Now that you’ve created your pages you need them to show up on the page where you want. This is very specific to the theme you choose so I don’t want to show visuals that could end up being contradictory. That being said, the general steps are:

• Dashboard —> Appearance —> Menus – From there you can add pages to your top navigation bar and put them in the order you want.
• Dashboard —> Appearance —> Widgets – This is generally where you would build your sidebars. You can have more than one sidebar on a page and you can customize what pages they will show up on. As I mentioned previously many sites only have a side bar on their blog page.

8.) Plugin’s: WordPress has a huge variety of  plugins available to help you improve site functionality, SEO, collect e-mails and thousands of other things. The plugins I am recommending here are all free and can be found by going to Plugins —> Add New and searching for the name.

• XML Sitemap Generator – Generates an XML Sitemap for better indexing of your site by the SERPS. This also makes it much easier to get your site setup in webmaster tools (Don’t even consider not using this one)
• SEO Ultimate – Another plug-in you shouldn’t go without even if your theme has solid SEO capabilities. This is the best all around SEO tool for WordPress and its free. You can do everything from changing multiple title tags to editing your meta tag descriptions and adding pages to your robot.txt file.
• W3 Total Cache and W3 Super Cache – These can majorly improve your sites loading speed and greatly lower the risk of your site crashing from too much traffic.
• Broken Link Checker – Will notify you of broken links on your site.
• Permalink Trailing Slash Fixer – If you used my recommended permalinks structure this will add a trailing slash in the URLs if its missing.

9.) Google Analytics: For those that don’t know, Google Analytics (or any other analytics service you might use) allows you to see how many visitors come to your site, what pages they visit, how long they stay and much much more. You may want to do add your analytics code earlier in the process but if you have a theme or plugin that can post your analytics code site wide then it’s fine to wait until the rest of your site structure is setup. To register your site go to Google Analytics and they will walk you through the process.

10.) Webmaster Tools: Submit your site to Google and Bing Webmaster tools (which now covers Yahoo). They have solid instructions in both Bing and Google so I won’t explain this step by step. Depending on the XML sitemap plugin you choose the instructions will also vary slightly. My only other point would be to do this as soon as your site structure is setup if you want the SERP’s to start indexing your pages. I once forgot to do this for two months and couldn’t figure out why a lot of my pages and links weren’t showing up. The data you get from using these tools is also crucial.

Now you have your site setup. It’s getting crawled by the search engines and feeding you data. The hard part comes next, you’ve gotta start posting! Blogging can be very competitive but if you have something interesting to say and you stick with it then good things will happen.

P.S. – I know I didn’t cover how to setup your RSS feed or integrate with your social media (facebook, twitter, etc.). Although that stuff is important I didn’t consider it fundamental in the first steps to getting a site up and running. Leave me some feedback and if anyone would like me to cover that I can come back with a separate post.

Follow me on Twitter!

Tags: blogging, navigation, plugins, SEO, webmaster tools, wordpress
Posted in SEO, tools | 8 Comments »

LinkedIn Launches Free Lead Collection Feature

Thursday, November 3rd, 2011

I have previously outlined why and how LinkedIn can be a great advertising platform for you B2B PPC strategy (here and here)

And now they have added another wonderful, FREE feature called Lead Collection for advertisers who are running LinkedIn ads. This new feature gives users that click to your site an easy way to contact your company by showing a “Request Contact” button above your website.

How it works

When a user clicks on your LinkedIn ad and is directed to your website, they will see a lead collection bar above your site which gives the user the option to be contacted by your company.

For each lead you will receive the member’s name, headline and a link to their LinkedIn Profile. LinkedIn members have the option of sharing their email address as well. You will receive this information via email to either the email address associated with a personal account, or the contact associated with the Business Account.

You can also view your leads in the Ads Dashboard under the leads tab. Here you can filter out leads by time frame, contact status and you can even see which campaign the lead came from.

You will also see a Lead count in the Ads Dashboard in the Ad Campaign Tab.

How Do I Set This Up?!!?

The great news – it’s very simple … so there is no excuse not to use this feature! (Did I mention, it’s FREE?)

All you have to do is go to your campaign settings and check “Yes” for the Collect Leads option:

No tracking or back-end coding needs to be set up as everything is tracked in the LinkedIn interface.

My 2 Cents

LinkedIn is still a relatively new player in the PPC space but this shows that they are making improvements to their product and offering more options for advertisers to connect with potential leads.

While you are most likely sending users to a landing page with some sort of contact form, it doesn’t hurt to give them as many options as possible to reach out to you! Especially when you can track the lead!

By collecting the leads via LinkedIn Lead Collection could also hold much more value to your sales team. For example: You are already (or should be) targeting users in your key demographic. The user resonated with your ad, clicked to your site and reached out to be contacted by you. You then have insta-access to their company information to determine if that lead actually holds value to you and whether or not it will be worth pursuing.

On the downside, this feature could get tricky if you are using a dummy account for your campaign (which I do not recommend via the tips section in my previous post, Using LinkedIn For Your B2B Strategy.

At this time, I am not exactly sure how contacting the leads will work as I have not set this up for a client yet (working on that), but I am assuming you will only be able to contact the lead directly via the account associated with the ad campaign. If you are using a dummy account you will need to be sure to check the email address associated with the account often as well as the campaign performance in the interface (which you should be checking daily!) to ensure you are following up with the lead in a timely manner. You may still be able to contact them as you choose since you will have the users information. You just won’t be able to see a breakdown of leads contacted vs. non-contacted in the leads tab of the LinkedIn interface.

This leads me to a few questions – Will this affect ad quality if users do not interact with the Request Contact button? Will LinkedIn remove the feature for companies that do not follow up with the leads properly? Will users interact with the button and find it helpful, or is it a bit creepy?

What are your thoughts? Will you test this new feature?

Follow me on Twitter!

Posted in LinkedIn, PPC, social media, tools | 2 Comments »

Go Beyond the Rankings: Talk Revenue with your Clients

Tuesday, November 1st, 2011

We’ve all been there before. You have a great linkbuilding strategy. You have every step of the process mapped out. You’re projecting 20 links and feel fully confident this strategy is enough to make an important keyword jump from 7 to 3 on Google.  Now you just need your client to make the investment for the strategy.

But there is one problem. They don’t see the value of the return on their investment.

Too often we get caught up in winning the rankings race, but not explaining to a client how it will directly impact their business.  We are all on the same page that increased rankings means increased visits means increased conversions means increased revenue.  But how much money are we talking here?

That’s why providing data to your clients that speaks in dollars and cents is critical.  In this particular example, I’m going to show how to get approval for that linkbuilding budget by showing wins in the revenue column.  Note: this strategy works particularly well with ecommerce clients that have transactions set up in Google Analytics.

Identify Keywords with High Average Values & Strong Conversion Rates

For larger e-commerce sites, it’s virtually impossible to track every keyword that builds revenue.  As SEO’s we often talk about “low hanging fruit” as part of a long-tailed keyword strategy to identify opportunities that we’re not always tracking/pushing to optimize.  Looking at a site’s past performance based on average values is just another way to attack the quick wins.

In this analysis I looked at one of our clients in the motorcycle apparel space.  For this particular industry, six months of data made sense because “hot products” tend to change relatively quickly.  In most cases the more data, the better.   But if your client sells products that have a quick turn over or are seasonal, set your data accordingly.

Along with date range, using the right filters will help with your analysis. Again, this all depends on the industry and your client’s product offerings.  You know their business, so set filters that are going to give you insight on real opportunities.

Next, sort by Average Value, determine the amount of rows you’d like to include and export your results to a CSV. Rather than explain how to quickly put this data together, I’ll refer you to Mark Lavoritano’s post on Striking Distance Keywords. It’s a great read and it shows you how to use RankChecker to quickly find where all of your potential opportunities are ranking on Google.

Let’s fast forward to the results.  Here I have a spreadsheet that shows keywords (sorry, had to block those out) with high average values, along with their respective ranking and conversion rate.  This additional data gives you even more insight on what will gain the quickest win. Off the bat I immediately identified three keywords that were long-tailed, had high average values with strong conversion rates and ranking within striking distance (Note: run a quick spot check on the keywords to make sure their positioning is accurate.  RankChecker is a great tool for proximity, but you may find slight discrepancies when doing a Google Chrome Incognito, Location USA search).

I Have My Data. I Have My Target Keywords. Now What?

Slingshot SEO’s recent Organic CTR Study has provided updated information that you should use to your advantage. While the AdWords Keyword Tool is also great measuring stick, using these CTR rates combined with actual data may provide a stronger analysis when pitching your client.

Let’s look at Keyword 1 and what we already know about it based on six months of data:

- It’s ranked 7 (let’s assume this as constant over the last 6 months)

- It has an average value of $490.32 (this can be skewed by outliers, so go back into GA to find out)

- It has an average 306 visits a month

- It has earned $2,043 a month

- Using Slingshot SEO’s CTR data, we can conclude that with a 1.88% CTR for keywords ranking 7^th, Keyword 1 has been searched 16,114 a month (Note: AdWords Keyword Tool estimates 14,800 exact match, global searches).

Now that we know the amount of searches a month, we can project what it’s worth to move up in the rankings.  If Keyword 1 ranked 3^rd, here is what we could expect:

- With a ranking of 3, Keyword 1 can assume a 7.22% CTR on 16,114 searches a month = 1,160 projected monthly visits

- Using Keyword 1’s conversion rate, ranking number 3 would bring 14.5 conversions a month

- Using Keyword 1’s Average Value, ranking number 3 would earn $7,122 a month

- Therefore, ranking number 3 for Keyword 1 would bring an additional $5K each month and an additional $60K a year

Drawbacks

Because everyone looks at data differently, it’s easy to see some of the drawbacks by using the data outlined above.

We’re looking at a fairly small sample size; what if one transaction skewed the data for the average value?  Go back and look to make sure it’s been relatively consistent. If not, find another keyword.

What if Slingshot SEO’s data isn’t accurate? Chances are, it’s not 100% and varies depending on the industry and the type of results. So play with the numbers and maybe use past data to form your own conclusions on CTR.

What if the keyword’s ranking fluctuated throughout the six months, further skewing the data? You’re probably right that it did have some movement.  But even if you were half off, you’d still see an additional $30K a year in revenue.

Keep in mind that SEO is not the only industry that uses data based on market research to make dollar-value projections. The underlying point is simple: Money talks. Speaking to your clients about rankings combined with revenue will help get you what you need to succeed.  Use keywords that have historically performed well, and show them a quick win where you can point out what it means to their bottom line by improving rankings.  Don’t just tell them you’re going to build 20 links.  Tell your client that building 20 links means moving a valuable keyword from 7 to 3 on Google, and increasing revenue by $60K a year.

The rest is up to you. You’ve shown the numbers, now you’ve got to show the win.

And, as the late great Al Davis once said, “Just win, baby.”

USE THE TOOL YOURSELF:

Special thanks to Mark Lavoritano for building an easy to use spreadsheet to share with everyone. As I was putting together this data, Mark was kind enough to put his excel chops to work and build a tool that involves very little work for the user and provides a quick snapshot of the impact on revenue based on keyword positioning. 

Download the tool here, add some data and let us know what you think.  Have a suggestion for improvement? Drop us a line in the comments and share your feedback!

Follow Ryan Fontana on Twitter

Posted in analytics, ecommerce, SEO, tools | 7 Comments »

Why Aren’t You Using Twitter To Build Links?

Tuesday, October 25th, 2011

Posted in linkbuilding, SEO, social media, tools | 15 Comments »

Using SQ Reports to Prioritize Keywords (And Write Better Content)

Thursday, August 18th, 2011

Disclaimer: I am not an SEO dude. I don’t have the same link building chops as Old Man Suarez, nor the architecture savvy of Senor Snyder. However, I am well versed in the timeless art of PPC and like to think I know a thing or two about the intertubez.

So picture this: After months of hard work, you’ve got top rankings for some of your keywords (bravo SEO’s!) and want to write some content to expand your reach into the long tail. More than likely, you would head over to Google Suggest/Promediacorp’s Suggester/Soovle or any other tool of choice and get some ideas for keyword variations you’d like to rank for. Then, you drop these variations into your traffic estimator of choice and boom; you’ve got keywords with volume. Finally, you would start developing content that’s designed to rank well, attract links and (above all) customers. You know the content development phase is going to take a long time, but eventually you’ll get those rankings and sweet, glorious traffic!

The fatal flaw in the (commonly used) method above is that you have no real idea how that traffic is going to convert. You’ve got about 900 possible long tail versions of that keyword that you’d love to rank for. But, can you really write content for all of those 900 keywords? Which keyword do you choose if there are 10 variations, all with the same search volume?

The answer to all of these questions lay within a gigantic dataset that your resident PPC Professional already has – Search Query reports! For those of you that don’t know, a search query report is a tool offered  by most major search engines and contains key metrics (impressions, clicks, cost, conversions) broken down by the exact query searched. That means you can find the exact keyword variations that haven’t just driven traffic but (drumroll please) CONVERTED!

Pretty valuable stuff from a PPC perspective, but here’s a trick that Wil and I came up with to help the SEO’s of the world to write content with keywords that will not only generate traffic volume, but are most likely to drive conversions as well.

Note: The guide below assumes you have excel 2007 or later. If you need old school instructions, leave a comment and I’ll write a guide for other versions.

1: Pick your lead horse

The first step is picking the lead keyword for which you’ll be optimizing the page. In this case, we’re going after Help Desk Software, which has 6,600 monthly searches.

2: Pull your SQ report

Navigate to the “keyword” tab of your AdWords interface and click on “See Search terms – All.” You could filter now, but I prefer to toy with the excel sheet itself once we have all the data so we don’t miss anything.  Make sure to pull it for a long time period – in this instance I’m going for the last 14 months but the more data you have, the better.  

3: Clean the data.

After you’ve exported the data into Excel, we need make it easier to work with. First, eliminate all of the columns you don’t need. Eliminate EVERY column except for Clicks, Impressions and Conversions (we’ll need conversion rate as well, but don’t worry about that now). If you really want, you can keep cost in there, but for this purpose it’s not necessary.

4: Organize the data

Hope you’ve got plenty of RAM. Depending on how much you spend and how large the account is, you’ll likely have a TON of data. In this example I ended up with approximately 18,000 rows, many of which are duplicates (a single query could be sourced from several different campaigns/countries/keywords). This is where our good friends Pivot Tables and Patience come into play. First, create a pivot table with the entire Search Query report included.

Add Search Term as the row label. Add Impressions, Clicks & Conversions as the values. This will create a dynamic chart that totals all the duplicates for you.

Ta-da – we have totals! (Note: you could use Sub-totals for this, but I find that takes a bit longer and is kind of annoying). Copy the entire pivot table into a new sheet (copy – paste special – paste values) so you can…

5: Play with the data

There’s a ton of great data, but in this case we’re only concerned with tails that contain the phrase “help desk software.” On the excel sheet execute a find-and-replace for help desk software, only changing the color.

Sort the cells by color (custom sort – sort on cell color – your color on top) and voilà, you’ve got all the “help desk software” queries isolated. I’d advise you to set a threshold from here on out (i.e. remove any queries with less than 10 conversions) so you’re only looking at significant data. Add in and calculate a “conversion rate” column, and sort the list in descending order. Depending on how big your initial data set was, you should have a nice little list of tail terms to weave into content.

It’s not a bad idea to run these through a traffic estimator just to make sure they get some traffic. In this case, all but 2 keywords showed up in the estimator.

6: Write!

So what do you think? Will you be using query data to help with your content efforts?

For a few more PPC-to-SEO ideas, check out Our Powers Combined Part 1, written by Ryan Fontana and myself!

Posted in PPC, SEO, tools | 4 Comments »