The Search Console API and BigQuery Export: Automating Your Own SEO Report in C#

Version note: The code in this post was built and run with .NET 10, Google.Apis.SearchConsole.v1 1.78.0.4234 and Microsoft.Data.SqlClient 7.1.2; the archive ran on SQL Server 2025. This post is based on the official documentation: the Search Console API reference, usage limits and the bulk data export help pages, as of 15 November 2026.

Guess First

For the blog of Bookstore, the online bookstore used as the shared example across this site's series, Emre pulled the last month from the Search Console API with the page and query dimensions and added it up. The total clicks came out lower than the chart in the interface. Selin, meanwhile, wants a two-year comparison. Why did the API total not match? And if you start today, which part of the data from two years ago can you still reach?

Authentication: Service Account and Property Access

For a job to access Search Console on its own, you use a service account. The flow has three steps: create a service account in your own Google Cloud project and enable the Search Console API; add the client_email from the service account's key file as a user of the property in Search Console; the job then gets a token with the key file in the webmasters.readonly scope. According to Google's API docs, read permission on the property is enough for searchAnalytics.query, so there is no need to make it an owner.

using Google.Apis.Auth.OAuth2;
using Google.Apis.SearchConsole.v1;
using Google.Apis.Services;

namespace Bookstore.Analytics.SearchConsole;

public static class SearchConsoleClient
{
    // The key file lives outside the repository; its path comes from configuration.
    public static async Task<SearchConsoleService> CreateAsync(string keyFilePath, CancellationToken ct = default)
    {
        ServiceAccountCredential account =
            await CredentialFactory.FromFileAsync<ServiceAccountCredential>(keyFilePath, ct);
        GoogleCredential credential = account.ToGoogleCredential()
            .CreateScoped(SearchConsoleService.ScopeConstants.WebmastersReadonly);

        return new SearchConsoleService(new BaseClientService.Initializer
        {
            HttpClientInitializer = credential,
            ApplicationName = "bookstore-analytics",
        });
    }

    // Local runs against a stub server; no credentials involved.
    public static SearchConsoleService CreateForStub(Uri baseUri) =>
        new(new BaseClientService.Initializer
        {
            BaseUri = baseUri.ToString(),
            ApplicationName = "bookstore-analytics",
        });
}

CredentialFactory.FromFileAsync<ServiceAccountCredential> requires the file to actually be a service account key. Google.Apis.Auth's own documentation says a credential file from an external source must be validated before use; fixing the type up front is the simplest form of that validation. The key file's path comes from the BOOKSTORE_GSC_KEY_FILE environment variable; the file lives on the server or in a secret store, not in the repository.

The Search Analytics Query

There is a single endpoint, searchAnalytics.query. The important fields: startDate and endDate are in Pacific Time and both inclusive; dimensions can be country, device, page, query, searchAppearance, date and hour; type defaults to web; filters offer equals, contains and, for RE2 regexes, includingRegex/excludingRegex; rowLimit is between 1 and 25,000 with a default of 1,000; dataState defaults to final, finalized data only. Response rows come sorted by clicks, descending, and the docs warn plainly that the API does not guarantee to return all rows, only the top ones.

The advice in Google's "Getting all your data" guide turns straight into code: ask for a single day per request; set rowLimit to 25,000 and keep increasing startRow until an empty response; data is usually ready after 2-3 days. The guide says at most 50,000 rows are exposed per day and search type, that adding the page and query dimensions may make the system drop some data, and that for accurate totals you must leave those two dimensions out. That is the first answer to the opening question: Emre's total comes from rows, and the rows lack both anonymized queries and dropped data. So the fetcher sends two queries per day: a total without dimensions and the detail.

using System.Globalization;
using Google.Apis.SearchConsole.v1;
using Google.Apis.SearchConsole.v1.Data;

namespace Bookstore.Analytics.SearchConsole;

public sealed class SearchAnalyticsFetcher(SearchConsoleService service, int pageSize = 25_000)
{
    private static readonly string[] DetailDimensions = ["page", "query", "country", "device"];

