Go Full-Search: Connect SEO & SEA data on Keyword level to Conversion and Revenue

Nowadays, for most companies, search engine advertising (SEA) and search engine optimization (SEO) are essential components of their online marketing strategy. With SEA, you can achieve short-term (paid) results, while SEO often requires a bit more longterm planning.

However, one of the keys to long-term success in your Full Search or Total Search strategy lies in finding the right balance between these channels by approaching them as a whole.

Managed Separately..

Unfortunately, SEA and SEO are often managed separately by advertisers and agencies. By leveraging synergies, you can add significant value to your Total Search Strategy, such as optimizing costs, resolve keyword cannibalization, and protecting your brand.

An integrated approach increases your visibility by leveraging both organic and paid positions. One of the steps to achieve this is by merging data from Google Ads, Google Search Console (the source of SEO data), and Google Analytics. This allows you to identify areas where you can invest more or save costs by fine-tuning your campaigns. This approach enables you to develop a much more effective strategy.

At the same time, a major challenge lies in:

• The constant evolution of SERPs (Search Engine Result Pages), particularly Google’s

• The different approaches of the two disciplines

• The absence of a common measurement method (not all data is made available by Google)

These factors make it challenging to create synergy between the channels, leading many advertisers to believe that it’s impossible. Also, many companies struggle with accessing and combining the necessary data streams and creating action-driven insights.

The power of an Integrated Total Search Dashboard

With an integrated SEA (Google Ads) and SEO (Google Search Console) dashboard, you can automate in-depth analyses to identify opportunities and issues, maximizing your results. By combining data at the most granular level (keyword level) with engagement and conversion/revenue (Google Anlaytics), the dashboard provides you with the tools to gain a comprehensive overview of your search performance.

This enables you to implement targeted optimizations and significantly enhance the effectiveness of your campaigns.

Image: Using trends and the ability to filter by search term clusters, you gain better insights into the performance of your focus areas within search, allowing you to identify optimizations.

Image: Understanding overlap is crucial: focusing on organic efforts lowers the CPA, increases traffic and conversions, and improves visibility through higher rankings.

What you gain and what you might be missing

An integrated dashboard not only gives you a holistic view of your search strategy but also provides clarity on your performance on critical KPIs such as impressions, cost per acquisition, website traffic, and revenue. This allows you to immediately see where your current approach is falling short and where you can improve. Below, we outline some examples of use cases applicable to the dashboard:

1. Identifying Overlap Between SEA and SEO: By identifying keywords that perform well both organically and in paid search, you can determine which keywords need fewer or more paid Google Ads campaigns. This helps you refine your strategy and allocate your budget more effectively.

2. Excluding Paid Ads for Keywords with Strong Organic Performance: This can be valuable but may also lead to reduced traffic and conversions. Test by turning off paid ads for certain overlapping keywords on alternating days. The impact on SEO traffic and conversions can then be easily (and automatically) analyzed in your total search dashboard.

3. Identifying Growth Opportunities: By analyzing trends in your search data, you can discover both short-term and long-term opportunities. For example, you can quickly identify keywords important to your business but currently underperforming in SEO. For these keywords, it may be worthwhile to intensify your Google Ads campaigns. Additionally, an integrated dashboard allows you to quickly capitalize on keywords that are currently trending. You can leverage these opportunities with paid ads in the short term while aligning your long-term SEO strategy with emerging trends.

4. Optimizing Google Ads: By quickly identifying keyword cannibalization between your Google Ads campaigns and ad groups, you can prevent multiple ads from competing for the same keywords. This results in more targeted traffic to your website and helps increase the quality scores of your Ads account. A higher quality score often leads to lower cost-per-click and better overall ad performance. By minimizing these overlaps, you optimize both the effectiveness of your ads and your ad budget.

5. Measuring the Actual Conversion Value of SEO Keywords: The dashboard enables you to link SEO traffic to conversions, allowing you to see exactly which keywords contribute to conversions (and revenue) on your website, such as purchases or sign-ups. By analyzing this data, you can identify high-quality keywords that actually lead to conversions, enabling you to further refine your SEO strategy (based on potential impact).

Common Challenges

While many companies see the potential, they often struggle with the necessary (data) technical and analytical components required for a scalable full search strategy. This complicates obtaining coherent insights and then translating them into actions that improve results. Recurring challenges we often see include:

