Barbora Ambrůzová: Analýza prodejních dat a tvorba dashboardů

.... aneb jak jsem objevila Ameriku v BI analýze

Do digitální akademie jsem se přihlásila, abych si rozšířila obzory a případně našla nový kariérní směr. Během tří měsíců jsem si mohla osahat celou řadu nástrojů pro vizualizaci dat. Už vím, že SELECT DISTINCT není sprosté slovo a dokonce zvládnu naprogramovat pár řádku v Pythonu. Všechny lekce a workshopy byly super, jen jsem pořád měla pocit, že to není ono. Že v reálu prostě nepřijde lektor a neřekne mi, jaký program mám pro vizualizaci použít, nebudu mít předem připravený ideální dataset s pročištěnými daty, že vedle mě nebude stát někdo a radit mi, kam mám kliknout a jaký vzorec použít, a že k výslednému grafu a reportu se budu muset prokousat vlastními silami. V rámci závěrečné práce jsem si proto chtěla zkusit práci na reálném projektu.

Měla jsem štěstí. Hned ten večer po prvním setkání v rámci digitální akademie jsem potkala kamaráda Mirka.

„Jeee! Čau Mirku, jak se máš, co v práci?”

-„Ale jo, jenom se nám poslední dobou moc nedaří a nevíme proč.”

„Hele, já se teď pustila do studia datové analýzy, tak ti to pak přijdu zanalyzovat.”

-„Tak jo!“

Mirek společně s dalšími lidmi provozuje eshop zabývající se potiskem triček. Na trhu jsou už od roku 2006. Obrat zatím nemají dost velký, aby si mohli dovolit a vůbec se jim vyplatilo nějaké komplexnější BI řešení. Sami si vytvořili prodejní databázi v PostgreSQL, postupně ji rozšiřovali, až se celá struktura stala ne úplně přehlednou a vyčíst nějaké relevantní čísla pro ně byl oříšek. Takže se na data ve větším kontextu přestali dívat. To chtěl Mirek změnit, a tak jsme se o pár týdnů později domlouvali, jestli mi data vážně poskytne a jaká je jeho představa o výstupu. Zadání znělo jasně. „Zjisti, co je špatně.“

Těšila jsem se, že spasím Mirka a jeho eshop! Naplánovala jsem si, jak v Power BI zanalyzuju data z prodejní databáze, z Google Analytics, z Adwords. Celé to propojím a vytvořím přehledné dashboardy, které bude Mirek moct pravidelně sledovat a dělat BI rozhodnutí podložená relevantními daty.

Růžové brýle jsem ale odložila záhy a musela si přiznat, že v čase určeném na vypracování projektu tohle všechno se svými dosavadními zkušenostmi nemůžu stihnout. Neuměla jsem odhadnout, kolik času mi bude trvat, než se v datech zorientuju. Nepočítala jsem, že budu se svým počítačem několikrát prožívat „modrou smrt“ v tu nejmíň vhodnou chvíli. Že setkání s DAXem nebude láska na první pohled. Že to celé bude hodně o komunikaci s Mirkem. A pak všechny ty barvičky! Ale hezky popořádku…

Bez dat to nepůjde

Nejdřív jsem potřebovala získat data. Mirkova počáteční nedůvěra v moje schopnosti a moje obavy „abych v databázi něco nepokazila“ vyústily ve variantu dat exportovaných z databáze do Excelu a ten pak nahrát do Power BI. Později ale přišlo uvědomění, že pokaždé, když by se Mirek chtěl v reportu podívat na jiné období nebo jinak aktualizovat a doplnit data, musel by udělat export dat do Excelu, pak vzniklý soubor nahrát do Power BI a pořád dokola. Proto jsme se nakonec domluvili na variantě přímého přístupu propojením databáze s Power BI. K tomu bylo potřeba nejdřív nainstalovat ODBC driver, získat od Mirka přístupové údaje do databáze se pomocí ODBC driveru připojit.

Datový model 

Tady pak přišlo moje první znejistění. Databáze obsahuje údaje o objednávkách za posledních osm let, čítá kolem padesáti tabulek a jejich primární klíče i cizí klíče byly velkou neznámou nejen pro mě. Naštěstí přišlo vysvobození ve formě nápovědy, že je možné v Power BI využít stejné SQL dotazy, jaké Mirek kdysi vytvořil k exportu dat do Excelu. A pak už to šlo jako na drátkách. V Power BI jsem získala tabulky podobné těm z Excelu. Jedna se týkala všeho kolem objednávek a ta druhá obsahovala informace ohledně jednotlivých položek každé objednávky. Nejprve jsem v tabulkách upravila datové typu a pak jsem tyto dvě tabulky jednoduše propojila přes id objednávek. Pak jsem si vytvořila dimenzionální tabulku obsahující datumové údaje. Zde jsem využila tohoto připraveného kódu:

let

DayCount = Duration.Days(Duration.From(#date(2018, 1, 1) - #date(2014, 1, 1))),

// Add a day back, as Duration math above will give you the days literally between rather than inclusive. Ex Dec 31 - Dec 1 = 30 days, but we need 31 days in that period

DayCountAdd = DayCount + 1,

// Create a Dates list object starting at the start date, for the duration of the day count, increment of one year

Source2 = List.Dates(#date(2014, 1, 1),DayCountAdd,#duration(1,0,0,0)),

// Turn that date list into a table

TableFromList = Table.FromList(Source2, Splitter.SplitByNothing()),

// Update the type to Date

ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),

// Rename the only column in the table to date

RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),

// Insert columns for different units within the date dimension and supporting columns for sorting

// Optional "cs-CZ" parameter is used where applicable

InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),

InsertQuarter = Table.AddColumn(InsertYear, "QuarterNumber", each Date.QuarterOfYear([Date])),

InsertMonth = Table.AddColumn(InsertQuarter, "MonthNumber", each Date.Month([Date])),

InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),

InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthNumber] * 100 + [DayOfMonth]),

InsertMonthName = Table.AddColumn(InsertDayInt, "Month", each Date.ToText([Date], "MMMM", "cs-CZ"), type text),

InsertCalendarMonth = Table.AddColumn(InsertMonthName, "Month Of Year", each [Month] & " " & Number.ToText([Year])),

InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter of Year", each "Q" & Number.ToText([QuarterNumber]) & " " & Number.ToText([Year])),

InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),

InsertDayName = Table.AddColumn(InsertDayWeek, "Day Of Week Name", each Date.ToText([Date], "dddd", "cs-CZ"), type text),

InsertWeekStarting = Table.AddColumn(InsertDayName, "Week Starting", each Date.StartOfWeek([Date]), type date),

InsertWeekEnding = Table.AddColumn(InsertWeekStarting, "Week Ending", each Date.EndOfWeek([Date]), type date),

InsertMonthYrSort = Table.AddColumn(InsertWeekEnding, "MonthYrSort", each Date.ToText([Date], "yyyyMM", "cs-CZ"), type number),

InsertQuarterSort = Table.AddColumn(InsertMonthYrSort, "QuarterYrSort", each Number.ToText([Year]) & Number.ToText([QuarterNumber])),

ChangeTypes2 = Table.TransformColumnTypes(InsertQuarterSort, {{"MonthYrSort", type number},{"QuarterYrSort", type number}}),

InsertWeekOfYr = Table.AddColumn(ChangeTypes2, "Week", each "Týden " & Number.ToText(Date.WeekOfYear([Date])), type text),

InsertWeekOfYrNr = Table.AddColumn(InsertWeekOfYr, "WeekNr", each Number.ToText(Date.WeekOfYear([Date])), type text),

InsertStartOfMonth = Table.AddColumn(InsertWeekOfYrNr, "StartOfMonth", each Date.StartOfMonth([Date]), type date),

InsertEndOfMonth  = Table.AddColumn(InsertStartOfMonth, "EndOfMonth", each Date.EndOfMonth([Date]), type date),

InsertToday = Table.AddColumn(InsertEndOfMonth, "TodayDate", each Date.FromText("2016-11-12")),

ChangedType_TodayDate = Table.TransformColumnTypes(InsertToday ,{{"TodayDate", type date}}),

InsertDaysFromToday = Table.AddColumn(ChangedType_TodayDate, "DaysFromToday", each [TodayDate]-[Date]),

ChangedType_DaysFromToday = Table.TransformColumnTypes(InsertDaysFromToday,{{"DaysFromToday", Int64.Type}}),

#"Changed Type" = Table.TransformColumnTypes(ChangedType_DaysFromToday,{{"Year", Int64.Type}, {"QuarterNumber", Int64.Type}, {"MonthNumber", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DateInt", Int64.Type}, {"DayInWeek", Int64.Type}, {"DaysFromToday", Int64.Type}, {"WeekNr", Int64.Type}}),

#"Replaced Value" = Table.ReplaceValue(#"Changed Type",0,7,Replacer.ReplaceValue,{"DayInWeek"}),

#"Přejmenované sloupce" = Table.RenameColumns(#"Replaced Value",{{"QuarterNumber", "Quarter"}})

in

#"Přejmenované sloupce"

Vytvořila jsem další tabulku, která mi umožnila přepočet hodnoty objednávek v eurech na české koruny. Zde jsme po dohodě s Mirkem zvolili pevný kurz s možností časem přejít na automatické stahování kurzu z ČNB. Na Obr. 1 je znázorněno, jak vypadal datový model. Jakmile jsem měla datový model a ujasnila si, na co vše se chci ohledně prodeje zaměřit a kde tyto informace najdu, začala jsem postupně vytvářet jednotlivé dashboardy.

Obrázek 1.: Datový model

Report, report, report….

Ve výsledku jsem z dat z prodejní databáze vytvořila sedm dashboardů.

1)      Tržby podle času a geografie (Obr. 2)

