MCC Skript na kontrolu rozpočtů pro Adwords a Sklik

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)})));
}

 

  1. 26. 1. 2017 - Odpovědět

    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 - Odpovědět

      @Martin Kudrna

      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.

  2. 26. 1. 2017 - Odpovědět

    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 - Odpovědět

      @Tomáš

      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.

  3. 26. 1. 2017 - Odpovědět

    Č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 - Odpovědět

      @Tomáš Hanáček

      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?

  4. 30. 1. 2017 - Odpovědět

    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 - Odpovědět

      @Petr

      Ahoj,
      když pustíš script v režimu náhled, co ti hlásí „Protokol“?

      • 31. 1. 2017 - Odpovědět

        @Karel Rujzl

        Ahoj,
        už vše funguje. Chyba byla u mě v configu. 🙂
        Díky.

  5. 31. 1. 2017 - Odpovědět

    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ží!

  6. 2. 2. 2017 - Odpovědět

    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 - Odpovědět

      @Tomáš Bzirský

      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 - Odpovědět

        @Karel Rujzl

        Ahoj, nemám, protože by mi to pak netahalo nové nastavení:)

        • 3. 2. 2017 - Odpovědět

          @Tomáš Bzirský

          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 - Odpovědět

            @Standa Jílek

            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 - Odpovědět

              @Tomáš Bzirský

              OK. Doporučuji update na aktuální verzi, rychlejší, a více funkcí 🙂

  7. 20. 2. 2017 - Odpovědět

    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

  8. 13. 3. 2017 - Odpovědět

    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.

  9. 22. 8. 2017 - Odpovědět

    Adwords v pohodě, ale nějak mi to nehází data z Skliku. Čím by to mohlo být? Díky! P.

Comment
Name
Email