Analiza link-urilor competiției: Link Intersect în Excel

-- Scris de

Unul din pașii importanți în dezvoltarea unei strategii SEO este cu siguranță analiza competiției. Backlink-urile competitorilor pot oferi informații despre metodele folosite de aceștia în strategiile lor de link building, și pot fi chiar oportunități pentru completarea profilului nostru de link-uri.

Aceste oportunități sunt însă dificil de găsit manual, mai ales în cazul nișelor competitive, unde website-urile tind să aibă un număr semnificativ de backlink-uri. Deși există câteva tool-uri potrivite pentru identificarea lor, precum Link Intersect de la SEOmoz, am preferat să construiesc un tool cu ajutorul Excel-ului care să ofere o flexibilitate mult mai mare în manipularea datelor.

Mai departe am scris un ghid pentru a explica cum poți realiza o astfel de analiză în Excel, în care am inclus și un template de la care să poți porni cât mai ușor.

Ce este de fapt Link Intersect?

După cum poate știi, tool-ul Link Intersect de la SEOmoz (cunoscut și ca Competitive Link Research Tool sau Competitive Link Finder) îți permite să găsești site-uri care au link-uri către competitorii tăi, dar nu și către tine. În felul ăsta poți găsi multe oportunități, mai ales de la domenii care au link-uri către mai mulți competitori, unde șansele de succes sunt mai mari ca aceștia să adauge link și către tine.

Competitive Link Finder   SEOmoz Labs

Versiunea pe care am construit-o în Excel te ajută să identifici aceste domenii-oportunitate, să vezi către ce competitori există link-uri, și exact ce URL-uri conțin link-urile respective, pe lângă alți metrici care te vor ajuta să decizi pe cine să contactezi.

De ce să folosesc versiunea în Excel?

În continuare am detaliat avantajele utilizării versiunii în Excel față de tool-ul de la SEOmoz.

În primul rând, tool-ul SEOmoz se află în secțiunea Labs (unde poți găsi tool-uri în variantă beta) de câțiva ani, iar ultima actualizare a fost în 2010. Tool-ul nu este foarte rapid din păcate și nu permite sortarea sau adăugarea/ștergerea metricilor care te interesează cel mai mult. Deși are aceste mici defecte, Link Intersect este un tool foarte bun, pe care l-am folosit înainte de a crea versiunea în Excel.

În al doilea rând, dacă ai o agenție de SEO relativ nouă (sau ești chiar freelancer) și bugetul nu îți lasă loc să plătești un developer care să-ți facă propriile tale tool-uri, Excel este probabil unul din cele mai folositoare programe pe care le ai la dispoziție. Este foarte bun pentru analiza și vizualizarea datelor și are multe pluginuri care te pot ajuta în activitatea de SEO. Mai mult de atât, aproape fiecare platformă cunoscută din domeniul de online marketing are posibilitatea de a exporta în Excel, având astfel o locație centralizată pentru toate datele de care ai nevoie.

În al treilea rând, versiunea în Excel îți va permite să:

  • folosești backlink data de la orice provider, nu numai OSE, cât timp conțin URL-uri sursă (paginile care conțin link-urile) și URL-uri destinație (paginile către care trimit link-urile); desigur, orice metrici te pot ajuta, însă sunt opționali din punct de vedere al funcționalității tool-ului
  • sortezi datele așa cum ai tu nevoie, fie după numărul de competitori spre care există link-uri, fie după unul din metricii incluși în datele importate
  • analizezi câți competitori vrei (cât timp ai destule resurse)

Am înțeles, zi-mi cum funcționează

Dacă ai ajuns aici, presupun că ești interesat de acest tool, așa că voi explica pas cu pas cum îl poți crea. Explicațiile vor include și un tabel pivot. Dacă nu ai mai lucrat cu așa ceva am pus la dispoziție și o serie de screenshot-uri pentru a arăta cum se implementează pas cu pas.

Dacă vrei să sari direct la rezultatul final, ultimul capitol conține un template și instrucțiuni de folosire.

De ce ai nevoie înainte să te apuci de treabă

