Script to identify Ad groups without any active RSA's

[Søren Jespersen]

We want to make a script that finds ad groups without any RSA's and and send them to a sheet.

The accounts are quite bid so we have tried to make some using GAQL. We have made 2 queries, but don't know how to combine them.

Query 1 - listing down all ad groups per campaign in the account, given conditions in Where clause

SELECT

ad_group.name,

campaign.name

FROM ad_group

WHERE

campaign.experiment_type = 'BASE'

AND campaign.status = 'ENABLED'

AND campaign.advertising_channel_type = 'SEARCH'

AND ad_group.status = 'ENABLED'

AND ad_group.name NOT LIKE '*DSA*'

QUERY 2 - listing down all ad groups with enabled ads per campaign

SELECT

campaign.name,

ad_group.name

FROM ad_group_ad

WHERE

campaign.experiment_type = 'BASE'

AND campaign.status = 'ENABLED'

AND campaign.advertising_channel_type = 'SEARCH'

AND ad_group.status = 'ENABLED'

AND ad_group.name NOT LIKE ‘DSA’

AND ad_group_ad.ad.type = 'RESPONSIVE_SEARCH_AD'

AND ad_group_ad.status = 'ENABLED'

Task: JOIN the 2 tables together in order to find enabled ad groups with missing ads.

From the initial research we found out that GAQL does not support join functions. In SQL this operation would allow us to quickly find enabled ad groups without any ads. LEFT JOIN in SQL would give us empty fields on the left.

What would be the way to do that? Is there a way to run 2 queries in the function script and then make a join of them? 

OR is there any column in “ad_group” table, that would allow us to create a query, where we list all ad groups the non existing ads. We have run through the list of available fields and nothing brought our attention.

Also is it possible to get the entire columns of the query list at once instead of just row by row, since it takes a very long time.

  

[Sigurd Fabrin]

Hi Søren,

If you reverse your two queries and exclude the IDs from Q1 in Q2 you should be good

Something like this approach

function main() {  
  let q1 =
    'SELECT '+
    'ad_group.id '+
    'FROM '+
    'ad_group_ad '+
    'WHERE '+
    'ad_group_ad.status = "ENABLED" '+
    'AND ad_group.status = "ENABLED" '+
    'AND campaign.status = "ENABLED" '+
    'AND ad_group_ad.ad.type = "RESPONSIVE_SEARCH_AD"';  
  let r1 = AdsApp.search(q1);
  let adGroupIds = [];
  while (r1.hasNext()) {
    let row = r1.next();
    adGroupIds.push(row.adGroup.id)
  }  
  let q2 =
    'SELECT '+
    'ad_group.id, '+
    'ad_group.name, '+
    'campaign.name '+
    'FROM '+
    'ad_group '+
    'WHERE '+
    'ad_group.id NOT IN ('+adGroupIds.join(',')+') '+ // join can be used js style
    'AND ad_group.status = "ENABLED" '+
    'AND campaign.status = "ENABLED" '+
    'AND ad_group.type = "SEARCH_STANDARD"'; // only search & skip DSA
  let r2 = AdsApp.search(q2);
  let arr = [];
  while (r2.hasNext()) {        
    let row = r2.next();      
    arr.push(['Campaign: "'+row.campaign.name+'"',' ID: '+row.adGroup.id,' adGroup: "'+row.adGroup.name+'"']);
  }
  console.log('Found '+arr.length+' adGroups without RSAs:\n\n'+arr.join('\n'));
}

Cheers

Sigurd

[Nils Rooijmans]

Hi  Søren  ,

here's  a script that creates a Google Sheet reporting ad groups that have less than the required number of Respsonsive Search Ads (RSAs).

If there is at least one ad group that has too little RSA’s the script will send an email with a link to the report.

https://nilsrooijmans.com/google-ads-script-report-ad-groups-that-dont-have-rsas/  

Hope this helps the readers over here,

Nils Rooijmans
https://nilsrooijmans.com

See my Google Ads Scripts FAQ to avoid the same mistakes I made: https://nilsrooijmans.com/google-ads-scripts-faq/ 

[Google Ads Scripts Forum Advisor]

Hi,

 

@Sigurd - We appreciate you for continuously sharing your insights to our members.

 

@Søren - Kindly try Sigurd's recommendation and let us know if you have any questions or encounter any issues on your end.

 

Regards,

 

Yasmin Gabrielle Google Ads Scripts Team

ref:_00D1U1174p._5004Q2dp0cw:ref