Need Help with Limited By Budget script

[Sreekanth]

Hi.

I got this script from the adword community, which checks for account with limited by budget and send mail 

Can anybody help me to change this script to send just one email with summary of the all the accounts which are Limited By Budget.

var RECEMAIL = "email"; //enter the recipients email address
var CLOSEPER = 0.8; // enter what you consider "close" to daily budget, e.g. 0.8 = 80% of budget

function main() {
//get all the accounts in the MCC
var accounts = MccApp.accounts().get();
//iterate through the accounts & run the campaign checker
while(accounts.hasNext()) {
var thisAccount = accounts.next();
var accountName = thisAccount.getName();
MccApp.select(thisAccount);
checkCampaigns(accountName);
}
}

function checkCampaigns(accountName) {
var campaigns = AdWordsApp.campaigns()
.withCondition('Status = ENABLED')
.get();
//initialise the email body as empty
var campList = "";
while(campaigns.hasNext()) {
var thisCampaign = campaigns.next();
var stats = thisCampaign.getStatsFor("YESTERDAY");
var budget = thisCampaign.getBudget();
var spend = stats.getCost();
var name = thisCampaign.getName();

if(spend > (budget * CLOSEPER)) {
Logger.log("Campaign: " + name + " Budget: " + budget + " Spend: " + spend);
campList += "Campaign: " + name + " Budget: " + budget + " Spend: " + spend + "\n";
}
}

if(campList.length !=0) { //only if there are limited Campaigns, send email
var campList="These Campaigns are close to their Daily Budget \n";
sendEmail(RECEMAIL,campList,accountName);
}
}

function sendEmail(email, body, accountName) {
var subject = "Account '" + accountName + "' - Campaigns close to Budget";
Logger.log("Sending email, subject: " + subject);
//remove the comment "//" below when testing completed
MailApp.sendEmail(email,subject, body);
}

[Jaren Callo (AdWords Scripts Team)]

Hi Sreekanth,

Here's what you can do to send the data in just 1 email:

• do not call sendEmail() in checkCampaigns(). Instead, call sendEmail() after processing all the accounts
• set campList as return value for checkCampaigns. Combine the return values from different account according to how you want to present the summary. Add necessary data in the return value needed in the summary.
• call sendEmail() with the update data and body. You need to update the subject as well.

Thanks,

Jaren P. Callo

AdWords Scripts Team

[Sreekanth]

Hi Jaren,

Thank you for your reply.

I really dont have any idea how to do this because I dont have any knowledge in scripting.

It will be great if you can guide me through this.

Thanks

[Tyler Sidell (AdWords Scripts Team)]

Hi Sreekanth,

Would you be able to send your CID (reply privately to the author) and we'll be able to set up the script for your account?

Thanks,

Tyler Sidell

AdWords Scripts Team

[Sreekanth]

Hi Tyler,

Sorry I am restricted to share you the CID.

Would you able to post the script here.? I will try it out and will give you the feedback.

Thanks

[Tyler Sidell (AdWords Scripts Team)]

Hi Sreekanth,

Based on Jaren's recommendations you could modify your script as follows.  However, on your end you would need to determine which data you want to send in the email, return that data and send that data in the sendEmail function (see Jaren's steps 2 and 3).  The script below will get you started to send one email as originally requested.

var RECEMAIL = "em...@email.com"; //enter the recipients email address

var CLOSEPER = 0.8; // enter what you consider "close" to daily budget, e.g. 0.8 = 80% of budget

function main(campList) {

    //get all the accounts in the MCC
    var accounts = MccApp.accounts().get();
    //iterate through the accounts & run the campaign checker
    while (accounts.hasNext()) {
        var thisAccount = accounts.next();
        var accountName = thisAccount.getName();
        MccApp.select(thisAccount);
        checkCampaigns(accountName);
    }

    sendEmail(RECEMAIL, campList, accountName);

}

function checkCampaigns(accountName) {
    var campaigns = AdWordsApp.campaigns()
        .withCondition('Status = ENABLED')
        .get();
    //initialise the email body as empty
    var campList = "";
    while (campaigns.hasNext()) {
        var thisCampaign = campaigns.next();
        var stats = thisCampaign.getStatsFor("YESTERDAY");
        var budget = thisCampaign.getBudget();
        var spend = stats.getCost();
        var name = thisCampaign.getName();

        if (spend > (budget * CLOSEPER)) {

            Logger.log("Campaign: " + name + " Budget: " + budget + " Spend: " + spend);
            campList += "Campaign: " + name + " Budget: " + budget + " Spend: " + spend + "\n";
        }
    }

    if (campList.length != 0) { //only if there are limited Campaigns, send email
        var campList = "These Campaigns are close to their Daily Budget \n";
    }
 
    return campList;

}

