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 a week 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_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_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

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: 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: site_crawler_pages

Web crawler data per URL

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: 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: 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: 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: ranking_report

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

Did this answer your question?