    // Page + query rows of one day; Google may drop some rows at this level of detail.
    public async Task<List<SearchAnalyticsRow>> FetchDetailAsync(
        string siteUrl, DateOnly day, string type = "web", CancellationToken ct = default)
    {
        List<SearchAnalyticsRow> rows = [];
        for (int startRow = 0; ; startRow += pageSize)
        {
            SearchAnalyticsQueryResponse response = await QueryAsync(siteUrl, day, type, DetailDimensions, startRow, ct);
            if (response.Rows is not { Count: > 0 } page)
            {
                return rows;
            }

            rows.AddRange(page.Select(r => new SearchAnalyticsRow(
                day, type, r.Keys[0], r.Keys[1], r.Keys[2], r.Keys[3],
                (int)(r.Clicks ?? 0), (int)(r.Impressions ?? 0), r.Position ?? 0)));
        }
    }

    // Without page and query dimensions the totals are complete.
    public async Task<DailyTotal> FetchTotalAsync(
        string siteUrl, DateOnly day, string type = "web", CancellationToken ct = default)
    {
        SearchAnalyticsQueryResponse response = await QueryAsync(siteUrl, day, type, [], 0, ct);
        ApiDataRow? row = response.Rows?.FirstOrDefault();
        return new DailyTotal(day, type, (int)(row?.Clicks ?? 0), (int)(row?.Impressions ?? 0), row?.Position ?? 0);
    }

    private Task<SearchAnalyticsQueryResponse> QueryAsync(
        string siteUrl, DateOnly day, string type, IList<string> dimensions, int startRow, CancellationToken ct)
    {
        string date = day.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
        SearchAnalyticsQueryRequest body = new()
        {
            StartDate = date,
            EndDate = date,
            Type = type,
            Dimensions = dimensions,
            DataState = "final",
            RowLimit = pageSize,
            StartRow = startRow,
        };
        return service.Searchanalytics.Query(body, siteUrl).ExecuteAsync(ct);
    }
}

The limits support this design too. For Search Analytics there are 1,200 queries per minute per site and per user, and 40,000 per minute and 30 million per day per project. On top of that there is a "load" quota: grouping or filtering by page and query, especially both together, are the most expensive queries; a six-month query costs far more than a one-day query. If you exceed the short-term quota, the docs say to wait 15 minutes and try again.

Testing the Google Client

To write tests without touching a real property or credentials, we replace the Google client library's HttpClientFactory and put a fake HttpMessageHandler in between. On the first try the tests failed with a JSON parse error: the client gzips request bodies by default. So the fake handler unpacks the body when it sees Content-Encoding: gzip.

public sealed class FakeGoogleHttpClientFactory(HttpMessageHandler handler) : Google.Apis.Http.HttpClientFactory
{
    protected override HttpMessageHandler CreateHandler(Google.Apis.Http.CreateHttpClientArgs args) => handler;
}

    [Fact]
    public async Task Pages_with_startRow_until_an_empty_response()
    {
        FakeHandler handler = new((_, body) =>
        {
            int startRow = (int)JsonNode.Parse(body)!["startRow"]!;
            return (HttpStatusCode.OK, startRow switch { 0 => Rows(2), 2 => Rows(1), _ => "{}" });
        });
        SearchAnalyticsFetcher fetcher = new(Service(handler), pageSize: 2);

        List<SearchAnalyticsRow> rows = await fetcher.FetchDetailAsync("sc-domain:bookstore.example", new DateOnly(2026, 11, 9));

        Assert.Equal(3, rows.Count);
        Assert.Equal([0, 2, 4], handler.Calls.Select(c => (int)JsonNode.Parse(c.Body)!["startRow"]!));
        JsonNode first = JsonNode.Parse(handler.Calls[0].Body)!;
        Assert.Equal("2026-11-09", (string)first["startDate"]!);
        Assert.Equal("2026-11-09", (string)first["endDate"]!);
        Assert.Equal("final", (string)first["dataState"]!);
        Assert.EndsWith("/webmasters/v3/sites/sc-domain%3Abookstore.example/searchAnalytics/query",
            handler.Calls[0].Request.RequestUri!.AbsolutePath);
    }

