Month to yesterday date Campaign spent script

[arpit.a...@adglobal360.com]

Hi Team,

I need a script where I can get start date to yesterday all campaigns spent on my email or in drive sheet.

For eg - Campaign started on 2nd January 2017 and today is 25th January 2017, I need a script where my cost of each campaign from 2nd January 2017 to 24th January 2017 gets updated in drive and it gets updated on daily basis.

[Joyce Lava (AdWords Scripts Team)]

Hello,

You may refer to this example script on how to export the report data into a spreadsheet. You can use Campaign Performance Report with custom date ranges to get the Cost of each campaign on the dates specified. The below script might be helpful in getting started with the report query:

 //variable yesterday is exactly 24 hours ago.  
  var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
  var now = new Date(); //date today
  var yesterday = new Date(now.getTime() - MILLIS_PER_DAY);
 
  //format the date yesterday and use it in the report as the custom date range's max date
  var timeZone = AdWordsApp.currentAccount().getTimeZone();
  var formattedMaxDate = Utilities.formatDate(yesterday, timeZone,
                                      'YYYYMMDD')
   
  var report = AdWordsApp.report(
    'SELECT CampaignName, Clicks, Impressions, Cost ' +
    'FROM   CAMPAIGN_PERFORMANCE_REPORT ' +
    'DURING 20170102, ' + formattedMaxDate);

You may schedule the script to run on a Daily basis. If you wish to get the report data in email, you may also use MailApp and refer to these sample scripts. Hope this helps.

Regards,

Joyce Lava

AdWords Scripts Team

[arpit.a...@adglobal360.com]

Hi Joyce,

Can you please send me a sample script with all functions passed. Take Example - I need campaign report from 1st Jan 2017 to 29th Jan 2017 and these dates gets updated automatically everyday.

I am not a coder so i am unable to understand things. I Can copy paste and make small changes.

[Anthony Madrigal]

Hi,

You won't be able to use reports to get data for all time. However, you can use the code below to get stats for your campaigns with the date range as ALL_TIME.

function main() {
var campaignIterator = AdWordsApp.campaigns()
      .get();
  while (campaignIterator.hasNext()) {
    var campaign = campaignIterator.next();
    // You can also request reports for pre-defined date ranges. See
    // https://developers.google.com/adwords/scripts/docs/reference/adwordsapp/adwordsapp_campaign#getStatsFor_1,
    // DateRangeLiteral section for possible values.
    var stats = campaign.getStatsFor('ALL_TIME');
    Logger.log(campaign.getName() + ', Clicks: ' + stats.getClicks() + ' , Impressions: ' +
      stats.getImpressions() + ' Cost: '+ stats.getCost());
  }
}

Cheers,

Anthony

AdWords Scripts Team

[arpit.a...@adglobal360.com]

Hi Anthony,

Thanks for sharing above script.

Where Can I see results of this script? Do I need to pass any function for it?

On Wednesday, January 25, 2017 at 11:42:35 AM UTC+5:30, (unknown) wrote:

[Anthony Madrigal]

Hi,

Once you copy and paste the code into the Scripts interface and run it, you should see all the changes and logs in the Execution logs.

Please let me know if you have any other issues.

[arpit.a...@adglobal360.com]

Is there any way I can get it on Email or Drive sheet?

On Wednesday, January 25, 2017 at 11:42:35 AM UTC+5:30, (unknown) wrote:

[Anthony Madrigal]

Hi,

You can have the results sent to your email by modifying the code to

function main() {
var body = [];

var campaignIterator = AdWordsApp.campaigns()
      .get();
  while (campaignIterator.hasNext()) {
    var campaign = campaignIterator.next();
    // You can also request reports for pre-defined date ranges. See
    // https://developers.google.com/adwords/scripts/docs/reference/adwordsapp/adwordsapp_campaign#getStatsFor_1,
    // DateRangeLiteral section for possible values.
    var stats = campaign.getStatsFor('ALL_TIME');

    body.push(campaign.getName() + ', Clicks: ' + stats.getClicks() + ' , Impressions: ' +
      stats.getImpressions() + ' Cost: '+ stats.getCost() + '\n');
  }
  MailApp.sendEmail("YOUR EMAIL ADDRESS","YOUR SUBJECT", body)
}

[arpit.a...@adglobal360.com]

Is it possible to pass multiple email id's into this?

Also Can I choose only enabled campaigns?

On Wednesday, January 25, 2017 at 11:42:35 AM UTC+5:30, (unknown) wrote:

[Anthony Madrigal]

Hi,

You can modify the following lines to get what you are looking for:

var campaignIterator = AdWordsApp.campaigns().withCondition("Status = ENABLED")
      .get();

MailApp.sendEmail("ema...@example.com,exam...@example.com","YOUR SUBJECT", body)

