# N-gram Analysis for Google Ads Search Terms at Scale

> A complete BigQuery and Looker Studio workflow for n-gram analysis of Google Ads search terms. Surface patterns that the raw search term report hides. With SQL examples and a real account use case.


**By Murtaza Rangwala** · **Published:** Jun 08, 2026 · **Read time:** 8 min read · **Category:** Audits

The search terms report is one of the most powerful tools in Google Ads. It's also one of the worst to use at scale. A campaign with 5,000 unique search terms in a month is impossible to skim. Sorting by cost only surfaces the obvious losers. The interesting patterns hide in the long tail.

**N-gram analysis** solves this. Instead of looking at individual search terms, you decompose every search term into its component words, two-word phrases, and three-word phrases. Then you aggregate. Suddenly, patterns that were invisible in the raw report become obvious.

This is the workflow every advanced PPC manager I know runs at least weekly. It's also one of the least documented expert techniques. Here's the full BigQuery + Looker Studio setup.

!!!
**The TL;DR:** N-gram analysis splits search terms into 1-word, 2-word, and 3-word components, then aggregates spend and conversions across every term that contains each component. Patterns that are invisible in the raw search term report (where each term shows up once) become obvious. The workflow scales to accounts with hundreds of thousands of search terms.
!!!

## What n-gram analysis actually does

A traditional search term report looks like:

| Search Term | Cost | Conversions |
|---|---|---|
| sports physio london | £24 | 2 |
| sports physio bondi | £18 | 1 |
| sports massage london | £31 | 0 |
| women's physio london | £45 | 3 |

Each row is one term. To find patterns, you'd have to read every row.

N-gram analysis pivots this. For 2-grams (two-word phrases), the same data becomes:

| 2-gram | Cost | Conversions |
|---|---|---|
| sports physio | £42 | 3 |
| sports massage | £31 | 0 |
| physio london | £69 | 5 |
| women's physio | £45 | 3 |

Now you can see that `sports massage` is burning £31 with zero conversions across all the terms it appears in. The pattern is obvious. The individual search terms hid it.

> N-grams find category-level winners and losers in seconds. The search terms report finds individual ones in hours.

## Why 1-grams, 2-grams, and 3-grams all matter

Each n-gram size surfaces different patterns:

- **1-grams (single words):** Reveal vocabulary problems. *"Cheap"* across all search terms might be expensive and converting poorly. *"Best"* might be the opposite.
- **2-grams (two-word phrases):** Reveal modifier patterns. *"Same day"* might be a strong converter. *"How to"* might be a budget drain.
- **3-grams (three-word phrases):** Reveal specific intent patterns. *"Same day delivery"* vs *"same day appointment"* — different intents, different performance.

Running all three sizes and cross-referencing gives you a complete picture.

## Step 1: Get your search terms data into BigQuery

The Google Ads search terms report doesn't export easily at scale. The cleanest way is via the **Google Ads Data Transfer Service**:

1. In BigQuery, open **Data Transfer Service**.
2. Create a new transfer from **Google Ads**.
3. Authenticate with your Google Ads MCC.
4. Configure daily transfers.

After 24 hours, you'll have a `p_SearchQueryStats_<account_id>` table with every search term, by date, by campaign, by ad group, with cost, clicks, impressions, conversions, and conversion value.

## Step 2: Write the n-gram SQL

The trick is using BigQuery's `UNNEST` and `SPLIT` to break each search term into its components.

### 1-gram query

```sql
WITH word_explode AS (
  SELECT
    LOWER(word) AS ngram,
    s.cost_micros / 1000000 AS cost,
    s.clicks,
    s.conversions,
    s.conversions_value
  FROM
    `your_project.your_dataset.p_SearchQueryStats_<account_id>` s,
    UNNEST(SPLIT(s.query, ' ')) AS word
  WHERE
    s.segments_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
)
SELECT
  ngram,
  SUM(cost) AS total_cost,
  SUM(clicks) AS total_clicks,
  SUM(conversions) AS total_conversions,
  SUM(conversions_value) AS total_conversion_value,
  SAFE_DIVIDE(SUM(conversions_value), SUM(cost)) AS roas
FROM
  word_explode
WHERE
  LENGTH(ngram) > 2  -- filter out stopwords like 'a', 'to', 'in'
GROUP BY
  ngram
HAVING
  total_cost > 25
ORDER BY
  total_cost DESC
LIMIT 200;
```

