#(<- my new comma in tag strategy)

[0x4468c7a6a728]

i wanna cosplay...

[iceeericeee]

I wonder how many tags i can add on to this

[your-villainous-neighbour]

FIC WRITER INTERVIEW

Tagged by: @hackedbyawriter thamk

Name: Sam/enigma3000

fandoms: (ones i've written for because we don't have time for me to list all my fandoms) SMZS, Marvel, Black Mirror (but thats just for my eyes go away), Angels in America, Attack on Titan, Article 15, Kota Factory), Padmaavat

two-shot: none. What is a two shot pls it's either 1 chapter or anywhere between 5 to 283728. god bless

most popular multi chapter fic: Five Stages would be the most popular, I think, considering it has the most reads/kudos of all my multi chapter works. Honourary mention to Guns and Roses though, because it has 101 kudos (as of 12th july 2021) for a fandom that's 50ish people big + for a pairing that literally doesn't even exist

Actual worst part of writing: getting started. GETTING STARTED. GOOD GOD HOW THE FUCK DO YOU OPEN A STORY "ONCE UPON A TIME THERE WAS A BITCH WHO NEEDED THERAPY" IS LOOKING REAL GOOD RN. Also the build up to the main event. No I don't want to write the drive home or the ensuing boring ass conversation. But I have to :(

How you choose your titles: I don't choose my titles as much as they choose me to be honest. They just sort of come to me, I'm pretty good w naming things that way. Most of the time it's a line/word from the fic. Sometimes it's a pun.

do you outline: kinda yes, kinda no. I definitely have a plot in mind and I never deviate from that (or if I do I make sure it's more a short detour than a whole new tangent), that way I avoid plot holes or discontinuity or writing too much/too little. But that's still very surface level, the real writing flows completely free.. Chapters are never outlined, save for what I have in mind. I've found I write better when given the freedom to let my characters do whatever tf they want. So yeah. It isn't so much an outline as a synopsis, really.

ideas you probably won't get around to, but wouldn't it be nice: five stages chapter 5- jk jk I'm doing it I promise, I have a 100 bullet point outline already I just need to get around to writing it. I think I'll say: smzs sun/moon au. Kartik as the Sun, Aman as the Moon, it's about the sun and the moon only getting to meet for two weeks in spring (y'know, when you see the sun and the moon in the sky together? yeah that) the fic is centered around one of those two week intervals before they're forced apart by duty again. It's absolutely beautiful, whatever I have of it, but I don't see myself completing it or posting it to be honest. hm.

callouts @ me: 1) enough with the fucking commas madarchod good god 2) no, waiting for the fic to just come to you is not a good strategy, employ a writing schedule or else 3) IT DOESN'T!!! NEED!!! TO BE PERFECT!!! 4) write for yourself more. Stop refreshing the page to see how many kudos you got. It doesn't matter. 5) you............. r e a l l y enjoy pushing the poetic license there do you NOT

best writing traits: I absolutely nail characterisation, I tend to understand characters inside out and the phrase "out of character" ain't even in my vocab <3 also I've been told that the way i do imagery/the way I describe things is very vivid, makes the reader feel like they're watching it happen. My dialogues flow naturally. And I use pretentious words like I'm fluent in superiority complex. So that's nice

Also, as a personal fave, I tend to add humour everywhere in a way that it blends seamlessly with the plot at hand :D I like to employ the full human emotional spectrum to keep my readers on their toes always ❤︎

spicy tangential option: All the sax and violins I write (especially the latter) is a... significantly watered down version of what plays out in my head :)

(W. Was that it. Was that what this question meant-)

Tagging: ok all the multi chapter writers i know have already been tagged so I'm just gonna say: anyone who wants to do this <3

[epicstuckyficrecs]

How To Tag Stucky Fics Part 2: Rating, Warnings, Fandoms, Relationships & Characters

Preliminary comments

First thing you can do to understand the tagging system better is read the Tag FAQ on ao3! And while you’re at it, I would suggest you also read the Wrangling Guidelines. Understanding the work of tag wranglers might help you to tag your fics better!

My aim with this is to encourage you to better use AO3’s tagging system, which means using canonical tags (especially for Fandom, Relationship and Character tags), VS unfilterable tags, which is everything else that’s not “canonical”. But any tag is better than no tags! Long, rambly tags are better than no tags! That’s what we have tag wranglers for! But my opinion is: if there’s a canonical tag for something, why not use it?

Tagging is an art, not a science. There are pretty much no absolute rules, which means that ao3′s tagging system’s biggest advantage is also its biggest disadvantage: you can do whatever you want with it! You can tag as much or as little as you want.

Every fandom has its quirks and odds about tagging. I’m obviously focused on Stucky, and incidentally on the MCU, so what I say here might not necessarily apply to all ships/fandoms.

As such, my opinion is as good as anyone else’s. I’m not pretending to be the utmost authority on how to tag: I’m just trying make good use of my experience to give writers some recommendations! These are my personal recommendations and in no way are you forced to follow them.

Tagging 101

Tagging has two main purposes, so you should keep them in mind while filling out the New Work form:

Content: tagging the content of your fics, so that readers who want to read said content can find your fic!

Trigger warnings: tagging content in your fics that people might want to avoid.

Basic rules of tagging (to help facilitate the job of our amazing tag wranglers):

Separate your Fandom, Relationship and Character tags by commas! There should be only one item per tag.

Always Use The Characters’ Last Name. Here’s why. Basically, when you tag with “Cute Peter”, wranglers have no idea if you’re talking about Peter Parker or Peter Quill or any of the hundreds of Peter characters in the Archive!

Personally, I would avoid using emojis in tags.

Some of these recommendations (and more) can be found in this AO3 News post under the How To Make Tags Work For You header.

Just read this post if you have no idea how tag wranglers actually wrangle tags. It’s really informative. The TLDR is: your tags are seen with no context, mixed in with all the other tags of other works in the wrangling interface, which are sorted by alphabetical order. Wranglers don’t know which tags in their workload belong to the same fic. Rambly Tumblr-style tags are fine, but just remember: “each tag stands on its own in the filters. Think about the idea you’re expressing in each tag, not just in your tags as a whole conversation. (...) Don’t assume that sarcasm, hyperbole, etc. will come through during sorting.”

Without further ado, let’s get into the actual tagging! I’m gonna be following the same order as if you were posting a new fic on AO3 :)

RATING & ARCHIVE WARNINGS

You can click on the little interrogation point in the form if you don’t know exactly how to tag for rating and warnings or check out the Ratings and Warnings section of AO3’s Terms of Service and FAQ. The only thing I would advise you here is to be as truthful as possible.

One important thing to know would be the difference between “No archive warnings apply” and “Choose not to use archive warnings”. From ao3commentoftheday:

“No Archive Warnings Apply means that the fic has nothing in it that people need to be warned about.

Choose Not to Use Archive Warnings means that the fic very well might include things that people would like to be warned about, but the author has decided not to warn because that warning might spoil the story (or for some other reason)”.

If you decide to use Choose Not to Use Archive Warnings, here’s a few options so your readers can still make an informed decision about reading your fic:

Additional Tags: You could use the Additional Tags field to list any details about what might be triggering in your work. For example, if there is a Character Death, but you don’t want to tag with MCD because it’s not a Major character, or it’s only temporary… There are tags for that! There was a great discussion on this post re: warnings vs spoilers on ao3commentoftheday, if you wanna go have a look!

Tag + Author’s note: Another thing you could do is use the “Additional Warnings In Author’s Note” tag and, as the tag says, give more details to your readers about triggers or warnings in your author’s note.

Author’s note (at the beginning) + End note (at the end): You could also write down a small summary of the triggering parts of your chapter in the end notes, and direct your readers to it in an Author’s Note at the beginning of the chapter.

FANDOMS

As I said above, please use the canonical Fandom tags!

MCU

I would really recommend you read this post on how to tag in the MCU! But basically, you should mainly be tagging your fic with Captain America (Movies) (or whatever movie is the focus of your story!). No need to use more general tags like Marvel (Movies) or Marvel Cinematic Universe ON TOP of your movie tag, it’ll show up there anyway! (If you’re writing RPF, use the Marvel Cinematic Universe RPF tag)

Always remember that if you’re tagging more than one fandom, you need to separate them with a comma! Don’t use a slash / or “and” (like “Captain America/Game of Thrones” or “Captain America and Game of Thrones”), or else it will create a single new tag that will be impossible to wrangle.

If your fic prominently features events or characters from other MCU movies (like The Avengers, Black Panther, etc.) or TV shows (like Agents of SHIELD or the Defenders), you could even add those fandom tags! But I wouldn’t if it’s not a major part of the plot though. For example: if Scott Lang is Bucky’s roommate, I would probably not tag with the “Ant-Man (Movies)” fandom tag. Ask yourself: would someone looking for Ant-Man fic want to find mine?

In fact, you can mostly apply this strategy to the whole tagging process. Ask yourself: would someone looking for xyz want to find my fic?

Also note that tagging multiple fandoms in the MCU will not make your work a Crossover according to AO3’s search engine, since they are under the same Metatag (aka Marvel Cinematic Universe). But if you tag another fandom in the bigger Marvel Metatag (for example, “Winter Soldier (Comics)”), then it will be considered a Crossover. (according to AO3: “Crossovers are defined here as works with at least two unrelated fandom tags, as determined by how tags are wrangled.”).

Alternate Universe

If your fic is an AU taking place in a non-MCU fandom (for example, Harry Potter), it’s up to you to decide how you want to tag.

Fandoms: You could add the Harry Potter - J. K. Rowling tag to the Fandoms field. Be aware that your fic will be considered a Crossover according to AO3’s search engine.

Additional Tags: there’s a few different ways to tag for AUs.

Alternate Universe - “...” : for example Alternate Universe - Hogwarts

Alternate Universe - “...” Setting: for example Alternate Universe - Harry Potter Setting

Alternate Universe - “...” Fusion (or just Alternate Universe - Fusion): for example Alternate Universe -The Little Mermaid Fusion, because there wasn’t a HP fusion tag in this case lol (according to Fanlore: “A fusion is a type of fanwork which merges two or more fandoms by incorporating characters from one fandom into the setting of another as if they had always been there.”) (emphasis mine)

In any case, you could also add the Crossover tag to the Additional Tags field (according to Fanlore: “A crossover is a fanfic in which two or more fandoms are combined in some way.”)

You could do all or neither of those things! As always, there are pretty much no absolute rules when it comes to tagging. But again, the more accurate you are, the better it is for your readers! :)

Now, I’ve used Harry Potter as an example, but there are a ton of Alternate Universe tags (under the Sub Tags section) for a great number of fandoms!

RELATIONSHIPS

From AO3’s FAQ: “The significant character interactions in the work, including romantic and/or sexual relationships and pairings (indicated in the Archive's canonical tags by a '/', e.g., James “Bucky” Barnes/Steve Rogers) and platonic relationships such as friendship, family, teammates, etc. (indicated in canonical tags by a '&', e.g., Steve Rogers & Natasha Romanoff). You can use whatever notation you like. Separate different relationships with commas.” (emphasis mine, and I changed the pairings for MCU ones)

So basically:

romantic or sexual = use the slash  /

platonic = use the ampersand &

I would recommend:

To only tag the one (1) main relationship in your fic (whether romantic or platonic), unless your story really focuses on more than one sexual/platonic relationship or contains a graphic scene depicting the second couple.

Any side pairings/friendships can go in the Additional Tags field (for example: Minor Clint Barton/Natasha Romanov or Steve Rogers & Bucky Barnes Friendship).

Ask yourself: would someone looking for xyz want to find my fic? Or as ao3commentoftheday put it: “Only tag the ship if someone who ships it would be satisfied with the amount of content (or ‘screen time’) that ship gets.”

Here’s a good post on what not to do when tagging Relationships. Namely:

Using portmanteau ship names like “Stucky”, or

Tagging multiple variations of the way you can refer to one ship, like: “Steve/Bucky”, “Steve Rogers/Bucky Barnes”, “Steven Grant Rogers/James Buchanan Barnes”.

It’s not a huuuuge deal since they’ll just get synned to the James “Bucky” Barnes/Steve Rogers tag, but there’s no need to use all of those when you have a perfectly good canon tag!

Again, there are no rules on how you need to tag your works. I would only recommend that you use canon tags as much as possible and that you choose the tag(s) that accurately represent your story.

CHARACTERS

Use canonical tags! I was incredibly surprised when I searched for the character “Steve Roger” in the Tag Search and ended up with almost 600 tags. The Characters field is not the place for “Mention of Steve Rogers”, “Pre-Serum Steve Rogers”, “Alpha Steve Rogers”, etc. Those should go in the Additional Tags, or else any modifier you’ve put before “Steve Rogers” will be useless, since it’ll just get synned to the Steve Rogers character tag.

Also sounds obvious but… don’t tag Relationships in the Characters field. And vice-versa.

Only tag the main characters! Try to keep it to a minimum. Don’t tag every single character that appears in your fic! (especially if there are a lot of them  and most only make a short appearance)

If you’re not sure who to tag, ask yourself: if someone reads my fic, in a year’s time, who would they remember being in there?

In any case, if you want to mention a character without putting it in the Characters field, you can use the Additional Tags!

Stay tuned for Part 3 where I’ll be tackling the Additional Tags! :D

See Part 1: A Comprehensive List of Stucky Tags here.

See Part 3: Additional Tags here! 

You can also access a handy bullet-point checklist that summarizes these posts on Google Docs here! 

[free-clickfunnels-blog]

What are ClickFunnels [2020]

“Imagine An Easy Way To Create Your Own High-Converting Marketing Funnels…”

(And You Don’t Pay Them ANYTHING Until AFTER They Close The Sale!)

“Let Me Show You How Easy It Is To Set Up Sales And Marketing Funnels Inside ClickFunnels…”

ClickFunnels Makes It SIMPLE For Any Entrepreneur To Build Their Own Marketing Funnels In Just Minutes…Without Needing To Rely On A Team Of Techies!

Have You Ever Felt Handcuffed As An Entrepreneur?

You know what I’m talking about.

You get an idea for a new product or service that you’d like to sell…

You have the idea…

You see the vision…

You know exactly what you need to do!

You know how this THING will change the world…

And then…

You’re Stuck…

This Is Why THOUSANDS Of Entrepreneurs Are Currently Taking POWER BACK FROM THE TECH GUYS!

… And Using ClickFunnels To Build And Launch Their Online Funnels FAST!

EVERYTHING You Need To Build And Launch Your Sales And Marketing Funnels In As Little As 10 Minutes!

Here’s How ClickFunnels Works…

ClickFunnels Gives You The Tools And Strategies You Need In One Convenient Spot!

Yes, ClickFunnels Gives You The Tools And Strategies You Need To Market, Sell, AND Deliver Your Products Online.

Simple Drag and Drop Webpage Editor!

​Quickly Build Sales Funnels That Convert!

​Smart Shopping Cart With 1 Click Upsells!

​Email and Facebook Marketing Automation!

​Everything Organized In One Simple Dashboard!

ClickFunnels Is A Website And Sales Funnel Builder For Entrepreneurs.

Yes, ClickFunnels was created so that entrepreneurs like you, who aren’t programmers and who don’t know how to code, can easily build beautiful pages inside of a sales funnel, to grow your company online! Let me show you it works:

Guide your visitors step-by-step through the entire sales process (so they don’t get confused or LOST, and leave)

​Point your customer straight to the ONE product or service they need most to help solve their problem

​Follow-up with your visitors, even after they leave your page!

Are you trying to sell stuff online?

Then I’m sure you will agree that converting your visitors into sales is hard.And getting yourself profitable? That’s even harder.But there’s a simple secret to increasing the value of every customer you sell to:

Sales funnels.

By creating a well-designed sales funnel, you can dramatically increase your sales and provide more value to your customers.

In today’s post, I’m going to introduce you to ClickFunnels, the software I’m using to build sales funnels for my business.

You don’t need any technical know-how to use it, and it will really change the way you run your business.

What Are ClickFunnels Software?

ClickFunnels was created back in 2014 by Russell Brunson and his team. Since that time, Russell has turned ClickFunnels into a $100 million a year business and they just crossed over 98,000 active members as of Dec 2019.

At its core, ClickFunnels is a software for building sales funnels. With a bunch of added bells and whistles.

You can use it to build:

Landing pages

Squeeze pages

Webinars

Membership sites

Every type of sales funnel you can think of, complete with order bumps, up sells and down sells

Basically you’re getting everything you need to market and sell your products online, without having to hire expensive developers.

And people are using ClickFunnels with great success.

If you check out their 2 Comma Club site, you can see that almost 300 entrepreneurs have already created 1 million dollar funnels using ClickFunnels.

That’s amazing!

How I started earning 5 figures a month with ClickFunnels

Back in 2015, I wanted to sell white-labeled logo design services and I was looking for an easy way to collect payments on one of my websites.

At the time, LeadPages was the popular software for building landing pages, but you couldn’t integrate payments with it, without some complicated hacks.

I felt super frustrated by this limitation, so I turned to a product I had been hearing some buzz about.

ClickFunnels.

And after getting a little experience with it, I wrote this initial review of ClickFunnels on the site.

It took several months, but it eventually started ranking for a bunch of ClickFunnels related keywords and this post started to get steady traffic.

But the weird thing was, is that none of this traffic was turning into affiliate commissions. I’m embarrassed to say that for the first 2 years after this post was published, I earned $0 from ClickFunnels.

This was incredibly frustrating, and to this day I’m still puzzled by why I couldn’t have earned at least a few random ClickFunnels commissions.

Because of this lack of success, I put ClickFunnels on the back-burner and focused on other things.

Do You Have A Guarantee?

Of course. :)

I guarantee that you’ll LOVE ClickFunnels…

and if for some reason you decide later that you don’t want to be a member anymore, then we’ll happily cancel your account without any penalties or fees.

So, Are You Excited About ClickFunnels Yet? :)

If So, Then NOW Is The Time To Take Action!

Here’s What You Need To Do Right Now:

Let Me Show You Everything You’ll Get Today When You Join ClickFunnels Today!

— — — — — — — LIMITED TIME OFFER — — — — — — —

ClickFunnels 30 Day Free Trial

Looking for a the longest possible Clickfunnels Free Trial?

Get ClickFunnels Free trial 30 day.

You can Try this 30 days ClickFunnels with this link.

Open this link and SignUp

you’ll get 30 day trial if you check your ClickFunnels account settings .

Tag : What are ClickFunnels , What is ClickFunnels …

[tonkidenver]

Magic calculator stocks

Magic calculator stocks how to#

Magic calculator stocks android#

Magic calculator stocks license#

Yes, you will receive updates and also improvements and new functionalities in the future, that we will add to the app. Magic Formula Investing: A money-making strategy that teaches investors a common-sense method for value investing in the stock market that is designed to beat the market's average annual returns. We recommend that you practice on your phone and then do it on a borrowed phone.

Magic calculator stocks android#

Yes, you can use PULSE on your own phone, on Android or iPhone. PULSE is ideal for beginners and workers because you can create routines to any situation, from a small group of people to a large stage.ĬAN I PERFORM THE TRICKS WITH MY OWN PHONE?

Magic calculator stocks how to#

