This script checks top searchterms from shopping, compares them to searchterms from search, sends you email with phrases that are either missing in search or should be pushed more.

This script

• checks top searchterms from shopping
• compares them to searchterms from search
• sends you email with phrases that are
• either missing in search or should be pushed more

🔥

Script logic

Shopping campaigns are amazing source of new searchterms because they can work better with language and people's search behavior than classical search campaigns. Honestly going through shopping searchterms to dig out good keywords should be on the to-do list of every ppc specialist. In what way shopping searchterms are better?

1. Shopping better pairs queries intent.If a customer looks for "perfume ysl" shopping will know it also means "perfume by yves saint laurent" and will show my shopping ads for brand Yves Saint Laurent. Search ads would not do such magic even in close variants.
2. Shopping pairs english words spelled phonetically in azbuka.Super-duper-cool feature if you advertise in Russia, Ukraine, Bulgaria etc.Many people don't switch keyboards to type the name in original way, they expect Google to figure it out, and it does. But only with shopping and DSA campaigns, but not in search. Without those you might miss a loooot of impressions on those markets.
3. Shopping will be your guiding star for a foreign market.When you're running campaigns across multiple countries and languages, it's possible that you don't speak all of them.. duh.. So you hire some translators but they are not ppc specialists and can't work with keywords so well. Shopping - by pulling data from your website through a feed - will show you for synonyms and words combinations your couldn't have even thought off simply because you're not a native.

Hence, even you're limited in time by billable hours from your client or simply need to prioritize the maximum efficiency of your ongoing optimization, this script will basically send you the gemstones you're missing in search keywords without any added research and volume estimations.

How to determine thresholds for the settings

Default script goes though shopping searchterms with

• more than 10 clicks &
• more than 100 conversion value

10 clicks - because i don't want to deal with "random" purchase of 1 click and 1 conversion. I want the top searches that more people buy to radically increase volume of my search campaigns. In some account it will be 10 clicks, in other i'd start with > 100 clicks.

100 conversion value - because again I want to maximize volume. If it's a product that costs 2 eur and makes 50eur revenue a month, even doubling it would not make that much of the difference. I'll get to those, but now - priorities!

Then script goes though search searchterms with

• more than 10 impressions

10 impressions -

a) because i don't want to get a report with 5K+ lines by including 1-10 impressions, i want script to fit into the 30 minutes deadline, which would fit for about 2,5-3K search searchterms. In huge accounts it might make sense to start with 50 impressions as a threshold;

b) because if i have this "good" shopping searchterm as a keywords in search campaign already but it has <10 impressions in 30 days, something is not ok anyway! either my ad is shit, or my bid is way to low, in any case it's something to look into.

Use your brain. You can adjust this script to any size of account, even change the date to "last 7 days" if you have enough data to work with and new trendy terms should be added every week.

Some results

I like to track the added value of the scripts I use when it's possible. In this case I was labeling the keywords I added from the report to see how much money i actually brought for the time spent on writing script and adding those words in the system, before I spend any more time on this ppc optimization activity. It was about 8 days of data and +65K impressions and +1.1K EUR. For account with extremely long conversion lag, I'm happy with result. It's 2018 but "back to basics" focusing on actual keywords still makes sense.

How to set up the script?

1. Copy this file on your google drive. Click File > Make a Copy https://docs.google.com/pla+sea=love
2. In your account click Tools > Scripts, click to add new script.
3. Paste the code below, update the *settings* part so that it makes sense for you.
4. Authorize script and save it.
5. Run it (or schedule if it makes sense for you).

Script code

