Skip to main content
All CollectionsReports
Detailed BigQuery schema description
Detailed BigQuery schema description

Shows the all the fields and schema descriptions for our BigQuery integration/exports.

Nic Parry avatar
Written by Nic Parry
Updated over 2 months ago

This describes the tables that are synched from the Authoritas platform to the DataSets in your Big Query project.


In addition, we automatically create Big Query Views of these tables which you will see in your DataSets. These are not described in detail below as you can see the query when you click on the View in Big Query.

These views have been specifically created to support the automation of a range of Authoritas Looker Data Studio reports for SEO. Please contact the support team if you would like to use or copy and customise any of our standard reports.

Table: analytics_page_statistics

Legacy Google Universal Analytics data showing statistics per page - similar to the Landing Page dimension from Google Analytics.

Field

Description

match_type

EQUALS / CONTAINS_SOURCE, differentiates between variations of sources (e.g. GOOGLE_US, GOOGLE_UK) or grouped together (e.g. GOOGLE)

ga_medium

Category of traffic (e.g. CPC, organic)

ga_source

Traffic source (e.g. GOOGLE, BING)

ga_social_network

Social network source if applicable

goal_number

Goal conversion count

stat_type

Analytics metric type (VISITS/DISTINCT_SEARCH_PHRASES/DISTINCT_LANDING_PAGES/COMPLETIONS/TRANSACTIONS/TOTAL_VALUE/PAGE_VALUE)

mobile

Boolean indicating if traffic is from mobile devices

date

Date of the analytics data

page

URL of the page

long_value

Numerical value for the stat_type

bounces

Number of single-page sessions

time_on_site

Session duration in seconds

visitors

Count of unique visitors

Table: analytics_page_statistics_ga4

Google Analytics 4 data showing statistics per page - similar to the Landing Page dimension from Google Analytics.

N/B: analytics_page_statistics will eventually be deprecated as it uses Google Univeral Analytics ('GA3') and Google is sunsetting this from 1 July 2023 and from 1 July 2024 for Google Analytics Premium users. So please ensure you use the table with the 'ga4' suffix.

Field

Description

match_type

EQUALS / CONTAINS_SOURCE, this differentiates between variations of sources (e.g. GOOGLE_US, GOOGLE_UK) or grouped together (e.g. GOOGLE)

ga_medium

Category of stat_type (e.g. CPC, organic)

ga_source

Specific source of stat_type (e.g. GOOGLE, BING)

ga_social_network

Social network source

goal_number

Goal conversion count

stat_type

One of the following values: VISITS / DISTINCT_SEARCH_PHRASES / DISTINCT_LANDING_PAGES / COMPLETIONS / TRANSACTIONS / TOTAL_VALUE / PAGE_VALUE

mobile

Is the source of stat_type mobile

date

Date of stat_type

page

The URL receiving the stat_type

long_value

Specific value of stat_type

bounces

Number of bounces on page

time_on_site

Time on site in seconds

visitors

Unique visitor count

Table: analytics_snapshot_statistics_ga4

Google Analytics 4 data showing general statistics, similar to the Summary Channel view from Google Analytics.

N/B: analytics_page_statistics will eventually be deprecated as it uses Google Univeral Analytics ('GA3') and Google is sunsetting this from 1 July 2023 and from 1 July 2024 for Google Analytics Premium users. So please ensure you use the table with the 'ga4' suffix.

Field

Description

match_type

EQUALS / CONTAINS_SOURCE, this differentiates between variations of sources (e.g. GOOGLE_US, GOOGLE_UK) or grouped together (e.g. GOOGLE)

ga_medium

Category of stat_type (e.g. CPC, organic)

ga_source

Specific source of stat_type (e.g. GOOGLE, BING)

ga_social_network

Social network source

goal_number

Goal conversion count

stat_type

One of the following values: VISITS / DISTINCT_SEARCH_PHRASES / DISTINCT_LANDING_PAGES / COMPLETIONS / TRANSACTIONS / TOTAL_VALUE / PAGE_VALUE

mobile

Is the source of stat_type mobile

date

Date of stat_type

long_value

Specific value of stat_type

bounces

