#Compare two columns in excel and remove duplicates

[dritastock]

Compare two columns in excel and remove duplicates

#COMPARE TWO COLUMNS IN EXCEL AND REMOVE DUPLICATES HOW TO#

If you need to remove the highlighting we used for the unique values, it’s pretty simple. To remove the filter completely, go again to the Home tab, click on ‘Sort & Filter’ one more time and unselect the option ‘Filter’.Īnd that’s all it takes! The Filter option has been removed.Īnd before we wrap it up, there’s one more thing worth mentioning. To display all the values unsorted, click on ‘Clear Filter’ and the column will show all values again. You do the same if you need to filter out only the duplicates – simply use the option ‘Sort by Color’ again to have the duplicates displayed all together. The filter option now shows in the header of both columns and we can conveniently sort the data in a column based on the colour.įor example, we can display only the unique values in the first column. Select the whole area (header included), go to the Home tab, click on ‘Sort & Filter’ and choose ‘Filter’. How to Turn on the Filter to Help You Sort the ValuesĪs soon as the unique values have been highlighted, we can turn on the filter to help us sort the values, which might help in further data processing. Confirm, and let’s have a look at other useful tricks that can help you efficiently compare the column contents. Let’s use this option and have the values highlighted in yellow. Of course, we can choose the colour we want to use to highlight the values. So, we want to highlight the values that are present only in one of the columns. Here in this window, we can choose whether we want to highlight duplicates – the values showing in the selected area twice, or we can choose the option ‘Unique’, which means that Excel will highlight the values that occur in both columns only once.Īnd this is exactly what we want because we can quickly find the differences between the columns in terms of the values they contain. Then we go to the Home tab, click on Conditional Formatting and hover over ��Highlight Cell Rules’.įrom the options that appeared, we select ‘Duplicate Values’. To find differences between the contents of two columns in Excel, we need to select both columns first.

#COMPARE TWO COLUMNS IN EXCEL AND REMOVE DUPLICATES HOW TO#

4 How to Remove to Highlighted Data How to Compare Two Columns in Excel to Find Differences

[graphickilop]

Compare two columns in excel and remove duplicates

#Compare two columns in excel and remove duplicates how to

In the New Formatting Rule window, select the option that says Use a formula to determine which cells to format. On the Home tab of the ribbon, choose the Conditional Formatting drop-down menu and select New Rule. In our case it is all of the phone numbers on the “Current” worksheet. Now that we've looked at how the formula works, let's see how the conditional formatting is applied.įirst select the entire range of cells that you want the formatting applied to. That's because when we apply the conditional formatting to our entire table, Excel will examine each cell individually to see if the criteria apply, but only if it's expressed as a relative reference (no dollar symbols in the reference). It's important that the B2 used for the criteria argument is expressed as a relative reference, not an absolute reference. With conditional formatting, we use those numbers to highlight the entries that are duplicates.įor our example, the formula looks like this: =COUNTIF(Historical!$B$2:$F$1001,B2) If it is NOT found on that sheet, it will return a zero. If the value that is found in cell B2 is also found in our designated range on the Historical tab, the COUNTIF function will return a number greater than zero. The criteria argument is simply the value that we are looking for. In my video tutorial, my range is from B2 to F1001 on the “Historical” sheet. Range is the group of cells that you want to look in to find a specific value. The first is range and the second is criteria. For this process we are using the COUNTIF function.ĬOUNTIF has two arguments. If so, it will return a value of the number of times that data is found. The formula we'll write is going to examine a cell to see if its contents can be found in another range that we specify.

#Compare two columns in excel and remove duplicates how to

Click to enlargeįirst let's look at how to write the formula and then we will see how to apply the conditional formatting. You can do so using a formula and conditional formatting. Let's say you have two Excel worksheets that have overlapping data and you want to call attention to any cells that have duplicate entries. Compare-Sheets-with-Conditional-Formatting-AFTER.xlsx Highlighting Duplicates Between Worksheets

[trustsdirect]

Compare two columns in excel and remove duplicates

Highlight duplicates in two sheets or columns How do I highlight duplicates in Google Sheets between two spreadsheets? Now go to Home-x26gt Conditional Formatting-x26gt Highlight Cells Rules-x26gt Duplicate Values.To select non-adjacent ranges, hold the CTRL key and use the mouse to select ranges. To highlight duplicates, select all ranges.Highlight Duplicate in Two or More Ranges How do I highlight duplicate values in two columns? Enter the ranges of the columns you want to compare.Click the ‘Dedupe Compare’ menu option then select ‘Compare two sheets’.Once Power Tools is added to your Google Sheets, go to the Add-Ons pull-down menu.How do I compare two columns in Google Sheets to find duplicates? How do you highlight matched cells in Excel?.How do you highlight cells that match cells in another column?.How do you highlight matching data in two columns?.How do I find the match between two columns in Excel?.How do I compare data in three columns in Excel to find duplicates?.How do you use Vlookup to find duplicates in two columns?.How do I compare two columns in Excel for matching?.How do you compare two sets of data for duplicates?.How do I compare two lists for matches in Google Sheets?.How do I compare two columns and differences in Google Sheets?.How do I compare duplicates in Google Sheets?.How do I check if a column has duplicates in Google Sheets?.How do I match data in two Google Sheets?.How do I find duplicates in two spreadsheets?.How do you build a list of duplicates between two columns Google Sheets?.How do I compare two Google spreadsheets for duplicates?.Is it possible to highlight duplicates between columns but not within columns?.How do you highlight the matching cell in two columns?.How do I highlight duplicate values between two columns in Excel?.How do you compare two columns to find duplicates?.How do I compare two lists for duplicates in Google Sheets?.How do I compare columns in Google Sheets for duplicates?.How do I highlight duplicates in Google Sheets between two spreadsheets?.How do I highlight duplicate values in two columns?.How do I compare two columns in Google Sheets to find duplicates?.

