Datová analytička hozená do vody

Už je to téměř rok od momentu, kdy jsem publikovala svůj závěrečný projekt v rámci Digitální akademie s Czechitas a je to víc než půl roku, co jsem si na LinkedIn přidala položku Data analyst. Navíc dnes, kdy moje kamarádka dostala odpověď, že byla vybrána pro účast na již třetím pražském běhu Digitální akademie, mám potřebu bilancovat. Digitální akademie rozhodně byla jen začátek.

Původně jsem chtěla psát klasický blog s pravidelnými příspěvky o tom, jak postupuji ve své kariéře, a co všechno už umím. Jeden článek jsem chtěla napsat o tom, jak se stát živnostníkem, protože mi trvalo pěkně dlouho, než jsem ten proces na všech možných úřadech pochopila a úspěšně absolvovala. Teď už si to taky nepamatuju, a stejně jako moji známí říkali mě, říkám i já potenciálním živnostníkům, že to zvládnou, není to těžký (haha). Místo toho mě teď čeká další zkouška a to je podání daňového přiznání, o kterém také pravděpodobně nenapíšu.

Zkrátka po dlouhé době chci napsat krátký článek o tom, jaké to je s daty, když vás prostě hodí do vody abyste se naučili plavat.

Data se zpravidla ukládají v databázích, to je něco jako regály se šanony, ty šanony jsou tabulky a v tabulkách jsou řádky a sloupce s daty (papíry v šanonech). Takovou databázi může člověk mít úplně malinkou u sebe na počítači, nebo může být obří uložená na druhém konci světa. Každopádně taková databáze potřebuje server na kterém běží (něco jako místnost na ty regály). Takže jeden z prvních a zásadních poznatků, které jsem učinila, když jsem měla začít sama používat nějaká data bylo, že si musím nainstalovat server! Taková věc pro člověka začínajícího od nuly v oblasti dat vůbec není samozřejmá.

Tady hraje roli ještě celá řada věcí, například serverů jsou mraky druhů, databází jsou mraky druhů, lze používat různé jazyky pro komunikaci s nimi, atd. Na to, abych o tom psala ale fakt nemám znalosti, takže to nechám pro vaši vlastní rešerši.

Když ale přijdete k již existujícímu serveru s funkční databází plnou dat, chcete se podívat, co tam je za data a třeba je analyzovat. K tomu slouží SQL (opět existuje víc druhů SQL podle toho, jakého typu je daná databáze). Zde už jsme doma, SQL jsme se zevrubně naučili na Digitální akademii! Ovšem když máte smůlu, data jsou uložena ve více různých databázích, které jsou navíc různého typu (například jedna je MySQL a druhá je Postgre), tak už není tak jednoduché se k datům dostat. Navíc když máte tu čest pracovat s daty, která jdou do milionů řádků, už taky nemusí utáhnout váš počítač.

Zde nastupuje další super znalost, kterou jsem se trochu naučila na Digitální akademii – programování.

Protože jediný způsob, jak spojit data ze dvou různých databází a následně je stáhnout, je pokud vím napsat skript (v mém případě v Pythonu), který to provede. Takže první věc, kterou uděláte je, že jdete na stackoverflow.com a hledáte tam, jestli někdo již takový problém řešil. Toto je velmi důležitý hint (tak se říká v IT dobré radě), protože umět správně napsat dotaz do Googlu a najít dané téma na stackoverflow.com je základ práce každého člověka, co se pokouší něco vytvořit v IT.

Následně se vynoří další hora překážek, o kterých třeba napíšu zase za rok, jako třeba kódování (proč není vždycky Unicode?), konce řádků (\n nebo \r\n???), špatně zvolená knihovna (proč se proboha jmenuje Psycopg2?), apod.

A když se to vše nakonec nějak podaří vyřešit, narazíte na limity svého počítače musíte se naučit pouštět onen skript na serveru.

Paráda, nakonec máte ta data u sebe. Jsou ve formátu csv, ale když je chcete otevřít v Excelu, tak se to nepodaří, protože mají dva tisíce sloupců a statisíce řádků a to je na excel trochu moc. Na tohle je opět skvělý Python a jeho knihovna Pandas! Ta je totiž dělaná na velká data a velmi dobře se s ní pracuje i když nejste opravdický programátor/opravdická programátorka. Opět nastupuje stackoverflow.com v kombinaci s dokumentací Pandas a jestli chcete grafy, tak ty zas umí jiné knihovny jako třeba Matplotlib.