Cheers,

Anthony

AdWords Scripts Team

[arpit.a...@adglobal360.com]

Thanks Anthony,

Script is working fine now.

Numbers look quite mess up on e-mail as there are 100+ campaigns. Is it possible to get result in drive too?

On Wednesday, January 25, 2017 at 11:42:35 AM UTC+5:30, (unknown) wrote:

[Anthony Madrigal]

Hi,

No problem. Glad it is working. You can use this code to have the results appended to a spreadsheet and emailing the users you listed the URL to get the spreadsheet.

function main() {

  var spreadsheet = SpreadsheetApp.create('YOUR REPORT NAME HERE');
  var email = ["ema...@example.com","ema...@example.com"];
  var sheet = spreadsheet.getSheets()[0];
  sheet.clear();
  sheet.appendRow(["Campaign", "Clicks", "Impressions", "Cost"]);

 
var campaignIterator = AdWordsApp.campaigns().withCondition("Status = ENABLED")
      .get();

  while (campaignIterator.hasNext()) {
    var campaign = campaignIterator.next();
    // You can also request reports for pre-defined date ranges. See
    // https://developers.google.com/adwords/scripts/docs/reference/adwordsapp/adwordsapp_campaign#getStatsFor_1,
    // DateRangeLiteral section for possible values.
    var stats = campaign.getStatsFor('ALL_TIME');

    sheet.appendRow([campaign.getName() ,stats.getClicks(),
      stats.getImpressions() , stats.getCost() ]);
  }
  spreadsheet.addEditors(email);
  MailApp.sendEmail(email,"Campaign Stats", "Your spreadsheet is at " + spreadsheet.getUrl());
}

[arpit.a...@adglobal360.com]

Hi Anthony,

This script is also working. Thanks for sharing this.

There is a question. If I schedule this script on daily basis, will this script give results in same file or in new file? Because I need results in same file.

Also, please include campaign type (Search or Display) code in the script shared by you.

On Wednesday, January 25, 2017 at 11:42:35 AM UTC+5:30, (unknown) wrote:

[Anthony Madrigal]

Hi,

Glad to hear the script is working. The script I provided will create a file each time. It also will currently be unable to include the campaign type. So what I've done is modify the script to use the same file and create separate sheets from the spreadsheet to be broken down by campaign type. Note: You will need to create a new Sheet in the spreadsheet first before running this code for it to work.

function main() {

  var spreadsheet = SpreadsheetApp.openByUrl("YOUR SPREADSHEET URL");

  var email = ["ema...@example.com","ema...@example.com"];

   
  var sheet1 = spreadsheet.getSheets()[0];
  sheet1.clear();
  sheet1.setName("Search Campaigns");
  sheet1.appendRow(["Campaign", "Clicks", "Impressions", "Cost"]);
  getCampaignStats("SEARCH",sheet1);
 
  var sheet2 = spreadsheet.getSheets()[1];
  sheet2.clear();
  sheet2.setName("Display Campaigns");
  sheet2.appendRow(["Campaign", "Clicks", "Impressions", "Cost"]);
  getCampaignStats("DISPLAY",sheet2);

 
  spreadsheet.addEditors(email);
  MailApp.sendEmail(email,"Campaign Stats", "Your spreadsheet is at " + spreadsheet.getUrl());
}

function getCampaignStats(campaigntype,sheet){

var campaignIterator = AdWordsApp.campaigns()
      .withCondition("Status = ENABLED")
      .withCondition("AdvertisingChannelType = '" + campaigntype + "'")

      .get();

  while (campaignIterator.hasNext()) {
    var campaign = campaignIterator.next();
    // You can also request reports for pre-defined date ranges. See
    // https://developers.google.com/adwords/scripts/docs/reference/adwordsapp/adwordsapp_campaign#getStatsFor_1,
    // DateRangeLiteral section for possible values.
    var stats = campaign.getStatsFor('ALL_TIME');


    sheet.appendRow([campaign.getName() ,stats.getClicks(),
      stats.getImpressions() , stats.getCost() ]);
  }
 
}

 Cheers,

Anthony

AdWords Scripts Team

[arpit.a...@adglobal360.com]

Hi,

This is also working fine. If I keep the same URL and run this script daily or thrice a week, will it not update in same sheet? Shall I try doing this?

On Wednesday, January 25, 2017 at 11:42:35 AM UTC+5:30, (unknown) wrote:

[Anthony Madrigal]

Hello,

The script I have provided will use the same spreadsheet no matter how often you run it. You will just need to put the URL of the spreadsheet where it says YOUR SPREADSHEET URL.

[arpit.a...@adglobal360.com]

Thanks Anthony.

This has solved a major reporting problem.

On Wednesday, January 25, 2017 at 11:42:35 AM UTC+5:30, (unknown) wrote: