SEO SQL Pattern #2: Analyzing performance over time

The time dimension is often overlooked in digital analytics. We take it for granted because the whole point of digital marketing is to grow—traffic, customers, and revenue—over time. Performance is inherently a factor of time!

This post follows from the post on Pattern #1, which is all about ranking data and does not take time for granted. In fact, by the end of it, you’ll probably be tired of hearing about it. I’ll introduce four typical ways of thinking about performance with regard to time, and I’ll share my favorite ways to follow these patterns in BigQuery SQL.

I introduce four sub-patterns: absolute time frame, relative time frames, comparing time frames, and observation in change over time.

Watch the Video Walkthrough

To get the most out of this post, watch the video below, where I walk through each pattern and explain the SQL queries in real time.

Pattern 1: Absolute Time Filtering

Why Use Absolute Time?

An absolute time range is a fixed period, such as a specific month or year. This is useful for static reports, like tracking performance for December 2024 without worrying about shifting dates.

Example Query: Absolute Time Filtering

-- ABSOLUTE time frame

SELECT DATE_TRUNC(data_date, MONTH) SUM(clicks) AS total_clicks, SUM(impressions) AS total_impressions FROM `searchconsole.searchdata_site_impression` WHERE DATE_TRUNC(data_date, MONTH) = '2024-12-01'; -- December 2024 only GROUP BY 1

Things to Remember:

• Absolute time is best for historical reporting (e.g., “How did we perform in January?”)
• I like to use DATE_TRUNC() for intuitive filtering that works on all types of date periods without remembering complex date formatting expressions.

BigQuery DATE_TRUNC Documentation

Pattern 2: Relative Time Filtering

Why Use Relative Time?

Unlike absolute time, a relative timeframe adjusts dynamically based on the current date. This is useful for ongoing monitoring (e.g., “How did we perform in the last 28 days?”).

Example Query: Relative Time Filtering

-- RELATIVE time frame (last 28 days)

SELECT MIN(data_date) min_date, MAX(data_date) max_date, SUM(clicks) AS total_clicks, SUM(impressions) AS total_impressions FROM `searchconsole.searchdata_site_impression` WHERE data_date > CURRENT_DATE() - 28; -- Last 28 days -- WHERE data_date > CURRENT_DATE() - 28 - 3; -- Last 28 days of data (accounting for latency)

What to remember

• Relative time keeps reports up-to-date automatically.
• You can use it to understand performance without waiting until the end of a period
• You will often need to adjust your time window to accommodate for Google Search Console’s latency

Learn about the CURRENT_DATE function

Pattern 3: Aggregating Data Over Time

Why Aggregate Data?

Analyzing SEO data at the daily level can be noisy. Aggregating data by week, month, or quarter helps identify long-term trends.

Example Query: Date Aggregation

-- Aggregate by week

SELECT   DATE_TRUNC(data_date, WEEK(MONDAY)) AS week_start,   FORMAT_DATE('%Y-%W', data_date) AS week,   SUM(impressions) AS total_impressions,   SUM(clicks) AS total_clicks FROM `searchconsole.searchdata_site_impression` WHERE data_date BETWEEN '2024-01-01' AND '2024-12-31'  -- Full year GROUP BY 1,2 ORDER BY 1;

Things to Remember:

• Aggregation helps smooth out fluctuations in daily data.
• DATE_TRUNC() is easier to use than FORMAT_DATE().
• Choose weekly/monthly/quarterly aggregation based on reporting needs.

Learn about the DATE_TRUNCC function

Pattern 4: Using Rolling Averages to Smooth Trends

Why Use Rolling Averages?

SEO traffic fluctuates daily, making it hard to see clear trends. A rolling average smooths these fluctuations for better insights.

Example Query: 7-Day Rolling Average

-- 7-day rolling average

SELECT   data_date,   SUM(impressions) AS daily_impressions,   AVG(SUM(impressions)) OVER (ORDER BY data_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_avg_impressions,   SUM(clicks) AS daily_clicks,   AVG(SUM(clicks)) OVER (ORDER BY data_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_avg_clicks FROM `searchconsole.searchdata_site_impression` WHERE data_date > CURRENT_DATE() - 52 * 7  -- Last year GROUP BY data_date ORDER BY data_date;

Things to Remember:

• Rolling windows remove seasonal effects like weekends or holidays.
• ROWS BETWEEN defines how many past days to include in the moving average, and it’s easy to use because both ends of the BETWEEN keyword are included in the time range.
• Adjust window size (e.g., 14-day or 28-day) based on volatility.

BigQuery Window Functions Documentation

Pattern 5: Comparing SEO Performance Over Time

Why Compare Time Periods?

Comparing two time periods helps identify growth, decline, and opportunities.

Example Query: Comparing the Last 28 Days vs. the Previous 28 Days

-- Compare last 28 days vs. previous 28 days

WITH last_28 AS (   SELECT query, SUM(clicks) AS last_clicks, SUM(impressions) AS last_impressions   FROM `searchconsole.searchdata_site_impression`   WHERE data_date BETWEEN CURRENT_DATE() - 28 AND CURRENT_DATE()   GROUP BY query ), previous_28 AS (   SELECT query, SUM(clicks) AS prev_clicks, SUM(impressions) AS prev_impressions   FROM `searchconsole.searchdata_site_impression`   WHERE data_date BETWEEN CURRENT_DATE() - 56 AND CURRENT_DATE() - 29   GROUP BY query ) SELECT l.query, l.last_clicks, p.prev_clicks, l.last_clicks - p.prev_clicks AS click_difference FROM last_28 l LEFT JOIN previous_28 p ON l.query = p.query ORDER BY click_difference DESC;

Things to Remember:

• Identifies top-growing and declining pages or queries.
• Uses WITH statements (CTEs) for cleaner comparisons.
• Sorting by click_difference surfaces weaknesses and opportunities

BigQuery Common Table Expressions (CTE) Documentation

Final Thoughts

Hopefully, now, when you wonder, “How did we perform last week vs the previous week,” you’ll identify the pattern for analysis and be able to quickly write the SQL to find the answer.

Take Your SQL Skills to the Next Level

Sign up for the SQL for SEO Course to become a better analyst than 99% of SEOs!