A tak se člověk dostane až k analyzování dat, které je teprve ta správná zábava.

Tak ten článek je delší, než jsem měla v plánu. A to jsem spoustu zajímavých problémů k řešení a poznatků ani nezmínila. Objevila jsem svět, kde se do smrti mám co učit a objevovat.

Datová analytička hozená do vody

Závěrečný projekt Digitální akademie s Czechitas (za podpory Google.org)

Analýza obsahu reklamních kampaní na facebooku se zaměřením na jednotlivé skupiny uživatelů

Mentor: Jan Černý, Databreakers s.r.o

V rámci svého závěrečného projektu Digitální akademie jsem se chtěla zaměřit na data ze sociálních sítí, protože jde o v dnešní době klíčové médium, které ovlivňuje myšlení i konání obrovského množství lidí. Díky stáži a menotringu ve firmě Databreakers s.r.o jsem měla možnost dozvědět se hodně o tom, jak reklama na Facebooku funguje a také získat data z reklamy na Facebooku pro tuto analýzu.

Na základě těchto dat bylo mým cílem zjistit, na jaké skupiny uživatelů Facebooku reklama úspěšně působí. To jsem analyzovala na tzv. breakdownech – skupinách uživatelů dle pohlaví, věku, regionu či použitého zařízení.

Výsledek analýzy může být využit jako nástroj pro klienty, kteří budou moci díky interaktivním reportům vidět, jak jejich kampaň působí na které skupiny uživatelů. Protože Facebook Business Manager nenabízí v oblasti cílení mnoho informací v nepříliš uživatelsky přátelském prostředí, mohou data z Facebook Ads Insights API přinést lepší využití.

Pro provádění analýzy mi bylo umožněno využít prostor na serveru Databreakers pro optimalizaci procesů. Následně jsem data zpracovávala pomocí MySQL a k vizualizaci jsem využila PowerBI.

Postup

V první řadě bylo třeba se zorientovat ve věcném obsahu analýzy. K tomu mi pomohla vstřícná spolupráce mentora Honzy Černého a dalších kolegů z Databreakers s.r.o, kteří mi poskytli náhled do toho, jak kampaně na Facebooku fungují, jaká je jejich struktura, jaké metriky pro vyhodnocování jsou klíčové, apod.

Postup realizace projektu naznačuje schéma níže. Jednotlivé kroky jsou poslány níže v textu. Nejdříve jsem analýzu provedla na menším vzorku dat. To bylo praktické na přípravu a kontrolu jednotlivých dotazů. Bohužel ale na takovém vzorku nebylo možné provést obecný pohled na data podle jednotlivých skupin uživatelů. Proto jsem následně celý proces provedla na datech čítajících přes deset milionů řádků. Tyto operace jsem již nemohla provádět na svém počítači, proto jsem využila serveru Databreakers, kam mi bylo umožněno se připojit.

Data jsem získala díky Databreakers s.r.o. Obsahují záznamy za reklamní kampaně zákazníků firmy. Data tvoří tabulky ve struktuře jak je vidět na diagramu níže. V této analýze jsem využila tři z nich, ze kterých je vytvořen datový model. Další tabulky můžou sloužit pro podrobnější analýzu podoby postů, konkrétní zaměření na zákazníky a sady reklam. Vzhledem k omezeném času pro analýzu to jsou ale podněty do budoucna. Tabulky jsem dostala samostatně, ale měla jsem k dispozici informace o primárních a cizích klíčích a stručný popis obsahu sloupců.

Další tabulky pro možné rozšíření analýzy:

  • Posts – detailní informace o podobě postů,
  • Ad_sets – informace o sadách reklam a jejich zacílení,
  • Scheduler_items – kódy a názvy kampaní
  • Accounts – identifikace zákazníků

Spojením tabulek pomocí MySQL jsem vytvořila datový model (viz níže):

    CREATE TABLE tab_promotervse AS
SELECT pta.kampan,
       pta.post_id,
       m.fb_api_metric,
       m.breakdown_type,
       m.breakdown,
       sum(i.value) AS sum_value
FROM insights i
LEFT JOIN metrics m ON m.id = i.metric_id
LEFT JOIN posts_to_ads pta ON pta.reklama_id_pta = i.reklama_id_i
WHERE m.fb_api_metric IN ('clicks',
                          'impressions')
  AND breakdown_type <> ''
  AND breakdown <> 'Unknown'
  AND post_id <> 0
  AND kampan <> 0
