Pull URL Performance Report in New Script Experience

[Carter Knight]

Hello,

I hope all is well. We have a script that pulls our URL performance and we received a notification that we need to convert it to the new scripts experience. However, when I converted the script, the placement URLs and the metrics are not the same, and there doesn't appear to be an option for a "cost" metric. Could you please help me to fix this? (please don't pay attention to the dates selected, the var report is where the issue is)

Script that works in old experience:

// the purpose of this script is to pull out the last xx days of campaign data from the display account

var settings = {  

  url: 'our google sheet url goes here',

  sheet:'our google sheet name of tab goes here',

  start:3, // Number of days. Start date is this number of days before end date

  end:1 // number of days ago from today. 0 = today, 1 = yesterday etc

}

function main() {

  getData();

}

function getData() {

  var sheet = SpreadsheetApp.openByUrl(settings.url).getSheetByName(settings.sheet);

  var dates = getDates(settings.start,settings.end);

  var report = AdsApp.report(

    'SELECT Url, Cost, Conversions, Clicks, Impressions' +

    ' FROM URL_PERFORMANCE_REPORT' +

    ' WHERE Impressions > 0 ' +

    'DURING ' + dates.report

  )

  report.exportToSheet(sheet);

  Logger.log('Data from '+dates.prettyStart+' until '+dates.prettyEnd+' URL placements, exported to spreadsheet\n\n'+settings.url);

}

function getDates(start,end) {

  var format = 'yyyyMMdd';

  var prettyFormat = 'MMM dd yyyy';

  var timeZone = AdsApp.currentAccount().getTimeZone();

  var today = new Date();

  var oneDay = 1000*60*60*24; // milliseconds in a day

  var endDate = new Date(today - (oneDay * end));

  var formatEndDate = Utilities.formatDate(endDate,timeZone,format);

  var prettyEndDate = Utilities.formatDate(endDate,timeZone,prettyFormat);

  var startDate = new Date(endDate - ((oneDay * start)-oneDay));

  var formatStartDate = Utilities.formatDate(startDate,timeZone,format);

  var prettyStartDate = Utilities.formatDate(startDate,timeZone,prettyFormat);

   return {report:formatStartDate+','+formatEndDate,prettyStart:prettyStartDate,prettyEnd:prettyEndDate}

}

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Script that pulls incorrect information in new experience

// the purpose of this script is to pull out the last xx days of campaign data from the display account

var settings = {  

  url: 'xxxxxx',

  sheet:'xxxxxxxxx',

  start:3, // Number of days. Start date is this number of days before end date

  end:1 // number of days ago from today. 0 = today, 1 = yesterday etc

}

function main() {

  getData();

}

function getData() {

  var sheet = SpreadsheetApp.openByUrl(settings.url).getSheetByName(settings.sheet);

  var dates = getDates(settings.start,settings.end);

  var report = AdsApp.report(

    'SELECT detail_placement_view.placement, metrics.cost_micros, metrics.conversions, metrics.clicks, metrics.impressions ' +

    'FROM detail_placement_view ' +

    'WHERE metrics.impressions > 0 ' +

      'AND segments.date BETWEEN "2022-07-17"' + ' AND ' + '"2022-07-19"'

    )

  report.exportToSheet(sheet);

  console.log('Data from '+dates.prettyStart+' until '+dates.prettyEnd+' URL placements, exported to spreadsheet\n\n'+settings.url);

}

function getDates(start,end) {

  var format = 'yyyyMMdd';

  var prettyFormat = 'MMM dd yyyy';

  var timeZone = AdsApp.currentAccount().getTimeZone();

  var today = new Date();

  var oneDay = 1000*60*60*24; // milliseconds in a day

  var endDate = new Date(today - (oneDay * end));

  var formatEndDate = Utilities.formatDate(endDate,timeZone,format);

  var prettyEndDate = Utilities.formatDate(endDate,timeZone,prettyFormat);

  var startDate = new Date(endDate - ((oneDay * start)-oneDay));

  var formatStartDate = Utilities.formatDate(startDate,timeZone,format);

  var prettyStartDate = Utilities.formatDate(startDate,timeZone,prettyFormat);

   return {report:formatStartDate+','+formatEndDate,prettyStart:prettyStartDate,prettyEnd:prettyEndDate}

}

Thank you,

Carter

[Google Ads Scripts Forum Advisor]

Hi Carter,
 

Thanks for reaching out to the Google Ads Scripts. I hope you are doing well today.
 

So our team can better check this concern, could you provide the following?
 

• CID.
• Script Name.
• Shareable links of your spreadsheet if applicable.

Regards,

Darwin Google Ads Scripts Team

ref:_00D1U1174p._5004Q2bm4RY:ref

[Carter Knight]

Hi Darwin,

Our CID is 780-004-4072

The script name is "Pull Ad Placement Data Script"

Thank you,

Carter

[Google Ads Scripts Forum Advisor]

Hi Carter,

Thank you for providing the requested information. I''ve checked the script in question and can confirm that its implementation is fine. Is there a discrepancy that you were getting between AdWords report vs Google Ads API reports? If so, could you please shot it to us via screenshot, so we could focus our investigation there?

