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)}))); }
26. 1. 2017 -
Ahoj, díky za script. Šlo by, aby ten sheet aktualizoval někam do Google Sheetu místo emailu? Abychom si mohli další data navzorcovat sami 🙂
M.
27. 1. 2017 -
Martin Kudrna: Skript jsme pustili ven pro inspiraci, co lze udělat a jak funguje hlavně napojení na Sklik API. Další rozšíření možná časem. Ale kdo trošku App Scriptu rozumí, tak si ten export dopíše, není to tak těžké. Zkuste.
26. 1. 2017 -
Paráda, jen mám malý dotaz: jak je to s ochranou dat? Když např. zadám přístup do Sklikového MCC, kdo jej uvidí?
Díky za odpověď
27. 1. 2017 -
Tomáš: Data jsou kompletně u Google a běží na HTTPS. Do config souboru je přístup jen pro ty co mají práva. Opět vše běží na HTTPS. Stejně tak veškerá komunikace scriptu.
26. 1. 2017 -
Čau Kájo,
podle mě by se hodil ještě sloupec „poměrové procentuální plnění budgetu“, abys hned viděl, jak jsi na tom třeba v půli měsíce – jestli přetahuješ nebo naopak budget nečerpáš. Co myslíš? Nebo aspoň já to tak využívám.
27. 1. 2017 -
Tomáš Hanáček: Máme tam absolutní částku, ta se mi jeví jako OK. Myslíš, že ty procenta ti nějak pomůžou víc?
30. 1. 2017 -
Ahoj,
děkuji za veřejně dostupný skript. V emailu mi chodí pouze prázdná tabulka. Skript mám v MMC účtu. Můžu se zeptat, kde dělám chybu?
Díky.
Petr
30. 1. 2017 -
Ahoj,
když pustíš script v režimu náhled, co ti hlásí „Protokol“?
31. 1. 2017 -
Ahoj,
už vše funguje. Chyba byla u mě v configu. 🙂
Díky.
31. 1. 2017 -
Nemohl by i na karte SKLIK byl sloupec pro nazev klienta? Rada klientu ma login na soukromy email prehistorickeho zneni a z nej nepozname o jakeho klienta jde (v Skliku si ucty pojmenovavame). Díííík, jste boží!
31. 1. 2017 -
Ahoj Roberte,
pokud chceš mít jen možnost poznámky v configu, stačí přidat úplně napravo sloupec.
Případně jsem už přidal do vzorového spreadsheetu.
(Je to pouze poznámka v configu, nepropisuje se do příchozího mailu 🙂
https://docs.google.com/spreadsheets/d/1J5TG4C-51HWxPsjFivemGHFjtywp4qiqR81mNX_SGfU/edit#gid=57106071
2. 2. 2017 -
Ahoj, nevím jestli je to bug u mě nebo se to děje všeobecně. Když vytvoříš kopii již existujícího configu, tak se z nového configu (ikdyž jsou data vymazaná) posílají i Sklik data původního configu. Adwords je v pohodě. Když to vytvořím z vašeho default configu, tak 2 configy vedle sebe fungují v pohodě.
A je to fakt pecka! Díky.
2. 2. 2017 -
Ahoj Tome, a nemáš ve scriptu spíš odkaz na ten starý config. Protože script si sahá jen tam kam ho nasměruješ. Jinak mě nenapadá, jak by to mohlo nastat. Možná ještě napadne něco Standu.
3. 2. 2017 -
Ahoj, nemám, protože by mi to pak netahalo nové nastavení:)
3. 2. 2017 -
Ahoj Tome,
můžu se zeptat jaké hodnoty ti to vracelo?
V congigu jsou nastavené samé jedničky, tak či tak by to nic nemělo vrátit? 🙂
S.
6. 2. 2017 -
Do e-maily mi to poslalo informace druhého účtu:)
Zkoušel jsem to nasimulovat znovu a už se mi ta chyba neukázala, asi problém mezi klávesnicí a židlí 🙁
6. 2. 2017 -
OK. Doporučuji update na aktuální verzi, rychlejší, a více funkcí 🙂
20. 2. 2017 -
Ahoj, kam prosimtě vložím mailové adresy, na které se mají výsledky posílat? Dopsal jsem je na řádek 12, píše mi to ale stále chybovou hlášku
TypeError: Cannot call method „getRange“ of null. (line 13)
Nebo se má po povolení opravnění změnit mail@mail.cz na mail, který chci a řádek 12 nechat, jak je. Díky, Robert
13. 3. 2017 -
Zřejmě to bude chtít updatnout soubor configu, zkus tento: https://docs.google.com/spreadsheets/d/1Whi3HgtFWtA4TTb_LnrHz8uN8l841aHRJ8drFkmk_qI/edit#gid=57106071
13. 3. 2017 -
Ahoj, mám stejný problém jako Robert Macků, řádek číslo 13.
tj.Var mail_settings = mail_sheet.getRange(„B1:B2“).getValues();
mi hlásí tuto chybovou hlášku.
TypeError: Cannot call method „getRange“ of null. (line 13)
Mockrát děkuji za pomoc.
13. 3. 2017 -
Zřejmě to bude chtít updatnout soubor configu, zkus tento: https://docs.google.com/spreadsheets/d/1Whi3HgtFWtA4TTb_LnrHz8uN8l841aHRJ8drFkmk_qI/edit#gid=57106071
22. 8. 2017 -
Adwords v pohodě, ale nějak mi to nehází data z Skliku. Čím by to mohlo být? Díky! P.
23. 8. 2017 -
Ahoj, Pavle, nasazuješ poslední verzi Standova skriptu odtud? http://www.standajilek.cz/skripty/ke-stazeni/mcc-skript-kontrola-rozpoctu-adwords-sklik-verze-2/ To by mělo fungovat.