[tgbroken-silhouette-blog]

Commodity Index Strategy

The Commodity Index Strategy is a fully diversified, turn key trading strategy that trades 27 of the most highly liquid futures markets available. It trades across seven non-correlated market sectors: Energies, Financials, Currencies, Metals, Meats, Grains and Softs. The strategy exploits price trends in the markets by waiting for low risk, high opportunity trades to occur. It uses wide stops to stay in the trade and capture big, bull-market moves. Subscribers to the Commodity Index Strategy are essentially creating their own personal commodity index. The strategy is made up of three core components. These unique, highly-advanced components give the Commodity Index Strategy an enormous trading edge over other commodity trading strategies and indexes. They are:

• Fundamental driven inputs • Directional biased signals • Superior money management controls

Fundamentally Driven Inputs

The Commodity Index strategy begins with the proprietary valuation determination algorithm. This 100% systematic filter determines when a market is under or over valued. The program also takes advantage of common sense, real-world trading inputs to filter out trades that do not make sense in the real world. These fundamental inputs are then passed on to the next component of the strategy.

Directionally Biased Signals

Market sectors traded do not necessarily trade long and short. This component of the strategy keys off of the fundamental inputs and establishes a trade direction for the market sector. They are long only, short only, or long and short.

Superior Money Management Controls

All signals generated must pass through the risk and money management filters. The strategy can then examine risk prior to selecting the trades for the account. Each trade has to meet certain criteria to ensure that no one trade can adversely impact your account. The Commodity Index Strategy also monitors the amount of risk that it will accept in each market group to protect against too much exposure in any one sector. The markets that make up each sector are usually highly correlated. Finally, the Commodity Index Strategy manages the total risk of your account by monitoring the amount of exposure in all the open positions. This protects your capital against a random event. No other commodity index actively manages your exposure to the individual markets. Investors participating in any of the mainstream commodity indexes still need to know when it is a good time to buy or sell. After this decision, they can still only purchase the index in its entirety. The Commodity Index Strategy switches back and forth from a cash position to being fully invested based on the proprietary valuation algorithm. The Commodity Index Strategy will also not initiate new positions in any over-valued markets. These features remove all the guess work by the investor.

Analysis of Real Time Results vs. Backtest Results

The Commodity Index Strategy began trading on June 15, 2007. It ended 2007 + 29.89% (not including T-bill interest and collateral held in foreign currencies) after only five and a half months of trading! Below is an analysis between the real-time trading performance and the results of the backtest, from June 15, 2007 to December 31, 2007. The tables below show three sets of trades. Table 1 is the actual trades by the Commodity Index Strategy. Table 2 is the trades from the backtest, from June 4, 2007 until December 31, 2007. Table 3 is the trades from the Order Manager from the Mechanica Trading platform. This is the exact order sheet used by Angus Jackson traders. Table 1 shows actual trades with real slippage and commission. We pay a discount commission, so we added the last column in table 1 to show the actual results with a full service commission as well. There are only two slight discrepancies between the orders and the actual trades. The first error is in the Sept 07 US Dollar Index. In our account, the trade was exited on 08/15/07 at 81.710. It was reentered on 8/21/07 at 81.250. This resulted in a $460 error plus an extra $18.34 round turn commission that went against us. This means that the trade should have had a $478.34 larger profit. The second error is more of a discrepancy between the real world and the hypothetical. Notice the Dec07 Mini-gold roll on July 26, 2007. In the orders, the roll did not take place. Most likely, this is due to the actual order being filled on July 23, 2007, rather than July 20, 2007 (as in the Order Manager). Again, this results in an extra commission of $16.86, which goes against us. Even with the two errors the actual account out-performed the order manager and the backtested trades by 6.02%. This figure does not include T-bill interest, which further increases the real time results. With the full service commission rate and the two errors, the actual results again outperformed the order manager and the backtested trades- this time by 2.37%. (Again this does not include the T-bill rate.) The actual account outperformed the order manger and the backtest for two reasons: 1) the exceptional execution by Angus Jackson�s traders 2) realistic slippage and commission used in the backtest and applied on contract rolls as well. The purpose of a backtest is to create a perspective of the structure that can be duplicated in the real world. An over optimized back-test or a simulation that can not be followed in real world trading is worse than useless. It is dangerous. Below is the complete backtest ran from 1 Jan 1990 to 8 April 2008.

Proof of Concept