The test checks that paging moves through startRow 0, 2 and 4 and stops at the empty response, that the date is a single day with final, and that the sc-domain: prefix is encoded in the URL. Another test checks that the total query sends no dimensions, and a third that the last final day is computed in Pacific Time:

$ dotnet test --filter "FullyQualifiedName~SearchAnalyticsFetcherTests|FullyQualifiedName~WeeklyReportTests"
Passed!  - Failed:     0, Passed:     5, Skipped:     0, Total:     5, Duration: 55 ms - Bookstore.Analytics.Tests.dll (net10.0)

A Local Run: Stub Server and SQL Server Archive

To try it end to end I wrote tools/Bookstore.Analytics.GscStub, a small minimal API bound only to 127.0.0.1. It is not Google; it mimics the shape of the Search Analytics request and response and returns synthetic rows. To show the "total larger than the rows" gap of the real API, it puts 8 percent more clicks into the total response than the rows contain. The stub itself uses UseRequestDecompression for the gzipped bodies. When the CLI gets this address with --stub, the client points its BaseUri there:

$ dotnet run --project tools/Bookstore.Analytics.GscStub --urls http://127.0.0.1:52703
$ dotnet run --project src/Bookstore.Analytics.Cli -- fetch sc-domain:bookstore.example \
    --from 2026-10-29 --to 2026-11-11 --stub http://127.0.0.1:52703/ --page-size 25
2026-10-29 web:  28 detail rows,  141 clicks in rows,  152 clicks in total
2026-10-30 web:  28 detail rows,  141 clicks in rows,  152 clicks in total
2026-10-31 web:  28 detail rows,   84 clicks in rows,   91 clicks in total
2026-11-01 web:  28 detail rows,   84 clicks in rows,   91 clicks in total
2026-11-02 web:  28 detail rows,  141 clicks in rows,  152 clicks in total
2026-11-03 web:  28 detail rows,  141 clicks in rows,  152 clicks in total
2026-11-04 web:  28 detail rows,  141 clicks in rows,  152 clicks in total
2026-11-05 web:  28 detail rows,  131 clicks in rows,  141 clicks in total
2026-11-06 web:  28 detail rows,  131 clicks in rows,  141 clicks in total
2026-11-07 web:  28 detail rows,   77 clicks in rows,   83 clicks in total
2026-11-08 web:  28 detail rows,   77 clicks in rows,   83 clicks in total
2026-11-09 web:  28 detail rows,  131 clicks in rows,  141 clicks in total
2026-11-10 web:  28 detail rows,  131 clicks in rows,  141 clicks in total
2026-11-11 web:  28 detail rows,  131 clicks in rows,  141 clicks in total

The stub's log sees one total and a three-page detail query per day; the third page comes back empty and the loop ends there:

query site=sc-domain:bookstore.example date=2026-10-29 dims=[] startRow=0 rowLimit=25
query site=sc-domain:bookstore.example date=2026-10-29 dims=[page,query,country,device] startRow=0 rowLimit=25
query site=sc-domain:bookstore.example date=2026-10-29 dims=[page,query,country,device] startRow=25 rowLimit=25
query site=sc-domain:bookstore.example date=2026-10-29 dims=[page,query,country,device] startRow=50 rowLimit=25