function sendEmail(email, body, accountName) {
    var subject = "Account '" + accountName + "' - Campaigns close to Budget";
    Logger.log("Sending email, subject: " + subject);
    //remove the comment "//" below when testing completed
    MailApp.sendEmail(email, subject, body);
}

Thanks,

Tyler Sidell
AdWords Scripts Team

[Sreekanth]

Hi, Tyler,

Thank you for this script.

I have created a budget proposal sheet. Which can be used to calculate - lost due to budget. Budget proposal sheet is given below.

Is it possible to create a spread sheet report of each and every client which are limited by budget and I want these reports to be in a single email.

Please see the screen shot attached

Here I only need acc id, account name and Full report column.

In the full reports column I need lost due to budget sheet url for each client.

Please see the report template given below.

https://docs.google.com/spreadsheets/d/1N9uoaQTjmt8vZ-szCQNgy119akz2Q13b4cZkTww8X3A/edit?usp=sharing

please let me know if you have any questions

I dont know if this is a big request. Please help.

[Tyler Sidell (AdWords Scripts Team)]

Hi Sreekanth,

This request would take some time to code as it is in fact creating spreadsheets within a main spreadsheet.  We do not have a current solution for your use case but I will discuss with the rest of the team about the possibility of creating something similiar to your request.  Will let you know if any progress is made.

Thanks,
Tyler Sidell

AdWords Scripts Team

[Sreekanth]

Hi Tyler,

I know that it was a big request. anyway thank you for your reply.
May be i should simplify it by avoiding the spreadsheet part and stick with the email which gives reports with basic details mentioned on the spreadsheet that i provided.? Can you do that.?

Thanks

[Tyler Sidell (AdWords Scripts Team)]

Hi Sreekanth,

The script below will create a spreadsheet with separate tabs for each account.  Each account will pull the statistics that were outlined in the template that you provided.  Please use this script to get started and modify it to fit your needs.  Note that you must replace REPLACE_WITH_SPREADSHEET_URL and EMAIL_ADDRESS.

function main() {
  var accountIterator = MccApp.accounts().get();
  var ssUrl = 'REPLACE_WITH_SPREADSHEET_URL';

  var spreadsheet = SpreadsheetApp.openByUrl(ssUrl);

  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 CampaignName, Clicks, Impressions, Ctr, AverageCpc, Cost, ConversionRate, SearchImpressionShare, SearchBudgetLostImpressionShare, Conversions from CAMPAIGN_PERFORMANCE_REPORT 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:G30");
    range.sort(7);
  }
  MailApp.sendEmail('EMAIL_ADDRESS', 'Budget Report', ssUrl);
}

Thanks,

Tyler Sidell

AdWords Scripts Team

[Sreekanth]

On Tuesday, June 21, 2016 at 11:20:17 AM UTC+5:30, Sreekanth wrote:

Hi Tyler,

Thank you so much for your time to make this script.

I think we missed one thing here. I dont want the report for all the accounts. I only need reports for accounts which are limited by budget( we have about more than 300 hundred accounts in adwords i dont think it is possible to add 300 + sheets on to a sheet). the script that i got to find the accounts which are limited by budget was given in my very first post on this thread. please have a look. Also i need only the details of enabled campaigns.

Thanks
Sreekanth P R

[Tyler Sidell (AdWords Scripts Team)]

Hi Sreekanth,

If you are looking to limit and filter the results that come back from the script that we've provided, you can add a WHERE clause to your report query.  You can filter by Cost, Amount, and CampaignStatus

Example:

var report = AdWordsApp.report("Select CampaignName, Clicks, Impressions, Ctr, AverageCpc, Cost, CampaignStatus, ConversionRate, SearchImpressionShare, SearchBudgetLostImpressionShare, Conversions from CAMPAIGN_PERFORMANCE_REPORT where Cost > 100 AND CampaignStatus = ENABLED during LAST_MONTH");

Thanks,

Tyler Sidell

AdWords Scripts Team