Nikol Kupková: Automatizace vyhodnocování AdWords kampaní

Pro svůj projekt v rámci Digitální akademie jsem si vybrala zautomatizování reportu pro PPC specialisty. Zejména tedy ty, kteří jsou na volné noze, takže nemají prostředky na nákladné nástroje, a zároveň se tolik neorientují v programování, aby si vytvořili vlastní skripty.

Jakou jsem zvolila cestu

Vycházela jsem ze dvou zdrojů dat: Google AdWords a Google Analytics a sepsala si hlavní KPI, která každý specialista pravidelně sleduje. Z obou zmíněných nástrojů jsem si stáhla CSV soubory s daty za jeden měsíc a pomocí PG Adminu a PostgreSQL databáze jsem je vyčistila a požadované metriky dala do jedné tabulky.

Základní sledovaná KPI

Adwords

• prokliky
• zobrazení
• CTR
• průměrná CPC
• cena
• průměrná pozice

Analytics

• návštěvy
• konverze
• konverzní poměr
• tržba

Vypočtené

• průměrná cena konverze (cena / konverze)
• PNO (cena / tržba)
• průměrná cena objednávky (tržba / konverze)

Na začátek trocha ruční práce

Ze všeho nejdřív si nainstalujte PostgreSQL včetně PG Adminu a stáhněte si data z AdWords a Analytics (pokud budete používat jiné metriky, musíte si je upravit ve skriptu).
AdWords

Analytics

Bohužel kvůli formátu stažených CSV (budu ráda, když mi někdo vysvětlíte, proč Google dává sloupec pod sloupec) to nepůjde bez předpřípravy.
Soubor z AdWords přeuložte (v poznámkovém bloku, příp. TextEditu) tak, aby jeho kódování bylo UTF-8. Soubor z Analytics otevřete v textovém editoru a smažte hlavičku obsahující informace o exportu (prvních pět řádků) a také tabulku návštěv rozpadlou na dny.
Upravené soubory dejte do jedné složky, ke které bude mít přístup uživatel Postgre (nějaké sdílené).

Zbytek nechte magii

Do PG Adminu zkopírujte obsah (do horního okna v Query Tool – viz obrázek níže) SQL skriptu a spusťte. Pokud by vaše metriky neseděly s těmi, co jsou na obrázcích, musíte je ve skriptu přepsat podle těch vašich.

-- ve zvolene DB si spustime tento skript
CREATE SCHEMA projekt_ppc;

-- vytvoreni tabulek
CREATE TABLE projekt_ppc.report
(
   prokliky integer,
   zobrazeni integer,
   ctr float,
   prum_cpc float,
   cena float,
   prum_pozice float,
   navstevy integer,
   konverze_ga integer,
   prum_cena_konverze float,
   konv_pomer float,
   trzba float,
   pno float,
   prum_hodnota_obj float
);

CREATE TABLE projekt_ppc.adw_csv
(
   stav_kampane character varying(100) COLLATE pg_catalog."default",
   kampan character varying(100) COLLATE pg_catalog."default",
   rozpocet character varying(100) COLLATE pg_catalog."default",
   stav character varying(100) COLLATE pg_catalog."default",
   stitky character varying(100) COLLATE pg_catalog."default",
   prokliky character varying(100) COLLATE pg_catalog."default",
   zobrazeni character varying(100) COLLATE pg_catalog."default",
   ctr character varying(100) COLLATE pg_catalog."default",
   prum_cpc character varying(100) COLLATE pg_catalog."default",
   cena character varying(100) COLLATE pg_catalog."default",
   konverze character varying(100) COLLATE pg_catalog."default",
   prum_pozice character varying(100) COLLATE pg_catalog."default",
   podil_cena_konverze character varying(100) COLLATE pg_catalog."default",
   konv_pomer character varying(100) COLLATE pg_catalog."default",
   podil_ztrac_zobr_hodnoceni character varying(100) COLLATE pg_catalog."default",
   podil_ztrac_zobr_rozpocet character varying(100) COLLATE pg_catalog."default"
);

CREATE TABLE projekt_ppc.ga_csv
(
   zdroj_medium character varying(100) COLLATE pg_catalog."default",
   navstevy character varying(100) COLLATE pg_catalog."default",
   nove_navstevy character varying(100) COLLATE pg_catalog."default",
   novi_uzivatele character varying(100) COLLATE pg_catalog."default",
   bounce_rate character varying(100) COLLATE pg_catalog."default",
   pocet_stranek_za_navstevu character varying(100) COLLATE pg_catalog."default",
   prum_doba_navstevy character varying(100) COLLATE pg_catalog."default",
   koverzni_pomer_cile character varying(100) COLLATE pg_catalog."default",
   splneni_cile character varying(100) COLLATE pg_catalog."default",
   hodnota_cile character varying(100) COLLATE pg_catalog."default"
);

