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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 |
//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)}))); } |
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.
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.
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ěď
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.
Č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.
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?
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
Ahoj,
když pustíš script v režimu náhled, co ti hlásí „Protokol“?
Ahoj,
už vše funguje. Chyba byla u mě v configu. :)
Díky.
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ží!
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
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.
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.
Ahoj, nemám, protože by mi to pak netahalo nové nastavení:)
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.
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í :(
OK. Doporučuji update na aktuální verzi, rychlejší, a více funkcí :)
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
Zřejmě to bude chtít updatnout soubor configu, zkus tento: https://docs.google.com/spreadsheets/d/1Whi3HgtFWtA4TTb_LnrHz8uN8l841aHRJ8drFkmk_qI/edit#gid=57106071
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.
Zřejmě to bude chtít updatnout soubor configu, zkus tento: https://docs.google.com/spreadsheets/d/1Whi3HgtFWtA4TTb_LnrHz8uN8l841aHRJ8drFkmk_qI/edit#gid=57106071
Adwords v pohodě, ale nějak mi to nehází data z Skliku. Čím by to mohlo být? Díky! P.
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.