Sklik skript pro lepší report vyhledávacích dotazů

/** ID of spreadsheet to use. Fill-in either ID or URL. */
var SPREADSHEET_URL = "";
/** URL of spreadsheet to use. Fill-in either ID or URL. */
var SPREADSHEET_ID = null;
/** Account to analyze. */
var ACCOUNT_USERNAME = '';

var DATA_FOLDER = '_sklik_data';
var DATA_FILE = 'search_queries_' + ACCOUNT_USERNAME + '_neg_kw_snapshot';
/**
 * String or regular expression for campaign matching. Set if you want to track and fetch statistics only for campaigns
 * containing a string or matching a regular expression.
 */
var CAMPAIGN_NAME_MATCH = null;
/** Set to true if you want to exclude keywords with exact match type. */
var IGNORE_EXACT_KEYWORDS = false;


/**
 * Script entry-point.
 */
function main() {
  var spreadsheet;
  if (SPREADSHEET_ID) {
    spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
  }
  else {
    spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  }
  var overviewSheet = lynt_create_or_get_sheet(spreadsheet, "Přehled", 1);
  var lastModifications = getLastModifications(overviewSheet);
  var queriesSheet = lynt_create_or_get_sheet(spreadsheet, "Vyhledávací dotazy", 2);
  var checkedQueriesSheet = lynt_create_or_get_sheet(spreadsheet, "Zkontrolované dotazy", 3);
  var checkedQueries = loadCheckedQueries(checkedQueriesSheet);
  var sklik = SklikAPI.Connect();
  sklik.loginFromProperties();
  if (ACCOUNT_USERNAME) {
    sklik.setUserIdByUsername(ACCOUNT_USERNAME);
  }
  var campaignsListRes = sklik.Campaigns.list().campaigns;
  var campaignIds = [];
  var campaigns = {};
  for (var i_1 = 0; i_1 < campaignsListRes.length; i_1++) {
    var campaign = campaignsListRes[i_1];
    if (CAMPAIGN_NAME_MATCH && !campaign.name.match(CAMPAIGN_NAME_MATCH)) {
      continue;
    }
    if (campaign.status == "active") {
      campaignIds[i_1] = campaign.id;
      campaigns[campaign.id] = {
        name: campaign.name
      };
    }
  }
  var batchSize = 100;
  for (var offset = 0; offset < campaignIds.length; offset += batchSize) {
    var campaignsGetRes = sklik.Campaigns.get(campaignIds.slice(offset, offset + batchSize)).campaigns;
    for (var i_2 = 0; i_2 < campaignsGetRes.length; i_2++) {
      var campaign = campaignsGetRes[i_2];
      var negativeKeywords = new Array(campaign.negativeKeywords.length);
      for (var kwI = 0; kwI < campaign.negativeKeywords.length; kwI++) {
        negativeKeywords[kwI] = formatNegativeKeyword(campaign.negativeKeywords[kwI]);
      }
      negativeKeywords.sort();
      campaigns[campaign.id].negativeKeywords = negativeKeywords;
    }
  }
  var groupIds = [];
  var groups = {};
  for (var offset = 0; offset < campaignIds.length; offset += batchSize) {
    var groupsListRes = sklik.Groups.list({
      campaignIds: campaignIds.slice(offset, offset + batchSize)
    }, ['deleted', 'cpc', 'cpcContext', 'cpt', 'createDate', 'maxUserDailyImpression']).groups;
    for (var i_3 = 0; i_3 < groupsListRes.length; i_3++) {
      var group = groupsListRes[i_3];
      if (group.status === "active") {
        groupIds.push(group.id);
        groups[group.id] = {
          name: group.name,
          campaignId: group.campaign.id,
          positiveKeywords: {},
          negativeKeywords: []
        };
      }
    }
  }
  for (var offset = 0; offset < groupIds.length; offset += batchSize) {
    var keywordListRes = sklik.Keywords.list({
      groupIds: groupIds.slice(offset, offset + batchSize),
      positiveKeywords: true,
      negativeKeywords: true
    }, {}, ["createDate", "deleted", "url", "cpc"]).keywords;
    for (var i_4 = 0; i_4 < keywordListRes.length; i_4++) {
      var keyword = keywordListRes[i_4];
      if (!keyword.disabled) {
        var negativeFmt = formatNegativeKeyword(keyword);
        if (negativeFmt === null) {
          if (IGNORE_EXACT_KEYWORDS && keyword.matchType === 'exact') {
            continue;
          }
          // Positive keyword
          groups[keyword.group.id].positiveKeywords[keyword.id] = {
            name: keyword.name,
            matchType: keyword.matchType
          };
        }
        else {
          // Negative keyword
          groups[keyword.group.id].negativeKeywords.push(negativeFmt);
        }
      }
    }
  }
  var prevCampaignSnapshots = {};
  var currCampaignSnapshots = {};
  var today = lynt_get_date(0);
  for (var groupId in groups) {
    if (groups.hasOwnProperty(groupId)) {
      var group = groups[groupId];
      group.negativeKeywords.sort();
      var currCampaignSnapshot = void 0;
      if (group.campaignId in currCampaignSnapshots) {
        currCampaignSnapshot = currCampaignSnapshots[group.campaignId];
      }
      else {
        currCampaignSnapshots[group.campaignId] = currCampaignSnapshot = {
          negativeKeywords: JSON.stringify(campaigns[group.campaignId].negativeKeywords),
          groupNegativeKeywords: {}
        };
      }
      var prevCampaignSnapshot = void 0;
      if (group.campaignId in prevCampaignSnapshots) {
        prevCampaignSnapshot = prevCampaignSnapshots[group.campaignId];
      }
      else {
        prevCampaignSnapshots[group.campaignId] = prevCampaignSnapshot = lynt_store_load(DATA_FOLDER, DATA_FILE + '_' + group.campaignId + '.json');
      }
      currCampaignSnapshot.groupNegativeKeywords[groupId] = JSON.stringify(group.negativeKeywords);
      if (groupId in lastModifications) {
        if (prevCampaignSnapshot && (groupId in prevCampaignSnapshot.groupNegativeKeywords)) {
          if (currCampaignSnapshot.negativeKeywords != prevCampaignSnapshot.negativeKeywords ||
            (currCampaignSnapshot.groupNegativeKeywords[groupId] != prevCampaignSnapshot.groupNegativeKeywords[groupId])) {
            lastModifications[groupId].lastChange = today;
          }
        }
        else {
          lastModifications[groupId].lastChange = today;
        }
      }
      else {
        lastModifications[groupId] = {
          campaignName: campaigns[group.campaignId].name,
          groupName: group.name,
          lastChange: today
        };
      }
    }
  }
  // Save snapshots
  saveLastModifications(overviewSheet, lastModifications);
  for (var campaignId in currCampaignSnapshots) {
    if (currCampaignSnapshots.hasOwnProperty(campaignId)) {
      lynt_store_save(DATA_FOLDER, DATA_FILE + '_' + campaignId + '.json', currCampaignSnapshots[campaignId]);
    }
  }
  var groupIdsByLastMod = {};
  for (var groupId in groups) {
    if (groups.hasOwnProperty(groupId)) {
      var lastModTime = lastModifications[groupId].lastChange.getTime();
      if (!(lastModTime in groupIdsByLastMod)) {
        groupIdsByLastMod[lastModTime] = [];
      }
      groupIdsByLastMod[lastModTime].push(groupId);
    }
  }
  var dateTo = lynt_get_date(1);
  var dateToTime = dateTo.getTime();
  var searchQueries = [];
  queriesSheet.clear();
  var lastEmptyRow = queriesSheet.getMaxRows();
  if (lastEmptyRow > 2) {
    queriesSheet.deleteRows(3, lastEmptyRow - 2);
  }
  var lastEmptyCol = queriesSheet.getMaxColumns();
  if (lastEmptyCol > 10) {
    queriesSheet.deleteColumns(11, lastEmptyCol - 10);
  }
  queriesSheet.getRange(1, 1, 1, 10).setValues([
        ['Vyhledávací dotaz', 'Klíčové slovo', 'Název reklamní sestavy', 'Název kampaně', 'Zobrazení', 'Prokliky', 'CTR', 'Konverze', 'Cena konverze', 'PNO']
    ]).setFontWeight('bold');
  var rowOffset = 2;
  var kwBatchSize = 5000;
  for (var time in groupIdsByLastMod) {
    if (groupIdsByLastMod.hasOwnProperty(time)) {
      if (time < dateToTime) {
        var dateFrom = new Date();
        dateFrom.setTime(time);
        var currGroupIds = groupIdsByLastMod[time];
        var keywordIds = [];
        var keywordGroupIds = {};
        for (var i_5 = 0; i_5 < currGroupIds.length; i_5++) {
          var posKw = groups[currGroupIds[i_5]].positiveKeywords;
          for (var kwId in posKw) {
            if (posKw.hasOwnProperty(kwId)) {
              keywordIds.push(parseInt(kwId, 10));
              keywordGroupIds[kwId] = currGroupIds[i_5];
            }
          }
        }
        for (var offset_1 = 0; offset_1 < keywordIds.length; offset_1 += kwBatchSize) {
          var stats = sklik.Keywords.stats(keywordIds.slice(offset_1, offset_1 + kwBatchSize), {
            dateFrom: dateFrom,
            dateTo: dateTo,
            granularity: 'total',
            splitBySearchQuery: true
          }, [
                        'transactions', 'transactionAvgPrice', 'transactionAvgValue', 'transactionAvgCount', 'lowBudgetLostImpressions',
                        'schedulingLostImpressions', 'lowRankLostImpressions', 'lowRelevanceLostImpressions', 'impressionShare', 'quality'
                    ]).report;
          for (var si = 0; si < stats.length; si++) {
            var groupId_1 = keywordGroupIds[stats[si].keywordId];
            var keyword = groups[groupId_1].positiveKeywords[stats[si].keywordId];
            var group = groups[groupId_1];
            for (var sni = 0; sni < stats[si].stats.length; sni++) {
              var queryStat = stats[si].stats[sni];
              if (checkedQueries[queryStat.searchQuery]) {
                continue;
              }
              if (!queryStat.searchQuery) {
                continue;
              }
              searchQueries.push([
                                queryStat.searchQuery,
                                formatPositiveKeyword(keyword),
                                group.name,
                                campaigns[group.campaignId].name,
                                queryStat.impressions,
                                queryStat.clicks,
                                queryStat.ctr,
                                queryStat.conversions,
                                queryStat.conversionAvgPrice,
                (queryStat.conversionValue > 0 ? 100 * queryStat.price / queryStat.conversionValue : '')
                            ]);
            }
            if (searchQueries.length >= 5000) {
              queriesSheet.insertRows(rowOffset, searchQueries.length);
              queriesSheet.getRange(rowOffset, 1, searchQueries.length, 10).setValues(searchQueries);
              rowOffset += searchQueries.length;
              searchQueries = [];
            }
          }
        }
      }
    }
  }
  if (searchQueries.length > 0) {
    queriesSheet.insertRows(rowOffset, searchQueries.length);
    queriesSheet.getRange(rowOffset, 1, searchQueries.length, 10).setValues(searchQueries);
  }
  queriesSheet.setFrozenRows(1);
  queriesSheet.sort(5, false);
  for (var i = 1; i <= 10; i++) {
    queriesSheet.autoResizeColumn(i);
  }
  lynt_remove_empty_sheets(spreadsheet);
}


