Začalo to tipem od Marka Prokopa na knihovnu na které se podílel Milan Kryl Mimic-for-Google-Apps-Script Poměrně krátký skriptík nám umožnil malou revoluci. Přímo z AppScriptu lze pracovat s API Skliku a díky tomu lze načítat informace ze Skliku i Adwords v jednom scriptu bez napojení na třetí stranu.
Pak stačilo už jen najít šikovného programátora a PPČkaře v jedné osobě Standu Jílka a script byl hotov. Každý podnět, který jsme s Peťou Větrovskou jako testeři vznesli nám zapracoval a níže vidíte výsledek. Nutno říct, že si Standa se scriptem vyhrál a na podobném principu už nám běží i další script na detailní kontrolu výkonu účtů a automatický reporting.
EDIT: Nová lepší verze sctiptu je tu!
Standa script a i další užitečné scripty dále rozvíjí na svém webu, doporučuji přejít na jeho verzi scriptu s centrálním configem!
Jelikož Seznam vypustil ven nové API Drak, které umožňuje komunikovat pomocí JSON, Standa nelenil a skript přepsal. Skript se tak zkrátil a zrychlil. Navíc na přání Peti přibyly další kontrolní sloupečky, které určitě oceníte. A hlavně nezapomeňte sledovat Standův nový web! Plánuje i online kurz na skritpování, tak ať vám neuteče.
Skripty na kontrolu rozpočtů měla již dříve hotové Hanka Kobzová. Vyžadovaly ale separátní skript pro Adwords a pro Sklik. To vše “náš” skript řeší a přidali jsme ještě pár funkcí navíc:
- skript se konfiguruje z jednoho místa a to Google Dokumentu ten si zkopírujte tady
- pokud nevyplníte Sklik nebo Adwords účty nevadí, skript pojede i tak
- skript běží pod MCC
- v samotném skriptu změníte jen URL konfiguračního souboru (na řádku 3) a je hotovo
- skript hlídá zadané rozpočty
- ukazuje PNO a CPA kampaní
- informuje vás o útratách za tento měsíc a meziročně
- ukazuje informaci o včerejší útratě
- NEW běží na API Drak – JSON
- NEW doporučuje denní rozpočet
A takto vypadá výstup v mailu:
Za všechny kdo se na scriptu podíleli (Standa, Peťa Větrovská a další) doufám, že se vám skriptík bude líbit a pomůže vám k zas o trošku lepším PPC kampaním.
//Url config spreadsheet-------------------------------------------------------------------------------------------------------------
//***********************************************************************************************************************************
var ss_config = SpreadsheetApp.openByUrl('SEM VLOŽTE URL CONFIG SOUBORU');
/************************************************************************************************************************************
Vytvořil: Stanislav Jílek [standajilek.cz]
Navrhli a testovali: Karel Rujzl [rujzl.cz] a Petra Větrovská [vetrovka.cz]
Prvotní myšlenka na kontrolu rozpočtů: Hana Kobzová [hanakobzova.cz]
/***********************************************************************************************************************************/
function main() {
//Mail settings --------------------------------------------------------------------
var mail_sheet = ss_config.getSheetByName("mail_settings");
var mail_settings = mail_sheet.getRange("B1:B2").getValues();
var mail = mail_settings[0];
var subject = mail_settings[1];
//Date settings --------------------------------------------------------------------
var current_date = new Date();
current_date.setTime(current_date.getTime() + 1000 * 60 * 60 * 8);
current_date.setDate(1);
current_date.setMonth(current_date.getUTCMonth() + 1);
var month = current_date.getUTCMonth();
var year = current_date.getUTCFullYear();
var day = new Date();
day.setTime(day.getTime() + 1000 * 60 * 60 * 8);
//Last 1 month
var last_1_months_date = new Date(year, month, 0)
var last_1_months = last_1_months_date.getUTCMonth();
var last_1_months_day_end = last_1_months_date.getDate();
var last_1_months_year = last_1_months_date.getUTCFullYear();
//Last 2 months
var last_2_months_date = new Date(year, month - 1, 0)
var last_2_months = last_2_months_date.getUTCMonth();
var last_2_months_day_end = last_2_months_date.getDate();
var last_2_months_year = last_2_months_date.getUTCFullYear();
//Last 12 months
var last_12_months_date = new Date(year - 1, month, 0)
var last_12_months = last_12_months_date.getUTCMonth();
var last_12_months_day_end = last_12_months_date.getDate();
var last_12_months_year = last_12_months_date.getUTCFullYear();
//Yesterday
var yesterday = new Date();
yesterday.setTime(yesterday.getTime() + 1000 * 60 * 60 * 8);
yesterday.setUTCDate(yesterday.getUTCDate() - 1);
var datum = [
[new Date(last_1_months_year, last_1_months, 1), new Date(last_1_months_year, last_1_months, last_1_months_day_end), last_1_months_year + "/" + (last_1_months + 1)],
[new Date(last_12_months_year, last_12_months, 1), new Date(last_12_months_year, last_12_months, last_12_months_day_end), last_12_months_year + "/" + (last_12_months + 1)],
[new Date(last_2_months_year, last_2_months, 1), new Date(last_2_months_year, last_2_months, last_2_months_day_end), last_2_months_year + "/" + (last_2_months + 1)],
[yesterday, yesterday, "Včera"]
]
//Count of period
var period = datum.length;
//--------------------------------------------------------------------------------------------
//Table header
var table_header = "<tr bgcolor='#ffd75d'><th>Účet</th><th>Období</th><th>Náklady</th><th>Obrat</th><th>Konverze</th><th>CPA</th><th>PNO</th><th>Rozpočet</th><th>Kontrola</th><th>%<br>času<br>z měsíce</th><th>%<br>vyčerpaného<br>rozpočtu</th><th>Doporučený<br>denní<br>rozpočet</th></tr>"
//HTML body table
var table = "<table border='1' style='border-collapse: collapse;' cellpadding='5'>";
//--------------------------------------------------------------------------------------------
//ADWORDS*************************************************************************************
try {
//Adwords settings
var adwords_sheet = ss_config.getSheetByName("adwords_settings");
var adwords_settings = adwords_sheet.getRange("A2:B" + adwords_sheet.getLastRow()).getValues();
//Add html
table = table + "<tr><td colspan='12' bgcolor='#4fabe5'><strong>ADWORDS</strong></td></tr>" + table_header;
//Cycle for a number of accounts
for (var i = 0; i < adwords_settings.length; i++)
{
try {
//Select account
var select_account = MccApp.accounts().withIds([adwords_settings[i][0]]).get().next();
//Save MCC
var mcc_account = AdWordsApp.currentAccount();
//Make changes to the selected account
MccApp.select(select_account);
//Variable (account)
var account_name = select_account.getName();
var currency = select_account.getCurrencyCode();
//Cycle for a number of periods
for (var j = 0; j < period; j++)
{
var datum_start = Utilities.formatDate(datum[j][0], "GTM - 1", 'yyyyMMdd');
var datum_end = Utilities.formatDate(datum[j][1], "GTM - 1", 'yyyyMMdd');
var report = AdWordsApp.report("SELECT Cost, ConversionValue, Conversions FROM ACCOUNT_PERFORMANCE_REPORT DURING " + datum_start + "," + datum_end).rows().next();
//Variable (report)
var cost = (parseFloat(report['Cost'].replace(",", "").replace(",", "").replace(",", "").replace(",", ""))).toFixed(0);
var conversions_value = (parseFloat(report['ConversionValue'].replace(",", "").replace(",", "").replace(",", "").replace(",", ""))).toFixed(0);
var conversions = (parseFloat(report['Conversions'].replace(",", "").replace(",", "").replace(",", "").replace(",", ""))).toFixed(0);
var pno = ((cost / conversions_value) * 100).toFixed(2);
pno = null_control(pno, cost, conversions_value);
var cpa = (cost / conversions).toFixed(0);
cpa = null_control(cpa, cost, conversions);
var budget = adwords_settings[i][1];
var control = budget - cost;
var month_percent = (100 / last_1_months_date.getUTCDate() * day.getUTCDate()).toFixed(2);
var budget_percent = ((cost / budget) * 100).toFixed(2);
budget_percent = null_control(budget_percent, cost, budget);
var budget_plan = (control / (last_1_months_date.getUTCDate() - day.getUTCDate())).toFixed(0);
budget_plan = null_control(budget_plan, control, control);
//Add html
table = table + add_html(i, j, datum[j][2], account_name, currency, cost, conversions_value, conversions, cpa, pno, budget, control, month_percent, budget_percent, budget_plan);
}
//Back to MCC
MccApp.select(mcc_account)
} catch (err) {
Logger.log("ADWORDS: " + err);
}
}
} catch (err) {
Logger.log("ADWORDS: " + err);
}
//--------------------------------------------------------------------------------------------
//SKLIK***************************************************************************************
try {
//Sklik settings
var sklik_sheet = ss_config.getSheetByName("sklik_settings");
var sklik_settings = sklik_sheet.getRange("A5:B" + sklik_sheet.getLastRow()).getValues();
//Login to Sklik (MCC)
var mcc_login = sklik_sheet.getRange("B2:B3").getValues();
var user_name = mcc_login[0];
var user_password = mcc_login[1];
//--------------------------------------------------------------------------------------------
//client.login
var client_login = sklik_api([user_name[0], user_password[0]], 'client.login');
//--------------------------------------------------------------------------------------------
//client.get
var client_get = sklik_api([{'session': client_login.session}], 'client.get')
var sklik_account = [];
for (var i = 0; i < sklik_settings.length; i++) //Sklik account
{
for (var j = 0; j < client_get.foreignAccounts.length; j++)
{
if (sklik_settings[i][0].toLowerCase() == client_get.foreignAccounts[j].username.toLowerCase())
{
sklik_account.push([client_get.foreignAccounts[j].userId, client_get.foreignAccounts[j].username, sklik_settings[i][1]]);
}
}
}
//Add html
var table = table + "<tr><td colspan='12' bgcolor='#ff4646'><strong>SKLIK</strong></td></tr>" + table_header;
//Cycle for a number of accounts
for (var i = 0; i < sklik_account.length; i++)
{
//--------------------------------------------------------------------------------------------
//client.stats
//Cycle for a number of periods
for (var j = 0; j < period; j++)
{
var datum_start = Utilities.formatDate(datum[j][0], "GTM - 1", 'yyyy-MM-dd');
var datum_end = Utilities.formatDate(datum[j][1], "GTM - 1", 'yyyy-MM-dd');
var client_stats = sklik_api([{'session': client_login.session, 'userId': sklik_account[i][0]},
{'dateFrom': datum_start, 'dateTo': datum_end, 'granularity': 'total'}
], 'client.stats')
//Variable
var account_name = sklik_account[i][1];
var currency = "CZK";
var cost = (client_stats.report[0].price / 100).toFixed(0);
var conversions_value = (client_stats.report[0].conversionValue / 100).toFixed(0);
var conversions = (client_stats.report[0].conversions).toFixed(0);
var pno = ((cost / conversions_value) * 100).toFixed(2);
pno = null_control(pno, cost, conversions_value);
var cpa = (cost / conversions).toFixed(0);
cpa = null_control(cpa, cost, conversions);
var budget = sklik_settings[i][1];
var control = budget - cost;
var month_percent = (100 / last_1_months_date.getUTCDate() * day.getUTCDate()).toFixed(2);
var budget_percent = ((cost / budget) * 100).toFixed(2);
budget_percent = null_control(budget_percent, cost, budget);
var budget_plan = (control / (last_1_months_date.getUTCDate() - day.getUTCDate())).toFixed(0);
budget_plan = null_control(budget_plan, control, control);
//Add html
table = table + add_html(i, j, datum[j][2], account_name, currency, cost, conversions_value, conversions, cpa, pno, budget, control, month_percent, budget_percent, budget_plan);
Utilities.sleep(200)
}
}
//--------------------------------------------------------------------------------------------
//client.logout
var client_logout = sklik_api([{'session': client_login.session}], 'client.logout');
//--------------------------------------------------------------------------------------------
} catch (err) {
Logger.log("SKLIK:" + err);
}
//Add html
table = table + "</table>";
//Send mail
MailApp.sendEmail({to: mail[0], subject: subject[0], htmlBody: table});
}
//********************************************************************************************
function number_format(number) {
number = number.toString();
number = number.split("").reverse().join("");
number = number.substr(0, 3) + " " + number.substr(3, 3) + " " + number.substr(6, 3) + " " + number.substr(9, 3) + " " + number.substr(12, 3);
number = number.split("").reverse().join("");
number = number.trim();
return(number)
}
//--------------------------------------------------------------------------------------------
function control_color(control) {
if (control > 0)
{
control = "green";
} else
{
control = "red";
}
return(control)
}
//--------------------------------------------------------------------------------------------
function row_color(row) {
if (row % 2 == 0)
{
row = "#ffffff";
} else
{
row = "#d5d5d5";
}
return(row)
}
//--------------------------------------------------------------------------------------------
function null_control(number, a, b) {
if (a == 0 || b == 0) {
number = 0;
}
return (number);
}
//--------------------------------------------------------------------------------------------
function add_html(i, j, datum, account_name, currency, cost, conversions_value, conversions, cpa, pno, budget, control, month_percent, budget_percent, budget_plan) {
var table = ""
if (j == 0)
{
table = "<tr bgcolor='" + row_color(i) + "'><td rowspan='4'><strong>" + account_name + "</strong></td>" +
"<td><strong>" + datum + "</strong></td>" +
"<td align='right'><strong>" + number_format(cost) + " " + currency + "</strong></td>" +
"<td align='right'><strong>" + number_format(conversions_value) + " " + currency + "</strong></td>" +
"<td align='right'><strong>" + number_format(conversions) + "</strong></td>" +
"<td align='right'><strong>" + number_format(cpa) + " " + currency + "</strong></td>" +
"<td align='right'><strong>" + pno + " %</strong></td>" +
"<td align='right'><strong>" + number_format(budget) + " " + currency + "</strong></td>" +
"<td align='right'><strong><font color='" + control_color(control) + "'>" + number_format(control) + " " + currency + "</font></strong></td>" +
"<td align='right'><strong>" + month_percent + " %</strong></td>" +
"<td align='right'><strong>" + budget_percent + " %</strong></td>" +
"<td align='right'><strong>" + number_format(budget_plan) + " " + currency + "</strong></td></tr>";
} else
{
table = "<tr bgcolor='" + row_color(i) + "'>" +
"<td>" + datum + "</td>" +
"<td align='right'>" + number_format(cost) + " " + currency + "</td>" +
"<td align='right'>" + number_format(conversions_value) + " " + currency + "</td>" +
"<td align='right'>" + number_format(conversions) + "</td>" +
"<td align='right'>" + number_format(cpa) + " " + currency + "</td>" +
"<td align='right'>" + pno + " %</td></tr>";
}
return(table)
}
//--------------------------------------------------------------------------------------------
function sklik_api(parameters, method) {
return(JSON.parse(UrlFetchApp.fetch('https://api.sklik.cz/jsonApi/drak/' + method, {'method': 'post', 'contentType': 'application/json', 'muteHttpExceptions': true, 'payload': JSON.stringify(parameters)})));
}