Benchmarking Competitors in BigQuery

Integrating Third-Party Data with BigQuery to Benchmark Against Competitors

Benchmarking your SEO performance against competitors helps you understand your position within your industry and identify areas for improvement. By integrating third-party data with BigQuery, you can enrich your Google Search Console data, gain insights into competitor activity, and create a comprehensive competitor benchmark SEO analysis. In this section, we’ll cover how to bring third-party data into BigQuery, analyze it alongside your own SEO metrics, and visualize competitor comparisons in Looker Studio.

1. Why Integrate Third-Party Data for Competitor Benchmarking?

Competitor benchmarking allows you to:

• Understand Market Position: See where you stand compared to competitors in terms of traffic, keyword performance, and visibility.
• Identify Strengths and Weaknesses: Recognize which keywords or content types are outperforming and where you might be falling behind.
• Adapt SEO Strategy: Adjust your approach based on competitor trends, targeting areas that present growth opportunities.

With competitor insights, your SEO efforts become more strategic, focusing on areas that can improve your competitive standing.

2. Bringing Third-Party Data into BigQuery

Start by sourcing relevant third-party data from SEO tools like SEMrush, Ahrefs, or Moz. Many of these platforms offer data exports or API access to retrieve metrics like competitor keyword rankings, traffic estimates, and backlink profiles.

• Step 1: Export or Collect Competitor Data

  • Export competitor data from your third-party SEO tool. Look for metrics such as:
    • Keyword Rankings: Positions and estimated traffic for competitor keywords.
    • Domain Authority: Scores that indicate the strength of competitor domains.
    • Traffic Estimates: Monthly organic traffic estimates by keyword.
  • You can typically export data as a CSV or use API access to retrieve data directly.

• Step 2: Upload Data to BigQuery

  • To upload CSV data, go to BigQuery, select your project, and choose “Create Table.”
  • Import the CSV and define each column’s data type (e.g., date, string, integer).
  • Name the table based on the competitor data (e.g., competitor_keywords) to keep it organized and easy to reference.

• Step 3: Combine Data Sources for Comprehensive Analysis

  • If using an API, consider setting up an automated data pipeline (e.g., with Google Cloud Functions or Cloud Composer) to pull data into BigQuery on a regular basis.
  • Create separate tables for each competitor or data type (e.g., keyword rankings, traffic estimates) for a clear, structured data setup.

3. Analyzing Competitor Data with Your SEO Metrics in BigQuery

With competitor data in BigQuery, you can now combine it with your Google Search Console metrics for an in-depth competitor analysis.

• Step 1: Set Up a Comparison Query

  • Write SQL queries to compare your keyword performance against competitors. For example, combine your keyword rankings with competitor rankings:

    SELECT
      your_data.query AS your_keyword,
      your_data.position AS your_position,
      competitor_data.position AS competitor_position,
      competitor_data.domain AS competitor_domain,
      your_data.clicks AS your_clicks,
      competitor_data.traffic_estimate AS competitor_traffic
    FROM
      `your_project.your_dataset.your_keywords` AS your_data
    LEFT JOIN
      `your_project.your_dataset.competitor_keywords` AS competitor_data
    ON
      your_data.query = competitor_data.query
    ORDER BY
      your_position, competitor_position;

  • This query compares your keyword positions and clicks against competitor positions and traffic estimates, highlighting areas where they might outrank you.

• Step 2: Identify Performance Gaps

  • Use conditional logic in SQL to highlight gaps. For instance, flag keywords where your position is lower than the competitor’s:

    SELECT
      query,
      your_position,
      competitor_position,
      CASE
        WHEN your_position > competitor_position THEN 'Competitor Leading'
        ELSE 'You Leading'
      END AS position_comparison
    FROM
      `your_project.your_dataset.keyword_comparison`;

  • This logic helps quickly spot keywords where competitors have an edge, guiding you to focus on improving those areas.

• Step 3: Calculate Share of Voice

  • Calculate your share of voice for high-value keywords by dividing your estimated traffic by total traffic (you + competitors):

    SELECT
      query,
      (your_data.clicks / (your_data.clicks + competitor_data.traffic_estimate)) * 100 AS share_of_voice
    FROM
      `your_project.your_dataset.keyword_comparison`
    WHERE
      query IN ('target_keyword1', 'target_keyword2'); -- Replace with specific keywords

  • Share of voice gives a metric to understand your visibility compared to competitors.

4. Visualizing Competitor Benchmarking Insights in Looker Studio

Connecting your BigQuery tables with Looker Studio allows you to visualize and communicate competitor benchmarking insights effectively.

• Step 1: Connect BigQuery Competitor Data to Looker Studio

  • In Looker Studio, click “Add Data” and select BigQuery. Link the tables or views you created, including both your metrics and competitor metrics.

• Step 2: Create Comparison Visualizations

  • Set up side-by-side bar charts or tables showing your rankings vs. competitor rankings by keyword.
  • Add scorecards or gauges to display metrics like share of voice or domain authority.

• Step 3: Use Conditional Formatting to Highlight Gaps

  • Apply color coding to keywords where competitors are ranking higher, helping you focus on areas for improvement.
  • Set up filters to focus on specific competitors, devices, or keyword groups for a more targeted analysis.

Example: A table showing top 10 keywords with columns for your position, competitor position, and traffic estimate helps highlight where competitors are gaining more visibility.

5. Using Competitor Insights to Inform SEO Strategy

Once you have a clear view of competitor performance, use these insights to inform your SEO decisions.

• Target High-Value Keywords: Prioritize optimization for keywords where competitors have higher visibility and strong traffic estimates.
• Strengthen Content for Gaps: For keywords where competitors consistently outrank you, consider updating content to improve relevance and ranking potential.
• Benchmark Progress Regularly: Use your Looker Studio dashboard to track changes over time, measuring improvements in share of voice or position relative to competitors.

Example: If a competitor has a higher share of voice for a key industry term, focus on content and technical improvements to enhance your position.

Summary

Integrating third-party data with BigQuery allows you to benchmark your SEO performance effectively, offering a comprehensive view of how you stack up against competitors. By combining GSC metrics with competitor data in Looker Studio, you can visualize areas of opportunity and create a data-driven SEO strategy focused on improving your competitive position. This approach enables ongoing tracking and refinement, keeping your strategy aligned with the market landscape.