GROUP BY pta.kampan,
         pta.post_id,
         m.id,
         m.breakdown_type,
         m.breakdown;

Vzhledem velké náročnosti dotazování na tak velká data, jsem pro snazší práci s jednotlivými dotazy vytvářela nové tabulky. Bylo by možné vše tvořit pomocí tzv. pohledů nebo vše seskupit do jednoho dotazu, ale při takto objemných datech bylo třeba šetřit paměť a urychlit jednotlivé kroky. Významnou roli při urychlení provádění dotazů hrají také indexy a primární a cizí klíče. Prvním dotazem jsem vytvořila první tabulku tab_promotervse. Přímo v tomto dotazu jsem již rovnou provedla i omezení na metriky impresí (shlédnutí reklamy) a kliků, odstranění prázdných řádků a řádků, kde je breakdown uveden jako “Unknown”. Imprese a kliky jsem vybrala, protože jde o přehledný způsob analýzy online reklamy, je možné tak dopočítat CTR (click through rate) poměrem mezi kliky a impresemi – tzv. proklikovost.

V dalším kroku bylo třeba vytvořit nové breakdowny s kategoriemi uživatelů dle věku a pohlaví zvlášť, protože přímo z API se data stáhla v členění do skupin podle věku i pohlaví společně (například “18-24,female”). Za tímto účelem bylo třeba vytvořit další tabulku tab_promoterctr, v rámci které se do předchozí tabulky vkládají řádky s hodnotami za jednotlivé kategorie věku a obě pohlaví zvlášť.

    CREATE TABLE tab_promoterctr AS
SELECT *
FROM tab_promotervse
UNION
SELECT kampan,post_id,fb_api_metric,
       'gender' AS breakdown_type,
       'female' AS breakdown,
       sum(sum_value)
FROM tab_promotervse
WHERE breakdown LIKE '%female%'
GROUP BY kampan,
         post_id,
         fb_api_metric
UNION
SELECT kampan,post_id,fb_api_metric,
       'gender' AS breakdown_type,
       'male' AS breakdown,
       sum(sum_value)
FROM tab_promotervse
WHERE breakdown LIKE '%,male%'
GROUP BY kampan,
         post_id,
         fb_api_metric
UNION
SELECT kampan,post_id,fb_api_metric,
       'gender' AS breakdown_type,
       'unknown' AS breakdown,
       sum(sum_value)
FROM tab_promotervse
WHERE breakdown LIKE '%,unknown%'
  AND breakdown_type = 'age,gender'
GROUP BY kampan,
         post_id,
         fb_api_metric
UNION
SELECT kampan,post_id,fb_api_metric,
       'age' AS breakdown_type,
       '13-17' AS breakdown,
       sum(sum_value)
FROM tab_promotervse
WHERE breakdown LIKE '%13-17%'
GROUP BY kampan,
         post_id,
         fb_api_metric
UNION
SELECT kampan,post_id,fb_api_metric,
       'age' AS breakdown_type,
       '18-24' AS breakdown,
       sum(sum_value)
FROM tab_promotervse
WHERE breakdown LIKE '%18-24%'
GROUP BY kampan,
         post_id,
         fb_api_metric
UNION
SELECT kampan,post_id,fb_api_metric,
       'age' AS breakdown_type,
       '25-34' AS breakdown,
       sum(sum_value)
FROM tab_promotervse
WHERE breakdown LIKE '%25-34%'
GROUP BY kampan,
         post_id,
         fb_api_metric
UNION
SELECT kampan,post_id,fb_api_metric,
       'age' AS breakdown_type,
       '35-44' AS breakdown,
       sum(sum_value)
FROM tab_promotervse
WHERE breakdown LIKE '%35-44%'
GROUP BY kampan,
         post_id,
         fb_api_metric
UNION
SELECT kampan,post_id,fb_api_metric,
       'age' AS breakdown_type,
       '45-54' AS breakdown,
       sum(sum_value)
FROM tab_promotervse
WHERE breakdown LIKE '%45-54%'
GROUP BY kampan,
         post_id,
         fb_api_metric
UNION
SELECT kampan,post_id,fb_api_metric,
       'age' AS breakdown_type,
       '55-64' AS breakdown,
       sum(sum_value)
FROM tab_promotervse
WHERE breakdown LIKE '%55-64%'
GROUP BY kampan,
         post_id,
         fb_api_metric
