Published on March 1, 2011
SEO for large sites and Excel
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
Coding…. cont s.eVar45="Op:UK:Promo:Flights-morocco-marrakech-“ pageTracker._setCustomVar(1, "visit type", "prospect", 2) DCSext.SEOvariable varmpcl = 'h-london---l1-------http://www.gumtree.com/london';
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
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,
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 ...
... your business needs to drive qualified visitors to your site and increase ... Free SEO & Online Marketing Solutions for Businesses Large or ...
Download. Sign up for our newsletter to download your copy of SeoTools for Excel. ... Just plain old Excel&Windows. firstname.lastname@example.org
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 ...
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 ...
Such doorway pages drain away the link popularity of a site and route it to the SEO and its other clients, ...
... be CLEVER think LARGE act FAST ... through SEO much like our very own site. ... the best SEO Company. GUARANTEED RESULT Excel ...
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 ...
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.