function main() {


/****************************** settings ******************************/

var email_to = 'email@domain.com';var email_subject = 'Good converting terms from shopping missing in search';var my_spreadsheet = 'PUT HERE URL OF THE GOOGLE SHEET WHOSE COPY YOU MADE';var shopping_campaign_code = 'PUT HERE SHOPPING CAMPAIGNS CODE';      // mostly it's "PLA" or "shopping" etc.var conversion_value_threshold = 100;    // minimum revenue for shopping searchtermsvar clicks_threshold = 20;               // minimum clicks for shopping searchtermsvar impressions_threshold = 10;          // minimum impressions for search searchtermsvar date_range = 'LAST_30_DAYS';         // you can change this to 'LAST_7_DAYS' etc.


/******************************* magic ******************************/

Logger.log("making shopping search term report");

var sheet_shopping = SpreadsheetApp.openByUrl(my_spreadsheet).getSheetByName('shopping');


  sheet_shopping.clearContents();

  var columns = [
 'Query',
 'CampaignName',
 'AdGroupName',
 'Impressions',
 'Clicks',
 'Cost',
 'ConversionValue'
 ];

 sheet_shopping.appendRow(columns);

 var searches = [];
 var columnsStr = columns.join(',') + " ";

 var report_shopping = AdWordsApp.report(
 'SELECT ' + columnsStr +
 ' FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
 ' WHERE' +
 ' Clicks > ' + clicks_threshold +
 ' AND ConversionValue > ' + conversion_value_threshold +
 ' AND CampaignName CONTAINS ' + shopping_campaign_code +
 ' DURING ' + date_range);

 var rows = report_shopping.rows();
 while (rows.hasNext()) {
 var row = rows.next();

 searches.push(row['Query']);


 sheet_shopping.appendRow([ row['Query'], row['CampaignName'], row['AdGroupName'], row['Impressions'], row['Clicks'], row['Cost'], row['ConversionValue']]);


 }

     Logger.log("making search search term report");

var sheet_search = SpreadsheetApp.openByUrl(my_spreadsheet).getSheetByName('search');
sheet_search.clearContents();

  var columns = [
 'Query',
 'CampaignName',
 'AdGroupName',
 'Impressions',
 'Clicks',
 'Cost',
 'ConversionValue'
 ];

 sheet_search.appendRow(columns);

 var searches = [];
 var columnsStr = columns.join(',') + " ";

 var report_search = AdWordsApp.report(
 'SELECT ' + columnsStr +
 ' FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
 ' WHERE' +
 ' Impressions > ' + impressions_threshold +
 ' AND CampaignName DOES_NOT_CONTAIN ' + shopping_campaign_code +
 ' DURING ' + date_range);

 var rows = report_search.rows();
 while (rows.hasNext()) {
 var row = rows.next();

 searches.push(row['Query']);


 sheet_search.appendRow([ row['Query'], row['CampaignName'], row['AdGroupName'], row['Impressions'], row['Clicks'], row['Cost'], row['ConversionValue']]);

 }




Logger.log("checking if email should be sent");



var email_sheet = SpreadsheetApp.openByUrl(my_spreadsheet).getSheetByName('email');
var xdata = email_sheet.getRange("A2:B2").getValues();

var  xrow = 0;
  var xcol = 0;
      if (xdata[xrow][xcol] === "Rows") {
        Logger.log("Nothing to send, all good");
      }
  else
       sendMail();
  Logger.log("email sent");



/****************************** email sending function ******************************/


function sendMail(){
var to_email_sheet = SpreadsheetApp.openByUrl(my_spreadsheet).getSheetByName('email');
var data_input = to_email_sheet.getRange("A1:C1000").getValues();
var data = [i for each (i in data_input)if (isNaN(i))];


var TABLEFORMAT = 'cellspacing="10" cellpadding="0" dir="ltr" border="1" style="width:70%;table-layout:fixed;font-size:10pt;font-family:verdana,sans,sans-serif;border-collapse:collapse;border:0.5px solid #fff;font-weight:normal;color:black;background-color:white;text-align:center;text-decoration:none;font-style:normal;'
var htmltable = '<table ' + TABLEFORMAT +' ">';

for (row = 0; row<data.length; row++){


      htmltable += '<tr>';

for (col = 0 ;col<data[row].length; col++){


  if (data[row][col] === "") {

    htmltable += '</table>';
  }

  else
    if (row === 0)  {

htmltable += '<th>' + data[row][col] + '</th>';

    }



  else {
htmltable += '<td>' + data[row][col] + '</td>';
             }
}
htmltable += '</tr>';

}



   htmltable += '</table>';


Logger.log(data);
     Logger.log(htmltable);

   MailApp.sendEmail(email_to, email_subject,'' ,{htmlBody: htmltable})

}
}