Script for PLA campaign history audit

What the script does:

Downloads the complete performance history of all products for a given period of time (you can set the number of days back on line 4, i.e. typing “30” will download history for the last 30 days).
Generates a CSV file that contains all the data.
You’ll find the CSV file in your Google Drive or script protocol.
You can then manipulate the data according to your needs in order to analyze it, regardless of the historic PLA campaign structure.

How to insert the script:

Copy script.
Insert into account.
Indicate the number of days back (from today) you want to download the historical data on line 4. Beware, 360 days or more can result in several million records.
Import the CSV file using Excel> Data> From Text.
Create a pivot table according to your needs.

var NAME_YOUR_FILE = 'YOUR NAME';
var DAYS_BACK = 180;
function main(){
 
  var to = lynt_get_date(1,1);
  var from = lynt_get_date(DAYS_BACK,1);
  var reportArray = [ "OfferId; CampaignName; AdGroupName; Cost; AllConversions; AllConversionValue; ProductTypeL1; ProductTypeL2; ProductTypeL3; CategoryL1; CategoryL2; CategoryL3; CategoryL4; Impressions; Brand; CustomAttribute0; CustomAttribute1; CustomAttribute2; CustomAttribute3; Clicks"];
  var report = AdWordsApp.report("SELECT OfferId, Clicks, CampaignName, AdGroupName, Cost, AllConversions, AllConversionValue, ProductTypeL1, ProductTypeL2, ProductTypeL3, CategoryL1, CategoryL2, CategoryL3, CategoryL4, Impressions, Brand, CustomAttribute0, CustomAttribute1, CustomAttribute2, CustomAttribute3 FROM SHOPPING_PERFORMANCE_REPORT DURING "+from+","+to+"");
  var iterator = report.rows();
  while(iterator.hasNext()) {
    var oneRow = iterator.next();
    var oneRowString = lynt_json_to_array(oneRow);
    reportArray.push(oneRowString);
  }
 
  doCSV(reportArray,NAME_YOUR_FILE);
 
}
 
 
function doCSV(input,name) {
 var data = input.join("\n");
 var file = DriveApp.createFile(name, data);
 Logger.log(file.getUrl());
}
 
function lynt_json_to_array(json) {
    var OfferId = json.OfferId;
    var Clicks = json.Clicks.replace(","," ").replace(".",",");
    var CampaignName = json.CampaignName;
    var AdGroupName = json.AdGroupName;
    var Cost = json.Cost.replace(","," ").replace(".",",");
    var AllConversions = json.AllConversions.replace(","," ").replace(".",",");
    var AllConversionValue = json.AllConversionValue.replace(","," ").replace(".",",");
    var ProductTypeL1 = json.ProductTypeL1;
    var ProductTypeL2 = json.ProductTypeL2;
    var ProductTypeL3 = json.ProductTypeL3;
    var CategoryL1 = json.CategoryL1;
    var CategoryL2 = json.CategoryL2;
    var CategoryL3 = json.CategoryL3;
    var CategoryL4 = json.CategoryL4;
    var Impressions = json.Impressions.replace(","," ").replace(".",",");
    var Brand = json.Brand;
    var CustomAttribute0 = json.CustomAttribute0;
    var CustomAttribute1 = json.CustomAttribute1;
    var CustomAttribute2 = json.CustomAttribute2;
    var CustomAttribute3 = json.CustomAttribute3;
    var string = 
      OfferId + ";'" + 
      CampaignName + ";'" + 
      AdGroupName + ";" + 
      Cost + ";" + 
      AllConversions + ";" + 
      AllConversionValue + ";" + 
      ProductTypeL1 + ";" + 
      ProductTypeL2 + ";" + 
      ProductTypeL3 + ";" + 
      CategoryL1 + ";" + 
      CategoryL2 + ";" + 
      CategoryL3 + ";" + 
      CategoryL4 + ";" + 
      Impressions + ";" + 
      Brand + ";" + 
      CustomAttribute0 + ";" + 
      CustomAttribute1 + ";" + 
      CustomAttribute2 + ";" + 
      CustomAttribute3 + ";" + 
      Clicks;
    return string;
}
 
function lynt_format_awql_date(datum){
  return datum.getUTCFullYear()+("0"+(datum.getUTCMonth()+1)).slice(-2)+("0"+datum.getUTCDate()).slice(-2);
}
 