Below is the hypothetical backtest or what we like to refer to as the proof of concept. The test is just over 18 � years. The test was run with an initial investment of $65,000. All tests were run with the Mechanica Portfolio Engineering software combined with an Excel macro to test 1 lot trading metrics. Slippage and commission was $75 on each new trade and $37.50 on each roll.

Equity Curve

The account equity is on the left hand scale and the account drawdown is on the right hand scale. The duration of the draw downs can be viewed by the red shaded valleys on the green curve. The depth of the draw downs can be viewed in the lower red shaded plot. The performance of the strategy can be seen by the green shaded plot. The Commodity Index Strategy averaged 28.73% per year or $18,935.28 per year. The drawdowns are larger in the Commodity Index Strategy as compared to our Commodity Trading Strategy. However, as shown, the larger drawdowns in the index strategy occur after huge run-ups in returns. The Commodity Index Strategy uses larger stops that allow us to stay in the trades to take advantage of bull market moves.

Trailing 12 Month Returns

The 12 month return or decline from each day in the test. The majority of the time the chart is positive. This means your chances to make money the first year trading are very much in your favor

Annual Return in USD

The return or decline at the end of each year. There has never been a losing year. The average return for all years is $18,935.28.

A Brokers Nightmare

Overtrading is the number one reason why traders lose money. This chart is the number of trades per year. The Commodity Index Strategy trades an average of only 20 times per year.

Summary Table

The metrics that we feel have the most value going forward are the standard deviation of daily returns and the average trade. The above standard deviation of daily return means that the daily change in your account value will be within $1047.95 approximately two thirds of the time. The average trade is $947.98. This provides us with a good cushion for a rough period or higher costs from slippage or commission.

Below is the list of markets traded and the performance per market. The Commodity Index Strategy is offered exclusively through an annual subscription that's traded by our preferred brokers. We recommend, especially if you are a newer trader, you take advantage of our preferred broker, Angus Jackson Inc. They have been in business for over twenty years. Their execution services are superb and they provide a seamless implementation of our strategies. We recommend trading the strategy with between 50�80k USD, depending on your level of risk. For larger account sizes, trade 1 additional lot for each 60�90k USD. The Commodity Index Strategy is available through the low-cost annual subscription rate of only $997. For more information click: https://us.spindices.com/indices/commodities/dow-jones-commodity-index

[hunterghost278]

Excel Merge Spreadsheets With Common Column

How to merge two sheets by using VLOOKUP in Excel?

Supposing there are two tables in two sheets in a workbook, you want to merge table 2 to the table 1 by using VLOOKUP function as below screenshot shown. Here this article, introduces the two methods on solving this job.

Here is a VLOOKUP function can quickly update the main table based on the lookup table.

You can merge (combine) rows from one table into another simply by pasting the data in the first empty cells below the target table. The table will increase in size to include the new rows. If the rows in both tables match up, you can merge the columns of one table with another—by pasting them in the first empty cells to the right of the table. To do this, right click the column to the right of the two you want to merge and select Insert. This will insert a new, blank column. In the first blank cell at the top of this new blank column, type =CONCATENATE (C3,” “,D3). Download google drive file stream pc. Replace C3 and D3 with whatever cells contain your data.

In the cell which you want to fill the updated data, type below formula

=VLOOKUP($A2,table2!$A$1:$B$11,2,FALSE)

Then drag the auto fill handle down to the cells to apply this formula for fill data.

In the above formula, A2 is the lookup value which in the main table, and table!A1:B11 is the table range you want to look up value in, and 2 indicates the second column of the table which you want to return. Odin sigil.

If you do not want to use VLOOKUP formula, you also can choose below method to combine two tables and update the contents.

Merge two tables by Kutools for Excel’s handy tool-Tables Merge

Here with Kutools for Excel, you can apply the Tables Merge tool to quickly merge two tables in two sheets and update the contents.

with more than 300 handy functions, makes your jobs more easier.

After free installing Kutools for Excel, please do as below:

1. Click Kutools Plus > Tables Merge to enable the tool.

2. In the Tables Merge dialog, you need to do as below:

Step 1: choose the main table and lookup table ranges.

Can Excel Combine Two Columns

Step 2: click Next to go to the step 2 of the Tables Merge dialog, select the column you want to look up based on .

Step 3: in this step, if you want to update the contents in main table based on the values in look up table, check the columns you want to update. In this case, here no data to update.

Step 4: in this part, choose the column data you want to add to the main table.

Step 5: if you want to set some options for the merged result, you can do the setting in this step, or you can directly click Finish in step 4. Tor browser 2020 download.

3. Now the contents in two tables have been merged or updated in main table.

Other Operations (Articles)

How to automatically merge blank cells above/left in Excel? In this article, I am going to talking about merging blank cells above or left automatically as below screenshot shown. Actually, there is no built-in utility that can handle this task, but the macro codes can.

Combine / merge multiple sheets into one sheet in Google sheet How to combine or merge multiple sheets into one single sheet in Google sheet? Here, I will talk about an easy formula for you to solve this task.

Compare And Merge Excel Spreadsheets

