Any specialist working with Google Ads is familiar with the process of analyzing search phrases. If you do not use a broad match type, and keep your negative keywords up to date, then there is quite a bit of garbage, but there are many opportunities for segmentation and more precise targeting.
However, with a large amount of data, this work becomes monotonous and can often be ignored. In order not to occupy my head with such a routine, I automated the process using a script.
The script collects data from Google Ads and its associated view in Universal Analytics.
This is the config:
function CONFIG() {
return {
// ID the GA profile that the ad account is associated with
gaProfileId: '1234567890',
// The label with which the script marks the created keywords
scriptLabel: 'Keyword Builder',
// Specify the number of days to select
// If we want to use data about conversions or profitability, then as a value
// you should specify a number greater than the conversion window.
customDaysInDateRange: 30,
// Specify how many days from today we shift the selection.
// Necessary in order not to take those days when statistics are not actual
// If we want to use data about conversions or profitability, then as a value
// you should specify a number equal to days in the conversion window.
customDateRangeShift: 0,
// Added match types (BROAD, PHRASE, EXACT). Leave only the ones you want on the list.
targetMatchType: [
'BROAD',
'PHRASE',
'EXACT'
],
}
}Select campaigns that are match for analysis:
function main() {
ensureAccountLabels(); // Checking and creating labels
var campaignQuery = 'SELECT ' +
'campaign.name ' +
'FROM campaign ' +
'WHERE campaign.advertising_channel_type = "SEARCH" ' +
'AND campaign.status != "REMOVED" ' +
'AND metrics.impressions > ' + CONFIG().customDaysInDateRange + ' ' +
'AND segments.date BETWEEN "' + customDateRange('from') + '" AND "' + customDateRange('to') + '"';
var campaignResult = AdsApp.search(campaignQuery, {
apiVersion: 'v8'
});
while (campaignResult.hasNext()) {
var campaign_row = campaignResult.next(),
campaign_name = campaign_row.campaign.name;
if (campaign_row) {
adGroupReport(campaign_name); // creating keywords
}
}
}In them, we select ad groups with sufficient statistics:
function adGroupReport(campaign_name) {
var adGroupSelector = AdsApp.adGroups()
.withCondition('Impressions > ' + CONFIG().customDaysInDateRange)
.withCondition('CampaignName = "' + campaign_name + '"')
.withCondition('Status != REMOVED')
.forDateRange('LAST_30_DAYS')
.orderBy('Cost DESC');
var adGroupIterator = adGroupSelector.get();
while (adGroupIterator.hasNext()) {
var ad_group = adGroupIterator.next(),
ad_group_id = ad_group.getId(),
ad_group_name = ad_group.getName(),
campaign_name = ad_group.getCampaign().getName(),
campaign_id = ad_group.getCampaign().getId();
Logger.log('Campaign: ' + campaign_name + ', Ad Group: ' + ad_group_name);
buildNewKeywords(ad_group_id, campaign_id);
Logger.log('-----------------------------------------------------------------------------------------');
}
}And already in the an groups themselves we generate reports, on the basis of which new phrases will be created. We join reports on search queries from Ads and Analytics and check for conflicts with negative keywords.
function buildNewKeywords(ad_group_id, campaign_id) {
var allNegativeKeywordsList = getNegativeKeywordForAdGroup(),
// Negative keywords collected from all levels (group, campaign, lists)
google_ads_queries = getSearchQweries(),
// search queries according to Google Ads
google_analytics_queries = getGaReport(AdsApp.currentAccount().getCustomerId().replace(/\-/gm, ''), CONFIG().gaProfileId);
// search queries based on Google Analytics data
var full_queries_list = google_ads_queries.concat(google_analytics_queries);
full_queries_list = unique(full_queries_list).sort();
addingKeywords(full_queries_list); // Add new keywords
function addingKeywords(arr) {
var adGroupIterator = AdsApp.adGroups()
.withCondition('CampaignId = ' + campaign_id)
.withCondition('AdGroupId = ' + ad_group_id)
.get();
while (adGroupIterator.hasNext()) {
var adGroup = adGroupIterator.next();
for (var k = 0; k < arr.length; k++) {
if (checkNegativeKeywords(arr[k]) != false) { // check for intersection with negative keywords
var match_types = CONFIG().targetMatchType;
for (var m = 0; m < match_types.length; m++) {
if (match_types[m] == 'BROAD') {
var new_key = arr[k];
}
if (match_types[m] == 'PHRASE') {
var new_key = '"' + arr[k] + '"';
}
if (match_types[m] == 'EXACT') {
var new_key = '[' + arr[k] + ']';
}
var keywordOperation = adGroup.newKeywordBuilder()
.withText(new_key)
.build();
if (keywordOperation.isSuccessful()) { // Get results
keyword.pause();
keyword.applyLabel(customDateRange('now').toString());
keyword.applyLabel(CONFIG().scriptLabel);
Logger.log('Added: ' + new_key.toString());
} else {
var errors = keywordOperation.getErrors();
// Error correction
}
}
}
}
}
}
function getSearchQweries() {
var report = [];
var search_term_query = 'SELECT ' +
'search_term_view.search_term, ' +
'metrics.impressions ' +
'FROM search_term_view ' +
'WHERE search_term_view.status NOT IN ("ADDED", "ADDED_EXCLUDED", "EXCLUDED") ' +
'AND campaign.id = ' + campaign_id + ' ' +
'AND ad_group.id = ' + ad_group_id + ' ' +
'AND metrics.impressions >= ' + CONFIG().customDaysInDateRange + ' ' +
'AND segments.date BETWEEN "' + customDateRange('from') + '" AND "' + customDateRange('to') + '"';
var search_term_result = AdsApp.search(search_term_query, {
apiVersion: 'v8'
});
while (search_term_result.hasNext()) {
var search_term_row = search_term_result.next();
var search_term = search_term_row.searchTermView.searchTerm.toLowerCase().trim();
var impressions = search_term_row.metrics.impressions;
if (search_term.split(' ').length <= 7) {
report.push(search_term);
}
}
report = unique(report).sort();
return report;
}
function getGaReport(id, profile_id) {
var report = [];
var today = new Date(),
start_date = new Date(today.getTime() - (CONFIG().customDaysInDateRange + CONFIG().customDateRangeShift) * 24 * 60 * 60 * 1000),
end_date = new Date(today.getTime() - CONFIG().customDateRangeShift * 24 * 60 * 60 * 1000),
start_formatted_date = Utilities.formatDate(start_date, 'UTC', 'yyyy-MM-dd'),
end_formatted_date = Utilities.formatDate(end_date, 'UTC', 'yyyy-MM-dd');
var table_id = 'ga:' + profile_id;
var metric = 'ga:impressions';
var options = {
'samplingLevel': 'HIGHER_PRECISION',
'dimensions': 'ga:keyword,ga:adMatchedQuery',
'sort': '-ga:impressions',
'filters': 'ga:adwordsCustomerID==' + id + ';ga:adKeywordMatchType!=Exact;ga:impressions>' + CONFIG().customDaysInDateRange + ';ga:adwordsAdGroupID==' + ad_group_id + ';ga:adwordsCampaignID==' + campaign_id,
'max-results': 10000
};
var ga_report = Analytics.Data.Ga.get(table_id, start_formatted_date, end_formatted_date, metric, options);
if (ga_report.rows) {
for (var i = 0; i < ga_report.rows.length; i++) {
var ga_row = ga_report.rows[i];
var keyword = ga_row[0].replace(/\+/gm, '').toLowerCase().trim(),
ad_matched_query = ga_row[1].toLowerCase().trim();
if (keyword != ad_matched_query) {
if (ad_matched_query.split(' ').length <= 7) {
report.push(ad_matched_query);
}
}
}
} else {
Logger.log('No rows returned.');
}
report = unique(report).sort();
return report;
}
function getNegativeKeywordForAdGroup() { // Get negative keywords from the group
var fullNegativeKeywordsList = [];
var adGroupIterator = AdsApp.adGroups()
.withCondition('CampaignId = ' + campaign_id)
.withCondition('AdGroupId = ' + ad_group_id)
.get();
if (adGroupIterator.hasNext()) {
var adGroup = adGroupIterator.next();
var adGroupNegativeKeywordIterator = adGroup.negativeKeywords()
.get();
while (adGroupNegativeKeywordIterator.hasNext()) {
var adGroupNegativeKeyword = adGroupNegativeKeywordIterator.next();
fullNegativeKeywordsList[fullNegativeKeywordsList.length] = adGroupNegativeKeyword.getText();
}
}
var negativesListFromCampaign = getCampaignNegatives(campaign_id);
fullNegativeKeywordsList = fullNegativeKeywordsList.concat(fullNegativeKeywordsList, negativesListFromCampaign).sort();
return fullNegativeKeywordsList;
function getCampaignNegatives(campaign_id) { // Get negative keywords from the campaign
var campaignNegativeKeywordsList = [];
var campaignIterator = AdsApp.campaigns()
.withCondition('CampaignId = ' + campaign_id)
.get();
if (campaignIterator.hasNext()) {
var campaign = campaignIterator.next();
var negativeKeywordListSelector = campaign.negativeKeywordLists() // Getting negative keywords from lists
.withCondition('Status = ACTIVE');
var negativeKeywordListIterator = negativeKeywordListSelector.get();
while (negativeKeywordListIterator.hasNext()) {
var negativeKeywordList = negativeKeywordListIterator.next();
var sharedNegativeKeywordIterator = negativeKeywordList.negativeKeywords().get();
var sharedNegativeKeywords = [];
while (sharedNegativeKeywordIterator.hasNext()) {
var negativeKeywordFromList = sharedNegativeKeywordIterator.next();
sharedNegativeKeywords[sharedNegativeKeywords.length] = negativeKeywordFromList.getText();
}
campaignNegativeKeywordsList = campaignNegativeKeywordsList.concat(campaignNegativeKeywordsList, sharedNegativeKeywords);
}
var campaignNegativeKeywordIterator = campaign.negativeKeywords().get();
while (campaignNegativeKeywordIterator.hasNext()) {
var campaignNegativeKeyword = campaignNegativeKeywordIterator.next();
campaignNegativeKeywordsList[campaignNegativeKeywordsList.length] = campaignNegativeKeyword.getText();
}
}
campaignNegativeKeywordsList = campaignNegativeKeywordsList.sort();
return campaignNegativeKeywordsList;
}
}
function checkNegativeKeywords(keywordForCheck) { // this is some ancient piece, I won't touch it
function checkingNegativeKeywords() {
var result = true;
function checkResult(check) {
if (check != true) {
result = false;
}
}
allNegativeKeywordsList.forEach(
function (negativeKeyword) {
var negativeWord = negativeKeyword.toString().toLowerCase();
var clearedNegativeKeyword = negativeWord.replace(/\+/g, '').replace(/\"/g, '');
if ((keywordForCheck.indexOf('[') != -1) || (keywordForCheck.indexOf('"') != -1)) { // negative keyword with exact or phrase match
if (negativeWord == keywordForCheck) {
checkResult(false);
// Logger.log('(1) Phrase: ' + keywordForCheck + ' Negative keyword: ' + negativeWord);
} else if (negativeWord.indexOf('[') == -1) {
if (clearedNegativeKeyword.indexOf(' ') != -1) {
if (keywordForCheck.indexOf(clearedNegativeKeyword) != -1) {
// the cleaned negative keyword is in the keyword, but the negative keyword is not exactly or broadly matched
checkResult(false);
// Logger.log('(2) Phrase: ' + keywordForCheck + ' Negative keyword: ' + negativeWord);
}
} else {
var words = [];
words = keywordForCheck.toLowerCase().replace(/\+/g, '').replace(/\"/g, '').replace(/\[/g, '').replace(/\]/g, '').split(' '); // split the key phrase into words
// Logger.log(words);
words.forEach(
function (word) {
if (negativeWord == word) { // check if the negative phrase (word) matches the words in the key phrase
checkResult(false);
// Logger.log('(3) Phrase: ' + keywordForCheck + ' Negative keyword: ' + negativeWord);
}
}
);
}
}
} else { // negative broad match keyword
if (negativeWord.indexOf(' ') != -1) {
var negativeWords = [];
negativeWords = negativeWord.replace(/\+/g, '').replace(/\"/g, '').replace(/\[/g, '').replace(/\]/g, '').split(' ');
var words = [];
words = keywordForCheck.toLowerCase().replace(/\+/g, '').replace(/\"/g, '').replace(/\[/g, '').replace(/\]/g, '').split(' '); // split the keyword into words
// Logger.log(words);
Array.prototype.diff = function (a) {
return this.filter(function (i) {
return !(a.indexOf(i) > -1);
});
};
var diffWords = negativeWords.diff(words);
if (diffWords.length == 0) {
checkResult(false);
// Logger.log('(4) Фраза: ' + keywordForCheck + ' Минус-фраза: ' + negativeWord);
}
} else {
var words = [];
words = keywordForCheck.toLowerCase().replace(/\+/g, '').replace(/\"/g, '').replace(/\[/g, '').replace(/\]/g, '').split(' '); // split the keyword into words
// Logger.log(words);
words.forEach(
function (word) {
if (negativeWord == word) { // check the match of the negative phrase (word) with the words in the key phrase
checkResult(false);
// Logger.log('(5) Phrase: ' + keywordForCheck + ' Negative keyword: ' + negativeWord);
}
}
);
}
}
}
);
return result;
}
return checkingNegativeKeywords();
}
}The script marks the added words with its label — Keyword Builder, as well as a label with the current date. This helps to understand who added this key and when. Keys are added during a pause, since everything that happens in an advertising campaign is the responsibility of a specialist, he must review them and turn them on himself.