Count of All Campaigns Across MCC (with conditions)

[James Williams]

Greetings,

I am new to scripts and I am learning as quickly as I can with the developers.google.com site and other tutorial videos. I have had some success implementing basic scripts, but I don't yet understand how to combine multiple things with conditions. I thought I might try posting here and see if I can get a response. Thanks in advance for your help.

I work at an ad agency, and we would like to be able to run a script at the MCC level that exports information to a Google Sheets document that can be shared. I know this is possible, I'm just not sure how to put it all together. 

Goal: Count of all campaigns over the last 30 days within our MCC except for the following criteria:

* exclude account called "Sandbox"

* exclude all accounts under MCC called "Yellow7"

* exclude accounts that begin with the label "z"

* exclude accounts with 0 impressions or clicks

The three pieces/steps I need are

* to scan the MCC and pull all accounts

* to scan all accounts and find the campaigns (and count them if possible)

* to export this to google sheets

I could also just total it in Google Sheets as well. 

Thanks again for any help,

James Williams

[Vincent Racaza (AdWords Scripts Team)]

Hi James,

Your use case is possible in AdWords Scripts, but then, you need to take note the conditions that you can only add in the MccApp.ManagedAccountSelector. With these conditions, below are the things that you can do in your script:

• For excluding account called "Sandbox" and all accounts under MCC called "Yellow7", you can use the "ManagerCustomerId NOT_IN []" condition. You just need to take note the CIDs of "Sandbox" and "Yellow7".
• For excluding account that begins with label "z", we will use the "LabelNames" column as a filter for this. However, LabelNames is of type array, and you can only have two operators (CONTAINS, DOES_NOT_CONTAINS) for array.
• For excluding accounts with 0 impressions and clicks, you can use the Impressions and Clicks columns.

Below is the sample script which would get all campaigns in your MCC based on your conditions:

function main() {

  //Edit the conditions below based on your requirements
  var accountIterator = MccApp.accounts()
         .withCondition("ManagerCustomerId NOT_IN ['XXX-XXX-XXXX', 'XXX-XXX-XXXX']")
         .withCondition("LabelNames DOES_NOT_CONTAIN 'z_sample_label'")
         .withCondition("Impressions != 0")
         .withCondition("Clicks != 0")
         .get();
  
  var spreadsheet = SpreadsheetApp.openByUrl('INSERT_SPREADSHEET_URL_HERE');
  var sheet = spreadsheet.getSheetByName("INSERT_SHEET_NAME_HERE");
  
  sheet.clear();
  sheet.appendRow(['CID','Account Name','Campaign Name']);
  var total = 0;
  
  while (accountIterator.hasNext()) {
    var account = accountIterator.next();
    MccApp.select(account);
    
    var campaignIterator = AdWordsApp.campaigns().get();
    total += campaignIterator.totalNumEntities();
    
    Logger.log("Total number of campaigns for " + account.getCustomerId() + " " + account.getName() + " is " + campaignIterator.totalNumEntities());
    
    while (campaignIterator.hasNext()) {
      var campaign = campaignIterator.next();
      sheet.appendRow([account.getCustomerId(), account.getName(), campaign.getName()]);
    }
  }
  sheet.appendRow(['','Total: ', total]);
}

You can Preview the script above to check on possible changes. Also, you can edit the script to suit your additional requirements.

Let me know if this helps.

Thanks,

Vincent Racaza

AdWords Scripts Team

[James Williams]

Vincent,

Thank you for your quick reply. I greatly appreciate it. I ran the script with the impressions and clicks condition, and was able to populate all of the accounts. Now, I just need to filter them by name. I mis-spoke when I said the word "label z". We actually do not have a label named "z". I meant to say that the account name or campaign name began with the letter z. That is how we are internally telling ourselves that the account is inactive. There are also campaigns that begin with the letter z, so we know those campaigns are inactive. So, for example, an active account may be called Company 3, and when it becomes inactive, we would label it with either an upper or lower case "z" like this: 

Company 1

Company 2

z - Company 3 (or Z - Company 3)

There also may be an active account, let's say it's called Company 1, with an inactive campaign like this: 

Company 1

   campaign 1

   campaign 2 

   z - campaign 3  

So, I put .withCondition("Name DOES_NOT_CONTAIN_IGNORE_CASE 'Z'") at the account iterator and campaign iterator levels, and that worked. So, the last issue I have is being able to filter out accounts by ID

The problem that I am having is with the first part of the code dealing with the filtering here; 

