| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | ||||||||||||||||
2 | Example | Website Article Inventory | |||||||||||||||
3 | ||||||||||||||||
4 | ||||||||||||||||
5 | ||||||||||||||||
6 | Name | Website | ||||||||||||||
7 | Example | https://www.example.com/ | ||||||||||||||
8 | ||||||||||||||||
9 | GA Automation Inputs | |||||||||||||||
10 | View ID | GA VIEW ID GOES HERE | ||||||||||||||
11 | Start Date | 7/1/2024 | ||||||||||||||
12 | End Date | 6/30/2025 | ||||||||||||||
13 | URL path | /blog | ||||||||||||||
14 | Revenue/Goal | goalCompletionsAll | ||||||||||||||
15 | ||||||||||||||||
16 | ||||||||||||||||
17 | Next Steps Summary | |||||||||||||||
18 | KEEP | 0 | ||||||||||||||
19 | OPTIMIZE | 5 | ||||||||||||||
20 | REVIEW | 0 | ||||||||||||||
21 | REDIRECT | 0 | ||||||||||||||
22 | REMOVE | 167 | ||||||||||||||
23 | AMPLIFY | 0 | ||||||||||||||
24 | TOTAL | 172 | ||||||||||||||
25 | ||||||||||||||||
26 | Instructions | |||||||||||||||
27 | - Share this sheet with a Google Account that has access to your client Google Analytics - Open the sheet with that GA account - Add the website above. This is important as it strips the URLs from your Screaming Frog download into "Slugs" in the FINAL tab so that they can be merged with GA data. If your slugs in the FINAL tab do not match GA, take a look at this again. - Add View ID above - Add URL path above - Go to the top >> Add-Ons >> Google Analytics >> Run Reports (if you don't have this add on, you can add it here: https://workspace.google.com/marketplace/app/google_analytics/477988381226) - Go to Buzzsumo, type in client url in Content section. Download CSV and upload content to the BUZZSUMO tab - Go to Ahrefs, type in client url, go to Top Content, Export CSV. Then upload content to the AHREFS tab. - Go to SEMrush, type in the client url, add a filter for the subdirectory or page type you are analyzing. Export all keywords for those pages. Paste in the SEMRUSH Tab. These will then get aggregated in the IMPORT tab and sent to the FINAL tab for your analysis. - Go to Screamingfrog, crawl the client site, Export as CSV. Upload the relevant columns to the SCREAMING FROG tab. You only really need the URLs here and status/meta if you want it. - Now the FINAL tab should be populated with data from each upload. - Drag down the formula in Column C so that each row has an "Action" assigned to it. - Paste all the data in the FINAL tab as text by using the Automate drop down above. You will have to give the script access the first time you run it. By pasting as text you will be able to filter the FINAL tab as you see fit! | |||||||||||||||
28 | ||||||||||||||||
29 | Notes | |||||||||||||||
30 | - If there are too many URLs in each action category you can use the hidden Variables tab and the Automate drop down menu to create a priority score to help you prioritize the urls in each category. If you use this functionality, unhide column V from the FINAL tab. - If you have too many keywords, you can limit the export to KW with a certain monthly search volume. - If GA is not set up to report on slugs (i.e. /blog/article-title) then you will need to customize the FINAL tab - You MUST run this with a Google account that has access to the client Google Analytics Account - Since Actions are determined by ranges of your own data, it is imperative that you only run this for one type of page (i.e. a blog subdirectory, all .pdf pages etc). | |||||||||||||||
31 | ||||||||||||||||
32 | ||||||||||||||||
33 | Template courtesy of Mary Albright @themerrymary If you enjoyed this template, consider supporting my template-making at https://buymeacoffee.com/themerrymary | |||||||||||||||