Skript pro lepší A/B testování inzerátů na Skliku

/** ID of spreadsheet to use. Fill-in either ID or URL. */
var SPREADSHEET_ID = '';
/** URL of spreadsheet to use. Fill-in either ID or URL. */
var SPREADSHEET_URL = '';
/** Sklik account. */
var ACCOUNT_USERNAME = 'autickaliska@seznam.cz';

var DATA_FOLDER = '_sklik_data';
var SNAPSHOT_FILE = 'ab_' + ACCOUNT_USERNAME + '_change_snapshot.json';
var MIN_IMPRESSIONS = 300;
var MIN_CLICKS = 50;


/**
 * Script entry point.
 */
function main() {
  var sklik = SklikAPI.Connect();
  sklik.loginFromProperties();
  if (ACCOUNT_USERNAME) {
    sklik.setUserIdByUsername(ACCOUNT_USERNAME);
  }
  var previousSnapshot = loadPreviousSnapshot();
  var currentSnapshot = getCurrentSnapshot(sklik);
  var spreadsheet;
  if (SPREADSHEET_URL) {
    spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  }
  else {
    spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
  }
  var changesSheet = lynt_create_or_get_sheet(spreadsheet, "Změny", 1);
  var changeData = getChangeData(changesSheet);
  var today = new Date();
  today.setHours(0);
  today.setMinutes(0);
  today.setSeconds(0);
  today.setMilliseconds(0);
  var yesterday = new Date(today.getTime() - 86400 * 1000);
  var processed = {};
  var changeGroups = processAndGroupChanges(currentSnapshot, previousSnapshot, changeData, today, processed);
  var adStats = fetchStatistics(sklik, changeGroups, today, yesterday);
  var resultsSheet = lynt_create_or_get_sheet(spreadsheet, "Výsledky testů", 2);
  var allResultsSheet = lynt_create_or_get_sheet(spreadsheet, "Všechny výsledky testů", 4);
  var singleAdsSheet = lynt_create_or_get_sheet(spreadsheet, "Sestavy s jedním inzerátem", 3);
  var results = [];
  var allResults = [];
  var singleAds = [];
  createResults(currentSnapshot, adStats, results, allResults, singleAds, changeData, yesterday);
  saveResults(spreadsheet, resultsSheet, allResultsSheet, singleAdsSheet, results, allResults, singleAds);
  saveChangeData(changesSheet, changeData);
  // Store inactive or deleted ad groups in the current snapshot.
  for (var groupId in previousSnapshot) {
    if (!previousSnapshot.hasOwnProperty(groupId) || processed[groupId]) {
      continue;
    }
    currentSnapshot[groupId] = previousSnapshot[groupId];
  }
  storeSnapshot(currentSnapshot);
  sklik.Client.logout();
  lynt_remove_empty_sheets(spreadsheet);
}


/**
 * Gets the current state of groups and their ads.
 */
function getCurrentSnapshot(sklik) {
  var activeCampaigns = {};
  var campaignList = sklik.Campaigns.list();
  var campaignIds = [];
  for (var i = 0; i < campaignList.campaigns.length; i++) {
    activeCampaigns[campaignList.campaigns[i].id] = (campaignList.campaigns[i].status == 'active');
    campaignIds.push(campaignList.campaigns[i].id);
  }
  var activeGroups = {};
  var batchSize = 100;
  for (var offset = 0; offset < campaignIds.length; offset += batchSize) {
    var groupList = sklik.Groups.list({
      campaignIds: campaignIds.slice(offset, offset + batchSize)
    });
    for (var i = 0; i < groupList.groups.length; i++) {
      activeGroups[groupList.groups[i].id] = (groupList.groups[i].status == 'active');
    }
  }
  var adList = sklik.Ads.list({}, {
    showId: true,
    showCreative1: true,
    showCreative2: true,
    showCreative3: true,
    showClickthruUrl: true,
    showClickthruText: true,
    showStatus: true,
    showStatusId: true,
    showSensitivity: false,
    showSensitivityId: false,
    showDeleted: false,
    showDeletedDate: false,
    showCreateDate: false,
    showPremiseMode: false,
    showPremiseId: false,
    showGroupId: true,
    showGroupName: true,
    showCampaignId: true,
    showCampaignName: true
  });
  // Group the ads by group ID.
  var groups = {};
  for (var i = 0; i < adList.ads.length; i++) {
    var ad = adList.ads[i];
    if (ad.status !== 'active' || !activeGroups[ad.group.id] || !activeCampaigns[ad.campaign.id]) {
      continue;
    }
    var adProps = {
      id: ad.id,
      creative1: ad.creative1,
      creative2: ad.creative2,
      creative3: ad.creative3,
      clickthruText: ad.clickthruText
    };
    if (groups[ad.group.id] == undefined) {
      groups[ad.group.id] = {
        group: ad.group,
        campaign: ad.campaign,
        ads: [adProps]
      };
    }
    else {
      groups[ad.group.id].ads.push(adProps);
    }
  }
  for (var groupId in groups) {
    if (!groups.hasOwnProperty(groupId)) {
      continue;
    }
    groups[groupId].ads.sort(function(lhs, rhs) {
      return lhs.id - rhs.id;
    });
  }
  return groups;
}


