Tag Archive | "Using"

Transform Your Business Website Using Our Free ‘Design 101’ Ebook

Is your current website design working for your business as well as it could be? You might know that it’s not, but don’t know where to start when it comes to a redesign. We understand that. Choosing a WordPress theme for your website can be a little overwhelming and leave you with lots of further
Read More…

The post Transform Your Business Website Using Our Free ‘Design 101’ Ebook appeared first on Copyblogger.


Copyblogger

Posted in IM NewsComments Off

How to drive conversion using a value proposition-focused testing strategy in email marketing

Value proposition is the maximized, optimized force of the perceived value that you are offering to potential customers. Many marketers, however, leave it on the website and forget about it in email. We’re busy testing this subject line or that, without any real strategy in mind.
A peer example in this blog post will show examples of how to formulate a testing strategy for every element of email marketing, giving you an advantage over the competition and driving conversion.

MarketingSherpa Blog

Posted in IM NewsComments Off

Paint by Numbers: Using Data to Produce Great Content

Posted by rjonesx.

It’s not every day that I write about content. To be honest, it’s probably a once-a-year kind of thing. I will readily admit that I’m a “links are king” kind of SEO, and have been since starting in this industry more than a decade ago. However, I do look over the fence from time to time to see if the grass is greener and, on occasion, I actually like what I see. Prior to joining Moz, I was a consultant at an agency like many of you reading this blog post. More often than not, one of the key concerns of my clients was what to write about. It seems that webmasters and business owners alike can easily acquire writer’s block after trudging through the uninspiring task of turning a list of keywords into website copy. So where do you look when you have run out of words

Numbers.

Alright, stick with me here. I imagine for some of you the idea of poring over numbers to remedy writer’s block would be like trying to stop a headache with a brick. It’s adding insult to injury. What I hope to show you in the next couple of paragraphs is how data can be an incredible source of inspiration in writing, especially if you can hit a few key principles: expose, relate, surprise, and share.

Expose

Chances are your business or website generates some amount of unique, first party data that you can expose to the world. It might be from analytics, your rank tracker like Moz, or from raw user data if you operate a forum. I’ll give you examples of how you might tap into these resources (especially when they don’t seem obvious or plenteous) but let’s start with a canonical example of one great use of first-party data in an industry that seems directly at odds with — dating.

The thought of quantifying and analyzing our love lives seems like an oxymoron of sorts. However, one of the most successful uses of data for content has been produced by the team at OK Cupid, whose “data”-tagged blog posts have earned thousands of solid backlinks and enviable traffic. The team at OK Cupid accomplishes this by tapping their huge resource for unique data, generated by their user base. Let’s look at one quick example: Congrats Graduates: No One Gives a Sh*t.

22% of female and 16% of male millenials say a college degree is mandatory for dating.

The blog post is fairly straightforward (and not particularly long) but it used unique data that isn’t really available to the average person. Because OK Cupid is in a privileged position, they can provide this kind of insight to their audience at large.

But maybe you don’t have a million customers with profiles on your site; where can you look for first party data? Well, here are a couple of ideas of the types of data your company or organization might have which can easily be turned into interesting content:

  • Google Analytics, Search Console data and Adwords data: Do you see trends around holidays that are interesting? Perhaps you notice that more people search for certain keywords at certain times. This could be even more interesting if there’s a local holiday (like a festival or event) that makes your data unique from the rest of the country.
  • Sales data: When do your sales go up or down? Do they coincide with events? Or do they happen to coincide with completely different types of keywords? Try using Google Correlate, which will identify keywords that follow the same patterns as your data.
  • Survey data: Use your sales or lead history to run surveys and generate insightful content.
    • A clothing store could compare responses to questions about personality by the colors of clothing that people purchase (Potential headline: Is It True What They Say About Red?)
    • A car parts store could compare the size of certain accessories to favorite sports (Potential headline: Big Trucks and Big Hits)
    • An insurance provider could compare the type of insurance requested vs. the level of education (Potential headline: What Smart People Do Differently with Insurance)

There are probably tons more sources of unique, first-party data that you or your business have generated over the years which can be turned into great content. If you dig through the data long enough, you’ll hit pay dirt.

Relate

