Search query analysis

/* README BEFORE EXECUTING THIS SCRIPT. Note the line numbers may change if you delete a line.																									
* Recommend to leave the line blank to avoid confusion. Modify this script only after copying to your local account.																									
* Modify the lines 9-21 as per the requirement of the account.																									
*																									
* FUNCTIONALITY: Compares the Search Queries with Keywords and provides analysis report with keyword recommendations.																									
*/																									
																									
//SPREADSHEET_URL of the google sheet created from the respective account																									
var SPREADSHEET_URL = 'Enter the Spreadsheet url of the account';																									
																									
//Change this variable to true if you want to receive email notification after the script is run and change it to false if email notification is not required																									
var RECEIVE_EMAIL_NOTIFICATION=true;																									
																									
//Email address to be edited by the user, for multiple emails addresess give them in comma separated																									
var RECIPIENT_EMAIL = 'Enter your email id';																									
																									
//Replace with one of these values: TODAY, YESTERDAY, LAST_7_DAYS, THIS_WEEK_SUN_TODAY, THIS_WEEK_MON_TODAY, LAST_WEEK, LAST_14_DAYS, LAST_30_DAYS, LAST_WEEK, LAST_BUSINESS_WEEK, LAST_WEEK_SUN_SAT, THIS_MONTH, LAST_MONTH. Do not change the format.																									
var DATE_RANGE_DURATION = 'LAST_30_DAYS';																									
																									
//Change the number of impressions of search queries that should be considered.																									
var IMPRESSION_THRESHOLD=10;																									
																									
//DO NOT CHANGE (To be changed by the developer on any change request)																									
var VERSION_NUMBER=1.2;																									
																									
var REPORTING_OPTIONS = {																									
// Comment out the following line to default to the latest reporting version.																									
apiVersion: 'v201809'																									
};																									
																									
// Name of the specific sheet in the spreadsheet.																									
var ZERO_SHEET_NAME = "Search Queries Analysis Instructions";																									
var FIRST_SHEET_NAME = "Matched Queries with Exact MT";																									
var SECOND_SHEET_NAME = "Matched Queries Not in Exact MT";																									
var THIRD_SHEET_NAME = "New Keyword Opportunity";																									
var FOURTH_SHEET_NAME = "Non Converting Search Queries";																									
var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);																									
																									
																									
																									
function main() {																									
getSqrReportAnalysis();																									
}																									
																									
