ABC
1
2
Link reclamation spreadsheet
3
by Charlie Williams (@pagesauce) & SEOptimise (@seoptimise)
4
5
Getting started
6
This sheet is designed to help you grab all the links you have reclaimed by fixing broken links reported by backlink tools such as Open Site Explorer, Majestic SEO and Ahrefs, and broken links reported by Google Webmaster Tools. Make a copy of this sheet by hitting File > Make a Copy. Follow the instructions below to put together your list. For full details please see the blog post at . Let us know any improvements or questions!
7
8
Analysing backlink data:
9
1. Export backlink data from Open Site Explorer, Ahrefs or Majestic SEO, remove uncessesary columns.
10
2. Paste into 404s found in backlink data sheet, columns A - F, keeping formula =IFERROR(VLOOKUP(F:F,I:J,2,FALSE),"") in column G.
11
3. Copy target URL column, copy into another spreadsheet and save as .csv file
12
4. Crawl with Screaming Frog.
13
5. Export 404 list from Screaming Frog, open and remove all columns except URL and server code.
14
6. Copy into coulmns I & J in 404s found in backlink data sheet.
15
7. Export 301 list from Screaming Frog, open and remove all columns except URL and server code.
16
8. Filter to only show 302s, then copy and paste into the bottom of 404 list already in place on 404s found in backlink data sheet.
17
9. Filter main backlink data table to only show those with 404 or 302 in server code (column G).
18
10. Rinse and repeat for second backlink data source.
19
11. Copy relevant data from both backlink sources into Master list of external links (de-duped) sheet. De-dupe column A & B at same time, to capture all links on each page.
20
12. Fix broken links, gain lost link authority.
21
22
Analysing GWT data:
23
1. Go to Google Webmaster Tools.
24
2. Navigate to Health > Crawl Errors > Not found.
25
3. Download top 1,000 pages with errors as .csv file. Open, copy URL column (A) and save into new .csv or .txt file.
26
4. Crawl this list of URLs in Screaming Frog.
27
5. Any that now return 200 or 301 server codes remove from the GWT list, and mark as fixed within GWT.
28
6. Go through each broken link in GWT, using the Linked from tab to find external sites. Copy these external sites onto a new sheet, also adding a column with the target URL.
29
7. Take complete list of URLs and target URLs they link to, and copy into 404s found in GWT sheet.
30
8. Copy URL column (A) and paste into new spreadsheet, save this new sheet as .csv or .txt file.
31
9. Crawl this new list of external URLs in Screaming Frog. Filter Response Codes to 301/302 and export.
32
10. Open export and hide all columns except Address and Status Code, copy and paste into GWT VLOOKUPtab.
33
11. Go to 404s found in GWT tab, filter Status Code column for any that 301/302. Remove these.
34
12. Go back to 301/302 export, and unhide destination URI. Take these URI and copy into URL column in 404s found in GWT tab.
35
13. In Screaming Frog and Filter Response Codes to 4XX error, and export.
36
14. Open export and hide all columns except Address and Status Code, copy and paste into GWT VLOOKUPtab.
37
15. Go to 404s found in GWT tab, filter Status Code column for any that 404 etc. Remove these.
38
16. Copy the URL column in 404s found in GWT tab once again (now we've removed those URLs that no longer exist/have moved), copy to new spreadsheet and save as .csv or .txt file.
39
17. Go to Screaming Frog, and in Configuration > Custom, create a filter looking for your target sites domain name, and choose 'Does not contain'. Once ready start your crawl.
40
18. Once crawl is complete select Custom tab, and filter by your your custom Filter. Export the results (if any).
41
19. Open export, hide all colums except Address and Status. Copy 2 data into columns A & B in GWT VLOOKUP tab.
42
20. In 404s found in GWT tab, filter Status Code that have anything in Status Code column (column C). Delete these rows.
43
21. Copy relevant data into Master list tab, de-dupe with you backlink data.
44
22. Fix broken links, gain lost link authority.
45
23. Pass list of fixed links to client/boss, and take a well earned break.
46