Keep merged cells while inserting a new row in Excel sheet While inserting a new row to the merged cells, the newly inserted rows will be automatically merged with the merged cells as below screenshot shown. Do you know how to insert a row without merging in Excel?

Merge and combine rows without losing data in Excel Excel only keeps the data in the upper-left most cell, if you apply 'Merge & Center' command (Home tab > Merge & Center on the Alignment panel) to merge rows of data in Excel. Users have to use another method to merge multiple rows of data into one row without deleting data. This tutorial is going to present you the method of how to merge rows of data into one row.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range..

Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average.. Prevent Duplicate Cells; Compare Ranges..

Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select..

Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more..

Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails..

Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments..

Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic..

Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF..

Pivot Table Grouping by week number, day of week and more.. Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name..

Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.

Open and create multiple documents in new tabs of the same window, rather than in new windows.

Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Excel Merge Multiple Columns

or post as a guest, but your post won't be published automatically.

Loading comment.. The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.

hello, i wants to know how to compare duplicate datas from different excel sheet and copy the entire details of duplicate value

[solsarin]

how to create a formula in excel to calculate percentage increase

how to create a formula in excel to calculate percentage increase

Hello dear friends, thank you for choosing us. In this post on the solsarin site, we will talk about “ how to create a formula in excel to calculate percentage increase “. Stay with us. Thank you for your choice.

How to calculate percentage change or difference between two numbers in Excel?

This article is talking about calculating percentage change or difference between two numbers in Excel

Calculate percentage change/difference between two numbers with formula

As below screenshot shown, you need to calculate the percentage change between new number 94 and old number 80. The formula =(new_value-old_value)/old_value can help you quickly calculate the percentage change between two numbers. Please do as follows.

1. Select a blank cell for locating the calculated percentage change, then enter formula =(A3-A2)/A2 into the Formula Bar, and then press the Enter key. See screenshot:

2. Keep selecting the result cell, then click the Percent Style button in the Number group under Home tab to format the cell as percentage. See screenshot:

Now the percentage difference between two given numbers is calculated.

The Best Office Productivity ToolsKutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails…

Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range…

Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns… Prevent Duplicate Cells; Compare Ranges…

Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select…

Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more…

Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments…

Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic…

Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF…

More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.

Open and create multiple documents in new tabs of the same window, rather than in new windows.

Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Calculator

An electronic calculator is typically a portable electronic device used to perform calculations, ranging from basic arithmetic to complex mathematics.

The first solid-state electronic calculator was created in the early 1960s. Pocket-sized devices became available in the 1970s, especially after the Intel 4004, the first microprocessor, was developed by Intel for the Japanese calculator company Busicom. They later became used commonly within the petroleum industry (oil and gas).

Modern electronic calculators

Modern electronic calculators vary from cheap, give-away, credit-card-sized models to sturdy desktop models with built-in printers. They became popular in the mid-1970s as the incorporation of integrated circuits reduced their size and cost. By the end of that decade, prices had dropped to the point where a basic calculator was affordable to most and they became common in schools.

Computer operating systems as far back as early Unix have included interactive calculator programs such as dc and hoc, and calculator functions are included in almost all personal digital assistant (PDA) type devices, the exceptions being a few dedicated address book and dictionary devices.

In addition to general purpose calculators, there are those designed for specific markets. For example, there are scientific calculators which include trigonometric and statistical calculations. Some calculators even have the ability to do computer algebra. Graphing calculators can be used to graph functions defined on the real line, or higher-dimensional Euclidean space. As of 2016, basic calculators cost little, but scientific and graphing models tend to cost more.

In 1986, calculators still represented an estimated 41% of the world’s general-purpose hardware capacity to compute information. By 2007, this had diminished to less than 0.05%.[1]

resource: wikipedia

Calculating Percentage Increase in Excel

Percentage increases involve two numbers. The basic mathematical approach for calculating a percentage increase is to subtract the second number from the first number. Using the sum of this figure, divide this remaining figure by the original number.

To give you an example, the cost of a household bill costs $100 in September, but $125 in October. To calculate this difference, you could use the excel formula =SUM(NEW-OLD)/OLD or for this example, =SUM(125-100)/100 in Excel.

If your figures are in separate cells, you can replace numbers for cell references in your formula. For example, if September’s bill amount is in cell B4 and October’s bill amount is in cell B5, your alternative Excel formula would be =SUM(B5-B4)/B4.

The percentage

The percentage increase between September and October is 25%, with this figure shown as a decimal number (0.25) by default in Excel using the formula above.

If you want to display this figure as a percentage in Excel, you’ll need to replace the formatting for your cell. Select your cell, then click the Percentage Style button in the Home tab, under the Number category.

Calculating Percentage Decrease in Excel

To calculate the percentage decrease between two numbers, you’ll use an identical calculation to the percentage increase. You subtract the second number from the first, then divide it by the first number. The only difference is that the first number will be smaller than the second number.

Using cell references, if October’s bill amount of $125 is in cell B4 and November’s bill amount of $100 is in cell B5, your Excel formula for a percentage decrease would be =SUM(B5-B4)/B4.

Calculating a Percentage as a Proportion of a Number

