Export Product Data

Export product data to a Google Sheet and calculate the ROI of each product.

Start Now!
Export Google Shopping 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.

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.
The script
// Copyright 2021. Increase BV. All Rights Reserved.
//
// Created By: Martijn Beumer
// for Producthero
// Tweaked/Formatted by: Tibbe van Asten
// 
// Last update: 02-03-2021
//
// ABOUT THE SCRIPT
// Export productdata from Shopping campaigns to a sheet.
//
////////////////////////////////////////////////////////////////////

var config = {

  LOG : true,
  DATE_RANGE : last_n_days(90),
  
  // Edit the URL of an empty Google Sheet in here, with '/edit' at the end
  SPREADSHEET_URL : "",
  CALCULATE_ROI: false

}  
  
////////////////////////////////////////////////////////////////////

function main() {
  
	var spreadsheet = SpreadsheetApp.openByUrl(config.SPREADSHEET_URL);
  var from = config.DATE_RANGE[0];
  var to = config.DATE_RANGE[1];
  
  var report = AdsApp.report(
    "SELECT OfferId, Clicks, Impressions, Ctr, Conversions, ConversionRate, ConversionValue, Cost " +
    "FROM SHOPPING_PERFORMANCE_REPORT " +
    "WHERE Impressions > 0 " +
    "DURING " + from +","+ to);
  
  var sheet = spreadsheet.getActiveSheet()
  report.exportToSheet(sheet);
  
  if(config.CALCULATE_ROI === true){
    sheet.getRange("I1").setValue("ROI");

      for(var i = 2;i < sheet.getLastRow()+1;i++){
        var cost = sheet.getRange("H"+i).getValue();
        var revenue = sheet.getRange("G"+i).getValue();

        if(cost > 0 && revenue > 0){ var roi = cost / revenue; } else { var roi = 0; }
        sheet.getRange("I"+i).setValue(parseFloat(roi).toFixed(2));   

      } // rowIterator
  }
  
  Logger.log("Producthero was here..");
  
} // function main()

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

function last_n_days(n) {
  
	var	from = new Date(), to = new Date();
	to.setUTCDate(from.getUTCDate() - n);
  
	return googleDateRange(from, to);
  
} // function last_n_days()

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

function googleDateRange(from, to) {
  
	function googleFormat(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 = googleFormat(from);
	to = googleFormat(to);
  
	var result = [from, to];
	
  if (inverse) {
		result = [to, from];
	}
  
	return result;
  
} // function googleDateRange()
Show whole script!
Loading Comments
The Experts
Tibbe van Asten Team Lead Search
Nils Rooijmans Water Cooler Topics
Martijn Kraan Freelance PPC Specialist
Bas Baudoin Teamlead SEA @ Happy Leads
How about you? JOIN US!
Sharing Knowledge
Caring

Sharing Knowledge

Adsscripts.com is all about sharing knowledge. In the current market, PPC specialists like to keep their knowledge and experience to themselves. We're convinced that sharing knowledge can ensure that everyone gets better at their work. We want to change this by sharing our knowledge about scripts with everyone.

Do you also want to contribute? We are open to new ideas and feedback on everything you find on Adsscripts.com.

Contact us

Training &
Workshop
Contact us!
Adsscripts Training & Workshop