function main() {

  //Edit the conditions below based on your requirements

  var accountIterator = MccApp.accounts()

         .forDateRange("LAST_30_DAYS")

         .withCondition("ManagerCustomerId NOT_IN ['XXX-XXX-XXXX', 'XXX-XXX-XXXX']")

         .withCondition("Name DOES_NOT_CONTAIN_IGNORE_CASE 'Z'")

         .withCondition("Impressions != 0")

         .withCondition("Clicks != 0")

         .get();, 

When I put an ID in the code, it tells me that it can't connect to AdWords and to try again later. I need a way to filter out accounts with an ID or an MCC with an ID that has accounts under it (this would be best since accounts may come and go under this MCC).

Thanks for your help,

James Williams

[Anthony Madrigal]

Hi James,

If you want to filter by the client account Ids, not MCC Ids, you can use withCondition("ExternalCustomerId NOT_IN ['XXX-XXX-XXXX', 'XXX-XXX-XXXX']").

Please let know if you still face issues.

Regards,

Anthony

AdWords Scripts Team

[James Williams]

Anthony,

Thank you for your reply. Yes, that took care of the issue. The last issue is this. We have our top-level MCC. Underneath that, we have child MCC's that have accounts within them. We want to exclude a child MCC and all accounts under them, without listing each account individually. Can I just tell the script to find the child MCC and exclude all accounts within it? If a new account comes in under the child MCC that we want to exclude, I don't want to have to go and manually paste the code back into the script. I want this to be hands off. Just hit the button and the results go to Google Sheets. Here's the example:

SMG MCC (Top-Level MCC. Our agency & everything under it) (CID#)

   * account 1                  (CID##)

   * account 2...etc          (CID##)

  - Sample Child MCC      (CID##)

    * account 1                 (CID##)

    * account 2 ...etc.        (CID##)

  - Sample 2 Child MCC    (CID##)

    * account 1                  (CID##)

    * account 2  ...etc.       (CID##)

  - Sample 3 MCC            (CID##) (exclude this and all accounts under)

     * account 1                 (CID##)

     * account 2...etc.        (CID##)

  

Thanks,

James Williams

[Anthony Madrigal]

Hi James,

If you want to filter by sub MCCs, you should use the withCondition for the field ManagerCustomerId. If you want to filter by client accounts, you should use ExternalCustomerId.

If you are facing issues or errors, please reply privately to author your MCC CID and script name so that I can have a look.

Thanks,

Anthony

AdWords Scripts Team

[James Williams]

Anthony,

I understand what you are saying. I read the following in the help section:

ManagerCustomerId

Account ID

withCondition("ManagerCustomerId IN ['123-456-7890']") or withCondition("ManagerCustomerId IN [1234567890]"). Used to select child accounts belonging to a specific submanager.

However, when I put this in the script, I get an error back that says it failed to read from AdWords. I am asking for approval from my director to give out the MCC CID. If I get it, I will reply. I don't know why this part is not working.

James Williams

[Anthony Madrigal]

Hi James,

I could take a look at your script and see why this is occurring. You could provide me with your MCC CID (where the script is located) and script name through reply privately to author so I can do further investigation.

Regards,

Anthony

AdWords Scripts Team

[James Williams]

Anthony,

A new issue has come up. Now that the code is working (though still has some issues that we discussed privately today), I manually checked the accounts and campaigns that the script pulled against what we have in AdWords. I found one place where it pulled a campaign that I cannot see in AdWords, and I also see that it will not pull YouTube or Gmail or some Remarketing campaigns. Do you know why this might be?

James Williams

[AussieWeb Conversion]

Hi James

A note on trying to use


 .withCondition("ManagerCustomerId IN ['111-222-3333']")
 .withCondition("ManagerCustomerId NOT_IN ['123-456-7890']")

to exclude all accounts in a sub-MCC under the main MCC you do want.  IT DOES NOT WORK!

eg if you have this structure and you want all accounts under MCC1 but not those under subMCC2
MCC1 111-222-3333
 - account1
 - account2
 - subMCC1 987-654-3210
   - account3
 - subMCC2 123-456-7890
   - account4
   - account5

@Anthony If you want private access to my script whereby I keep testing this since I really want it to work, let me know.

Regards
Nigel

[Anthony Madrigal]

Hi guys,

@Nigel, thanks for pointing this out. Our team have recently been aware of this issue and are working on a fix.

@James, could you please reply privately with more details (such as the campaign name/Id) on this new issue so we can further discuss from there?

Thanks,

Anthony

AdWords Scripts Team

[AussieWeb Conversion]

Hi Anthony

Would you mind emailing me when a fix is rolled out?
I've been waiting on this for 3 years now. I requested this fix back in April 2014.
https://groups.google.com/forum/#!topic/adwords-scripts/UzIeAHgkWLM

Regards
Nigel

[Anthony Madrigal]

Hi Nigel,

No problem. I'll be sure to do that once there is a fix.

Regards,

Anthony

AdWords Scripts Team