In Excel, the formula to calculate this example would be =50/100. Using cell references, where $100 is in cell B3 and $50 is in cell B4, the excel formula required is =B4/B3.

Random Posts

how much alcohol in budweiser beer

what percent alcohol is smirnoff green apple

How to Calculate Percentages of a Number

Calculating the percentage of a number is something that you’ll encounter in day-to-day life. A good example would be an item for sale, where a discount of 20% is being applied to the original price of $200. A store employee would need to know what 20% of $200 was. They could then subtract this number from the original price to provide the discounted price.

simple mathematical calculation

This requires another simple mathematical calculation in Excel. Only the multiplication operator (*) and percentage sign (%) are used here. To calculate what 20% of the original $200 price is, you can use either =20%*200 or =0.2*200 to make the calculation in Excel.

To use cell references, where 20% is in cell B4 and the original price of $200 is in cell B5, you could use the formula =B4*B5 instead.

Using Excel for Complex Calculations

As this guide shows, Excel is great for simple calculations, but it handles more complex ones, too. Calculations using functions like the VLOOKUP function are made easy, thanks to the built-in function search tool.

If you’re new to Excel, take advantage of some Excel tips every user should know to improve your productivity further.

[firsttimeinterview]

Leading 50 Sql Interview Questions As Well As Responses

Auto increment enables the individual to create a unique number to be created whenever a brand-new record is put in the table. CAR INCREMENT is the keyword for Oracle, AUTO_INCREMENT in MySQL and IDENTITY key phrase can be made use of in SQL WEB SERVER for auto-incrementing. Mainly this keyword phrase is used to create the primary key for the table. Normalization sets up the existing tables and its fields within the database, resulting in minimal replication. It is utilized to streamline a table as long as feasible while keeping the special fields. If you have really little to claim on your own, the recruiter power believe you have null to state. pl sql interview inquiries I make myself feel magnificent in front the interview begins. With this question, the employer will certainly evaluate you on just how you prioritise your job listing. I expect operations with damien once again in the future strained. You mightiness require a compounding of different kinds of questions in order to fully cover the concern, and also this may diverge betwixt individuals. Candidates appear to interviews with a surface of thrilling you. A primary key is a unique sort of unique trick. A international key is utilized to keep the referential web link stability between 2 information tables. It protects against actions that can ruin links between a kid and a moms and dad table. A primary secret is utilized to define a column that uniquely determines each row. Null value and duplicate values are not permitted to be entered in the main crucial column. However, you may not be given this hint, so it is on you to remember that in such a scenario a subquery is exactly what you require. After you experience the basic SQL meeting inquiries, you are likely to be asked something extra certain. And also there's no better feeling worldwide than acing a concern you practiced. But if geekinterview do is method SQL interview inquiries while neglecting the basics, something is mosting likely to be missing out on. Request too many inquiries might jump the meeting as well as reach them, but request none will make you look unenthusiastic or not really prepared. When you are taking the test, you should prioritise making certain that all parts of it run. Leading 50 google analytics interview questions & answers. Nerve-coaching from the interview people that will certainly blast your troubled sensations to ensure that you can be laser-focused as well as surefooted as soon as you land in the hot seat. Terrific, attempting, and also im type of gallant i was capable to settle it under such tension. Those who pass the phone or picture meeting go on to the in-person meetings. Once again, it's crafty concern, and not simply in damages of operations it out. On the far side permitting myself to get some shouting meemies out, i really taken pleasure in the possibility to obtain a better feel for campus/atmosphere. Knowing the extremely certain answers to some really particular SQL meeting questions is excellent, however it's not going to help you if you're asked something unexpected. Don't get me wrong-- targeted prep work can absolutely help. Keep in https://is.gd/snW9y3 that this not a Not Null restraint as well as do not puzzle the default worth constraint with forbiding the Null entries. The default worth for the column is set just when the row is created for the first time and column worth is ignored on the Insert. Denormalization is a data source optimization strategy for enhancing a database infrastructure performance by including redundant data to one or more tables. Normalization is a database design strategy to organize tables to lower information redundancy and data reliance. SQL constraints are the set of regulations to restrict the insertion, deletion, or upgrading of information in the databases. They restrict the kind of data going in a table for keeping information precision and integrity. PRODUCE-- Used to develop the data source or its items like table, index, feature, views, sets off, etc. A special key is used to distinctly identify each document in a database. A CHECK restraint is utilized to limit the worths or kind of data that can be stored in a column. A Primary key is column whose worths distinctively determine every row in a table. The main role of a main key in a data table is to maintain the internal stability of a information table. Query/Statement-- They're frequently utilized reciprocally, yet there's a slight difference. Listed here are various SQL interview concerns as well as solutions that declares your understanding regarding SQL as well as provide new understandings as well as discovering the language. Experience these SQL interview inquiries to rejuvenate your understanding before any type of meeting.

