9. běh Digitální akademie v Brně

Jak jsme postupovaly

Náš projekt se zabývá analýzou dat z prodeje IT produktů za období 2012 až 2018. Do této chvíle jsou data uložena v excelových tabulkách, které slouží pro další zpracování – vytváření dalších excelových tabulek :). Obchodní zástupci a vedení firmy s daty pracují na denní bázi, především za účelem kontroly prodejů, smluvních zákazníků, ale i pro plánování kroků a směřování společnosti do dalších období.

Časová náročnost zpracování dat a získání žádoucích výsledků je v současné chvíli něco přes 5 dní měsíčně. To je v přepočtu 480 hodin ročně. Současně vznikají chyby ve zpracování, které mají dopad na finanční bonusy obchodních zástupců. Četnost těchto chyb se pohybuje okolo 20 %, tedy každý pátý report obsahuje chybu.

Hlavním cílem našeho projektu byla úspora času na ½ dne měsíčně, tedy na 48 hodin ročně a současně snížení rizika chyb ve zpracování dat na 5 %, tedy aby chybu obsahoval maximálně každý dvacátý report. Výstupem projektu je dashboard, který usnadní a zefektivní zpracování dat obchodníkům i vedení firmy.

Čištění dat

Prodeje produktů jsme získaly v 7 excelových tabulkách, za každý rok jednu. Každá tabulka vzniká v interním systému z více zdrojů. Ve velké části případů jsou data zadávána manuálně. Objevila se celá řada překlepů, logických chyb, chybějících údajů a nesmyslů. Každá tabulka měla rozsah 150-200 tisíc řádků. K dispozici jsme měly také tabulku KPI prodejů smluvních zákazníků i obchodních zástupců. Kromě oprav a kontroly logické správnosti dat jsme data anonymizovaly pomocí funkce vlookup v Excelu, sjednotily adresy pomocí PSČ České pošty (opět vlookup) a rozdělily do csv souborů.

Ukázka části zdrojových dat s počtem řádků
Anonymizovaná data za použití vlookup

SQL databáze

Využily jsme Microsoft SQL Server Management Studio, ve kterém jsme založily databázi a postupně naimportovaly veškeré tabulky ve formátu csv. S využitím CREATE TABLE a INSERT INTO jsme vytvořily tabulky nové (podle předem nakresleného datového modelu) a vložily do nich agregovaná data. U všech sloupců jsme upravily datový typ. Napsaly jsme „insert script“, který nám usnadní práci při zpracování dalších tabulek, které budou v praxi přibývat jednou měsíčně. Využili jsme především příkazů SELECT, SELECT DISTINCT, UNION, UNION ALL. Celou databázi jsme v závěru přesunuly na Azure, abychom zajistily její funkčnost 24/7.

Vytvoření nové tabulky
Spojení všech tabulek Sales pomocí UNION ALL

PowerBI

Další fáze projektu spočívala v importu dat z databáze do PowerBI. Po importu jsme upravily vazby v datovém modelu, vytvořily tabulku Date a také tabulku Measures, do které jsme vytvářely všechny výpočty. Pracovaly jsme s jednoduchými příkazy v jazyce DAX, většinou jsme si ale vystačily s předdefinovanými Measures, úpravy jsme využily pouze u některých time intelligence ukazatelů (YoY, MoM, QoQ).

Propojení tabulek v PowerBI

Dle informací od obchodních zástupců a vedení společnosti jsme vytvořily dashboardy s ukazateli, které se ve firmě reálně sledují. Popisky grafů máme v českém jazyce, proměnné v anglickém, aby výstup odpovídal aktuálním zvyklostem prezentace dat ve společnosti. Výstupem je tedy několik dashboardů:

Obecný dashboard obsahuje analýzu vratek (před rokem 2016 vratky možné nebyly), obrat produktů ve vztahu k jejich ceně, nejprodávanější kategorie produktů, nejprodávanější produkty a celkový meziroční obrat v procentech, včetně predikce na dva roky dopředu.

Úkazka obecného dashboardu

Dashboard pro management – náhled pro vedení společnosti, kde zobrazujeme, jak si vedou obchodníci (co do obratu, počtu bodů i plnění KPIs) a jak si vedou reselleři. Vedení společnosti zajímá především srovnání s loňskem, plnění plánu a srovnání jednotlivých obchodníků.

Úkazka dashboardu pro vedení