Number of bounces on page

time_on_site

Time on site in seconds

visitors

Unique visitor count

Table: analytics_website_statistics_ga4

Google Analytics 4 data showing statistics per website, similar to the Referral Traffic view Google Analytics.

N/B: analytics_website_statistics will eventually be deprecated as it uses Google Univeral Analytics ('GA3') and Google is sunsetting this from 1 July 2023 and from 1 July 2024 for Google Analytics Premium users. So please ensure you use the table with the 'ga4' suffix.

Field

Description

match_type

EQUALS / CONTAINS_SOURCE, this differentiates between variations of sources (e.g. GOOGLE_US, GOOGLE_UK) or grouped together (e.g. GOOGLE)

ga_medium

Category of stat_type (e.g. CPC, organic)

ga_source

Specific source of stat_type (e.g. GOOGLE, BING)

ga_social_network

Social network source

goal_number

Goal conversion count

stat_type

One of the following values: VISITS / DISTINCT_SEARCH_PHRASES / DISTINCT_LANDING_PAGES / COMPLETIONS / TRANSACTIONS / TOTAL_VALUE / PAGE_VALUE

mobile

Is the source of stat_type mobile

date

Date of stat_type

website

The website receiving the stat_type

long_value

Specific value of stat_type

Table: ctr

Contains all the CTR models for the project. Having a row(ctr) per ranking position and config.

Field

Description

rank

Ranking position for the applied CTR

ctr

CTR percentage

config_name

Configuration name

config_id

Configuration ID

Table: keywords_ctr

Joining table to apply CTRs to specific keyword ranks.

Field

Description

ctr_id

config_id from ctr table

keyword_id

keyword_id from keywords_ranking table

Table: keywords_metadata

Additional keyword information and attributes.

Field

Description

keyword_id

Unique identifier for the keyword

keyword

The keyword phrase

frequency

Update frequency for keyword tracking

Table: keywords_rankings

All keyword ranking data, with a row per ranking position. This table also includes null ranks for tracked domains that are not ranking for the keyword.

Field

Description

date

Date of the rank check

rank

ranking position for the page/URL

domain_id

For joining extra data

domain

Domain ranking at this position

keyword_id

For joining extra data (ctr, tags etc.)

keyword

Actual keyword string

search_volume

Monthly search volume for the keyword

cpc

CPC for the keyword

ranked_url

URL ranking at this rank position

ranked_flags

Multiple values representing the different result types this ranked_url has for this keyword

any_flags

Multiple values representing all the present result types that this keyword has

ranking_configuration_name

The configuration name set up on the platform, for most configs this represents a specific Search Engine/Location combination

transactional_intent_score

User Intent Transactional score for the keyword

navigational_intent_score

User Intent Navigational score for the keyword

informational_intent_score

User Informational Intent score for the keyword

research_intent_score

User Research Intent score for the keyword

is_local_intent

Does this keyword have Local user intent

is_transactional_intent_dominant

Does this keyword have dominant transactional user intent

is_navigational_intent_dominant

Does this keyword have navigational transactional user intent

is_research_intent_dominant

Does this keyword have research transactional user intent

is_informational_intent_dominant

Does this keyword have informational transactional user intent

Table: keywords_tags

Field

Description

keyword

Actual keyword string

keyword_id

Used to join to other tables

tags

Multi value field with all tags for the keyword

Table: link_data_websites

Contains all backlinks for all specifed domains (primary domain and competitors) collected for the project. Backlink data is sourced from Majestic using the Majestic Fresh Index. We collect a maximum of 3 backlinks from any site. Data may be collected at different intervals for different domains (e.g. monthly or bi-monthly) depending upon platform settings.

Field

Description

website

The website that was analysed

index_date

Date of the analysis

influence

The influence of the website, which is a proxy for its quality of reputation

relevance

The relevance of the website to the market defining keywords in the project

linking_pages_count

The number of pages linking to this website

site_type

Website classification

intersection_domains

Intersected domains found in link data indexes in a project

pages

Pages with backlinks

url

The URL of the page

title

The title of the page

outbound_links_from_page

Outbound link count

has_keywords_in_target_link

Are the market defining keywords in the target link

