As Google evolves towards a new standard with advanced LLMs and Retrieval-Augmented Generation, understanding how search engines rank your website becomes even more critical for maintaining a strong online presence and ensuring that LLMs consider your site when retrieving information. While GenAI technologies continue to evolve, it is natural to ask if they will eventually replace traditional search engines as the primary means of finding information online. This raises the question — how can you ensure your website remains visible and relevant in a world where LLMs and RAG systems are the new gatekeepers of digital content?

At Astrafy, we are one step forward. This accelerator lies in taking a proactive approach to search engine optimization and website analytics. By integrating Google Search Console (GSC) data and Google Analytics 4 (GA4) data, you can gain a comprehensive view of your site’s performance, track key performance indicators (KPIs), and make data-driven decisions to enhance your SEO strategy.

This diagram illustrates a process for enhancing SEO by integrating Google Search Console (GSC) and Google Analytics 4 (GA4) data through a dbt pipeline. It outlines a sequence starting from data integration, moving through analysis of SEO performance, traffic, user behavior, and conversion rates, leading to actionable insights. These insights drive data-driven decisions that enhance SEO strategies, ensuring the website remains competitive in search rankings.


This article will guide you through our approach to merging these powerful tools using a dbt transformation pipeline. It will help you unlock actionable insights and stay ahead of the curve as the search landscape continues to shift.

Business Value

Understanding the mechanics behind search engine results and how they may evolve with the rise of LLMs is essential for achieving your desired outcomes and ensuring your website remains a valuable resource for your target audience.

Our competitive advantage at Astrafy lies in our comprehensive coverage of both critical scopes of SEO analytics: performance evaluation and understanding the reasons behind rankings.

Traditional tools often fail to bridge the gap between these aspects, but our SEO Analytics Package excels by integrating data from both GSC and GA4. This integration allows us to diagnose issues, identify causes, and provide tailored solutions, ensuring that your website maintains strong visibility and relevance.

By analyzing the “why” and the “how” of search engine rankings, we offer a full organic search KPI analysis stack that enables you to make informed decisions and stay ahead in the rapidly evolving digital landscape.

The Importance of SEO in our LLM-Reigned lived

When a user submits a search query, these AI models analyze the intent and context behind the request and then scour the internet to identify the most relevant and engaging content to present to the user. This is where SEO comes into play. By optimizing web pages with targeted keywords, meta tags, and structured data, content creators can increase the likelihood of their information being recognized and prioritized by LLMs. However, the true power of SEO lies in its ability to foster user engagement.

This flowchart depicts how a user's search query is processed by an AI model to find relevant content online. It highlights the SEO scope, emphasizing the use of targeted keywords and meta tags to boost content visibility for AI recognition. The objective is to increase the chances of the webpage being presented back to the user.

LLMs are designed to prioritize content that not only matches the search query but also provides a seamless and engaging user experience. Websites that feature well-written, informative, and visually appealing content are more likely to capture the attention of LLMs and be selected as the go-to source for the user’s query. By incorporating SEO strategies that enhance user engagement, such as creating interactive elements, optimizing page load times, and providing a mobile-friendly experience, content creators can signal to LLMs that their website is a trusted and valuable resource.

In the LLM-driven landscape of the digital world, the ability to attract and retain user engagement is paramount. By aligning your SEO efforts with the preferences and behaviors of these advanced AI systems, you can unlock the full potential of your online presence and position your content as the go-to source for your target audience. Embracing SEO as a key driver of user engagement is essential for success in the age of LLMs.

Why Integrate Google Search Console with GA4

Integrating Google Search Console with GA4 allows you to combine search engine data with user behavior analytics, providing a holistic view of your website’s performance. This integration enables you to:

  • Track Comprehensive KPIs: Monitor key performance indicators such as impressions, clicks, average position, click-through rate (CTR), user sessions, session duration, and bounce rate.

  • Enhance Data Granularity: Gain detailed insights into how users interact with your site after finding it through search engines, allowing for more precise optimization strategies.

  • Identify and Address Issues: Detect technical issues and content gaps that might affect your search rankings and user experience and implement targeted improvements.

KPIs to Track