/**
 * Loads previously stored snapshot. If no snapshot is stored, returns NULL.
 */
function loadPreviousSnapshot() {
  var snapshot = lynt_store_load(DATA_FOLDER, SNAPSHOT_FILE);
  if (snapshot) {
    return snapshot;
  }
  else {
    return {};
  }
}


/**
 * Saves the current snapshot.
 */
function storeSnapshot(snapshotObj) {
  lynt_store_save(DATA_FOLDER, SNAPSHOT_FILE, snapshotObj);
}


/**
 * Loads list of groups and date of last modification from the sheet.
 */
function getChangeData(sheet) {
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  if (lastRow > 1 && lastCol > 0) {
    var range = sheet.getRange(2, 1, lastRow - 1, lastCol);
    var data = range.getValues();
    var changes = {};
    for (var i = 0; i < data.length; i++) {
      var row = data[i];
      changes[row[0]] = {
        groupId: row[0],
        campaignName: row[1],
        groupName: row[2],
        lastChangeAt: row[3]
      };
    }
    return changes;
  }
  else {
    return {};
  }
}


/**
 * Compares two snapshots, updates collection of last modification dates and groups the ad groups by the date of last
 * modification. The ad groups are grouped for more effective querying for ad statistics.
 */
function processAndGroupChanges(currentSnapshot, previousSnapshot, changeData, today, processed) {
  var changeGroups = {};
  for (var groupId in currentSnapshot) {
    if (!currentSnapshot.hasOwnProperty(groupId)) {
      continue;
    }
    var current = currentSnapshot[groupId];
    var prev = previousSnapshot[groupId];
    processed[groupId] = true;
    var changed;
    if (!prev) {
      changed = true;
    }
    else {
      var currJSON = JSON.stringify(current.ads);
      var prevJSON = JSON.stringify(prev.ads);
      changed = (currJSON != prevJSON);
    }
    var date;
    if (changed || !changeData[groupId]) {
      date = today;
    }
    else {
      date = changeData[groupId].lastChangeAt;
    }
    if (!changeData[groupId]) {
      changeData[groupId] = {
        groupId: groupId,
        campaignName: current.campaign.name,
        groupName: current.group.name,
        lastChangeAt: date
      };
    }
    else {
      changeData[groupId].lastChangeAt = date;
    }
    var dateKey = date.toDateString();
    if (!changeGroups[dateKey]) {
      changeGroups[dateKey] = [];
    }
    changeGroups[dateKey].push(current);
  }
  return changeGroups;
}


/**
 * Fetches statistics for ad groups.
 */
function fetchStatistics(sklik, changeGroups, today, yesterday) {
  var adStats = {};
  var bulkSize = 100;
  for (var date in changeGroups) {
    if (!changeGroups.hasOwnProperty(date)) {
      continue;
    }
    var ids = [];
    var groups = changeGroups[date];
    for (var i = 0; i < groups.length; i++) {
      var group = groups[i];
      for (var j = 0; j < group.ads.length; j++) {
        ids.push(group.ads[j].id);
      }
    }
    var lastChangeAt = new Date(date);
    if (lastChangeAt < today) {
      for (var offset = 0; offset < ids.length; offset += bulkSize) {
        var stats = sklik.Ads.stats(ids.slice(offset, offset + bulkSize), {
          dateFrom: lastChangeAt,
          dateTo: yesterday,
          granularity: 'total'
        });
        for (var i = 0; i < stats.report.length; i++) {
          var adReport = stats.report[i];
          adStats[adReport.adId] = {
            id: adReport.adId,
            impressions: adReport.stats[0].impressions,
            clicks: adReport.stats[0].clicks
          };
        }
      }
    }
  }
  return adStats;
}


/**
 * Performs A/B testing and creates result report.
 */
