Lucen

Google Search Console data reference

Analytics
6 tables
95 columns

Lucen syncs your Google Search Console data (Analytics) to your warehouse and models it into the tables below. You connect Google Search Console with one OAuth sign-in. By default your data refreshes every day at 07:00 Buenos Aires time (UTC-3). Your plan sets how many times a day it can sync, and you can choose the times, see pricing.

Your plan sets how much history the first sync loads, see pricing.

Send Google Search Console data to

Tables

gold_search_console_country

Grain
country_code
Use for
Geo

gold_search_console_device

Grain
device
Use for
Mobile / desktop / tablet

gold_search_console_page

Grain
page_url
Use for
Pages and hostnames

gold_search_console_page_query

Grain
page_url, query
Use for
Queries on a page

gold_search_console_query

Grain
query
Use for
Disclosed search terms

gold_search_console_traffic

Grain
none
Use for
Site clicks, CTR, position trends

Columns

Column names match your warehouse exactly. Descriptions come from dbt.

gold_search_console_country columns

IDNameTypeDescription
site_urlSite urlSTRINGExact Search Console property identifier. Keep overlapping Domain and URL-prefix properties separate.
dateDateDATEFinalized performance date in Google's Pacific calendar. Missing rows alone do not prove zero activity.
search_typeSearch typeSTRINGSearch surface; web only in this version. Keep in grouping and join keys.
country_codeCountry codeSTRINGGoogle three-letter code for the search country, not residence or conversion location.
countryCountrySTRINGEnglish country name derived from country_code; null when the code has no known name.
clicksClicksFLOAT64Clicks for this country, using byProperty aggregation. Not a repeated site-total column. Sum within this model before joins; never add across reports.
impressionsImpressionsFLOAT64Impressions for this country, using byProperty aggregation. Not a repeated site-total column. Sum within this model before joins; never add across reports.
ctrCtrFLOAT64Clicks / impressions, as a fraction. For groups use SUM(clicks) / NULLIF(SUM(impressions), 0); never SUM or AVG this column.
average_positionAverage positionFLOAT64Average position at this model's aggregation basis. For groups use SUM(position_impression_sum) / NULLIF(SUM(impressions), 0); never SUM or unweighted AVG.
position_impression_sumPosition impression sumFLOAT64Position numerator: average_position * impressions. Sum within this model, then divide by summed impressions to calculate group position.
aggregation_typeAggregation typeSTRINGGoogle aggregation basis: byProperty. Page and property metrics are not interchangeable, even for identical dates.
client_idClient idSTRINGTenant identifier. Keep in grouping and join keys; never combine tenants.
data_updated_atData updated atTIMESTAMPTimezone-aware extraction instant of the selected snapshot. Use MAX(date) for the latest performance date.

gold_search_console_device columns

IDNameTypeDescription
site_urlSite urlSTRINGExact Search Console property identifier. Keep overlapping Domain and URL-prefix properties separate.
dateDateDATEFinalized performance date in Google's Pacific calendar. Missing rows alone do not prove zero activity.
search_typeSearch typeSTRINGSearch surface; web only in this version. Keep in grouping and join keys.
deviceDeviceSTRINGGoogle device category. An independent breakdown; joining other facts cannot infer query or country attribution.
clicksClicksFLOAT64Clicks for this device, using byProperty aggregation. Not a repeated site-total column. Sum within this model before joins; never add across reports.
impressionsImpressionsFLOAT64Impressions for this device, using byProperty aggregation. Not a repeated site-total column. Sum within this model before joins; never add across reports.
ctrCtrFLOAT64Clicks / impressions, as a fraction. For groups use SUM(clicks) / NULLIF(SUM(impressions), 0); never SUM or AVG this column.
average_positionAverage positionFLOAT64Average position at this model's aggregation basis. For groups use SUM(position_impression_sum) / NULLIF(SUM(impressions), 0); never SUM or unweighted AVG.
position_impression_sumPosition impression sumFLOAT64Position numerator: average_position * impressions. Sum within this model, then divide by summed impressions to calculate group position.
aggregation_typeAggregation typeSTRINGGoogle aggregation basis: byProperty. Page and property metrics are not interchangeable, even for identical dates.
client_idClient idSTRINGTenant identifier. Keep in grouping and join keys; never combine tenants.
data_updated_atData updated atTIMESTAMPTimezone-aware extraction instant of the selected snapshot. Use MAX(date) for the latest performance date.

gold_search_console_page columns

21

