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.

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.