The archive side is written so that running the job twice for the same day leaves a single copy: it deletes that day's rows first and then writes them again with SqlBulkCopy, both in the same transaction.

    // Idempotent: running the job twice for the same day leaves one copy.
    public async Task ReplaceDayAsync(DailyTotal total, IReadOnlyList<SearchAnalyticsRow> rows, CancellationToken ct = default)
    {
        await using SqlConnection connection = new(connectionString);
        await connection.OpenAsync(ct);
        await using SqlTransaction tx = (SqlTransaction)await connection.BeginTransactionAsync(ct);

        await using (SqlCommand delete = new("""
            DELETE dbo.SearchAnalyticsDaily  WHERE DataDate = @d AND SearchType = @t;
            DELETE dbo.SearchAnalyticsTotals WHERE DataDate = @d AND SearchType = @t;
            INSERT dbo.SearchAnalyticsTotals VALUES (@d, @t, @c, @i, @p);
            """, connection, tx))
        {
            delete.Parameters.Add("@d", SqlDbType.Date).Value = total.Date.ToDateTime(TimeOnly.MinValue);
            delete.Parameters.Add("@t", SqlDbType.VarChar, 16).Value = total.SearchType;
            delete.Parameters.Add("@c", SqlDbType.Int).Value = total.Clicks;
            delete.Parameters.Add("@i", SqlDbType.Int).Value = total.Impressions;
            delete.Parameters.Add("@p", SqlDbType.Float).Value = total.Position;
            await delete.ExecuteNonQueryAsync(ct);
        }

        using DataTable table = ToTable(rows);
        using SqlBulkCopy bulk = new(connection, SqlBulkCopyOptions.Default, tx)
        {
            DestinationTableName = "dbo.SearchAnalyticsDaily",
        };
        await bulk.WriteToServerAsync(table, ct);
        await tx.CommitAsync(ct);
    }

After pulling 11 November a second time, the table still held 28 rows and 131 clicks for that day. To run this job once a day you can use cron, Windows Task Scheduler or a BackgroundService; scheduling, retry and idempotency are covered in the Background Jobs and Scheduled Jobs series posts "Cron Expressions" and "Retry and Idempotency in Background Jobs". Mind the time zone: run the job after the day has ended in Pacific Time and the data is final, so at least three days behind.

A Weekly Report and Threshold Alerts

The point of the archive is a weekly summary: this week's site total against the previous week, the pages that gained and lost the most, and an alert when a threshold is crossed. The site total comes from the dimensionless totals table, the page changes from the detail table:

    public static WeeklySummary Build(
        int previousSiteClicks, int currentSiteClicks,
        IEnumerable<PageWeek> previous, IEnumerable<PageWeek> current,
        int top = 3, double dropThreshold = 0.30, int minClicks = 50)
    {
        Dictionary<string, int> before = previous.ToDictionary(p => p.Page, p => p.Clicks);
        Dictionary<string, int> after = current.ToDictionary(p => p.Page, p => p.Clicks);

        List<PageChange> changes = before.Keys.Union(after.Keys)
            .Select(page => new PageChange(page, before.GetValueOrDefault(page), after.GetValueOrDefault(page)))
            .ToList();

        List<string> alerts = [];
        if (previousSiteClicks >= minClicks && currentSiteClicks < previousSiteClicks * (1 - dropThreshold))
        {
            alerts.Add($"site clicks fell {Drop(previousSiteClicks, currentSiteClicks)} week over week");
        }

        alerts.AddRange(changes
            .Where(c => c.PreviousClicks >= minClicks && c.CurrentClicks < c.PreviousClicks * (1 - dropThreshold))
            .OrderByDescending(c => c.PreviousClicks - c.CurrentClicks)
            .Select(c => $"{c.Page} fell {Drop(c.PreviousClicks, c.CurrentClicks)} ({c.PreviousClicks} -> {c.CurrentClicks})"));

        return new WeeklySummary(
            previousSiteClicks,
            currentSiteClicks,
            changes.Where(c => c.Delta > 0).OrderByDescending(c => c.Delta).ThenBy(c => c.Page, StringComparer.Ordinal).Take(top).ToList(),
            changes.Where(c => c.Delta < 0).OrderBy(c => c.Delta).ThenBy(c => c.Page, StringComparer.Ordinal).Take(top).ToList(),
            alerts);
    }

The alert has two conditions: the drop must exceed 30 percent and the previous week must have had at least 50 clicks. Without the second condition a page going from 4 clicks to 2 raises an alarm every week. In the stub's synthetic data we put a drop and a rise into the second week; the report finds them and returns a non-zero exit code when there is an alert, so the tool that runs the job can send a notification:

$ dotnet run --project src/Bookstore.Analytics.Cli -- report 2026-11-11; echo "exit code: $?"
week 2026-11-05..2026-11-11 vs 2026-10-29..2026-11-04
site clicks: 942 -> 871
gainers:
   +105  /blog/cs8618-nullable-uyarisi (301 -> 406)
losers:
   -169  /blog/ef-core-migration-rehberi (391 -> 222)
alerts:
  ! /blog/ef-core-migration-rehberi fell 43% (391 -> 222)
exit code: 3

Beyond 16 Months: BigQuery Bulk Data Export

The alternative to your own archive is Search Console's daily bulk export to BigQuery. Setup: in a Cloud project with billing enabled, turn on the BigQuery API and the BigQuery Storage API, grant the BigQuery Job User and BigQuery Data Editor roles to search-console-data-export@system.gserviceaccount.com, then in Search Console under Settings, Bulk data export, enter the project ID, the dataset name (default searchconsole) and the location. The location is not easy to change later. The first export arrives within 48 hours of setup and does not backfill: the export starts from the day it was set up. That is why the answer to the second opening question is "16 months back from today": for anything older, neither the API nor the export can help.

The export has a searchdata_site_impression table (aggregated by property), a searchdata_url_impression table (by URL) and an ExportLog table. Anonymized queries are marked in the rows with an empty query and is_anonymized_query. Position is stored as zero-based sums; you compute the average with SUM(sum_position) / SUM(impressions) + 1. The tables are partitioned by data_date, and most of the cost is the data scanned; always write the query with a date filter:

SELECT
  url,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions,
  SUM(sum_position) / SUM(impressions) + 1 AS avg_position
FROM searchconsole.searchdata_url_impression
WHERE data_date BETWEEN DATE '2026-11-05' AND DATE '2026-11-11'
  AND search_type = 'WEB'
GROUP BY url
ORDER BY clicks DESC
LIMIT 20;

Cost and operations notes: storage and queries are charged once you exceed the free tier; without a partition expiration the data piles up forever, and if you set one it must be at least 14 days. Adding a column to the table schema breaks the export. On a persistent error Search Console retries the next day, but a day's data is dropped after about a week, and an export that keeps failing for about a month stops entirely.

Production Notes

Do not put the key file in the repository or write it to a log. Its path comes from configuration, the file itself lives in a secret store, and the service account gets only read access on the property.

Respect the quota. Run the daily job one day at a time, do not query the same data again and again, and on errors retry after waiting, not immediately.

Store totals and detail separately. Use the dimensionless totals table for the site trend and the detail table for page and query analysis; do not try to derive one from the other.

Keep alerts weekly. Daily clicks drop naturally on weekends and holidays, so daily thresholds produce needless alarms.

Common Pitfalls

Expecting the API to give the same number as the interface. Queries grouped by page and query lack anonymized queries and truncated rows; the aggregation type can differ too.

Pulling the last days as final and finding them empty, or pulling them as all and reporting numbers that change later. Final data arrives 2-3 days behind.

Postponing the export to "later". The bulk export brings no historical data; the longer you wait, the more history you lose.

Committing a service account key in sample code or a test file. Even a fake value in a real format gets caught by secret scanning tools; in tests use the fake handler, which never needs credentials.

A Small Experiment

★☆☆ Run the stub and run fetch again with --page-size 10. How many detail queries do you see per day in the stub log, and why?

★★☆ Add searchAppearance support to SearchAnalyticsFetcher. According to the docs this dimension cannot be combined with other dimensions; query with it alone first to find the types, then send a filtered second query per type. Test it with the fake handler.

★★★ Add a "newly lost queries" section to WeeklyReport: queries that had at least 20 impressions last week but do not appear at all this week. Write the query against the archive table and add its test.

The Search Console data is now ours and no longer limited to 16 months. In the next post we move to the other half of the journey, what readers do on the site: GA4's event model, page_view in an Angular SPA and key events.