Lenka Švrčinová: Analýza obchodních výsledků v návaznosti na používání CRM systému

Než jsem se přihlásila do Digitální akademie, měla jsem o práci s daty jen velmi malé povědomí. Po práci v oblasti podpory exportu jsem nakoukla do IT světa a už bych se tu ráda usadila. Začínám pozvolna, zabývám se implementací CRM pro menší firmy, přípravou mailových kampaní v MailChimp nebo tvorbou webových stránek ve Webnode. Ale člověk nemusí být génius, aby tohle zvládal, chtělo by to něco sofistikovanějšího.

A protože se ráda učím nové věci, při jednom hledání on-line kurzů jsem objevila Czechitas, zrovna měl začínat dlouhodobější kurz Digitální akademie. Po pravdě, když jsem ho otevřela poprvé, dost jsem váhala. Většinu pojmů jsem slyšela poprvé v životě, jediný program na data, který jsem znala, byl Excel a jen jsem se modlila, aby byl kurz opravdu pro začátečníky, ne pro někoho, kdo s těmito programy pracuje "jen" pět, deset let. Ale nedalo mi to, vypravila jsem se na úvodní schůzku a přijela domů úplně nadšená. Za devět týdnů ze mě bude datová analytička (!). Natočila jsem motivační videovizitku, poslala vyplněný formulář a za pár týdnů sedla do lavic.

Ze začátku jsme do dozvěděli pár úvodních informací - jak vybírat projekt, kde čerpat data, kdo je to vlastně datový analytik a jak všichni (?), kdo to dělají, to vlastně nikdy nestudovali. Zdá se, že stačí zdravý selský rozum a Google. Po týdnu jsme v kavárně prezentovali své nápady na závěrečnou práci mentorům a po přidělení mentora jsme mohli začít na samotné práci.

Anonymizace

S firmou, se kterou spolupracuji, jsem se domluvila na možnosti zpracování jejich účetních dat a porovnání s daty ze CRM systému. Dostávám jeden soubor z účetního systému Pohoda, další dva si stáhnu ze CRM Pipedrive. Málokterá firma pustí do světa informace o svých výsledcích, proto bylo potřeba začít anonymizací dat. 

V Excelu jsem pomocí funkce SVYHLEDAT přiřadila jednotlivým zákazníkům ID ze CRM systému, abychom je mohli v budoucnu jednoduše dohledat bez nutnosti archivovat číselník. Nahradila jsem i jména jednotlivých obchodníků a stejně tak jsem anonymizovala adresy zákazníků jejich rozřazením do jednotlivých okresů a krajů. To bylo trochu složitější, každý obchodník píše adresu svého zákazníka trochu jiným způsobem, někdy je město vlevo, někdy vpravo, jindy uprostřed. Nakonec jsem v přímo v CRM našla možnost převodu adres pomocí Google, rozdělila adresu na jednotlivé části a potom už to bylo víceméně jednoduché. Protože mají některé obce v ČR stejný název, použila jsem jako hlavní vodítko pro rozřazení PSČ a podle číselníku České pošty provedla přiřazení do příslušného okresu / kraje. Podle názvu obcí jsem rozřadila ty ostatní, jen jsem u duplicit zkontrolovala správnost.

Excel - anonymizace sídla zákazníka pomocí SVYHLEDAT

Příprava importních tabulek v SQL

Po první lekci SQL jsem trochu v rozpacích. Měla bych v něm zpracovávat ty tabulky, ale vůbec netuším, co se po mně chce. Chvíli odolávám, mentorovi se ozývám až po měsíci, kdy je jasné, že další lekce nebudou :-) . 

Opatrně začínám pracovat. Podle přednášek připravím kostru tabulek a jejich vnitřek postupně nakopíruji do SQL.

Command line - import tabulek do SQL

Soubory postupně prohlídnu v SQL a všímám si, že mám v tabulce dvě hlavičky - připravenou i zkopírovanou. Ujistím se, že mám správně zapsán příkaz (SELECT * FROM PD_activity WHERE ID_act = 'ID_act') a tenhle řádek úpravou příkazu smažu (DELETE FROM PD_activity WHERE ID_act = 'ID_act'). První opravdový počin v SQL se povedl, to by mohlo jít..

Pro jistotu kontroluji, jestli se jednotlivé sloupce načetly správně (SELECT DISTINCT okres_org FROM PD_organization.. postupně pro všechny sloupce). 

