Google 広告で最も成果の高い検索キーワードを Google スプレッドシートにエクスポートする方法

Apps Script を使用して、Google 広告で最も成果の高い検索キーワードを Google スプレッドシートにエクスポートする方法

この記事をお楽しみいただけましたか?

Share it on social media!

コンテンツ

P-MAXキャンペーンや部分一致キャンペーンを運用している方なら、この苦労は既にご存知でしょう。Googleは実際の検索クエリを隠してしまうのですが、このスクリプトを使えば、それらを復元できます。MCCに直接接続し、コンバージョンに至ったクエリを取得し、CPAとコンバージョン率を計算したGoogleスプレッドシートに出力します。

私たちのスクリプト「Apps Script を使用して最も効果的な Google 広告の検索キーワードを Google スプレッドシートにエクスポートする方法」は、複数のクライアントを管理する代理店や推測を避けたいマーケティング担当者に最適です。

Let’s break it down.

What Export Top Performing Google Ads Search Terms Script Does

すべてのキャンペーンの6か月分のクエリデータを取得します
不要な情報（ブランド用語やコンバージョン率の低いノイズなど）をフィルタリング
コンバージョン単価とコンバージョン率を計算します
すべてをクリーンな Google スプレッドシートにドロップし、アカウントごとに 1 つのタブを作成します

Google 広告スクリプトを追加する方法

Top-performers-1-script-add - B2B Website Platform Developer - Bright Vessel

スクリプトセクションでは、Google 広告 MCC 内のすべての一括自動化を設定します。

1. Log in to your MCC (Manager Account) and go to Tools > Bulk Actions > Scripts

2. Click the blue plus (+) button to create a new script

スクリプトエディタが開きます。ご安心ください。ゼロからコーディングする必要はありません。既に記述されているスクリプトを貼り付けるだけです。このボタンをクリックすると、自動化用の空のスクリプトコンテナが作成されます。

スクリプトに名前を付けることは、長期的な管理とスケジュール設定に不可欠です。

3. Name the script so it’s easy to identify later

スクリプトエディタウィンドウの上部に、名前を入力するフィールドがあります。「パフォーマンスの高い検索語句レポート」のように、わかりやすい名前を付けてください。こうすることで、複数のスクリプトやアカウントを管理する際に、スクリプトを識別しやすくなります。

You can name it Top Performing Actual Search Terms and drop in the script (included at the bottom of this post).

スクリプトに名前を付ける

4. Paste the entire script into the editor

function main() {
// Set the destination Google Sheet URL
var spreadsheetUrl = 'Paste Your Google Sheet URL Here';

// Get the account name to dynamically name the sheet
var accountName = AdsApp.currentAccount().getName();

// Use the account name to label the tab inside the spreadsheet
var sheetName = accountName + ' - Top Performing Terms PC 90 Days';

// Open the spreadsheet by URL
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);

// Get the sheet by name, or create it if it doesn't exist
var sheet = spreadsheet.getSheetByName(sheetName) || spreadsheet.insertSheet(sheetName);

// Clear any existing data from the sheet
sheet.clear();

// Write header row to the spreadsheet
sheet.getRange('A1').setValue('Campaign Name');
sheet.getRange('B1').setValue('Ad Group Name');
sheet.getRange('C1').setValue('Search Term');
sheet.getRange('D1').setValue('Conversions');
sheet.getRange('E1').setValue('Conversion Value');
sheet.getRange('F1').setValue('Cost Per Conversion (CPA)');
sheet.getRange('G1').setValue('Conversion Rate (%)');

// Set the custom date range for reporting (format: YYYYMMDD)
var startDate = '20230315';
var endDate = '20240915';

// Build the query from the Search Query Performance Report
var report = AdsApp.report(
"SELECT CampaignName, AdGroupName, Query, Conversions, ConversionValue, Cost, Clicks " +
"FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
"WHERE Conversions > 0 " +
"DURING " + startDate + "," + endDate
);

