What You Can Expect

Background Story
Tools
Step 1. Download All Relevant Articles You Need For The Internal Linking SEO Campaign Using Google Search Console

Step 2. Use Google Analytics To Find The Pages That Have High Conversions And Engagement

Step 3. Based On Data On The Spreadsheets, Compare Article Stats To Analyze Which Ones To Categorize Into Which Tier

Step 4. Export All Blog Article Data (Titles And URLs) Into A Spreadsheet

Step 5. Use An SEO Tool To View SEO Stats Then Sort Pages According To Their Ratings

Step 6. Use VLOOKUP To Retrieve Data From 2 Worksheets

Step 7. Fill In The Proper Items In The Formula To Activate VLOOKUP

Step 8. Sort The Data To Finalize Which Articles To Consider As Tier 1 Pages

Step 9. Put Tier 1 Articles Up In The Best Positions

Step 10. Link To Tier 2 Articles From Tier 1 Articles, Tier 3 From Tier 2, And So On

Time Frame

Conclusion

How Our Internal Linking SEO Campaign Began

Here’s A Little Background Story For How Our Internal Linking SEO Campaign Began:

Some Time Ago, Dave Came Across This Article About Creating Site Architecture Using The Silo Structure.

In Essence, You Divide All The Pages In Your Website Into Silos, Or Topic Themes, Starting From The Broader Category, And Further Dividing Them Into Related, More Specific Subtopics.

The Goal Is To Create A More Seamless Crawling Experience For Search Engine Robots, As Well As To Distribute The Link Juice From Your Main Content Pages More Equally.

Now, Dave Thought Of A Better Way He Could Use This Strategy.

What If He Divided All Of His Pages Not Into Themes, Per Se, But Hierarchies?

That Is, All Articles Will Be Categorized By Order Of Priority From Tiers 1 Through 3.

Top Money Pages Would Have To Be Tier 1.

These Are The Articles That Rank For The Exact Keywords You Are Targeting, Bring In The Kind Of Traffic You Want, And Convert The Most—Or At Least, That’s What You Want These Pages To Do.

Tier 2 Pages Are Those That Are Doing Pretty Well.

They Attract Good Traffic And Rank For Certain Keywords, But Not Necessarily The Exact Ones Or At The Level That You Want.

Still, They Bring A Pretty Good Amount Of Views To Your Site.

Tier 3 Pages Are The Lowest-Performing Pages.

They Don’t Get That Much Traffic Or Engagement And Don’t Rank Well For Any Keywords That Matter To You But Are Written Well And Could Serve The Occasional Niche Reader.

All Of The Highest-Priority Pages Should Show Up Everywhere That Counts—The Headers, Footers, Categories, Etc.

Then, These Tier 1 Pages Would Link To Tier 2 Pages, And Tier 2 Pages To All The Other Tier 3 Pages, Thus Sharing Visibility And Link Juice To All Pages On The Website.

Intrigued, Dave Set Out To Test It On Another Site He Was Working On: AvocadoPesto.

True Enough, After Two Months, His Idea Got Validated When Traffic For AvocadoPesto’s Articles Shot Up By 20%!

Dave Didn’t Do Any Other Optimizations During This Period As Well, So He Was Pretty Sure The Changes Can Be Attributed To The Only Updates He Made: Internal Linking.

Convinced By Now Of The Strategy’s Effectivity, The Idea To Replicate The Same Process For NinjaInfluence Was Born.

And So, This Article Will Discuss With You How We Executed Our Internal Linking SEO Campaign And Answer The Following Questions Step By Step.

1. How Did We Collect All The Articles We Need?
2. What Tools Did We Use?
3. What Characteristics Did We Look For?
4. How Did We Decide Which Articles To Put Into Tiers?
5. How Did We Decide Which Articles Are In Which Tiers?
6. How Do We Go About Linking To Them In The Actual Articles?
7. How Long Before We Checked Back To See If We’ve Gotten Any Results From This Campaign?

Started Campaign: August 31, 2017.

As Of October 23, 2017, We Already Got A 40% Increase In Traffic And Around December 15, 2017, It Got To Almost A 50% Traffic Increase.

Tools Used

