ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Step 0:Subscribe to seonotebook.com for more spreadsheets and tips, did that? let's get to it...
2
Step 1:Watch the video tutorial and make a copy of this sheet
3
Step 2: Install this Google Sheets addon: https://workspace.google.com/marketplace/app/importfromweb_web_scraping_in_google_she/278587576794
4
Step 3:Navigate to the Extensions menu in sheets and activate free trial or purchase the addon
5
Step 4:Get your hands on a Top Pages report from Ahrefs, enter the whole site or just a subfolder from your competitor's website
6
Step 5:Select all and paste that data into the tab "Ahrefs data"
7
Step 6:Locate your competitor's sitemap. You can Google "competitor + xml sitemap" or you can check their Robots.txt file
8
Step 7:Paste the sitemap into cell A1 of the tab "Enter Sitemap(s)"
9
Step 8:Check the tab "URLs" to see what populates
10
Step 9:if you see URLs populate, you're good to go. If you see more sitemaps, copy them and repaste them into the "Enter Sitemap(s)" tab. The sheet will automatically combined the URLs from each sitemap ๐Ÿ’ช
11
Step 10Play around with the different filters, check the "New only" tab to see newly published content, check "Curated" to see the most important metrics, check "ALL" to see all the ahrefs data or check "refresh only" to get a list of pages they're actively refreshing.
12
13
Tips:If you mess something up and sorting doesn't work, make another copy and start fresh.
14
Sort by "lastmod" to see your competitors most recent changes.
15
Play around with the different filters. Get a quick sense of their top performing pages and how often they update them.
16
Take the data out of this spreadheet for further data manipulation.
17
Have fun with it and tell me how you're using it steve@seonotebook.com
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100