1. Separated Teams: Organizationally, we often see challenges such as SEO and SEA teams having separate yearly plans, that are not aligned. Both teams may optimize for the same search terms, even though the budget might be better spread across different terms or strategy. An essential requirement for effectively utilizing insights is for teams to look at the same data, align their plans, and evaluate based on these insights.

2. Separated Data Streams: Many companies use separate tools and reports to evaluate SEO and SEA performance. This automatically leads to fragmented data, with multiple versions of the truth within the organization. This makes it difficult to get a complete picture of performance and to spot and capitalize on key insights.

3. Technical Capabilities: Unlocking and combining various data sources is another challenge we frequently encounter. What data do you need? How do you retrieve it? And how do you combine the data without creating an incomprehensible tangle of code? A solid infrastructure is a crucial prerequisite for reliable and scalable use of data.

4. Granularity of Data: Keywords are incredibly important within search. These are ultimately the search terms people use and where companies want to be found. To generate insights, it’s essential that analyses are also conducted at this level. This level of data granularity is often missing in reports or dashboards.

The Building Blocks

But, where do you start? A solid technical and organizational foundation is a prerequisite for success. The organization aspect involves how we can translate this data into actions that improve KPIs. The technical aspect concerns data and how we can make it accessible.

Organizational Building Blocks

Organizationally, it is essential to ensure close and effective collaboration between SEO and SEA teams. This requires a structured approach to ensure that both teams not only work efficiently but also strengthen each other rather than working against each other. You can achieve this by:

• Regular meetings: Schedule frequent recurring meetings to discuss the progress of both teams. These meetings provide an opportunity to evaluate strategies, share insights, and adjust plans together as needed.

• Appointing data ambassadors: Designate specific team members as data ambassadors. These individuals play a crucial role in collecting, analyzing, and interpreting data. They ensure that decision-making within both teams is firmly grounded in data analysis, making strategies based on facts rather than assumptions. The data ambassadors act as the link between both teams, helping to maintain a consistent data-driven approach.

• Developing a use-case driven workflow: Create a workflow based on concrete use cases and practical examples. This workflow should enable teams to work systematically and with a clear focus on achieving objectives (visibility/traffic/revenue). By working from specific use cases, both teams can respond effectively to unique challenges and opportunities while ensuring consistency in their approach.

• Preventing cannibalization: Formulate actions based on detailed data analysis, with the goal of improving the KPIs of both channels without causing cannibalization. This means aligning the strategies of SEO and SEA in such a way that they complement each other rather than compete. Avoiding internal competition between channels is crucial to maximizing the overall effectiveness of marketing efforts.

The ultimate goal of this approach is to develop an integrated and cohesive full search strategy where both teams collaborate optimally. This ensures that each channel reaches its full potential, overall performance is improved, and the organization as a whole achieves better results in terms of visibility, traffic, and conversions.

Image: The technical infrastructure for developing a comprehensive integrated search dashboard

Technical Building Blocks

A well-designed marketing intelligence hub is crucial for the success of your data integration and analysis. This hub consists of various core components that together ensure an effective and reliable data stream. For building an integrated SEO and Google Ads dashboard, the following building blocks are essential:

Data integration

The first step is to ensure you can collect and store the necessary data. Various platforms can be used for this. We often use Google BigQuery due to its direct integrations with Google sources and its extensive analytical capabilities. If you also want to add data from Bing for example, you can use tools like Airbyte. In this phase, you should do the following:

• Set up Google Search Console Export: SEO data consists of impression and click data at the search term and landing page levels, which can be automated from Google Search Console. This consists of a few steps and is well explained here.

• Set up Google Ads Export: Google Ads data is sourced from… Google Ads! We also use a native Google integration to get the data into BigQuery. This blog explains how to do it. The beauty of this integration is that you can load the data daily at the query level.

• Set up Google Analytics 4 Export: Website data is sourced from GA4 and is automatically stored in BigQuery. See here how to set this up in a few clicks. We link conversions from Google Ads and SEO at the search term level to this data. Ideally, we report on the financial truth, but for this use case, GA4 data will also provide valuable insights.

• Further Enrichment: We recommend including your keyword research in your data hub, for example, via Google Sheets. This makes it easy to segment search terms based on criteria such as search segments and business value. Also, add data about competitors and historical rankings of your search terms, for example, from SEMRush. This gives you a complete picture of what’s happening in the search engine.