target_links

link

The URL the backlink is pointing to.

anchor_texts

The anchor text used for the backlink.

Table: mapped_pages

Details which URLs/pages have been mapped to target specific keywords

Field

Description

keyword_id

For joining to other tables

keyword

Keyword string

mapped_page

URL/page that is mapped to the keyword

Table: market_winners

This is a summary table, snapshotting tracked domains and their position in the market.

Field

Description

date

Date of the summary

domain

Tracked domain

ranking_configuration_name

The configuration name set up on the platform, for most configs this represents a specific Search Engine/Location combination

top_3

How many keywords the domain is ranking for in positions 1 to 3

top_10

How many keywords the domain is ranking for in positions 1 to 10

top_30

How many keywords the domain is ranking for in positions 1 to 30

estimated_media_value

Estimated value based on CPC and CTR

percentage_of_total_media_value

Estimated share of total media value

Table: page_structure

Detailed page analysis and structure information from crawled pages.

Field

Description

user_agent

Browser user agent used for crawling

url

URL of the analyzed page

keyword

Associated keyword if applicable

ranking_configuration_name

Configuration used for ranking analysis

date

Date of the page analysis

response

Raw response data

schema

Schema markup found on the page

response_code

HTTP response code

response_headers

HTTP response headers

meta_robots

Robots meta tag directives

meta_title

Page meta title

meta_type

Meta type specification

meta_description

Page meta description

rel_canonical

Canonical URL if specified

page_type

Classified page type

final_url

Final URL after redirects

tags

Page classification tags

opengraph

Open Graph meta tags

twitter_card

Twitter Card meta tags

main_content

Main content area analysis

images

Array of image information

src

Image source URL

file_name

Image file name

html_tag

HTML tag containing the image

width

Specified image width

height

Specified image height

file_width

Actual image file width

file_height

Actual image file height

external_links

Array of external link information

title

Link title

url

Link URL

href_lang

Language targeting for the link

rel

Link relationship attributes

internal_links

Array of internal link information

title

Internal link title

url

Internal link URL

href_lang

Language targeting for internal link

rel

Internal link relationship attributes

h1

H1 heading content

h2

H2 heading content

h3

H3 heading content

h4

H4 heading content

h5

H5 heading content

h6

H6 heading content

paragraph_word_count

Word count in paragraphs

word_count

Total word count

video_count

Number of videos on page

paragraph_count

Number of paragraphs

text_block_count

Number of text blocks

whole_page

Complete page content

images

Additional image analysis

src

Image source URLs

Table: project_settings

This is a utility table, that's used by joining to other tables/views in order to assist in filtering data in Data Studio

Field

Description

primary_domain

The primary domain tracked in the project

competitor_domain

All other listed domains for the project


Table: ranking_configs

Configuration settings for ranking analysis.

Field

Description

ranking_configuration_id

Unique identifier for the configuration

ranking_configuration_name

Name of the configuration

is_active

Whether the configuration is currently active

is_default

Whether this is the default configuration

is_mobile

Whether this configuration is for mobile rankings

engine

Search engine being tracked

Table: search_analytics_pages

Google Search Console page performance data.

Field

Description

site_url

Website URL

page_id

Unique identifier for the page

page

Page URL

country

Country of the search origin

device

Device type (desktop, mobile, tablet)

clicks

Number of clicks from search results

impressions

Number of times the page appeared in search results

ctr

Click-through rate

position

Average search position

date

Date of the data

Table: search_volumes

Keyword search volume and cost data.

Field

Description

keyword_id

Unique identifier for the keyword

keyword

The keyword phrase

ranking_configuration_name

Associated ranking configuration

year

Year of the volume data

month

Month of the volume data

volume

Monthly search volume

cpc_usd

Cost per click in USD

cpc_local_currency

Cost per click in local currency

custom_volume

Custom-defined search volume

custom_cpc_usd

Custom-defined CPC in USD

custom_cpc_local_currency

Custom-defined CPC in local currency

impressions_volume

Volume based on impression data

Table: serps_data

Detailed search engine results page data.

Field

Description

keyword_id

Unique identifier for the keyword

keyword

The keyword phrase