Data is foreign. It’s a language almost no one speaks in their day-to-day conversations, a notation meant for machines. This consideration requires that we make data immediately relatable to our readers. We shouldn’t just ask “What does the data say?”, but instead “What does the data say to me?” How we make data relatable is simple — organize your data by how people identify themselves. This can be geographic, economic, biological, social, or cultural distinctions with which we regularly categorize ourselves.

Many of the best examples of this kind of strategy involve geography (perhaps because everyone lives somewhere, and it’s pretty non-controversial to make generic claims about one location or another). Take a look at a map of your country and try not to look first towards where you live. I’m a North Carolinian, and I almost immediately find myself interested in anything that compares my state to others.

So maybe you aren’t OK Cupid with millions of users and you can’t find unique data to share — don’t worry, there’s still hope. The example below is a rather ingenious method of using Google Adwords data to build a geographical story that’s relatable to any potential customer in the United States. The webmasters at Opulent used state-level Keyword Planner to visualize popularity across the country in a piece they call the “State of Style.

When I found this on Reddit’s DataIsBeautiful (where most of these examples come from), I immediately checked to see what performed best in North Carolina. I honestly couldn’t care less about popular fashion or jewelry brands, but my interest in North Carolina eclipsed that lack of interest. Geography-based data visualization has produced successful content related to in sports, politics, beer, and even knitting.

If you walk away with any practical ideas from this post, I think this example has got to be it. Fire up an Adwords campaign and find out how consumer demand breaks down in your industry at a state-by-state level. Are you a marketer and want to attract clients in a particular sector? Here’s your chance to write a whitepaper on national demand. If you’re a local business, you can target Google Keyword Planner to your city and compare it to other cities around the country.

Surprise

Perhaps the greatest opportunity with data-focused content is the chance to truly surprise your reader. There’s something exciting about learning an interesting fact (who hasn’t seen one of these lying around and didn’t pick it up?). So, how do you make your data “pop?” How do you make numbers fascinating?

Perspective.

Let’s start with a simple statistic:

The cost of ending polio between 2013 and 2018 is

$ 5.5 Billion Dollars.

How does that number feel to you? Does it feel big or little? Is it interesting on its own? Probably not, let’s try and spice it up a bit.

$ 5.5 billion dollars doesn’t seem that much when you realize people spend that amount on iPhones every 2 weeks. We could rid the world of polio for that much! Or, what if we present it like this…

In this light, it seems almost insane to spend that much money preventing just a couple more polio cases relative to the huge gains we could make on malaria. Of course, the statistics don’t tell the full story. Polio is in the end-stages of eradication where the cost-per-case is much higher, and as malaria is attacked, it too will see cost-per-case increase. But the point remains the same: by giving the polio numbers some sort of context, some sort of forced perspective, we make the data far more intriguing and appealing.

So how would this work with content for your own site? Let’s look at an example from BestPlay.co, which wrote a piece on Board Games are Getting Worse. Board games aren’t a data-centric industry, but that doesn’t keep them from producing awesome content with data. Here’s a generic graph they provide in the piece which shows off average board game ratings.

There really isn’t much to see here. There’s nothing intrinsically shocking about the data as we look at it. So how do they add perspective to make their point and give the user intrigue? Simple — apply a historical perspective.

With this historical perspective, we can see board game scores getting better and better up until 2012, when they began to take a dive — the first multi-year dive in their recorded history. To draw users in, you use comparison to provide surprising perspectives.

Share

This final method is the one that I think is most overlooked. Once you’ve created your fancy piece of content, let your audience do some leg work for you by releasing the data set. There’s an entire community of the Internet just looking for great data sets which could take advantage of your data and cite your content in their own publications. You can find everything from All of Donald Trump’s Tweets to Everything Lost at TSA to Hand-drawn Pictures of Pineapples. While there is a good chance your data set won’t ever be used, it can pick up a couple of extra links in the event that it does.

Putting it all together

What happens when a webmaster combines these types of methods — exposing unique data, making it relatable and surprising, even for a topic that seems averse to data? You get something like this: Jeans vs. Leggings.

This piece played the geography card for relatability:

They compared user interest in jeans to give perspective to the growth of demand for leggings:

Slice.com reveals their first-party data to make interesting, data-driven content that ultimately scores them links from sites like In Style Magazine, Shape.com, and the NY Post. Looking at fashion through the lens of data meant great traffic and great shares.

How do I get started?

Get down and dirty with the data. Don’t wait until you end up with a nice report in your hand, but start slicing and dicing things looking for interesting patterns or results. You can start with the data you already have: Google Analytics, Google Search Console, Google Adwords, and, if you’re a Moz customer, even your rank tracking data or keyword research data. If none of these avenues work, dig through the amazing data resources found on Reddit or WebHose. Look for a story in the numbers by relating the data to your audience and making comparisons to provide perspective. It isn’t a foolproof formula, but it is pretty close. The right slice of data will cut straight through writer’s block.

Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don’t have time to hunt down but want to read!


Moz Blog

Posted in IM NewsComments Off

3 tactics we’re using for Answer Boxes

The featured snippet (answer box) that sometimes appears at the top of the SERPs is a coveted spot for marketers, but how can you get your content there? Columnist Brian Patterson has some tips.

The post 3 tactics we’re using for Answer Boxes appeared first on Search Engine Land.



Please visit Search Engine Land for the full article.


Search Engine Land: News & Info About SEO, PPC, SEM, Search Engines & Search Marketing

Posted in IM NewsComments Off

Using call and SMS data to drive new customers

Voice search now accounts for 20% of queries on Google’s mobile app and Android devices. Inbound call volume is continuing to increase even as messaging apps and chatbots become more popular. Facebook expects 37 billion call conversions by 2019, as social media’s share of mobile calls to…



Please visit Search Engine Land for the full article.


Search Engine Land: News & Info About SEO, PPC, SEM, Search Engines & Search Marketing

Posted in IM NewsComments Off

Using Cloud Analytics for a Winning March Madness Bracket

Welcome to the first of two days of low workforce production and nail biting moments that lead into 3 glorious weeks March Madness. The first round of 2017 NCAA Division I Men’s Basketball Tournament tips off today and it should be another doozy of an experience. With everyone scrambling to get their brackets in before the 12:15pm EST, hundreds of thousands bracketologists spent the last few days studying stats, algorithms, trends, match ups, or just favorite mascot to complete their winning pool entry(s).

But for Nic Smith, Global VP of Product Marketing for Cloud Analytics at SAP, it’s all about the data and analytics. In a clever and fun way to jump into March Maddness, Nic and his SAP Data Genius team are using their company’s BusinessObjects Cloud to breakdown which teams will advance in this year’s college basketball tournament.

Using analytic capabilities that include data preparation, modelling, data exploration, planning and what-if analysis, visual storytelling, and automated smart data discovery to uncover hidden patterns for which teams will perform best in the tournament, Nic outlines the steps his team used to create the winning bracket.

Nic’s team is planning to share results as the rounds of the tournament unfold and provide updated analysis, what-if scenarios, and insights based on college team performance. He also challenges anyone to see if they have what it takes to beat the SAP DataGenius bracket and begin and share (trash talk) their results using #vizthemadness with @SAPAnalytics.

The post Using Cloud Analytics for a Winning March Madness Bracket appeared first on WebProNews.


WebProNews

Posted in IM NewsComments Off

Using Brand Ads in Unexpected Ways Drove Higher Installs for Lyft

Can brand ads for apps drive installs better than traditional direct response ads? That’s what Lyft discovered working with Google’s Art, Copy & Code project.

In a case study published today, Google shares some surprising findings from their analysis of thousands of YouTube videos running TrueView app install ads for Lyft.

The case study takes two popular Lyft ads designed to raise brand awareness: Shaq disguised as a driver, and Seattle Seahawk star Richard Sherman undercover and trash talking himself and members of the Golden State Warriors pranking a teammate.

They A/B tested these branding ads against Lyft’s direct response ads to gauge if videos meant to drive awareness could also drive consumers to install mobile apps.

The Shaq ad had some impressive stats vs the direct response ads: 2x the branding lift , 8% higher click rate and a similar conversion rate.

Small tweaks to the Shaq ad, like adding music on top of the brand overlay, had some powerful results.

Unexpectedly, ads tested without a promotional offer lead to more people installing the app than ones with a promo.