/**
 * Get the date of last modification.
 */
function getLastModifications(overviewSheet) {
  var lastRow = overviewSheet.getLastRow();
  var lastCol = overviewSheet.getLastColumn();
  if (lastRow > 1 && lastCol >= 4) {
    var data = overviewSheet.getRange(2, 1, lastRow, 4).getValues();
    var changes = {};
    for (var r = 0; r < data.length; r++) {
      var row = data[r];
      if (row.length > 3 && row[0] && row[3]) {
        changes[row[0]] = {
          campaignName: row[1],
          groupName: row[2],
          lastChange: row[3]
        };
      }
    }
    return changes;
  }
  else {
    return {};
  }
}


/**
 * Saves last modifications to spreadsheets.
 */
function saveLastModifications(overviewSheet, changes) {
  overviewSheet.clear();
  var data = [
        ['ID sestavy', 'Název kampaně', 'Jméno sestavy', 'Poslední změna']
    ];
  for (var groupId in changes) {
    if (changes.hasOwnProperty(groupId)) {
      var groupChangeInfo = changes[groupId];
      data.push([
                groupId,
                groupChangeInfo.campaignName,
                groupChangeInfo.groupName,
                groupChangeInfo.lastChange
            ]);
    }
  }
  var dataRange = overviewSheet.getRange(1, 1, data.length, data[0].length);
  dataRange.setValues(data);
  for (var i = 1; i <= 4; i++) {
    overviewSheet.autoResizeColumn(i);
  }
}