Dashboard pro každého obchodníka – uzpůsobený tak, aby si mohl obchodní zástupce vyfiltrovat rok, případně i měsíce, které ho zajímají a zobrazit si svoje prodejní výsledky a také výsledky resellerů:

Úkazka dashboardu pro jednotlivé obchodníky

Dashboard Online

Vzhledem k neustálé pracovní vytíženosti a služebním cestám obchodníků jsme se snažily vymyslet co možná nejsnadnější a nejrychlejší přístup k dashboardům odkudkoliv. Rozhodly jsme se proto vytvořit webovou stránku, kde se po přihlášení obchodníci k datům ihned dostanou téměř odkudkoliv. PowerBI dashboard je do stránky vložen pomocí HTML kódu a zabezpečení proběhlo na serveru, na kterém běží doména (Forpsi – basic auth tedy bylo možné nastavit přímo v administraci).

Vygenerovaný kód pro vložení do HTML

Webová prezentace

Responzivní webová stránka slouží zároveň pro prezentaci našeho projektu. Vytvořily jsme ji za pomocí HTML 5, CSS 3 a Javascriptu v naprosto skvělém editoru VS Code. K vytvoření stránky nám dopomohly znalosti získané na kurzech Czechitas Web od A do Z a Javascript vedené Luďkem Rolečkem, všemocný Google a velké nadšení :).

Javascript pro skrývání hamburger menu a fungování tlačítek
Ukázka CSS kódu

Další vývoj

Naše práce na projektu je teprve na začátku. Čeká nás představení finálního řešení společnosti a následná implementace řešení. V prvním kroku přesuneme doménu do firemní sítě, s tím bude souviset úprava řešení zabezpečení – rády bychom zabezpečení přenesly do vrstvy aplikace, ne do vrstvy serveru, jak to máme aktuálně. Dalším krokem a oblastí rozvoje je automatizace čistění dat.

Hlavní cíle a přínosy projektu

Data v dashboardu představují prodeje smluvních zákazníků dle jednotlivých obchodníků. Obchodník má vždy na starosti resellery z určitého regionu. U těchto resellerů pravidelně kontroluje prodeje HW a spotřebního materiálu, plnění závazků, počty bodů, tenderů apod. Vzhledem k tomu, že jsou obchodníci většinu času na cestách, potřebují mít přehled u sebe kdykoliv a kdekoliv. Také vedení firmy potřebuje mít přehled o všech svých obchodnících a jejich zákaznících, ale zároveň se zajímá také o prodejnost jednotlivých kategorií. Veškeré informace následně slouží pro plánování akcí, slev, marketingových aktivit apod. na další měsíce a roky.

ruce

Zákazníci

Pro každého obchodního zástupce je klíčový přehled o zákaznících. V PowerBI jsme každému obchodníkovi vytvořily tabulku jen s jeho zákazníky. Tabulku si lze vyfiltrovat podle let a měsíců a ihned tak zjistí, jak si stojí. Každý produkt je jinak výdělečný. Je tedy důležité kontrolovat i výkonnost jednotlivých produktů rozdělených do kategorií.

zvonek

Srovnání a plánování

Práce každého obchodníka stojí i na srovnávání měsíčních, kvartálních a ročních výsledků. V PowerBI jsme vytvořily filtr pomocí kterého si obchodník jednoduše nakliká požadované období. V současné době si obchodník musí otevřít tabulku (nebo někdy i více tabulek) v Excelu pro jednolivé roky a tam filtrovat. Naše řešení tedy ušetří hodiny času.

Kdo stojí za projektem?

Kateřina Blatná šipka vpravo

Kateřina Blatná

Markeťačka s velkým nadšením do informačních technologií, která si oblíbila kouzla s webovým kódem. Digitální akademie mi otevřela další zajímavou a neprobádanou oblast. Jsem vděčná za tuto příležitost.

Alena Krejčířová šipka vlevo

Alena Krejčířová

S velkým nadšením využívám data pro rozhodování, i když ne vždy to v oblasti HR je zcela jednoduché. Z Digitální Akademie mi učarovala oblast vizualizace dat, kterou se chystám dál probádat.

Děkujeme našim mentorům Romanu Barošovi a Honzovi Richterovi i všem ostatním mentorům, koučům, zástupcům firem i celému týmu Czechitas a hlavně všem ostatním kolegyním z Digitální akademie. Všichni jsme dohromady tvořili skvělý tým!