Summing values from report

[Jan Hahlbrock]

Hello,

I have the following report:
report = AdsApp.report('SELECT segments.product_item_id, metrics.cost_micros, metrics.conversions_value, campaign.name, metrics.conversions, segments.product_custom_attribute4, segments.product_custom_attribute3 FROM shopping_performance_view WHERE campaign.name REGEXP_MATCH ".*_PPF_.*" AND campaign.name NOT REGEXP_MATCH ".*_PPF_Y.*" AND metrics.cost_micros > 50000000 AND segments.date DURING LAST_30_DAYS ORDER BY segments.product_item_id ASC');

As you can see I am trying to report on OfferIds while only selecting certain campaigns.

Now i want to sum the values for cost, conversionvalue and conversions for each ID, so that I can calculate the ROAS for each product in this specific set of campaigns and write them to a spreadsheet along with the total conversions.

My initial idea was to save the values from one row into variables, call the next row and compare the IDs but I am afraid this is not going to work in all cases.

I have found this thread  on stackoverflow which could be helpful : https://stackoverflow.com/questions/14446511/most-efficient-method-to-groupby-on-an-array-of-objects
but since I call the rows individually i dont know how to implement this.

Any help is appreciated :)

Kind regards

Jan

[Google Ads Scripts Forum Advisor]

Hello Jan,

Thank you for raising your concern to us. James here, from the Google Ads scripts support team. Allow me to assist you further.

I completely understand your concern. However, I believe that there’s no supported method yet in getting the sum of the cost, conversion value and conversions for each product(item ID) via Google Ads scripts.

However, can you please share with us the following information below so that we can check your existing script and see what else we can do to assist you with your milestone?

• Google Ads account ID / CID
• Name of the script where the given report syntax is implemented

Regards,

James Howell Google Ads Scripts Team

ref:_00D1U1174p._5004Q2b6W2L:ref

[Jan Hahlbrock]

Hello James,

thank you for looking into my issue.

CID: 711-414-0874

ScriptName: Sum values from report

Kind regards,

Jan

[Google Ads Scripts Forum Advisor]

Hi Jan,

Thank you for providing the requested information. Would it be possible for your to elaborate your use case here? A sample report (from spreadsheet) will help us visualizing your goal, and so that we can provide guidance precisely.

Regards,

Teejay Wennie Google Ads Scripts Team

ref:_00D1U1174p._5004Q2b6W2L:ref

[Jan Hahlbrock]

Hi Teejay,

a person at Stackoverflow helped me with this, here's the answer :  https://stackoverflow.com/questions/72658666/sum-values-in-object/72659170#72659170

 The use case is to find products within a specific CSS and push them to another Campaign based on performance.

Kind Regards,

Jan

[Besserwisser Skript]

Hey James,

Thank you for looking into this problem. I have gotten a step further with this issue, feel free to have a look at it.

CID: 711-414-0874

Scriptname: Sum values from report

Regards,

Jan

[Google Ads Scripts Forum Advisor]

Hi Jan,

Thank you for providing updates here. I would suggest to follow the suggestion in the provided stackoverflow link as it appears correct. However, to have functionality to get the sum of rows in the Google Ads scripts, allow me to raise a feature request to my team, subject for review.

For now please follow our blog to get updated with news and releases about Google Ads scripts.

Regards,

Ernie John Google Ads Scripts Team

ref:_00D1U1174p._5004Q2b6W2L:ref

[Besserwisser Skript]

Hi Teejay,

I have found a solution for my problem with the help of some friendly folks at stackoverflow : https://stackoverflow.com/questions/72658666/sum-values-in-object/72659170#72659170

The use case here is, to identify bad performing products in smart shopping in one specific CSS and handle them in an extra campaign.

Kind Regards,

Jan

[Besserwisser Skript]

Hello James,

CID: 711-414-0874

ScriptName: Sum values from report

Kind Regards,

Jan

On Monday, June 13, 2022 at 11:31:21 AM UTC+2 adsscripts wrote: