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

Slide 1

Slide 1 text

Sterling Sky noahlearner What I learned building software with AI

Slide 2

Slide 2 text

Sterling Sky noahlearner The Mashup

Slide 3

Slide 3 text

Sterling Sky noahlearner GSC Bulk Export

Slide 4

Slide 4 text

Sterling Sky noahlearner EXPLORER

Slide 5

Slide 5 text

Sterling Sky noahlearner and to build it using only AI.

Slide 6

Slide 6 text

Sterling Sky noahlearner I promise you, within 6 months anyone will be able to build anything with an LLM. -Dave Sottimano

Slide 7

Slide 7 text

Sterling Sky noahlearner

Slide 8

Slide 8 text

Sterling Sky noahlearner The GSC Bulk Export

Slide 9

Slide 9 text

Sterling Sky noahlearner GSC Bulk Export is 3 tables •ExportLog •searchdata_url_impression •searchdata_site_impression

Slide 10

Slide 10 text

Sterling Sky noahlearner

Slide 11

Slide 11 text

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.

Slide 12

Slide 12 text

Sterling Sky noahlearner It includes a ton of data

Slide 13

Slide 13 text

Sterling Sky noahlearner GSC Bulk Export includes •All Countries •All Devices •All Search Types •All Search Appearances (Booleans) •Anonymized Queries •No Row Limits

Slide 14

Slide 14 text

Sterling Sky noahlearner ALL Search Types BOOM!

Slide 15

Slide 15 text

Sterling Sky noahlearner there weren’t [m]any data viz tools set up for the Bulk Export yet.

Slide 16

Slide 16 text

Sterling Sky noahlearner With Explorer we can see more:

Slide 17

Slide 17 text

Sterling Sky noahlearner With Explorer we can see our data: •Brand vs Non Brand

Slide 18

Slide 18 text

Sterling Sky noahlearner With Explorer we can see our data: •Brand vs Non Brand •Funnel stage (top, middle, bottom)

Slide 19

Slide 19 text

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)

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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.

Slide 22

Slide 22 text

Sterling Sky noahlearner It’s not about having more data, it’s about having more ways to see it.

Slide 23

Slide 23 text

noahlearner Sterling Sky What Could Possibly Go Wrong, Right? What Could Possibly Go Wrong, Right?

Slide 24

Slide 24 text

Sterling Sky noahlearner My approach

Slide 25

Slide 25 text

Sterling Sky noahlearner

Slide 26

Slide 26 text

noahlearner Sterling Sky Painkillers not Vitamins

Slide 27

Slide 27 text

Sterling Sky noahlearner A Painkiller saves 50-100s of hours of work per month

Slide 28

Slide 28 text

Sterling Sky noahlearner Painkillers smooth out capacity challenges

Slide 29

Slide 29 text

Sterling Sky noahlearner Painkillers make us sticky

Slide 30

Slide 30 text

Sterling Sky noahlearner

Slide 31

Slide 31 text

Sterling Sky noahlearner I coded with VSCode + Co-Pilot

Slide 32

Slide 32 text

Sterling Sky noahlearner Copilot Autocomplete Lines + small blocks of code.

Slide 33

Slide 33 text

Sterling Sky noahlearner I was happy as a clam

Slide 34

Slide 34 text

noahlearner Sterling Sky the Dr. Marie Haynes Intervention

Slide 35

Slide 35 text

noahlearner Sterling Sky How has my workflow changed since?

Slide 36

Slide 36 text

Sterling Sky noahlearner I used ChatGPT

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

Sterling Sky noahlearner Context window measures how many tokens that the model can process at once

Slide 39

Slide 39 text

Sterling Sky noahlearner Open AI

Slide 40

Slide 40 text

Sterling Sky noahlearner I built Custom GPTs

Slide 41

Slide 41 text

Sterling Sky noahlearner Noah’s Stackbot

Slide 42

Slide 42 text

Sterling Sky noahlearner Stackbot allowed me to scale output to larger functions

Slide 43

Slide 43 text

Sterling Sky noahlearner Noah’s WPT GPT

Slide 44

Slide 44 text

Sterling Sky noahlearner WPGPT helped me build Wordpress Plugins

Slide 45

Slide 45 text

Sterling Sky noahlearner

Slide 46

Slide 46 text

Sterling Sky noahlearner Noah’s apps script GPT

Slide 47

Slide 47 text

Sterling Sky noahlearner Claude Sonnet 3.5

Slide 48

Slide 48 text

Sterling Sky noahlearner 25X the context window size

Slide 49

Slide 49 text

Sterling Sky noahlearner Claude Sonnet 3.5 Artifacts + Projects

Slide 50

Slide 50 text

Sterling Sky noahlearner Artifact Interactive code on right of chat

Slide 51

Slide 51 text

Sterling Sky noahlearner Project Add relevant code

Slide 52

Slide 52 text

Sterling Sky noahlearner File management sucked

Slide 53

Slide 53 text