// Get report rows and set starting row for data insertion
var rows = report.rows();
var rowNumber = 2;

// Iterate through each row of the report
while (rows.hasNext()) {
var row = rows.next();

// Lowercase the search term for consistent brand filtering
var searchTerm = row['Query'].toLowerCase();
var conversions = parseFloat(row['Conversions']);

// Skip rows with less than 1 conversion
if (conversions < 1) continue;

// Exclude branded terms to avoid polluting the report
if (searchTerm.includes('parker chase') || searchTerm.includes('parker-chase') || searchTerm.includes('endeavor schools')) {
continue;
}

// Optional generic filter - add or remove terms here
if (
searchTerm.includes('creative') ||
searchTerm.includes('learning') ||
searchTerm.includes('content')
) {
continue;
}

// Extract cost and click values to calculate performance
var cost = parseFloat(row['Cost']);
var clicks = parseFloat(row['Clicks']);

// Calculate cost per conversion (CPA)
var cpa = conversions > 0 ? cost / conversions : 0;

// Calculate conversion rate
var conversionRate = clicks > 0 ? (conversions / clicks) * 100 : 0;

// Format values
cpa = cpa.toFixed(2);
conversionRate = conversionRate.toFixed(2);

// Write the row to the spreadsheet
sheet.getRange(rowNumber, 1).setValue(row['CampaignName']);
sheet.getRange(rowNumber, 2).setValue(row['AdGroupName']);
sheet.getRange(rowNumber, 3).setValue(row['Query']);
sheet.getRange(rowNumber, 4).setValue(conversions);
sheet.getRange(rowNumber, 5).setValue(row['ConversionValue']);
sheet.getRange(rowNumber, 6).setValue('$' + cpa); // Include dollar sign for CPA
sheet.getRange(rowNumber, 7).setValue(conversionRate + '%'); // Add percentage symbol

// Move to the next row in the sheet
rowNumber++;
}

// Log how many rows were processed
Logger.log('Export completed. Total rows processed: ' + (rowNumber - 2));
}

スクリプトでカスタマイズできるもの

The script includes several marked settings you can adjust before running it. These are all inside the script's comment blocks and are meant to be changed based on your specific goals, reporting range, or formatting preferences.

Here’s a breakdown of what you can modify and why:

1. Spreadsheet URL

var spreadsheetUrl = 'https://docs.google.com/spreadsheets/d/xxxxx/edit';

What it does:
This is where the script sends your data. Replace the placeholder URL with the link to your Google Sheet. If you're using multiple email addresses, ensure the Sheet is shared with the Google account tied to your MCC.

2. Date Range Settings

var startDate = '20230315';
var endDate = '20240915';

What it does:
This controls the reporting window. Dates must be formatted as YYYYMMDD. You can adjust these to match whatever time frame you're analyzing, the last 30 days, the last 90 days, custom quarterly ranges, etc.

3. Sheet Name Behavior

var sheetName = accountName + ' - Top Performing Terms PC 90 Days';

What it does:
This line dynamically names the Sheet tab based on the account running the script. You can change the text string portion ('—Top Performing Terms PC 90 Days') if you want to label reports differently. This is useful when running across multiple brands or sub-accounts.

4. Branded Term Filtering

if (searchTerm.includes('parker chase') || searchTerm.includes('parker-chase') || searchTerm.includes('endeavor schools')) {
continue;
}

What it does:
This section excludes any branded terms from your report. You can update this list by adding or removing .includes() conditions. For example, if you want to exclude “MyBrand,” just add:

|| searchTerm.includes('mybrand')

スクリプトはすべてのクエリを小文字に変換するため、一貫して小文字を使用してください。

5. Generic Term Filtering

if (
searchTerm.includes('creative') ||
searchTerm.includes('learning') ||
searchTerm.includes('content')
) {
continue;
}

What it does:
This optional filter weeds out terms that aren’t brand-specific but still muddy the data, like “learning” or “creative.” You can add or remove filters here based on what you consider noise.

6. Performance Threshold

if (conversions < 1) continue;