Data Transformation

Once you have collected and stored the data in your data warehouse, the next step is to transform and link this data. This requires some SQL knowledge. We use Google Dataform to properly align the data streams, so we can create a reliable dashboard.

• Google Search Console Data: This data requires relatively few transformations. The query below illustrates how to group the data by search term (query) and landing page:

1SELECT
2    DATE(data_date) AS date,
3    "google_organic" AS source_group,
4    query,
5    LOWER(url) AS url,
6    is_page_experience,
7    LOWER(device) AS device,
8    SUM(impressions) AS impressions,
9    SUM(clicks) AS clicks,
10    SUM(sum_position) AS sum_position
11FROM 
12    `your_project_id.your_table.searchdata_url_impression`
13GROUP BY 
14    date, source_group, query, url, is_page_experience, device

• Google Ads Data: This data is rich in information and can be complex to transform. Use the following query to select key categories such as campaign and ad group IDs, and aggregate metrics at the query level:

1SELECT
2    segments_date AS date,
3    'google_ads' AS source_group,
4    LOWER(segments_device) AS device,
5    customer_id AS account_id,
6    campaign_id,
7    ad_group_id AS adgroup_id,
8    SUBSTR(
9      segments_keyword_ad_group_criterion, 
10      INSTR(segments_keyword_ad_group_criterion, '~') + 1
11    ) AS adgroup_id_criterion_criterion_id,
12    ad_group_ad_ad_id,
13    search_term_view_search_term AS query,  
14    SUM(metrics_impressions) AS impressions,
15    SUM(metrics_clicks) AS clicks,
16    SUM(metrics_cost_micros) / 1000000 AS cost,
17    SUM(metrics_conversions) AS conversions,
18    SUM(metrics_conversions_value) AS conversion_value
19FROM
20    `your_project_id.your_table.ads_SearchQueryStats_yourGoogleAdsID`
21WHERE
22    _data_date >= '2020-01-01'
23GROUP BY 
24   all

• Google Analytics 4 Data: Google Analytics 4 (GA4) data can be complex and often requires specific adjustments depending on your business. It’s important to aggregate the data at the session level. Many blogs have been written about this, and this article covers the various steps.

Connecting the data

After the basic transformations, the next step is linking the data. For SEO and Google Ads data, this is relatively straightforward: you can combine this data with a left join on date, device, and query. Linking SEO and Google Ads data to GA4 is more complex.

SEO data cannot be directly linked to GA4 because the search terms from organic clicks are not passed to GA4 for privacy reasons. Thus, you cannot see exactly which search terms triggered conversions, only at the landing page level. To bridge this gap, use estimates to determine the number of sessions and conversions per search term. This can be done with machine learning but is also easier using a distribution key as explained in this article; here, you calculate the number of sessions and conversions at the landing page level based on the ratio of clicks on search terms per landing page.

For SEA data, you can link this at the campaign level to GA4. For detailed insights at the keyword level, you can set up custom tracking to identify which keywords triggered conversions. These Keyword IDs can then be matched with the adgroup_id_criterion_criterion_id in the Google Ads dataset.

Once the data is linked and enriched, you can start visualizing the data in your dashboards.

Data Visualization

Data needs to be accessible to everyone in the organization, even without technical knowledge. Therefore, it’s important to select a good intelligence dashboard tool that can visualize Google Ads and SEO insights. At TurnTwo, we make extensive use of Google tools, so Looker Studio is a good tool for visualization. Of course, you can also use other tools such as PowerBI / Tableau, etc.

Optimize your search strategy with Integrated Insights

In summary, an integrated SEO and SEA dashboard provides you with the opportunity to improve your search strategy by combining both channels into a single overview. By integrating data from Google Ads, Search Console, and GA4, you gain valuable insights into keyword overlap, conversion value, and optimization opportunities. These insights help you invest more strategically, refine your search strategy, reduce costs, and maximize conversions.

Want to take your search strategy to the next level? Start by collecting and transforming your data, connecting the different data streams, and visualizing the results. Additionally, it is crucial that your organization is well-structured to facilitate collaboration between SEO and SEA teams. This involves structuring workflows, appointing data ambassadors, and organizing regular alignment meetings to optimize the effectiveness of both channels.

By following these steps, you lay a strong foundation for success and improve the effectiveness of your online marketing.