keyword_deleted_at

Timestamp when keyword was deleted if applicable

search_volume

Monthly search volume

date

Date of SERP capture

ranking_configuration_name

Configuration used for the SERP analysis

ranking_configuration_deleted_at

Timestamp when configuration was deleted if applicable

total_results

Total number of search results

items

Array of organic search results

universal_rank

Position in universal search results

result_type

Type of search result

result_type_rank

Position within the result type

estimated_visits

Estimated visits based on position and CTR

page_number

SERP page number

domain

Domain of the result

subdomain

Subdomain of the result

url

URL of the result

title

Title of the result

description

Meta description or snippet

flags

Result attributes or features

visible

Whether the result is visible

paid_items

Array of paid search results

rank

Position in search results

url

URL of paid result

headline

Ad headline

description

Ad description

flags

Ad attributes or features

visible

Whether the ad is visible

row_num

Row number in results

Table: site_crawler_pages

Web crawler data per URL

Field

Description

domain

The domain that was crawled

crawl_date

Date of crawl

url

The URL/page

title

Title of the page

description

Description of the page

tier

Depth of crawl

keywords_count

Number of keywords found on the page

outbound_internal_links_count

Outbound internal link count

outbound_external_links_count

Outbound external link count

backlinks_count

Total links to this page

external_broken_links

Are there any broken external links

internal_broken_links

Are there any broken internal links

page_issues

Count of page issues

links

List of links on page

tier

Depth of crawl

url

URL linked to

http_code

HTTP response code of link

redirect_http_code

HTTP redirect code of link

View: analytics_page_statistics_ga4_monthly

Monthly aggregation of GA4 page statistics.

View: analytics_page_statistics_view

Customized view of page statistics with additional metrics.

View: backlinks

The same data as in link_data_websites with domain type (Primary or Competitor) joined. This allows for copying of Data Studio reports without having to modify any filters after copying.

View: competitive_domain_metrics

Analysis of competitor domain performance metrics.

View: competitors_rankings_view

Focused view of competitor ranking data.

View: content_audit

Content performance and audit metrics view.

View: keyword_rankings_flattened

This view flattens out the any_flags field of keywords_rankings which allows for aggregating metrics related to all result types in Data Studio. It also joins primary_domain to all rows to for use in Data Studio filters.

View: keywords_rankings_with_share

The same data as in keywords_rankings with a calculated visibility share metric.

View: keywords_rankings_joined_ctr

Keywords rankings joined with CTR data.

View: keywords_rankings_top_rank

Top ranking positions for keywords.

View: link_data_websites_monthly

Monthly aggregation of backlink data.

View: page_structure_joined_view

Page structure data with additional joined information.

View: page_structure_url_page_types

Page structure categorized by URL and page types.

View: per_month_keyword_rankings

This view only includes the most recent ranking data per month for each keyword. This allows for visualising ranking data over time for keywords with different rank check frequencies.

View: per_month_keyword_rankings_all

Comprehensive monthly rankings including all domains.

View: per_month_keyword_rankings_tags_primary_domain_only

Monthly rankings filtered to primary domain with tags.

View: per_month_keywords_rankings

Alternative monthly keyword rankings view.

View: per_month_keywords_rankings_joined_ctr

Monthly rankings with CTR data.

View: per_month_keywords_rankings_tags

Monthly rankings with keyword tags.

View: per_month_search_analytics_pages

Monthly search analytics for pages.

View: per_week_keywords_rankings

Weekly view of keyword rankings.

View: per_week_keywords_rankings_lag

Weekly rankings with lag comparison.

View: per_week_keywords_rankings_tags

Weekly rankings with keyword tags.

View: ranking_report

A summary view representing each domain present in keyword rankings showing each domains visibility over time.

View: search_analytics_pages_monthly

Monthly aggregation of search analytics data.

View: serps_data_organic_only

SERP data filtered to organic results only.

View: share_of_search

Market share of search visibility metrics.

View: site_crawler_pages_monthly

Monthly aggregation of crawler data.

View: universal_rankings_summary

Summary of universal search rankings.

View: universal_results_count

Count of universal search results by type.

Did this answer your question?