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/

Komentáře

Okomentovat

Populární příspěvky z tohoto blogu

Barbora Junová: Podpora začínajícího podnikání zaměřeného na prodej výrobků a poskytování služeb

-
Obrázek
Podpora začínajícího p odnikání zaměřeného na prodej výrobků a poskytování služeb Barbora Junová Úvod Rozhodnout se na začátku Digitální akademie, čeho se bude týkat můj projekt, bylo velmi těžké. Vůbec jsem tehdy totiž netušila, co všechno se za ty necelé dva měsíce zvládnu naučit a jaké budu ovládat nástroje ke zpracování dat. Jelikož jsem nechtěla mít svoji závěrečnou práci pouze na teoretické bázi, jako to bylo u většiny úkolů na vysoké škole, mým nejvýznamnějším kritériem byla praktičnost celé této věci. Po hlubokém zamyšlení konečně přišel on – ten NÁPAD. Ze všech stran jsem na přednáškách slyšela, jak moc je důležité pracovat v jakémkoliv odvětví s daty, ze kterých lze vyčíst téměř cokoliv. Vzpomněla jsem si na kamaráda, který právě rozjížděl svou vlastní firmu a napadlo mě, mu s jeho záměrem pomoct a zároveň tak vyřešit i téma této závěrečné práce. Jak už jsem zmínila, ze...
Další informace

Gabriela Kubová, Lucie Čuprová: Analýza ruských e-shopů pomocí srovnávače cen Yandex

-
Obrázek
Úvodní slovo Do projektu Digitální akademie jsme se každá přihlásily z trošku jiného důvodu, i když základní pohnutka u nás obou byla stejná, naučit se efektivně pracovat s velkými objemy dat. Ani jedna z nás neměla dřívější zkušenosti s oborem IT, ale stejně jako každý jiný člověk, který v dnešní době pracuje v kanceláři, jsme se denně potkávaly s jednotlivými programy především z balíčku Microsoft Office. Na začátku Akademie jsme neměly ani tušení, jaké množství informací získáme a do jakých oblastí zvládneme v opravdu krátkém čase tří měsíců nakouknout. A to je hlavně to, o co v Digitální akademii podle nás jde. Rozšířit si obzory, možná i změnit přístup k řešení zadaných úkolů a hlavně si vyzkoušet v praxi, že IT se naučit jde, jen to chce mít pro danou věc zápal. Při vybírání dvojičky, tématu projektu a mentora jsme obě chtěly využít některé naše dřívější zkušenosti, proto bylo hrozně důležité, že jsme se na začátku Digitální akademie při seznamování všichni navzájem bavily...
Další informace

Kateřina Kolouchová & Lenka Tomešová: Vliv počasí na kriminalitu v New Yorku a Brně

-
Obrázek
Jak vznikla naše spolupráce Daly jsme se do řeči hned na první lekci Digitální akademie, Excelu, kam jsme přišly zvědavé a lehce nervózní z toho, jak budou celé tři měsíce probíhat. Od té doby jsme si většinou sedaly vedle sebe a postupně jsme zjistily, že máme obdobné priority ohledně budoucí práce a spojují nás i zájmy o společenské dění, takže spolupráce na projektu byla jasná volba 😊 Poděkování Ještě než se pustíme do samotného popisování projektu, rády bychom obě poděkovaly našemu mentorovi Vráťovi za počáteční nakopnutí a pozitivní přístup k řešení problémů, Milošovi za pomoc se sdílením databáze přes Azure, Matějovi za rady se statistikou a R, Zdendovi za teoretické konzultace a psychickou podporu, Klárce a Ondráškovi za to, že to doma bez maminky zvládli. Naše díky patří i všem lektorům a lektorkám a celému týmu Czechitas za organizaci jedinečného kurzu, který nám rozšířil obzory ve světě datové analýzy. A taky díky všem holkám, které do toho s námi šly, za tu vzáj...
Další informace