UNION
SELECT kampan,post_id,fb_api_metric,
       'age' AS breakdown_type,
       '65+' AS breakdown,
       sum(sum_value)
FROM tab_promotervse
WHERE breakdown LIKE '%65+%'
GROUP BY kampan,
         post_id,
         fb_api_metric ;

Cílem analýzy bylo zjistit obecné trendy v datech v pohledu na jednotlivé skupiny uživatelů a zároveň možnost využití pro jednotlivé zákazníky. Proto, aby bylo možné s daty dále pracovat a vizualizovat je, je třeba vytvořit další tabulky. Analýza je provedena ve třech pohledech na data v různé podrobnosti – z pohledu pouze na skupiny uživatelů, na jednotlivé kampaně a jednotlivé posty v rámci kampaní.

Tvořené tabulky mají ke každému řádku přiřazeny sloupce s impresemi, kliky a CTR. Přičemž CTR je třeba dopočítat podílem kliků vůči impresím. Vzhledem k omezené možnosti agregace v případě podrobnějších dat u poměrové metriky CTR jsem u tabulek v podrobnějším pohledu na kampaně a posty zvolila výpočet CTR jako metriku v PowerBI, kde je výpočet dynamický.

Pro obecnější analýzu rozptylů jednotlivých skupin uživatelů jsou data agregována podle breakdownů. V případě tohoto dotazu jsem vytvořila tabulku promoter_breakdownCI se skupinami uživatelů a hodnotami impresí a kliků a CTR.

        CREATE TABLE promoter_breakdownCI AS
SELECT ctr1.breakdown_type,
       ctr1.breakdown,
       sum(ctr1.sum_value) AS impression,
       sum(ctr2.sum_value) AS clicks,
       sum(ctr2.sum_value)/sum(ctr1.sum_value) AS CTR
FROM tab_promoterctr AS ctr1
JOIN tab_promoterctr AS ctr2 ON ctr1.post_id = ctr2.post_id
AND ctr1.kampan = ctr2.kampan
AND ctr1.breakdown_type = ctr2.breakdown_type
AND ctr1.breakdown = ctr2.breakdown
AND ctr2.fb_api_metric = 'clicks'
WHERE ctr1.fb_api_metric = 'impressions'
GROUP BY ctr1.breakdown_type,
         ctr1.breakdown;

S daty v podrobnosti dle kampaní lze dále pracovat v detailu zaměřeném na úspěšnost jednotlivých kampaní, což je využitelné pro klienty i pro zpřesňování. Takto vznikla tabulka promoter_kampanCI.

    CREATE TABLE promoter_kampanCI AS
SELECT ctr1.kampan,
       ctr1.breakdown_type,
       ctr1.breakdown,
       sum(ctr1.sum_value) AS impression,
       sum(ctr2.sum_value) AS clicks
FROM tab_promoterctr AS ctr1
JOIN tab_promoterctr AS ctr2 ON ctr1.post_id = ctr2.post_id
AND ctr1.kampan = ctr2.kampan
AND ctr1.breakdown_type = ctr2.breakdown_type
AND ctr1.breakdown = ctr2.breakdown
AND ctr2.fb_api_metric = 'clicks'
WHERE ctr1.fb_api_metric = 'impressions'
GROUP BY ctr1.kampan,
         ctr1.breakdown_type,
         ctr1.breakdown;

A pro detailní pohled na úspěšnost jednotlivých postů v rámci kampaní je vhodná třetí tabulka s informacemi seskupenými podle postů – tabulka promoter_postCI.

CREATE TABLE promoter_postCI AS
SELECT ctr1.kampan,
       ctr1.post_id,
       ctr1.breakdown_type,
       ctr1.breakdown,
       sum(ctr1.sum_value) AS impression,
       sum(ctr2.sum_value) AS clicks
FROM tab_promoterctr AS ctr1
JOIN tab_promoterctr AS ctr2 ON ctr1.post_id = ctr2.post_id
AND ctr1.kampan = ctr2.kampan
AND ctr1.breakdown_type = ctr2.breakdown_type
AND ctr1.breakdown = ctr2.breakdown
AND ctr2.fb_api_metric = 'clicks'
WHERE ctr1.fb_api_metric = 'impressions'
GROUP BY ctr1.kampan,
         ctr1.post_id,
         ctr1.breakdown_type,
         ctr1.breakdown;

Kompaktní tabulky jsem se rozhodla vizualizovat v PowerBI. Pro získání přehledu o rozdělení dat v rámci jednotlivých kampaní i v jejich kombinacích je tento nástroj velmi praktický.

