//Skript pro porovnání CTR rozšířených textových reklam a starých formátů
//podle umístění reklamy a podle zařízení.
//návody a další AdWords skripty na www.ondrasvarc.cz
var vzor_url = 'SEM_VLOŽTE_CELOU_URL_VZOROVÉHO_SEŠITU_Z_VAŠEHO_GDRIVE'; //URL vzorového sešitu do uvozovek ve formátu https://docs.google.com/spreadsheets/d/UNIKATNI_ID_SESITU/edit
var vzor = SpreadsheetApp.openByUrl(vzor_url);
function main(){
var historie = getImpressionHistory();
extendedCTR(historie, vzor);
}
function extendedCTR(ret_map, vzor) {
var spreadsheet = vzor.copy('Porovnání CTR starých a nových reklam ' + AdWordsApp.currentAccount().getName());
var umisteni = spreadsheet.getSheetByName('Umístění');
var zarizeni = spreadsheet.getSheetByName('Zařízení');
var detailne = spreadsheet.getSheetByName('Detail');
var iter = iter = AdWordsApp.adGroups().get();
while(iter.hasNext()) {
var AdGroup = iter.next();
var id = AdGroup.getId();
var kampan = AdGroup.getCampaign().getName();
var sestava = AdGroup.getName();
if(ret_map[id]) {
var API_VERSION = { includeZeroImpressions : false };
var from = ret_map[id];
var rok = from.slice(0,4);
var mesic = from.slice(4,6);
var den = from.slice(6,8);
var prev_days_ago = 0;
var end_date = getDateDaysAgo(prev_days_ago);
var date_range = from+','+end_date;
var creationDate = den + "-" + mesic + "-" + rok;
var cols = ['CampaignName', 'AdGroupName','AdType','Device','AdNetworkType2', 'Impressions', 'Clicks'];
var reportType = 'AD_PERFORMANCE_REPORT';
var query = ['select',cols.join(','),'from',reportType,'where AdGroupId IN [' + id + '] ', 'during',date_range].join(' ');
var report = AdWordsApp.report(query, API_VERSION);
var rows = report.rows();
var eta_imp_s = 0; var eta_clck_s = 0; //ETA imprese a kliky v searchi
var old_imp_s = 0; var old_clck_s = 0; //old imprese a kliky v searchi
var eta_imp_o = 0; var eta_clck_o = 0; //eta imp a clck v ostatni
var old_imp_o = 0; var old_clck_o = 0; //old imp a clck v ostatni
var eta_mobile_imp_s = 0; var eta_mobile_clck_s = 0; //ETA mobile search
var eta_tablet_imp_s = 0; var eta_tablet_clck_s = 0; //ETA tablet search
var eta_comp_imp_s = 0; var eta_comp_clck_s = 0; //ETA computer search
var old_mobile_imp_s = 0; var old_mobile_clck_s = 0; //old mobile search
var old_tablet_imp_s = 0; var old_tablet_clck_s = 0; //old tablet search
var old_comp_imp_s = 0; var old_comp_clck_s = 0; //old comp search
var eta_mobile_imp_o = 0; var eta_mobile_clck_o = 0; //ETA mobile other
var eta_tablet_imp_o = 0; var eta_tablet_clck_o = 0; //ETA tablet other
var eta_comp_imp_o = 0; var eta_comp_clck_o = 0; //ETA comp other
var old_mobile_imp_o = 0; var old_mobile_clck_o = 0; //old mobile other
var old_tablet_imp_o = 0; var old_tablet_clck_o = 0; //old tablet other
var old_comp_imp_o = 0; var old_comp_clck_o = 0; //old computer other
while(rows.hasNext()){
var row = rows.next();
var zobrazeni = Number(row['Impressions']); //number from string
var kliknuti = Number(row['Clicks']);
if(row['AdType'] == 'Expanded text ad'){
if(row['AdNetworkType2'] == 'Google search'){
eta_imp_s = eta_imp_s + zobrazeni; eta_clck_s = eta_clck_s + kliknuti;
}else{eta_imp_o = eta_imp_o + zobrazeni; eta_clck_o = eta_clck_o + kliknuti;}
if(row['Device'] == 'Mobile devices with full browsers'){
if(row['AdNetworkType2'] == 'Google search'){
eta_mobile_imp_s = eta_mobile_imp_s + zobrazeni; eta_mobile_clck_s = eta_mobile_clck_s + kliknuti;
}else{eta_mobile_imp_o = eta_mobile_imp_o + zobrazeni; eta_mobile_clck_o = eta_mobile_clck_o + kliknuti;}
}else if(row['Device'] == 'Tablets with full browsers'){
if(row['AdNetworkType2'] == 'Google search'){
eta_tablet_imp_s = eta_tablet_imp_s + zobrazeni; eta_tablet_clck_s = eta_tablet_clck_s + kliknuti;
}else{eta_tablet_imp_o = eta_tablet_imp_o + zobrazeni; eta_tablet_clck_o = eta_tablet_clck_o + kliknuti;}
}else if(row['Device'] == 'Computers'){
if(row['AdNetworkType2'] == 'Google search'){
eta_comp_imp_s = eta_comp_imp_s + zobrazeni; eta_comp_clck_s = eta_comp_clck_s + kliknuti;
}else{eta_comp_imp_o = eta_comp_imp_o + zobrazeni; eta_comp_clck_o = eta_comp_clck_o + kliknuti;}
}
}else{
if(row['AdNetworkType2'] == 'Google search'){
old_imp_s = old_imp_s + zobrazeni; old_clck_s = old_clck_s + kliknuti;
}else{old_imp_o = old_imp_o + zobrazeni; old_clck_o = old_clck_o + kliknuti;}
if(row['Device'] == 'Mobile devices with full browsers'){
if(row['AdNetworkType2'] == 'Google search'){
old_mobile_imp_s = old_mobile_imp_s + zobrazeni; old_mobile_clck_s = old_mobile_clck_s + kliknuti;
}else{old_mobile_imp_o = old_mobile_imp_o + zobrazeni; old_mobile_clck_o = old_mobile_clck_o + kliknuti;}
}else if(row['Device'] == 'Tablets with full browsers'){
if(row['AdNetworkType2'] == 'Google search'){
old_tablet_imp_s = old_tablet_imp_s + zobrazeni; old_tablet_clck_s = old_tablet_clck_s + kliknuti;
}else{old_tablet_imp_o = old_tablet_imp_o + zobrazeni; old_tablet_clck_o = old_tablet_clck_o + kliknuti;}
}else if(row['Device'] == 'Computers'){
if(row['AdNetworkType2'] == 'Google search'){
old_comp_imp_s = old_comp_imp_s + zobrazeni; old_comp_clck_s = old_comp_clck_s + kliknuti;
}else{old_comp_imp_o = old_comp_imp_o + zobrazeni; old_comp_clck_o = old_comp_clck_o + kliknuti;}
}
}
}
var eta_mobile_ctr_s = eta_mobile_clck_s/eta_mobile_imp_s; var eta_mobile_ctr_o = eta_mobile_clck_o/eta_mobile_imp_o;
var eta_tablet_ctr_s = eta_tablet_clck_s/eta_tablet_imp_s; var eta_tablet_ctr_o = eta_tablet_clck_o/eta_tablet_imp_o;
var eta_comp_ctr_s = eta_comp_clck_s/eta_comp_imp_s; var eta_comp_ctr_o = eta_comp_clck_o/eta_comp_imp_o;
var old_mobile_ctr_s = old_mobile_clck_s/old_mobile_imp_s; var old_mobile_ctr_o = old_mobile_clck_o/old_mobile_imp_o;
var old_tablet_ctr_s = old_tablet_clck_s/old_tablet_imp_s; var old_tablet_ctr_o = old_tablet_clck_o/old_tablet_imp_o;
var old_comp_ctr_s = old_comp_clck_s/old_comp_imp_s; var old_comp_ctr_o = old_comp_clck_o/old_comp_imp_o;
var old_imp_comp = old_comp_imp_s + old_comp_imp_o; var old_clck_comp = old_comp_clck_s + old_comp_clck_o;
var old_imp_mobile = old_mobile_imp_s + old_mobile_imp_o; var old_clck_mobile = old_mobile_clck_s + old_mobile_clck_o;
var old_imp_tablet = old_tablet_imp_s + old_tablet_imp_o; var old_clck_tablet = old_tablet_clck_s + old_tablet_clck_o;
var eta_imp_comp = eta_comp_imp_s + eta_comp_imp_o; var eta_clck_comp = eta_comp_clck_s + eta_comp_clck_o;
var eta_imp_mobile = eta_mobile_imp_s + eta_mobile_imp_o; var eta_clck_mobile = eta_mobile_clck_s + eta_mobile_clck_o;
var eta_imp_tablet = eta_tablet_imp_s + eta_tablet_imp_o; var eta_clck_tablet = eta_tablet_clck_s + eta_tablet_clck_o;
var old_ctr_comp = old_clck_comp/old_imp_comp; var old_ctr_mobile = old_clck_mobile/old_imp_mobile; var old_ctr_tablet = old_clck_mobile/old_imp_mobile;
var eta_ctr_comp = eta_clck_comp/eta_imp_comp; var eta_ctr_mobile = eta_clck_mobile/eta_imp_mobile; var eta_ctr_tablet = eta_clck_mobile/eta_imp_mobile;
var old_ctr_s = old_clck_s/old_imp_s; var old_ctr_o = old_clck_o/old_imp_o;
var eta_ctr_s = eta_clck_s/eta_imp_s; var eta_ctr_o = eta_clck_o/eta_imp_o;
umisteni.appendRow([creationDate, kampan, sestava, old_ctr_s, eta_ctr_s, old_ctr_o, eta_ctr_o,
old_imp_s, eta_imp_s, old_imp_o, eta_imp_o]); //umístění
zarizeni.appendRow([creationDate, kampan, sestava, old_ctr_comp, eta_ctr_comp, old_ctr_mobile, eta_ctr_mobile, old_ctr_tablet, eta_ctr_tablet,
old_imp_comp, eta_imp_comp, old_imp_mobile, eta_imp_mobile, old_imp_tablet, eta_imp_tablet]); //zařízení
detailne.appendRow([creationDate, kampan, sestava, old_comp_ctr_s, eta_comp_ctr_s, old_mobile_ctr_s, eta_mobile_ctr_s, old_tablet_ctr_s, eta_tablet_ctr_s, old_comp_ctr_o, eta_comp_ctr_o, old_mobile_ctr_o, eta_mobile_ctr_o, old_tablet_ctr_o, eta_tablet_ctr_o,
old_comp_imp_s, eta_comp_imp_s, old_mobile_imp_s, eta_mobile_imp_s, old_tablet_imp_s, eta_tablet_imp_s, old_comp_imp_o, eta_comp_imp_o, old_mobile_imp_o, eta_mobile_imp_o, old_tablet_imp_o, eta_tablet_imp_o]); //detail
Logger.log(AdGroup.getName() + " done");
}
}
Logger.log("Report je vygenerován na adrese: " + spreadsheet.getUrl());
}
function getImpressionHistory() {
var API_VERSION = { includeZeroImpressions : false };
var first_date = new Date('10/23/2000');
var max_days_ago = diffDays(first_date,new Date());
var cols = ['Date','AdGroupId','Impressions'];
var report = 'AD_PERFORMANCE_REPORT';
var ret_map = {};
var prev_days_ago = 0;
for(var i = 30; i < max_days_ago; i+=30) {
var start_date = getDateDaysAgo(i);
var end_date = getDateDaysAgo(prev_days_ago);
var date_range = start_date+','+end_date;
Logger.log('Getting data for ' + date_range);
var query = ['select',cols.join(','),'from',report,'where AdType IN [EXPANDED_TEXT_AD]', 'during',date_range].join(' ');
var report_iter = AdWordsApp.report(query, API_VERSION).rows();
if(!report_iter.hasNext()) { Logger.log('No more impressions found. Breaking.'); break; } // no more entries
while(report_iter.hasNext()) {
var row = report_iter.next();
if(ret_map[row['AdGroupId']]) {
var [year,month,day] = (row['Date']).split('-');
var from_row = new Date(year, parseFloat(month)-1, day);
var from_row_formated = Utilities.formatDate(from_row,AdWordsApp.currentAccount().getTimeZone(),"yyyyMMdd");
var from_map = ret_map[row['AdGroupId']];
if(from_row_formated < from_map) {
ret_map[row['AdGroupId']] = from_row_formated;
}
} else {
var [year,month,day] = (row['Date']).split('-');
ret_map[row['AdGroupId']] = year + month + day;
}
}
prev_days_ago = i;
}
return ret_map;
}
function diffDays(firstDate,secondDate) {
var oneDay = 24*60*60*1000; // hours*minutes*seconds*milliseconds
return Math.round(Math.abs((firstDate.getTime() - secondDate.getTime())/(oneDay)));
}
function getDateDaysAgo(days) {
var the_past = new Date();
the_past.setDate(the_past.getDate() - days);
return Utilities.formatDate(the_past,AdWordsApp.currentAccount().getTimeZone(),"yyyyMMdd");
}