//Daylight saving time correction
function lynt_DST(datum, offset){
  var yr = datum.getFullYear();
  var dst_start = new Date("March 14, " + yr +" 02:00:00");
  var dst_end = new Date("November 07, " + yr +" 02:00:00");
  var day = dst_start.getDay();
  dst_start.setDate(14-day);
  day = dst_end.getDay();
  dst_end.setDate(7-day);
  if (datum >= dst_start && datum < dst_end){
    return offset+1;
  } else {
  return offset;
  }
}
 
//Getting data for x days back in right format for AWQL query
function lynt_get_date(pocet_dni, zona) {
  var minule = new Date(); 
  //kdyz je letni cas, tak o hodinu dele (GMT+1)
  var offset = lynt_DST(minule, zona);
  minule.setTime(minule.getTime() - (1000 * 60 * 60 * (24) * pocet_dni) + offset);
  return lynt_format_awql_date(minule);
}

var NAME_YOUR_FILE = 'YOUR NAME';

var DAYS_BACK = 180;

function main(){

var to = lynt_get_date(1,1);

var from = lynt_get_date(DAYS_BACK,1);

var reportArray = [ "OfferId; CampaignName; AdGroupName; Cost; AllConversions; AllConversionValue; ProductTypeL1; ProductTypeL2; ProductTypeL3; CategoryL1; CategoryL2; CategoryL3; CategoryL4; Impressions; Brand; CustomAttribute0; CustomAttribute1; CustomAttribute2; CustomAttribute3; Clicks"];

var report = AdWordsApp.report("SELECT OfferId, Clicks, CampaignName, AdGroupName, Cost, AllConversions, AllConversionValue, ProductTypeL1, ProductTypeL2, ProductTypeL3, CategoryL1, CategoryL2, CategoryL3, CategoryL4, Impressions, Brand, CustomAttribute0, CustomAttribute1, CustomAttribute2, CustomAttribute3 FROM SHOPPING_PERFORMANCE_REPORT DURING "+from+","+to+"");

var iterator = report.rows();

while(iterator.hasNext()) {

var oneRow = iterator.next();

var oneRowString = lynt_json_to_array(oneRow);

reportArray.push(oneRowString);

}

doCSV(reportArray,NAME_YOUR_FILE);

}

function doCSV(input,name) {

var data = input.join("\n");

var file = DriveApp.createFile(name, data);

Logger.log(file.getUrl());

}

function lynt_json_to_array(json) {

var OfferId = json.OfferId;

var Clicks = json.Clicks.replace(","," ").replace(".",",");

var CampaignName = json.CampaignName;

var AdGroupName = json.AdGroupName;

var Cost = json.Cost.replace(","," ").replace(".",",");

var AllConversions = json.AllConversions.replace(","," ").replace(".",",");

var AllConversionValue = json.AllConversionValue.replace(","," ").replace(".",",");

var ProductTypeL1 = json.ProductTypeL1;

var ProductTypeL2 = json.ProductTypeL2;

var ProductTypeL3 = json.ProductTypeL3;

var CategoryL1 = json.CategoryL1;

var CategoryL2 = json.CategoryL2;

var CategoryL3 = json.CategoryL3;

var CategoryL4 = json.CategoryL4;

var Impressions = json.Impressions.replace(","," ").replace(".",",");

var Brand = json.Brand;

var CustomAttribute0 = json.CustomAttribute0;

var CustomAttribute1 = json.CustomAttribute1;

var CustomAttribute2 = json.CustomAttribute2;

var CustomAttribute3 = json.CustomAttribute3;

var string =

OfferId + ";'" +

CampaignName + ";'" +

AdGroupName + ";" +

Cost + ";" +

AllConversions + ";" +

AllConversionValue + ";" +

ProductTypeL1 + ";" +

ProductTypeL2 + ";" +

ProductTypeL3 + ";" +

CategoryL1 + ";" +

CategoryL2 + ";" +

CategoryL3 + ";" +

CategoryL4 + ";" +

Impressions + ";" +

Brand + ";" +

CustomAttribute0 + ";" +

CustomAttribute1 + ";" +

CustomAttribute2 + ";" +

CustomAttribute3 + ";" +

Clicks;

return string;

}

function lynt_format_awql_date(datum){

return datum.getUTCFullYear()+("0"+(datum.getUTCMonth()+1)).slice(-2)+("0"+datum.getUTCDate()).slice(-2);

}

//Daylight saving time correction