function getSqrReportAnalysis(){																									
var now = new Date();																									
var sheet0 = ss.getSheetByName(ZERO_SHEET_NAME);																									
if(sheet0!=null){																									
sheet0.clearContents();																									
sheet0.clearFormats();																									
}else{																									
sheet0=ss.insertSheet(ZERO_SHEET_NAME);																									
}																									
sheet0.getRange("A1").setValue("Report Run On:").setFontWeight('bold');																									
sheet0.getRange("B1").setValue(now).setFontWeight('bold');																									
sheet0.getRange("A2").setValue("Sheet 1: Matched Queries with Exact MT").setFontWeight('bold');																									
sheet0.getRange("A3").setValue("Sheet 2: Matched Queries Not in Exact MT").setFontWeight('bold');																									
sheet0.getRange("A4").setValue("Sheet 3: New Keyword Opportunity").setFontWeight('bold');																									
sheet0.getRange("A5").setValue("Sheet 4: Non Converting Search Queries").setFontWeight('bold');																									
sheet0.getRange("B2").setValue("This sheet contains the search queries present in the keyword setup with Exact Match types. These keywords need to be added as negatives to the campaigns and adgroups that triggered these keywords wrongly.");																									
sheet0.getRange("B3").setValue("This sheet contains the search queries already present in the Active Keyword setup but are added with Phrase, Broad, BMM (not Exact) match type/types. These search queries need to be added with Exact match in the Actual ad groups (to improve the average CPC).The bids for the new Exact matches to be determined using the Bids for Phrase/Broad/BMM keywords. Use the New Max CPC column to calculate the bids for the new Exact matches as - 1.5X of Phrase match and 2X of Broad/BMM match. (These are default bids. Ultimately, the campaign manager is to decide what bid to be used)");																									
sheet0.getRange("B4").setValue("This sheet contains all the search queries having generated conversions and are not present in the keyword setup");																									
sheet0.getRange("B5").setValue("This sheet contains all the search queries that didn't generate any conversion at all");																									
var sheet1 = ss.getSheetByName(FIRST_SHEET_NAME);																									
if(sheet1!=null){																									
sheet1.clearContents();																									
sheet1.clearFormats();																									
}else{																									
sheet1=ss.insertSheet(FIRST_SHEET_NAME);																									
}																									
																									
sheet1.getRange("A1").setValue("Search Query").setFontWeight('bold');																									
sheet1.getRange("B1").setValue("Search Query Match Type").setFontWeight('bold');																									
sheet1.getRange("C1").setValue("Trigger Campaign").setFontWeight('bold');																									
sheet1.getRange("D1").setValue("Trigger Adgroup").setFontWeight('bold');																									
sheet1.getRange("E1").setValue("Actual Campaign").setFontWeight('bold');																									
sheet1.getRange("F1").setValue("Actual Adgroup").setFontWeight('bold');																									
sheet1.getRange("G1").setValue("Max Cpc").setFontWeight('bold');																									
sheet1.getRange("H1").setValue("Impressions").setFontWeight('bold');																									
sheet1.getRange("I1").setValue("Clicks").setFontWeight('bold');																									
sheet1.getRange("J1").setValue("Orders").setFontWeight('bold');																									
sheet1.getRange("K1").setValue("Revenue").setFontWeight('bold');																									
sheet1.getRange("L1").setValue("Cost").setFontWeight('bold');																									
var sheet2 = ss.getSheetByName(SECOND_SHEET_NAME);																									
if(sheet2!=null){																									
sheet2.clearContents();																									
sheet2.clearFormats();																									
}else{																									
sheet2=ss.insertSheet(SECOND_SHEET_NAME);																									
}																									
sheet2.getRange("A1").setValue("Search Query").setFontWeight('bold');																									
sheet2.getRange("B1").setValue("Search Query Match Type").setFontWeight('bold');																									
sheet2.getRange("C1").setValue("Trigger Campaign").setFontWeight('bold');																									
sheet2.getRange("D1").setValue("Trigger Adgroup").setFontWeight('bold');																									
sheet2.getRange("E1").setValue("Actual Campaign").setFontWeight('bold');																									
sheet2.getRange("F1").setValue("Actual Adgroup").setFontWeight('bold');																									
sheet2.getRange("G1").setValue("Max Cpc").setFontWeight('bold');																									
sheet2.getRange("H1").setValue("Impressions").setFontWeight('bold');																									
sheet2.getRange("I1").setValue("Clicks").setFontWeight('bold');																									
sheet2.getRange("J1").setValue("Orders").setFontWeight('bold');																									
sheet2.getRange("K1").setValue("Revenue").setFontWeight('bold');																									
sheet2.getRange("L1").setValue("Cost").setFontWeight('bold');																									
var sheet3 = ss.getSheetByName(THIRD_SHEET_NAME);																									
if(sheet3!=null){																									
sheet3.clearContents();																									
sheet3.clearFormats();																									
}else{																									
sheet3=ss.insertSheet(THIRD_SHEET_NAME);																									
}																									
sheet3.getRange("A1").setValue("Search Query").setFontWeight('bold');																									
sheet3.getRange("B1").setValue("Search Query Match Type").setFontWeight('bold');																									
sheet3.getRange("C1").setValue("Trigger Campaign").setFontWeight('bold');																									
sheet3.getRange("D1").setValue("Trigger Adgroup").setFontWeight('bold');																									
sheet3.getRange("E1").setValue("Impressions").setFontWeight('bold');																									
sheet3.getRange("F1").setValue("Clicks").setFontWeight('bold');																									
sheet3.getRange("G1").setValue("Orders").setFontWeight('bold');																									
sheet3.getRange("H1").setValue("Revenue").setFontWeight('bold');																									
sheet3.getRange("I1").setValue("Cost").setFontWeight('bold');																									
var sheet4 = ss.getSheetByName(FOURTH_SHEET_NAME);																									
if(sheet4!=null){																									
sheet4.clearContents();																									
sheet4.clearFormats();																									
}else{																									
sheet4=ss.insertSheet(FOURTH_SHEET_NAME);																									
}																									
sheet4.getRange("A1").setValue("Search Query").setFontWeight('bold');																									
sheet4.getRange("B1").setValue("Search Query Match Type").setFontWeight('bold');																									
sheet4.getRange("C1").setValue("Trigger Campaign").setFontWeight('bold');																									
sheet4.getRange("D1").setValue("Trigger Adgroup").setFontWeight('bold');																									
sheet4.getRange("E1").setValue("Impressions").setFontWeight('bold');																									
sheet4.getRange("F1").setValue("Clicks").setFontWeight('bold');																									
sheet4.getRange("G1").setValue("Orders").setFontWeight('bold');																									
sheet4.getRange("H1").setValue("Revenue").setFontWeight('bold');																									
sheet4.getRange("I1").setValue("Cost").setFontWeight('bold');																									
																									
																									
var a1 = []; // Search query of which Exact is not a match type and Added/Excluded status is 'None'																									
var report = AdWordsApp.report(																									
"SELECT Query,CampaignName,AdGroupName,QueryMatchTypeWithVariant,Impressions,Clicks,Cost,Conversions,ConversionValue "+																									
"FROM SEARCH_QUERY_PERFORMANCE_REPORT WHERE Impressions>="+IMPRESSION_THRESHOLD+" AND QueryMatchTypeWithVariant NOT_IN ['EXACT','NEAR_EXACT'] and QueryTargetingStatus = NONE "+																									
"DURING "+DATE_RANGE_DURATION);																									
var rows = report.rows();																									
while (rows.hasNext()) {																									
var row = rows.next();																									
a1.push(row);																									
}																									
var b1 = [];																									
var report = AdWordsApp.report(																									
"SELECT Criteria,CampaignName,AdGroupName,KeywordMatchType,CpcBid "+																									
" FROM KEYWORDS_PERFORMANCE_REPORT WHERE Status= ENABLED and AdGroupStatus = ENABLED AND "+																									
" CampaignStatus = ENABLED and IsNegative= FALSE");																									
var rows = report.rows();																									
while (rows.hasNext()) {																									
var row = rows.next();																									
var keywordsLower = row['Criteria'].toLowerCase();																									
b1.push(row);																									
}																									
																									
var bFound = 0;																									
for(var i=0;i<a1.length;i++){																									
bFound = 0;																									
for(var j = 0 ; j < b1.length; j++){																									
if(a1[i]['Query'].toLowerCase() == b1[j]['Criteria'].toLowerCase().replace(/\+/g, "").trim()){																									
if(b1[j]['KeywordMatchType'].toLowerCase()=="exact"){																									
sheet1.appendRow([a1[i]['Query'], a1[i]['QueryMatchTypeWithVariant'], a1[i]['CampaignName'], a1[i]['AdGroupName'], b1[j]['CampaignName'],																									
b1[j]['AdGroupName'],b1[j]['CpcBid'],a1[i]['Impressions'],a1[i]['Clicks'],a1[i]['Conversions'],a1[i]['ConversionValue'],a1[i]['Cost']]);																									
}else{																									
sheet2.appendRow([a1[i]['Query'], a1[i]['QueryMatchTypeWithVariant'], a1[i]['CampaignName'], a1[i]['AdGroupName'], b1[j]['CampaignName'],																									
b1[j]['AdGroupName'],b1[j]['CpcBid'],a1[i]['Impressions'],a1[i]['Clicks'],a1[i]['Conversions'],a1[i]['ConversionValue'],a1[i]['Cost']]);																									
}																									
bFound=1;																									
break;																									
}																									
}																									
if(bFound==0){																									
if(a1[i]['Conversions']>0){																									
sheet3.appendRow([a1[i]['Query'], a1[i]['QueryMatchTypeWithVariant'], a1[i]['CampaignName'], a1[i]['AdGroupName'],																									
a1[i]['Impressions'],a1[i]['Clicks'],a1[i]['Conversions'],a1[i]['ConversionValue'],a1[i]['Cost']]);																									
}else{																									
sheet4.appendRow([a1[i]['Query'], a1[i]['QueryMatchTypeWithVariant'], a1[i]['CampaignName'], a1[i]['AdGroupName'],																									
a1[i]['Impressions'],a1[i]['Clicks'],a1[i]['Conversions'],a1[i]['ConversionValue'],a1[i]['Cost']]);																									
}																									
}																									
}																									
if (RECEIVE_EMAIL_NOTIFICATION) {																									
if(RECIPIENT_EMAIL && RECIPIENT_EMAIL !="Enter your email id"){																									
MailApp.sendEmail(RECIPIENT_EMAIL,'SQR Script Run Completed',SPREADSHEET_URL);																									
}																									
}																									
}

