SEO for large sites and Excel

50 %
50 %
Information about SEO for large sites and Excel

Published on March 1, 2011

Author: cheehowan


SEO for large sites and Excel CheeHo Wan @cheehowan

Table of contents Why do this? What challenges are there between small and large websites? Where does Excel come in? How do we do data analysis? What data classifications are important? Coding How to structure data in Excel Outputs Excel tips

Why do this? It looks hard and I have a data analyst Inform strategy - Take a data driven approach is important for organisational buy-in and informing your SEO strategy. Forecasting – breaking data into bite size pieces

Informing strategy- Head vs tail analysis Head centric strategy Tail centric strategy Number of keywords Understanding which verticals should take head or tail strategies

Why do this? It looks hard and I have a data analyst Data latency – SEO has large enough delays without data visibility being an issue Breaking problems into tangible chunks enables you to act faster Look awesome in front of your work collegues! Spot opportunities others can’t see

What challenges are there between large and small websites? Analysis Too much data and keywords! Implementation Require large scale changes to see traffic growth Need to break data into workable chunks Tasks need automation Changes to site architecture Content mashups Large scale content writing / UGC Modified search - automated Domain authority building to win long tail Time and resource constraint become an increasing factor in large website SEO

Where does EXCEL come in? Everywhere! Keyword analysis Head vs tail Trend analysis – customer search patterns Back link analysis Classifying websites, using filtering to spot opportunities Traffic to rankings to links correlation Internal link analysis Important to turn daily and weekly task into automated process

How do we do analysis – love the drill down! Total SEO traffic Brand SEO Head Type of page Non- brand SEO Product Tail traffic Start wide, then narrow your analysis (don’t get sucked into detailed analysis too quick) The only way to do this kind of analysis is to classify your data into smaller chunks Site Depth Date

What data classifications are important for SEO? Page type Page duration Brand Product % of unique content per page Length of content per page Date – week, monthly, yearly Site depth Actionable insights rather than just data for data sake!

Example - MAYDAY Data analysis Symptoms Actions Drop in traffic on ‘transient pages’ What inbound link strategies can we apply to stop drop off Page type analysis - listing vs item pages Pages with inbound links do not lose rank Can we create more permanent pages? Pages with unique content do not lose traffic Can we add more “mashup” content to pages to make them more unique Analysis of trade vs private ads, page length, market data What internal linking structures can we apply to the website Pages with poor internal linking suffer

How do I code this into my site? Add some JAVASCRIPT to every entry page which classifies pages into different types varfuntionality_is_enabled=1;function engine_match(){if(funtionality_is_enabled!=1){return 0}var engines=new Array("google","","","","bing");for(var i=0;i<engines.length;i++){if(document.referrer.length>0&&document.referrer.indexOf(engines[i])!=-1){return 1}}return 0}; Store the information in a custom variables available in Omniture, Webtrends, Mediaplex, Google analytics

Coding…. cont s.eVar45="Op:UK:Promo:Flights-morocco-marrakech-“ pageTracker._setCustomVar(1, "visit type", "prospect", 2) DCSext.SEOvariable varmpcl = 'h-london---l1-------';

How to structure data in Excel Excel becomes powerful when you can cross- correlate multiple sets of data The key is to get a single view of your data (merge them all on one view To do this you will need a combination of VLOOKUP’s, SUMIFS, PIVOTS Analytics Traffic Clear understanding of cause and effect Single data set – cross correlate data Rankings Historic link data Date

Key tip Keep all classifying dimensions as columns As opposed to Allows you to merge data across multiple data sets and pivot data in any dimension

Tip 2: Understand SUMIFS Multiple lookup values to merge ranking with traffic data SUMIFs must be used when merging data as you have two or more lookup values VLOOKUP only allow you to merge one set of data

Pivots – what you end up with

Typical data outputs

Understand YoY trends to predict future behaviour and see opportunities What went up and why? Can we repeat this trend elsewhere?

Head vs Tail Traffic & Conversions Does your head deliver against conversion?

Informing strategy- Head vs tail analysis Head centric strategy Tail centric strategy Understanding which verticals should take head or tail strategies

Tail opportunities What are the tail keywords you should focus on – does this correlate to traffic?

Example output What is the impact on your activities

Link back analysis – Majestic data

Excel tips

Common excel functions URL extraction Left, mid, right, search Errors (#Value,#NA) ISNA (), ISERROR() and IF function Classification of data Sumifs(), VLOOKUP, At some point when data sets are too large or formulas are too complex, you will need to use MACROS or ACCESS!

Excel ninja shortcuts Paste Values - ALT ‘E’ ‘S’ ‘V’ Paste Formats - ALT ‘E’ ‘S’ ‘T’ Freeze panes = ALT WF

Missing cells in PIVOTS Control G Click Special, select blanks =, UP, CTRL, enter,


Add a comment

Related pages

SeoTools for Excel

Create SEO reports and dashboards ... This can lead to placing content on some sites that may look good at ... SEOTools for Excel has become a fundamental ...
Read more

An SEO Company Solving Internet Marketing Problems -

... your business needs to drive qualified visitors to your site and increase ... Free SEO & Online Marketing Solutions for Businesses Large or ...
Read more

Download SeoTools for Excel | Niels Bosma

Download. Sign up for our newsletter to download your copy of SeoTools for Excel. ... Just plain old Excel&Windows.
Read more

How to do Awesome SEO Keyword Research for a Large ...

How to do Awesome SEO Keyword Research for a Large, Templated Site. SEO 09 Nov 12 ... Using the concatenate function in Excel you should be able to do this ...
Read more

Excel Statistics for SEO and Data Analysis - Moz

Excel Statistics for SEO and Data ... Opens Site Explorer is great for getting more data about our ... Thanks for great input in using excel for SEO ...
Read more

Do you need an SEO? - Search Console Help

Such doorway pages drain away the link popularity of a site and route it to the SEO and its other clients, ...
Read more


... be CLEVER think LARGE act FAST ... through SEO much like our very own site. ... the best SEO Company. GUARANTEED RESULT Excel ...
Read more

A Step-by-Step Guide for On-Page SEO Management [Free SEO ...

A Step-by-Step Guide for On-Page SEO ... your site and exporting the results into an Excel ... organize your SEO: You get a larger overview of ...
Read more

Excel Visibility – Become Visible On The Web

Welcome To Excel Visibility Want to ... lasts with our “be CLEVER think LARGE act FAST get ... our clients website through SEO much like our very own site.
Read more