BigQuery Help Guide

Follow these steps to connect your AccuRanker data with Google BigQuery.

Prerequisites

To connect BigQuery to AccuRanker, you need:

• A 20K keyword AccuRanker subscription or above.
• To contact AccuRanker customer support with a request to enable Big Query.
• A Google Cloud account with a Google BigQuery instance.
• Enabled billing on Google BigQuery.
• Permissions to assign specific roles for a google service account.

Step-by-Step Guide to Setting Up BigQuery Access

1. Create Service Account

Follow Google's Create a service account key documentation to set up service account key authentication.

2. Setup Service Account Permissions

We support three different permission scenarios to grant the necessary permissions to the service account. If you haven’t set up permissions before, read Google’s Manage access to service accounts documentation.

You can either grant access to the project or a single dataset.

Permissions Type	Role	Resource
BigQuery Admin Role	BigQuery Admin	Project or Dataset already created
BigQuery Data Owner and BigQuery Job User	BigQuery Data Owner BigQuery Job User	Project or Dataset already created
Custom IAM Roles	bigquery.tables.create bigquery.tables.get bigquery.tables.update bigquery.tables.updateData bigquery.tables.delete bigquery.jobs.create	Project or Dataset already created

Create Service Account Keys

To use the service account flow, you must provide the JSON file for your service account.

1. Create a New Key by selecting JSON format.
2. Click Create.
3. Download the JSON file to a secure location; this file is essential for connecting to BigQuery.

Finish BigQuery Configuration

1. Head to the domain you want to connect to BigQuery.
2. In the overview, click on “Integrations” where you can find the BigQuery integration.
3. Give the connection a name and specify a location for the dataset.
   
   • If you’re unsure about the location, you can read about the different locations here.
4. Upload the Service Account JSON key and click “Connect”.
   
   • If you have already created a dataset, ensure the dataset location matches the already created dataset. We will then test the connection and prompt you when the connection has been successfully tested.

(Optional) Backfill Historical Data

You can now go to your domain settings and start a backfill of your AccuRanker data if you need historical data.

---

How We Update BigQuery and Backfill Data

We ensure that our BigQuery data is updated daily to reflect the most recent changes. Every day, we append new data from the previous day. This approach ensures that all critical datasets remain current while preserving historical records for analysis.

Daily Update Process

The update process focuses on the following datasets:

All_ranks and Own_ranks

• These datasets are updated daily by appending new data.
• They maintain a comprehensive and up-to-date record of rankings.

Keyword_metadata and Competitor_metadata

• These datasets are augmented as necessary.
• New records are incorporated or existing ones are refreshed to ensure metadata remains accurate and complete.

---

Monthly Update Cycle

In addition to daily updates, the Search_volume and AI_search_volume datasets are managed on a monthly cycle.

• Update Schedule: On the 15th of each month, these tables are truncated and replaced with the latest data from the Google Keyword Planner (GKP) and updated search volume metrics.
• Purpose: This process ensures that the most accurate and recent insights are available.
• Historical Data: These datasets include backfilled data spanning the past four years, providing a robust historical perspective for trend analysis.

---

Backfill Data

After a successful connection, you can choose to backfill your data. Here's how it works:

• Scope: Decide how far back you want to backfill. You can backfill data all the way back to the date of your first rank.
• Restrictions: To avoid malicious behavior, you can only backfill once. If you need to backfill again, you must request this through customer support.

---

BigQuery Schema Guide

The naming convention for the tables will be

accuranker{schema_name}{our_internal_domain_slug}

The schema names are listed below and each schema can be downloaded in a JSON format.

• keyword_metadata
• competitor_metadata
• own_ranks
• all_ranks
• search_volume
• AI_search_volume

---

Table Descriptions

Table Name	Description
all_ranks	This table contains all information on keyword ranking, including dynamic rankings and competitors.
own_ranks	This table contains all information on keyword rankings that you track.
keyword_metadata	This table contains details of currently tracked keywords, including tags and domain-specific settings.
competitor_metadata	This table contains details of currently tracked competitors, including settings for competitors.
search_volume	This table contains the monthly search volume for each keyword. If the keyword has a location, it shows both location-specific and country-level search volumes. If no location is specified, the search volume is the same for both.
ai_search_volume	This table contains the monthly AI search volume for each keyword. It includes search_volume for a specific search type and search_volume_total for both search types combined.