What it does:
This line removes queries with no conversions. You can increase this threshold if you want, only for higher-performing terms. For example, change to:

if (conversions < 3) continue;
...to include only search terms with 3+ conversions in the date range.

7. Sheet Output Formatting

sheet.getRange(rowNumber, 6).setValue('$' + cpa);
sheet.getRange(rowNumber, 7).setValue(conversionRate + '%');

What it does:
This controls how CPA and conversion rate are written on the sheet. You can edit or delete the string additions to remove symbols (e.g., $ or %).

出力フィールド

スクリプトが正常に実行されると、Googleスプレッドシートに構造化された表が作成されます。各行は、指定された期間内に少なくとも1回のコンバージョンをもたらした検索キーワードを表します。

最も成果の高いキーワードのパフォーマンス指標を整理して表示した Google スプレッドシートのクローズアップ

Googleスプレッドシートの例

出力フィールド

このスクリプトはGoogleスプレッドシートにパフォーマンスに関する一連の列を生成し、各検索語句がどのように貢献しているかを明確に把握できるようにします。これらの列には、検索語句がトリガーされた場所を追跡するためのキャンペーン名と広告グループ名、ユーザーが実際に入力した検索語句、その検索語句によって促進されたコンバージョン数、合計コンバージョン値、平均コンバージョン単価、全体的なコンバージョン率が含まれます。各フィールドは、何が効果的で何が無駄になっているかを把握する上で非常に重要です。

Campaign Name
Name of the campaign where the ad was triggered
Ad Group Name
An ad group that matched the search term
Search Term
The exact phrase the user typed into Google
Conversions
Number of tracked conversions from that term
Conversion Value
The total value of those conversions, based on your account settings
Cost Per Conversion (CPA)
The average cost to generate one conversion from that search term
Conversion Rate (%)
Percentage of clicks that resulted in a conversion

このスクリプトをどのように強化できるか

This script is a solid foundation, but it can go further depending on how deep you want your reporting to go. If you manage multiple brands or accounts under one MCC, adding campaign labels or filters can help isolate data by brand or business unit. Scheduling the script to run automatically on a weekly or monthly basis saves time and ensures consistent reports. You can build logic to send alerts when key metrics, such as CPA or conversion rate, cross certain thresholds. For more granular analysis, you can combine this with other reports KEYWORDS_PERFORMANCE_REPORT to get match-type breakdowns. Inside the Sheet, adding pivot tables or conditional formatting can surface insights faster. And if you want to go visual, the data can be pulled into Looker Studio for dashboards your clients can understand.

キャンペーンラベルやアカウントレベルのフィルタを追加して、大規模なクライアントセンター全体のデータをセグメント化します。
組み込みのスケジュールを使用して、毎週または毎月のレポートを自動化します。
CPA が特定の値を超えた場合、またはコンバージョン率がしきい値を下回った場合にメールアラートをトリガーします
KEYWORDS_PERFORMANCE_REPORT などの他のレポートのデータを結合して、マッチタイプを分類します。
基本的なピボット集計や条件付き書式をスプレッドシートに直接追加する
出力をLooker Studioに接続して、視覚的なパフォーマンスダッシュボードを作成します。

まとめ：Bright Vesselの力

Bright Vesselでは、雑然とした広告環境から明確なパフォーマンスインサイトを必要とするクライアントのために、このようなカスタム自動化ツールを構築しています。使いにくいダッシュボードを何度も探したり、必要なデータを取得するために手動でエクスポートを何度も実行したりすることにうんざりしているなら、このスクリプトは私たちがプロセスを効率化する数多くの方法の一つに過ぎません。

大規模なトラッキングを自動化する別の方法については、「Google スプレッドシートと Apps Script を使用して、MCC アカウント全体で Google 広告の目標ステータスを監視する方法」の関連チュートリアルをご覧ください。

Need a version of this that integrates with Slack, builds Looker dashboards, or connects multiple ad accounts into a master sheet? We can make it. 話しましょう.