### 2-gram query

For two-word phrases, you need to build sliding windows:

```sql
WITH terms_array AS (
  SELECT
    SPLIT(LOWER(s.query), ' ') AS words,
    s.cost_micros / 1000000 AS cost,
    s.clicks,
    s.conversions,
    s.conversions_value
  FROM
    `your_project.your_dataset.p_SearchQueryStats_<account_id>` s
  WHERE
    s.segments_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    AND ARRAY_LENGTH(SPLIT(s.query, ' ')) >= 2
),
bigrams AS (
  SELECT
    CONCAT(words[OFFSET(i)], ' ', words[OFFSET(i+1)]) AS ngram,
    cost, clicks, conversions, conversions_value
  FROM
    terms_array,
    UNNEST(GENERATE_ARRAY(0, ARRAY_LENGTH(words) - 2)) AS i
)
SELECT
  ngram,
  SUM(cost) AS total_cost,
  SUM(clicks) AS total_clicks,
  SUM(conversions) AS total_conversions,
  SUM(conversions_value) AS total_conversion_value,
  SAFE_DIVIDE(SUM(conversions_value), SUM(cost)) AS roas
FROM
  bigrams
GROUP BY
  ngram
HAVING
  total_cost > 25
ORDER BY
  total_cost DESC
LIMIT 200;
```

### 3-gram query

Same pattern, with `i+2` instead of `i+1` in the concat, and `ARRAY_LENGTH(words) - 3` in the generator.

## Step 3: Build the Looker Studio dashboard

In Looker Studio:

1. Connect a new BigQuery data source for each of the three n-gram queries (or schedule them as views).
2. Create a multi-tab dashboard:
   - **Tab 1: 1-gram analysis** — top 50 words by cost, sortable by ROAS, cost-per-conversion, conversion rate.
   - **Tab 2: 2-gram analysis** — same view for two-word phrases.
   - **Tab 3: 3-gram analysis** — same view for three-word phrases.
   - **Tab 4: Negatives candidates** — n-grams with cost > £50 and zero conversions in the last 30 days.
3. Add filters for date range, campaign, ad group, and minimum cost threshold.
4. Add conditional formatting: red for n-grams below 1.0 ROAS, green for n-grams above 4.0 ROAS.

This dashboard becomes your weekly review tool. Open it, look for red, add negatives, look for green, expand bids.

## Step 4: Apply what you find

The n-gram analysis surfaces three types of actions:

### 1. Negative keyword candidates

N-grams that consistently appear in non-converting or low-ROAS search terms are negative candidates.

Example: across 240 search terms containing the word `"refund"`, total cost was £580 with 1 conversion. Adding `refund` as a phrase negative across the account cuts that waste instantly.

### 2. Bid-up candidates

N-grams that consistently appear in high-ROAS search terms are candidates for new exact-match campaigns or higher bid adjustments.

Example: 2-gram `"same day"` appears across 80 search terms with cost £420 and ROAS of 6.2. Building a dedicated campaign for `same day [your service]` queries with higher bids captures more of that demand.

### 3. New keyword candidates

N-grams that show high impression volume but you're not targeting directly suggest expanding your keyword list.

Example: 3-gram `"weekend brunch dubai"` appears in many high-converting terms. If you don't have a campaign specifically for brunch-related queries, build one.

## Step 5: Automate the weekly review

In BigQuery, schedule each n-gram query to materialise as a daily table. In Looker Studio, the dashboard refreshes automatically.