It is unsurprising that ads featuring Shaq will outperform ones that don’t. The important takeaway from this study is that your best branding creative may be your best direct response creative and that you should test and tweak to optimize.

The post Using Brand Ads in Unexpected Ways Drove Higher Installs for Lyft appeared first on WebProNews.


WebProNews

Posted in IM NewsComments Off

Google Using RAISR Technology on Google+ and Saving 75% in Bandwidth

Google+ has become a haven for high end photos by professional photographers who obviously care about image quality. Google’s solution to the huge bandwidth requirements for their free service is a technology called RAISR. Lower bandwidth is also a benefit to the end user by increasing loading speeds and lowering data costs. This is especially concerning outside of the United States where it’s rare not to have to pay for internet based on data usage.

Back in November Google introduced a machine learning technology called “RAISR: Rapid and Accurate Image Super-Resolution”, that creates high-quality versions of low-resolution images. “RAISR produces results that are comparable to or better than the currently available super-resolution methods, and does so roughly 10 to 100 times faster, allowing it to be run on a typical mobile device in real-time,” explained Peyman Milanfar, Lead Scientist at Google Research. “Furthermore, our technique is able to avoid recreating the aliasing artifacts that may exist in the lower resolution image.”

Here’s how Google’s technical team (Yaniv Romano, John Isidoro, Peyman Milanfar) described it in June 2016:

Given an image, we wish to produce an image of larger size with significantly more pixels and higher image quality. This is generally known as the Single Image Super-Resolution (SISR) problem. The idea is that with sufficient training data (corresponding pairs of low and high resolution images) we can learn set of filters (i.e. a mapping) that when applied to given image that is not in the training set, will produce a higher resolution version of it, where the learning is preferably low complexity. In our proposed approach, the run-time is more than one to two orders of magnitude faster than the best competing methods currently available, while producing results comparable or better than state-of-the-art.

A closely related topic is image sharpening and contrast enhancement, i.e., improving the visual quality of a blurry image by amplifying the underlying details (a wide range of frequencies). Our approach additionally includes an extremely efficient way to produce an image that is significantly sharper than the input blurry one, without introducing artifacts such as halos and noise amplification. We illustrate how this effective sharpening algorithm, in addition to being of independent interest, can be used as a pre-processing step to induce the learning of more effective upscaling filters with built-in sharpening and contrast enhancement effect.

“RAISR, which was introduced in November, uses machine learning to produce great quality versions of low-resolution images, allowing you to see beautiful photos as the photographers intended them to be seen,” noted John Nack, Product Manager of Digital Photography at Google. “By using RAISR to display some of the large images on Google+, we’ve been able to use up to 75 percent less bandwidth per image we’ve applied it to.”

“While we’ve only begun to roll this out for high-resolution images when they appear in the streams of a subset of Android devices, we’re already applying RAISR to more than 1 billion images per week, reducing these users’ total bandwidth by about a third,” said Nack. “In the coming weeks we plan to roll this technology out more broadly — and we’re excited to see what further time and data savings we can offer.”

The post Google Using RAISR Technology on Google+ and Saving 75% in Bandwidth appeared first on WebProNews.


WebProNews

Posted in IM NewsComments Off

Google Using RAISR Technology on Google+ and Saving 75% in Bandwidth

Google+ has become a haven for high end photos by professional photographers who obviously care about image quality. Google’s solution to the huge bandwidth requirements for their free service is a technology called RAISR. Lower bandwidth is also a benefit to the end user by increasing loading speeds and lowering data costs. This is especially concerning outside of the United States where it’s rare not to have to pay for internet based on data usage.

Back in November Google introduced a machine learning technology called “RAISR: Rapid and Accurate Image Super-Resolution”, that creates high-quality versions of low-resolution images. “RAISR produces results that are comparable to or better than the currently available super-resolution methods, and does so roughly 10 to 100 times faster, allowing it to be run on a typical mobile device in real-time,” explained Peyman Milanfar, Lead Scientist at Google Research. “Furthermore, our technique is able to avoid recreating the aliasing artifacts that may exist in the lower resolution image.”

Here’s how Google’s technical team (Yaniv Romano, John Isidoro, Peyman Milanfar) described it in June 2016:

Given an image, we wish to produce an image of larger size with significantly more pixels and higher image quality. This is generally known as the Single Image Super-Resolution (SISR) problem. The idea is that with sufficient training data (corresponding pairs of low and high resolution images) we can learn set of filters (i.e. a mapping) that when applied to given image that is not in the training set, will produce a higher resolution version of it, where the learning is preferably low complexity. In our proposed approach, the run-time is more than one to two orders of magnitude faster than the best competing methods currently available, while producing results comparable or better than state-of-the-art.

A closely related topic is image sharpening and contrast enhancement, i.e., improving the visual quality of a blurry image by amplifying the underlying details (a wide range of frequencies). Our approach additionally includes an extremely efficient way to produce an image that is significantly sharper than the input blurry one, without introducing artifacts such as halos and noise amplification. We illustrate how this effective sharpening algorithm, in addition to being of independent interest, can be used as a pre-processing step to induce the learning of more effective upscaling filters with built-in sharpening and contrast enhancement effect.

“RAISR, which was introduced in November, uses machine learning to produce great quality versions of low-resolution images, allowing you to see beautiful photos as the photographers intended them to be seen,” noted John Nack, Product Manager of Digital Photography at Google. “By using RAISR to display some of the large images on Google+, we’ve been able to use up to 75 percent less bandwidth per image we’ve applied it to.”

“While we’ve only begun to roll this out for high-resolution images when they appear in the streams of a subset of Android devices, we’re already applying RAISR to more than 1 billion images per week, reducing these users’ total bandwidth by about a third,” said Nack. “In the coming weeks we plan to roll this technology out more broadly — and we’re excited to see what further time and data savings we can offer.”

The post Google Using RAISR Technology on Google+ and Saving 75% in Bandwidth appeared first on WebProNews.


WebProNews

Posted in IM NewsComments Off

How to Uncover Hidden Keyword-Level Data Using Google Sheets

Posted by SarahLively

TL;DR

Keyword-level data isn’t gone, it’s just harder to get to. By using Google Sheets to marry the data from Search Console and Google Analytics into a sheet, you’ll have your top keywords and landing page engagement metrics together (for free!). It’s not perfect keyword-level data, but in 7 steps you can see the keywords that drove clicks to a page and the organic engagement metrics for that page, all together in one place. The Google Analytics Add-on for Google Sheets will pull organic landing page engagement metrics, and the Search Analytics for Sheets Add-on will pull the top queries by landing page from Search Console. Then, use VLOOKUP and an Array Formula to combine the data into a new tab that has your specified landing pages, the keywords that drove clicks there, and the specified engagement metrics.

What do you mean you don’t know which keyword drove that conversion?

Since the disappearance of keyword-level data in Google Analytics, SEOs have been struggling to tie keyword strategies to legitimate, measurable metrics. We put much of our time, resources, and research efforts into picking the perfect keyword theme, full of topically relevant terms that leverage new semantic strategies. We make sure to craft the perfect metadata, positioning our top keywords in the right place in the title tag and integrating them seamlessly into the meta description, but then what? We monitor rankings and look to landing page metrics, but all of our data is disjointed and we’re left to extrapolate insights based on a limited understanding of how our themes are truly performing.

There is good news, though! Keyword-level data is still there — it’s just much harder to get to given the structure of existing platforms. If you’re like me, you have your landing page metrics in Google Analytics, your keyword click data in Search Console, and your keyword themes in a manual program (probably Excel). Given the way Google Analytics exports data, the way Search Console separates keywords and landing pages, and the nuances you’ve applied to your own keyword theme documents, it’s difficult to marry all of the data in a way that gives you actionable insights and real-time data monitoring capabilities.

Difficult… but not impossible. Enter: Google Sheets. In 7 easy steps you can pull all of this data into one sheet so you can see your keyword theme, the keywords you’re getting clicks for, the page ranking, and any organic metric for that page (think engagement metrics, conversion metrics, revenue metrics, etc.), all in one place! You can monitor keyword opportunities within striking distance, whether the keywords you want to rank for are actually ranking, and what terms and themes are driving the majority of your revenue or conversions. At the end of the day all of this works to give you actionable metrics you can monitor and change through keyword strategies. It’s much easier than you may think, and the steps below will get you started.

