AdWords
3.9K members online now
3.9K members online now
Dive into advanced features like Remarketing, Flexible Bid Strategies, AdWords Editor, and AdWords Scripts
Guide Me
star_border
Reply

MCC script

[ Edited ]
Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭
Hi, I'm new to scripts and I'm trying to make a script that collects all data (AccountDescriptiveName, CampaignName, AdGroupName, AdGroupId, KeywordTextMatchingQuery, Clicks, Impressions, CTR, AveragePosition) of last month of all my clients that are inside my MCC account and put them on different spreadsheets.
Until now I have this code:
 
function main() {
  var accountIterator = MccApp.accounts().get();

  var spreadsheet = SpreadsheetApp.openById("xxxxxxxxxxxxxxxxxx");

  var mccAccount = AdWordsApp.currentAccount();
  var i=0;
  while (accountIterator.hasNext()) {
    var account = accountIterator.next();
    // Switch to the account you want to process.
    MccApp.select(account);

    var report = AdWordsApp.report("Select AccountDescriptiveName, CampaignName, AdGroupName, AdGroupId, KeywordTextMatchingQuery, Clicks, Impressions, Ctr, AveragePosition from CAMPAIGN_PERFORMANCE_REPORT where Impressions > 0 during LAST_MONTH");

    var sheet = spreadsheet.getActiveSheet();
    //Name sheet by account name
    if(i == 0){
      spreadsheet.renameActiveSheet("Report "+account.getName());
    }
    else if(i != 0 && !spreadsheet.getSheetByName("Report "+account.getName())){
      spreadsheet.insertSheet("Report "+account.getName());
    }
    else if(i != 0 && spreadsheet.getSheetByName("Report "+account.getName())){
      spreadsheet.setActiveSheet(spreadsheet.getSheets()[i]);
    }
    
    report.exportToSheet(spreadsheet.getActiveSheet());
    i++;

    var range = sheet.getRange("A2:F30");
    range.sort(6);
  }
}
 
Is there something or somebody that can help me to achieve this please...
 
Sorry for my English is not my native language
2 Expert replyverified_user
1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Diseño N
May 2016

Re: MCC script

Rising Star
# 4
Rising Star

Hi @Diseño N

The script you're using actually does what you described, except for 1 issue:

 

You're collecting the data from CAMPAIGN_PERFORMANCE_REPORT, but this report does not contains some of the statistics you mentioned, like AdGroupName, AdGroupId, KeywordTextMatchingQuery. The full list of your desired statistics are found in the SEARCH_QUERY_PERFORMANCE_REPORT.

You could run your script and just change the report source:

    var report = AdWordsApp.report("Select AccountDescriptiveName, CampaignName, AdGroupName, AdGroupId, KeywordTextMatchingQuery, Clicks, Impressions, Ctr, AveragePosition from SEARCH_QUERY_PERFORMANCE_REPORT where Impressions > 0 during LAST_MONTH");

or you could adjust the fields to ones that are under CAMPAIGN_PERFORMANCE_REPORT. You can find the full list here:
https://developers.google.com/adwords/api/docs/appendix/reports/campaign-performance-report

 

 

Another side note:
If you have too many accounts in your MCC, you could hit the spreadsheet limit of 2,000,000 cells.

The simple math is:
1 sheet contains 26,000 cells (A-Z columns * 1000 rows).
26,000 * 77 = 2,002,000
So you can have 76 sheets, and this is assuming that reports are not adding rows to the sheets over the original 1000.

In the case you do have more accounts than that, you could bypass the problem:
You could wrap the spreadsheet actions under a condition to exclude sheets creation of inactive accounts (which will be created in your current script), or deleting all cells in a new created sheet, except 1 cell.

Hope that helps.

View solution in original post

Re: MCC script

Top Contributor
# 2
Top Contributor

This is just to ping @Jon_Gritton, our ultimate expert in scripts.

Re: MCC script

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭
Thanks Lakatos!
Marked as Best Answer.
Solution
Accepted by topic author Diseño N
May 2016

Re: MCC script

Rising Star
# 4
Rising Star

Hi @Diseño N

The script you're using actually does what you described, except for 1 issue:

 

You're collecting the data from CAMPAIGN_PERFORMANCE_REPORT, but this report does not contains some of the statistics you mentioned, like AdGroupName, AdGroupId, KeywordTextMatchingQuery. The full list of your desired statistics are found in the SEARCH_QUERY_PERFORMANCE_REPORT.

You could run your script and just change the report source:

    var report = AdWordsApp.report("Select AccountDescriptiveName, CampaignName, AdGroupName, AdGroupId, KeywordTextMatchingQuery, Clicks, Impressions, Ctr, AveragePosition from SEARCH_QUERY_PERFORMANCE_REPORT where Impressions > 0 during LAST_MONTH");

or you could adjust the fields to ones that are under CAMPAIGN_PERFORMANCE_REPORT. You can find the full list here:
https://developers.google.com/adwords/api/docs/appendix/reports/campaign-performance-report

 

 

Another side note:
If you have too many accounts in your MCC, you could hit the spreadsheet limit of 2,000,000 cells.

The simple math is:
1 sheet contains 26,000 cells (A-Z columns * 1000 rows).
26,000 * 77 = 2,002,000
So you can have 76 sheets, and this is assuming that reports are not adding rows to the sheets over the original 1000.

In the case you do have more accounts than that, you could bypass the problem:
You could wrap the spreadsheet actions under a condition to exclude sheets creation of inactive accounts (which will be created in your current script), or deleting all cells in a new created sheet, except 1 cell.

Hope that helps.