Aside From Google’s Free Tools, Below Are The Other Tools We Used To Complete Our Internal Linking SEO Campaign:

• Ahrefs – For Identifying Link Authority.
• Moz – For Identifying Domain And Page Authority (DA And PA, Respectively).
• NinjaInfluence – For Exporting Data (Because It Has Automatic Duplicate Removal Feature)

Step 1. Download All The Articles Using Google Search Console

To Do This, Go To:

Google Search Console > Search Traffic > Search Analytics

Once There:

Tweak To Display 500 Rows > Download Results Into A Google Spreadsheet > Clean Up Results Manually (Remove Duplicates, Non-Blog Pages, Etc.)

Step 2. Use Google Analytics To Find The Pages That Have High Conversions And Engagement

Go To:

Google Analytics > Behavior > All Pages

Show 500 Rows, Select View Full Report, Then Copy The Data Into A Spreadsheet

 

Step 3. Compare Article Stats To Analyze Which Ones To Categorize Into Which Tier

The Stats You Should Look Out For Are:

• Impressions
• CTR
• Bounce Rate
• Goal Conversion

These Give You The Idea Of How Much Value Your Article Generates.

For Example, An Article With High Impressions But Low CTR Means That Google Thinks It’s A Good Article To Prioritize In Its SERPs, But The Low CTR Says That Users Who See It On The SERPs Don’t Find It Relevant Enough To Click It.

Step 4. Export All Blog Article Data (Titles And URLs) Into A Spreadsheet

Since Our Site Runs On WordPress, Our SEO Manager Tarek Used A WordPress Plugin Called Export All URLs To Transfer All Our Data From The NinjaInfluence Blog (Titles And URLs) Into A Spreadsheet.

Step 5. Use An SEO Tool To View SEO Stats Then Sort Pages According To Their Ratings

We Used The Ahrefs Batch Analysis Tool To See Our SEO Data.

We Just Grabbed The URLs From Our Sheet And Pasted Them Into This Input Section.

We Had To Do This In Batches Because We Can Only Apply Batch Analysis For Up To 200 URLs.

We Downloaded The Results Into A Spreadsheet Then Copied Only The UR Ratings.

We Then Pasted This New Data Back To Our Original Spreadsheet, Making Sure The UR Ratings Corresponded To The Correct Articles.

Next, We Sorted Them According To Their UR Ratings.

Note: UR Rating Is Not The Only Factor For Us Considering Which Articles Should Be Tier 1. But It Is One Step To Help Us Identify Which Articles Are Important.

This Also Helps Us Track The Articles’ Ranking Improvements Over Time.

Step 6. Use VLOOKUP To Retrieve Data From 2 Worksheets

VLOOKUP Is A Function That Allows You To Extract A Column Of Data From Separate Tables By Using An Identifier That It Has In Common With Another Table.

These Tables Can Either Exist In The Same Worksheet, Separate Worksheets, Or Even Different Spreadsheets Altogether.

When You’re Working With A Ton Of Data, VLOOKUP Is A Faster, More Accurate Way Than A Manual Process. (VLOOKUP Only Works For Data Arranged In Columns Vertically.

For Data Arranged Horizontally Across Rows, You Can Use HLOOKUP.)

In Our Sample SpreadSheet, We Have Three Worksheets:

• The All Posts Worksheet, Which Contains All Our Blog Post Data Imported From WordPress, And Which Should Include All The Final Data That We Need For The Campaign
• The AnalyticsData Worksheet, Which Contains All Our Google Analytics Data
• The GSCData Worksheet, Which Contains All Our Google Search Console Data

In The All Posts Sheet, We Already Have The Page URLs And The UR Ratings We Copy Pasted From Ahrefs.

We Still Need Other Data From The Other Two Sheets, AnalyticsData And GSC Data.

VLOOKUP Will Help Us Pull All That Data Together, And Fast.

But First, Let’s Explain What Our Formula Means.

The VLOOKUP Formula (In Google Sheets) Looks Like This.

VLOOKUP(Search_key, Range, Index, [Is_sorted])

“=Vlookup” Simply Defines The Name Of The Formula You Are Using.