In the instructions video you will see how to quickly remove traces before returning the phone to the spectator.ĬAN I PERFORM PULSE ON CLOSE UP, PARLOR or STAGE? HOW CAN I REMOVE TRACES ON THE SPECTATOR'S PHONE? You just have to take care of the angles so that they do not see you when you enter the URL or use your NFC, this will take you only a few seconds.In the instructional video, you will see how to do it under the spectator's noses. Yes, everyone can see the iPhone calculator after you have put it on home screen, it will take you 2 seconds, since it's exactly the same as the real iPhone calculator.On Android phones, you can always perform the routines from your phone, but in the instructions, you will see how to use Pulse in case your spectators have Android. Pulse works on all iOS and Android phones. You can configure your decimal format, with the dots or commas, and also the date format according to your region. All retirement calculators require the same basic inputs to work their magic your. You will be able to learn how to use all of its features very quickly, you will be amazed at how easy to use and how powerful Pulse is. Assumptions Required To Estimate How Much Money You Need To Retire. You will be able to configure Pulse in just a few seconds, since it does not have complicated configurations.

Magic calculator stocks license#

You will receive a physical license that will allow you to activate the app for life.Inside the package you will find INCLUDED an NFC chip, you will need to watch the instructions inside the app to know how to use it. Pulse will auto-detect what type of phone your spectator has, and will show an identical iPhone calculator or a modern Android calculator.In the instructional video you will learn how to perform it. will be able to ERASE and REAPPEAR numbers, even with the spectator's calculator, in a unique and very simple way. You can perform the toxic trick (force any number on any phone), at any time, in a much easier and safer way, without the probability of failure.Īlso, you can force the date and time with the exact minutes in which you are automatically!, the app will do everything for you!Īnd. Just by using an NFC tag (INCLUDED!) that you can palm, or also, by entering a small URL in front of the noses of your spectators! PULSE is a magic calculator that you can use on your phone or even better, on the spectator's phones very easily.

[michaelandy101-blog]

10 Steps to Mix STAT Rating Information with Web site Efficiency Metrics

New Post has been published on http://tiptopreview.com/10-steps-to-blend-stat-ranking-data-with-site-performance-metrics/

10 Steps to Mix STAT Rating Information with Web site Efficiency Metrics

Too usually, we assume that search engine marketing greatest practices will work in any trade towards any aggressive set. However most greatest practices go untested and will not be “best” in each scenario.

Everyone knows that ways that labored in 2020 gained’t essentially transfer the needle in 2021 as Core Internet Vitals (CWV) and different alerts shuffle to the entrance. We’ve got to do higher for our companies and our purchasers.

I’m an information nerd at coronary heart with numerous battle scars from 15 years in search engine marketing. The concept of analyzing hundreds of native SERPs gave the impression of an excessive amount of enjoyable to cross up. I discovered some stunning correlations, and simply as importantly, constructed a strategy and information set that may be up to date quarterly to indicate adjustments over time.

I analyzed 50,000+ SERPs within the retail banking sector so I might make sense of the large shifts in rankings and search behaviors in the course of the lockdown interval. We’ve got a whole lot of historic information for financial institution web sites, so evaluating pre/put up COVID information could be simpler than ranging from scratch.

I’ll share how I did it beneath. However first, I need to share WHY I feel sharing this kind of analysis is so necessary for the search engine marketing group.

Why validate search engine marketing greatest practices with information?

It’s a good time to be an search engine marketing. We’ve got wonderful instruments and might collect extra information than ever. We’ve got thriving communities and wonderful basic training materials.

But, we regularly see our craft distilled into overly-simplified “best practices” which can be assumed to be universally true. But when there’s one common fact in search engine marketing, it’s that there are not any common truths. A greatest follow might be misinterpreted or outdated, resulting in missed alternatives or outright hurt to a enterprise.

Utilizing the rising significance of CWV for example, SEOs have a possibility (and obligation) to separate truth from fiction. We have to know if, and by how a lot, CWV will impression rankings over time so we are able to prioritize our efforts.

We are able to elevate our search engine marketing sport individually and collectively by testing and validating greatest practices with analysis. It simply takes a curious thoughts, the suitable instruments, and a willingness to simply accept the outcomes reasonably than drive a story.

Failing to validate greatest practices is a legal responsibility for search engine marketing practitioners and reveals an unwillingness to problem assumptions. In my expertise, an absence of knowledge can result in a senior stakeholders’ opinions carrying extra weight than an search engine marketing professional’s suggestions.

Begin by asking the suitable questions

Actual perception comes from combining information from a number of sources to reply crucial questions and guarantee your methods are backed by legitimate information. In my evaluation of native banks, I began by itemizing the questions I needed to know the solutions to:

What traits are shared by top-ranking native financial institution web sites?

Who’re banks really competing towards within the SERPs? Is it primarily different banks?

How do aggressive SERPS change primarily based on when/the place/how customers search?

How can smaller, native companies acquire an edge over bigger rivals from outdoors their area?

How does SERP composition have an effect on a financial institution’s capacity to rank properly for focused key phrases?

How necessary are Core Internet Vitals (CWV) for rankings? How does this variation over time?

You could possibly run this identical evaluation by changing “banks” with different native enterprise classes. The listing of potential questions is infinite so you’ll be able to regulate them primarily based in your wants.

Right here’s an necessary reminder – be ready to simply accept the solutions even when they’re inconclusive or contradictory to your assumptions. Information-driven SEOs need to keep away from affirmation bias if we’re going to stay goal.

Right here’s how I analyzed 50,000 search ends in just a few hours

I mixed three of my favourite instruments to research SERPs at scale and collect the information wanted to reply my questions:

STAT to generated rating stories for choose key phrases

Screaming Frog to crawl web sites and collect technical search engine marketing information

Power BI to research the massive information units and create easy visualizations

Step 1: Decide your information wants

I used US Census Bureau information to establish all cities with populations over 100,000, as a result of I needed a illustration of native financial institution SERPs throughout the nation. My listing ended up together with 314 separate cities, however you could possibly customise your listing to fit your wants.

I additionally needed to collect information for desktop and cellular searches to check SERP variations between the machine sorts.

Step 2: Determine your key phrases

I selected “banks near me” and “banks in city, st” primarily based on their robust native intent and excessive search volumes, in comparison with extra particular key phrases for banking providers.

Step three: Generate a STAT import file in .csv format

After you have your key phrases and market listing, it’s time to prepare the bulk upload for STAT. Use the template supplied within the hyperlink to create a .csv file with the next fields:

Venture: The identify of the brand new STAT mission, or an current mission.

Folder: The identify of the brand new folder, or an current folder. (That is an non-obligatory column that you would be able to depart clean.)

Web site: The area identify for the location you need to observe. Word, for our functions you’ll be able to enter any URL you need to observe right here. The Prime 20 Report will embrace all rating URLs for the goal key phrases even when they aren’t listed in your “Site” column.

Key phrase: The search question you’re including.

Tags: Enter as many key phrase tags as you need, separated by commas. I used “city” and “near me” as tags to tell apart between the question sorts. (That is an non-obligatory column that you would be able to depart clean.)

Market: Specify the market (nation and language) wherein you wish to observe the key phrase. I used “US-en” for US English.

Location: If you wish to observe the key phrase in a particular location, specify town, state, province, ZIP code, and/or postal code. I used town and state listing in “city, st” format.

Gadget: Choose whether or not you desire to Desktop or Smartphone outcomes. I chosen each.

Every market, location, and machine kind will multiply the variety of key phrases you need to observe. I ended up with 1,256 key phrases (314 markets X 2 key phrases X 2 units) in my import file.

As soon as your file is full, you’ll be able to import to STAT and start monitoring.

Step Four: Run a Prime 20 Report in STAT for all key phrases

STAT’s built-in Google SERP Top 20 Comparison report captures the highest 20 natural outcomes from every SERP at completely different intervals (every day, weekly, month-to-month, and so on.) to have a look at adjustments over time. I didn’t want every day information so I merely let it run on two consecutive days and eliminated the information I didn’t want. I re-run the identical report quarterly to trace adjustments over time.

Watch the video beneath to discover ways to arrange this report! 

My 1,256 key phrases generated over 25,000 rows of knowledge per day. Every row is a unique natural itemizing and consists of the key phrase, month-to-month search quantity, rank (consists of the native pack), base rank (doesn’t embrace the native pack), https/http protocol of the rating URL, the rating URL, and your tags.

Right here’s an instance of the uncooked output in CSV format:

It’s simple to see how helpful this information is by itself but it surely turns into much more highly effective after we clear it up and begin crawling the rating URLs.

Step 5: Clear up and normalize your STAT URLs information

At this level you could have invested 1-2 hours in gathering the preliminary information. This step is a little more time consuming, however information cleaning lets you run extra superior evaluation and uncover extra helpful insights in Screaming Frog.

Listed below are the adjustments I made to the STAT rankings information to organize for the following steps in Screaming Frog and Energy BI. You’ll find yourself with a number of columns of URLs. Every serves a objective later.

Duplicate the Rating URL column to a brand new column referred to as Normalized URL.

Take away URL parameters from the Normalized URL fields through the use of Excel’s text to columns instrument and separating by “?”. I deleted the brand new columns(s) containing the URL parameters as a result of they weren’t useful in my evaluation.

Duplicate the brand new, clear Normalized URL column to a brand new column referred to as TLD. Use the textual content to columns instrument on the TLD column and separate by “/” to take away the whole lot besides the area identify and subdomains. Delete the brand new columns. I selected to maintain the subdomains in my TLD column however you’ll be able to take away them if it helps your evaluation.