How does the script work?

When running ads, marketers tend to increase ad spend to drive incremental returns. However, sometimes those incremental returns end up hurting the campaign’s overall efficiency and effectiveness by not being truly aligned with the end goals.

The lower target ROAS script filters out data that is not meaningful to the end goal. The script takes into account factors like search query level performance and ad group level 80:20 analysis, as well as ROAS benchmarking by brand, non-brand, and shopping. Smart bidding accounts for some of these factors when optimizing campaigns, but maybe using weak data points to get there. The script ensures that smart bidding algorithms are running on high-quality data to run fully optimize campaigns that further your brand’s end goal.

How do I use it?

All the scripts can be customized to run for MCC / Non-MCC accounts. To run the script:

Copy the script code
Open the Adwords Account (MCC / Non MCC)
Navigate to Bulk Operations > Scripts > Click New > Paste the code
Give a name to the script (we typically recommend using the same name we provided so you can better track the code and its results)

Make sure to edit the Output File Path point to your account drive. Ensure all authorizations that are required are in place so the script can run smoothly. Schedule the script as per the need of your account.

Your Search Query Analysis Report includes:

Matched queries with exact match, which shows the search queries present in the keyword setup with exact match types. These keywords need to be added as negatives to the campaigns and ad groups that triggered these keywords wrongly.
Matched queries not in exact match, which shows the search queries already present in your Active Keyword set up, but added as phrase, broad, BMM (not exact) types. These search queries need to be added with the exact match in your live ad groups, which will improve the average CPC. The bids for the new exact matches will be determined using the bids for phrase, broad, and/or BMM keywords.
- Use the New Max CPC column on your report to calculate the bids for the new exact matches as 1.5X of phrase match and 2X of broad/BMM match. (These are default bids. Ultimately, the campaign manager decides what bid will be used).
New keyword opportunities, which details all the search queries that have generated conversions, but are not present in the keyword setup. Add them to your campaign.
Non-converting search queries, which details all the search queries that did not generate any conversion. Add these as negatives to your campaign.

Search Query Analysis Script

How does the script work?

How do I use it?

Your Search Query Analysis Report includes:

How Can We Help