What We Need To Define Is Each Of The Items Inside The Parentheses, Because Those Are The Items You’ll Need To Change As You Go Along.

1. Search Key / Lookup Value

For VLOOKUP To Work, You’re Going To Need To Designate A Unique Identifier That Your Separate Worksheets Have In Common.

It Serves As A Way To Make Sure The Data You’re Retrieving From One Sheet Matches With The Data In The Other Sheet.

2. Range / Table Range

This Is The Range Of Cells Where The Search Will Run Through. The First Column In This Range Should Contain The Identifier.

3. Index / Column Number

This Is The Number Of The Column That Contains The Data Or Value That You’re Looking For.

4. [Is_sorted] / True / False

Dictates Whether An Approximate (True) Or Exact (False) Match Will Be Accepted.

Step 7. Fill In The Proper Items In The Formula To Activate VLOOKUP

In This Section, We’re Going To Explain How We Filled In The Elements In Our VLOOKUP Formula.

If You Check In Our Sample Document, You’ll See How We Entered The Pieces We Needed, And Our Formula Ended Up Looking Like This.

So To Recap.

We Have Three Worksheets:

• All Posts
• AnalyticsData
• GSC Data

What We Want To Accomplish:

• Extract The Sessions Data From Our AnalyticsData Sheet
• Paste This Data Under The Traffic Column In Our All Posts Sheet
• Make Sure The Data Is For The Same Article (Hence Using The URL As The Identifier).

To Do That, We First Need To Define A Name For The Range / Table Range.

Naming The Range / Table Range

So, We Will Highlight The Range That Contains The Sessions Column In The AnalyticsData Sheet.

Next, Click Data Then Named Ranges.

Define A Name For Your Range.

It Should Be Something Specific And Easy To Recall (In Our Case, We Named It Analytics). Then, Click Done.

Next, We Went Back To The All Posts Worksheet And Located The Section (Row Or Column) Where We Need The Data To Reappear.

We Need Our Data From The AnalyticsData Sheet To Reappear Under The Traffic Column In Our All Posts Sheet.

The All Posts Sheet Is Where We’ll Be Consolidating All The Data We Need So We Will Paste The VLOOKUP Formula Here.

Defining The Search Key / Lookup Value / Identifier

For The Search Key/ Lookup Value, We Used The Page URLs As Our Identifier, Since All Our Worksheets Have That Value In Common.

In Our Main Sheet (All Posts) Where We Pasted The VLOOKUP Formula, You’ll See That The Cell Number B2 Contains Our Identifier, The Page URL Info.

So, We’ll Enter That Value (B2) Into Our VLOOKUP Formula.

Again, This Is A Way To Make Sure The Sessions Data We’re Getting From The Other Sheet, AnalyticsData, Is From The Same Article. (Defined By The Same URL.)

Defining The Index / Column Number

The Column Number Of The Sessions Data That We Need (Situated From Within The AnalyticsData Sheet) Is 2.

So We Put That Value Into Our VLOOKUP Formula As Well.

Defining [Is_sorted] / True / False

For This Process, Just Set The Value To False.

Again, To Recap, This Is What The VLOOKUP Formula Should Look Like Based On What I’ve Explained Above.

And There You Have It. We Just Basically Copied This VLOOKUP Formula And Adjusted The Input Items As The Number Of Data We Needed To Process Increased.

You Can Download Our Sample Sheet Complete With VLOOKUP Formula Through Here.

Step 8. Sort The Data To Finalize Which Articles To Consider As Tier 1 Pages

Sort The Final Data According To Goal Value And Goal Count To See Which Ones Are Money Articles (The Pages That Make The Most Money For The Business).

Money Articles Are The Pages Where Visitors Convert And Also The Pages That Contain Content With Topics That Are Highly Relevant For Our Target Readers Or That We Want To Rank.

These Are Automatically Tagged As Tier 1 Pages.

Next, We Also Looked At How Much Traffic A Page Has Gotten As Well. If The Topic Is Relevant To Our Business, We Can Add It To Our Tier 1. If Not As Much, It Goes To Tier 2.

Tier 3 Pages Are The Pages That Rank Low On Both Ratings.

Step 9. Put Tier 1 Articles Up In The Best Positions