Pentru crearea tool-ului nu trebuie să ai la dispoziție decât următoarele:

  • S-ar putea să ți se pară ciudat, dar vei avea nevoie de Excel pentru a realiza tool-ul :) Personal am folosit varianta 2010 pe 32 biți, dar ar trebui să meargă și cu alte versiuni.
  • Backlink data. Poți folosi OSE de la SEOmoz, Majestic, etc., în principiu orice provider care îndeplinește cerințele care le-am menționat mai sus și poate face export în Excel. Pentru acest exemplu am folosit exporturi din OSE. O alternativă ar fi să folosești un API pentru a importa date, rămâne la alegerea ta.
  • 6 – 8 ore din timpul tău. Glumesc :)

Pasul 1: Exportăm backlink data (sari peste dacă știi cum / ai deja datele necesare)

Acest pas este relativ simplu. Dacă ai folosit Excel în trecut pentru a analiza backlink-uri, probabil știi deja cum să exporți datele. Eu am folosit un cont PRO de la SEOmoz, așa că în continuare mă voi folosi de OSE pentru acest pas.

Din moment ce abia am lansat site-ul companiei, nu îl voi folosi ca exemplu. De asemenea, prefer să nu aleg un site aleatoriu, așa că voi alege seomoz.org ca domeniu primar.

Voi alege 3 competitori (am menționat faptul ca poți alege câți vrei, dar din moment ce aceștia sunt site-uri destul de mari, trei sunt de-ajuns pentru acest exemplu). Aceștia sunt: distilled.net, seerinteractive.com și seogadget.co.uk.

Exportul datelor pentru fiecare site (inclusiv cel primar) nu este complicat. Intră în OSE, introdu numele domeniului și click pe Search. În continuare va trebui să filtrezi rezultatele astfel încât sa includă doar link-uri externe fie către pagini din domeniul root, fie către pagini din subdomeniu (pentru site-urile care sunt pe un subdomeniu care este separat de domeniul root, cum ar fi un blog pe blogspot.com).

Open Site Explorer

NOTĂ: Filtrarea link-urilor către domeniul root va rezulta de obicei în mai multe date, dar vor fi necesare două formule în plus la pasul 3.

Opțional, poți filtra astfel încât să incluzi doar link-uri dofollow. Nu uita sa dai click pe butonul de Filter după ce ai terminat.

În continuare, va fi nevoie să descarci aceste link-uri. OSE pune la dispoziție doua opțiuni. Fie folosești butonul „Download CSV” și vei primi până la 10k link-uri, fie folosești modulul de Advanced Reports unde ai un credit zilnic limitat și vei primi până la 100k link-uri.

În acest caz, va trebui să alegi opțiunile „External linking page” și „Any page on this root domain” (sau după caz, subdomain). Restul opțiunilor pot fi lăsate implicit (default), dar poți alege să filtrezi link-uri care au Domain Authority/Page Authority peste o anumită valoare, pentru a reduce numărul total de rezultate.

Open Site Explorer2

Poți rula la rând mai multe rapoarte, fără să pierzi timp așteptând ca unul să fie gata până să îl pornești pe următorul. Vei primi un mail când sunt gata de descărcat.

Repetă acest pas pentru fiecare competitor. La final ar trebui să avem patru fișiere CSV (unul pentru datele noastre, trei pentru datele competitorilor).

Pasul 2: Importăm datele în Excel (sari peste dacă ai deja datele în Excel)

Urmează să preluăm toate datele exportate şi să le ducem în Excel. Pentru a face asta, avem două opțiuni:

  • fie deschidem fișierele CSV direct, copiem coloanele de care avem nevoie (adică URL, Target URL, și orice metrici de care suntem interesați) și le dăm Paste într-un worksheet nou
  • fie folosim wizard-ul din Excel pentru Text Import pentru a importa datele într-un worksheet gol fără a deschide CSV-urile

Ambele opțiuni sunt destul de simple, însă cred că prima este cea mai ușoară (nici nu are nevoie de screenshot-uri). Problema este că prima opțiune nu funcționează dacă Windows-ul este setat cu formatul pentru România.

Această problemă apare pentru că fișierele CSV conțin Comma-Separated Values (valori separate prin virgulă), virgula fiind delimitatorul default în formatul English (United States). Pentru România, delimitatorul default este punct și virgulă (en. semicolon), ceea ce face ca Excel să nu poată citi corect datele din CSV.

Pentru a rezolva această problemă, va fi nevoie sa deschizi Regional and Language Options (sau varianta în limba română) în Windows din Control Panel și să setezi formatul pentru English (United States), sau să păstrezi România și, la Advanced Settings, schimbă punct în loc de virgulă pentru „decimal symbol”, și virgulă în loc de punct și virgulă pentru „list delimiter”.