As an aside, I could see that you're extracting Cost, Conversions, Clicks, Impressions metrics. Please do note that on Google Ads API reports, Zero metrics are always excluded when segmenting a report,

Regards,

Teejay Wennie Google Ads Scripts Team

ref:_00D1U1174p._5004Q2bm4RY:ref

[Carter Knight]

Hi Teejay,

The script under the new scripts experience will pull the data, however, it doesn't appear that there is an option to pull "cost" as a metric. I've only seen metrics.cost_micros and metrics.average_cost, which gives us 7-digit or 8-digit numbers that don't make much sense to us (screenshot attached).

Ok, so do you mean that if a placement URL has all zeros in cost, conversions, clicks, and impressions during a certain time period it will not pull that URL in the report?

Thank you!

Carter

[Google Ads Scripts Forum Advisor]

Hi Carter,

The micro unit is 1,000,000 times the account currency. For example, $1.23 will come back as 1230000, in micros. The new reporting only supports cost_micros, not cost. 

Entities with zero metrics will not be returned in a report that requests metrics. If a report is not segmented, or does not contain a predicate, these entities can be returned in reporting.

Regards,

Matt Google Ads Scripts Team

ref:_00D1U1174p._5004Q2bm4RY:ref

[Carter Knight]

Interesting, thank you for that explanation.

In our previous report, the metrics were summed by the corresponding placement URL, do you know how I can do that with the new script experience version of the script? Here's an example attached of two of the same URLs split out instead of being summed together.

[Google Ads Scripts Forum Advisor]

HI Carter,

Could you please share the query that you were using? This will help us to provide insight precisely. Also, could you please share your CID as well?

Regards,

Teejay Wennie Google Ads Scripts Team

ref:_00D1U1174p._5004Q2bm4RY:ref

[Carter Knight]

Yep sure, the query is:

  var report = AdsApp.report(
    'SELECT detail_placement_view.placement, metrics.cost_micros, metrics.conversions, metrics.clicks, metrics.impressions ' +
    'FROM detail_placement_view ' +
    'WHERE metrics.impressions > 0 ' +
      'AND segments.date BETWEEN "2022-07-17"' + ' AND ' + '"2022-07-19"')

Our CID is 780-004-4072

The script name is "Pull Ad Placement Data Script"

Thank you,

Carter

[Google Ads Scripts Forum Advisor]

Hi Carter,

Thank you for providing the requested information. I've tested your query and was able to get the behavior on my end. As per further checking, there is nt segmentation on your query that could cause reports to split out. However, please note that every report is initially segmented by the resource specified in the FROM clause. For your use case, it is segmented by detail_placement_view.resource_name. Since finance.yahoo.com is placed on different Ad Groups, the report will return the resource name as such:

• customers/7800044072/detailPlacementViews/127174260686~MTRfZmluYW5jZS55YWhvby5jb20
• customers/7800044072/detailPlacementViews/143453431092~MTRfZmluYW5jZS55YWhvby5jb20
• customers/7800044072/detailPlacementViews/122478707474~MTRfZmluYW5jZS55YWhvby5jb2

If you notice, it contains difference AdGroups Ids, so it is an expected behavior that you will get a split out report for your use case.

[Carter Knight]

I see, thank you for that information! Is there a way to aggregate the data so that it only shows the placement listing that is not segmented by ad group? For example, in the screenshot attached, yahoo.com appears three times in the report but the metrics are all the same, so I'm looking for a way to only grab placement metrics without them being split out by ad group.

Thank you,

Carter

[Carter Knight]

Also, in the query, we were using detail_placement_view.placement instead of detail_placement_view.resource_name.

[Google Ads Scripts Forum Advisor]

Hi Carter,

The resource_name field of the resource in the FROM clause is returned and metrics are segmented by it, even when the resource_name field is not explicitly included in the query. In your case, you query is segmented by the detail_placement_view.resource_name even though it is not explicitly included in the query. Since your concern is specific to Google Ads API reports, we would recommend reaching out to Google Ads API team via this forum link. The developer specialist there should be able to provide in-depth insight specially for a way to only grab placement metrics without them being split out by ad group. You may provide them your current query and ask for guidance and if it's possible to not split out the result by ad group.

[Ahmet Çekici]

Revert my script settings, this is not my request. It is done with the attack of hackers. Please do not change my script settings.

29 Tem 2022 Cum 08:52 tarihinde Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com> şunu yazdı:

--
-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/RUVui000000000000000000000000000000000000000000000RFRPNB00mYVdW6UtRyKAgdI0lOJdmw%40sfdc.net.

[Google Ads Scripts Forum Advisor]

Hello,

Thank you for reaching out to us.

Could you please provide more details to your concern so that we can have a clear understanding of your issue? Also, kindly provide the following details below so that we can check this further.

• Google Ads account ID / CID. You can check this guide on finding it.
• Name of the affected script

Regards,

James Howell Google Ads Scripts Team

ref:_00D1U1174p._5004Q2bm4RY:ref