function lynt_DST(datum, offset){

var yr = datum.getFullYear();

var dst_start = new Date("March 14, " + yr +" 02:00:00");

var dst_end = new Date("November 07, " + yr +" 02:00:00");

var day = dst_start.getDay();

dst_start.setDate(14-day);

day = dst_end.getDay();

dst_end.setDate(7-day);

if (datum >= dst_start && datum < dst_end){

return offset+1;

} else {

return offset;

}

//Getting data for x days back in right format for AWQL query

function lynt_get_date(pocet_dni, zona) {

var minule = new Date();

//kdyz je letni cas, tak o hodinu dele (GMT+1)

var offset = lynt_DST(minule, zona);

minule.setTime(minule.getTime() - (1000 * 60 * 60 * (24) * pocet_dni) + offset);

return lynt_format_awql_date(minule);

}

Comments

Alexandre Langzam says

February 17, 2017 at 5:17 pm

Hi Jakub,
went to your conference as well it was brilliant.
So how about this script, I tried to use it but sadly I get this error message after 20 minutes : “Array length 50000211 exceeds supported capacity limit. (line 24)”
I tried to reduce the timerange a bit but it doesnt help.

Thanks

- Jakub Kašparů says
  
  February 17, 2017 at 6:27 pm
  
  You need to really decrease your time range, maybe on this account best way would be API, because you can download directly.CSV files from API. What is your number of days back?
  
  - Alexandre Langzam says
    
    February 18, 2017 at 8:29 pm
    
    Thanks for your reply, you think thats because the account is too big? could be its a pretty massive one indeed. Is there a way to filter in some specific campaign in the script?
    in term of the time I tried 180 days, then 30, then 15 it still wasn’t working. As I decreased the time range the error message would come more rapidly but would still come..
    
    Not sure what you mean by “best way would be API”? I am not at all familiar with that
    
    Thanks!
    
    - Jakub Kašparů says
      
      February 18, 2017 at 10:12 pm
      
      I deleted some minor flaw from debugging the script, which caused problems, try it now. If you like you can filter by Campaign name. Let me know.
      
      - Jakub Kašparů says
        
        February 18, 2017 at 10:25 pm
        
        And I deleted month dimension within the script, now it should be ok 🙂 Let me know 🙂
Alexandre Langzam says

February 20, 2017 at 10:54 am

Hello, thanks for your time again

no the new one is not working, I have this message “file YOUR NAME exceeds the maximum file size. (line 23)”
I tried different different time range, I took 10 days at the end, with a turnover (conv value) of only like 8000 euro in this time range and in this account I do not believe that the reason of crashing is the size of this account.

Many thanks

- Jakub Kasparu says
  
  February 20, 2017 at 11:02 am
  
  Interesting, I will look on it, but I tried this on many accounts and its working. Problem is somewhere in storing data in CSV file on your Google Disk. Let me try it out on other account, but it’s weird.
  
  - Alexandre Langzam says
    
    February 20, 2017 at 11:49 am
    
    Thanks!
    an interesting thing is that in this new script there is no input URL to copy paste
    
    the excel file which contains the results is supposed to pop up into a CSV file that didnt exist before?
    Maybe thats a part that got wrong, do you have to create the CSV file before? and where do I put it ?
    
    - Jakub Kasparu says
      
      February 20, 2017 at 12:05 pm
      
      I will shoot step by step youtube video guide this week. That would be best for the script. How many products do you have in this account? 10k ? 100k ?
      
      - Alexandre Langzam says
        
        February 20, 2017 at 12:19 pm
        
        Hi so very good question I have around 500k products.
        That might be the problem then?
        
        Do you think I could filter in the script for products with more than 1 click, that would eliminate a lot of them.
      - Alexandre Langzam says
        
        February 20, 2017 at 1:53 pm
        
        Thanks actually its working now 🙂
        
        I put the condition myself of click > 1 and now the file is openable
        
        great stuff !
Jean says

February 21, 2018 at 11:18 am

Very nice info and right to the point. I don’t know if this is in fact the best place to ask but do you folks have any thoughts on where to get some professional writers? Thanks in advance 🙂

latest movie download says

February 21, 2018 at 9:26 pm

Hello, you’re definitely correct. I frequently look over your posts thoroughly. I’m also curious about how to watch movies online, perhaps you might write about that sometimes. See you.

Reader Interactions

Comments

Leave a Reply Cancel reply