/**
 * Loads a list of queries that were already checked.
 */
function loadCheckedQueries(sheet) {
  var lastRow = sheet.getLastRow();
  if (lastRow > 1) {
    var range = sheet.getRange(2, 1, lastRow - 1, 1);
    var data = range.getValues();
    var result = {};
    for (var i = 0; i < data.length; i++) {
      result[data[i][0]] = true;
    }
    return result;
  }
  else {
    sheet.getRange(1, 1).setValue("Zkontrolované dotazy").setFontWeight("bold");
    sheet.setFrozenRows(1);
    sheet.setColumnWidth(1, 400);
    return {};
  }
}


/**
 * Formats negative keyword to PPC-guy friendly representation.
 */
function formatNegativeKeyword(keyword) {
  switch (keyword.matchType) {
    case 'negativeBroad':
      return keyword.name;
    case 'negativePhrase':
      return '"' + keyword.name + '"';
    case 'negativeExact':
      return '[' + keyword.name + ']';
    default:
      return null;
  }
}


/**
 * Formats positive keyword to PPC-guy friendly representation.
 */
function formatPositiveKeyword(keyword) {
  switch (keyword.matchType) {
    case 'broad':
      return keyword.name;
    case 'phrase':
      return '"' + keyword.name + '"';
    case 'exact':
      return '[' + keyword.name + ']';
    default:
      return null;
  }
}


/**
 * 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;
}