Zde si může Mirek prohlédnout vývoj tržeb ve vybraném období a státu. Pomocí dalších filtrů jsem omezila stornované a vrácené objednávky. Zároveň je tu k nahlédnutí i vývoj počtu objednávek v čase a graf pro srovnání tržby s předchozím období. Abych mohla vytvořit graf srovnávající dvě období vypočítala jsem metriku _Tržba, kde došlo i k přepočtu hodnotu eurových objednávek na koruny. Tato metrika pak byla použita napříč všemi reporty. Použila jsem tento vzorec:

_Tržba = SUMX(Polozky;Polozky[cena_po_sleve]*RELATED('Prevodnik men'[Kurz]))

Pak jsem vypočítala další metriku _Tržba LY s využitím tohoto vzorce:

_Tržba LY = CALCULATE([_Tržba]; SAMEPERIODLASTYEAR(Datum[Datum]))

A nakonec jsem upravila datumový filtr na požadované období.

 Obrázek 2.: Tržby podle času a geografie

2)      TOP grafik a produkt (Obr. 3)

Mirka také zajímalo, který grafik a produkt přinášejí firmě největší tržby. V dashboardu si může zobrazit TOP 5 grafiků a jejich podíl na tržbě v čase, filtrovat v různých časových obdobích, v tabulce sledovat nejvíce prodávané produkty.

Obrázek 3.: TOP grafik a produkt

3)      TOP služba (Obr. 4)

V rámci tohoto dashboardu jsem se snažila zpřehlednit, jaký podíl mají na tržbě jednotlivé služby, které eshop poskytuje.

Obrázek 4.: Top služba

4)      TOP zákazník (Obr. 5)

Kteří zákazníci u nás utratí nejvíc peněz? Kteří zákazníci u nás nakoupí pouze jednou, kteří nakoupí vícekrát? Jaké je průměrná hodnota objednávky? Na tyto otázky může Mirek najít odpověď v tomto dashboardu.

Obrázek 4.: Top služba

Dlouho jsem nemohla přijít na to, jak zákazníky na „one-timery“ a „repeatery“ rozdělit. Načítala jsem zkušenosti uživatelů s DAXem zběhlejších a testovala jsem funkci SUMMARIZE nebo GROUP BY. Až jsem se nakonec rozhodla pro vytvoření nové tabulky pomocí SQL dotazu přímo z dat z databáze.

select email, count(*)

from objednavka

where cas_stornovani is null

group by email

order by count(*) desc;

Vznikne mi nová tabulka, kde je vidět, který zákazník objednal v eshopu zboží vícekrát. 

5)      Slevové kupóny (Obr. 6)

V tomto dashboardu může Mirek sledovat, které slevové kupóny přinášející nejvíc tržeb, jejich uplatnění v čase a zákazníky, kteří slevové kupóny uplatňují nejčastěji.

Obrázek 6.: Slevové kupóny

6)      Reklamace a storno (0br. 7)

      Jaké procento z celkového počtu objednávek tvoří ty stornované? Jaký je nejčastější důvod storna?

Obrázek 7.: Reklamace a storno

7)      Expedice (Obr. 8)

V rámci tohoto reportu jsem si chtěla vyzkoušet, jak se dají v Power BI tvořit segmenty. Nejdřív jsem si vypočítala sloupec, který mi pro každý řádek vrátil počet hodin od zadání objednávky až po její expedici.

_Doba expedice = DATEDIFF(Objednavky[cas_vytvoreni];Objednavky[cas_expedovani];HOUR)

Pak jsem použila funkci IF, abych objednávky podle času potřebného k expedici rozdělila do tří skupin.

_Segment doby expedice = IF(Objednavky[_Doba expedice]<72;"méně než 72 h"; IF(Objednavky[_Doba expedice]>=72 &&Objednavky[_Doba expedice]<=144 ; "mezi 72 h a 144 h"; "více než 144 h"))

Obrázek 8.: Expedice

Je to vážně konec?

Co jsem se během vypracování projektu naučila? Nejsem nutně ten, kdo ví všechno – mým úkolem bylo hlavně data představit v přehledné formě. Všechno nějakou dobu trvá (obvious). Googlit a ptát se, nechat si radit, zkoušet znovu a znovu a opravovat.

Mirka už jsem s reporty seznámila a je spokojený. Může prodejní data sledovat přehledně na jednom místě. Data je možné do Power BI díky přímému propojení s databází pravidelně nahrávat. Tím ale celý projekt nekončí. Domluvili jsme se na další spolupráci, kdy budeme reporty vylepšovat a přidávat další metriky a grafy. Rádi bychom se zaměřily i na data z Google Analytics a Adwords. Taky jsme mluvili o možných úpravách databáze a ukládání dat do ní.

Na závěr bych chtěla poděkovat Mirkovi za důvěru a zpřístupnění dat. Luboši Bednářovi, který mou práci vedl, za každou konzultaci a pomoc, díky níž se práce posouvala k lepšímu. Díky také patří Adéle a Báře z Intelligent Technologies za veškeré jejich rady s DAXem.

Zdroje:

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-quickstart-learn-dax-basics/

https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-7-3-dax-functions/

https://powerbi.microsoft.com/en-us/