Poți vedea tot procesul aici (Solution #3):

Putem folosi în schimb a doua variantă, dar problema cu aceasta este că, din cauza modului în care Excel importă datele, unele pot fi afișate greșit, ceea ce ar duce la câțiva pași în plus pentru a curăța datele și a scoate toate erorile. Din această cauză m-am decis să nu includ tutorial pentru această opțiune (dar o poți folosi oricum dacă vrei să păstrezi delimitatorul default).

Indiferent de ce variantă alegi, după ce ai inclus datele pentru primul domeniu, copiază datele pentru următoarele domenii sub cele existente, fără să mai incluzi primul rând cu numele coloanelor (header row). În felul acesta o să obții o listă continuă cu datede la toate domeniile și numai primul rând va conține numele coloanelor.

NOTĂ: Dacă ai un număr mare (peste 50k) de backlink-uri de analizat, introdu doar un număr limitat (10-20k) pentru început, urmând ca, după adăugarea coloanelor cu formule, să adaugi și restul (tot în grupe de câte 10-20k). Acest lucru este necesar în funcție de versiunea de Excel utilizată și performanțele sistemului, pentru a evita apariția unor erori.

Bun, acum ar trebui să avem toate datele necesare în Excel. Deși este opțional, recomand ca lista să fie transformată într-un tabel, deoarece e mai ușor de lucrat astfel. Pentru a face acest lucru, selectează toată lista (click pe una din celule care conține date, cum ar fi A1, și apasă CTRL-A), apoi transformă într-un tabel (CTRL-L).

Menționez că fără tabel, formulele vor trebui editate pentru a include referințe la celulele exacte (de ex. $A2 în loc de [@URL]).

excel4

Pasul 3: Aplicăm formulele necesare

Avem acum toate datele în Excel și va fi necesar să aplicăm câteva formule.

Primele două formule vor lua datele din coloanele URL (cel sursă) și Target URL și vor returna doar subdomeniile din care fac parte. Poți găsi aceste formule într-un ghid realizat de Distilled, pe care eu l-am găsit foarte util: „Excel for SEO”.

Va trebui să adăugăm două coloane noi pentru rezultatele respective. Vom numi prima coloană „Source Subdomain”, iar a doua va fi „Target Subdomain”. Din moment ce datele fac parte dintr-un tabel, va fi nevoie doar să introducem numele coloanelor în primele două coloane alăturate , iar Excel le va atașa la tabel automat.

excel8

Prima formulă este:

Source Subdomain

=MID([@URL],FIND("://",[@URL])+3,IFERROR(FIND("/",[@URL],9),LEN([@URL])+1)-(FIND("://",[@URL])+3))

(unde [URL] este coloana care conține URL-ul sursă, poate fi diferit numele dacă nu folosești OSE)

A doua formulă este:

Target Subdomain

=MID([@[Target URL]],FIND("://",[@[Target URL]])+3,IFERROR(FIND("/",[@[Target URL]],9),LEN([@[Target URL]])+1)-(FIND("://",[@[Target URL]])+3))

(practic aceeași formulă, cu excepția coloanei Target URL).

Aceste formule preiau și returnează ce se află între „://” și primul „/” din URL-uri (acest lucru permite și prelucrarea link-urilor dinspre/către locații „https”). Porțiunea cu IFERROR este pentru cazul în care nu exista un slash la sfârșitul URL-ului atunci când URL-ul reprezintă pagina principală a site-ului (OSE întotdeauna adaugă automat acest slash, însă nu și Majestic SEO).

Va fi nevoie de introducerea formulei doar în prima celulă din coloană, iar Excel va popula automat restul coloanei (dacă folosim tabel).

NOTĂ: Dacă ai selectat opțiunea „pages to subdomain” în loc de „to root domain” la primul pas, nu va fi necesar să folosești următoarele două formule, deoarece vei avea un singur subdomeniu pentru fiecare site (de ex. www.seomoz.org pentru SEOmoz, seogadget.co.uk pentru SEO Gadget, etc.). Dacă te afli în această situație, sari direct la formula Unique Domains.

Probabil că te întrebi de ce am preluat până acum doar subdomeniul din coloana Target URL, și nu domeniul root. Asta înseamnă că seomoz.org și www.seomoz.org vor fi considerate site-uri diferite, lucru care ne poate pune probleme mai târziu (vei vedea domenii care au link-uri către 4 sau chiar mai mulți competitori, chiar dacă ai doar 3 competitori în date).

excel9

Pentru a rezolva această problemă, trebuie să extragem domeniul root din subdomeniu. Din păcate, acest lucru va fi puțin complicat deoarece trebuie să diferențiem între TLD-uri (Top-Level Domain) și SLD-uri (Second-Level Domain), pentru că unul din competitorii noștri este pe un SLD (este vorba de SEO Gadget), și nu vrem să returnăm domeniul „co.uk” în loc de „seogadget.co.uk” (deci nu putem folosi metoda de a „extrage tot ce există după ultimul punct ca TLD”).

În primul rând, va fi nevoie să listăm toate TLD-urile și SLD-urile din coloana Target URL către un loc separat de tabel. Personal am ales coloana Z pentru acest lucru. Lista noastră va fi următoarea:

.com
.net
.org
.co.uk

excel10

Adaugă întodeauna SLD-urile sub TLD-uri (ca principiu), astfel încât să fie detectate ultimele. Poți gândi această listă ca un set de reguli, formula va verifica fiecare regulă și va returna ultimul rezultat găsit. De exemplu, dacă unul din competitori este un blog pe „blogspot.com” (care în realitate nu e un SLD, dar îl considerăm ca unul pentru analiza noastră, deoarece nu suntem interesați de „blogspot.com” ca drept competitor), va trebui să-l plasăm sub TLD-ul „.com” astfel încât sa fie atribuit corect.

Cu lista definită, următoarea formulă va extrage TLD/SLD-ul (în continuare le voi numi doar TLD) din coloana Target Subdomain. Folosim formula în prima coloană alăturată, pe care o vom denumi-o „TLD”. Formula este:

TLD

=LOOKUP(2^15,SEARCH($Z$1:$Z$4,[@[Target Subdomain]]),$Z$1:$Z$4)

Valoarea 2^15 din interiorul lookup-ului spune formulei să caute întotdeauna ultima apariție a TLD-ului în Target Subdomain. Dacă ești curios în ce caz ar fi folositor, imaginează-ți domeniul „test.comparison.org”. TLD-ul pe care îl vrei returnat este desigur „.org”. Însă, dacă nu folosim 2^15, Excel va întâlni mai întâi „.comparison” și se va opri, deoarece a găsit valoarea „.com”, care este greșită.

Range-ul $Z$1:$Z$4 face referință la celulele care conțin TLD-urile.

excel11

Acum că avem TLD-ul, vom extrage domeniul root. Pentru a face asta, luăm practic Target Subdomain, ii scoatem TLD-ul, luăm tot ce găsim după ultimul punct, apoi aplicăm TLD-ul înapoi. Exemplu: dacă avem „some.thing.example.com”, vom elimina mai întâi TLD-ul ca să ajungem la „some.thing.example”, și vom extrage ce este după ultimul punct, ceea ce ne dă „example”, după care vom aplica în final TLD-ul pentru domeniul root final „example.com”.

Tot procesul este realizat de o singură formulă, pe care o vom introduce în coloana alăturată pe care o denumim „Target Root Domain”:

Target Root Domain

=IFERROR(RIGHT([@[Target Subdomain]],LEN([@[Target Subdomain]])-FIND("|",SUBSTITUTE(LEFT([@[Target Subdomain]],LEN([@[Target Subdomain]])-LEN([@TLD])),".","|",LEN(LEFT([@[Target Subdomain]],LEN([@[Target Subdomain]])-LEN([@TLD])))-LEN(SUBSTITUTE(LEFT([@[Target Subdomain]],LEN([@[Target Subdomain]])-LEN([@TLD])),".",""))))),[@[Target Subdomain]])

E o formulă puțin cam lungă, însă am vrut să includ tot într-o singură formulă ca să evit crearea mai multor coloane. Porțiunea de la început cu IFERROR este pentru cazul în care subdomeniul este de fapt chiar domeniul root, caz în care îl va returna pe acesta. Restul formulei realizează procesul descris mai sus.

excel12

Acum avem în sfârșit domeniile root!

A cincea (sau a treia, în funcție dacă ai folosit ultimele două) formulă are scopul de a verifica dacă un domeniu sursă are măcar un link către un domeniu destinație, ca să putem vizualiza mai târziu câți competitori primesc link-uri de la acea sursă.

În tabelul pivot pe care îl vom construi în pasul următor, această formulă va avea rolul unui fel de „distinct count” a domeniilor root destinație pentru fiecare URL sursă. Din păcate, nu putem realiza acest pas decât dacă folosim o formulă, excepție fiind cazul în care folosești Excel 2013.

Menționez acest lucru deoarece, pentru a crea acest „distinct count” ne putem folosi de trei formule. Este posibil ca două dintre ele să fie ceva mai rapide, dar pe de altă parte există risculsa primim avertizări de la Excel (cel puțin în cazul versiunii 2010 pe 32 biți, pe care o folosesc) în cazul unui număr mare de link-uri (peste 30k).
Din această cauză am decis să folosesc o formulă care, deși poate să nu fie la fel de rapidă, pare să funcționeze mai bine cu un număr mare de link-uri și ne oferă același rezultat.

Vom crea o nouă coloană denumită „Unique Domains”, și vom folosi următoarea formulă:

Unique Domains (varianta pentru domenii rădăcină)

=IF(COUNTIFS(INDIRECT(ADDRESS(ROW(Table2[#Headers])+1,COLUMN([Source Subdomain]))&":"&ADDRESS(ROW([@[Source Subdomain]]),COLUMN([Source Subdomain]))), [@[Source Subdomain]], INDIRECT(ADDRESS(ROW(Table2[#Headers])+1,COLUMN([Target Root Domain]))&":"&ADDRESS(ROW([@[Target Root Domain]]),COLUMN([Target Root Domain]))), [@[Target Root Domain]])=1,1,0)

Funcția COUNTIFS numără de câte ori un Source Subdomain are asociat același Target Root Domain. IF-ul identifică prima asociere de acest fel și returnează valoarea 1 pentru ea și valoarea 0 pentru următoarele asocieri (e un fel de a spune “Da, această sursă are măcar un link către această destinație”).

Formula pare să fie destul de lungă, însă ea este echivalentă cu următoarea formulă:

=IF(COUNTIFS($O$2:$O2,$O2,$R$2:$R2,$R2)=1,1,0)

În acest caz, coloana O este Source Subdomain, iar coloana R este Target Root Domain. Numărul 2 este numărul rândului în care introduci prima dată formula (rândul imediat dedesubt celui cu numele coloanei).

Am ales însă să folosesc varianta lungă pentru a putea fi aplicată fără să fie nevoie identificarea coloanelor/rândurilor necesare. De aceea sunt folosite funcțiile INDIRECT și ADDRESS, care au scopul identificării automate a celulelor necesare pentru formulă, indiferent de locația tabelului sau de numărul de coloane al acestuia.

Ține minte, dacă n-ai folosit formulele de TLD/Root Domain, atunci vei folosi [@[Target Subdomain] în loc de domeniul root

Din păcate, funcția COUNTIFS nu există în Excel 2003, și va fi nevoie să folosești o altă formulă care o poți găsi aici (versiunea SUMPRODUCT).

Ultima formulă este relativ simplă și ne va ajuta să determinăm dacă link-ul este către noi (domeniul primar) sau nu, pentru a putea filtra acest lucru mai târziu. Vom crea o coloană alăturată denumită „Link To Us”, și vom introduce următoarea formulă (unde vei înlocui „seomoz.org” cu domeniul tău).

Link To Us (varianta pentru domenii rădăcină)

=IF([@[Target Root Domain]]="seomoz.org";1;0)

Formula conține un „if conditional”, care returnează 1 pentru domeniul nostru, 0 pentru competitori.

excel14

Ține minte, dacă n-ai folosit formulele de TLD/Root Domain, atunci vei folosi [@[Target Subdomain] în loc de domeniul root (caz în care, în acest exemplu, vei avea [@[Target Subdomain]=”www.seomoz.org”).

Pasul 4: Construim Tabelul Pivot

OK, acum că am terminat de configurat tabelul cu backlink data, ultimii doi pași vor fi relativi simpli.

Vom crea un nou sheet (sau vom redenumi unul din cele default) denumit „Pivot Table”. Aici este locul unde vom vizualiza datele și unde le vom analiza.

excel16

creăm atunci tabelul pivot. Procesul este următorul:

Click pe tabul Insert -> click butonul Pivot Table -> introdu numele tabelului care conține backlink data (de obicei „Table1”, cu excepția cazului în care l-ai redenumit sau ai creat mai multe tabele) -> apasă OK.

excel17

Vei avea acum un tabel pivot gol cu un sidebar denumit „Field List”. Îl vom configura astfel:

  1. Trage câmpurile Source Subdomain, Target Root Domain (sau Target Subdomain dacă nu ai folosit cele două formule) și URL în chenarul Row Labels (în această ordine)
  2. Trage câmpurile Unique Domains, Link To Us și oricare alte metrici ai nevoie, cum ar fi Domain Authority, în chenarul Values (ordinea nu contează). Toate câmpurile ar trebui să fie adăugate automat ca sumă („Sum of…”). Pentru metrici vom avea nevoie de medie, astfel încât trebuie să repetăm procesul următor pentru fiecare metric: click pe „Sum of Domain Authority (sau orice altă metrică ai)” -> click Value Field Settings -> alege Average în loc Sum, sub „Summarize value field by” -> apasă OK.
    excel18

În final, configurația câmpurilor ar trebui să fie următoarea (va diferi puțin în funcție de ce provider pentru backlink data folosești și de ce care metrici ești interesat):

excel19

În continuare, va fi nevoie să restrângem rândurile sub câmpurile Source Subdomain ca să avem o vizibilitate mai bună a datelor. Pentru a face acest lucru, click pe unul din subdomeniile sursă din tabelul pivot -> asigură-te ca ești în tabul Options din meniul pentru Pivot Table -> click Collapse Entire Field.

excel20

Poți repeta procesul și pentru Target Root Domain.

Pasul 5: Sortăm datele și-am terminat

Acum că avem tot ce trebuie în tabelul pivot, ne rămâne doar să sortăm datele. În primul rând, din moment ce încercăm să găsim link-uri doar către competitori, vom filtra subdomeniile sursă care au link deja către noi.

Pentru a face asta, click pe dropdown-ul de la Row Labels -> Value Filters -> Equals… -> în fereastra care se deschide selectează „Sum of Link To Us” și introdu ”0” în câmpul pentru valori -> apasă OK.

excel21
excel22

În principiu, vrei să vezi care sunt site-urile care au link-uri către cât mai mulți din competitorii tăi, deoarece, așa cum am menționat la începutul ghidului, acestea sunt probabil site-urila care șansele de a pune link-uri și spre tine cresc.

Pentru a sorta, click din nou pe dropdown-ul de la Row Labels -> More Sort Options -> alege Descending (Z to A) by -> alege Sum of Unique Domains -> apasă OK.

excel23
excel24

În sfârșit, avem un tabel pivot cu domenii care au link-uri către competitorii noștri, dar nu și către noi, sortat după numărul de competitori către care au link-uri.

Poți desigur sorta și după Domain Authority (sau orice alt metric pe care l-ai inclus). Încă nu am găsit o metodă de a sorta tabelul după mai multe coloane (adică să sortez după Sum of Unique Domains mai întâi, iar apoi după Domain Authority).

Varianta finală arată astfel:

excel26

NOTĂ: Înainte de salvarea fișierului, pentru a reduce durata deschiderii și mărimea acestuia, putem să înlocuim toate formulele cu valorile lor pentru a evita recalcularea acestora. Aceasta se face selectând toate datele tabelului (CTRL-A) -> după care copiem (CTRL-C) -> și apoi inserăm doar valorile (Paste Values).

Cam asta este tot. Felicitări dacă ai ajuns până aici și sper că acest ghid te-a ajutat și că acum ai un tool nou cu care să te joci.

Pe viitor

Sunt câteva modificări care ar putea fi adăugate la acest tool:

  • opțiunea de a sorta după Unique Domains mai întâi, apoi după una sau mai mulți metrici
  • opțiunea de a filtra după valoarea unei metrici mai mică/mare decât x (de ex. Domain Authority mai mare ca 30); poți realiza parțial acest lucru trăgând acel metric în chenarul Report Filter și apoi alegând toate valorile de care ai nevoie (proces foarte plictisitor)
  • opțiunea de a reface tabelul pivot pentru a vedea oportunități pentru Co-Citations; de exemplu: în loc să ai Source Subdomain ca rânduri principale, să ai în schimb câmpul URL, astfel încât să vezi exact care pagini (față de care site-uri) au link-uri către mai mulți competitori

Dacă ai idee cum pot fi implementate acestea, scrie un comentariu mai jos.

Template și instrucțiuni

Cum am promis, voi include un template care conține deja toate formulele și tabelul pivot, și la care trebuie să mai adaugi doar backlink data.

Am decis să fac două fișiere separate:

  • vertify-link-intersect-sample-SUBDOMAIN.xlsx – acesta poate fi folosit când există backlink data de la câte un singur subdomeniu pentru fiecare site (atât în cazul tău cât și al competitorilor), iar backlink-urile sunt către pagini din subdomeniile respective (în acest caz, domeniu.com și www.domeniu.com sunt considerate site-uri/subdomenii diferite, și va trebui să alegi pentru care din ele vei lua datele)
  • vertify-link-intersect-sample-ROOTDOMAIN.xlsx – acesta este destinat cazurilor în care pentru cel puțin un website folosești backlink data către pagini din domeniul rădăcină; în acest caz vor exista două coloane în plus pentru extragerea domeniului rădăcină și va trebui configurată lista de TLD-uri

Template-ul conține 3 coloane pentru date, din care două sunt pentru URL-uri și una pentru un metric care dorești să îl folosești la analiză. Dacă vrei, poți adăuga coloane în plus pentru a avea mai mulți metrici disponibile (de ex. PA, DA, ACRank, PR, etc.) sau filtre (de ex. dofollow/nofollow). De asemenea, tabelul conține un rând cu “sample data” pentru a auto-introduce formulele în rândurile următoare. Rândul respectiv nu trebuie șters decât după ce s-au introdus datele.

Modul de utilizare este:

  1. Exportă datele de la providerul preferat (de obicei în format CSV).
  2. Deschide CSV-urile (vezi Pasul 2 din ghid dacă ai probleme cu vizualizarea lor) și șterge coloanele de care nu ești interesat. Trebuie să rămână coloanele cu URL-urile sursă și destinație, și eventual coloane cu metrici/filtre (template-ul conține o coloană pentru metrici, care poate fi ștearsă sau pot fi adăugate altele; ordinea coloanelor trebuie să fie aceeași cu cea din template).
  3. Deschide fișierul de template, intră pe sheet-ul Configuration și introduce numele subdomeniului sau a domeniului rădăcină, și eventual lista de TLD-uri pentru site-urile participante, dacă este cazul (mai multe detalii despre cum trebuie configurată lista respectivă se găsesc în ghid la pasul 3).
  4. Din fișierele CSV copie toate datele în afară de “header” (primul rând) în fișierul de template, începând cu rândul imediat următor celui care conține “sample data“; tabelul ar trebui să includă automat datele noi și să calculeze rezultatul formulelor automat. La finalul introducerii datelor se poate șterge rândul cu “sample data“. Cum am menționat și la pasul 2, dacă ai un număr mare (peste 50k) de backlink-uri de analizat, introdu-le în template în grupuri de câte 10-20k, pentru a evita o durată mare de calcul sau chiar apariția erorilor.
  5. După ce formulele termină de calculat, intră în sheet-ul Pivot Table și dă click-dreapta -> Refresh pe tabelul pivot. Ar trebui acum să ai toate subdomeniile sursă, sortate după numărul de subdomenii/domenii rădăcină către care au link-uri (dacă rândurile sunt deja extinse, poți urmări pasul 4 pentru a vedea cum pot fi restrânse)
  6. Înainte să salvezi, nu uita ca poți înlocui formulele din tabel cu valorile acestora pentru a reduce mărimea acestuia și durata de deschidere/salvare a fișierului (vezi pasul 5).

excel25
Dacă ai probleme cu fișierele sau nu ai înțeles ceva, lasă un comentariu mai jos.

3ukapi

QBTPVVVKYNGD

Autor:

Mihai este specialist SEO, fondator al firmei Vertify. Are experiență de peste 6 ani în domeniul serviciilor SEO si este specializat pe audituri si analize tehnice.

Un comentariu la “Analiza link-urilor competiției: Link Intersect în Excel”

  1. Alexandra Tanasa

    I have 99 problems, but competitive link analysis ain’t one :)

    Răspunde

Lasă un răspuns