As a result, your following task won't be about describing what SQL restrictions and tricks imply generally, although you need to be really accustomed to the principle. You will rather be given the chance to show your capacity to specify on a details type of an SQL restriction-- the foreign crucial constraint. Write a SQL query to find the 10th tallest peak (" Altitude") from a " Hill" table. Adjustment to the column with VOID worth and even the Insert procedure specifying the Void value for the column is permitted. Click the Set Primary Trick toolbar button to set the StudId column as the primary crucial column. A RIGHT OUTER SIGN UP WITH is one of the JOIN procedures that enables you to specify a SIGN UP WITH clause. It maintains the unrivaled rows from the Table2 table, joining them with a NULL in the shape of the Table1 table. And afterwards, as currently as we cut that possibility, everyone burst out laughing yet you can not be in on the technique." there are many " weak points" that you can become favorable situations to reveal an response that your recruiter will certainly value and see. - this environs covers the ironware, servers, running plan, internet internet browsers, various other software program system, etc. Duties that you were not able-bodied to be full. "i figure out which task is near considerable, and afterwards i try to do that project initially previously end up the being successful one. Permanently example, there are approach of audit package, arsenal package, etc. While it's much easier to ask common questions, you take the chance of not obtaining the discerning details you need to make the best hiring decision. Question optimization is a procedure in which database system compares various question techniques and pick the question with the least cost. Main key developed on more than one column is called composite main trick. REMOVE removes some or all rows from a table based on the problem. ABBREVIATE gets rid of ALL rows from a table by de-allocating the memory pages. The procedure can not be rolled back DECLINE command eliminates a table from the data source completely. The main distinction in between the two is that DBMS conserves your information as documents whereas RDMS saves your information in tabular type. Also, as the keyword Relational implies, RDMS permits various tables to have connections with one another using Primary Keys, Foreign Keys etc. This creates a dynamic chain of hierarchy in between tables which also uses practical constraint on the tables. Think that there go to the very least 10 records in the Mountain table. That's why leading firms are progressively relocating far from common concerns and also are instead supplying candidates a split at real-life inner circumstances. "At Airbnb, we provided possible hires access to the tools we make use of and also a vetted information collection, one where we knew its restrictions and troubles. It allowed them to concentrate on the shape of the data as well as framework answers to problems that were significant to us," notes Geggatt. A Check constraint look for a certain condition prior to inserting data right into a table. If the data passes all the Inspect restrictions then the data will certainly be inserted right into the table or else the data for insertion will be disposed of. The CHECK constraint guarantees that all values in a column satisfy particular conditions. https://tinyurl.com/c7k3vf9t restricts the insertion of null worths right into a column. If we are using a Not Void Constraint for a column after that we can not neglect the worth of this column during insertion of data right into the table. The default constraint enables you to establish a default value for the column.

[trendingjobs]

Leading 50 Sql Interview Questions And Also Solutions

Automobile increment enables the individual to create a serial number to be produced whenever a new document is placed in the table. AUTOMOBILE INCREMENT is the keyword for Oracle, AUTO_INCREMENT in MySQL and IDENTITY keyword phrase can be utilized in SQL WEB SERVER for auto-incrementing. Mainly this search phrase is used to produce the main trick for the table. Normalization organizes the existing tables and its areas within the data source, causing minimal duplication. It is utilized to simplify a table as high as possible while preserving the special areas. If you have actually little to state for yourself, the job interviewer power believe you have void to claim. pl sql meeting questions I make myself feel divine ahead the interview starts. With sql queries interview questions , the employer will certainly judge you on exactly how you prioritise your task checklist. I look forward to functions with damien once again in the future strained. You mightiness require a compounding of dissimilar types of questions in order to fully cover the concern, and this may split betwixt participants. https://bit.ly/3tmWIsh turn up to interviews with a surface of impressing you. A main secret is a unique sort of unique trick. A foreign secret is used to maintain the referential web link stability in between two information tables. It prevents activities that can damage web links in between a kid and also a moms and dad table. A main secret is utilized to specify a column that distinctively identifies each row. Null worth as well as replicate worths are not permitted to be entered in the main key column. However, you might not be provided this tip, so it gets on you to bear in mind that in such a circumstance a subquery is exactly what you require. After you go through the basic SQL meeting concerns, you are most likely to be asked something more details. As well as there's no far better feeling on the planet than acing a inquiry you exercised. Yet if all you do is method SQL meeting concerns while disregarding the essentials, something is going to be missing. Demand a lot of questions may leap the interview and also reach them, but request none will certainly make you look unenthusiastic or unprepared. When you are taking the test, you need to prioritise making certain that all parts of it run. Top 50 google analytics meeting inquiries & responses. Nerve-coaching from the meeting guys that will blast your restless sensations to make sure that you can be laser-focused as well as surefooted once you land in the spot. Terrific, attempting, and im sort of gallant i was qualified to resolve it under such stress. Those who pass the phone or photo interview proceed to the in-person meetings. Once again, it's smart question, and not just in damages of workings it out. Beyond permitting https://geekinterview.net to get some screaming meemies out, i truly appreciated the chance to obtain a far better feel for campus/atmosphere. Recognizing the very details solution to some very details SQL interview questions is excellent, but it's not going to aid you if you're asked something unforeseen. Don't get me wrong-- targeted prep work can certainly help. Keep in mind that this not a Not Null restriction and do not puzzle the default value restriction with disallowing the Void access. The default value for the column is established just when the row is produced for the first time and also column worth is neglected on the Insert. Denormalization is a data source optimization technique for boosting a data source facilities performance by including redundant information to one or more tables. Normalization is a database design strategy to arrange tables to decrease information redundancy and information dependence. SQL constraints are the set of rules to limit the insertion, removal, or updating of data in the data sources. They restrict the kind of information entering a table for keeping data precision and also honesty. PRODUCE-- Used to develop the database or its objects like table, index, feature, views, activates, and so on. A distinct key is made use of to uniquely determine each document in a database. A CHECK constraint is used to restrict the values or type of data that can be kept in a column. A Primary key is column whose values distinctively recognize every row in a table. The main function of a main type in a information table is to maintain the interior honesty of a information table. Query/Statement-- They're typically used mutually, yet there's a minor difference. Listed here are various SQL meeting inquiries as well as answers that reaffirms your understanding about SQL as well as offer brand-new insights as well as learning more about the language. Undergo these SQL meeting questions to freshen your expertise prior to any type of meeting. Consequently, your next task will not be about discussing what SQL restraints and keys indicate generally, although you have to be really accustomed to the idea. You will certainly instead be offered the chance to demonstrate your capability to clarify on a particular type of an SQL restraint-- the international essential restraint. Compose a SQL query to find the 10th tallest peak (" Altitude") from a "Mountain" table. Alteration to the column with VOID value or perhaps the Insert procedure defining the Null value for the column is enabled. Click on the Set Main Key toolbar button to establish the StudId column as the main essential column. A RIGHT OUTER SIGN UP WITH is among the JOIN procedures that permits you to define a SIGN UP WITH condition. It maintains the unequaled rows from the Table2 table, joining them with a NULL in the shape of the Table1 table.

