In today’s rapidly evolving digital landscape, artificial intelligence is reshaping every field by making previously inaccessible skills like coding and data visualization available to everyone. This democratization of technology allows AI operators who can craft effective prompts to perform low- and medium-difficulty tasks, freeing up time for more strategic decision-making.
For SEO professionals, this presents an exciting opportunity to streamline complex reporting processes and gain deeper insights from large datasets. In this guide, we’ll explore how to use AI chatbots like ChatGPT to run sophisticated BigQuery queries for SEO reporting, focusing on two key examples:
- Analyzing Traffic Decline Due to Google Algorithm Impact
- Combining Search Traffic Data with Engagement Metrics from GA4
By leveraging ChatGPT for BigQuery tasks, SEO experts can significantly reduce the burden of running detailed reports while unlocking more powerful data analysis capabilities.
Why Learn BigQuery for SEO?
While tools like Google Search Console (GSC) and Google Analytics 4 (GA4) offer user-friendly interfaces, they often have limitations in terms of data access and completeness. Issues like data sampling in GSC, which omits anonymized queries and limits table rows, can hinder comprehensive analysis.
BigQuery solves these problems by allowing you to run complex, custom reports without data sampling issues – especially beneficial for large websites. While Looker Studio is an alternative, our focus here is on illustrating ChatGPT’s potential for BigQuery operations.
SQL Basics for SEO Professionals
For those new to SQL, here’s a quick reference of key statements and conditions you’ll encounter:
[SQL basics table omitted for brevity]
Now, let’s dive into practical examples of using ChatGPT to generate BigQuery queries for SEO analysis.
Example 1: Analyzing Traffic Decline Due to Google Algorithm Impact
When affected by a Google algorithm update, it’s crucial to analyze impacted pages before making hasty changes. For sites with thousands of pages, GSC’s 1000-row export limit can be restrictive. Here’s how to use ChatGPT to generate a BigQuery query for comprehensive analysis:
Start with a prompt like this:
Imagine you are a data analyst experienced in Google Analytics 4 (GA4), Google Search Console, SQL, and BigQuery. Your task is to generate an SQL query to compare ‘WEB’ Search Console data for the periods ‘2024-05-08’ to ‘2024-05-20’ and ‘2024-04-18’ to ‘2024-04-30’. Extract the total clicks, impressions, and average position for each URL for each period. Additionally, calculate the differences in these metrics between the periods for each URL (where average position should be calculated as the sum of positions divided by the sum of impressions).
Details:
BigQuery project name: use_your_bigquery_projectname
Dataset name: searchconsole
Table name: searchdata_url_impression
Please provide the SQL query that meets these requirements.
2. Copy the generated SQL into BigQuery’s SQL editor. If you encounter errors (e.g., mismatched column names), you can easily fix them:
- Click on your dataset in the left panel
- Select all columns on the right side
- Click “Copy as Table”
- Paste this as a follow-up prompt to ChatGPT
3. ChatGPT will then generate an updated SQL query tailored to your exact table structure.
4. Run the query and export the results to CSV or Google Sheets for further analysis.
For larger datasets, consider saving results as a BigQuery table and connecting to visualization tools like Looker Studio or Databox. However, be mindful of BigQuery’s pricing structure beyond the free 1 TB monthly limit.
Example 2: Combining Search Traffic Data with GA4 Engagement Metrics
Understanding user engagement alongside search traffic data can provide valuable insights into content performance and potential ranking factors. Here’s how to use ChatGPT to combine GSC data with GA4 engagement metrics:
1. Familiarize yourself with GA4’s table structure, particularly the event_params column containing dimensions like page_location and engagement_time_msec.
2. Use a prompt like this:
Imagine you are a data analyst experienced in Google Analytics 4 (GA4), Google Search Console, SQL, and BigQuery. Compose a SQL query that pulls the following data from Google Search Console for each URL for the previous 7 days, excluding the current day:
1.Clicks,
2.Impressions,
3.Average position (calculated as the sum of positions divided by the sum of impressions).
From GA4 BigQuery table unnest from event_params ga_session_id, engagement_time_msec and page_location. Select only rows which have engagement_time_msec set as not null group all sessions with the same IDs and page_location and SUM engagement_time_msec and divides to SUM of sessions number. Join GA4 and Google Search Console data by URLs for the same period.
Additionally, optimize the query to pull from GA4’s table partitions and not query the entire table.
[Include project, dataset, and table details as in the previous example]
3. If the initial results aren’t perfect, provide additional context from GA4’s official documentation on engagement_time_msec calculation.
4.Refine the query through follow-up prompts until you get the desired results.
By leveraging ChatGPT in this way, SEO professionals can quickly generate complex BigQuery queries to analyze the relationship between search performance and user engagement metrics.