Once We’ve Identified Which Articles We Wanted To Be More Visible, We Put Them Up In The Best Places Where Our Target Readers Will More Likely See Them.

These Sections Are Typically Our Home Page And Page Footer.

There’s Also Now A Higher Chance To Convert Using These New Articles Because We’ve Ensured That They Are The Type Of Articles That Resonated With Our Most Profitable Customers (MPCs).

Step 10. Link To Tier 2 Articles From Tier 1 Articles, Tier 3 From Tier 2, And So On

Rule Of Thumb: 1 Link Per 100 Words.

The Main Goal Is To Surface The Money Articles At The Top And Make Room For Other Tier 2 And Tier 3 Articles.

If The Actual Article Content Does Not Have An Opportunity For Linking Straight From The Body Using Relevant Keywords, You Can Also Add A “Related Story” Section At The Bottom And Link Out To A Tier 2 Or 3 Article.

Tier 1 In The Header And Footer, Then Under That Tier 2s, Then Under Those, Tier 3s.

When Linking Out, The Key Is To Make Sure That They Are Topically Related.

If It’s Not Contextual, Don’t Insert The Link In An Article Just Because A Spreadsheet Dictates That You Need To.

What If An Article Doesn’t Fit The Context Of The Main Post You Will Link From?

There Are Ways To Get Around This.

If An Article You Need To Link To Doesn’t Fit Anywhere In The Main Post You’re Linking From, Add The Articles You Need To By Linking To Them Via Sections Like:

• You Might Also Like This Article
• Related Articles
• Other Featured Articles
• Related Stories
• Related Reading
• Etc.

You Can Also Create A Different Category In Your Blog Menu And Add About 5 Or 6 More Tier 1 Articles.

You Can Even Try Other Ways Such As Adding More Categories, Creating Sub-Categories In Your Home Page, Etc.

This Is What We Did, And So Far, It Has Worked Well For Us.

For Example, Here’s A Screenshot Of The Header Where We Inserted Some Of Our Tier 1 Articles.

How Many Pages Can You Use For Your Tiers?

Technically, It Depends On How Many Pages You Have That Are Doing Great, And How Much Space You Have To Feature Those Pages On.

Allocating This Would Be Like Doing Prime Factorization.

It’s All A Matter Of How Many Links You Can Display Per Page Of Content.

In Our Case, The Number Of Links We Could Display On Our Homepage Limited How Many Tier 1 Articles We Could Have.

Since We Could Only Display Five On Our Menu And Seven In The Footer, That Gave Us Space For 12 Tier 1 Articles.

Second, If A Site Has Thousands Of Articles, For Example, Then There Should Be At Least A Hundred Tier 2 Articles.

That Will Give You Enough Real Estate To Link To Your Tier 3s.

NinjaInfluence Had Around 300 Total Articles At The Time We Did Our Internal Linking SEO Campaign, So We Divided Those Accordingly.

For Every Hundred Articles, Our SEO Manager Tarek Selected 10 Tier 2 Articles. So For Around Our 300 Total, We Ended Up With 30 Tier 2 Articles.

Also, Since We Did Not Have More Than 10 Linkable Tier 1 Articles, We Had To Choose Only 30 Tier 2 Articles.

Try To Link To All Of Your Tier 3 Articles Where They Make Sense But Don’t Sweat It If You Can’t Find Any Appropriate Sections To Put Them In.

Timeframe

To Analyze Your Internal Linking SEO Campaign Results, You Need To Wait At Least One Month Before Recording Progress.

However, You Can Observe Immediate Changes In Google Analytics.

Full Changes Should Be Visible Within Three Months.

In Our Case, We Saw Some Immediate DA/PA Increase.

But As We’ve Already Shown In Our Intro, It Was The Almost 50% Traffic Increase That Ultimately Validated Out Strategy.

Conclusion

And There You Go! A Complete Step-By-Step Walkthrough Of The Internal Linking SEO Strategy We Used To Boost Our Organic Traffic By Almost 50%.

You Can Also Download The Sample Sheet Complete With VLOOKUP Formula That We Used Here.

Try It Out, And Let Us Know How This Strategy Affected Your Traffic!