Nenačetly. V jednom sloupci se za každou položkou objevily dva středníky. Ty bych mohla vymazat při převodu do finálních tabulek. TO UMÍM.

Tvorba finálních tabulek

Aha, tak neumím..

Kdo tohle nezná, je šťastlivec, mně to dost snížilo sebevědomí..

Založila jsem si slovník chybových hlášek a pomocí Googlu odlaďovala všechny chyby (NOT NULL je nesmí být nuly nebo nevadí nuly??). 

Nakonec jsem se s tím vším poprala a převod se povedl.

SQL - vložení údajů z importní tablulk do finálni, zároveň odstraňujeme nechtěné středníky za názvy zemí

No hurá.

Nastal čas na další lekci SQL. S mentorem Romanem se potkávám v kavárně a dostávám poměrně intenzívní tříhodinové školení. Chvíli se vzpamatovávám z přemíry informací, ale postupně mi některé věci dochází. 

Takže zase od začátku.. příkazy mám sice uložené v počítači, ale píšu si je sama, protože SE TO UČÍM PRO SEBE a jen kontroluji a hledám chyby. 

A tohle mě opravdu nadchlo: Není nutné složitě vypisovat příkaz v Command Line pro opakovaný import několika tabulek. Stačí si je nachystat do poznámkového bloku s příponou bat a pak jen kliknout na název souboru.

Poznámkový blok - hromadný import tabulek do SQL

Načtou se samy:

SQL - výsledek importu

Tvorba číselníků

Nejdřív si vytvořím číselník pro typ aktivit. Vytvořím prázdnou tabulku - první sloupec bude primární klíč s automatickým číslováním od jedničky, takže:

CREATE TABLE t_type_act (

    id_type_act INT IDENTITY (1,1),

    type_act NVARCHAR (20) NOT NULL

)

Do tabulky vložím hodnoty z importní tabulky. První sloupec se bude číslovat automaticky, proto vypisuji jen příkaz pro druhý sloupec:

INSERT INTO t_type_act (type_act)
SELECT DISTINCT

    type_act

FROM import_activity

Obdobně pokračuji ve tvorbě dalších číselníků - pro okresní města a pro státy: CREATE TABLE t_okres a INSERT INTO. Objevuje se hláška "Cannot insert the value NULL into column 'okres', table 'PROJEKT.dbo.t_okres'; column does not allow nulls. INSERT fails.", která mě upozorňuje na to, že mezi okresy jsou i NULL hodnoty, ale v číselníku jsme nulové hodnoty zamítli (to je rovnou odpověď na dotaz, co znamená NOT NULL). Vyřeším úpravou dovětku WHERE ... IS NOT NULL.

Tvorba finálních tabulek po stopadesáté

Začínám tabulkou final_activity. Tabulku vytvořím:

CREATE TABLE final_activity (

    ID_act int identity (1,1),

    ID_org int not null,

    date_act date not null,

    owner_act nvarchar (50) not null,

    type_act nvarchar (20) not null

)

Když zkontroluji tabulku v Design table, mám pocit, že si se mnou SQL Management Studio hraje:

SQL - rozdíl mezi záměrem a výsledkem

Nakonec se mi podaří upravit datový typ i autoinkrementaci a do tabulky vložím údaje. 

Kdybych tvořila tabulku jen z importní tabulky, vypadalo by vložení dat následně:

INSERT INTO final_activity (ID_org, date_act, owner_act, type_act)

SELECT

    ID_org,

    date_act,

    owner_act,

    type_act

FROM import_activity

Ale protože chci tabulku propojit s vytvořeným číselníkem, přidám pod příkaz LEFT OUTER JOIN, kterým označím, že chci tabulku propojit s číselníkem podle políček t_type_act v obou tabulkách (LEFT OUTER JOIN t_type_act T ON T.type_act = I.type_act). Písmenem T a I označuji alias příslušné tabulky (I = importní, T = číselník). Stejná písmena použijeme i pro určení, který sloupec kopírujeme ze které tabulky:

INSERT INTO final_activity (ID_org, date_act, owner_act, type_act)

SELECT

    I.ID_org,

    I.date_act,

    I.owner_act,

    T.id_type_act

FROM import_activity I

LEFT OUTER JOIN t_type_act T ON T.type_act = I.type_act

Další tabulka bude final_organization. Tady potřebuji, aby se mi ID_org zachovalo to, které mám vytvořeno v rámci anonymizace - vynechávám autoinkrementaci.