And after that, as presently as we reduced that prospect, every person broken out laughing however you can not be in on the method." there are many "weaknesses" that you can turn into favorable scenarios to reveal an solution that your interviewer will certainly respect and also see. - this environments covers the ironware, servers, operating system, web internet browsers, various other software application system, etc. Tasks that you were not able-bodied to be total. "i find out which job is near considerable, and after that i try to do that job first previously end up the doing well one. For good example, there are technique of audit plan, arsenal bundle, and so on. While it's much easier to ask generic questions, you run the risk of not acquiring the careful details you need to make the most effective hiring determination. Query optimization is a process in which database system compares various query methods and select the query with the least cost. Primary vital created on more than one column is called composite primary trick. REMOVE removes some or all rows from a table based on the problem. TRUNCATE eliminates ALL rows from a table by de-allocating the memory pages. The procedure can not be curtailed DROP command removes a table from the database totally. The primary difference in between the two is that DBMS saves your info as files whereas RDMS saves your details in tabular form. Also, as the keyword Relational implies, RDMS allows various tables to have relationships with one another making use of Main Keys, Foreign Keys and so on. This develops a dynamic chain of power structure between tables which likewise uses helpful constraint on the tables. Assume that there are at the very least 10 documents in the Hill table. That's why leading firms are increasingly relocating far from generic concerns and also are rather providing prospects a crack at real-life internal circumstances. "At Airbnb, we provided potential hires access to the devices we make use of as well as a vetted data set, one where we understood its restrictions as well as issues. It allowed them to concentrate on the shape of the information as well as framework answers to problems that were meaningful to us," notes Geggatt. A Examine constraint checks for a certain condition prior to putting information into a table. If the data passes all the Examine restraints then the data will be inserted right into the table or else the data for insertion will certainly be thrown out. The CHECK restriction guarantees that all worths in a column please specific conditions. A Not Null restraint restricts the insertion of null worths into a column. If we are making use of a Not Void Restriction for a column then we can not neglect the value of this column throughout insertion of data into the table. The default constraint enables you to establish a default worth for the column.

[interviewexperttips]

Top 50 Sql Meeting Questions And Solutions