Every Monday morning, the dashboard shows the previous 30 days of n-gram performance. The marketer's job is now:

- Review the negatives candidates list. Approve and add.
- Review the high-ROAS n-grams. Decide whether to build new campaigns.
- Spot trend changes (e.g., a previously profitable n-gram suddenly underperforming — usually a sign of seasonality or competitor activity).

Total time per week: 15-30 minutes once you're set up. Replaces hours of manual search term review.

## Use case: a regional dental group with three brands

A composite based on patterns I've seen.

A dental group with three brands and ~£25k/month in Google Ads was getting ~12,000 unique search terms a month. The PPC manager was spending 5-6 hours a week on manual search term review.

We set up the n-gram dashboard. First-week findings:

- **1-gram `"prices"`** burning £390/month with 1 conversion. Customers were price-shopping but not converting on this account. Added as negative.
- **2-gram `"emergency dentist"`** generating ROAS of 8.4. The account was bidding modestly on it. Built a dedicated campaign with higher bids and emergency-specific ad copy.
- **2-gram `"near me"`** profitable but spending volume was capped by lower bids. Increased bids, captured more of the demand.
- **3-gram `"how much does"`** burning £210/month with zero conversions. Pure research intent. Added as negative phrase.

Total negatives identified in the first week: 23.

**Results over 60 days:**

- Wasted spend down ~£1,800/month.
- New campaigns built on high-ROAS n-grams contributed ~£3,400/month in additional bookings.
- PPC manager's weekly search term review time dropped from 5-6 hours to 30 minutes.

The n-gram dashboard didn't replace strategy. It surfaced the patterns the strategist needed to act on.

## Common mistakes

- **Running n-gram analysis without a minimum cost threshold.** Tiny n-grams clutter the dashboard. Filter for n-grams with at least £25 spend (or whatever's significant for your account).
- **Ignoring stopwords.** Words like *"a"*, *"the"*, *"to"* appear everywhere. Filter them out at the SQL level.
- **Forgetting conversion lag.** A 30-day n-gram window may not show full conversion performance for B2B / long-cycle accounts. Adjust the window.
- **Adding negatives without testing.** *"Free"* as a broad negative could block *"free shipping"* on e-commerce. Use phrase or exact match where the term could legitimately appear in buyer queries.
- **Doing n-gram analysis manually in spreadsheets.** Works for one campaign. Doesn't scale. Get to BigQuery.

## Bottom line

The search terms report is a list of trees. N-gram analysis is the forest. Both matter, but the forest tells you which trees to look at.

- Pipe search terms data into **BigQuery** via the Data Transfer Service.
- Run **1-gram, 2-gram, and 3-gram queries** for full-spectrum coverage.
- Build a **Looker Studio dashboard** that surfaces negatives candidates and bid-up candidates.
- Automate the weekly refresh and **spend 30 minutes a week** acting on insights, not hours reading raw reports.

Every senior PPC manager I trust runs some version of this workflow. The ones who don't are missing patterns that are obvious to the ones who do. The setup takes a day. The advantage compounds every week after that.

---

**Sources and further reading:**

- [Google Ads Data Transfer to BigQuery (Google Cloud)](https://cloud.google.com/bigquery/docs/google-ads-transfer)
- [About search terms reports (Google Ads Help)](https://support.google.com/google-ads/answer/2472708)
- [BigQuery UNNEST and SPLIT (Google Cloud)](https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions)
- [Looker Studio Google Ads connector](https://support.google.com/looker-studio/answer/9420946)

---

**Tags:** search terms, search terms in looker studio, n-gram analysis

## About the author

Murtaza Rangwala is a senior independent Google Ads consultant. 8 years, 1,900+ campaigns shipped, $250M+ in client revenue generated. Independent practice capped at four concurrent clients.

- More posts: https://www.murtazarangwala.com/blog
- Book a 30-min call: https://calendly.com/murtaza_rangwala/30min
- Free Google Ads audit: https://www.murtazarangwala.com/#audit