CREATE TABLE final_organization (

    id_org INT NOT NULL,

    owner_org NVARCHAR (50) NOT NULL,

    segment_org CHAR(1) NULL,

    okres_org INT NULL,

    country_org INT NULL

)

A tabulku naplním podobným způsobem jako v předchozím případu, jen mám dva JOINy.

INSERT INTO final_organization (id_org, owner_org, segment_org, okres_org, country_org)

SELECT

    I.id_org,

    I.owner_org,

    I.segment_org,

    O.id_okres,

    C.id_country

FROM import_organization I

LEFT OUTER JOIN t_okres O ON O.okres = I. okres_org

LEFT OUTER JOIN t_country C ON C.country = I.country_org

Tabulku s účetními daty necháme tak, jak je - není potřeba na ní nic měnit.

Konečně analýza

Vývoj aktivit v čase

Začnu něčím jednodušším. V SQL si připravím příkaz pro vyselektování jednotlivých typů aktivit, které zaměstnanci firmy zaznamenali do CRM systému, společně s datem vykonání aktivity.

SQL - vytvoření tabulky pro analýzu obchodních aktivit

Výslednou tabulku vložím do Excelu (CTRL A, CTRL C, CTRL V.. nic, co bych neuměla, ale nenapadlo by mě, že to jde uložit přímo z SQL). Uložím, nahraju do Power BI a hraju si. Po chvíli zkoušení mi přijde Power BI celkem intuitivní, snažím se vymyslet grafy, které budou nejvíce zajímavé pro firmu, která mi data poskytla.

Power BI - využívání CRM pro obchodní aktivity

V grafu nahoře vidíme vývoj jednotlivých druhů aktivit uskutečněných za celou dobu používání CRM, levý graf dole ukazuje aktivity za posledních 12 měsíců v sestupném uspořádání - pro zajímavost jsem vyfiltrovala jen aktivity uskutečněné po 31. říjnu 2016 (všechna data končila koncem letošního října). A v posledním grafu jde vidět zastoupení aktivit v jednotlivých měsících roku - z tohoto grafu jsem vyřadila aktivitu "nabídka", protože se ve firmě používá až od konce letošního srpna a výsledky by byly zkreslené.

Obrat jednotlivých obchodníků v čase

Pro sestavení tabulky v SQL nejprve zkouším jen jeden měsíc:

SELECT

    owner_org,

    leden_2015

FROM import_organization I

LEFT OUTER JOIN import_ucetnictvi U ON U.id_org = I.id_org

Vypíše všechny organizace se svým ID, ke každé přiřadí ownera a jaký měli v lednu obrat.

Přidám GROUP BY owner_org, aby se sečetly podle ownera, SQL radí, že je potřeba přidat "aggregate function", přidám SUM (leden_2015) a protože to funguje, rozšířím i na ostatní měsíce.

SQL - vytvoření tabulky pro vývoj obratu jednotlivých obchodníků

V Excelu ještě upravím pro další použití - tabulku transponuju, přidám součty a můžu nahrát do Power BI.

Power BI - porovnání obratu, schůzek a telefonických jednání ve stejném časovém úseku

No.. zase tak jednoduché to nebylo. Napadlo mě, že bych mohla porovnat, jestli obrat koresponduje s vynaloženým úsilím, ale chyběly mi údaje ve stejné struktuře, jako jsem měla u tabulky pro obrat (údaje o uskutečněných aktivitách rozdělené podle obchodníků a seskupené do jednotlivých měsíců). Nakonec jsem použila importní tabulku a zjistila jsem, že Power BI si s tabulkou poradí tak, jak je. Ve všech grafech jsem vybrala tři stejné obchodníky, označila je vždy stejnou barvou a v jednom grafu vyfiltrovala jen telefonáty a ve druhém jen osobní schůzky.

Z mého pohledu vztah mezi aktivitou (telefonáty, schůzky u zákazníků) a následkem v podobě vlivu na výši obratu vidět není, z velké části z důvodu, že dlouhou dobu po zavedení CRM nebyli jednotliví obchodníci v systému přiřazeni ke "svým" firmám a jeden obchodník nastoupil před rokem a část zákazníků i se svým obratem byla převedena na něj. Firma navíc přechází na plné využití CRM systému pozvolna, větší zapojení deklarují až od září 2017 (poslední zobáček v grafu), kdy nárůst aktivit i obratu viditelný je. Rozhodně by bylo zajímavé zpracovat tuhle analýzu později, abychom viděli dopad využití CRM systému na obrat firmy, což bylo mým hlavním cílem.

