Google BigQuery Extract
Overview
Integrating seoClarity into BigQuery allows for data to be connected to additional data sources such as Looker Studio (formerly Google Data Studio), Tableau, and Domo among others.
Requirements
A Google BigQuery account is required to integrate data into and admin access to a seoClarity domain profile.
Google BigQuery Integration
2) To create the Service account, define the following options:
Service account: Select New service account from the dropdown.
Service account name: Enter a name and description for the account.
Service account ID: This will populate automatically based on the service account name and Bigquery account. Nothing needs to be entered manually for this field.
Click "Create and Continue".
3) Role: Select BigQuery Admin from the dropdown. BigQuery Admin allows for the ability to manage resources in the Project.
4) Navigate to the IAM & Admin > Service Account section. Select the account you are setting up and click on the three dots "Manage Keys".
5) Select Add Key> Create New Key.
6) Key type: Select the P12 radio button option. Once the fields are input, select Create and the P12 credential file will be generated. Learn More
7) Navigate to the IAM & Admin section. From IAM Permissions tab, select the edit (pencil) icon in the Inheritance column. Select the Add Another Role button and choose BigQuery Data Editor.
Data Editor allows for the ability to create a data table within the Project.
8) Once Data Editor has been selected, Select the Add Another Role button once again and choose BigQuery Job User. Select the Save button. Job user allows for the ability to check the existence of all jobs, enumerate their own jobs and cancel their own jobs.
9) Once the service account has been created and both Admin and Data Editor roles are set, let us know via support@seoclarity.net by providing the service account (email) name, P12 file, project name, and dataset name to confirm the integration.
Partitioned Data Delivery
Once setup has been confirmed, seoClarity will verify access and create a partitioned (by date) table. This strategy results in better performance and lower query costs for a number of use cases. The partitioning by date option cannot be changed once setup. If you'd like to have a monthly partition instead, you can request the same before the integration.
Ranking data will be updated in the morning at the same frequency as the profile is set up for: daily, weekly or biweekly. By default, the provided ranking data includes the top 10 results and can be adjusted by request. The provided ranking data is based on the list of domains provided at the time of integration, where additional domains can be added by request.
seoClarityRanking Data - Master list of fields
| The following fields represent the complete data set scraped from the search engine results page. | ||
| Field | Type | Description |
| Keyword | text | Keyword being rank checked |
| Search Engine | text | Search engine on which rankings were checked |
| Device | text | Device used for rank checking - Desktop or Mobile |
| Country | text | Country for which rankings were checked |
| Location | text | Specific Location (City / State/ Zip) for which rankings were checked) |
| Search Volume | long int | Avg Search Volume for the keyword |
| CPC | int | Avg Cost per click for the keyword |
| Rank | int | Rank position (typically from 1 to 100) |
| Sub-rank | text | Rank position of listings WITHIN a SERP feature (local, news, video, image etc.). A value of 3-1 indicates the SERP feature is in Rank position 3 and the Sub-Rank Domain and URL (below) are in the first position WITHIN that feature. 3-2 indicates second listing within the feature and so on |
| Sub-rank Domain | text | Domain that is ranking within a specific SERP feature |
| Sub-rank Url | text | URL found ranking within the SERP feature |
| Sub-rank Title | text | If available (for Shopping, Local Listing, Video etc), it will represent the title found for the listing within the SERP feature |
| url | text | Main URL for the result or SERP feature |
| domain | text | Main Domain found ranking for the result or SERP feature |
| type | text | Indicates type of SERP listing - Organic, Image, Video, etc |
| Answer Box Present | boolean | Yes / No field indicating presence of Answer Box / featured snippets |
| Local Listings Present | boolean | Yes / No field indicating presence of Local Pack |
| People Also Ask Present | boolean | Yes / No field indicating presence of People Also Ask |
| Images Present | boolean | Yes / No field indicating presence of Image carousel |
| News Present | boolean | Yes / No field indicating presence of News pack |
| Video present | boolean | Indicates if the keyword triggers a video result |
| Date of rank check | date | yyyy-mm-dd format |
| URL Feature - Price | boolean | indicates presence of Price structured data for URL |
| URL Feature - FAQ | boolean | indicates presence of FAQ structured data for URL |
| URL Feature - Stock | boolean | indicates presence of Stock structured data for URL |
| URL Feature - Star | boolean | indicates presence of Star Ratings structured data for URL |
| Tags | text | Comma delimited list of tags |
Related Articles
Google Search Console Bulk Data Integration via BigQuery
Google Search Console Bulk Data Integration via BigQuery Google now allows Google Search Console (GSC) users to export all of their data via an API to BigQuery set up in Google Cloud Services (Google announcement). You can take the additional step of ...Google Trends
Google Trends Overview The Google Trends is a feature that allows for Google Trends data to be retrieved for up to 10 keywords per domain profile, per day. Keywords cannot be aggregated across domain profiles to support more than 10 per profile. ...Google Maps (enabled feature)
Google Maps Overview seoClarity's Google Maps sheds light into an aspect of local SEO, by providing performance metrics within Google's local business listings. Google's results are increasingly geo-localized and the inclusion of Google Local ...How to Export and Download Data
Downloading Data Exports Overview seoClarity Platform data can be exported from charts or tables via the download and export options. Most downloads are instantaneously generated from the time they are selected. Some features, like Rank Intelligence, ...Google Business Profile (enabled feature)
Google Business Profile Overview Google My Business, now known as Google Business Profile, is an enabled feature that uses the Local Clarity integration to view data for GBP. Google Business Profile Use Cases Optimize your Google Business Profile ...