Slide 1
Slide 1 text
What Fantasy Football Taught
Me About Building Nuanced
SEO Reports
Owain Lloyd-Williams
SON OF JACK LTD
@Owain_LW
Slide 2
Slide 2 text
#brightonSEO | owainlloydwilliams.com | @Owain_LW
What Fantasy Football
Taught Me About
Building Nuanced SEO
Reports
Slide 3
Slide 3 text
A LITTLE ABOUT ME
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 4
Slide 4 text
A LITTLE ABOUT ME
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 5
Slide 5 text
CHINA SEO NICHE
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 6
Slide 6 text
CHINA SEO NICHE
https://builtvisible.com/baidu-seo-guide/ https://moz.com/learn/seo/content-
marketing-china-side
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 7
Slide 7 text
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
Slide 8
Slide 8 text
BACK TO IT THEN
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 9
Slide 9 text
IN LOCKDOWN I WAS BORED
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 10
Slide 10 text
SO I STARTED DOING THIS….
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 11
Slide 11 text
AND ENDED UP WITH THIS….
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 12
Slide 12 text
MORE BOREDOM LED TO THIS
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 13
Slide 13 text
AND THIS…
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 14
Slide 14 text
AS WELL AS THIS
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 15
Slide 15 text
AS WELL AS THIS
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 16
Slide 16 text
SCREAMING INTO THE ABYSS ON TWITTER
https://twitter.com/Owain_LW/status/1398215813061033985
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 17
Slide 17 text
Owain Lloyd-Williams | owainlloydwilliams.com | @Owain_LW
https://twitter.com/Owain_LW/status/1398215813061033985
Slide 18
Slide 18 text
Owain Lloyd-Williams | owainlloydwilliams.com | @Owain_LW
https://twitter.com/Owain_LW/status/1398215813061033985
Slide 19
Slide 19 text
EQUATING FOOTBALL STATS WITH SEO DATA POINTS
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 20
Slide 20 text
FOR EXAMPLE
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 21
Slide 21 text
OR
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 22
Slide 22 text
OR EVEN (BEAR WITH ME)
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 23
Slide 23 text
TENUOUS? MAYBE.
Slide 24
Slide 24 text
No content
Slide 25
Slide 25 text
HERE’S WHAT I ENDED UP WITH
1) Crawl Stats/Keywords Ranked/Traffic
Slide 26
Slide 26 text
HERE’S WHAT I ENDED UP WITH
2) Technical Issues/Traffic/Keywords
Slide 27
Slide 27 text
HERE’S WHAT I ENDED UP WITH
3) Traffic/Keywords/Conversions
Slide 28
Slide 28 text
HERE’S WHAT I ENDED UP WITH
4) Keywords & Ecommerce Forecasting
Slide 29
Slide 29 text
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
Slide 30
Slide 30 text
DATA SOURCES
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 31
Slide 31 text
POPULATING THE DATA…
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 32
Slide 32 text
CRUNCHING THE DATA
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 33
Slide 33 text
CRUNCHING THE DATA
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 34
Slide 34 text
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
Slide 35
Slide 35 text
DATA SOURCES - STACKING UP YOUR TOOLS
Slide 36
Slide 36 text
POPULATING THE DATA…
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 37
Slide 37 text
CRUNCHING THE DATA
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 38
Slide 38 text
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
Slide 39
Slide 39 text
POPULATING THE DATA…
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 40
Slide 40 text
CRUNCHING THE DATA
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 41
Slide 41 text
MANUAL DATA PULLING? A FAMILIAR EXCERICSE…
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 42
Slide 42 text
ANOTHER REPORTING TEMPLATE…
https://www.owainlloydwilliams.com/measuring-seo-roi-reporting-template/
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 43
Slide 43 text
DON’T WE DO THIS ALL THE TIME AS SEOs?
Slide 44
Slide 44 text
DON’T WE DO THIS ALL THE TIME AS SEOs?
Slide 45
Slide 45 text
DON’T WE DO THIS ALL THE TIME AS SEOs?
Slide 46
Slide 46 text
THERE ARE FIXES: SEOTOOLS FOR EXCEL
Slide 47
Slide 47 text
FORMULA TIME…
Slide 48
Slide 48 text
EXCEL FORMULAS DISCOVERED/USED DURING THE PROCESS
1) OFFSET
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 49
Slide 49 text
EXCEL FORMULAS DISCOVERED/USED DURING THE PROCESS
1) OFFSET
https://www.distilled.net/excel-for-seo/
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 50
Slide 50 text
EXCEL FORMULAS DISCOVERED/USED DURING THE PROCESS
2) EXTRACTING DATA FROM SUBFOLDERS
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 51
Slide 51 text
EXCEL FORMULAS DISCOVERED/USED DURING THE PROCESS
2) EXTRACTING DATA FROM SUBFOLDERS
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 52
Slide 52 text
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
Slide 53
Slide 53 text
EXCEL FORMULAS DISCOVERED/USED DURING THE PROCESS
3) IF/ISNA/VLOOKUP
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 54
Slide 54 text
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
Slide 55
Slide 55 text
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
Slide 56
Slide 56 text
EXCEL FORMULAS DISCOVERED/USED DURING THE PROCESS
3) IF/ISNA/VLOOKUP
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 57
Slide 57 text
THE NEED FOR MANUAL
REPORTING IN 2021
Slide 58
Slide 58 text
CONTEXTUALISING SEO
ISSUES BETTER
Slide 59
Slide 59 text
CONTEXTUALISING SEO ISSUES BETTER
Over reliance
on automated
reports can
keep you away
from the bigger
picture
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 60
Slide 60 text
CONTEXTUALISING SEO ISSUES BETTER
This doesn’t tell us
much
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 61
Slide 61 text
LIMITLESS POTENTIAL
Slide 62
Slide 62 text
LIMITLESS POTENTIAL
TEMPLATES
ARE
TEMPLATES
ARE
TEMPLATES
ARE
TEMPLATES
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 63
Slide 63 text
LIMITLESS POTENTIAL
• LOG FILES
• INDEXATION
• HREFLANG
http://www.bydatabedriven.com/seo-dashboard-in-excel/
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 64
Slide 64 text
LIMITLESS POTENTIAL
https://medium.com/@jacobjs/beginners-guide-to-seotools-for-excel-part-1-db84ed54daff
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 65
Slide 65 text
AS A PROCESS, IT
KEEPS YOU SHARP
Slide 66
Slide 66 text
IT HELPED ME IN FPL, THAT’S ALL I’LL SAY…
#brightonSEO | owainlloydwilliams.com | @Owain_LW
Slide 67
Slide 67 text
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
Slide 68
Slide 68 text
THANKS!
Slide 69
Slide 69 text
No content