Sterling Sky noahlearner Iteration cycles were too slow

Slide 54

Slide 54 text

noahlearner Sterling Sky Photo by Gwendal Cottin on Unsplash TOTAL PITA

Slide 55

Slide 55 text

Sterling Sky noahlearner Working with AI means tons of Iterations

Slide 56

Slide 56 text

Sterling Sky noahlearner The speed of iteration is key to innovation

Slide 57

Slide 57 text

Sterling Sky noahlearner Cursor

Slide 58

Slide 58 text

Sterling Sky noahlearner Cursor is a VS Code clone with AI chat in the IDE.

Slide 59

Slide 59 text

Sterling Sky noahlearner Knows your code due to massive 200k context window

Slide 60

Slide 60 text

branch.tools noahlearner TAKAWAYS from the Build

Slide 61

Slide 61 text

noahlearner Sterling Sky I developed best practices

Slide 62

Slide 62 text

Sterling Sky noahlearner 1. Describe end goal in high level terms

Slide 63

Slide 63 text

Sterling Sky noahlearner 1. Describe end goal in high level terms 2. Ask the LLM to summarize the goal

Slide 64

Slide 64 text

Sterling Sky noahlearner 1. Describe end goal in high level terms 2. Ask the LLM to summarize the goal 3. Iteratively clarify the goal

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

Slide 68

Slide 68 text

Slide 69

Slide 69 text

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

Slide 70

Slide 70 text

Sterling Sky noahlearner The iteration is where the time is spent.

Slide 71

Slide 71 text

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.

Slide 72

Slide 72 text

noahlearner Sterling Sky Bulk Export costs tied to amount of data Queried

Slide 73

Slide 73 text

noahlearner Sterling Sky Costs for 2 years S / M sized site: $0-$5 Enterprise site: $50 -$400

Slide 74

Slide 74 text

noahlearner Sterling Sky Bulk Export does not have historic backfill

Slide 75

Slide 75 text

noahlearner Sterling Sky Coding with AI is still for early adopters

Slide 76

Slide 76 text

Sterling Sky noahlearner

Slide 77

Slide 77 text

Sterling Sky noahlearner Tolerance for uncertainty comfortable with uncertainty, potential setbacks, and pain.

Slide 78

Slide 78 text

Sterling Sky noahlearner Less risk-averse willing to accept potential failures as part of the innovation process.

Slide 79

Slide 79 text

Sterling Sky noahlearner Early adopters are gluttons for punishment

Slide 80

Slide 80 text

noahlearner Sterling Sky Models create trash outputs

Slide 81

Slide 81 text

Sterling Sky noahlearner Noah’s Slide Builder

Slide 82

Slide 82 text

Sterling Sky noahlearner

Slide 83

Slide 83 text

Sterling Sky noahlearner

Slide 84

Slide 84 text

Sterling Sky noahlearner 10 iterations later

Slide 85

Slide 85 text

Sterling Sky noahlearner

Slide 86

Slide 86 text

Sterling Sky noahlearner

Slide 87

Slide 87 text

Sterling Sky noahlearner

Slide 88

Slide 88 text

Sterling Sky noahlearner

Slide 89

Slide 89 text

Sterling Sky noahlearner What Could Possibly Go Wrong, Right?

Slide 90

Slide 90 text

Sterling Sky noahlearner This was as close as I could get

Slide 91

Slide 91 text

Sterling Sky noahlearner Models lie

Slide 92

Slide 92 text

Sterling Sky noahlearner

Slide 93

Slide 93 text

Sterling Sky noahlearner

Slide 94

Slide 94 text

Sterling Sky noahlearner 8 iterations + 15 minutes later

Slide 95

Slide 95 text

Sterling Sky noahlearner As an AI language model, I don't have direct access to your codebase or file system.

Slide 96

Slide 96 text

Sterling Sky noahlearner I can't create or modify files in your codebase directly.

Slide 97

Slide 97 text

Sterling Sky noahlearner Photo by Xavi Cabrera on Unsplash What The F

Slide 98

Slide 98 text

Sterling Sky noahlearner Aider creates + edits files in your local git repo

Slide 99

Slide 99 text

Sterling Sky noahlearner

Slide 100

Slide 100 text

noahlearner Sterling Sky Models forget context

Slide 101

Slide 101 text

noahlearner Sterling Sky Models create broken code.

Slide 102

Slide 102 text

noahlearner Sterling Sky ¯\_(ツ)_/¯

Slide 103

Slide 103 text

Sterling Sky noahlearner AI + I Built : SQL Procedures docs / setup page

Slide 104

Slide 104 text

Sterling Sky noahlearner Docs to set up your pipeline + Report.

Slide 105

Slide 105 text

Sterling Sky noahlearner Setup takes 20-30 minutes

Slide 106

Slide 106 text

Sterling Sky noahlearner Stay in touch! [email protected] linkedin.com/in/noahlearner/ @noahlearner theseocommunity.com/apply.html