Performance KPIs

  • Branded vs. Non-Branded Traffic: Differentiates between brand awareness and generic search visibility.

  • Impressions: Number of times your site appears in search results.

  • Average Position: Average rank of your site in SERPs.

  • Keyword Rankings: Position tracking for targeted keywords.

Engagement KPIs

  • Clicks: Number of users who clicked on your site from search results.

  • Click Through Rate (CTR): Percentage of impressions that resulted in clicks.

  • Conversion Rate: Percentage of visitors who complete a desired action.

  • Session Duration: Average time users spend on your site.

Granularity of Data Analysis

For a thorough analysis, it’s essential to examine data at various granular levels:

  • Page-Level Analysis: Track KPIs for individual pages to identify high-performing content and pages needing improvement. This granularity helps pinpoint specific areas that contribute to or detract from your site’s overall performance.

  • Website-Level Analysis: Assess the overall performance of your website to understand broad trends and the cumulative effect of individual page performances. This view is crucial for strategic planning and comprehensive SEO audits.

  • Keyword-Level Analysis: Monitor KPIs for specific keywords to evaluate your search engine optimization efforts for targeted terms. Understanding which keywords drive traffic and conversions allows for better keyword targeting and content strategy development.

By merging data from Google Search Console and GA4, you can create a unified dashboard that streamlines your analysis and reporting processes. This integration makes it easier to identify trends, track progress, and demonstrate the impact of your SEO efforts, ultimately leading to better-informed decisions and improved search engine performance.

The Proposed Model

This flowchart shows the data flow in SEO analysis, starting with 'searchdata_url_impression' and 'ga4_int_events', moving through 'enhanced_seo' and 'enhanced_ga4' processes, and leading to 'keyword_overview' and 'page_overview' outcomes.


The proposed model uses raw GA4 and GSC data to create detailed keyword and page overviews, eventually generating a comprehensive web page overview. Let’s delve into the logic behind aggregating and grouping this data, focusing on the page_overview and keyword_overview stages.

Page Overview: Aggregating and Grouping Data

The page_overview view aggregates data from both GA4 and GSC to provide a detailed performance summary for each webpage.

Aggregation Logic

  • GA4 Data: Aggregates engagement metrics such as the amount of booked meetings, session counts, exits, and entrances.

  • GSC Data: Aggregates metrics like impressions, clicks, and summed position.

The goal is to combine these metrics to understand how users interact with specific pages and how those pages perform in search results.

Grouping criteria

GA4 and GSC data are grouped by page_url, data_date, and country. This grouping allows us to align user behaviour metrics with search performance metrics on a per-page basis, allowing us to filter by date and country, as requested by our marketing team in Astrafy.

Here’s how the SQL transformation is implemented:

{{ config(
  materialized="view",
  alias= "page_overview"
) }}

WITH ga4 AS (
  SELECT
    page_url,
    date_date,
    country,
    SUM(book_meeting) AS count_book_meeting,
    COUNT(ga_session_id) AS count_session_id,
    SUM(is_exit) AS total_exits,
    SUM(is_entrance) AS total_entrances
  FROM {{ ref('enhanced_ga4')}}
  GROUP BY date_date, country, page_url
),

seo AS (
SELECT
  page_url,
  data_date,
  country,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  SUM(sum_position) AS sum_position
FROM {{ ref('enhanced_seo')}}
GROUP BY data_date, country, page_url
)

SELECT
  ga4.*,
  seo.impressions,
  seo.clicks,
  seo.sum_position
FROM seo
LEFT JOIN ga4
ON

  • Data Join: The LEFT JOIN combines GA4 and GSC data based on page_url, ensuring all pages with search data are included.

Keyword Overview: Summarizing Keyword Performance

The keyword_overview view summarises keyword performance across your site. This stage focuses on how different queries drive traffic to your pages.

Aggregation Logic

  • Clicks and Impressions: Summing these metrics provides total visibility and engagement from search queries.

  • Average Position: Summed positions are used to calculate the average position of your keywords in search results.

Grouping Criteria

Data is grouped by query, data_date, country, and page_url. This granularity ensures each keyword’s performance is tracked accurately across different dimensions.

Here’s the SQL code for the keyword overview:

{{ config(
  materialized="view",
  alias= "keyword_overview"
) }}

WITH base AS (
  SELECT
    data_date,
    query,
    country,
    page_url,
    SUM(clicks) AS total_clicks,
    SUM(impressions) AS total_impressions,
    SUM(sum_position) AS sum_position,
  FROM {{ ref('enhanced_seo') }}
  GROUP BY data_date, query, country, page_url
)

SELECT * FROM

  • Aggregation: Clicks, impressions, and positions are summed to reflect the total daily performance of each keyword.

  • Grouping: By query, data_date, country, and page_url to ensure detailed tracking of keyword performance.

Utilizing the data

We all know the quote, “A picture speaks a thousand words”, but at Astrafy, we like to take it a step further and say, ”A dashboard speaks a thousand words and can help you save a thousand euros.” We’ve created a comprehensive dashboard featuring insightful graphs to visualize our data effectively.

Integrating AI for Dashboard Summarization

In order to comply with our main objective of providing data that can derive actionable insights, we developed a Looker extension that uses AI to review your dashboards and suggest improvements based on your specific needs.

This GIF shows an SEO Analytics dashboard interface where users can input keywords, page URLs, and choose data dimensions and time periods for analysis. The dashboard features areas for summarization and instructions, facilitating detailed SEO insights generation.

Key Insights

  • Top Keywords Driving Traffic: Our dashboard highlights which keywords are bringing the most visitors to our webpage. By analyzing this data, we can identify trends in user searches — an invaluable observation for optimizing our content strategy.

  • Leading Queries: Understanding the top queries driving traffic to our site helps us tailor our content to better meet user needs.

This image displays two graphs related to SEO keyword analysis. The top graph shows a bar chart of top keywords by click count and impression sum. The bottom graph illustrates the daily performance of keywords in terms of impressions over a period from February to June.

Top Performing Pages

  • Page Performance Monitoring: The dashboard allows us to track our top-performing pages seamlessly, providing insights into which content resonates most with our audience.

Customization:

  • Individual page insights: Our proposed dashboard design allows us to drill into specific pages to understand the main keywords driving traffic to them.

This image features a bar chart displaying the top search queries filtered by page. The chart highlights how often certain queries are used, with two queries notably higher in frequency compared to the others.

Metric Evolution

  • Evolution of Metrics: We can observe the growth and changes in key metrics over time, helping us make data-driven decisions for future strategies.

  • We can compare the evolution of metrics to understand how they can complement each other and measure the overall context.

This image shows a line graph comparing clicks to impressions over several months. The graph indicates a general trend of impressions consistently outnumbering clicks, with both metrics experiencing fluctuations but remaining relatively stable overall.


Conclusion

With LLMs and Retrieval-Augmented Generation entering the SEO battle, maintaining your website’s visibility and relevance is more critical than ever. As these technologies evolve and potentially replace traditional search engines, it’s essential to understand how your site is ranked and deemed reliable. At Astrafy, we offer a comprehensive SEO Analytics Package that integrates Google Search Console (GSC) and Google Analytics 4 (GA4), providing a holistic view of your website’s performance. By merging GSC and GA4 data using an SQL dbt transformation pipeline, we help you track key performance indicators (KPIs), diagnose issues, and implement tailored solutions to enhance your SEO strategy and stay ahead in the rapidly shifting digital landscape.

Optimizing your web pages with targeted keywords, meta tags, and structured data is essential for being recognized and prioritized by LLMs. These models not only look for relevance to the search query but also prioritize engaging user experiences. By enhancing user engagement through well-written content, interactive elements, optimized page load times, and mobile-friendly experiences, you signal to LLMs that your site is a trusted resource. Embracing a proactive approach to SEO and leveraging advanced analytics tools ensures your website remains a valuable resource for your target audience, securing your online success in the competitive digital landscape.

Thank you

If you enjoyed reading this article, stay tuned as we regularly publish technical articles on leveraging your data best using Google Cloud technologies. Follow Astrafy on LinkedIn, Medium, and Youtube to be notified of the next article.

If you are looking for support on Modern Data Stack or Google Cloud solutions, feel free to reach out to us at sales@astrafy.io.