Lastly, create yet another column referred to as Full URL that may ultimately develop into the listing of URLs that you simply’ll crawl in Screaming Frog. To generate the Full URL, merely use Excel’s concatenate function to mix the Protocol and Normalized URL columns. Your system will look one thing like this: =concatenate(A1, “://”, C1) to incorporate the “://” in a legitimate URL string.

The 25,000+ rows in my information set are properly inside Excel’s limitations, so I’m able to manipulate the information simply in a single place. Chances are you’ll want to make use of a database (I like BigQuery) as your information units develop.

Step 6: Categorize your SERP outcomes by web site kind

Skimming by way of the SERP outcomes, it’s simple to see that banks should not the one kind of web site that rank for key phrases with native search intent. Since considered one of my preliminary questions was SERP composition, I needed to establish all the various kinds of web sites and label each for additional evaluation.

This step is by far probably the most time consuming and insightful. I spent three hours categorizing the preliminary batch of 25,000+ URLs into one of many following classes:

Establishment (banks and credit score union web sites)

Listing (aggregators, native enterprise directories, and so on.)

Evaluations (native and nationwide websites like Yelp.com)

Schooling (content material about banks on .edu domains)

Authorities (content material about banks on .gov domains and municipal websites)

Jobs (careers websites and job aggregators)

Information (native and nationwide information websites with banking content material)

Meals Banks (sure, loads of meals banks rank for “banks near me” key phrases)

Actual Property (industrial and residential actual property listings)

Search Engines (ranked content material belonging to a search engine)

Social Media (ranked content material on social media websites)

Different (utterly random outcomes not associated to any of the above)

Your native SERPs will seemingly comprise many of those web site sorts and different unrelated classes reminiscent of meals banks. Pace up the method by sorting and filtering your TLD and Normalized URL columns to categorize a number of rows concurrently. For instance, all of the yelp.com rankings might be categorized as “Reviews” with a fast copy/paste.

At this level, your rankings information set is full and you might be prepared to start crawling the top-ranking websites in your trade to see what they’ve in widespread.

Step 7: Crawl your goal web sites with Screaming Frog

My preliminary STAT information recognized over 6,600 distinctive pages from native financial institution web sites that ranked within the high 20 natural search outcomes. That is far too many pages to judge manually. Enter Screaming Frog, a crawler that mimics Google’s net crawler and extracts tons of search engine marketing information from web sites.

I configured Screaming Frog to crawl every of the 6,600 rating pages for a bigger evaluation of traits shared by top-ranking financial institution web sites. Don’t simply let SF free although. Make sure to configure it correctly to avoid wasting time and keep away from crawling pointless pages.

These settings guarantee we’ll get all the information we have to reply our questions in a single crawl:

List Mode: Paste in a de-duplicated Full URL listing out of your STAT information. In my case, this was 6,600+ URLs.

Database Storage Mode: It might be a bit slower than Reminiscence (RAM) Storage, however saving your crawl outcomes in your arduous disk ensures you gained’t lose your outcomes in case you make a mistake (like I’ve many occasions) and shut your report earlier than you end analyzing the information.

Limit Crawl Depth: Set this to zero (zero) so the spider will solely crawl the URLs in your listing with out following inside hyperlinks to different pages on these domains.

APIs: I extremely advocate utilizing the Pagespeed Insights Integration to drag Lighthouse velocity metrics immediately into your crawl information. When you’ve got a Moz account with API entry, you too can pull hyperlink and area information from the Moz API with the built-in integration.

After you have configured the spider, let it rip! It might take a number of minutes to a number of hours relying on what number of URLs you’re crawling and your laptop’s velocity and reminiscence constraints. Simply be affected person! You may strive working bigger crawls in a single day or on an additional laptop to keep away from bogging your major machine down.

Step eight: Export your Screaming Frog crawl information to Excel

Dumping your crawl information into Excel is remarkably simple.

Step 9: Be part of your information units in Energy BI

At this level, you need to have two information sources in Excel: one to your STAT rankings information and one other to your Screaming Frog crawl information. Our objective is to mix the 2 information sources to see how natural search rank could also be influenced by on-page search engine marketing parts and web site efficiency. To do that, we should first merge the information.

When you’ve got entry to a Home windows PC, the free version of Power BI is highly effective sufficient to get you began. Start by loading your two data sources into a new project utilizing the Get Information wizard.

As soon as your information units are loaded, it’s time to make the magic occur by creating relationships in your data to unlock correlations between rankings and web site traits. To mix your information in Energy BI, create a many-to-many relationship between your STAT Full URL and Screaming Frog Authentic URL fields. 

If you’re new to BI instruments and information visualization, don’t fear! There are many useful tutorials and movies only a fast search away. At this level, it’s actually arduous to interrupt something and you’ll experiment with numerous methods to research your information and share insights with many kinds of charts and graphs.

I ought to be aware that Energy BI is my most popular information visualization instrument however you could possibly use Tableau or some equally highly effective. Google Information Studio was not an possibility for this evaluation as a result of it solely permits for left outer joins of the a number of information sources and doesn’t assist “many-to-many” relationships. It’s a technical approach of claiming Information Studio isn’t versatile sufficient to create the information relationships that we’d like.

Step 10: Analyze and visualize!

Energy BI’s built-in visualizations mean you can shortly summarize and current information. That is the place we are able to begin analyzing the information to reply the questions we got here up with earlier.

Outcomes — what did we be taught?

Listed below are a pair examples of the insights gleaned from merging our rankings and crawl information. Spoiler alert — CWV doesn’t strongly impression natural rankings….but!

Who’re banks really competing towards within the SERPs? Is it primarily different banks?

On desktops, about 67% of natural search outcomes belong to monetary establishments (banks and credit score unions) with heavy competitors from evaluations websites (7%) and online directories (22%). This data helps form our search engine marketing methods for banks by exposing alternatives to watch and preserve listings in related directories and evaluations websites.

Okay, now let’s mash up our information sources to see how the distribution of web site classes varies by rank on desktop units. Out of the blue, we are able to see that monetary establishments really occupy nearly all of the highest three outcomes whereas evaluations websites and directories are extra prevalent in positions Four-10.

How necessary are Core Internet Vitals (CWV) for rankings? How does this variation over time?

Web site efficiency and web site velocity are sizzling subjects in search engine marketing and can solely develop into extra necessary as CWV turns into a rating sign in Could this yr. We are able to start to know the relationships between web site velocity and rankings by evaluating STAT rankings and Pagespeed Insights information from Screaming Frog stories.

As of January 2021, websites with greater Lighthouse Performance Scores (i.e. they load sooner) are inclined to rank higher than websites with decrease scores. This might assist justify investments in web site velocity and web site efficiency.

Some CWV parts correlate extra intently with higher rankings and others are extra scattered. This isn’t to say CWV aren’t necessary or significant, however reasonably it’s a place to begin for additional evaluation after Could.

So what? What can we be taught from this kind of evaluation?

Individually, STAT and Screaming Frog are extremely highly effective search engine marketing instruments. The information they supply are helpful in case you occur to be an search engine marketing however the capacity to merge information and extract relationships will multiply your worth in any group that values information, and acts on insights.

Moreover validating some typically accepted search engine marketing information with information (“faster sites are rewarded with better rankings”), higher use of relational information may assist us keep away from spending precious time on much less necessary ways (“improve Cumulative Layout Shift at all costs!”).

In fact, correlation doesn’t indicate causation, and aggregated information doesn’t assure an final result for particular person websites. However in case you’re a financial institution marketing skilled answerable for buyer acquisition from natural channels, you’ll must deliver this kind of information to your stakeholders to justify elevated investments in search engine marketing.

By sharing the instruments and methodology, I hope others will take it additional by constructing and contributing their further findings to the search engine marketing group. What different datasets can we mix to deepen our understanding of SERPs on a bigger scale? Let me know your ideas within the feedback!

Source link

[epackingvietnam]

10 Steps to Blend STAT Ranking Data with Site Performance Metrics

Posted by AndrewMiller

Too often, we assume that SEO best practices will work in any industry against any competitive set. But most best practices go untested and may not be “best” in every situation.

We all know that tactics that worked in 2020 won’t necessarily move the needle in 2021 as Core Web Vitals (CWV) and other signals shuffle to the front. We have to do better for our businesses and our clients.

I’m a data nerd at heart with lots of battle scars from 15 years in SEO. The idea of analyzing thousands of local SERPs sounded like too much fun to pass up. I found some surprising correlations, and just as importantly, built a methodology and data set that can be updated quarterly to show changes over time.

I analyzed 50,000+ SERPs in the retail banking sector so I could make sense of the massive shifts in rankings and search behaviors during the lockdown period. We have a lot of historical data for bank websites, so comparing pre/post COVID data would be easier than starting from scratch.

I’ll share how I did it below. But first, I want to share WHY I think sharing this type of research is so important for the SEO community.

Why validate SEO best practices with data?

It’s a great time to be an SEO. We have amazing tools and can gather more data than ever. We have thriving communities and excellent basic training materials.

Yet, we often see our craft distilled into overly-simplified “best practices” that are assumed to be universally true. But if there’s one universal truth in SEO, it’s that there are no universal truths. A best practice can be misinterpreted or outdated, leading to missed opportunities or outright harm to a business.

Using the increasing importance of CWV as an example, SEOs have an opportunity (and obligation) to separate fact from fiction. We need to know if, and by how much, CWV will impact rankings over time so we can prioritize our efforts.

We can elevate our SEO game individually and collectively by testing and validating best practices with research. It just takes a curious mind, the right tools, and a willingness to accept the results rather than force a narrative.

Failing to validate best practices is a liability for SEO practitioners and shows an unwillingness to challenge assumptions. In my experience, a lack of data can lead to a senior stakeholders’ opinions carrying more weight than an SEO expert’s recommendations.

Start by asking the right questions

Real insight comes from combining data from multiple sources to answer critical questions and ensure your strategies are backed by valid data. In my analysis of local banks, I started by listing the questions I wanted to know the answers to:

What characteristics are shared by top-ranking local bank websites?

Who are banks actually competing against in the SERPs? Is it primarily other banks?

How do competitive SERPS change based on when/where/how users search?

How can smaller, local businesses gain an edge over larger competitors from outside their region?

How does SERP composition affect a bank’s ability to rank well for targeted keywords?

How important are Core Web Vitals (CWV) for rankings? How does this change over time?

You could run this same analysis by replacing “banks” with other local business categories. The list of potential questions is endless so you can adjust them based on your needs.

Here’s an important reminder - be prepared to accept the answers even if they are inconclusive or contradictory to your assumptions. Data-driven SEOs have to avoid confirmation bias if we’re going to remain objective.

Here’s how I analyzed 50,000 search results in a few hours

I combined three of my favorite tools to analyze SERPs at scale and gather the data needed to answer my questions:

STAT to generated ranking reports for select keywords

Screaming Frog to crawl websites and gather technical SEO data

Power BI to analyze the large data sets and create simple visualizations

Step 1: Determine your data needs

I used US Census Bureau data to identify all cities with populations over 100,000, because I wanted a representation of local bank SERPs across the country. My list ended up including 314 separate cities, but you could customize your list to suit your needs.

I also wanted to gather data for desktop and mobile searches to compare SERP differences between the device types.

Step 2: Identify your keywords

I chose “banks near me” and “banks in {city, st}” based on their strong local intent and high search volumes, compared to more specific keywords for banking services.

Step 3: Generate a STAT import file in .csv format

Once you have your keywords and market list, it’s time to prepare the bulk upload for STAT. Use the template provided in the link to create a .csv file with the following fields:

Project: The name of the new STAT project, or an existing project.

Folder: The name of the new folder, or an existing folder. (This is an optional column that you can leave blank.)

Site: The domain name for the site you want to track. Note, for our purposes you can enter any URL you want to track here. The Top 20 Report will include all ranking URLs for the target keywords even if they aren’t listed in your “Site” column.

Keyword: The search query you’re adding.

Tags: Enter as many keyword tags as you want, separated by commas. I used “city” and “near me” as tags to distinguish between the query types. (This is an optional column that you can leave blank.)

Market: Specify the market (country and language) in which you would like to track the keyword. I used “US-en” for US English.

Location: If you want to track the keyword in a specific location, specify the city, state, province, ZIP code, and/or postal code. I used the city and state list in “city, st” format.

Device: Select whether you would like Desktop or Smartphone results. I selected both.

Each market, location, and device type will multiply the number of keywords you must track. I ended up with 1,256 keywords (314 markets X 2 keywords X 2 devices) in my import file.

Once your file is complete, you can import to STAT and begin tracking.

Step 4: Run a Top 20 Report in STAT for all keywords

STAT’s built-in Google SERP Top 20 Comparison report captures the top 20 organic results from each SERP at different intervals (daily, weekly, monthly, etc.) to look at changes over time. I did not need daily data so I simply let it run on two consecutive days and removed the data I did not need. I re-run the same report quarterly to track changes over time.

Watch the video below to learn how to set up this report! 

My 1,256 keywords generated over 25,000 rows of data per day. Each row is a different organic listing and includes the keyword, monthly search volume, rank (includes the local pack), base rank (does not include the local pack), https/http protocol of the ranking URL, the ranking URL, and your tags.

Here’s an example of the raw output in CSV format:

It’s easy to see how useful this data is by itself but it becomes even more powerful when we clean it up and start crawling the ranking URLs.

Step 5: Clean up and normalize your STAT URLs data

At this point you may have invested 1-2 hours in gathering the initial data. This step is a bit more time consuming, but data cleansing allows you to run more advanced analysis and uncover more useful insights in Screaming Frog.

Here are the changes I made to the STAT rankings data to prepare for the next steps in Screaming Frog and Power BI. You’ll end up with multiple columns of URLs. Each serves a purpose later.

Duplicate the Ranking URL column to a new column called Normalized URL.

Remove URL parameters from the Normalized URL fields by using Excel’s text to columns tool and separating by “?”. I deleted the new columns(s) containing the URL parameters because they were not helpful in my analysis.

Duplicate the new, clean Normalized URL column to a new column called TLD. Use the text to columns tool on the TLD column and separate by “/” to remove everything except the domain name and subdomains. Delete the new columns. I chose to keep the subdomains in my TLD column but you can remove them if it helps your analysis.

Finally, create one more column called Full URL that will eventually become the list of URLs that you’ll crawl in Screaming Frog. To generate the Full URL, simply use Excel’s concatenate function to combine the Protocol and Normalized URL columns. Your formula will look something like this: =concatenate(A1, “://”, C1) to include the “://” in a valid URL string.

The 25,000+ rows in my data set are well within Excel’s limitations, so I am able to manipulate the data easily in one place. You may need to use a database (I like BigQuery) as your data sets grow.

Step 6: Categorize your SERP results by website type

Skimming through the SERP results, it’s easy to see that banks are not the only type of website that rank for keywords with local search intent. Since one of my initial questions was SERP composition, I had to identify all of the different types of websites and label each one for further analysis.

This step is by far the most time consuming and insightful. I spent 3 hours categorizing the initial batch of 25,000+ URLs into one of the following categories:

Institution (banks and credit union websites)

Directory (aggregators, local business directories, etc.)

Reviews (local and national sites like Yelp.com)

Education (content about banks on .edu domains)

Government (content about banks on .gov domains and municipal sites)

Jobs (careers sites and job aggregators)

News (local and national news sites with banking content)

Food Banks (yes, plenty of food banks rank for “banks near me” keywords)

Real Estate (commercial and residential real estate listings)

Search Engines (ranked content belonging to a search engine)

Social Media (ranked content on social media sites)

Other (completely random results not related to any of the above)

Your local SERPs will likely contain many of these website types and other unrelated categories such as food banks. Speed up the process by sorting and filtering your TLD and Normalized URL columns to categorize multiple rows simultaneously. For example, all the yelp.com rankings can be categorized as “Reviews” with a quick copy/paste.

At this point, your rankings data set is complete and you are ready to begin crawling the top-ranking sites in your industry to see what they have in common.

Step 7: Crawl your target websites with Screaming Frog

My initial STAT data identified over 6,600 unique pages from local bank websites that ranked in the top 20 organic search results. This is far too many pages to evaluate manually. Enter Screaming Frog, a crawler that mimics Google’s web crawler and extracts tons of SEO data from websites.

I configured Screaming Frog to crawl each of the 6,600 ranking pages for a larger analysis of characteristics shared by top-ranking bank websites. Don’t just let SF loose though. Be sure to configure it properly to save time and avoid crawling unnecessary pages.

These settings ensure we’ll get all the info we need to answer our questions in one crawl:

List Mode: Paste in a de-duplicated Full URL list from your STAT data. In my case, this was 6,600+ URLs.

Database Storage Mode: It may be a bit slower than Memory (RAM) Storage, but saving your crawl results on your hard disk ensures you won’t lose your results if you make a mistake (like I have many times) and close your report before you finish analyzing the data.

Limit Crawl Depth: Set this to 0 (zero) so the spider will only crawl the URLs on your list without following internal links to other pages on those domains.

APIs: I highly recommend using the Pagespeed Insights Integration to pull Lighthouse speed metrics directly into your crawl data. If you have a Moz account with API access, you can also pull link and domain data from the Moz API with the built-in integration.

Once you have configured the spider, let it rip! It could take several minutes to several hours depending on how many URLs you’re crawling and your computer’s speed and memory constraints. Just be patient! You might try running larger crawls overnight or on an extra computer to avoid bogging your primary machine down.

Step 8: Export your Screaming Frog crawl data to Excel

Dumping your crawl data into Excel is remarkably easy.

Step 9: Join your data sets in Power BI

At this point, you should have two data sources in Excel: one for your STAT rankings data and another for your Screaming Frog crawl data. Our goal is to combine the two data sources to see how organic search rank may be influenced by on-page SEO elements and site performance. To do this, we must first merge the data.

If you have access to a Windows PC, the free version of Power BI is powerful enough to get you started. Begin by loading your two data sources into a new project using the Get Data wizard.

Once your data sets are loaded, it’s time to make the magic happen by creating relationships in your data to unlock correlations between rankings and site characteristics. To combine your data in Power BI, create a many-to-many relationship between your STAT Full URL and Screaming Frog Original URL fields. 

If you are new to BI tools and data visualization, don’t worry! There are lots of helpful tutorials and videos just a quick search away. At this point, it’s really hard to break anything and you can experiment with lots of ways to analyze your data and share insights with many types of charts and graphs.

I should note that Power BI is my preferred data visualization tool but you may be able to use Tableau or some equally powerful. Google Data Studio was not an option for this analysis because it only allows for left outer joins of the multiple data sources and does not support “many-to-many” relationships. It’s a technical way of saying Data Studio isn’t flexible enough to create the data relationships that we need.

Step 10: Analyze and visualize!

Power BI’s built-in visualizations allow you to quickly summarize and present data. This is where we can start analyzing the data to answer the questions we came up with earlier.

Results — what did we learn?

Here are a couple examples of the insights gleaned from merging our rankings and crawl data. Spoiler alert — CWV doesn’t strongly impact organic rankings….yet!

Who are banks actually competing against in the SERPs? Is it primarily other banks?

On desktops, about 67% of organic search results belong to financial institutions (banks and credit unions) with heavy competition from reviews sites (7%) and online directories (22%). This information helps shape our SEO strategies for banks by exposing opportunities to monitor and maintain listings in relevant directories and reviews sites.

Okay, now let’s mash up our data sources to see how the distribution of website categories varies by rank on desktop devices. Suddenly, we can see that financial institutions actually occupy the majority of the top 3 results while reviews sites and directories are more prevalent in positions 4-10.

How important are Core Web Vitals (CWV) for rankings? How does this change over time?

Site performance and site speed are hot topics in SEO and will only become more important as CWV becomes a ranking signal in May this year. We can begin to understand the relationships between site speed and rankings by comparing STAT rankings and Pagespeed Insights data from Screaming Frog reports.

As of January 2021, sites with higher Lighthouse Performance Scores (i.e. they load faster) tend to rank better than sites with lower scores. This could help justify investments in site speed and site performance.

Some CWV elements correlate more closely with better rankings and others are more scattered. This isn’t to say CWV aren’t important or meaningful, but rather it’s a starting point for further analysis after May.

So what? What can we learn from this type of analysis?

Separately, STAT and Screaming Frog are incredibly powerful SEO tools. The data they provide are useful if you happen to be an SEO but the ability to merge data and extract relationships will multiply your value in any organization that values data, and acts on insights.

Besides validating some generally accepted SEO knowledge with data (“faster sites are rewarded with better rankings”), better use of relational data can also help us avoid spending valuable time on less important tactics (“improve Cumulative Layout Shift at all costs!”).

Of course, correlation does not imply causation, and aggregated data does not guarantee an outcome for individual sites. But if you’re a bank marketing professional responsible for customer acquisition from organic channels, you’ll need to bring this type of data to your stakeholders to justify increased investments in SEO.

By sharing the tools and methodology, I hope others will take it further by building and contributing their additional findings to the SEO community. What other datasets can we combine to deepen our understanding of SERPs on a larger scale? Let me know your thoughts in the comments!

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!

#túi_giấy_epacking_việt_nam #túi_giấy_epacking #in_túi_giấy_giá_rẻ #in_túi_giấy #epackingvietnam #tuigiayepacking

[bfxenon]

10 Steps to Blend STAT Ranking Data with Site Performance Metrics

Posted by AndrewMiller

Too often, we assume that SEO best practices will work in any industry against any competitive set. But most best practices go untested and may not be “best” in every situation.

We all know that tactics that worked in 2020 won’t necessarily move the needle in 2021 as Core Web Vitals (CWV) and other signals shuffle to the front. We have to do better for our businesses and our clients.

I’m a data nerd at heart with lots of battle scars from 15 years in SEO. The idea of analyzing thousands of local SERPs sounded like too much fun to pass up. I found some surprising correlations, and just as importantly, built a methodology and data set that can be updated quarterly to show changes over time.

I analyzed 50,000+ SERPs in the retail banking sector so I could make sense of the massive shifts in rankings and search behaviors during the lockdown period. We have a lot of historical data for bank websites, so comparing pre/post COVID data would be easier than starting from scratch.

I’ll share how I did it below. But first, I want to share WHY I think sharing this type of research is so important for the SEO community.

Why validate SEO best practices with data?

It’s a great time to be an SEO. We have amazing tools and can gather more data than ever. We have thriving communities and excellent basic training materials.

Yet, we often see our craft distilled into overly-simplified “best practices” that are assumed to be universally true. But if there’s one universal truth in SEO, it’s that there are no universal truths. A best practice can be misinterpreted or outdated, leading to missed opportunities or outright harm to a business.

Using the increasing importance of CWV as an example, SEOs have an opportunity (and obligation) to separate fact from fiction. We need to know if, and by how much, CWV will impact rankings over time so we can prioritize our efforts.

We can elevate our SEO game individually and collectively by testing and validating best practices with research. It just takes a curious mind, the right tools, and a willingness to accept the results rather than force a narrative.

Failing to validate best practices is a liability for SEO practitioners and shows an unwillingness to challenge assumptions. In my experience, a lack of data can lead to a senior stakeholders’ opinions carrying more weight than an SEO expert’s recommendations.

Start by asking the right questions

Real insight comes from combining data from multiple sources to answer critical questions and ensure your strategies are backed by valid data. In my analysis of local banks, I started by listing the questions I wanted to know the answers to:

What characteristics are shared by top-ranking local bank websites?

Who are banks actually competing against in the SERPs? Is it primarily other banks?

How do competitive SERPS change based on when/where/how users search?

How can smaller, local businesses gain an edge over larger competitors from outside their region?

How does SERP composition affect a bank’s ability to rank well for targeted keywords?

How important are Core Web Vitals (CWV) for rankings? How does this change over time?

You could run this same analysis by replacing “banks” with other local business categories. The list of potential questions is endless so you can adjust them based on your needs.

Here’s an important reminder - be prepared to accept the answers even if they are inconclusive or contradictory to your assumptions. Data-driven SEOs have to avoid confirmation bias if we’re going to remain objective.

Here’s how I analyzed 50,000 search results in a few hours

I combined three of my favorite tools to analyze SERPs at scale and gather the data needed to answer my questions:

STAT to generated ranking reports for select keywords

Screaming Frog to crawl websites and gather technical SEO data

Power BI to analyze the large data sets and create simple visualizations

Step 1: Determine your data needs

I used US Census Bureau data to identify all cities with populations over 100,000, because I wanted a representation of local bank SERPs across the country. My list ended up including 314 separate cities, but you could customize your list to suit your needs.

I also wanted to gather data for desktop and mobile searches to compare SERP differences between the device types.

Step 2: Identify your keywords

I chose “banks near me” and “banks in {city, st}” based on their strong local intent and high search volumes, compared to more specific keywords for banking services.

Step 3: Generate a STAT import file in .csv format

Once you have your keywords and market list, it’s time to prepare the bulk upload for STAT. Use the template provided in the link to create a .csv file with the following fields:

Project: The name of the new STAT project, or an existing project.

Folder: The name of the new folder, or an existing folder. (This is an optional column that you can leave blank.)

Site: The domain name for the site you want to track. Note, for our purposes you can enter any URL you want to track here. The Top 20 Report will include all ranking URLs for the target keywords even if they aren’t listed in your “Site” column.

Keyword: The search query you’re adding.

Tags: Enter as many keyword tags as you want, separated by commas. I used “city” and “near me” as tags to distinguish between the query types. (This is an optional column that you can leave blank.)

Market: Specify the market (country and language) in which you would like to track the keyword. I used “US-en” for US English.

Location: If you want to track the keyword in a specific location, specify the city, state, province, ZIP code, and/or postal code. I used the city and state list in “city, st” format.

Device: Select whether you would like Desktop or Smartphone results. I selected both.

Each market, location, and device type will multiply the number of keywords you must track. I ended up with 1,256 keywords (314 markets X 2 keywords X 2 devices) in my import file.

Once your file is complete, you can import to STAT and begin tracking.

Step 4: Run a Top 20 Report in STAT for all keywords

STAT’s built-in Google SERP Top 20 Comparison report captures the top 20 organic results from each SERP at different intervals (daily, weekly, monthly, etc.) to look at changes over time. I did not need daily data so I simply let it run on two consecutive days and removed the data I did not need. I re-run the same report quarterly to track changes over time.

Watch the video below to learn how to set up this report! 

My 1,256 keywords generated over 25,000 rows of data per day. Each row is a different organic listing and includes the keyword, monthly search volume, rank (includes the local pack), base rank (does not include the local pack), https/http protocol of the ranking URL, the ranking URL, and your tags.

Here’s an example of the raw output in CSV format:

It’s easy to see how useful this data is by itself but it becomes even more powerful when we clean it up and start crawling the ranking URLs.

Step 5: Clean up and normalize your STAT URLs data

At this point you may have invested 1-2 hours in gathering the initial data. This step is a bit more time consuming, but data cleansing allows you to run more advanced analysis and uncover more useful insights in Screaming Frog.

Here are the changes I made to the STAT rankings data to prepare for the next steps in Screaming Frog and Power BI. You’ll end up with multiple columns of URLs. Each serves a purpose later.

Duplicate the Ranking URL column to a new column called Normalized URL.

Remove URL parameters from the Normalized URL fields by using Excel’s text to columns tool and separating by “?”. I deleted the new columns(s) containing the URL parameters because they were not helpful in my analysis.

Duplicate the new, clean Normalized URL column to a new column called TLD. Use the text to columns tool on the TLD column and separate by “/” to remove everything except the domain name and subdomains. Delete the new columns. I chose to keep the subdomains in my TLD column but you can remove them if it helps your analysis.

Finally, create one more column called Full URL that will eventually become the list of URLs that you’ll crawl in Screaming Frog. To generate the Full URL, simply use Excel’s concatenate function to combine the Protocol and Normalized URL columns. Your formula will look something like this: =concatenate(A1, “://”, C1) to include the “://” in a valid URL string.

The 25,000+ rows in my data set are well within Excel’s limitations, so I am able to manipulate the data easily in one place. You may need to use a database (I like BigQuery) as your data sets grow.

Step 6: Categorize your SERP results by website type

Skimming through the SERP results, it’s easy to see that banks are not the only type of website that rank for keywords with local search intent. Since one of my initial questions was SERP composition, I had to identify all of the different types of websites and label each one for further analysis.

This step is by far the most time consuming and insightful. I spent 3 hours categorizing the initial batch of 25,000+ URLs into one of the following categories:

Institution (banks and credit union websites)

Directory (aggregators, local business directories, etc.)

Reviews (local and national sites like Yelp.com)

Education (content about banks on .edu domains)

Government (content about banks on .gov domains and municipal sites)

Jobs (careers sites and job aggregators)

News (local and national news sites with banking content)

Food Banks (yes, plenty of food banks rank for “banks near me” keywords)

Real Estate (commercial and residential real estate listings)

Search Engines (ranked content belonging to a search engine)

Social Media (ranked content on social media sites)

Other (completely random results not related to any of the above)

Your local SERPs will likely contain many of these website types and other unrelated categories such as food banks. Speed up the process by sorting and filtering your TLD and Normalized URL columns to categorize multiple rows simultaneously. For example, all the yelp.com rankings can be categorized as “Reviews” with a quick copy/paste.

At this point, your rankings data set is complete and you are ready to begin crawling the top-ranking sites in your industry to see what they have in common.

Step 7: Crawl your target websites with Screaming Frog

My initial STAT data identified over 6,600 unique pages from local bank websites that ranked in the top 20 organic search results. This is far too many pages to evaluate manually. Enter Screaming Frog, a crawler that mimics Google’s web crawler and extracts tons of SEO data from websites.

I configured Screaming Frog to crawl each of the 6,600 ranking pages for a larger analysis of characteristics shared by top-ranking bank websites. Don’t just let SF loose though. Be sure to configure it properly to save time and avoid crawling unnecessary pages.

These settings ensure we’ll get all the info we need to answer our questions in one crawl:

List Mode: Paste in a de-duplicated Full URL list from your STAT data. In my case, this was 6,600+ URLs.

Database Storage Mode: It may be a bit slower than Memory (RAM) Storage, but saving your crawl results on your hard disk ensures you won’t lose your results if you make a mistake (like I have many times) and close your report before you finish analyzing the data.

Limit Crawl Depth: Set this to 0 (zero) so the spider will only crawl the URLs on your list without following internal links to other pages on those domains.

APIs: I highly recommend using the Pagespeed Insights Integration to pull Lighthouse speed metrics directly into your crawl data. If you have a Moz account with API access, you can also pull link and domain data from the Moz API with the built-in integration.

Once you have configured the spider, let it rip! It could take several minutes to several hours depending on how many URLs you’re crawling and your computer’s speed and memory constraints. Just be patient! You might try running larger crawls overnight or on an extra computer to avoid bogging your primary machine down.

Step 8: Export your Screaming Frog crawl data to Excel

Dumping your crawl data into Excel is remarkably easy.

Step 9: Join your data sets in Power BI

At this point, you should have two data sources in Excel: one for your STAT rankings data and another for your Screaming Frog crawl data. Our goal is to combine the two data sources to see how organic search rank may be influenced by on-page SEO elements and site performance. To do this, we must first merge the data.

If you have access to a Windows PC, the free version of Power BI is powerful enough to get you started. Begin by loading your two data sources into a new project using the Get Data wizard.

Once your data sets are loaded, it’s time to make the magic happen by creating relationships in your data to unlock correlations between rankings and site characteristics. To combine your data in Power BI, create a many-to-many relationship between your STAT Full URL and Screaming Frog Original URL fields. 

If you are new to BI tools and data visualization, don’t worry! There are lots of helpful tutorials and videos just a quick search away. At this point, it’s really hard to break anything and you can experiment with lots of ways to analyze your data and share insights with many types of charts and graphs.

I should note that Power BI is my preferred data visualization tool but you may be able to use Tableau or some equally powerful. Google Data Studio was not an option for this analysis because it only allows for left outer joins of the multiple data sources and does not support “many-to-many” relationships. It’s a technical way of saying Data Studio isn’t flexible enough to create the data relationships that we need.

Step 10: Analyze and visualize!

Power BI’s built-in visualizations allow you to quickly summarize and present data. This is where we can start analyzing the data to answer the questions we came up with earlier.

Results — what did we learn?

Here are a couple examples of the insights gleaned from merging our rankings and crawl data. Spoiler alert — CWV doesn’t strongly impact organic rankings….yet!

Who are banks actually competing against in the SERPs? Is it primarily other banks?

On desktops, about 67% of organic search results belong to financial institutions (banks and credit unions) with heavy competition from reviews sites (7%) and online directories (22%). This information helps shape our SEO strategies for banks by exposing opportunities to monitor and maintain listings in relevant directories and reviews sites.

Okay, now let’s mash up our data sources to see how the distribution of website categories varies by rank on desktop devices. Suddenly, we can see that financial institutions actually occupy the majority of the top 3 results while reviews sites and directories are more prevalent in positions 4-10.

How important are Core Web Vitals (CWV) for rankings? How does this change over time?

Site performance and site speed are hot topics in SEO and will only become more important as CWV becomes a ranking signal in May this year. We can begin to understand the relationships between site speed and rankings by comparing STAT rankings and Pagespeed Insights data from Screaming Frog reports.

As of January 2021, sites with higher Lighthouse Performance Scores (i.e. they load faster) tend to rank better than sites with lower scores. This could help justify investments in site speed and site performance.

Some CWV elements correlate more closely with better rankings and others are more scattered. This isn’t to say CWV aren’t important or meaningful, but rather it’s a starting point for further analysis after May.

So what? What can we learn from this type of analysis?

Separately, STAT and Screaming Frog are incredibly powerful SEO tools. The data they provide are useful if you happen to be an SEO but the ability to merge data and extract relationships will multiply your value in any organization that values data, and acts on insights.

Besides validating some generally accepted SEO knowledge with data (“faster sites are rewarded with better rankings”), better use of relational data can also help us avoid spending valuable time on less important tactics (“improve Cumulative Layout Shift at all costs!”).

Of course, correlation does not imply causation, and aggregated data does not guarantee an outcome for individual sites. But if you’re a bank marketing professional responsible for customer acquisition from organic channels, you’ll need to bring this type of data to your stakeholders to justify increased investments in SEO.

By sharing the tools and methodology, I hope others will take it further by building and contributing their additional findings to the SEO community. What other datasets can we combine to deepen our understanding of SERPs on a larger scale? Let me know your thoughts in the comments!

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!

[nutrifami]

10 Steps to Blend STAT Ranking Data with Site Performance Metrics

Posted by AndrewMiller

Too often, we assume that SEO best practices will work in any industry against any competitive set. But most best practices go untested and may not be “best” in every situation.

We all know that tactics that worked in 2020 won’t necessarily move the needle in 2021 as Core Web Vitals (CWV) and other signals shuffle to the front. We have to do better for our businesses and our clients.

I’m a data nerd at heart with lots of battle scars from 15 years in SEO. The idea of analyzing thousands of local SERPs sounded like too much fun to pass up. I found some surprising correlations, and just as importantly, built a methodology and data set that can be updated quarterly to show changes over time.

I analyzed 50,000+ SERPs in the retail banking sector so I could make sense of the massive shifts in rankings and search behaviors during the lockdown period. We have a lot of historical data for bank websites, so comparing pre/post COVID data would be easier than starting from scratch.

I’ll share how I did it below. But first, I want to share WHY I think sharing this type of research is so important for the SEO community.

Why validate SEO best practices with data?

It’s a great time to be an SEO. We have amazing tools and can gather more data than ever. We have thriving communities and excellent basic training materials.

Yet, we often see our craft distilled into overly-simplified “best practices” that are assumed to be universally true. But if there’s one universal truth in SEO, it’s that there are no universal truths. A best practice can be misinterpreted or outdated, leading to missed opportunities or outright harm to a business.

Using the increasing importance of CWV as an example, SEOs have an opportunity (and obligation) to separate fact from fiction. We need to know if, and by how much, CWV will impact rankings over time so we can prioritize our efforts.

We can elevate our SEO game individually and collectively by testing and validating best practices with research. It just takes a curious mind, the right tools, and a willingness to accept the results rather than force a narrative.

Failing to validate best practices is a liability for SEO practitioners and shows an unwillingness to challenge assumptions. In my experience, a lack of data can lead to a senior stakeholders’ opinions carrying more weight than an SEO expert’s recommendations.

Start by asking the right questions

Real insight comes from combining data from multiple sources to answer critical questions and ensure your strategies are backed by valid data. In my analysis of local banks, I started by listing the questions I wanted to know the answers to:

What characteristics are shared by top-ranking local bank websites?

Who are banks actually competing against in the SERPs? Is it primarily other banks?

How do competitive SERPS change based on when/where/how users search?

How can smaller, local businesses gain an edge over larger competitors from outside their region?

How does SERP composition affect a bank’s ability to rank well for targeted keywords?

How important are Core Web Vitals (CWV) for rankings? How does this change over time?

You could run this same analysis by replacing “banks” with other local business categories. The list of potential questions is endless so you can adjust them based on your needs.

Here’s an important reminder - be prepared to accept the answers even if they are inconclusive or contradictory to your assumptions. Data-driven SEOs have to avoid confirmation bias if we’re going to remain objective.

Here’s how I analyzed 50,000 search results in a few hours

I combined three of my favorite tools to analyze SERPs at scale and gather the data needed to answer my questions:

STAT to generated ranking reports for select keywords

Screaming Frog to crawl websites and gather technical SEO data

Power BI to analyze the large data sets and create simple visualizations

Step 1: Determine your data needs

I used US Census Bureau data to identify all cities with populations over 100,000, because I wanted a representation of local bank SERPs across the country. My list ended up including 314 separate cities, but you could customize your list to suit your needs.

I also wanted to gather data for desktop and mobile searches to compare SERP differences between the device types.

Step 2: Identify your keywords

I chose “banks near me” and “banks in {city, st}” based on their strong local intent and high search volumes, compared to more specific keywords for banking services.

Step 3: Generate a STAT import file in .csv format

Once you have your keywords and market list, it’s time to prepare the bulk upload for STAT. Use the template provided in the link to create a .csv file with the following fields:

Project: The name of the new STAT project, or an existing project.

Folder: The name of the new folder, or an existing folder. (This is an optional column that you can leave blank.)

Site: The domain name for the site you want to track. Note, for our purposes you can enter any URL you want to track here. The Top 20 Report will include all ranking URLs for the target keywords even if they aren’t listed in your “Site” column.

Keyword: The search query you’re adding.

Tags: Enter as many keyword tags as you want, separated by commas. I used “city” and “near me” as tags to distinguish between the query types. (This is an optional column that you can leave blank.)

Market: Specify the market (country and language) in which you would like to track the keyword. I used “US-en” for US English.

Location: If you want to track the keyword in a specific location, specify the city, state, province, ZIP code, and/or postal code. I used the city and state list in “city, st” format.

Device: Select whether you would like Desktop or Smartphone results. I selected both.

Each market, location, and device type will multiply the number of keywords you must track. I ended up with 1,256 keywords (314 markets X 2 keywords X 2 devices) in my import file.

Once your file is complete, you can import to STAT and begin tracking.

Step 4: Run a Top 20 Report in STAT for all keywords

STAT’s built-in Google SERP Top 20 Comparison report captures the top 20 organic results from each SERP at different intervals (daily, weekly, monthly, etc.) to look at changes over time. I did not need daily data so I simply let it run on two consecutive days and removed the data I did not need. I re-run the same report quarterly to track changes over time.

Watch the video below to learn how to set up this report! 

My 1,256 keywords generated over 25,000 rows of data per day. Each row is a different organic listing and includes the keyword, monthly search volume, rank (includes the local pack), base rank (does not include the local pack), https/http protocol of the ranking URL, the ranking URL, and your tags.

Here’s an example of the raw output in CSV format:

It’s easy to see how useful this data is by itself but it becomes even more powerful when we clean it up and start crawling the ranking URLs.

Step 5: Clean up and normalize your STAT URLs data

At this point you may have invested 1-2 hours in gathering the initial data. This step is a bit more time consuming, but data cleansing allows you to run more advanced analysis and uncover more useful insights in Screaming Frog.

Here are the changes I made to the STAT rankings data to prepare for the next steps in Screaming Frog and Power BI. You’ll end up with multiple columns of URLs. Each serves a purpose later.

Duplicate the Ranking URL column to a new column called Normalized URL.

Remove URL parameters from the Normalized URL fields by using Excel’s text to columns tool and separating by “?”. I deleted the new columns(s) containing the URL parameters because they were not helpful in my analysis.

Duplicate the new, clean Normalized URL column to a new column called TLD. Use the text to columns tool on the TLD column and separate by “/” to remove everything except the domain name and subdomains. Delete the new columns. I chose to keep the subdomains in my TLD column but you can remove them if it helps your analysis.

Finally, create one more column called Full URL that will eventually become the list of URLs that you’ll crawl in Screaming Frog. To generate the Full URL, simply use Excel’s concatenate function to combine the Protocol and Normalized URL columns. Your formula will look something like this: =concatenate(A1, “://”, C1) to include the “://” in a valid URL string.

The 25,000+ rows in my data set are well within Excel’s limitations, so I am able to manipulate the data easily in one place. You may need to use a database (I like BigQuery) as your data sets grow.

Step 6: Categorize your SERP results by website type

Skimming through the SERP results, it’s easy to see that banks are not the only type of website that rank for keywords with local search intent. Since one of my initial questions was SERP composition, I had to identify all of the different types of websites and label each one for further analysis.

This step is by far the most time consuming and insightful. I spent 3 hours categorizing the initial batch of 25,000+ URLs into one of the following categories:

Institution (banks and credit union websites)

Directory (aggregators, local business directories, etc.)

Reviews (local and national sites like Yelp.com)

Education (content about banks on .edu domains)

Government (content about banks on .gov domains and municipal sites)

Jobs (careers sites and job aggregators)

News (local and national news sites with banking content)

Food Banks (yes, plenty of food banks rank for “banks near me” keywords)

Real Estate (commercial and residential real estate listings)

Search Engines (ranked content belonging to a search engine)

Social Media (ranked content on social media sites)

Other (completely random results not related to any of the above)

Your local SERPs will likely contain many of these website types and other unrelated categories such as food banks. Speed up the process by sorting and filtering your TLD and Normalized URL columns to categorize multiple rows simultaneously. For example, all the yelp.com rankings can be categorized as “Reviews” with a quick copy/paste.

At this point, your rankings data set is complete and you are ready to begin crawling the top-ranking sites in your industry to see what they have in common.

Step 7: Crawl your target websites with Screaming Frog

My initial STAT data identified over 6,600 unique pages from local bank websites that ranked in the top 20 organic search results. This is far too many pages to evaluate manually. Enter Screaming Frog, a crawler that mimics Google’s web crawler and extracts tons of SEO data from websites.

I configured Screaming Frog to crawl each of the 6,600 ranking pages for a larger analysis of characteristics shared by top-ranking bank websites. Don’t just let SF loose though. Be sure to configure it properly to save time and avoid crawling unnecessary pages.

These settings ensure we’ll get all the info we need to answer our questions in one crawl:

List Mode: Paste in a de-duplicated Full URL list from your STAT data. In my case, this was 6,600+ URLs.

Database Storage Mode: It may be a bit slower than Memory (RAM) Storage, but saving your crawl results on your hard disk ensures you won’t lose your results if you make a mistake (like I have many times) and close your report before you finish analyzing the data.

Limit Crawl Depth: Set this to 0 (zero) so the spider will only crawl the URLs on your list without following internal links to other pages on those domains.

APIs: I highly recommend using the Pagespeed Insights Integration to pull Lighthouse speed metrics directly into your crawl data. If you have a Moz account with API access, you can also pull link and domain data from the Moz API with the built-in integration.

Once you have configured the spider, let it rip! It could take several minutes to several hours depending on how many URLs you’re crawling and your computer’s speed and memory constraints. Just be patient! You might try running larger crawls overnight or on an extra computer to avoid bogging your primary machine down.

Step 8: Export your Screaming Frog crawl data to Excel

Dumping your crawl data into Excel is remarkably easy.

Step 9: Join your data sets in Power BI

At this point, you should have two data sources in Excel: one for your STAT rankings data and another for your Screaming Frog crawl data. Our goal is to combine the two data sources to see how organic search rank may be influenced by on-page SEO elements and site performance. To do this, we must first merge the data.

If you have access to a Windows PC, the free version of Power BI is powerful enough to get you started. Begin by loading your two data sources into a new project using the Get Data wizard.

Once your data sets are loaded, it’s time to make the magic happen by creating relationships in your data to unlock correlations between rankings and site characteristics. To combine your data in Power BI, create a many-to-many relationship between your STAT Full URL and Screaming Frog Original URL fields. 

If you are new to BI tools and data visualization, don’t worry! There are lots of helpful tutorials and videos just a quick search away. At this point, it’s really hard to break anything and you can experiment with lots of ways to analyze your data and share insights with many types of charts and graphs.

I should note that Power BI is my preferred data visualization tool but you may be able to use Tableau or some equally powerful. Google Data Studio was not an option for this analysis because it only allows for left outer joins of the multiple data sources and does not support “many-to-many” relationships. It’s a technical way of saying Data Studio isn’t flexible enough to create the data relationships that we need.

Step 10: Analyze and visualize!

Power BI’s built-in visualizations allow you to quickly summarize and present data. This is where we can start analyzing the data to answer the questions we came up with earlier.

Results — what did we learn?

Here are a couple examples of the insights gleaned from merging our rankings and crawl data. Spoiler alert — CWV doesn’t strongly impact organic rankings….yet!

Who are banks actually competing against in the SERPs? Is it primarily other banks?

On desktops, about 67% of organic search results belong to financial institutions (banks and credit unions) with heavy competition from reviews sites (7%) and online directories (22%). This information helps shape our SEO strategies for banks by exposing opportunities to monitor and maintain listings in relevant directories and reviews sites.

Okay, now let’s mash up our data sources to see how the distribution of website categories varies by rank on desktop devices. Suddenly, we can see that financial institutions actually occupy the majority of the top 3 results while reviews sites and directories are more prevalent in positions 4-10.

How important are Core Web Vitals (CWV) for rankings? How does this change over time?

Site performance and site speed are hot topics in SEO and will only become more important as CWV becomes a ranking signal in May this year. We can begin to understand the relationships between site speed and rankings by comparing STAT rankings and Pagespeed Insights data from Screaming Frog reports.

As of January 2021, sites with higher Lighthouse Performance Scores (i.e. they load faster) tend to rank better than sites with lower scores. This could help justify investments in site speed and site performance.

Some CWV elements correlate more closely with better rankings and others are more scattered. This isn’t to say CWV aren’t important or meaningful, but rather it’s a starting point for further analysis after May.

So what? What can we learn from this type of analysis?

Separately, STAT and Screaming Frog are incredibly powerful SEO tools. The data they provide are useful if you happen to be an SEO but the ability to merge data and extract relationships will multiply your value in any organization that values data, and acts on insights.

Besides validating some generally accepted SEO knowledge with data (“faster sites are rewarded with better rankings”), better use of relational data can also help us avoid spending valuable time on less important tactics (“improve Cumulative Layout Shift at all costs!”).

Of course, correlation does not imply causation, and aggregated data does not guarantee an outcome for individual sites. But if you’re a bank marketing professional responsible for customer acquisition from organic channels, you’ll need to bring this type of data to your stakeholders to justify increased investments in SEO.

By sharing the tools and methodology, I hope others will take it further by building and contributing their additional findings to the SEO community. What other datasets can we combine to deepen our understanding of SERPs on a larger scale? Let me know your thoughts in the comments!

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!

[xaydungtruonggia]

10 Steps to Blend STAT Ranking Data with Site Performance Metrics

Posted by AndrewMiller

Too often, we assume that SEO best practices will work in any industry against any competitive set. But most best practices go untested and may not be “best” in every situation.

We all know that tactics that worked in 2020 won’t necessarily move the needle in 2021 as Core Web Vitals (CWV) and other signals shuffle to the front. We have to do better for our businesses and our clients.

I’m a data nerd at heart with lots of battle scars from 15 years in SEO. The idea of analyzing thousands of local SERPs sounded like too much fun to pass up. I found some surprising correlations, and just as importantly, built a methodology and data set that can be updated quarterly to show changes over time.

I analyzed 50,000+ SERPs in the retail banking sector so I could make sense of the massive shifts in rankings and search behaviors during the lockdown period. We have a lot of historical data for bank websites, so comparing pre/post COVID data would be easier than starting from scratch.

I’ll share how I did it below. But first, I want to share WHY I think sharing this type of research is so important for the SEO community.

Why validate SEO best practices with data?

It’s a great time to be an SEO. We have amazing tools and can gather more data than ever. We have thriving communities and excellent basic training materials.

Yet, we often see our craft distilled into overly-simplified “best practices” that are assumed to be universally true. But if there’s one universal truth in SEO, it’s that there are no universal truths. A best practice can be misinterpreted or outdated, leading to missed opportunities or outright harm to a business.

Using the increasing importance of CWV as an example, SEOs have an opportunity (and obligation) to separate fact from fiction. We need to know if, and by how much, CWV will impact rankings over time so we can prioritize our efforts.

We can elevate our SEO game individually and collectively by testing and validating best practices with research. It just takes a curious mind, the right tools, and a willingness to accept the results rather than force a narrative.

Failing to validate best practices is a liability for SEO practitioners and shows an unwillingness to challenge assumptions. In my experience, a lack of data can lead to a senior stakeholders’ opinions carrying more weight than an SEO expert’s recommendations.

Start by asking the right questions

Real insight comes from combining data from multiple sources to answer critical questions and ensure your strategies are backed by valid data. In my analysis of local banks, I started by listing the questions I wanted to know the answers to:

What characteristics are shared by top-ranking local bank websites?

Who are banks actually competing against in the SERPs? Is it primarily other banks?

How do competitive SERPS change based on when/where/how users search?

How can smaller, local businesses gain an edge over larger competitors from outside their region?

How does SERP composition affect a bank’s ability to rank well for targeted keywords?

How important are Core Web Vitals (CWV) for rankings? How does this change over time?

You could run this same analysis by replacing “banks” with other local business categories. The list of potential questions is endless so you can adjust them based on your needs.

Here’s an important reminder - be prepared to accept the answers even if they are inconclusive or contradictory to your assumptions. Data-driven SEOs have to avoid confirmation bias if we’re going to remain objective.

Here’s how I analyzed 50,000 search results in a few hours

I combined three of my favorite tools to analyze SERPs at scale and gather the data needed to answer my questions:

STAT to generated ranking reports for select keywords

Screaming Frog to crawl websites and gather technical SEO data

Power BI to analyze the large data sets and create simple visualizations

Step 1: Determine your data needs

I used US Census Bureau data to identify all cities with populations over 100,000, because I wanted a representation of local bank SERPs across the country. My list ended up including 314 separate cities, but you could customize your list to suit your needs.

I also wanted to gather data for desktop and mobile searches to compare SERP differences between the device types.

Step 2: Identify your keywords

I chose “banks near me” and “banks in {city, st}” based on their strong local intent and high search volumes, compared to more specific keywords for banking services.

Step 3: Generate a STAT import file in .csv format

Once you have your keywords and market list, it’s time to prepare the bulk upload for STAT. Use the template provided in the link to create a .csv file with the following fields:

Project: The name of the new STAT project, or an existing project.

Folder: The name of the new folder, or an existing folder. (This is an optional column that you can leave blank.)

Site: The domain name for the site you want to track. Note, for our purposes you can enter any URL you want to track here. The Top 20 Report will include all ranking URLs for the target keywords even if they aren’t listed in your “Site” column.

Keyword: The search query you’re adding.

Tags: Enter as many keyword tags as you want, separated by commas. I used “city” and “near me” as tags to distinguish between the query types. (This is an optional column that you can leave blank.)

Market: Specify the market (country and language) in which you would like to track the keyword. I used “US-en” for US English.

Location: If you want to track the keyword in a specific location, specify the city, state, province, ZIP code, and/or postal code. I used the city and state list in “city, st” format.

Device: Select whether you would like Desktop or Smartphone results. I selected both.

Each market, location, and device type will multiply the number of keywords you must track. I ended up with 1,256 keywords (314 markets X 2 keywords X 2 devices) in my import file.

Once your file is complete, you can import to STAT and begin tracking.

Step 4: Run a Top 20 Report in STAT for all keywords

STAT’s built-in Google SERP Top 20 Comparison report captures the top 20 organic results from each SERP at different intervals (daily, weekly, monthly, etc.) to look at changes over time. I did not need daily data so I simply let it run on two consecutive days and removed the data I did not need. I re-run the same report quarterly to track changes over time.

Watch the video below to learn how to set up this report! 

My 1,256 keywords generated over 25,000 rows of data per day. Each row is a different organic listing and includes the keyword, monthly search volume, rank (includes the local pack), base rank (does not include the local pack), https/http protocol of the ranking URL, the ranking URL, and your tags.

Here’s an example of the raw output in CSV format:

It’s easy to see how useful this data is by itself but it becomes even more powerful when we clean it up and start crawling the ranking URLs.

Step 5: Clean up and normalize your STAT URLs data

At this point you may have invested 1-2 hours in gathering the initial data. This step is a bit more time consuming, but data cleansing allows you to run more advanced analysis and uncover more useful insights in Screaming Frog.

Here are the changes I made to the STAT rankings data to prepare for the next steps in Screaming Frog and Power BI. You’ll end up with multiple columns of URLs. Each serves a purpose later.

Duplicate the Ranking URL column to a new column called Normalized URL.

Remove URL parameters from the Normalized URL fields by using Excel’s text to columns tool and separating by “?”. I deleted the new columns(s) containing the URL parameters because they were not helpful in my analysis.

Duplicate the new, clean Normalized URL column to a new column called TLD. Use the text to columns tool on the TLD column and separate by “/” to remove everything except the domain name and subdomains. Delete the new columns. I chose to keep the subdomains in my TLD column but you can remove them if it helps your analysis.

Finally, create one more column called Full URL that will eventually become the list of URLs that you’ll crawl in Screaming Frog. To generate the Full URL, simply use Excel’s concatenate function to combine the Protocol and Normalized URL columns. Your formula will look something like this: =concatenate(A1, “://”, C1) to include the “://” in a valid URL string.

The 25,000+ rows in my data set are well within Excel’s limitations, so I am able to manipulate the data easily in one place. You may need to use a database (I like BigQuery) as your data sets grow.

Step 6: Categorize your SERP results by website type

Skimming through the SERP results, it’s easy to see that banks are not the only type of website that rank for keywords with local search intent. Since one of my initial questions was SERP composition, I had to identify all of the different types of websites and label each one for further analysis.

This step is by far the most time consuming and insightful. I spent 3 hours categorizing the initial batch of 25,000+ URLs into one of the following categories:

Institution (banks and credit union websites)

Directory (aggregators, local business directories, etc.)

Reviews (local and national sites like Yelp.com)

Education (content about banks on .edu domains)

Government (content about banks on .gov domains and municipal sites)

Jobs (careers sites and job aggregators)

News (local and national news sites with banking content)

Food Banks (yes, plenty of food banks rank for “banks near me” keywords)

Real Estate (commercial and residential real estate listings)

Search Engines (ranked content belonging to a search engine)

Social Media (ranked content on social media sites)

Other (completely random results not related to any of the above)

Your local SERPs will likely contain many of these website types and other unrelated categories such as food banks. Speed up the process by sorting and filtering your TLD and Normalized URL columns to categorize multiple rows simultaneously. For example, all the yelp.com rankings can be categorized as “Reviews” with a quick copy/paste.

At this point, your rankings data set is complete and you are ready to begin crawling the top-ranking sites in your industry to see what they have in common.

Step 7: Crawl your target websites with Screaming Frog

My initial STAT data identified over 6,600 unique pages from local bank websites that ranked in the top 20 organic search results. This is far too many pages to evaluate manually. Enter Screaming Frog, a crawler that mimics Google’s web crawler and extracts tons of SEO data from websites.

I configured Screaming Frog to crawl each of the 6,600 ranking pages for a larger analysis of characteristics shared by top-ranking bank websites. Don’t just let SF loose though. Be sure to configure it properly to save time and avoid crawling unnecessary pages.

These settings ensure we’ll get all the info we need to answer our questions in one crawl:

List Mode: Paste in a de-duplicated Full URL list from your STAT data. In my case, this was 6,600+ URLs.

Database Storage Mode: It may be a bit slower than Memory (RAM) Storage, but saving your crawl results on your hard disk ensures you won’t lose your results if you make a mistake (like I have many times) and close your report before you finish analyzing the data.

Limit Crawl Depth: Set this to 0 (zero) so the spider will only crawl the URLs on your list without following internal links to other pages on those domains.

APIs: I highly recommend using the Pagespeed Insights Integration to pull Lighthouse speed metrics directly into your crawl data. If you have a Moz account with API access, you can also pull link and domain data from the Moz API with the built-in integration.

Once you have configured the spider, let it rip! It could take several minutes to several hours depending on how many URLs you’re crawling and your computer’s speed and memory constraints. Just be patient! You might try running larger crawls overnight or on an extra computer to avoid bogging your primary machine down.

Step 8: Export your Screaming Frog crawl data to Excel

Dumping your crawl data into Excel is remarkably easy.

Step 9: Join your data sets in Power BI

At this point, you should have two data sources in Excel: one for your STAT rankings data and another for your Screaming Frog crawl data. Our goal is to combine the two data sources to see how organic search rank may be influenced by on-page SEO elements and site performance. To do this, we must first merge the data.

If you have access to a Windows PC, the free version of Power BI is powerful enough to get you started. Begin by loading your two data sources into a new project using the Get Data wizard.

Once your data sets are loaded, it’s time to make the magic happen by creating relationships in your data to unlock correlations between rankings and site characteristics. To combine your data in Power BI, create a many-to-many relationship between your STAT Full URL and Screaming Frog Original URL fields. 

If you are new to BI tools and data visualization, don’t worry! There are lots of helpful tutorials and videos just a quick search away. At this point, it’s really hard to break anything and you can experiment with lots of ways to analyze your data and share insights with many types of charts and graphs.

I should note that Power BI is my preferred data visualization tool but you may be able to use Tableau or some equally powerful. Google Data Studio was not an option for this analysis because it only allows for left outer joins of the multiple data sources and does not support “many-to-many” relationships. It’s a technical way of saying Data Studio isn’t flexible enough to create the data relationships that we need.

Step 10: Analyze and visualize!

Power BI’s built-in visualizations allow you to quickly summarize and present data. This is where we can start analyzing the data to answer the questions we came up with earlier.

Results — what did we learn?

Here are a couple examples of the insights gleaned from merging our rankings and crawl data. Spoiler alert — CWV doesn’t strongly impact organic rankings….yet!

Who are banks actually competing against in the SERPs? Is it primarily other banks?

On desktops, about 67% of organic search results belong to financial institutions (banks and credit unions) with heavy competition from reviews sites (7%) and online directories (22%). This information helps shape our SEO strategies for banks by exposing opportunities to monitor and maintain listings in relevant directories and reviews sites.

Okay, now let’s mash up our data sources to see how the distribution of website categories varies by rank on desktop devices. Suddenly, we can see that financial institutions actually occupy the majority of the top 3 results while reviews sites and directories are more prevalent in positions 4-10.

How important are Core Web Vitals (CWV) for rankings? How does this change over time?

Site performance and site speed are hot topics in SEO and will only become more important as CWV becomes a ranking signal in May this year. We can begin to understand the relationships between site speed and rankings by comparing STAT rankings and Pagespeed Insights data from Screaming Frog reports.

As of January 2021, sites with higher Lighthouse Performance Scores (i.e. they load faster) tend to rank better than sites with lower scores. This could help justify investments in site speed and site performance.

Some CWV elements correlate more closely with better rankings and others are more scattered. This isn’t to say CWV aren’t important or meaningful, but rather it’s a starting point for further analysis after May.

So what? What can we learn from this type of analysis?

Separately, STAT and Screaming Frog are incredibly powerful SEO tools. The data they provide are useful if you happen to be an SEO but the ability to merge data and extract relationships will multiply your value in any organization that values data, and acts on insights.

Besides validating some generally accepted SEO knowledge with data (“faster sites are rewarded with better rankings”), better use of relational data can also help us avoid spending valuable time on less important tactics (“improve Cumulative Layout Shift at all costs!”).

Of course, correlation does not imply causation, and aggregated data does not guarantee an outcome for individual sites. But if you’re a bank marketing professional responsible for customer acquisition from organic channels, you’ll need to bring this type of data to your stakeholders to justify increased investments in SEO.

By sharing the tools and methodology, I hope others will take it further by building and contributing their additional findings to the SEO community. What other datasets can we combine to deepen our understanding of SERPs on a larger scale? Let me know your thoughts in the comments!

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!

[camerasieunhovn]

10 Steps to Blend STAT Ranking Data with Site Performance Metrics

Posted by AndrewMiller

Too often, we assume that SEO best practices will work in any industry against any competitive set. But most best practices go untested and may not be “best” in every situation.

We all know that tactics that worked in 2020 won’t necessarily move the needle in 2021 as Core Web Vitals (CWV) and other signals shuffle to the front. We have to do better for our businesses and our clients.

I’m a data nerd at heart with lots of battle scars from 15 years in SEO. The idea of analyzing thousands of local SERPs sounded like too much fun to pass up. I found some surprising correlations, and just as importantly, built a methodology and data set that can be updated quarterly to show changes over time.

I analyzed 50,000+ SERPs in the retail banking sector so I could make sense of the massive shifts in rankings and search behaviors during the lockdown period. We have a lot of historical data for bank websites, so comparing pre/post COVID data would be easier than starting from scratch.

I’ll share how I did it below. But first, I want to share WHY I think sharing this type of research is so important for the SEO community.

Why validate SEO best practices with data?

It’s a great time to be an SEO. We have amazing tools and can gather more data than ever. We have thriving communities and excellent basic training materials.

Yet, we often see our craft distilled into overly-simplified “best practices” that are assumed to be universally true. But if there’s one universal truth in SEO, it’s that there are no universal truths. A best practice can be misinterpreted or outdated, leading to missed opportunities or outright harm to a business.

Using the increasing importance of CWV as an example, SEOs have an opportunity (and obligation) to separate fact from fiction. We need to know if, and by how much, CWV will impact rankings over time so we can prioritize our efforts.

We can elevate our SEO game individually and collectively by testing and validating best practices with research. It just takes a curious mind, the right tools, and a willingness to accept the results rather than force a narrative.

Failing to validate best practices is a liability for SEO practitioners and shows an unwillingness to challenge assumptions. In my experience, a lack of data can lead to a senior stakeholders’ opinions carrying more weight than an SEO expert’s recommendations.

Start by asking the right questions

Real insight comes from combining data from multiple sources to answer critical questions and ensure your strategies are backed by valid data. In my analysis of local banks, I started by listing the questions I wanted to know the answers to:

What characteristics are shared by top-ranking local bank websites?

Who are banks actually competing against in the SERPs? Is it primarily other banks?

How do competitive SERPS change based on when/where/how users search?

How can smaller, local businesses gain an edge over larger competitors from outside their region?

How does SERP composition affect a bank’s ability to rank well for targeted keywords?

How important are Core Web Vitals (CWV) for rankings? How does this change over time?

You could run this same analysis by replacing “banks” with other local business categories. The list of potential questions is endless so you can adjust them based on your needs.

Here’s an important reminder - be prepared to accept the answers even if they are inconclusive or contradictory to your assumptions. Data-driven SEOs have to avoid confirmation bias if we’re going to remain objective.

Here’s how I analyzed 50,000 search results in a few hours

I combined three of my favorite tools to analyze SERPs at scale and gather the data needed to answer my questions:

STAT to generated ranking reports for select keywords

Screaming Frog to crawl websites and gather technical SEO data

Power BI to analyze the large data sets and create simple visualizations

Step 1: Determine your data needs

I used US Census Bureau data to identify all cities with populations over 100,000, because I wanted a representation of local bank SERPs across the country. My list ended up including 314 separate cities, but you could customize your list to suit your needs.

I also wanted to gather data for desktop and mobile searches to compare SERP differences between the device types.

Step 2: Identify your keywords

I chose “banks near me” and “banks in {city, st}” based on their strong local intent and high search volumes, compared to more specific keywords for banking services.

Step 3: Generate a STAT import file in .csv format

Once you have your keywords and market list, it’s time to prepare the bulk upload for STAT. Use the template provided in the link to create a .csv file with the following fields:

Project: The name of the new STAT project, or an existing project.

Folder: The name of the new folder, or an existing folder. (This is an optional column that you can leave blank.)

Site: The domain name for the site you want to track. Note, for our purposes you can enter any URL you want to track here. The Top 20 Report will include all ranking URLs for the target keywords even if they aren’t listed in your “Site” column.

Keyword: The search query you’re adding.

Tags: Enter as many keyword tags as you want, separated by commas. I used “city” and “near me” as tags to distinguish between the query types. (This is an optional column that you can leave blank.)

Market: Specify the market (country and language) in which you would like to track the keyword. I used “US-en” for US English.

Location: If you want to track the keyword in a specific location, specify the city, state, province, ZIP code, and/or postal code. I used the city and state list in “city, st” format.

Device: Select whether you would like Desktop or Smartphone results. I selected both.

Each market, location, and device type will multiply the number of keywords you must track. I ended up with 1,256 keywords (314 markets X 2 keywords X 2 devices) in my import file.

Once your file is complete, you can import to STAT and begin tracking.

Step 4: Run a Top 20 Report in STAT for all keywords

STAT’s built-in Google SERP Top 20 Comparison report captures the top 20 organic results from each SERP at different intervals (daily, weekly, monthly, etc.) to look at changes over time. I did not need daily data so I simply let it run on two consecutive days and removed the data I did not need. I re-run the same report quarterly to track changes over time.

Watch the video below to learn how to set up this report! 

My 1,256 keywords generated over 25,000 rows of data per day. Each row is a different organic listing and includes the keyword, monthly search volume, rank (includes the local pack), base rank (does not include the local pack), https/http protocol of the ranking URL, the ranking URL, and your tags.

Here’s an example of the raw output in CSV format:

It’s easy to see how useful this data is by itself but it becomes even more powerful when we clean it up and start crawling the ranking URLs.

Step 5: Clean up and normalize your STAT URLs data

At this point you may have invested 1-2 hours in gathering the initial data. This step is a bit more time consuming, but data cleansing allows you to run more advanced analysis and uncover more useful insights in Screaming Frog.

Here are the changes I made to the STAT rankings data to prepare for the next steps in Screaming Frog and Power BI. You’ll end up with multiple columns of URLs. Each serves a purpose later.

Duplicate the Ranking URL column to a new column called Normalized URL.

Remove URL parameters from the Normalized URL fields by using Excel’s text to columns tool and separating by “?”. I deleted the new columns(s) containing the URL parameters because they were not helpful in my analysis.

Duplicate the new, clean Normalized URL column to a new column called TLD. Use the text to columns tool on the TLD column and separate by “/” to remove everything except the domain name and subdomains. Delete the new columns. I chose to keep the subdomains in my TLD column but you can remove them if it helps your analysis.

Finally, create one more column called Full URL that will eventually become the list of URLs that you’ll crawl in Screaming Frog. To generate the Full URL, simply use Excel’s concatenate function to combine the Protocol and Normalized URL columns. Your formula will look something like this: =concatenate(A1, “://”, C1) to include the “://” in a valid URL string.

The 25,000+ rows in my data set are well within Excel’s limitations, so I am able to manipulate the data easily in one place. You may need to use a database (I like BigQuery) as your data sets grow.

Step 6: Categorize your SERP results by website type

Skimming through the SERP results, it’s easy to see that banks are not the only type of website that rank for keywords with local search intent. Since one of my initial questions was SERP composition, I had to identify all of the different types of websites and label each one for further analysis.

This step is by far the most time consuming and insightful. I spent 3 hours categorizing the initial batch of 25,000+ URLs into one of the following categories:

Institution (banks and credit union websites)

Directory (aggregators, local business directories, etc.)

Reviews (local and national sites like Yelp.com)

Education (content about banks on .edu domains)

Government (content about banks on .gov domains and municipal sites)

Jobs (careers sites and job aggregators)

News (local and national news sites with banking content)

Food Banks (yes, plenty of food banks rank for “banks near me” keywords)

Real Estate (commercial and residential real estate listings)

Search Engines (ranked content belonging to a search engine)

Social Media (ranked content on social media sites)

Other (completely random results not related to any of the above)

Your local SERPs will likely contain many of these website types and other unrelated categories such as food banks. Speed up the process by sorting and filtering your TLD and Normalized URL columns to categorize multiple rows simultaneously. For example, all the yelp.com rankings can be categorized as “Reviews” with a quick copy/paste.

At this point, your rankings data set is complete and you are ready to begin crawling the top-ranking sites in your industry to see what they have in common.

Step 7: Crawl your target websites with Screaming Frog

My initial STAT data identified over 6,600 unique pages from local bank websites that ranked in the top 20 organic search results. This is far too many pages to evaluate manually. Enter Screaming Frog, a crawler that mimics Google’s web crawler and extracts tons of SEO data from websites.

I configured Screaming Frog to crawl each of the 6,600 ranking pages for a larger analysis of characteristics shared by top-ranking bank websites. Don’t just let SF loose though. Be sure to configure it properly to save time and avoid crawling unnecessary pages.

These settings ensure we’ll get all the info we need to answer our questions in one crawl:

List Mode: Paste in a de-duplicated Full URL list from your STAT data. In my case, this was 6,600+ URLs.

Database Storage Mode: It may be a bit slower than Memory (RAM) Storage, but saving your crawl results on your hard disk ensures you won’t lose your results if you make a mistake (like I have many times) and close your report before you finish analyzing the data.

Limit Crawl Depth: Set this to 0 (zero) so the spider will only crawl the URLs on your list without following internal links to other pages on those domains.

APIs: I highly recommend using the Pagespeed Insights Integration to pull Lighthouse speed metrics directly into your crawl data. If you have a Moz account with API access, you can also pull link and domain data from the Moz API with the built-in integration.

Once you have configured the spider, let it rip! It could take several minutes to several hours depending on how many URLs you’re crawling and your computer’s speed and memory constraints. Just be patient! You might try running larger crawls overnight or on an extra computer to avoid bogging your primary machine down.

Step 8: Export your Screaming Frog crawl data to Excel

Dumping your crawl data into Excel is remarkably easy.

Step 9: Join your data sets in Power BI

At this point, you should have two data sources in Excel: one for your STAT rankings data and another for your Screaming Frog crawl data. Our goal is to combine the two data sources to see how organic search rank may be influenced by on-page SEO elements and site performance. To do this, we must first merge the data.

If you have access to a Windows PC, the free version of Power BI is powerful enough to get you started. Begin by loading your two data sources into a new project using the Get Data wizard.

Once your data sets are loaded, it’s time to make the magic happen by creating relationships in your data to unlock correlations between rankings and site characteristics. To combine your data in Power BI, create a many-to-many relationship between your STAT Full URL and Screaming Frog Original URL fields. 

If you are new to BI tools and data visualization, don’t worry! There are lots of helpful tutorials and videos just a quick search away. At this point, it’s really hard to break anything and you can experiment with lots of ways to analyze your data and share insights with many types of charts and graphs.

I should note that Power BI is my preferred data visualization tool but you may be able to use Tableau or some equally powerful. Google Data Studio was not an option for this analysis because it only allows for left outer joins of the multiple data sources and does not support “many-to-many” relationships. It’s a technical way of saying Data Studio isn’t flexible enough to create the data relationships that we need.

Step 10: Analyze and visualize!

Power BI’s built-in visualizations allow you to quickly summarize and present data. This is where we can start analyzing the data to answer the questions we came up with earlier.

Results — what did we learn?

Here are a couple examples of the insights gleaned from merging our rankings and crawl data. Spoiler alert — CWV doesn’t strongly impact organic rankings….yet!

Who are banks actually competing against in the SERPs? Is it primarily other banks?

On desktops, about 67% of organic search results belong to financial institutions (banks and credit unions) with heavy competition from reviews sites (7%) and online directories (22%). This information helps shape our SEO strategies for banks by exposing opportunities to monitor and maintain listings in relevant directories and reviews sites.

Okay, now let’s mash up our data sources to see how the distribution of website categories varies by rank on desktop devices. Suddenly, we can see that financial institutions actually occupy the majority of the top 3 results while reviews sites and directories are more prevalent in positions 4-10.

How important are Core Web Vitals (CWV) for rankings? How does this change over time?

Site performance and site speed are hot topics in SEO and will only become more important as CWV becomes a ranking signal in May this year. We can begin to understand the relationships between site speed and rankings by comparing STAT rankings and Pagespeed Insights data from Screaming Frog reports.

As of January 2021, sites with higher Lighthouse Performance Scores (i.e. they load faster) tend to rank better than sites with lower scores. This could help justify investments in site speed and site performance.

Some CWV elements correlate more closely with better rankings and others are more scattered. This isn’t to say CWV aren’t important or meaningful, but rather it’s a starting point for further analysis after May.

So what? What can we learn from this type of analysis?

Separately, STAT and Screaming Frog are incredibly powerful SEO tools. The data they provide are useful if you happen to be an SEO but the ability to merge data and extract relationships will multiply your value in any organization that values data, and acts on insights.

Besides validating some generally accepted SEO knowledge with data (“faster sites are rewarded with better rankings”), better use of relational data can also help us avoid spending valuable time on less important tactics (“improve Cumulative Layout Shift at all costs!”).

Of course, correlation does not imply causation, and aggregated data does not guarantee an outcome for individual sites. But if you’re a bank marketing professional responsible for customer acquisition from organic channels, you’ll need to bring this type of data to your stakeholders to justify increased investments in SEO.

By sharing the tools and methodology, I hope others will take it further by building and contributing their additional findings to the SEO community. What other datasets can we combine to deepen our understanding of SERPs on a larger scale? Let me know your thoughts in the comments!

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!

[ductrungnguyen87]

10 Steps to Blend STAT Ranking Data with Site Performance Metrics

Posted by AndrewMiller

Too often, we assume that SEO best practices will work in any industry against any competitive set. But most best practices go untested and may not be “best” in every situation.

We all know that tactics that worked in 2020 won’t necessarily move the needle in 2021 as Core Web Vitals (CWV) and other signals shuffle to the front. We have to do better for our businesses and our clients.

I’m a data nerd at heart with lots of battle scars from 15 years in SEO. The idea of analyzing thousands of local SERPs sounded like too much fun to pass up. I found some surprising correlations, and just as importantly, built a methodology and data set that can be updated quarterly to show changes over time.

I analyzed 50,000+ SERPs in the retail banking sector so I could make sense of the massive shifts in rankings and search behaviors during the lockdown period. We have a lot of historical data for bank websites, so comparing pre/post COVID data would be easier than starting from scratch.

I’ll share how I did it below. But first, I want to share WHY I think sharing this type of research is so important for the SEO community.

Why validate SEO best practices with data?

It’s a great time to be an SEO. We have amazing tools and can gather more data than ever. We have thriving communities and excellent basic training materials.

Yet, we often see our craft distilled into overly-simplified “best practices” that are assumed to be universally true. But if there’s one universal truth in SEO, it’s that there are no universal truths. A best practice can be misinterpreted or outdated, leading to missed opportunities or outright harm to a business.

Using the increasing importance of CWV as an example, SEOs have an opportunity (and obligation) to separate fact from fiction. We need to know if, and by how much, CWV will impact rankings over time so we can prioritize our efforts.

We can elevate our SEO game individually and collectively by testing and validating best practices with research. It just takes a curious mind, the right tools, and a willingness to accept the results rather than force a narrative.

Failing to validate best practices is a liability for SEO practitioners and shows an unwillingness to challenge assumptions. In my experience, a lack of data can lead to a senior stakeholders’ opinions carrying more weight than an SEO expert’s recommendations.

Start by asking the right questions

Real insight comes from combining data from multiple sources to answer critical questions and ensure your strategies are backed by valid data. In my analysis of local banks, I started by listing the questions I wanted to know the answers to:

What characteristics are shared by top-ranking local bank websites?

Who are banks actually competing against in the SERPs? Is it primarily other banks?

How do competitive SERPS change based on when/where/how users search?

How can smaller, local businesses gain an edge over larger competitors from outside their region?

How does SERP composition affect a bank’s ability to rank well for targeted keywords?

How important are Core Web Vitals (CWV) for rankings? How does this change over time?

You could run this same analysis by replacing “banks” with other local business categories. The list of potential questions is endless so you can adjust them based on your needs.

Here’s an important reminder - be prepared to accept the answers even if they are inconclusive or contradictory to your assumptions. Data-driven SEOs have to avoid confirmation bias if we’re going to remain objective.

Here’s how I analyzed 50,000 search results in a few hours

I combined three of my favorite tools to analyze SERPs at scale and gather the data needed to answer my questions:

STAT to generated ranking reports for select keywords

Screaming Frog to crawl websites and gather technical SEO data

Power BI to analyze the large data sets and create simple visualizations

Step 1: Determine your data needs

I used US Census Bureau data to identify all cities with populations over 100,000, because I wanted a representation of local bank SERPs across the country. My list ended up including 314 separate cities, but you could customize your list to suit your needs.

I also wanted to gather data for desktop and mobile searches to compare SERP differences between the device types.

Step 2: Identify your keywords

I chose “banks near me” and “banks in {city, st}” based on their strong local intent and high search volumes, compared to more specific keywords for banking services.

Step 3: Generate a STAT import file in .csv format

Once you have your keywords and market list, it’s time to prepare the bulk upload for STAT. Use the template provided in the link to create a .csv file with the following fields:

Project: The name of the new STAT project, or an existing project.

Folder: The name of the new folder, or an existing folder. (This is an optional column that you can leave blank.)

Site: The domain name for the site you want to track. Note, for our purposes you can enter any URL you want to track here. The Top 20 Report will include all ranking URLs for the target keywords even if they aren’t listed in your “Site” column.

Keyword: The search query you’re adding.

Tags: Enter as many keyword tags as you want, separated by commas. I used “city” and “near me” as tags to distinguish between the query types. (This is an optional column that you can leave blank.)

Market: Specify the market (country and language) in which you would like to track the keyword. I used “US-en” for US English.

Location: If you want to track the keyword in a specific location, specify the city, state, province, ZIP code, and/or postal code. I used the city and state list in “city, st” format.

Device: Select whether you would like Desktop or Smartphone results. I selected both.

Each market, location, and device type will multiply the number of keywords you must track. I ended up with 1,256 keywords (314 markets X 2 keywords X 2 devices) in my import file.

Once your file is complete, you can import to STAT and begin tracking.

Step 4: Run a Top 20 Report in STAT for all keywords

STAT’s built-in Google SERP Top 20 Comparison report captures the top 20 organic results from each SERP at different intervals (daily, weekly, monthly, etc.) to look at changes over time. I did not need daily data so I simply let it run on two consecutive days and removed the data I did not need. I re-run the same report quarterly to track changes over time.

Watch the video below to learn how to set up this report! 

My 1,256 keywords generated over 25,000 rows of data per day. Each row is a different organic listing and includes the keyword, monthly search volume, rank (includes the local pack), base rank (does not include the local pack), https/http protocol of the ranking URL, the ranking URL, and your tags.

Here’s an example of the raw output in CSV format:

It’s easy to see how useful this data is by itself but it becomes even more powerful when we clean it up and start crawling the ranking URLs.

Step 5: Clean up and normalize your STAT URLs data

At this point you may have invested 1-2 hours in gathering the initial data. This step is a bit more time consuming, but data cleansing allows you to run more advanced analysis and uncover more useful insights in Screaming Frog.

Here are the changes I made to the STAT rankings data to prepare for the next steps in Screaming Frog and Power BI. You’ll end up with multiple columns of URLs. Each serves a purpose later.

Duplicate the Ranking URL column to a new column called Normalized URL.

Remove URL parameters from the Normalized URL fields by using Excel’s text to columns tool and separating by “?”. I deleted the new columns(s) containing the URL parameters because they were not helpful in my analysis.

Duplicate the new, clean Normalized URL column to a new column called TLD. Use the text to columns tool on the TLD column and separate by “/” to remove everything except the domain name and subdomains. Delete the new columns. I chose to keep the subdomains in my TLD column but you can remove them if it helps your analysis.

Finally, create one more column called Full URL that will eventually become the list of URLs that you’ll crawl in Screaming Frog. To generate the Full URL, simply use Excel’s concatenate function to combine the Protocol and Normalized URL columns. Your formula will look something like this: =concatenate(A1, “://”, C1) to include the “://” in a valid URL string.

The 25,000+ rows in my data set are well within Excel’s limitations, so I am able to manipulate the data easily in one place. You may need to use a database (I like BigQuery) as your data sets grow.

Step 6: Categorize your SERP results by website type

Skimming through the SERP results, it’s easy to see that banks are not the only type of website that rank for keywords with local search intent. Since one of my initial questions was SERP composition, I had to identify all of the different types of websites and label each one for further analysis.

This step is by far the most time consuming and insightful. I spent 3 hours categorizing the initial batch of 25,000+ URLs into one of the following categories:

Institution (banks and credit union websites)

Directory (aggregators, local business directories, etc.)

Reviews (local and national sites like Yelp.com)

Education (content about banks on .edu domains)

Government (content about banks on .gov domains and municipal sites)

Jobs (careers sites and job aggregators)

News (local and national news sites with banking content)

Food Banks (yes, plenty of food banks rank for “banks near me” keywords)

Real Estate (commercial and residential real estate listings)

Search Engines (ranked content belonging to a search engine)

Social Media (ranked content on social media sites)

Other (completely random results not related to any of the above)

Your local SERPs will likely contain many of these website types and other unrelated categories such as food banks. Speed up the process by sorting and filtering your TLD and Normalized URL columns to categorize multiple rows simultaneously. For example, all the yelp.com rankings can be categorized as “Reviews” with a quick copy/paste.

At this point, your rankings data set is complete and you are ready to begin crawling the top-ranking sites in your industry to see what they have in common.

Step 7: Crawl your target websites with Screaming Frog

My initial STAT data identified over 6,600 unique pages from local bank websites that ranked in the top 20 organic search results. This is far too many pages to evaluate manually. Enter Screaming Frog, a crawler that mimics Google’s web crawler and extracts tons of SEO data from websites.

I configured Screaming Frog to crawl each of the 6,600 ranking pages for a larger analysis of characteristics shared by top-ranking bank websites. Don’t just let SF loose though. Be sure to configure it properly to save time and avoid crawling unnecessary pages.

These settings ensure we’ll get all the info we need to answer our questions in one crawl:

List Mode: Paste in a de-duplicated Full URL list from your STAT data. In my case, this was 6,600+ URLs.

Database Storage Mode: It may be a bit slower than Memory (RAM) Storage, but saving your crawl results on your hard disk ensures you won’t lose your results if you make a mistake (like I have many times) and close your report before you finish analyzing the data.

Limit Crawl Depth: Set this to 0 (zero) so the spider will only crawl the URLs on your list without following internal links to other pages on those domains.

APIs: I highly recommend using the Pagespeed Insights Integration to pull Lighthouse speed metrics directly into your crawl data. If you have a Moz account with API access, you can also pull link and domain data from the Moz API with the built-in integration.

Once you have configured the spider, let it rip! It could take several minutes to several hours depending on how many URLs you’re crawling and your computer’s speed and memory constraints. Just be patient! You might try running larger crawls overnight or on an extra computer to avoid bogging your primary machine down.

Step 8: Export your Screaming Frog crawl data to Excel

Dumping your crawl data into Excel is remarkably easy.

Step 9: Join your data sets in Power BI

At this point, you should have two data sources in Excel: one for your STAT rankings data and another for your Screaming Frog crawl data. Our goal is to combine the two data sources to see how organic search rank may be influenced by on-page SEO elements and site performance. To do this, we must first merge the data.

If you have access to a Windows PC, the free version of Power BI is powerful enough to get you started. Begin by loading your two data sources into a new project using the Get Data wizard.

Once your data sets are loaded, it’s time to make the magic happen by creating relationships in your data to unlock correlations between rankings and site characteristics. To combine your data in Power BI, create a many-to-many relationship between your STAT Full URL and Screaming Frog Original URL fields. 

If you are new to BI tools and data visualization, don’t worry! There are lots of helpful tutorials and videos just a quick search away. At this point, it’s really hard to break anything and you can experiment with lots of ways to analyze your data and share insights with many types of charts and graphs.

I should note that Power BI is my preferred data visualization tool but you may be able to use Tableau or some equally powerful. Google Data Studio was not an option for this analysis because it only allows for left outer joins of the multiple data sources and does not support “many-to-many” relationships. It’s a technical way of saying Data Studio isn’t flexible enough to create the data relationships that we need.

Step 10: Analyze and visualize!

Power BI’s built-in visualizations allow you to quickly summarize and present data. This is where we can start analyzing the data to answer the questions we came up with earlier.

Results — what did we learn?

Here are a couple examples of the insights gleaned from merging our rankings and crawl data. Spoiler alert — CWV doesn’t strongly impact organic rankings….yet!

Who are banks actually competing against in the SERPs? Is it primarily other banks?

On desktops, about 67% of organic search results belong to financial institutions (banks and credit unions) with heavy competition from reviews sites (7%) and online directories (22%). This information helps shape our SEO strategies for banks by exposing opportunities to monitor and maintain listings in relevant directories and reviews sites.

Okay, now let’s mash up our data sources to see how the distribution of website categories varies by rank on desktop devices. Suddenly, we can see that financial institutions actually occupy the majority of the top 3 results while reviews sites and directories are more prevalent in positions 4-10.

How important are Core Web Vitals (CWV) for rankings? How does this change over time?

Site performance and site speed are hot topics in SEO and will only become more important as CWV becomes a ranking signal in May this year. We can begin to understand the relationships between site speed and rankings by comparing STAT rankings and Pagespeed Insights data from Screaming Frog reports.

As of January 2021, sites with higher Lighthouse Performance Scores (i.e. they load faster) tend to rank better than sites with lower scores. This could help justify investments in site speed and site performance.

Some CWV elements correlate more closely with better rankings and others are more scattered. This isn’t to say CWV aren’t important or meaningful, but rather it’s a starting point for further analysis after May.

So what? What can we learn from this type of analysis?

Separately, STAT and Screaming Frog are incredibly powerful SEO tools. The data they provide are useful if you happen to be an SEO but the ability to merge data and extract relationships will multiply your value in any organization that values data, and acts on insights.

Besides validating some generally accepted SEO knowledge with data (“faster sites are rewarded with better rankings”), better use of relational data can also help us avoid spending valuable time on less important tactics (“improve Cumulative Layout Shift at all costs!”).

Of course, correlation does not imply causation, and aggregated data does not guarantee an outcome for individual sites. But if you’re a bank marketing professional responsible for customer acquisition from organic channels, you’ll need to bring this type of data to your stakeholders to justify increased investments in SEO.

By sharing the tools and methodology, I hope others will take it further by building and contributing their additional findings to the SEO community. What other datasets can we combine to deepen our understanding of SERPs on a larger scale? Let me know your thoughts in the comments!

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!

[gamebazu]

10 Steps to Blend STAT Ranking Data with Site Performance Metrics

Posted by AndrewMiller

Too often, we assume that SEO best practices will work in any industry against any competitive set. But most best practices go untested and may not be “best” in every situation.

We all know that tactics that worked in 2020 won’t necessarily move the needle in 2021 as Core Web Vitals (CWV) and other signals shuffle to the front. We have to do better for our businesses and our clients.

I’m a data nerd at heart with lots of battle scars from 15 years in SEO. The idea of analyzing thousands of local SERPs sounded like too much fun to pass up. I found some surprising correlations, and just as importantly, built a methodology and data set that can be updated quarterly to show changes over time.

I analyzed 50,000+ SERPs in the retail banking sector so I could make sense of the massive shifts in rankings and search behaviors during the lockdown period. We have a lot of historical data for bank websites, so comparing pre/post COVID data would be easier than starting from scratch.

I’ll share how I did it below. But first, I want to share WHY I think sharing this type of research is so important for the SEO community.

Why validate SEO best practices with data?

It’s a great time to be an SEO. We have amazing tools and can gather more data than ever. We have thriving communities and excellent basic training materials.

Yet, we often see our craft distilled into overly-simplified “best practices” that are assumed to be universally true. But if there’s one universal truth in SEO, it’s that there are no universal truths. A best practice can be misinterpreted or outdated, leading to missed opportunities or outright harm to a business.

Using the increasing importance of CWV as an example, SEOs have an opportunity (and obligation) to separate fact from fiction. We need to know if, and by how much, CWV will impact rankings over time so we can prioritize our efforts.

We can elevate our SEO game individually and collectively by testing and validating best practices with research. It just takes a curious mind, the right tools, and a willingness to accept the results rather than force a narrative.

Failing to validate best practices is a liability for SEO practitioners and shows an unwillingness to challenge assumptions. In my experience, a lack of data can lead to a senior stakeholders’ opinions carrying more weight than an SEO expert’s recommendations.

Start by asking the right questions

Real insight comes from combining data from multiple sources to answer critical questions and ensure your strategies are backed by valid data. In my analysis of local banks, I started by listing the questions I wanted to know the answers to:

What characteristics are shared by top-ranking local bank websites?

Who are banks actually competing against in the SERPs? Is it primarily other banks?

How do competitive SERPS change based on when/where/how users search?

How can smaller, local businesses gain an edge over larger competitors from outside their region?

How does SERP composition affect a bank’s ability to rank well for targeted keywords?

How important are Core Web Vitals (CWV) for rankings? How does this change over time?

You could run this same analysis by replacing “banks” with other local business categories. The list of potential questions is endless so you can adjust them based on your needs.

Here’s an important reminder - be prepared to accept the answers even if they are inconclusive or contradictory to your assumptions. Data-driven SEOs have to avoid confirmation bias if we’re going to remain objective.

Here’s how I analyzed 50,000 search results in a few hours

I combined three of my favorite tools to analyze SERPs at scale and gather the data needed to answer my questions:

STAT to generated ranking reports for select keywords

Screaming Frog to crawl websites and gather technical SEO data

Power BI to analyze the large data sets and create simple visualizations

Step 1: Determine your data needs

I used US Census Bureau data to identify all cities with populations over 100,000, because I wanted a representation of local bank SERPs across the country. My list ended up including 314 separate cities, but you could customize your list to suit your needs.

I also wanted to gather data for desktop and mobile searches to compare SERP differences between the device types.

Step 2: Identify your keywords

I chose “banks near me” and “banks in {city, st}” based on their strong local intent and high search volumes, compared to more specific keywords for banking services.

Step 3: Generate a STAT import file in .csv format

Once you have your keywords and market list, it’s time to prepare the bulk upload for STAT. Use the template provided in the link to create a .csv file with the following fields:

Project: The name of the new STAT project, or an existing project.

Folder: The name of the new folder, or an existing folder. (This is an optional column that you can leave blank.)

Site: The domain name for the site you want to track. Note, for our purposes you can enter any URL you want to track here. The Top 20 Report will include all ranking URLs for the target keywords even if they aren’t listed in your “Site” column.

Keyword: The search query you’re adding.

Tags: Enter as many keyword tags as you want, separated by commas. I used “city” and “near me” as tags to distinguish between the query types. (This is an optional column that you can leave blank.)

Market: Specify the market (country and language) in which you would like to track the keyword. I used “US-en” for US English.

Location: If you want to track the keyword in a specific location, specify the city, state, province, ZIP code, and/or postal code. I used the city and state list in “city, st” format.

Device: Select whether you would like Desktop or Smartphone results. I selected both.

Each market, location, and device type will multiply the number of keywords you must track. I ended up with 1,256 keywords (314 markets X 2 keywords X 2 devices) in my import file.

Once your file is complete, you can import to STAT and begin tracking.

Step 4: Run a Top 20 Report in STAT for all keywords

STAT’s built-in Google SERP Top 20 Comparison report captures the top 20 organic results from each SERP at different intervals (daily, weekly, monthly, etc.) to look at changes over time. I did not need daily data so I simply let it run on two consecutive days and removed the data I did not need. I re-run the same report quarterly to track changes over time.

Watch the video below to learn how to set up this report! 

My 1,256 keywords generated over 25,000 rows of data per day. Each row is a different organic listing and includes the keyword, monthly search volume, rank (includes the local pack), base rank (does not include the local pack), https/http protocol of the ranking URL, the ranking URL, and your tags.

Here’s an example of the raw output in CSV format:

It’s easy to see how useful this data is by itself but it becomes even more powerful when we clean it up and start crawling the ranking URLs.

Step 5: Clean up and normalize your STAT URLs data

At this point you may have invested 1-2 hours in gathering the initial data. This step is a bit more time consuming, but data cleansing allows you to run more advanced analysis and uncover more useful insights in Screaming Frog.

Here are the changes I made to the STAT rankings data to prepare for the next steps in Screaming Frog and Power BI. You’ll end up with multiple columns of URLs. Each serves a purpose later.

Duplicate the Ranking URL column to a new column called Normalized URL.

Remove URL parameters from the Normalized URL fields by using Excel’s text to columns tool and separating by “?”. I deleted the new columns(s) containing the URL parameters because they were not helpful in my analysis.

Duplicate the new, clean Normalized URL column to a new column called TLD. Use the text to columns tool on the TLD column and separate by “/” to remove everything except the domain name and subdomains. Delete the new columns. I chose to keep the subdomains in my TLD column but you can remove them if it helps your analysis.

Finally, create one more column called Full URL that will eventually become the list of URLs that you’ll crawl in Screaming Frog. To generate the Full URL, simply use Excel’s concatenate function to combine the Protocol and Normalized URL columns. Your formula will look something like this: =concatenate(A1, “://”, C1) to include the “://” in a valid URL string.

The 25,000+ rows in my data set are well within Excel’s limitations, so I am able to manipulate the data easily in one place. You may need to use a database (I like BigQuery) as your data sets grow.

Step 6: Categorize your SERP results by website type

Skimming through the SERP results, it’s easy to see that banks are not the only type of website that rank for keywords with local search intent. Since one of my initial questions was SERP composition, I had to identify all of the different types of websites and label each one for further analysis.

This step is by far the most time consuming and insightful. I spent 3 hours categorizing the initial batch of 25,000+ URLs into one of the following categories:

Institution (banks and credit union websites)

Directory (aggregators, local business directories, etc.)

Reviews (local and national sites like Yelp.com)

Education (content about banks on .edu domains)

Government (content about banks on .gov domains and municipal sites)

Jobs (careers sites and job aggregators)

News (local and national news sites with banking content)

Food Banks (yes, plenty of food banks rank for “banks near me” keywords)

Real Estate (commercial and residential real estate listings)

Search Engines (ranked content belonging to a search engine)

Social Media (ranked content on social media sites)

Other (completely random results not related to any of the above)

Your local SERPs will likely contain many of these website types and other unrelated categories such as food banks. Speed up the process by sorting and filtering your TLD and Normalized URL columns to categorize multiple rows simultaneously. For example, all the yelp.com rankings can be categorized as “Reviews” with a quick copy/paste.

At this point, your rankings data set is complete and you are ready to begin crawling the top-ranking sites in your industry to see what they have in common.

Step 7: Crawl your target websites with Screaming Frog

My initial STAT data identified over 6,600 unique pages from local bank websites that ranked in the top 20 organic search results. This is far too many pages to evaluate manually. Enter Screaming Frog, a crawler that mimics Google’s web crawler and extracts tons of SEO data from websites.

I configured Screaming Frog to crawl each of the 6,600 ranking pages for a larger analysis of characteristics shared by top-ranking bank websites. Don’t just let SF loose though. Be sure to configure it properly to save time and avoid crawling unnecessary pages.

These settings ensure we’ll get all the info we need to answer our questions in one crawl:

List Mode: Paste in a de-duplicated Full URL list from your STAT data. In my case, this was 6,600+ URLs.

Database Storage Mode: It may be a bit slower than Memory (RAM) Storage, but saving your crawl results on your hard disk ensures you won’t lose your results if you make a mistake (like I have many times) and close your report before you finish analyzing the data.

Limit Crawl Depth: Set this to 0 (zero) so the spider will only crawl the URLs on your list without following internal links to other pages on those domains.

APIs: I highly recommend using the Pagespeed Insights Integration to pull Lighthouse speed metrics directly into your crawl data. If you have a Moz account with API access, you can also pull link and domain data from the Moz API with the built-in integration.

Once you have configured the spider, let it rip! It could take several minutes to several hours depending on how many URLs you’re crawling and your computer’s speed and memory constraints. Just be patient! You might try running larger crawls overnight or on an extra computer to avoid bogging your primary machine down.

Step 8: Export your Screaming Frog crawl data to Excel

Dumping your crawl data into Excel is remarkably easy.

Step 9: Join your data sets in Power BI

At this point, you should have two data sources in Excel: one for your STAT rankings data and another for your Screaming Frog crawl data. Our goal is to combine the two data sources to see how organic search rank may be influenced by on-page SEO elements and site performance. To do this, we must first merge the data.

If you have access to a Windows PC, the free version of Power BI is powerful enough to get you started. Begin by loading your two data sources into a new project using the Get Data wizard.

Once your data sets are loaded, it’s time to make the magic happen by creating relationships in your data to unlock correlations between rankings and site characteristics. To combine your data in Power BI, create a many-to-many relationship between your STAT Full URL and Screaming Frog Original URL fields. 

If you are new to BI tools and data visualization, don’t worry! There are lots of helpful tutorials and videos just a quick search away. At this point, it’s really hard to break anything and you can experiment with lots of ways to analyze your data and share insights with many types of charts and graphs.

I should note that Power BI is my preferred data visualization tool but you may be able to use Tableau or some equally powerful. Google Data Studio was not an option for this analysis because it only allows for left outer joins of the multiple data sources and does not support “many-to-many” relationships. It’s a technical way of saying Data Studio isn’t flexible enough to create the data relationships that we need.

Step 10: Analyze and visualize!

Power BI’s built-in visualizations allow you to quickly summarize and present data. This is where we can start analyzing the data to answer the questions we came up with earlier.

Results — what did we learn?

Here are a couple examples of the insights gleaned from merging our rankings and crawl data. Spoiler alert — CWV doesn’t strongly impact organic rankings….yet!

Who are banks actually competing against in the SERPs? Is it primarily other banks?

On desktops, about 67% of organic search results belong to financial institutions (banks and credit unions) with heavy competition from reviews sites (7%) and online directories (22%). This information helps shape our SEO strategies for banks by exposing opportunities to monitor and maintain listings in relevant directories and reviews sites.

Okay, now let’s mash up our data sources to see how the distribution of website categories varies by rank on desktop devices. Suddenly, we can see that financial institutions actually occupy the majority of the top 3 results while reviews sites and directories are more prevalent in positions 4-10.

How important are Core Web Vitals (CWV) for rankings? How does this change over time?

Site performance and site speed are hot topics in SEO and will only become more important as CWV becomes a ranking signal in May this year. We can begin to understand the relationships between site speed and rankings by comparing STAT rankings and Pagespeed Insights data from Screaming Frog reports.

As of January 2021, sites with higher Lighthouse Performance Scores (i.e. they load faster) tend to rank better than sites with lower scores. This could help justify investments in site speed and site performance.

Some CWV elements correlate more closely with better rankings and others are more scattered. This isn’t to say CWV aren’t important or meaningful, but rather it’s a starting point for further analysis after May.

So what? What can we learn from this type of analysis?

Separately, STAT and Screaming Frog are incredibly powerful SEO tools. The data they provide are useful if you happen to be an SEO but the ability to merge data and extract relationships will multiply your value in any organization that values data, and acts on insights.

Besides validating some generally accepted SEO knowledge with data (“faster sites are rewarded with better rankings”), better use of relational data can also help us avoid spending valuable time on less important tactics (“improve Cumulative Layout Shift at all costs!”).

Of course, correlation does not imply causation, and aggregated data does not guarantee an outcome for individual sites. But if you’re a bank marketing professional responsible for customer acquisition from organic channels, you’ll need to bring this type of data to your stakeholders to justify increased investments in SEO.

By sharing the tools and methodology, I hope others will take it further by building and contributing their additional findings to the SEO community. What other datasets can we combine to deepen our understanding of SERPs on a larger scale? Let me know your thoughts in the comments!

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!

https://ift.tt/3ea8T85

[kjt-lawyers]

10 Steps to Blend STAT Ranking Data with Site Performance Metrics

Posted by AndrewMiller

Too often, we assume that SEO best practices will work in any industry against any competitive set. But most best practices go untested and may not be “best” in every situation.

We all know that tactics that worked in 2020 won’t necessarily move the needle in 2021 as Core Web Vitals (CWV) and other signals shuffle to the front. We have to do better for our businesses and our clients.

I’m a data nerd at heart with lots of battle scars from 15 years in SEO. The idea of analyzing thousands of local SERPs sounded like too much fun to pass up. I found some surprising correlations, and just as importantly, built a methodology and data set that can be updated quarterly to show changes over time.

I analyzed 50,000+ SERPs in the retail banking sector so I could make sense of the massive shifts in rankings and search behaviors during the lockdown period. We have a lot of historical data for bank websites, so comparing pre/post COVID data would be easier than starting from scratch.

I’ll share how I did it below. But first, I want to share WHY I think sharing this type of research is so important for the SEO community.

Why validate SEO best practices with data?

It’s a great time to be an SEO. We have amazing tools and can gather more data than ever. We have thriving communities and excellent basic training materials.

Yet, we often see our craft distilled into overly-simplified “best practices” that are assumed to be universally true. But if there’s one universal truth in SEO, it’s that there are no universal truths. A best practice can be misinterpreted or outdated, leading to missed opportunities or outright harm to a business.

Using the increasing importance of CWV as an example, SEOs have an opportunity (and obligation) to separate fact from fiction. We need to know if, and by how much, CWV will impact rankings over time so we can prioritize our efforts.

We can elevate our SEO game individually and collectively by testing and validating best practices with research. It just takes a curious mind, the right tools, and a willingness to accept the results rather than force a narrative.

Failing to validate best practices is a liability for SEO practitioners and shows an unwillingness to challenge assumptions. In my experience, a lack of data can lead to a senior stakeholders’ opinions carrying more weight than an SEO expert’s recommendations.

Start by asking the right questions

Real insight comes from combining data from multiple sources to answer critical questions and ensure your strategies are backed by valid data. In my analysis of local banks, I started by listing the questions I wanted to know the answers to:

What characteristics are shared by top-ranking local bank websites?

Who are banks actually competing against in the SERPs? Is it primarily other banks?

How do competitive SERPS change based on when/where/how users search?

How can smaller, local businesses gain an edge over larger competitors from outside their region?

How does SERP composition affect a bank’s ability to rank well for targeted keywords?

How important are Core Web Vitals (CWV) for rankings? How does this change over time?

You could run this same analysis by replacing “banks” with other local business categories. The list of potential questions is endless so you can adjust them based on your needs.

Here’s an important reminder - be prepared to accept the answers even if they are inconclusive or contradictory to your assumptions. Data-driven SEOs have to avoid confirmation bias if we’re going to remain objective.

Here’s how I analyzed 50,000 search results in a few hours

I combined three of my favorite tools to analyze SERPs at scale and gather the data needed to answer my questions:

STAT to generated ranking reports for select keywords

Screaming Frog to crawl websites and gather technical SEO data

Power BI to analyze the large data sets and create simple visualizations

Step 1: Determine your data needs

I used US Census Bureau data to identify all cities with populations over 100,000, because I wanted a representation of local bank SERPs across the country. My list ended up including 314 separate cities, but you could customize your list to suit your needs.

I also wanted to gather data for desktop and mobile searches to compare SERP differences between the device types.

Step 2: Identify your keywords

I chose “banks near me” and “banks in {city, st}” based on their strong local intent and high search volumes, compared to more specific keywords for banking services.

Step 3: Generate a STAT import file in .csv format

Once you have your keywords and market list, it’s time to prepare the bulk upload for STAT. Use the template provided in the link to create a .csv file with the following fields:

Project: The name of the new STAT project, or an existing project.

Folder: The name of the new folder, or an existing folder. (This is an optional column that you can leave blank.)

Site: The domain name for the site you want to track. Note, for our purposes you can enter any URL you want to track here. The Top 20 Report will include all ranking URLs for the target keywords even if they aren’t listed in your “Site” column.

Keyword: The search query you’re adding.

Tags: Enter as many keyword tags as you want, separated by commas. I used “city” and “near me” as tags to distinguish between the query types. (This is an optional column that you can leave blank.)

Market: Specify the market (country and language) in which you would like to track the keyword. I used “US-en” for US English.

Location: If you want to track the keyword in a specific location, specify the city, state, province, ZIP code, and/or postal code. I used the city and state list in “city, st” format.

Device: Select whether you would like Desktop or Smartphone results. I selected both.

Each market, location, and device type will multiply the number of keywords you must track. I ended up with 1,256 keywords (314 markets X 2 keywords X 2 devices) in my import file.

Once your file is complete, you can import to STAT and begin tracking.

Step 4: Run a Top 20 Report in STAT for all keywords

STAT’s built-in Google SERP Top 20 Comparison report captures the top 20 organic results from each SERP at different intervals (daily, weekly, monthly, etc.) to look at changes over time. I did not need daily data so I simply let it run on two consecutive days and removed the data I did not need. I re-run the same report quarterly to track changes over time.

Watch the video below to learn how to set up this report! 

My 1,256 keywords generated over 25,000 rows of data per day. Each row is a different organic listing and includes the keyword, monthly search volume, rank (includes the local pack), base rank (does not include the local pack), https/http protocol of the ranking URL, the ranking URL, and your tags.

Here’s an example of the raw output in CSV format:

It’s easy to see how useful this data is by itself but it becomes even more powerful when we clean it up and start crawling the ranking URLs.

Step 5: Clean up and normalize your STAT URLs data

At this point you may have invested 1-2 hours in gathering the initial data. This step is a bit more time consuming, but data cleansing allows you to run more advanced analysis and uncover more useful insights in Screaming Frog.

Here are the changes I made to the STAT rankings data to prepare for the next steps in Screaming Frog and Power BI. You’ll end up with multiple columns of URLs. Each serves a purpose later.

Duplicate the Ranking URL column to a new column called Normalized URL.

Remove URL parameters from the Normalized URL fields by using Excel’s text to columns tool and separating by “?”. I deleted the new columns(s) containing the URL parameters because they were not helpful in my analysis.

Duplicate the new, clean Normalized URL column to a new column called TLD. Use the text to columns tool on the TLD column and separate by “/” to remove everything except the domain name and subdomains. Delete the new columns. I chose to keep the subdomains in my TLD column but you can remove them if it helps your analysis.

Finally, create one more column called Full URL that will eventually become the list of URLs that you’ll crawl in Screaming Frog. To generate the Full URL, simply use Excel’s concatenate function to combine the Protocol and Normalized URL columns. Your formula will look something like this: =concatenate(A1, “://”, C1) to include the “://” in a valid URL string.

The 25,000+ rows in my data set are well within Excel’s limitations, so I am able to manipulate the data easily in one place. You may need to use a database (I like BigQuery) as your data sets grow.

Step 6: Categorize your SERP results by website type

Skimming through the SERP results, it’s easy to see that banks are not the only type of website that rank for keywords with local search intent. Since one of my initial questions was SERP composition, I had to identify all of the different types of websites and label each one for further analysis.

This step is by far the most time consuming and insightful. I spent 3 hours categorizing the initial batch of 25,000+ URLs into one of the following categories:

Institution (banks and credit union websites)

Directory (aggregators, local business directories, etc.)

Reviews (local and national sites like Yelp.com)

Education (content about banks on .edu domains)

Government (content about banks on .gov domains and municipal sites)

Jobs (careers sites and job aggregators)

News (local and national news sites with banking content)

Food Banks (yes, plenty of food banks rank for “banks near me” keywords)

Real Estate (commercial and residential real estate listings)

Search Engines (ranked content belonging to a search engine)

Social Media (ranked content on social media sites)

Other (completely random results not related to any of the above)

Your local SERPs will likely contain many of these website types and other unrelated categories such as food banks. Speed up the process by sorting and filtering your TLD and Normalized URL columns to categorize multiple rows simultaneously. For example, all the yelp.com rankings can be categorized as “Reviews” with a quick copy/paste.

At this point, your rankings data set is complete and you are ready to begin crawling the top-ranking sites in your industry to see what they have in common.

Step 7: Crawl your target websites with Screaming Frog

My initial STAT data identified over 6,600 unique pages from local bank websites that ranked in the top 20 organic search results. This is far too many pages to evaluate manually. Enter Screaming Frog, a crawler that mimics Google’s web crawler and extracts tons of SEO data from websites.

I configured Screaming Frog to crawl each of the 6,600 ranking pages for a larger analysis of characteristics shared by top-ranking bank websites. Don’t just let SF loose though. Be sure to configure it properly to save time and avoid crawling unnecessary pages.

These settings ensure we’ll get all the info we need to answer our questions in one crawl:

List Mode: Paste in a de-duplicated Full URL list from your STAT data. In my case, this was 6,600+ URLs.

Database Storage Mode: It may be a bit slower than Memory (RAM) Storage, but saving your crawl results on your hard disk ensures you won’t lose your results if you make a mistake (like I have many times) and close your report before you finish analyzing the data.

Limit Crawl Depth: Set this to 0 (zero) so the spider will only crawl the URLs on your list without following internal links to other pages on those domains.

APIs: I highly recommend using the Pagespeed Insights Integration to pull Lighthouse speed metrics directly into your crawl data. If you have a Moz account with API access, you can also pull link and domain data from the Moz API with the built-in integration.

Once you have configured the spider, let it rip! It could take several minutes to several hours depending on how many URLs you’re crawling and your computer’s speed and memory constraints. Just be patient! You might try running larger crawls overnight or on an extra computer to avoid bogging your primary machine down.

Step 8: Export your Screaming Frog crawl data to Excel

Dumping your crawl data into Excel is remarkably easy.

Step 9: Join your data sets in Power BI

At this point, you should have two data sources in Excel: one for your STAT rankings data and another for your Screaming Frog crawl data. Our goal is to combine the two data sources to see how organic search rank may be influenced by on-page SEO elements and site performance. To do this, we must first merge the data.

If you have access to a Windows PC, the free version of Power BI is powerful enough to get you started. Begin by loading your two data sources into a new project using the Get Data wizard.

Once your data sets are loaded, it’s time to make the magic happen by creating relationships in your data to unlock correlations between rankings and site characteristics. To combine your data in Power BI, create a many-to-many relationship between your STAT Full URL and Screaming Frog Original URL fields. 

If you are new to BI tools and data visualization, don’t worry! There are lots of helpful tutorials and videos just a quick search away. At this point, it’s really hard to break anything and you can experiment with lots of ways to analyze your data and share insights with many types of charts and graphs.

I should note that Power BI is my preferred data visualization tool but you may be able to use Tableau or some equally powerful. Google Data Studio was not an option for this analysis because it only allows for left outer joins of the multiple data sources and does not support “many-to-many” relationships. It’s a technical way of saying Data Studio isn’t flexible enough to create the data relationships that we need.

Step 10: Analyze and visualize!

Power BI’s built-in visualizations allow you to quickly summarize and present data. This is where we can start analyzing the data to answer the questions we came up with earlier.

Results — what did we learn?

Here are a couple examples of the insights gleaned from merging our rankings and crawl data. Spoiler alert — CWV doesn’t strongly impact organic rankings….yet!

Who are banks actually competing against in the SERPs? Is it primarily other banks?

On desktops, about 67% of organic search results belong to financial institutions (banks and credit unions) with heavy competition from reviews sites (7%) and online directories (22%). This information helps shape our SEO strategies for banks by exposing opportunities to monitor and maintain listings in relevant directories and reviews sites.

Okay, now let’s mash up our data sources to see how the distribution of website categories varies by rank on desktop devices. Suddenly, we can see that financial institutions actually occupy the majority of the top 3 results while reviews sites and directories are more prevalent in positions 4-10.

How important are Core Web Vitals (CWV) for rankings? How does this change over time?

Site performance and site speed are hot topics in SEO and will only become more important as CWV becomes a ranking signal in May this year. We can begin to understand the relationships between site speed and rankings by comparing STAT rankings and Pagespeed Insights data from Screaming Frog reports.

As of January 2021, sites with higher Lighthouse Performance Scores (i.e. they load faster) tend to rank better than sites with lower scores. This could help justify investments in site speed and site performance.

Some CWV elements correlate more closely with better rankings and others are more scattered. This isn’t to say CWV aren’t important or meaningful, but rather it’s a starting point for further analysis after May.

So what? What can we learn from this type of analysis?

Separately, STAT and Screaming Frog are incredibly powerful SEO tools. The data they provide are useful if you happen to be an SEO but the ability to merge data and extract relationships will multiply your value in any organization that values data, and acts on insights.

Besides validating some generally accepted SEO knowledge with data (“faster sites are rewarded with better rankings”), better use of relational data can also help us avoid spending valuable time on less important tactics (“improve Cumulative Layout Shift at all costs!”).

Of course, correlation does not imply causation, and aggregated data does not guarantee an outcome for individual sites. But if you’re a bank marketing professional responsible for customer acquisition from organic channels, you’ll need to bring this type of data to your stakeholders to justify increased investments in SEO.

By sharing the tools and methodology, I hope others will take it further by building and contributing their additional findings to the SEO community. What other datasets can we combine to deepen our understanding of SERPs on a larger scale? Let me know your thoughts in the comments!

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!

[noithatotoaz]

10 Steps to Blend STAT Ranking Data with Site Performance Metrics

Posted by AndrewMiller

Too often, we assume that SEO best practices will work in any industry against any competitive set. But most best practices go untested and may not be “best” in every situation.

We all know that tactics that worked in 2020 won’t necessarily move the needle in 2021 as Core Web Vitals (CWV) and other signals shuffle to the front. We have to do better for our businesses and our clients.

I’m a data nerd at heart with lots of battle scars from 15 years in SEO. The idea of analyzing thousands of local SERPs sounded like too much fun to pass up. I found some surprising correlations, and just as importantly, built a methodology and data set that can be updated quarterly to show changes over time.

I analyzed 50,000+ SERPs in the retail banking sector so I could make sense of the massive shifts in rankings and search behaviors during the lockdown period. We have a lot of historical data for bank websites, so comparing pre/post COVID data would be easier than starting from scratch.

I’ll share how I did it below. But first, I want to share WHY I think sharing this type of research is so important for the SEO community.

Why validate SEO best practices with data?

It’s a great time to be an SEO. We have amazing tools and can gather more data than ever. We have thriving communities and excellent basic training materials.

Yet, we often see our craft distilled into overly-simplified “best practices” that are assumed to be universally true. But if there’s one universal truth in SEO, it’s that there are no universal truths. A best practice can be misinterpreted or outdated, leading to missed opportunities or outright harm to a business.

Using the increasing importance of CWV as an example, SEOs have an opportunity (and obligation) to separate fact from fiction. We need to know if, and by how much, CWV will impact rankings over time so we can prioritize our efforts.

We can elevate our SEO game individually and collectively by testing and validating best practices with research. It just takes a curious mind, the right tools, and a willingness to accept the results rather than force a narrative.

Failing to validate best practices is a liability for SEO practitioners and shows an unwillingness to challenge assumptions. In my experience, a lack of data can lead to a senior stakeholders’ opinions carrying more weight than an SEO expert’s recommendations.

Start by asking the right questions

Real insight comes from combining data from multiple sources to answer critical questions and ensure your strategies are backed by valid data. In my analysis of local banks, I started by listing the questions I wanted to know the answers to:

What characteristics are shared by top-ranking local bank websites?

Who are banks actually competing against in the SERPs? Is it primarily other banks?

How do competitive SERPS change based on when/where/how users search?

How can smaller, local businesses gain an edge over larger competitors from outside their region?

How does SERP composition affect a bank’s ability to rank well for targeted keywords?

How important are Core Web Vitals (CWV) for rankings? How does this change over time?

You could run this same analysis by replacing “banks” with other local business categories. The list of potential questions is endless so you can adjust them based on your needs.

Here’s an important reminder - be prepared to accept the answers even if they are inconclusive or contradictory to your assumptions. Data-driven SEOs have to avoid confirmation bias if we’re going to remain objective.

Here’s how I analyzed 50,000 search results in a few hours

I combined three of my favorite tools to analyze SERPs at scale and gather the data needed to answer my questions:

STAT to generated ranking reports for select keywords

Screaming Frog to crawl websites and gather technical SEO data

Power BI to analyze the large data sets and create simple visualizations

Step 1: Determine your data needs

I used US Census Bureau data to identify all cities with populations over 100,000, because I wanted a representation of local bank SERPs across the country. My list ended up including 314 separate cities, but you could customize your list to suit your needs.

I also wanted to gather data for desktop and mobile searches to compare SERP differences between the device types.

Step 2: Identify your keywords

I chose “banks near me” and “banks in {city, st}” based on their strong local intent and high search volumes, compared to more specific keywords for banking services.

Step 3: Generate a STAT import file in .csv format

Once you have your keywords and market list, it’s time to prepare the bulk upload for STAT. Use the template provided in the link to create a .csv file with the following fields:

Project: The name of the new STAT project, or an existing project.

Folder: The name of the new folder, or an existing folder. (This is an optional column that you can leave blank.)

Site: The domain name for the site you want to track. Note, for our purposes you can enter any URL you want to track here. The Top 20 Report will include all ranking URLs for the target keywords even if they aren’t listed in your “Site” column.

Keyword: The search query you’re adding.

Tags: Enter as many keyword tags as you want, separated by commas. I used “city” and “near me” as tags to distinguish between the query types. (This is an optional column that you can leave blank.)

Market: Specify the market (country and language) in which you would like to track the keyword. I used “US-en” for US English.

Location: If you want to track the keyword in a specific location, specify the city, state, province, ZIP code, and/or postal code. I used the city and state list in “city, st” format.

Device: Select whether you would like Desktop or Smartphone results. I selected both.

Each market, location, and device type will multiply the number of keywords you must track. I ended up with 1,256 keywords (314 markets X 2 keywords X 2 devices) in my import file.

Once your file is complete, you can import to STAT and begin tracking.

Step 4: Run a Top 20 Report in STAT for all keywords

STAT’s built-in Google SERP Top 20 Comparison report captures the top 20 organic results from each SERP at different intervals (daily, weekly, monthly, etc.) to look at changes over time. I did not need daily data so I simply let it run on two consecutive days and removed the data I did not need. I re-run the same report quarterly to track changes over time.

Watch the video below to learn how to set up this report! 

My 1,256 keywords generated over 25,000 rows of data per day. Each row is a different organic listing and includes the keyword, monthly search volume, rank (includes the local pack), base rank (does not include the local pack), https/http protocol of the ranking URL, the ranking URL, and your tags.

Here’s an example of the raw output in CSV format:

It’s easy to see how useful this data is by itself but it becomes even more powerful when we clean it up and start crawling the ranking URLs.

Step 5: Clean up and normalize your STAT URLs data

At this point you may have invested 1-2 hours in gathering the initial data. This step is a bit more time consuming, but data cleansing allows you to run more advanced analysis and uncover more useful insights in Screaming Frog.

Here are the changes I made to the STAT rankings data to prepare for the next steps in Screaming Frog and Power BI. You’ll end up with multiple columns of URLs. Each serves a purpose later.

Duplicate the Ranking URL column to a new column called Normalized URL.

Remove URL parameters from the Normalized URL fields by using Excel’s text to columns tool and separating by “?”. I deleted the new columns(s) containing the URL parameters because they were not helpful in my analysis.

Duplicate the new, clean Normalized URL column to a new column called TLD. Use the text to columns tool on the TLD column and separate by “/” to remove everything except the domain name and subdomains. Delete the new columns. I chose to keep the subdomains in my TLD column but you can remove them if it helps your analysis.

Finally, create one more column called Full URL that will eventually become the list of URLs that you’ll crawl in Screaming Frog. To generate the Full URL, simply use Excel’s concatenate function to combine the Protocol and Normalized URL columns. Your formula will look something like this: =concatenate(A1, “://”, C1) to include the “://” in a valid URL string.

The 25,000+ rows in my data set are well within Excel’s limitations, so I am able to manipulate the data easily in one place. You may need to use a database (I like BigQuery) as your data sets grow.

Step 6: Categorize your SERP results by website type

Skimming through the SERP results, it’s easy to see that banks are not the only type of website that rank for keywords with local search intent. Since one of my initial questions was SERP composition, I had to identify all of the different types of websites and label each one for further analysis.

This step is by far the most time consuming and insightful. I spent 3 hours categorizing the initial batch of 25,000+ URLs into one of the following categories:

Institution (banks and credit union websites)

Directory (aggregators, local business directories, etc.)

Reviews (local and national sites like Yelp.com)

Education (content about banks on .edu domains)

Government (content about banks on .gov domains and municipal sites)

Jobs (careers sites and job aggregators)

News (local and national news sites with banking content)

Food Banks (yes, plenty of food banks rank for “banks near me” keywords)

Real Estate (commercial and residential real estate listings)

Search Engines (ranked content belonging to a search engine)

Social Media (ranked content on social media sites)

Other (completely random results not related to any of the above)

Your local SERPs will likely contain many of these website types and other unrelated categories such as food banks. Speed up the process by sorting and filtering your TLD and Normalized URL columns to categorize multiple rows simultaneously. For example, all the yelp.com rankings can be categorized as “Reviews” with a quick copy/paste.

At this point, your rankings data set is complete and you are ready to begin crawling the top-ranking sites in your industry to see what they have in common.

Step 7: Crawl your target websites with Screaming Frog

My initial STAT data identified over 6,600 unique pages from local bank websites that ranked in the top 20 organic search results. This is far too many pages to evaluate manually. Enter Screaming Frog, a crawler that mimics Google’s web crawler and extracts tons of SEO data from websites.

I configured Screaming Frog to crawl each of the 6,600 ranking pages for a larger analysis of characteristics shared by top-ranking bank websites. Don’t just let SF loose though. Be sure to configure it properly to save time and avoid crawling unnecessary pages.

These settings ensure we’ll get all the info we need to answer our questions in one crawl:

List Mode: Paste in a de-duplicated Full URL list from your STAT data. In my case, this was 6,600+ URLs.

Database Storage Mode: It may be a bit slower than Memory (RAM) Storage, but saving your crawl results on your hard disk ensures you won’t lose your results if you make a mistake (like I have many times) and close your report before you finish analyzing the data.

Limit Crawl Depth: Set this to 0 (zero) so the spider will only crawl the URLs on your list without following internal links to other pages on those domains.

APIs: I highly recommend using the Pagespeed Insights Integration to pull Lighthouse speed metrics directly into your crawl data. If you have a Moz account with API access, you can also pull link and domain data from the Moz API with the built-in integration.

Once you have configured the spider, let it rip! It could take several minutes to several hours depending on how many URLs you’re crawling and your computer’s speed and memory constraints. Just be patient! You might try running larger crawls overnight or on an extra computer to avoid bogging your primary machine down.

Step 8: Export your Screaming Frog crawl data to Excel

Dumping your crawl data into Excel is remarkably easy.

Step 9: Join your data sets in Power BI

At this point, you should have two data sources in Excel: one for your STAT rankings data and another for your Screaming Frog crawl data. Our goal is to combine the two data sources to see how organic search rank may be influenced by on-page SEO elements and site performance. To do this, we must first merge the data.

If you have access to a Windows PC, the free version of Power BI is powerful enough to get you started. Begin by loading your two data sources into a new project using the Get Data wizard.

Once your data sets are loaded, it’s time to make the magic happen by creating relationships in your data to unlock correlations between rankings and site characteristics. To combine your data in Power BI, create a many-to-many relationship between your STAT Full URL and Screaming Frog Original URL fields. 

If you are new to BI tools and data visualization, don’t worry! There are lots of helpful tutorials and videos just a quick search away. At this point, it’s really hard to break anything and you can experiment with lots of ways to analyze your data and share insights with many types of charts and graphs.

I should note that Power BI is my preferred data visualization tool but you may be able to use Tableau or some equally powerful. Google Data Studio was not an option for this analysis because it only allows for left outer joins of the multiple data sources and does not support “many-to-many” relationships. It’s a technical way of saying Data Studio isn’t flexible enough to create the data relationships that we need.

Step 10: Analyze and visualize!

Power BI’s built-in visualizations allow you to quickly summarize and present data. This is where we can start analyzing the data to answer the questions we came up with earlier.

Results — what did we learn?

Here are a couple examples of the insights gleaned from merging our rankings and crawl data. Spoiler alert — CWV doesn’t strongly impact organic rankings….yet!

Who are banks actually competing against in the SERPs? Is it primarily other banks?

On desktops, about 67% of organic search results belong to financial institutions (banks and credit unions) with heavy competition from reviews sites (7%) and online directories (22%). This information helps shape our SEO strategies for banks by exposing opportunities to monitor and maintain listings in relevant directories and reviews sites.

Okay, now let’s mash up our data sources to see how the distribution of website categories varies by rank on desktop devices. Suddenly, we can see that financial institutions actually occupy the majority of the top 3 results while reviews sites and directories are more prevalent in positions 4-10.

How important are Core Web Vitals (CWV) for rankings? How does this change over time?

Site performance and site speed are hot topics in SEO and will only become more important as CWV becomes a ranking signal in May this year. We can begin to understand the relationships between site speed and rankings by comparing STAT rankings and Pagespeed Insights data from Screaming Frog reports.

As of January 2021, sites with higher Lighthouse Performance Scores (i.e. they load faster) tend to rank better than sites with lower scores. This could help justify investments in site speed and site performance.

Some CWV elements correlate more closely with better rankings and others are more scattered. This isn’t to say CWV aren’t important or meaningful, but rather it’s a starting point for further analysis after May.

So what? What can we learn from this type of analysis?

Separately, STAT and Screaming Frog are incredibly powerful SEO tools. The data they provide are useful if you happen to be an SEO but the ability to merge data and extract relationships will multiply your value in any organization that values data, and acts on insights.

Besides validating some generally accepted SEO knowledge with data (“faster sites are rewarded with better rankings”), better use of relational data can also help us avoid spending valuable time on less important tactics (“improve Cumulative Layout Shift at all costs!”).

Of course, correlation does not imply causation, and aggregated data does not guarantee an outcome for individual sites. But if you’re a bank marketing professional responsible for customer acquisition from organic channels, you’ll need to bring this type of data to your stakeholders to justify increased investments in SEO.

By sharing the tools and methodology, I hope others will take it further by building and contributing their additional findings to the SEO community. What other datasets can we combine to deepen our understanding of SERPs on a larger scale? Let me know your thoughts in the comments!

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!