Skrypt Google Ads: GA Performance Checker (1 Day)

Reklamy Google Ads prawie nigdy nie działają w odosobnieniu. Skuteczność sprzedaży sklepu Internetowego to także inne kanały. E-mail marketing, wyniki organiczne (SEO), programy afiliacyjne czy sprzedaż telefoniczna. Wszystko to ma duży wpływ na sukces (lub jego brak).

Zdarza się, że nagły wzrost sprzedaży w jednym kanale może osłabić inny.
Dla przykładu wzmożona aktywność e-mail marketingu oraz programów afiliacyjnych może zmniejszyć skuteczność działań w Google Ads.

Istotnym elementem prowadzenia kont reklamowych jest monitoring aktywności wszystkich kanałów. Najczęściej analizę tego typu można wykonać w Google Analytics.

Poniższy skrypt Google Ads zautomatyzuje dla nas część tego procesu.

Codziennie rano pobierze dane o wynikach sprzedaży (transakcje oraz użytkownicy) za dzień wczorajszy oraz dane z poprzedniego tygodnia. Porówna skuteczność i wyśle je w formie tabeli na wskazany adres e-mail.

Przykładowe zestawienie wygląda następująco:

Legenda:
Kolor zielony: wzrost względem poprzedniego tygodnia
Kolor żółty: wynika taki sam jak w poprzednim tygodniu
Kolor czerwony: spadek względem poprzedniego tygodnia

Konfiguracja:
1. Wprowadź ID wybranego widoku Google Analytics (administracja -> ustawienia widoku -> Identyfikator widoku danych) do zmiennej ANALYTICS_VIEW_ID
2. Wprowadź swój e-mail do zmiennej EMAIL
3. Wprowadź nazwę (dowolną, zostanie dodana w nagłówku tabeli) konta do zmiennej ACCOUNT_NAME
3. W zaawansowanych ustawieniach włącz „Analytics„:
https://developers.google.com/google-ads/scripts/docs/features/advanced-apis

Harmonogram: raz dziennie, w godzinach porannych

---

Po więcej na temat Skryptów zapraszam na grupę FB o automatyzacji:
https://www.facebook.com/groups/skrypty.google.ads

---

/*

Copyright 2020 Krzysztof Bycina, www.LiveAds.pl
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
    http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

*/

// --------------------------------------- Configuration:

var EMAIL = "YOUR EMAIL";
var ACCOUNT_NAME = "YOUR STORE NAME";
var ANALYTICS_VIEW_ID = "YOUR VIEW ID";

// --------------------------------------- End of the configuration

//Prepare the dates:
var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
var now = new Date();
var eightDays = new Date(now.getTime() - 8 * MILLIS_PER_DAY);
var eightDaysAgo = formatDate(eightDays, "yyyy-MM-dd");
var yesterday = new Date(now.getTime() - 1 * MILLIS_PER_DAY);
var yesterdaysDate = formatDate(yesterday, "yyyy-MM-dd");

function main() {
  
    //Prepare the HTML tabel
    var htmlData = prepareHTML();

    //Get the data from Google Analytics for eight days ago
    var eightDaysAgoAnalytics = fetchTheData(eightDaysAgo, eightDaysAgo);

    //Get the data from Google Analytics for yesterday
    var yesterdaysAnalytics = fetchTheData(yesterdaysDate, yesterdaysDate);

    //Write the Google Analytics data to the HTML format
    htmlData = writeTheDataToHtml(yesterdaysAnalytics, eightDaysAgoAnalytics, htmlData);

    //Check if there are differences in the number of channels & write missing data to the HTML format
    if (eightDaysAgoAnalytics.length > yesterdaysAnalytics.length) {
        var metricDifference = findMetricDifference(eightDaysAgoAnalytics, yesterdaysAnalytics);
        htmlData = writeDifferenceToHtml(metricDifference, htmlData);
    }
  
    //Sum and write the metrics to the HTML format
    htmlData = writeSumToHtml(eightDaysAgoAnalytics, yesterdaysAnalytics, htmlData);
  
    //Close the HTML tags:
    htmlData.push('</table>', '</body>', '</html>');

  
        //Send the email:
        sendEmails(htmlData);

    }

    function fetchTheData(startDate, endDate) {
        Logger.log("Google Analytics import was made for START: " + startDate + " END: " + endDate);
        var results = Analytics.Data.Ga.get(
            'ga:' + ANALYTICS_VIEW_ID,
            startDate,
            endDate,
            'ga:transactions,ga:users', {
                'dimensions': 'ga:channelGrouping',
            }
        );
        return results.rows;
    }

    function writeTheDataToHtml(yesterdaysAnalytics, eightDaysAgoAnalytics, htmlData) {
        for (i = 0; i < eightDaysAgoAnalytics.length; i++) {
            for (j = 0; j < yesterdaysAnalytics.length; j++) {
                if (eightDaysAgoAnalytics[i][0].indexOf(yesterdaysAnalytics[j][0]) !== -1) {
                    htmlData.push('<tr>', " <td style='padding: 0px 5px'>" + yesterdaysAnalytics[j][0] + '</td>',
                        "<td style='padding: 0px 5px; background-color:" + findColor(yesterdaysAnalytics[j][1], eightDaysAgoAnalytics[i][1]) + "'>" + yesterdaysAnalytics[j][1] + '</td>',
                        "<td style='padding: 0px 5px; background-color:" + findColor(yesterdaysAnalytics[j][2], eightDaysAgoAnalytics[i][2]) + "'>" + yesterdaysAnalytics[j][2] + '</td>',
                        "<td style='padding: 0px 5px; background-color:" + findColor(eightDaysAgoAnalytics[j][1], yesterdaysAnalytics[i][1]) + "'>" + eightDaysAgoAnalytics[i][1] + '</td>',
                        "<td style='padding: 0px 5px; background-color:" + findColor(eightDaysAgoAnalytics[j][2], yesterdaysAnalytics[i][2]) + "'>" + eightDaysAgoAnalytics[i][2] + '</td>', '</tr>');

                }

            }
        }
        return htmlData;
    }

    function writeDifferenceToHtml(metricDifference, htmlData) {
        htmlData.push('<tr>', "<td style='padding: 0px 5px'>" + metricDifference[0][0] + '</td>',
            "<td style='padding: 0px 5px; background-color:" + findColor(0, metricDifference[0][1]) + "'>" + "0" + '</td>',
            "<td style='padding: 0px 5px; background-color:" + findColor(0, metricDifference[0][2]) + "'>" + "0" + '</td>',
            "<td style='padding: 0px 5px; background-color:" + findColor(metricDifference[0][1], 0) + "'>" + metricDifference[0][1] + '</td>',
            "<td style='padding: 0px 5px; background-color:" + findColor(metricDifference[0][2], 0) + "'>" + metricDifference[0][2] + '</td>', '</tr>');
        return htmlData;
    }

    function writeSumToHtml(eightDaysAgoAnalytics, yesterdaysAnalytics, htmlData) {
        htmlData.push('<tr>', "<td style='padding: 0px 5px; font-size: large; font-weight: bold'>Total:</td>",
                        "<td style='padding: 0px 5px; font-size: large; font-weight: bold; background-color:" + findColor(sumMetrics(yesterdaysAnalytics,1), sumMetrics(eightDaysAgoAnalytics,1)) + "'>" + sumMetrics(yesterdaysAnalytics,1) + '</td>',
                        "<td style='padding: 0px 5px; font-size: large; font-weight: bold; background-color:" + findColor(sumMetrics(yesterdaysAnalytics,2), sumMetrics(eightDaysAgoAnalytics,2)) + "'>" + sumMetrics(yesterdaysAnalytics,2) + '</td>',
                        "<td style='padding: 0px 5px; font-size: large; font-weight: bold; background-color:" + findColor(sumMetrics(eightDaysAgoAnalytics,1), sumMetrics(yesterdaysAnalytics,1)) + "'>" + sumMetrics(eightDaysAgoAnalytics,1) + '</td>',
                        "<td style='padding: 0px 5px; font-size: large; font-weight: bold; background-color:" + findColor(sumMetrics(eightDaysAgoAnalytics,2), sumMetrics(yesterdaysAnalytics,2)) + "'>" + sumMetrics(eightDaysAgoAnalytics,2) + '</td>','</tr>');
        return htmlData;
    }


    function prepareHTML() {
        var theHTML = [];
        theHTML.push(
            "<html>",
            "<body> ",
                "<table width=700 cellpadding=0 border=1 cellspacing=0>",
                    "<tr>",
                        "<td colspan=5 align=right style='padding: 0px 5px; font-size: small'>Developed by <a href='https://www.Li" +
            "veAds.pl?utm_source=ga_performance&utm_medium=email&utm_campaign=daily' target='_blank'>www.LiveAds.pl</a></td>",
                        "</tr>",
                    "<tr bgcolor='#3c78d8'>",
                        "<td colspan=5 style='font: normal 18pt verdana, sans-serif; padding: 3px 10px; color: white; background-color:#3366ff'>" + ACCOUNT_NAME + " Analytics Summary</div>",
                            "</td>",
                        "</tr>",
                    "<tr>",
                        "<td></td>",
                        "<td colspan='2' style='text-align: center; font-size: x-large'>" + formatDate(yesterday, "EEEE") + " (" + formatDate(yesterday,'dd.MM') + "):</td>",
                        "<td colspan='2' style='text-align: center; font-size: x-large'>" + formatDate(eightDays, "EEEE") + " (" + formatDate(eightDays,'dd.MM') + "):</td>",
                        '</tr>',
                    "<tr>",
                        "<td style='padding: 0px 5px; font-size: large; font-weight: bold'>Channel:</td>",
                        "<td style='padding: 0px 5px; font-size: large; font-weight: bold'>Transactions:</td>",
                        "<td style='padding: 0px 5px; font-size: large; font-weight: bold'>Users:</td>",
                        "<td style='padding: 0px 5px; font-size: large; font-weight: bold'>Transactions:</td>",
                        "<td style='padding: 0px 5px; font-size: large; font-weight: bold'>Users:</td>"
        );
        return theHTML;
    }


    function sumMetrics(data, position) {
      var sum = 0;
      data.forEach(function(channel) {
           sum += parseInt(channel[position]);
        });
      return sum;
    }

    function formatDate(date, format) {
        var timeZone = AdsApp.currentAccount().getTimeZone();
        return Utilities.formatDate(date, timeZone, format);
    }

    function findColor(firstMetric, secondMetric) {
        if (parseInt(firstMetric) > parseInt(secondMetric)) {
            return "#99ff99";
        } else if (parseInt(firstMetric) === parseInt(secondMetric)) {
            return "#ffff99";
        } else {
            return "#ff8080";

        }
    }

    function findMetricDifference(eightDaysAgoAnalytics, yesterdaysAnalytics) {
        var metricDifference = [];
        outerloop:
            for (var i = 0; i < eightDaysAgoAnalytics.length; ++i) {
                for (var j = 0; j < yesterdaysAnalytics.length; ++j) {
                    if (eightDaysAgoAnalytics[i][0] == yesterdaysAnalytics[j][0]) continue outerloop;
                }
                metricDifference.push(eightDaysAgoAnalytics[i]);
            }
        return metricDifference;
    }

    function sendEmails(htmlData) {
        MailApp.sendEmail(EMAIL, ACCOUNT_NAME + ' Google Analytics Daily Summary', '', {
            htmlBody: htmlData.join('\n')
        });
    }