Export Product Data

Export Get started!

This script was written by Martijn Beumer from Producthero. With a few minor adjustments, you can now use this script to export all product data from Google Shopping campaigns to a Google Sheet. This report can then be used as input for your product feed, for example. With the help of this product data you can view the performance per productId. In addition, the script offers the possibility to calculate the ROI of each product in the sheet based on the costs and turnover from Google Ads.

Author

Tibbe van Asten

Contact me!

Difficulty

Category
Google Ads Scripts

Google Shopping ROI

The calculation of the ROI per product from Google Shopping can serve as input for the campaign structure in Google Shopping. Organizing the Smart Shopping or regular Google Shopping campaigns with Smart Bidding based on ROI can provide more control over the performance and use of budget in your account. This script can automatically calculate the ROI of any product. Please note, if you have a lot of products, it is advisable to put these calculations with a formula in the sheet itself, because otherwise the script will take a very long time.

Instellingen

LOG: Indicate whether the script should report the intermediate steps by changing the value to 'true'.
DATE_RANGE: Indicate how many days the script has to look back to calculate the CPA.
SPREADSHEET_URL: Make a copy of this spreadsheet and enter the URL here to use the results of the script.
CALCULATE_ROI: Set to 'true' to calculate the ROI of each product and add this to the sheet. Recommended to add the formula in the sheet itself when dealing with a lot of products.

// Copyright 2023 // // Created By: Martijn Beumer // for Producthero // Tweaked/Formatted by: Tibbe van Asten // // Last update: 14-06-2022 // // ABOUT THE SCRIPT // Export productdata from Shopping campaigns to a sheet. // //////////////////////////////////////////////////////////////////// var config = { LOG : true, DATE_RANGE : last_n_days(30), // Edit the URL of an empty Google Sheet in here, with '/edit' at the end SPREADSHEET_URL : "", CONVERT_MICROS: true } //////////////////////////////////////////////////////////////////// function main() { var spreadsheet = SpreadsheetApp.openByUrl(config.SPREADSHEET_URL); var sheet = spreadsheet.getActiveSheet(); var from = config.DATE_RANGE[0]; var to = config.DATE_RANGE[1]; var report = AdsApp.report( "SELECT segments.product_item_id, metrics.clicks, metrics.impressions, metrics.ctr, metrics.conversions, metrics.conversions_from_interactions_rate, metrics.conversions_value, metrics.cost_micros " + "FROM shopping_performance_view " + "WHERE metrics.impressions > 0 " + "AND segments.date BETWEEN '" + config.DATE_RANGE.split(',')[0] + "' AND '" + config.DATE_RANGE.split(',')[1] + "'"); report.exportToSheet(sheet); if(config.LOG === true){ Logger.log("Initial export complete"); } if(config.CONVERT_MICROS === true){ const lastRow = sheet.getLastRow(); const range = sheet.getRange("H2:H"+lastRow); const values = range.getValues(); var newValues = []; for (let i = 0; i < values.length; i++) { var micro = values[i]; var normal = micro / 1000000; newValues.push([normal]); } range.setValues(newValues); } Logger.log("Thanks for using this script!"); } // function main() //////////////////////////////////////////////////////////////////// function last_n_days(n) { var from = new Date(), to = new Date(); to.setUTCDate(from.getUTCDate() - n); return google_date_range(from, to); } // function last_n_days() //////////////////////////////////////////////////////////////////// function google_date_range(from, to) { function google_format(date) { var date_array = [date.getUTCFullYear(), date.getUTCMonth() + 1, date.getUTCDate()]; if (date_array[1] < 10) date_array[1] = '0' + date_array[1]; if (date_array[2] < 10) date_array[2] = '0' + date_array[2]; return date_array.join(''); } var inverse = (from > to); from = google_format(from); to = google_format(to); var result = [from, to]; if (inverse) { result = [to, from]; } return result.join(','); } // function google_date_range()