What Fantasy Football Taught Me About Building Nuanced SEO Reports

The document explores the parallels between fantasy football and building nuanced SEO reports, highlighting the importance of incorporating various data points like crawl stats and traffic metrics. It emphasizes how utilizing statistical analysis can enhance SEO reporting and contextualize issues better. The author shares insights on tools, templates, and manual reporting processes to improve SEO reporting effectiveness.

Transcript

1. What Fantasy Football Taught Me About Building Nuanced SEO Reports Owain Lloyd-Williams SON OF JACK LTD @Owain_LW
2. #brightonSEO | owainlloydwilliams.com | @Owain_LW What Fantasy Football Taught Me About Building Nuanced SEO Reports
3. A LITTLE ABOUT ME #brightonSEO | owainlloydwilliams.com | @Owain_LW
4. A LITTLE ABOUT ME #brightonSEO | owainlloydwilliams.com | @Owain_LW
5. CHINA SEO NICHE #brightonSEO | owainlloydwilliams.com | @Owain_LW
6. CHINA SEO NICHE https://builtvisible.com/baidu-seo-guide/ https://moz.com/learn/seo/content- marketing-china-side #brightonSEO | owainlloydwilliams.com | @Owain_LW
7. China SEO Niche https://adage.com/article/viewpoint/advertisers-china-s- search-market/305704 https://www.owainlloydwilliams.com/exploring-baidu- analytics-seo-tools/ #brightonSEO | owainlloydwilliams.com | @Owain_LW
8. BACK TO IT THEN #brightonSEO | owainlloydwilliams.com | @Owain_LW
9. IN LOCKDOWN I WAS BORED #brightonSEO | owainlloydwilliams.com | @Owain_LW
10. SO I STARTED DOING THIS…. #brightonSEO | owainlloydwilliams.com | @Owain_LW
11. AND ENDED UP WITH THIS…. #brightonSEO | owainlloydwilliams.com | @Owain_LW
12. MORE BOREDOM LED TO THIS #brightonSEO | owainlloydwilliams.com | @Owain_LW
13. AND THIS… #brightonSEO | owainlloydwilliams.com | @Owain_LW
14. AS WELL AS THIS #brightonSEO | owainlloydwilliams.com | @Owain_LW
15. AS WELL AS THIS #brightonSEO | owainlloydwilliams.com | @Owain_LW
16. SCREAMING INTO THE ABYSS ON TWITTER https://twitter.com/Owain_LW/status/1398215813061033985 #brightonSEO | owainlloydwilliams.com | @Owain_LW
17. Owain Lloyd-Williams | owainlloydwilliams.com | @Owain_LW https://twitter.com/Owain_LW/status/1398215813061033985
18. Owain Lloyd-Williams | owainlloydwilliams.com | @Owain_LW https://twitter.com/Owain_LW/status/1398215813061033985
19. EQUATING FOOTBALL STATS WITH SEO DATA POINTS #brightonSEO | owainlloydwilliams.com | @Owain_LW
20. FOR EXAMPLE #brightonSEO | owainlloydwilliams.com | @Owain_LW
21. OR #brightonSEO | owainlloydwilliams.com | @Owain_LW
22. OR EVEN (BEAR WITH ME) #brightonSEO | owainlloydwilliams.com | @Owain_LW
23. TENUOUS? MAYBE.
25. HERE’S WHAT I ENDED UP WITH 1) Crawl Stats/Keywords Ranked/Traffic
26. HERE’S WHAT I ENDED UP WITH 2) Technical Issues/Traffic/Keywords
27. HERE’S WHAT I ENDED UP WITH 3) Traffic/Keywords/Conversions
28. HERE’S WHAT I ENDED UP WITH 4) Keywords & Ecommerce Forecasting
29. REPORT ONE: CRAWLING/KEYWORDS/TRAFFIC Player name Total points Minutes played Clean sheets Goals Website Pages in sitemap Pages indexed on GSC Page indexed on site search Crawled, not indexed Excluded from index Indexed, not in sitemap Traffic Keywords ranked
30. DATA SOURCES #brightonSEO | owainlloydwilliams.com | @Owain_LW
31. POPULATING THE DATA… #brightonSEO | owainlloydwilliams.com | @Owain_LW
32. CRUNCHING THE DATA #brightonSEO | owainlloydwilliams.com | @Owain_LW
33. CRUNCHING THE DATA #brightonSEO | owainlloydwilliams.com | @Owain_LW
34. REPORT TWO: TECHNICAL ISSUES/TRAFFIC/KEYWORDS/GOALS Player name Team Times Picked Total points Minutes played Clean sheets Goals Assists Points on Bench Landing Page Keyword Category Traffic Total Keywords Ranked Medium Issues Urgent Issues Goal 1 Goal 2 Goal 3 Average Issues/Keywords
35. DATA SOURCES - STACKING UP YOUR TOOLS
36. POPULATING THE DATA… #brightonSEO | owainlloydwilliams.com | @Owain_LW
37. CRUNCHING THE DATA #brightonSEO | owainlloydwilliams.com | @Owain_LW
38. REPORT THREE: FORECASTING Player name Team Times picked Nationality Price Goals Keyword Keyword bucket Rank Landing page Keyword difficulty Traffic from keyword Goals from keyword Forecasted traffic/goals
39. POPULATING THE DATA… #brightonSEO | owainlloydwilliams.com | @Owain_LW
40. CRUNCHING THE DATA #brightonSEO | owainlloydwilliams.com | @Owain_LW
41. MANUAL DATA PULLING? A FAMILIAR EXCERICSE… #brightonSEO | owainlloydwilliams.com | @Owain_LW
42. ANOTHER REPORTING TEMPLATE… https://www.owainlloydwilliams.com/measuring-seo-roi-reporting-template/ #brightonSEO | owainlloydwilliams.com | @Owain_LW
43. DON’T WE DO THIS ALL THE TIME AS SEOs?
44. DON’T WE DO THIS ALL THE TIME AS SEOs?
45. DON’T WE DO THIS ALL THE TIME AS SEOs?
46. THERE ARE FIXES: SEOTOOLS FOR EXCEL
47. FORMULA TIME…
48. EXCEL FORMULAS DISCOVERED/USED DURING THE PROCESS 1) OFFSET #brightonSEO | owainlloydwilliams.com | @Owain_LW
49. EXCEL FORMULAS DISCOVERED/USED DURING THE PROCESS 1) OFFSET https://www.distilled.net/excel-for-seo/ #brightonSEO | owainlloydwilliams.com | @Owain_LW
50. EXCEL FORMULAS DISCOVERED/USED DURING THE PROCESS 2) EXTRACTING DATA FROM SUBFOLDERS #brightonSEO | owainlloydwilliams.com | @Owain_LW
51. EXCEL FORMULAS DISCOVERED/USED DURING THE PROCESS 2) EXTRACTING DATA FROM SUBFOLDERS #brightonSEO | owainlloydwilliams.com | @Owain_LW
52. EXCEL FORMULAS DISCOVERED/USED DURING THE PROCESS https://builtvisible.com/excel-formulas-for-seos-extracting-url-strings/ =SUBSTITUTE(TRIM(LEFT(RIGHT(SUBSTITUTE(" /"&IF(RIGHT(A2,1)="/",LEFT(A2,LEN(A2)- 1),A2),"/",REPT(" ",99)),2*99),999))," ","/") #brightonSEO | owainlloydwilliams.com | @Owain_LW
53. EXCEL FORMULAS DISCOVERED/USED DURING THE PROCESS 3) IF/ISNA/VLOOKUP #brightonSEO | owainlloydwilliams.com | @Owain_LW
54. EXCEL FORMULAS DISCOVERED/USED DURING THE PROCESS DO THE REPORTING TEMPLATES CONTAIN THE SAME DOMAIN/URL THAT I CAN CROSS REFERENCE IN ONE COMBINED SHEET? #brightonSEO | owainlloydwilliams.com | @Owain_LW
55. EXCEL FORMULAS DISCOVERED/USED DURING THE PROCESS =IF(ISNA(VLOOKUP(A2,Sheet1!A:F,6,FALSE)), ”NotFound”, VLOOKUP(Sheet2!A2,Sheet1!A:F,6,FALSE)) #brightonSEO | owainlloydwilliams.com | @Owain_LW
56. EXCEL FORMULAS DISCOVERED/USED DURING THE PROCESS 3) IF/ISNA/VLOOKUP #brightonSEO | owainlloydwilliams.com | @Owain_LW
57. THE NEED FOR MANUAL REPORTING IN 2021
58. CONTEXTUALISING SEO ISSUES BETTER
59. CONTEXTUALISING SEO ISSUES BETTER Over reliance on automated reports can keep you away from the bigger picture #brightonSEO | owainlloydwilliams.com | @Owain_LW
60. CONTEXTUALISING SEO ISSUES BETTER This doesn’t tell us much #brightonSEO | owainlloydwilliams.com | @Owain_LW
61. LIMITLESS POTENTIAL
62. LIMITLESS POTENTIAL TEMPLATES ARE TEMPLATES ARE TEMPLATES ARE TEMPLATES #brightonSEO | owainlloydwilliams.com | @Owain_LW
63. LIMITLESS POTENTIAL • LOG FILES • INDEXATION • HREFLANG http://www.bydatabedriven.com/seo-dashboard-in-excel/ #brightonSEO | owainlloydwilliams.com | @Owain_LW
64. LIMITLESS POTENTIAL https://medium.com/@jacobjs/beginners-guide-to-seotools-for-excel-part-1-db84ed54daff #brightonSEO | owainlloydwilliams.com | @Owain_LW
65. AS A PROCESS, IT KEEPS YOU SHARP
66. IT HELPED ME IN FPL, THAT’S ALL I’LL SAY… #brightonSEO | owainlloydwilliams.com | @Owain_LW
67. WHAT NEXT? https://docs.google.com/spreadsheets/d/1lA hKA4OIfw9Hyh3xfHgTDNJALXwX9- Aimp2HCHL05aw/edit#gid=631008577 https://www.owainlloydwilliams.com/fantasy -football-stats-for-seo-reporting/ 2021/2022 season? API pulling? #brightonSEO | owainlloydwilliams.com | @Owain_LW
68. THANKS!