Výsledky

Vytvořila jsem report, který umožňuje analýzu dle tří úrovní obecnosti – na úrovni breakdownů, kampaní a postů. V reportu je v grafu zároveň vidět hodnota CTR dle jednotlivých breakdownů a zároveň podíl počtu impresí na celku tzv. support. Samotná metrika proklikovosti má totiž omezenou vypovídací schopnost, pokud k ní nemáme informaci o tom, jaké množství shlédnutí ji podporuje. I menší hodnota CTR za jednu skupinu uživatelů tedy může mít větší význam oproti jiné skupině, která má vyšší CTR, ale podporuje ji jen pár shlédnutí. Hodnoty impresí jsou normalizované vůči celku, aby bylo možné srovnání. Pro podrobnější pohled je v zobrazen také sloupec v tabulce s konkrétními hodnotami. Ponechala jsem pouze kódy kampaní bez uvedení jejich názvu a jména zákazníka důvodu ochrany citlivých dat. Ze stejného důvodu byly také u postů přečíslovány jejich kódy.

Náhled reportu je možné vidět na obrázku níže:

Z obecného hlediska je na úrovni agregovaných dat ze všech kampaní zajímavé rozdělení hodnot dle věkových skupin a dle použitého zařízení. Z věkových skupin jsou nejúspěšnější uživatelé mezi 18 a 34 lety, protože, ačkoli jejich CTR není celkově nejvyšší, je reprezentováno silným supportem impresí. To potvrzuje i pohled dle jednotlivých kampaní. Podrobnější pohled na věkové skupiny pak nabízí původní typ breakdownu „age,gendder“, kde jsou věkové skupiny rozlišeny navíc dle pohlaví. Samotná data za pohlaví nejsou tolik významná.

V případě použitého zařízení není překvapením, že nejvyšší support mají smartphony na platformě Android. Zařízení „Desktop“ tedy osobní počítač má dle očekávání také vysoké počty impresí, obě tato zařízení mají ale nižší proklikovost. Nejvyšší CTR má často Ipod, ovšem ten je podpořen naprosto minimálním počtem impresí, takže není třeba jej považovat za významný. Zajímavé jsou ale zařízení Iphone a Ipad, které nemají tak vysoký počet impresí, ale zároveň mají poměrně vyšší CTR.

Kromě výše uvedeného celkového pohledu na data z kampaní nabízí tento report řadu pohledů na data, které v rozhraní Facebook Business Manager nejsou k dispozici. Přímo v rozhraní je možné si zobrazit metriky na úrovni kampaní, sad reklam a jednotlivých reklam, není ale možné si zobrazit úspěšnost samotných postů, přičemž na jednom postu může být navázáno víc reklam. Zobrazení CTR je pak standardně možné pouze celkově, není možné vidět vliv na jednotlivé cílové skupiny. Z hlediska typů cílových skupin nabízí výše uvedený report navíc rozdělení dle regionů, detail všech použitých zařízení a oddělené cílové skupiny samostatně dle pohlaví a věku. Oproti rozhraní je pak zde možné zvolit libovolnou kombinaci skupin. Pro detekci anomalit – specifického účinku obsahu na konkrétní cílové skupiny – je vytvořený report praktičtější a uživatelsky přátelštější než rozhraní Facebook Business Manager.

Závěr

Díky tomuto projektu jsem si vyzkoušela práci s Velkými daty, která jsou složitá a je třeba je vyčistit. Ověřila jsem si jak je SQL mocný nástroj. Vytvořila si tento web a mohla zde publikovat interaktivní report z PowerBI.

Ponaučení zní – dvakrát přemýšlej, než spustíš dotaz. A ještě si ho raději šestkrát přečti.

Ve světě sociálních sítí je stále nerozluštěná otázka toho, jaký obsah v jaké míře koho zajímá. Analyzování metrik ve vazbě na cílové skupiny uživatelů má tak velký potenciál. Výše popsaná analýza představuje jen úvodní fázi rozsáhlejšího procesu. V první řadě bude třeba vytvořit podobný pohled na skupiny uživatelů s dalšími metrikami (například cena za klik, cena za konverzi). Dalšími kroky bude automatizované napojení na API a nastavení dynamických přehledů úspěchu různých cílových skupin.

Pokud se podaří pokročit v této práci dále, výsledky budu průběžně publikovat.

Závěrečný projekt Digitální akademie s Czechitas (za podpory Google.org)