FoundConf Noah Learner.pdf
本地来源：seo-llm/raw/seo知识库/TXT整理/PPT/John喜欢和引用的那些海外演讲嘉宾PPTs/FoundConf-Noah Learner.pdf.txt
It sure would be cool
if you could talk about
what you do around
here.
- Joy Hawkins
noahlearner | Sterling Sky
Me flying to Japan
noahlearner | Sterling Sky
I feel Like this
noahlearner | Sterling Sky
Out of control. And a
little afraid.
noahlearner | Sterling Sky
We all
experience
Turbulence
noahlearner | Sterling Sky
We experience
turbulence
every day
noahlearner | Sterling Sky
This deck is about
noahlearner | Sterling Sky
Controlling
the
controllables
noahlearner | Sterling Sky
Seeing wider
noahlearner | Sterling Sky
Thinking bigger
noahlearner | Sterling Sky
What we see
noahlearner | Sterling Sky
Go deep into the data
noahlearner | Sterling Sky
noahlearner | Sterling Sky
Google Analytics
noahlearner | Sterling Sky
How do you make
money?
noahlearner | Sterling Sky
What are you doing in each
channel to drive revenue?
noahlearner | Sterling Sky
What’s a channel?
noahlearner | Sterling Sky
Channels are how we
categorize traffic sources
to our websites
noahlearner | Sterling Sky
Primary dimensions:
noahlearner | Sterling Sky
Source
where the traffic originated
google, facebook, newsletter
noahlearner | Sterling Sky
Medium
the general category of the source
organic, cpc, email, referral
noahlearner | Sterling Sky
Campaign
the specific marketing campaign name (if
tagged)
noahlearner | Sterling Sky
Secondary dimensions:
Source platform
the platform that sent the traffic
Campaign ID
unique identifier for campaigns
Google Ads parameters
for paid Google traffic
noahlearner | Sterling Sky
Google’s list
of sources +
categories
noahlearner | Sterling Sky
Important
LLM sources are not
on the list
noahlearner | Sterling Sky
We SEOs are obsessed
with Organic Search
noahlearner | Sterling Sky
We need to understand
how all the channels
work on our sites
noahlearner | Sterling Sky
Organic Search
Source matches a list of search sites listed
under "SOURCE_CATEGORY_SEARCH"
OR
Medium exactly matches organic
noahlearner | Sterling Sky
Organic Social
Source matches a regex list of social sites
OR
Medium is one of (“social”, “social-network”,
“social-media”, “sm”, “social network”, “social
media”)
noahlearner | Sterling Sky
Organic Video
Source matches a list of video sites
OR
Medium matches regex ^(.*video.*)$
noahlearner | Sterling Sky
Referral
Medium is one of ("referral", "app", or "link")
noahlearner | Sterling Sky
Email
Source = email|e-mail|e_mail|e mail
OR
Medium = email|e-mail|e_mail|e mail
noahlearner | Sterling Sky
Direct
Source exactly matches "(direct)"
AND
Medium is one of ("(not set)", "(none)")
noahlearner | Sterling Sky
What about LLMs?
noahlearner | Sterling Sky
Enter Custom
Channels
How to build them
noahlearner | Sterling Sky
noahlearner | Sterling Sky
LLM Channel - source matches Regex
^.*ai|.*¥.openai.*|.*copilot.*|.*chatgpt.*|.*
gemini.*|.*gpt.*|.*neeva.*|.*writesonic.*|.*
nimble.*|.*outrider.*|.*perplexity.*|.*googl
e.*bard.*|.*bard.*google.*|.*bard.*|.*edge
services.*|.*astastic.*|.*copy.ai.*|.*bnngpt.
*|.*gemini.*google.*$
noahlearner | Sterling Sky
LLM
Channel
Drag to top
noahlearner | Sterling Sky
noahlearner | Sterling Sky
Week
Grain
is the amount of time we
measure our data by
Month
noahlearner | Sterling Sky
noahlearner | Sterling Sky
Use calculated
fields to reduce
cognitive load
noahlearner | Sterling Sky
noahlearner | Sterling Sky
noahlearner | Sterling Sky
Drilldown
noahlearner | Sterling Sky
noahlearner | Sterling Sky
noahlearner | Sterling Sky
1
2
3
4
noahlearner | Sterling Sky
Cross filtering allows us to go
from a haystack to a needle
noahlearner | Sterling Sky
noahlearner | Sterling Sky
Filter from channel to page + see
where + when key events occur
noahlearner | Sterling Sky
1
2
3
4
5
noahlearner | Sterling Sky
Revenue =
Traffic X Conv rate X Avg order / Lead
Value
noahlearner | Sterling Sky
Organic search traffic is down
noahlearner | Sterling Sky
For Revenue to grow we must
noahlearner | Sterling Sky
replace lost traffic
noahlearner | Sterling Sky
AND improve conversion rates
noahlearner | Sterling Sky
AND increase avg order / lead
values too
noahlearner | Sterling Sky
Where does your
audience spend time?
noahlearner | Sterling Sky
Are you active there?
noahlearner | Sterling Sky
Are you putting your
marketing budget where
your audience is?
noahlearner | Sterling Sky
How well do you know
your customers?
noahlearner | Sterling Sky
Next we look at Referrals
The future demands we go wider
noahlearner | Sterling Sky
1
2
3
4
noahlearner | Sterling Sky
1
2
3
noahlearner | Sterling Sky
1
2
3
noahlearner | Sterling Sky
noahlearner | Sterling Sky
Zoom In
+
Zoom Out
noahlearner | Sterling Sky
Let’s take it a step further
The Unassigned Channel
noahlearner | Sterling Sky
noahlearner | Sterling Sky
Unassigned
is the value Google Analytics uses
when no other channel rules match
the event data.
noahlearner | Sterling Sky
Remember grain + drill down?
noahlearner | Sterling Sky
noahlearner | Sterling Sky
noahlearner | Sterling Sky
It’s an entirely new story now
noahlearner | Sterling Sky
With a near weekly shape.
Is it BOT TRAFFIC?
noahlearner | Sterling Sky
Zoom In
on
Aug 4
noahlearner | Sterling Sky
What kind of bots would visit
our site each week?
noahlearner | Sterling Sky
We
found
the
cause
noahlearner | Sterling Sky
noahlearner | Sterling Sky
???
noahlearner | Sterling Sky
noahlearner | Sterling Sky
noahlearner | Sterling Sky
Less impressions
=
Less queries being tracked
noahlearner | Sterling Sky
How much less?
noahlearner | Sterling Sky
In a sample site I saw
noahlearner | Sterling Sky
noahlearner | Sterling Sky
20% less queries tracked
noahlearner | Sterling Sky
39% less rows of information
noahlearner | Sterling Sky
Less queries tracked
=
Less data
noahlearner | Sterling Sky
Less data
=
Lower costs to store
noahlearner | Sterling Sky
Less data
=
Lower costs to query
noahlearner | Sterling Sky
Num=100 deprecation
=
Perfect time for GSC Bulk Export
noahlearner | Sterling Sky
GSC Bulk
Export
noahlearner | Sterling Sky
noahlearner | Sterling Sky
noahlearner | Sterling Sky
noahlearner | Sterling Sky
noahlearner | Sterling Sky
noahlearner | Sterling Sky
GSC Bulk Export is 3 tables
•ExportLog
•searchdata_url_impression
•searchdata_site_impression
noahlearner | Sterling Sky
site_impression
noahlearner | Sterling Sky
url_impression
noahlearner | Sterling Sky
export_log
noahlearner | Sterling Sky
It includes a ton of data
noahlearner | Sterling Sky
GSC Bulk Export includes
•All countries
•All devices
•All search types
•All search appearances (booleans)
•Anonymized queries
•No row limits
noahlearner | Sterling Sky
Total storage
costs through
36 months: $0.12
noahlearner | Sterling Sky
ALL Search
Types
Find Your
Way to Explore
the Data that work for you!
BOOM!
noahlearner | Sterling Sky
it supercharges our
num=100 exploration
noahlearner | Sterling Sky
Missing URL Queries after NUM=100
WITH A AS (
SELECT
query,
SUM(clicks) as clicks,
SUM(impressions) as impressions
FROM `project`.`searchconsole`.`searchdata_url_impression`
WHERE data_date BETWEEN '2025-09-03' AND '2025-09-09' -- Week 1
GROUP BY query
),
B AS (
SELECT
query,
SUM(clicks) as clicks,
SUM(impressions) as impressions
FROM `project`.`searchconsole`.`searchdata_url_impression`
WHERE data_date BETWEEN '2025-09-10' AND '2025-09-16' -- Week 2
GROUP BY query
)
Find Your Way to Explore
the Data that work for you!
-- Queries in A but not in B
SELECT A.*
FROM A
LEFT JOIN B ON A.query = B.query
WHERE B.query IS NULL
ORDER BY A.impressions DESC;
noahlearner | Sterling Sky
Missing Site Queries after NUM=100
WITH A AS (
SELECT
query,
SUM(clicks) as clicks,
SUM(impressions) as impressions
FROM `project`.`searchconsole`.`searchdata_site_impression`
WHERE data_date BETWEEN '2025-09-03' AND '2025-09-09' -- Week 1
GROUP BY query
),
B AS (
SELECT
query,
SUM(clicks) as clicks,
SUM(impressions) as impressions
FROM `project`.`searchconsole`.`searchdata_site_impression`
WHERE data_date BETWEEN '2025-09-10' AND '2025-09-16' -- Week 2
GROUP BY query
)
Find Your Way to Explore
the Data that work for you!
-- Queries in A but not in B
SELECT A.*
FROM A
LEFT JOIN B ON A.query = B.query
WHERE B.query IS NULL
ORDER BY A.impressions DESC;
noahlearner | Sterling Sky
An agency
Specialization
Find Your Way to
Explore
the Data that work for you!
noahlearner | Sterling Sky
Pages w lost anonymous queries
Week over week
WITH A AS (
SELECT
url,
SUM(clicks) as clicks,
SUM(impressions) as impressions,
SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) as avg_position
FROM `project`.`searchconsole`.`searchdata_url_impression`
WHERE data_date BETWEEN '2025-09-03' AND '2025-09-09' -- Week 1
AND query IS NULL
GROUP BY url
),
B AS (
SELECT
url,
SUM(clicks) as clicks,
SUM(impressions) as impressions,
SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) as avg_position
FROM `project`.`searchconsole`.`searchdata_url_impression`
WHERE data_date BETWEEN '2025-09-10' AND '2025-09-16' -- Week 2
AND query IS NULL
GROUP BY url
)
Find Your Way to Explore
the Data that work for you!
-- Pages in Week 1 but not in Week 2 (lost visibility)
SELECT
A.url,
A.impressions as week1_impressions,
A.clicks as week1_clicks,
A.avg_position as week1_avg_position
FROM A
LEFT JOIN B ON A.url = B.url
WHERE B.url IS NULL
ORDER BY A.impressions DESC;
noahlearner | Sterling Sky
Pages with anonymous Queries
SELECT
url,is_anonymized_query,
SUM(clicks) as clicks,
SUM(impressions) as impressions,
SAFE_DIVIDE(SUM(sum_position),
SUM(impressions)) as avg_position
Find Your Way to Explore
the Data that work for you!
FROM `project`.`searchconsole`.`searchdata_url_impression`
WHERE data_date BETWEEN '2024-09-03' AND '2025-09-09' and
is_anonymized_query=true
GROUP BY url, is_anonymized_query
noahlearner | Sterling Sky
Take it up to 11
with Branch
Explorer
noahlearner | Sterling Sky
there weren’t [m]any data
viz tools set up for the
Bulk Export yet
noahlearner | Sterling Sky
With
Explorer
Find
Your Way to Explore
we can see more:
the Data that work for you!
noahlearner | Sterling Sky
WithFind
Explorer
canto
see our
data:
Yourwe
Way
Explore
•Brand
vs
Non
Brand
the Data that work for you!
noahlearner | Sterling Sky
With Explorer we can see our data:
Find
Your
Way
to
Explore
•Brand vs Non Brand
the
Data
that
work
for
you!
•Funnel stage (top, middle, bottom)
noahlearner | Sterling Sky
With Explorer we can see our data:
•Brand
vs
Non
Brand
Find Your Way to Explore
•Funnel
stage
(top,
middle,
bottom)
the Data that work for you!
•Is GBP URL (url has GBP related UTMS)
noahlearner | Sterling Sky
With Explorer we can see our data:
•Brand vs Non Brand
Findstage
Your(top,
Way
to Explore
•Funnel
middle,
bottom)
•Is GBP
(urlthat
has GBP
related
UTMS)
theURL
Data
work for
you!
•Segment our site by URL patterns
noahlearner | Sterling Sky
Find Your Way to Explore
the Data that work for you!
noahlearner | Sterling Sky
With Explorer we can see:
•Brand vs Non Brand
•Funnel stage (top, middle, bottom)
•Is GBP
URL
(url
has
GBP
related
UTMS)
Find Your Way to Explore
•Segment
our
site
by
URL
patterns
the Data that work for you!
•Which Directories, pages, + queries are
driving change on our sites.
noahlearner | Sterling Sky
Find Your Way to Explore
the Data that work for you!
noahlearner | Sterling Sky
noahlearner | Sterling Sky
noahlearner | Sterling Sky
noahlearner | Sterling Sky
Your Way
tomore
Explore
It’sFind
not about
having
data, it’s
about
having
more
ways
toyou!
see it.
the Data
that
work
for
noahlearner | Sterling Sky
The Best data viz
book I know
noahlearner | Sterling Sky
How we think
noahlearner | Sterling Sky
Critical thinking
noahlearner | Sterling Sky
Use a decision
making process
noahlearner | Sterling Sky
Problem Solving 101, Ken Watanabe
noahlearner | Sterling Sky
The Two Octobers way
noahlearner | Sterling Sky
Define the problem
noahlearner | Sterling Sky
Look for 2nd + 3rd order
consequences
noahlearner | Sterling Sky
https://fs.blog/first-principles/
noahlearner | Sterling Sky
Gather + explore data
noahlearner | Sterling Sky
Extract insights
noahlearner | Sterling Sky
Form hypothesis
noahlearner | Sterling Sky
Execute test
noahlearner | Sterling Sky
Track + continuously
improve
noahlearner | Sterling Sky
Learn about +
remove bias
https://thedecisionlab.com/biases-index
noahlearner | Sterling Sky
Learn to prioritize
noahlearner | Sterling Sky
What should we do
and when should we
do it?
noahlearner | Sterling Sky
noahlearner | Sterling Sky
noahlearner | Sterling Sky
Find your Ikigai
noahlearner | Sterling Sky
See wider
noahlearner | Sterling Sky
Think bigger
noahlearner | Sterling Sky
noahlearner | Sterling Sky
noahlearner | Sterling Sky
本文档为站内渲染。原始文件本地路径：saas/source/seo-llm/raw-seo知识库-TXT整理-PPT-John喜欢和引用的那些海外演讲嘉宾PPTs-FoundConf-Noah-L-3a0883.txt（仅本地保留，不入库不部署）