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.