Vehicle increment enables the user to create a serial number to be created whenever a new document is put in the table. VEHICLE INCREMENT is https://is.gd/snW9y3 for Oracle, AUTO_INCREMENT in MySQL and also IDENTITY keyword can be made use of in SQL SERVER for auto-incrementing. Mainly this keyword is utilized to create the primary trick for the table. Normalization prepares the existing tables and also its fields within the database, resulting in minimum replication. It is used to streamline a table as much as feasible while keeping the special areas. If you have actually little to claim for yourself, the recruiter power believe you have null to claim. pl sql meeting concerns I make myself feel magnificent ahead the interview starts. With this concern, the employer will evaluate you on exactly how you prioritise your task checklist. I look forward to functions with damien once more in the future stressful. You mightiness require a compounding of dissimilar kinds of concerns in order to fully cover the problem, as well as this may diverge betwixt individuals. Candidates appear to interviews with a finish of exciting you. A primary key is a special type of special key. A international trick is used to preserve the referential web link stability between two data tables. It prevents activities that can ruin web links in between a kid and a parent table. A main secret is used to define a column that distinctly identifies each row. Null worth as well as duplicate values are not enabled to be entered in the primary key column. Nonetheless, you might not be offered this tip, so it gets on you to bear in mind that in such a situation a subquery is precisely what you require. After you experience the essential SQL interview inquiries, you are most likely to be asked something more particular. And also there's no much better sensation on the planet than acing a inquiry you exercised. Yet if all you do is practice SQL interview questions while overlooking the fundamentals, something is mosting likely to be missing out on. Request https://tinyurl.com/c7k3vf9t might jump the interview and get to them, but demand none will make you look withdrawn or unprepared. When you are taking the examination, you need to prioritise fashioning sure that all parts of it run. Top 50 google analytics meeting inquiries & solutions. Nerve-coaching from the interview men that will blast your uneasy sensations to make sure that you can be laser-focused and also surefooted once you land in the spot. Terrific, trying, and also im sort of gallant i was capable to solve it under such stress. Those that pass the phone or image interview proceed to the in-person meetings. Once again, it's crafty inquiry, and also not just in damages of operations it out. On the far side enabling myself to get some yelling meemies out, i truly delighted in the possibility to obtain a better feel for campus/atmosphere. Knowing the really certain answers to some really specific SQL interview questions is excellent, however it's not going to assist you if you're asked something unexpected. Don't get me wrong-- targeted prep work can certainly assist. Note that this not a Not Void restraint as well as do not perplex the default worth restriction with refusing the Null entrances. The default value for the column is set only when the row is produced for the very first time and also column worth is ignored on the Insert.

Denormalization is a database optimization method for enhancing a database facilities performance by adding repetitive information to one or more tables. Normalization is a database layout method to arrange tables to minimize information redundancy as well as information dependency. SQL restraints are the set of rules to limit the insertion, removal, or upgrading of data in the databases. They limit the kind of data entering a table for keeping information precision as well as integrity. DEVELOP-- Made use of to produce the data source or its things like table, index, function, sights, triggers, etc. A one-of-a-kind secret is utilized to distinctively identify each document in a database. A CHECK restraint is made use of to restrict the values or kind of data that can be kept in a column. A Primary key is column whose worths distinctively determine every row in a table. The primary role of a main key in a information table is to preserve the internal integrity of a data table. Query/Statement-- They're typically made use of interchangeably, however there's a small distinction. Listed here are numerous SQL interview questions and solutions that declares your knowledge regarding SQL as well as supply brand-new insights as well as finding out about the language. Undergo these SQL meeting concerns to revitalize your knowledge before any type of interview. As a result, your following job won't have to do with clarifying what SQL restraints and also tricks indicate in general, although you must be really knowledgeable about the idea. You will instead be offered the opportunity to show your capability to clarify on a particular kind of an SQL restraint-- the international vital restriction. Write a SQL question to find the 10th highest height ("Elevation") from a "Mountain" table. Alteration to the column with NULL worth or perhaps the Insert procedure specifying the Void worth for the column is allowed. Click on the Set Main Secret toolbar switch to set the StudId column as the main vital column. my explanation RIGHT OUTER JOIN is among the JOIN operations that permits you to define a SIGN UP WITH provision. It preserves the unequaled rows from the Table2 table, joining them with a NULL in the shape of the Table1 table. And afterwards, as presently as we reduced that possibility, everybody broken out laughing but you can't be in on the technique." there are numerous " weak points" that you can turn into positive situations to reveal an response that your interviewer will respect and see. - this environments covers the hardware, web servers, operating system, web internet browsers, various other software application system, etc. Duties that you were not able-bodied to be total. "i identify which https://bit.ly/3tmWIsh is near considerable, and afterwards i try to do that job first earlier finish the doing well one. For good example, there are approach of accounting plan, armoury plan, etc. While it's simpler to ask common concerns, you risk not acquiring the discerning information you need to make the best hiring decision. Question optimization is a process in which data source system compares different query strategies as well as select the inquiry with the least expense. Main key produced on greater than one column is called composite primary key. DELETE removes some or all rows from a table based upon the problem. TRIM eliminates ALL rows from a table by de-allocating the memory pages. The procedure can not be curtailed DECLINE command gets rid of a table from the database entirely. The main difference between both is that DBMS saves your info as files whereas RDMS conserves your information in tabular kind. Also, as the keyword Relational indicates, RDMS permits various tables to have relationships with one another using Main Keys, Foreign Keys and so on. This creates a vibrant chain of power structure between tables which likewise supplies valuable constraint on the tables. Presume that there go to least 10 documents in the Hill table. That's why leading companies are progressively relocating away from common questions and are rather supplying candidates a crack at real-life inner situations. "At Airbnb, we provided possible hires accessibility to the tools we utilize as well as a vetted data collection, one where we understood its restrictions as well as troubles. It permitted them to focus on the shape of the information and framework answers to issues that were purposeful to us," keeps in mind Geggatt. A Inspect restraint look for a certain condition before inserting data into a table. If the data passes all the Check constraints after that the data will certainly be placed right into the table or else the information for insertion will be thrown out. The CHECK restriction makes sure that all worths in a column satisfy certain problems. A Not Void restraint limits the insertion of null worths into a column. If we are making use of a Not Null Restraint for a column then we can not disregard the worth of this column throughout insertion of data into the table. The default restriction allows you to set a default worth for the column.

[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