-- vytvoreni funkci
CREATE OR REPLACE FUNCTION projekt_ppc.process_csv_adwords(
    vstup character varying)
   RETURNS void
   LANGUAGE 'plpgsql'
   COST 100.0
   VOLATILE
AS $function$
    BEGIN
       delete from "projekt_ppc".adw_csv;
       execute 'COPY "projekt_ppc".adw_csv FROM '''||vstup||''' (HEADER true, format ''csv'', ENCODING ''UTF-8'', delimiter '' '');';
      
       UPDATE "projekt_ppc".adw_csv
        SET rozpocet = replace(rozpocet, ',', '.'), ctr = replace(ctr, ',', '.'), prum_cpc = replace(prum_cpc, ',', '.'),
       cena = replace(cena, ',', '.'), konverze = replace(konverze, ',', '.'), prum_pozice = replace(prum_pozice, ',', '.'),
       podil_cena_konverze = replace(podil_cena_konverze, ',', '.'), konv_pomer = replace(konv_pomer, ',', '.'),
       podil_ztrac_zobr_hodnoceni = replace(podil_ztrac_zobr_hodnoceni, ',', '.'),
       podil_ztrac_zobr_rozpocet = replace(podil_ztrac_zobr_rozpocet, ',', '.');

        UPDATE "projekt_ppc".adw_csv
        SET ctr = replace(ctr, ' %', ''), konv_pomer = replace(konv_pomer, ' %', ''),
       podil_ztrac_zobr_hodnoceni = replace(podil_ztrac_zobr_hodnoceni, ' %', ''),
       podil_ztrac_zobr_rozpocet = replace(podil_ztrac_zobr_rozpocet, ' %', '');
      
        DELETE
       FROM "projekt_ppc".adw_csv
        WHERE stav_kampane = 'Stav kampaně';      
   END; 
$function$;

CREATE OR REPLACE FUNCTION projekt_ppc.process_csv_analytics(
    vstup character varying)
   RETURNS void
   LANGUAGE 'plpgsql'
   COST 100.0
   VOLATILE
AS $function$

    BEGIN
       delete from "projekt_ppc".ga_csv;
       execute 'COPY "projekt_ppc".ga_csv FROM '''||vstup||''' (HEADER true, format ''csv'', ENCODING ''UTF-8'', delimiter '','');';
      
       UPDATE "projekt_ppc".ga_csv
        SET nove_navstevy = replace(nove_navstevy, ',', '.'), bounce_rate = replace(bounce_rate, ',', '.'),
       pocet_stranek_za_navstevu = replace(pocet_stranek_za_navstevu, ',', '.'),
       koverzni_pomer_cile = replace(koverzni_pomer_cile, ',', '.'),
       hodnota_cile = replace(hodnota_cile, ',', '.');

        UPDATE "projekt_ppc".ga_csv
        SET nove_navstevy = regexp_replace(nove_navstevy, '\D%$','','g'),
       bounce_rate = regexp_replace(bounce_rate, '\D%$','','g'),
       koverzni_pomer_cile = regexp_replace(koverzni_pomer_cile, '\D%$','','g'),
       hodnota_cile = regexp_replace(hodnota_cile, '\DKč$','','g');
      
       DELETE
       FROM "projekt_ppc".ga_csv
        WHERE zdroj_medium is null;
      
   END;
 $function$;

CREATE OR REPLACE FUNCTION projekt_ppc.export_report(vystup varchar(250))
RETURNS void AS $$
    BEGIN
   DELETE FROM projekt_ppc.report;
  
   INSERT into projekt_ppc.report  (
   SELECT CASE prokliky WHEN ' --' THEN NULL ELSE CAST (prokliky AS INT) END,
       CASE zobrazeni WHEN ' --' THEN NULL ELSE CAST (zobrazeni AS INT) END,
       CASE ctr WHEN ' --' THEN NULL ELSE CAST (ctr AS FLOAT) END,
       CASE prum_cpc WHEN ' --' THEN NULL ELSE CAST (prum_cpc AS FLOAT) END,
       CASE cena WHEN ' --' THEN NULL ELSE CAST (cena AS FLOAT) END,
       CASE prum_pozice WHEN ' --' THEN NULL ELSE CAST (prum_pozice AS FLOAT) END,
       CASE navstevy WHEN ' --' THEN NULL ELSE CAST (navstevy AS INT) END,
       CASE splneni_cile WHEN ' --' THEN NULL ELSE CAST (splneni_cile AS INT) END,
       CASE splneni_cile WHEN '0.00' THEN NULL ELSE CAST(cena AS FLOAT)/CAST(splneni_cile AS INT) END AS prum_cena_konverze,
       CASE konv_pomer WHEN ' --' THEN NULL ELSE CAST (koverzni_pomer_cile AS FLOAT) END,
       CASE hodnota_cile WHEN ' --' THEN NULL ELSE CAST (hodnota_cile AS FLOAT) END,
       CASE hodnota_cile WHEN '0.00' THEN NULL ELSE CAST(cena AS FLOAT)/CAST(hodnota_cile AS FLOAT) END AS pno,
       CASE splneni_cile WHEN '0.00' THEN NULL ELSE CAST(hodnota_cile AS FLOAT)/CAST(splneni_cile AS INT) END  AS prum_cena_objx
       FROM
       projekt_ppc.ga_csv,
       projekt_ppc.adw_csv
       WHERE stav_kampane = 'Celkem – všechny aktivní kampaně'
       );

       execute 'COPY projekt_ppc.report TO '''||vystup||''' (HEADER true, format ''csv'', ENCODING ''UTF-8'', delimiter '','');';
   END;
 $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION projekt_ppc.create_report(zdroj_ga varchar(250), zdroj_adw varchar(250), vystup varchar(250))
RETURNS void AS $$
    BEGIN
       perform projekt_ppc.process_csv_adwords(zdroj_adw);
       perform projekt_ppc.process_csv_analytics(zdroj_ga);
       perform projekt_ppc.export_report(vystup);
   END;
 $$ LANGUAGE plpgsql;

Ve druhém skriptu (níže) změňte cesty k souborům a také spusťte. Vytvoří se vám v dané složce CSV soubor s výslednou tabulkou.

-- volani funkce pro vytvoreni reportu
SELECT projekt_ppc.create_report('/Users/Shared/analytics.csv', '/Users/Shared/adwords.csv', '/Users/Shared/report.csv');

Výsledná tabulka

Pro další použití stačí přeuložit ve vybrané složce vstupní CSV soubory, upravit je a v PG Adminu spustit druhý skript (ten kratší).
Během přípravy projektu jsem došla k závěru, že úplně nejjednodušší a zároveň nejefektivnější by bylo vytvořit si JS skripty, které Google sepsal na míru pro AdWords a Spreadsheets (pro Analytics je tam dokonce zabudovaný doplňek). Na tom bych si ale neprocvičila práci s SQL, což bylo mým cílem při vymýšlení tématu projektu.
Další možností by bylo se pomocí Pythonu (nebo jiného jazyka, který to umožňuje) napojit na API obou nástrojů, pravidelně data stahovat a automaticky exportovat hotovou tabulku.

Co dál

Data z každého měsíce je dobré sbírat do jednoho souboru v Excelu nebo Spreadsheets. Budete mít hezký přehled o tom, jak se vašim kampaním daří.
Vedle toho si zkuste tabulky (případně rozšířené o další metriky) jednotlivých časových období nahrát do vizualizačního nástroje a sledovat dlouhodobý vývoj výkonu kampaní. Z těch bezplatných doporučuji PowerBI.
Zkoušela jsem i Google Data Studio, ale hned na začátku mě odradilo, kolik stěžejních funkcí tam chybí. Například tam nenajdete celkové hodnoty, nejde spojovat více datových zdrojů a automatizace reportů jde dělat pouze přes externí nástroje. Je však stále ve vývoji, takže časem to může být všechno jinak.

Na závěr

Díky této práci jsem zjistila, že SQL má své limity v čištění dat, na které se vyplatí umět programovat např. v Pythonu, aby se člověk vyhnul “ruční práci”. Pokud tedy nemáte přístup k nástrojům typu Alteryx nebo Keboola.
Možností, jak takový reporting zautomatizovat je opravdu hodně. Ráda bych projekt časem rozšířila o řešení pomocí Pythonu i JS skriptů.
Nakonec chci poděkovat mé mentorce, Monice Kratochvílové z Kiwi, která mě nasměrovala a ušetřila mi spoustu práce hledáním odpovědí na internetu, a příteli Davidovi, který mi pomáhal, když jsem se zasekla. :-)

Zdroje

https://www.postgresql.org/docs/9.6/static/index.html
https://blog.adstage.io/2017/01/17/effective-ppc-reports/
http://www.sqlines.com/postgresql/stored_procedures_functions
Možnosti pro Sklik:
https://petrbures.wordpress.com/2015/12/09/sklik-api/
http://www.zaklik.cz/vyhodnocovani/rozdily-v-mereni-sklik-adwords-analytics/
Skripty pro Spreadsheets:
https://developers.google.com/adwords/scripts/docs/solutions/account-summary
http://tyinternety.cz/prirucka-marketera/prirucka-marketera-moznosti-adwords-skriptu/