Vývoj obratu v jednotlivých segmentech

Od září 2016 začala firma používat rozdělení zákazníků na jednotlivé segmenty podle výše obratu - zákazníkům A, B, C je věnována větší péče než těm ostatním.

Pro vytvoření tabulky jsem použila stejný model jako u tabulky pro obrat obchodníků, jen jsem vybrala jiný sloupec z jiné tabulky.

Další tabulku mi pomohl sestavit mentor, z časových důvodů jsem se ani nesnažila ji vytvořit sama, ale doufám, že bych ke stejnému výsledku mohla dospět zdlouhavějším způsobem postupného vytváření tabulek.  Novým pojmem je ISNULL(leden_2015,0), který mění hodnoty NULL na 0, kterou umí sčítat na rozdíl od NULL.

SQL - vytvoření tabulky pro celkový obrat, průměrný obrat a počet faktur

Po zadání tabulek do Power BI jsem vytvořila následující grafy:

Power BI - obrat v jednotlivých segmentech

V horním grafu vidíme vývoj jednotlivých segmentů po měsících, z posledního vývoje se zdá, že výraznější snaha o péči o firmy v segmentu A a B se pozitivně projevuje na celkovém obratu. Koláčový graf a popisky vpravo ukazují poměr jednotlivých segmentů dle výše vystavených faktur za sledované období. Stejné výsledky jsou vidět v tabulce vlevo dole (ta složitá..).

Sídlo zákazníků - okres, kraj

Zajímalo mě, ze kterých míst má firma nejvíce zákazníků. Už v úvodu práce jsem si převedla sídla zákazníků na příslušné okresy a kraje, pokud chci provázání na obrat firmy, pomůžu si v SQL následujícím příkazem:

GitHub - ukázka kódu pro obrat v jednotlivých okresech

Vývoj v čase u jednotlivých obcí mi tolik zajímavý nepřijde, proto v Excelu dopočítám celkové součty a googlím, kde najdu mapu pro vložení do Power BI. Po vložení tabulky do Power BI zkouším jednotlivé mapy, než odpovídá výsledek mé představě. 

Power BI - zákazníci v jednotlivých okresech / krajích podle obratu

V grafu je rozdělení zákazníků na okresy (kolečka s velikostí podle obratu) a na kraje (podle barev). Města i kraje jsou podle grafů seřazeny podle výše obratu, není překvapením, že firma má největší obrat ve Zlínském kraji, kde má sídlo, a v Praze, kde má pobočku.

Zákazníci celosvětově

Power BI - zákazníci v jednotlivých zemích podle obratu

Firma má zákazníky i v zahraniční, pro lepší přehled jsem vytvořila mapovou vizualizaci a filtrem zrušila Českou republiku. Zajímavými údaji ve vývoji zakázek podle obratu jsou prudký nárůst zájmu ze Slovenska v únoru 2017, kdy byl slovenský obrat vyšší než tuzemský a hodnoty z USA, které dosahují v "lepších" časech až polovinu obratu českých zakázek.

Přehled nejvýznamnějších zahraničních zákazníků podle zemí jsem vytvořila pomocí filtrovacího typu Top N s vyloučením České republiky.

Závěr

Moje práce s daty není zdaleka u konce. Čím více se blížím termínu odevzdání hotového článku, tím častěji mě napadá, co bych ještě mohla vylepšit, dodělávám nové tabulky, nové grafy, čtu články na internetu, sleduju videa.. Asi je dobře, že má práce i finální termín, jinak bych ji vylepšovala donekonečna.

Každopádně by se mi moc líbila naznačená možnost zautomatizování celého procesu analýzy včetně publikování výstupu na webu, aby měla firma neustálý přehled o tom, jak si vede. 

Práce s daty mě velmi nadchla, obzvlášť s Power BI jsem si ke konci vyhrála, ale jsem ráda, že jsem si mohla sáhnout i na MS SQL, za což děkuji svému mentorovi. 

Své rodině děkuji za klid při práci a omlouvám se svému psovi, kterého mezitím nechali tito báječní lidé skoro vyhladovět.. :)

Zdroje

• zápisy z přednášek Czechitas a rady lektorů - Roman Baroš, Pavel Lasák, Honza Mayer
• Google
• Stack Owerflow
• Luboš Bednář (Intelligent Technologies) - Power BI - Part 1 
• Luboš Bednář (Intelligent Technologies) - Power BI - Part 2
• Embedding Github code into your Blogger blog
• Mapa ČR pro vizualizaci v Power BI