AI and Me: how we built a GSC Bulk Export Data Pipeline

Here's a concise summary of the conference talk:

Noah presented his vision of merging Google Search Console (GSC) Bulk Data Export with his Branch Explorer product to create a powerful SEO analytics tool. The GSC Bulk Export provides comprehensive search data directly to BigQuery without row limits, offering detailed insights including anonymized queries and search appearances.

Noah built the tool entirely using AI, including ChatGPT and Claude. He shared lessons learned from this process, including AI's capabilities and limitations, the importance of iterative development, and effective tools for AI-assisted coding.

Key points about the GSC Bulk Export data pipeline include:
1. No historical data backfill
2. Potential costs associated with BigQuery usage
3. Advanced segmentation and custom pipeline possibilities

Noah discussed AI development pitfalls, such as natural language misunderstandings and error resolution challenges. He created custom GPTs to assist with specific coding tasks.

The final product, Branch Explorer with Bulk Data Integration, is a Looker Studio-based visualization tool that allows users to explore GSC data in novel ways, including segmentation by subdirectories, UTM parameters, and device types. Noah made the tool available to conference attendees.

Transcript

1. Sterling Sky noahlearner What I learned building software with AI
2. Sterling Sky noahlearner The Mashup
3. Sterling Sky noahlearner GSC Bulk Export
4. Sterling Sky noahlearner EXPLORER
5. Sterling Sky noahlearner and to build it using only AI.
6. Sterling Sky noahlearner I promise you, within 6 months anyone will be able to build anything with an LLM. -Dave Sottimano
7. Sterling Sky noahlearner
8. Sterling Sky noahlearner The GSC Bulk Export
9. Sterling Sky noahlearner GSC Bulk Export is 3 tables •ExportLog •searchdata_url_impression •searchdata_site_impression
10. Sterling Sky noahlearner
11. Sterling Sky noahlearner GSC Bulk Export is 3 tables •ExportLog •searchdata_url_impression •searchdata_site_impression* *I didn’t use the site_impression table due to time constraints.
12. Sterling Sky noahlearner It includes a ton of data
13. Sterling Sky noahlearner GSC Bulk Export includes •All Countries •All Devices •All Search Types •All Search Appearances (Booleans) •Anonymized Queries •No Row Limits
14. Sterling Sky noahlearner ALL Search Types BOOM!
15. Sterling Sky noahlearner there weren’t [m]any data viz tools set up for the Bulk Export yet.
16. Sterling Sky noahlearner With Explorer we can see more:
17. Sterling Sky noahlearner With Explorer we can see our data: •Brand vs Non Brand
18. Sterling Sky noahlearner With Explorer we can see our data: •Brand vs Non Brand •Funnel stage (top, middle, bottom)
19. Sterling Sky noahlearner With Explorer we can see our data: •Brand vs Non Brand •Funnel stage (top, middle, bottom) •Is GBP URL (url has GBP related UTMS)
20. Sterling Sky noahlearner With Explorer we can see our data: •Brand vs Non Brand •Funnel stage (top, middle, bottom) •Is GBP URL (url has GBP related UTMS) •Segment our site by URL patterns
21. Sterling Sky noahlearner With Explorer we can see: •Brand vs Non Brand •Funnel stage (top, middle, bottom) •Is GBP URL (url has GBP related UTMS) •Segment our site by URL patterns •Which Directories, pages, + queries are driving change on our sites.
22. Sterling Sky noahlearner It’s not about having more data, it’s about having more ways to see it.
23. noahlearner Sterling Sky What Could Possibly Go Wrong, Right? What Could Possibly Go Wrong, Right?
24. Sterling Sky noahlearner My approach
25. Sterling Sky noahlearner
26. noahlearner Sterling Sky Painkillers not Vitamins
27. Sterling Sky noahlearner A Painkiller saves 50-100s of hours of work per month
28. Sterling Sky noahlearner Painkillers smooth out capacity challenges
29. Sterling Sky noahlearner Painkillers make us sticky
30. Sterling Sky noahlearner
31. Sterling Sky noahlearner I coded with VSCode + Co-Pilot
32. Sterling Sky noahlearner Copilot Autocomplete Lines + small blocks of code.
33. Sterling Sky noahlearner I was happy as a clam
34. noahlearner Sterling Sky the Dr. Marie Haynes Intervention
35. noahlearner Sterling Sky How has my workflow changed since?
36. Sterling Sky noahlearner I used ChatGPT
37. Sterling Sky noahlearner Tokens 1 token ~= 4 chars in English 1 token ~= ¾ words 100 tokens ~= 75 words 1-2 sentence ~= 30 tokens 1 paragraph ~= 100 tokens 1,500 words ~= 2048 tokens
38. Sterling Sky noahlearner Context window measures how many tokens that the model can process at once
39. Sterling Sky noahlearner Open AI
40. Sterling Sky noahlearner I built Custom GPTs
41. Sterling Sky noahlearner Noah’s Stackbot
42. Sterling Sky noahlearner Stackbot allowed me to scale output to larger functions
43. Sterling Sky noahlearner Noah’s WPT GPT
44. Sterling Sky noahlearner WPGPT helped me build Wordpress Plugins
45. Sterling Sky noahlearner
46. Sterling Sky noahlearner Noah’s apps script GPT
47. Sterling Sky noahlearner Claude Sonnet 3.5
48. Sterling Sky noahlearner 25X the context window size
49. Sterling Sky noahlearner Claude Sonnet 3.5 Artifacts + Projects
50. Sterling Sky noahlearner Artifact Interactive code on right of chat
51. Sterling Sky noahlearner Project Add relevant code
52. Sterling Sky noahlearner File management sucked
53. Sterling Sky noahlearner Iteration cycles were too slow
54. noahlearner Sterling Sky Photo by Gwendal Cottin on Unsplash TOTAL PITA
55. Sterling Sky noahlearner Working with AI means tons of Iterations
56. Sterling Sky noahlearner The speed of iteration is key to innovation
57. Sterling Sky noahlearner Cursor
58. Sterling Sky noahlearner Cursor is a VS Code clone with AI chat in the IDE.
59. Sterling Sky noahlearner Knows your code due to massive 200k context window
60. branch.tools noahlearner TAKAWAYS from the Build
61. noahlearner Sterling Sky I developed best practices
62. Sterling Sky noahlearner 1. Describe end goal in high level terms
63. Sterling Sky noahlearner 1. Describe end goal in high level terms 2. Ask the LLM to summarize the goal
64. Sterling Sky noahlearner 1. Describe end goal in high level terms 2. Ask the LLM to summarize the goal 3. Iteratively clarify the goal
65. Sterling Sky noahlearner 1. Describe end goal in high level terms 2. Ask the LLM to summarize the goal 3. Iteratively clarify the goal 4. Ask model to build architecture
66. Sterling Sky noahlearner 1. Describe end goal in high level terms 2. Ask the LLM to summarize the goal 3. Iteratively clarify the goal 4. Ask model to build architecture 5. Iteratively clarify architecture
67. Sterling Sky noahlearner 1. Describe end goal in high level terms 2. Ask the LLM to summarize the goal 3. Iteratively clarify the goal 4. Ask model to build architecture 5. Iteratively clarify architecture 6. Ask model to build function pseudo code
68. Sterling Sky noahlearner 1. Describe end goal in high level terms 2. Ask the LLM to summarize the goal 3. Iteratively clarify the goal 4. Ask model to build architecture 5. Iteratively clarify architecture 6. Ask model to build function pseudo code 7. Ask model to build actual code
69. Sterling Sky noahlearner 1. Describe end goal in High level terms 2. Ask the LLM to summarize the goal 3. Iteratively clarify the goal 4. Ask model to build architecture 5. Iteratively clarify architecture 6. Ask model to build function pseudo code 7. Ask model to build actual code 8. Iterate, and iterate and iterate
70. Sterling Sky noahlearner The iteration is where the time is spent.
71. noahlearner Sterling Sky GSC Bulk Export shows •epoch_version field in ExportLog table lets us see when Google had data quality issues. •Google is testing serps all the time. •what pages are triggering anonymous queries.
72. noahlearner Sterling Sky Bulk Export costs tied to amount of data Queried
73. noahlearner Sterling Sky Costs for 2 years S / M sized site: $0-$5 Enterprise site: $50 -$400
74. noahlearner Sterling Sky Bulk Export does not have historic backfill
75. noahlearner Sterling Sky Coding with AI is still for early adopters
76. Sterling Sky noahlearner
77. Sterling Sky noahlearner Tolerance for uncertainty comfortable with uncertainty, potential setbacks, and pain.
78. Sterling Sky noahlearner Less risk-averse willing to accept potential failures as part of the innovation process.
79. Sterling Sky noahlearner Early adopters are gluttons for punishment
80. noahlearner Sterling Sky Models create trash outputs
81. Sterling Sky noahlearner Noah’s Slide Builder
82. Sterling Sky noahlearner
83. Sterling Sky noahlearner
84. Sterling Sky noahlearner 10 iterations later
85. Sterling Sky noahlearner
86. Sterling Sky noahlearner
87. Sterling Sky noahlearner
88. Sterling Sky noahlearner
89. Sterling Sky noahlearner What Could Possibly Go Wrong, Right?
90. Sterling Sky noahlearner This was as close as I could get
91. Sterling Sky noahlearner Models lie
92. Sterling Sky noahlearner
93. Sterling Sky noahlearner
94. Sterling Sky noahlearner 8 iterations + 15 minutes later
95. Sterling Sky noahlearner As an AI language model, I don't have direct access to your codebase or file system.
96. Sterling Sky noahlearner I can't create or modify files in your codebase directly.
97. Sterling Sky noahlearner Photo by Xavi Cabrera on Unsplash What The F
98. Sterling Sky noahlearner Aider creates + edits files in your local git repo
99. Sterling Sky noahlearner
100. noahlearner Sterling Sky Models forget context
101. noahlearner Sterling Sky Models create broken code.
102. noahlearner Sterling Sky ¯\_(ツ)_/¯
103. Sterling Sky noahlearner AI + I Built : SQL Procedures docs / setup page
104. Sterling Sky noahlearner Docs to set up your pipeline + Report.
105. Sterling Sky noahlearner Setup takes 20-30 minutes
106. Sterling Sky noahlearner Stay in touch! noah@sterlingsky.ca linkedin.com/in/noahlearner/ @noahlearner theseocommunity.com/apply.html