Hi, I'm Daniel Waisberg,
Search Advocate at Google.
In this video,
I'll talk a little more about the Search Console bulk data export.
In my previous video,
I discussed how to set it up,
so if you missed it,
make sure to check it out.
Today, I'll go through the data available in Google BigQuery
after you successfully set up a bulk data export.
I'll explain the table schema,
and I'll show a simple query
to help you pull and analyze your Search Console data.
You need a basic understanding of BigQuery and SQL
to use this feature.
It's not easy-peasy,
but you can do it.
♪ [music] ♪
Bulk data exports include performance data.
It shows important metrics
about how your site performs in Google Search,
including what search queries show your site,
your page's click-through rate,
and in which countries you're performing best.
All this over time.
The data available through bulk exports
is very similar to the Search Analytics API,
but potentially much larger,
depending on your site.
Let's take a look at the tables exported
and discuss the fields available
in each of them.
[electronic beep]
The export contains partition tables.
The partitions are dates.
For each day, you'll get an update to the tables.
This will be very handy when optimizing your queries
because querying the entire table every day
is very inefficient.
I'll talk more about it later.
The first table you see is the
It's a record of what data was saved for that day.
Failed exports are not recorded
because, well, if we can't update the table,
how can we record that?
The second table is the
This data is aggregated by property.
For example, if two pages show up
in the same Google search result,
you would see only one impression here.
Also, for position,
when aggregating by property,
the topmost position of your property in search results is reported.
In this table, you'll see the date,
the search query,
a Boolean value showing if the query is anonymized,
the country,
the search type,
and the device.
For the combination of all of them,
you'll see how many impressions,
clicks,
and the sum top position.
This last
is the sum of the topmost position of the site in the search results
where "0" is the top position in the results page.
The third table,
searchdata_url_impression
is all your performance data aggregated by URL
rather than by site.
That means, for example,
that if two pages show up
in the same Google search result,
you would see two impressions,
one for each page.
As you can see,
this table is the largest of the three.
In addition to the fields in the previous table,
it includes the URL,
a Boolean value
showing whether the URL is anonymized for Discover
to protect user privacy,
and several Boolean fields
showing if the URL was shown
with a specific search appearance.
All the fields starting with
such as
is_events,
and so on.
It's a lot of data.
Make sure to check the links in the description
to learn more about the data schema
and field details.
The challenge of dealing with large datasets
is that it can get very slow and expensive
if you're not careful.
Let me go through some SQL tips and best practices
when querying the data.
[electronic beep]
There are three very important best practices
when querying Search Console data on BigQuery.
Let me go through a query to highlight them.
[electronic beep]
First, always use aggregation functions.
Table rows are not guaranteed to be consolidated by date,
URL, site,
or any combination of keys.
For example, you might have a query
that appears more than once for the same day,
so you would need to group by query
to make sure all the clicks and impressions are counted.
In this case,
we're grouping by both URLs and queries.
Second, try to limit queries by date
whenever possible.
This will speed up requests
and save processing costs.
You can do that by using a
to limit the date range in the date partition table.
In this case,
we're limiting it to the last two weeks.
Third, filter out
which will help decrease the size of the data request.
You can do that
by pulling data only for rows
where the
[electronic beep]
That's it.
Wasn't so scary, right?
[gasps]
Now that you have access to so much data,
maybe it's a good time to learn
or refresh your SQL skills.
You can do some cool analysis using this data,
like cluster analysis, machine learning,
and other exciting things you learned in Stats courses.
Up and to the right,
and stay tuned.
♪ [music] ♪