Follow this guide to build out a basic Google Sheet that ties Search Console, Google Analytics, and your keyword theme into one place for a few pages, and then you’ll be well on your way to building out automated sheets that give you greater insight into keyword-level data!

Step 1: Get the Google Analytics and Search Analytics for Sheets Add-ons

The Google Analytics Add-on will allow you to pull any metric from Google Analytics into your spreadsheet and Search Analytics for Sheets will pull data from Search Console. Pulling from these two sources will be the key to combining the data from Google Analytics and the Search Analytics report in a meaningful way. Once you have a new sheet open and you’re in the add-on feature, finding and installing Google Analytics and Search Analytics for Sheets should be pretty straightforward. Also, both add-ons are free.

Step 2: Create Google Analytics reports

Once you’ve installed the Google Analytics add-on, you’ll find “Google Analytics” in your menu. Hover over Google Analytics and select Create new report to get started. After the sidebar menu pops in, select the Account, Property, and View that you want to pull data from. You will also be able to name your report (see note below) and then select Create Report. You do not have to worry about the metrics and dimensions at this point, but that will come later.

Note: At the end of this article I have a template you can use to combine the data from Google Analytics and Search Analytics. If you want to use the template, make sure you name this first report Organic Landing Pages Last Year. I will also walk through the formulas and functions used in this article, so you don’t have to rely on the template, but the nomenclature of each tab must be consistent to use my exact formulas. There are plenty of opportunities to rename the report and tabs, so don’t stress if you miss this part and name your report something different; just know that if at the end the template isn’t working, you should double-check the tab names.

Step 3: Configure your Google Analytics reports

The Report Configuration tab you now see as the first tab in your sheet is where you can configure the data you want to pull. I highly recommend familiarizing yourself with this functionality by watching this quick, five-minute video from Google as an overview on how to generate reports from Google Analytics in Google Sheets. Listed below are the fields being used for this report, and you can find an extensive overview of what all of these fields mean and the metrics you can use within them here: https://developers.google.com/analytics/solutions/google-analytics-spreadsheet-add-on.

Note: If you prefer to simply fill in your sheet and read the details on each field configuration later, you can paste the cells below into your table at cell B5 (just double-check it looks like the screenshot above) and skip down to the last paragraph in this section, right after Segments.

395daysAgo
365daysAgo
ga:sessions, ga:bounces, ga:goalCompletionsAll
ga:landingPagePath
-ga:sessions
sessions::condition::ga:medium==organic

Report Name:

The name you set when you created the report. This can be changed, but note that when you run your report, the tab with your report will use this report name.

Type:

This will automatically fill in “core” for you, meaning we are pulling from the Core Reporting API.

View:

This will also automatically fill in your Profile ID, which you set when you created the report.

Start Date:

To compare the last 30 days to the same 30 days the previous year, we will set the Start Date as 395daysAgo

End Date:

To compare a full 30 days last year to a full 30 days this year, we will set the End Date as 365daysAgo

Metrics:

This refers to the metrics you want to pull and will dictate the columns you see in your report. For this report we want to look at sessions, bounces, and goal completions, so we are using the metrics ga:sessions, ga:bounces, ga:goalCompletionsAll. Google has an excellent tool for searching possible metrics here (https://developers.google.com/analytics/devguides/reporting/core/dimsmets) if you want to eventually test and pull anything other than sessions, bounce rate, and goal completions.

Dimensions:

Dimensions refers to the dimensions you want to see specific metrics for; in this case, landing pages. We’re using landing pages as the dimension because this will allow us to match Search Analytics landing page query data with landing page Google Analytics. To pull the metrics you selected above by landing page, use ga:landingPagePath

Sort:

The Google Analytics API will default to sort your metrics in ascending order. For me, it’s more valuable to see the top landing pages in descending order so I can get a quick look at the pages driving the most traffic to my site. To do this, you simply place a minus (-) sign before the metric you want to sort your date by: -ga:sessions. You can learn more about sorting metrics through the Google Analytics API here: https://developers.google.com/analytics/devguides/reporting/core/v3/reference#sort.

Segments:

The last field we’re going to be adding to is Segments so we can look at just organic traffic. This is where you could put in new organic users, return organic users, or any special segment you’ve created in Google Analytics. However, for this report we’re going to use the primary organic traffic segment that’s standard in Google Analytics: sessions::condition::ga:medium==organic.

As mentioned, we want to see organic traffic to each page during the last 30 days compared to the previous year. To do this, we need to generate two reports: one with our session data for the last 30 days, and one for the session data for the same span of time one year ago. We have 2015 ready to go, so simply paste that into column C, rename the Report Name to Organic Landing Pages This Year and change Start Date to 30daysAgo and End Date to yesterday. Double-check the screenshot above matches your configurations before moving on.

Step 4: Run your Google Analytics report

You will run the report you just created by selecting Run reports under the Google Analytics add-on. We won’t be reviewing scheduling reports in this article, but it can be useful to time these to run on a specific day to align with any ongoing reporting you have. You can learn more about scheduling reports here: https://developers.google.com/analytics/solutions/google-analytics-spreadsheet-add-on#scheduling-reports.

If everything has been completed correctly so far, you should see this popup:

If, for some reason, you see a popup noting that you have an error, Google Analytics is great at letting you know exactly which field has been implemented incorrectly. Double-check your segments here (https://developers.google.com/analytics/devguides/reporting/core/v3/reference) and as long as you’re using valid formatting, you should be able to fix any issues.

Assuming everything went according to plan, you’ll see a spreadsheet that looks like this:

Step 5: Run your Search Analytics for Sheets report

Running a Search Analytics for Sheets report is really simple. Click to your empty sheet (Sheet1), and in the same place you were able to launch Google Analytics, launch the sidebar for Search Analytics for Sheets. From there, you’ll authorize the app and set the parameters of your report. Any metrics that I updated are highlighted in the screenshot below, but you want to group by query and page, aggregate by page, and have the results display on the active sheet. The default for Search Analytics for Sheets is to pull from the previous 90 days, but you can adjust this to display whatever makes sense for your website.

As long as everything runs correctly, you’ll see your top search queries, landing pages, clicks, impressions, CTR, and average position in descending order by clicks. Rename Sheet1 to Search Console Data, and your sheet should look like this:

Step 6: Remove the domain name from Search Analytics landing pages

Hopefully you can see where this is going now. We have one tab with all of our Google Analytics data by landing page, and one with our Search Analytics data by landing page, so all that’s left is to marry the data.

First, we just need to strip the domain name from the Search Console data. You’ll notice the data from Google Analytics pulls the top landing pages excluding the https://domain-name.com, and Search Console pulls the entire domain.Therefore, we have to format them identically in order to combine the data. To do this, you’ll need to execute a “find and replace” on your Page column in the Search Console tab in Google Sheets and replace https://domain-name.com with no replacement (eliminating the domain name from the URL).

Step 7: Combine the data

Download the Keyword Level Data template here. This template has the proper formulas in place to pull landing page sessions year over year, bounce rate, and total goal conversions. I’ve also set Column C up as “Target Keywords” to type in the terms you’re actively targeting on each page. This way, you can see if what you’re targeting is similar to what you’re ranking for in Google. Once the template is up, copy the Keyword Data tab to your worksheet.

After you copy the sheet over, you should see a new sheet with a tab called Keyword Data. From here, select the Keyword Data tab and click Copy to…

Select the sheet you have built with your data, and a copy of the Keyword Data tab will populate at the end of your sheet.

If you’ve done everything correctly so far, you will be able to update your URLs and the data will automatically appear within the template for your specific pages. When adding your page URL, be sure not to include the domain name. For example, if you wanted to see data for https://www.domain-name.com/products/, you would type /products/ in cell B6 and see the data populate. Also make sure everything is matching up with trailing slashes between your Google Analytics data and your Search Console data. If you have issues with duplicate URL structures, you may need to work with the data a bit to make the URL structure formatting consistent (and also you should fix that on the server side!). Your results should look something like this:

How is the template working?

If you’re interested in looking at more than two pages and really building this out into a more robust report, you probably want to understand what formulas are controlling the results so you can expand the data.

The majority of this template utilizes VLOOKUP to pull the Google Analytics data into the sheet. If you’re not sure how VLOOKUP works, you can read more on that here.

The year-over-year percent change column and bounce rate column are simple calculations. For example, the percent change in cell G6 is calculated using =(E6-F6)/F6 and the bounce rate in cell I6 uses =(H6/E6). You’re probably familiar with these common Excel functions already.

The more complicated formula is the array formula that’s being used to pull the keyword data from Search Analytics. Due to the fact that a VLOOKUP will stop after the first match, and we want to see up to five matches for queries, we’re utilizing an array formula instead to pull the matches in up to 5 cells. There are other functions that will do this as well (pull all possible matches in a sheet, that is); however, the array formula is unique in that it lets us limit the results to five rows (otherwise, if you have 10 matches for one term but 4 for another, you wouldn’t be able to structure your sheet in way that displays multiple pages within one tab).

Here is the array formula that’s used in cell D6:

=ArrayFormula(IF(ISERROR(INDEX(‘Search Console Data’!$ A$ 1:$ B$ 5000,SMALL(IF(‘Search Console Data’!$ B$ 1:$ B$ 5000=$ B$ 6,ROW(‘Search Console Data’!$ A$ 1:$ B$ 5000)),ROW(2:2)),1)),”",INDEX(‘Search Console Data’!$ A$ 1:$ B$ 5000,SMALL(IF(‘Search Console Data’!$ B$ 1:$ B$ 5000=$ B$ 6,ROW(‘Search Console Data’!$ B$ 1:$ B$ 5000)),ROW(2:2)),1)))

This formula is allowing multiple values to pull for the value in B6, but also allows the formula to drag down and expand through cell D11. The array formula in cell D11 is:

=ArrayFormula(IF(ISERROR(INDEX(‘Search Console Data’!$ A$ 1:$ B$ 5000,SMALL(IF(‘Search Console Data’!$ B$ 1:$ B$ 5000=$ B$ 6,ROW(‘Search Console Data’!$ A$ 1:$ B$ 5000)),ROW(7:7)),1)),”",INDEX(‘Search Console Data’!$ A$ 1:$ B$ 5000,SMALL(IF(‘Search Console Data’!$ B$ 1:$ B$ 5000=$ B$ 6,ROW(‘Search Console Data’!$ B$ 1:$ B$ 5000)),ROW(7:7)),1)))

You can learn more about array formulas here, but the way they are executed in Google Sheets is a bit different than Excel. From my research, this formula gave the results I wanted (multiple matches controlled in a specific set of cells), but if you know of a function in Google Sheets that does something similar, feel free to share in the comments!

Conclusion

Keyword-level data isn’t gone! Google is giving us valuable insights into what terms are leading users to our sites — we just need to combine the data in a meaningful way. Google Sheets is a powerful way to connect to various APIs and pull loads of data from multiple sources. There are some limitations to the Search Analytics report (see this great post from Russ Jones on some inaccuracies he found in Search Console Search Analytics data), so hopefully this small sheet will inspire you to expand the data and include more engagement metrics from Google Analytics, additional click data from Search Console, rankings data, data for traffic outside of organic, and more. Not to mention all of this can be scheduled, so you can have your Search Analytics and Google Analytics data ready when you open your sheets and automate almost this entire process.

We don’t have to use tools like Search Console and Google Analytics in a vacuum simply because they exist that way. Experiment with ways to combine the data on your own to gain more valuable insights into your campaigns!

Also, if you loved this, if any of this doesn’t work for you, if you know paid tools that do this, you’re doing this a different way, you’re doing this in a bigger way, or this just didn’t make sense to you — comment! I would love to hear how other SEOs are gleaning insights into keyword data in the new days of (not provided) and improve on this process with your help!

Shout outs

A special shout out goes to @mihaiaperghis for publishing this blog post on How to Use Search Analytics in Google Sheets for Better SEO Insights as I was finishing up this post. Thanks to your post, I was able to find a free, easy way to pull from the Search Analytics API into sheets. Before reading, I was utilizing and wrote about a paid add-on that was ~$ 30/month, so thanks to your post I can call this entire process free. Also thanks to @SWallaceSEO for reviewing this article, testing the sheet, and helping me with edits and debugging!

Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don’t have time to hunt down but want to read!


Moz Blog

Posted in IM NewsComments Off

Advert