gold_search_console_page_query columns

24

gold_search_console_query columns

IDNameTypeDescription
site_urlSite urlSTRINGExact Search Console property identifier. Keep overlapping Domain and URL-prefix properties separate.
dateDateDATEFinalized performance date in Google's Pacific calendar. Missing rows alone do not prove zero activity.
search_typeSearch typeSTRINGSearch surface; web only in this version. Keep in grouping and join keys.
queryQuerySTRINGReturned search term, part of the row grain. Omitted queries are absent, not zero-valued rows.
query_word_countQuery word countINT64Number of whitespace-separated words in the returned search query.
brand_classificationBrand classificationSTRINGConfigured classification: branded, non_branded or unclassified. Without brand terms, brand share is unavailable; otherwise it covers disclosed queries only.
clicksClicksFLOAT64Clicks for this returned query, using byProperty aggregation. Not a repeated site-total column. Sum within this model before joins; never add across reports.
impressionsImpressionsFLOAT64Impressions for this returned query, using byProperty aggregation. Not a repeated site- total column. Sum within this model before joins; never add across reports.
ctrCtrFLOAT64Clicks / impressions, as a fraction. For groups use SUM(clicks) / NULLIF(SUM(impressions), 0); never SUM or AVG this column.
average_positionAverage positionFLOAT64Average position at this model's aggregation basis. For groups use SUM(position_impression_sum) / NULLIF(SUM(impressions), 0); never SUM or unweighted AVG.
position_impression_sumPosition impression sumFLOAT64Position numerator: average_position * impressions. Sum within this model, then divide by summed impressions to calculate group position.
aggregation_typeAggregation typeSTRINGGoogle aggregation basis: byProperty. Page and property metrics are not interchangeable, even for identical dates.
client_idClient idSTRINGTenant identifier. Keep in grouping and join keys; never combine tenants.
data_updated_atData updated atTIMESTAMPTimezone-aware extraction instant of the selected snapshot. Use MAX(date) for the latest performance date.

gold_search_console_traffic columns

IDNameTypeDescription
site_urlSite urlSTRINGExact Search Console property identifier. Keep overlapping Domain and URL-prefix properties separate.
dateDateDATEFinalized performance date in Google's Pacific calendar. Missing rows alone do not prove zero activity.
search_typeSearch typeSTRINGSearch surface; web only in this version. Keep in grouping and join keys.
clicksClicksFLOAT64Clicks for this site, using byProperty aggregation. Headline site total. Sum within this model before joins; never add across reports.
impressionsImpressionsFLOAT64Impressions for this site, using byProperty aggregation. Headline site total. Sum within this model before joins; never add across reports.
ctrCtrFLOAT64Clicks / impressions, as a fraction. For groups use SUM(clicks) / NULLIF(SUM(impressions), 0); never SUM or AVG this column.
average_positionAverage positionFLOAT64Average position at this model's aggregation basis. For groups use SUM(position_impression_sum) / NULLIF(SUM(impressions), 0); never SUM or unweighted AVG.
position_impression_sumPosition impression sumFLOAT64Position numerator: average_position * impressions. Sum within this model, then divide by summed impressions to calculate group position.
aggregation_typeAggregation typeSTRINGGoogle aggregation basis: byProperty. Page and property metrics are not interchangeable, even for identical dates.
client_idClient idSTRINGTenant identifier. Keep in grouping and join keys; never combine tenants.
data_updated_atData updated atTIMESTAMPTimezone-aware extraction instant of the selected snapshot. Use MAX(date) for the latest performance date.

Typical questions

Prompt to try
Site clicks / impressions

gold_search_console_traffic

Prompt to try
Top pages

gold_search_console_page

Prompt to try
Queries for a page

gold_search_console_page_query

Starter queries

sql
SELECT date, site_url, search_type, clicks, impressions
FROM `your-project.your_org_gold.gold_search_console_traffic`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
ORDER BY date DESC
LIMIT 100;
sql
SELECT date, site_url, search_type, page_url, hostname
FROM `your-project.your_org_gold.gold_search_console_page`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
ORDER BY date DESC
LIMIT 100;
sql
SELECT date, site_url, search_type, query, query_word_count
FROM `your-project.your_org_gold.gold_search_console_query`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
ORDER BY date DESC
LIMIT 100;
sql
SELECT date, site_url, search_type, device, clicks
FROM `your-project.your_org_gold.gold_search_console_device`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
ORDER BY date DESC
LIMIT 100;

Integration page