function createResults(currentSnapshot, adStats, results, allResults, singleAds, changeData, statsTo) {
  for (var groupId in currentSnapshot) {
    if (!currentSnapshot.hasOwnProperty(groupId)) {
      continue;
    }
    var group = currentSnapshot[groupId];
    var ads = currentSnapshot[groupId].ads.slice(0);
    if (ads.length == 1) {
      singleAds.push([group.campaign.name, group.group.name, adStats[ads[0].id] ? adStats[ads[0].id].impressions : 0]);
    }
    else {
      var groupImpressions = 0;
      for (var i = 0; i < ads.length; i++) {
        var ad = ads[i];
        if (adStats[ad.id]) {
          groupImpressions += adStats[ad.id].impressions;
        }
      }
      ads.sort(function(lhs, rhs) {
        return (adStats[rhs.id] ? adStats[rhs.id].impressions : 0) - (adStats[lhs.id] ? adStats[lhs.id].impressions : 0);
      });
      var stats0 = adStats[ads[0].id];
      var stats1 = adStats[ads[1].id];
      var result;
      if (stats0 && stats1) {
        if ((stats0.impressions + stats1.impressions) > MIN_IMPRESSIONS && (stats0.clicks + stats1.clicks) > MIN_CLICKS) {
          result = getAdWinner(stats0.impressions, stats0.clicks, stats1.impressions, stats1.clicks, 95);
        }
        else {
          result = Winner.UNDECIDED_SHORT_INTERVAL;
        }
      }
      else {
        result = Winner.UNDECIDED_SHORT_INTERVAL;
      }
      var win;
      var winUrl;
      if (result == Winner.WINNER_A) {
        win = ads[0].creative1;
        winUrl = formatAdvertUrl(group.campaign.id, group.group.id, changeData[groupId].lastChangeAt, statsTo);
      }
      else if (result == Winner.WINNER_B) {
        win = ads[1].creative1;
        winUrl = formatAdvertUrl(group.campaign.id, group.group.id, changeData[groupId].lastChangeAt, statsTo);
      }
      else if (result == Winner.UNDECIDED) {
        win = "Nelze rozhodnout.";
        winUrl = "";
      }
      else if (result == Winner.UNDECIDED_SMALL_SAMPLE) {
        win = "Malý počet zobrazení a prokliků.";
        winUrl = "";
      }
      else if (result == Winner.UNDECIDED_SHORT_INTERVAL) {
        win = "Inzeráty změněny, příliš malý interval pro vyhodnocení.";
        winUrl = "";
      }
      var resultRow = [group.campaign.name, group.group.name, groupImpressions, win, winUrl];
      allResults.push(resultRow);
      if (result == Winner.WINNER_A || result == Winner.WINNER_B) {
        results.push(resultRow);
      }
    }
  }
  // Sort by group impressions.
  results.sort(function(lhs, rhs) {
    // [2]: groupImpression.
    return rhs[2] - lhs[2];
  });
  allResults.sort(function(lhs, rhs) {
    // [2]: groupImpression.
    return rhs[2] - lhs[2];
  });
  singleAds.sort(function(lhs, rhs) {
    // [2]: groupImpression.
    return rhs[2] - lhs[2];
  });
  singleAds.unshift(["Kampaň", "Sestava", "Počet zobrazení sestavy"]);
  allResults.unshift(["Kampaň", "Sestava", "Počet zobrazení sestavy", "Vítězná reklama", "URL reklamy"]);
  results.unshift(["Kampaň", "Sestava", "Počet zobrazení sestavy", "Vítězná reklama", "URL reklamy"]);
}


/**
 * Saves results to spreadsheets.
 */
function saveResults(spreadsheet, resultsSheet, allResultsSheet, singleAdsSheet, results, allResults, singleAds) {
  resultsSheet.clear();
  var resultsRange = resultsSheet.getRange(1, 1, results.length, results[0].length);
  resultsRange.setValues(results);
  resultsRange.offset(0, 0, 1, results[0].length).setFontWeight("bold");
  allResultsSheet.clear();
  var allResultsRange = allResultsSheet.getRange(1, 1, allResults.length, allResults[0].length);
  allResultsRange.setValues(allResults);
  allResultsRange.offset(0, 0, 1, allResults[0].length).setFontWeight("bold");
  singleAdsSheet.clear();
  var singleAdsRange = singleAdsSheet.getRange(1, 1, singleAds.length, singleAds[0].length);
  singleAdsRange.setValues(singleAds);
  singleAdsRange.offset(0, 0, 1, singleAds[0].length).setFontWeight("bold");
  autoResizeSheetColumns(resultsSheet);
  autoResizeSheetColumns(allResultsSheet);
  autoResizeSheetColumns(singleAdsSheet);
  spreadsheet.setActiveSheet(resultsSheet);
}


/**
 * Saves last modification dates to spreadsheets.
 */
function saveChangeData(sheet, changeData) {
  sheet.clear();
  var data = [
        ['ID sestavy', 'Kampaň', 'Sestava', 'Poslední změna']
    ];
  for (var groupId in changeData) {
    if (!changeData.hasOwnProperty(groupId)) {
      continue;
    }
    var entry = changeData[groupId];
    data.push([
            entry.groupId,
            entry.campaignName,
            entry.groupName,
            entry.lastChangeAt.toLocaleDateString()
        ]);
  }
  var range = sheet.getRange(1, 1, data.length, data[0].length);
  range.setValues(data);
  range.offset(0, 0, 1, data[0].length).setFontWeight("bold");
  autoResizeSheetColumns(sheet);
}


/**
 * Normal distribution.
 */
function cnormdist(x) {
  var b1 = 0.319381530;
  var b2 = -0.356563782;
  var b3 = 1.781477937;
  var b4 = -1.821255978;
  var b5 = 1.330274429;
  var p = 0.2316419;
  var c = 0.39894228;
  var t;
  if (x >= 0.0) {
    t = 1.0 / (1.0 + p * x);
    return (1.0 - c * Math.exp(-x * x / 2.0) * t *
      (t * (t * (t * (t * b5 + b4) + b3) + b2) + b1));
  }
  else {
    t = 1.0 / (1.0 - p * x);
    return (c * Math.exp(-x * x / 2.0) * t *
      (t * (t * (t * (t * b5 + b4) + b3) + b2) + b1));
  }
}
/**
 * Winner enumeration definition.
 */
var Winner;
(function(Winner) {
  Winner[Winner["WINNER_A"] = 0] = "WINNER_A";
  Winner[Winner["WINNER_B"] = 1] = "WINNER_B";
  Winner[Winner["UNDECIDED"] = 2] = "UNDECIDED";
  Winner[Winner["UNDECIDED_SMALL_SAMPLE"] = 3] = "UNDECIDED_SMALL_SAMPLE";
  Winner[Winner["UNDECIDED_SHORT_INTERVAL"] = 4] = "UNDECIDED_SHORT_INTERVAL";
})(Winner || (Winner = {}));


/**
 * Evaluates winning ad (A/B testing).
 */
function getAdWinner(impressionA, clicksA, impressionB, clicksB, probability) {
  var ctrA = clicksA / impressionA;
  var ctrB = clicksB / impressionB;
  var errA = Math.sqrt(ctrA * (1 - ctrA) / impressionA);
  var errB = Math.sqrt(ctrB * (1 - ctrB) / impressionB);
  var p = cnormdist((ctrA - ctrB) / Math.sqrt(errA * errA + errB * errB));
  if (p > probability / 100) {
    return Winner.WINNER_A;
  }
  else if (p < 1 - probability / 100) {
    return Winner.WINNER_B;
  }
  else {
    return Winner.UNDECIDED;
  }
}


/**
 * Gets URL for editing the given advert.
 */
function formatAdvertUrl(campaignId, groupId, dateFrom, dateTo) {
  var dateFromStr = new Date(dateFrom.getTime() - dateFrom.getTimezoneOffset() * 60 * 1000).toISOString().substr(0, 10);
  var dateToStr = new Date(dateTo.getTime() - dateTo.getTimezoneOffset() * 60 * 1000).toISOString().substr(0, 10);
  return 'https://www.sklik.cz/campaigns/' + campaignId + '/groups/' + groupId + '/adverts?table=(dir:DESC,limit:10,page:1,sort:ctr)&dateRange=(from:%27' + dateFromStr + '%27,to:%27' + dateToStr + '%27)&status=active';
}


/**
 * Resizes columns in sheet.
 */
function autoResizeSheetColumns(sheet) {
  var cols = sheet.getLastColumn();
  for (var i = 1; i <= cols; i++) {
    sheet.autoResizeColumn(i);
  }
}


/**
 * Gets the day specified number of days before today.
 *
 * This function correctly transforms between normal time and DST.
 */
function lynt_get_date(daysBack) {
  var past = new Date();
  past.setHours(0);
  past.setMinutes(0);
  past.setSeconds(0);
  past.setMilliseconds(0);
  var originalOffset = past.getTimezoneOffset();
  var pastTime = past.getTime() - daysBack * 86400 * 1000;
  past.setTime(pastTime);
  var newOffset = past.getTimezoneOffset();
  var offsetDiff = newOffset - originalOffset;
  if (offsetDiff != 0) {
    pastTime += offsetDiff * 60 * 1000;
    past.setTime(pastTime);
  }
  return past;
}


/**
 * Gets the last sunday.
 */
function lynt_get_suday() {
  var dayOffset = new Date().getDay();
  return lynt_get_date(dayOffset);
}


/**
 * Gets the date specified number of days before the last sunday.
 */
function lynt_get_xdays_back_from_sunday(pocet_dni) {
  var dayOffset = new Date().getDay();
  return lynt_get_date(dayOffset + pocet_dni);
}


/**
 * Adds specified number of milliseconds to the specified date.
 *
 * This function correctly transforms between normal time and DST.
 */
function lynt_date_add(date, miliseconds) {
  var newTime = date.getTime() + miliseconds;
  var newDate = new Date(newTime);
  var offsetDiff = newDate.getTimezoneOffset() - date.getTimezoneOffset();
  if (offsetDiff != 0) {
    newTime += offsetDiff * 60 * 1000;
    newDate.setTime(newTime);
  }
  return newDate;
}


var SortOrder;
(function(SortOrder) {
  SortOrder[SortOrder["ASC"] = 0] = "ASC";
  SortOrder[SortOrder["DESC"] = 1] = "DESC";
})(SortOrder || (SortOrder = {}));


/**
 * Creates or opens specified sheet in spreadsheet.
 */
function lynt_create_or_get_sheet(spreadsheet, name, position) {
  var sheet = spreadsheet.getSheetByName(name);
  if (sheet == null) {
    sheet = spreadsheet.insertSheet(name, position);
  }
  return sheet;
}

function lynt_remove_empty_sheets(spreadsheet) {
  var sheets = spreadsheet.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getLastRow() == 0) {
      spreadsheet.deleteSheet(sheets[i]);
    }
  }
}

function lynt_highlight_range(range, higlightingRules) {
  var rows = range.getNumRows();
  var cols = range.getNumColumns();
  var values = range.getValues();
  var backgrounds = new Array(rows);
  var value, color;
  for (var r = 0; r < rows; r++) {
    backgrounds[r] = new Array(cols);
    for (var c = 0; c < cols; c++) {
      color = '#FFFFFF';
      value = values[r][c];
      if (typeof(value) == 'number') {
        for (var ri = 1; ri < higlightingRules.length; ri++) {
          if (value <= higlightingRules[ri].value) {
            color = lynt_color_to_hex(lynt_get_color_from_range(value, higlightingRules[ri - 1], higlightingRules[ri]));
            break;
          }
        }
      }
      backgrounds[r][c] = color;
    }
  }
  range.setBackgrounds(backgrounds);
}


/**
 * Computes color from the given range.
 */
function lynt_get_color_from_range(value, upper, lower) {
  var ratio = (value - lower.value) / (upper.value - lower.value);
  return {
    r: lower.color.r + ratio * (upper.color.r - lower.color.r),
    g: lower.color.g + ratio * (upper.color.g - lower.color.g),
    b: lower.color.b + ratio * (upper.color.b - lower.color.b),
  };
}

function lynt_color_to_hex(color) {
  var rHex = Math.round(color.r).toString(16);
  var gHex = Math.round(color.g).toString(16);
  var bHex = Math.round(color.b).toString(16);
  if (rHex.length < 2) {
    rHex = '0' + rHex;
  }
  if (gHex.length < 2) {
    gHex = '0' + gHex;
  }
  if (bHex.length < 2) {
    bHex = '0' + bHex;
  }
  return '#' + rHex + gHex + bHex;
}


function lynt_store_save(dataFolderName, fileName, object) {
  var objectJson = JSON.stringify(object);
  var dataFolder = lynt_get_data_folder(dataFolderName);
  var files = dataFolder.getFilesByName(fileName);
  var dataFile;
  if (files.hasNext()) {
    dataFile = files.next();
    dataFile.setContent(objectJson);
  }
  else {
    dataFolder.createFile(fileName, objectJson, "application/json");
  }
  return true;
}

function lynt_store_load(dataFolderName, fileName) {
  var dataFolder = lynt_get_data_folder(dataFolderName);
  var files = dataFolder.getFilesByName(fileName);
  var dataFile;
  if (files.hasNext()) {
    dataFile = files.next();
  }
  if (dataFile) {
    var objectJson = dataFile.getBlob().getDataAsString("UTF-8");
    return JSON.parse(objectJson);
  }
  else {
    return null;
  }
}

function lynt_get_data_folder(dataFolderName) {
  var root = DriveApp.getRootFolder();
  var folders = root.getFoldersByName(dataFolderName);
  var dataFolder;
  if (folders.hasNext()) {
    dataFolder = folders.next();
  }
  else {
    dataFolder = root.createFolder(dataFolderName);
  }
  return dataFolder;
}