4camping: Analýza objednávek e-shopu a vliv otevření kamenné prodejny na prodeje e-shopu
Luca Papp, Anna Gustavsson
Mentoring: Tereza Müllerová, Martin Beňačka, David Krejbich (Škoda Auto)
Úvodem, aneb oč tu běží
Projekt analyzuje objednávky e-shopu společnosti 4camping z hlediska času, geografického umístění a vybraných kritérií. Hlavním cílem je zjistit, zda otevření kamenné prodejny ovlivňuje prodeje e-shopu. A podívat se na to, jak velký je tento vliv a jestli se liší v rámci různých kritérií. Měly bychom si zároveň udělat představu, kde by bylo nejvhodnější otevřít případnou další prodejnu.
Jak to všechno začalo
Přestože zpracování open data zdrojů bylo hodně lákavé, pro náš projekt jsme vsadily na analýzu obchodních dat.
Obchodní data nám poskytla společnost, kde pracuje Luca. Jedná se o 4camping.cz, firmu provozující e-shop i kamenné prodejny s outdoorovým vybavením — produktem, který nám je oběma blízký, takže volba byla jasná.
Když Luca konzultovala s vedením 4campingu, jaké otázky by pro ně byly zajímavé z poskytnutých dat zodpovědět, padla otázka, jaký vliv, a jestli vůbec, má otevření nové prodejny na prodeje e-shopu zákazníkům z okolí prodejny. Otázka nesmírně zajímavá a dle googlování evidentně pro omnichannel obchody velmi důležitá.
Naším vedlejším (osobním) cílem projektu bylo ozkoušet si co nejvíc nástrojů, se kterými jsme se v Akademii potkaly.
- Profilace a čištění dat probíhalo v Keboole, využily jsme snowflake workspace, snowflake transformace, které jsme pak naházely do flow.
- S Pythonem jsme pracovaly ve VS Code, výstupy jsme pak v .csv nahrávaly do Kebooly.
- V Keboole jsme ozkoušely i komponentu Geocoding Augmentation.
- Visualizaci zpracovaných dat z databáze jsme dělaly v Tableau.
- Visualizaci dat z Google Analytics proběhla v Looker Studiu.
Plán práce na projektu:
- Identifikovat relevantní data v databázi 4campingu
- Prostudovat a vyčistit vybraná data, dodat chybějící atributy
- Metodika a zpracování tématu Haló efektu
- Zpracování výstupu — vizualizace, doporučení na základě výstupu — kde otevřít další prodejny.
- Analýza základních ukazatelů z Google Analytics a visualizace v Looker Studiu
Ad 1 | Identifikovat relevantní data v databázi 4campingu
V databázi jsme prošly 250 tabulek. Identifikovaly jsme 44 tabulek, které byly pro naše účely relevantní. Postupně jsme je stáhly na Google Drive, odkud jsme je postupně nahrávaly do Kebooly.
Vytvořily jsme datový model, který jsme pak (v závislosti na slepých uličkách, do kterých jsme se dostávaly) ne jednou upravovaly. Musely jsme se i vracet zpátky do databáze 4campingu pro chybějící tabulky, některé tabulky jsme nakonec vůbec nepoužily.
Ad 2 | Profilace a čištění dat
Ve zkratce, data nebyla tak čistá jak jsme doufaly. Narazily jsme hlavně u adres. Největší oříšek byl vymyslet, jakým způsobem budeme pracovat s geografickým určením objednávek.
Nakonec jsme vsadily na komplet geografické údaje až na úroveň souřadnic.
Protože na ID objednávky je navázáno hned několik adres bylo třeba pro každou objednávku určit ten relevantní údaj. Identifikovaly jsme tři typy případů, pro které jsme aplikovaly různé přístupy:
Tímto jsme dokázaly přiřadit relevantní adresu ke každé objednávce a u části objednávek jsme měly i souřadnice.
Přiřazování souřadnic k adresám bylo ve znamení Pythonu.
Pro zahřátí jsme geolokovaly 3248 adres poboček České pošty.
import csv
from geopy.geocoders import Nominatim
adresy = []
with open("balik_na_postu.csv", encoding="utf-8") as soubor:
for radek in soubor:
adresy.append(radek.strip())
# vytvoření instance geokodéru
geolocator = Nominatim(user_agent="my_app", timeout=20)
nenalezene_adresy = []
vysledky = []
# iterace přes seznam adres a získání jejich souřadnic
for adresa in adresy:
adresa_rozdelena = adresa.split(",")
adresa_upravena = adresa_rozdelena[0].replace(" ", "").replace("č.p.", "")
if adresa_upravena.isdigit():
mesto = adresa_rozdelena[2]
adresa = f"{mesto} {adresa_upravena}, {','.join(adresa_rozdelena[1:])}"
location = geolocator.geocode(adresa)
if hasattr(location, "latitude") and hasattr(location, "longitude"):
latitude = location.latitude
longitude = location.longitude
vysledky.append([adresa, latitude, longitude])
else:
nenalezene_adresy.append(adresa)
# zapsání výsledků do souboru CSV
with open("balik_na_postu_ok.csv", "w", encoding="utf-8", newline="") as soubor:
writer = csv.writer(soubor)
writer.writerow(["Adresa", "Latitude", "Longitude"])
for radek in vysledky:
writer.writerow(radek)
with open("balik_na_postu_unrecognized.csv", "w", encoding="utf-8") as soubor:
for adresa in nenalezene_adresy:
print(adresa, file=soubor)Následně nás čekalo přiřazení souřadnic u cca 200 000 adres zákazníků. To bylo větší sousto.
- práce s naším kódem (Nominatim — Openstreet map) byla s tak velkým objemem dat hodně těžkopádná. Bylo to pomalé, padalo to a úspěšnost byla vždy kolem 80% přiřazených souřadnic.
- Zároveň jsme tak hledaly i další řešení. Zkoušely jsme komponentu geolokace v Keboole. Tam to běželo rychleji, nicméně úspěšnost byla cca 75%. Tímto děkujeme Chochovi za neúnavné doplňování minut našemu projektu v Keboole.
- Na 2. hackatonu si k nám našel cestu API klíč geolokační služby AWS. Díky patří AWS a Zdeňku Bulanovi. Tato služba umožnila geolokovat cca 220 000 adres během cca 10 hodin s mimořádnou úspěšností. V průběhu se kód několikrát zastavil, protože nerozpoznal část adresy, ale stačilo vždy lehce dle chybové hlášky poupravit kód a jelo se dál.
import boto3
import json
import os.path
import csv
places = []
cache = {}
if os.path.isfile('location-cache.json'):
with open('location-cache.json', 'r', encoding="utf-8") as file:
cache = json.loads(file.read())
session = boto3.session.Session(
aws_access_key_id='xxxxxxxxxxxxxxxxxxxx',
aws_secret_access_key='xxxxxxxxxxxxxxxx'
)
location_client = session.client(service_name='location', region_name='eu-west-1')
num = 0
with open('dta_complete.csv', 'w', encoding='utf-8', newline='') as output_file:
writer = csv.writer(output_file)
writer.writerow(["Address", "Latitude", "Longitude", "SubRegion", "Region", "Municipality", "Neighborhood", "PostalCode"])
with open('dta_missing_1.csv', encoding='utf-8') as input_file:
for line in input_file:
place = line.strip().strip('"')
num = num + 1
if num == 1:
continue
if num % 100 == 0:
print(num)
print(json.dumps(cache, default=str), file=open('location-cache.json', 'w', encoding="utf-8"))
if num > 225000:
break
if place not in cache:
try:
response = location_client.search_place_index_for_text(
IndexName='MyPlaceIndex',
Language='cs',
MaxResults=1,
Text=place
)
cache[place] = response['Results'][0]['Place']
except IndexError:
# Handle the case when response['Results'] is empty or IndexError occurs
print(f"No results found for place: {place}")
continue
neighborhood = cache[place].get("Neighborhood", "")
postalCode = cache[place].get("PostalCode", "")
subregion = cache[place].get("SubRegion", "")
latitude = cache[place]["Geometry"]["Point"][1]
longitude = cache[place]["Geometry"]["Point"][0]
region = cache[place].get("Region", "")
municipality = cache[place].get("Municipality", "")
radek = [place, latitude, longitude, subregion, region, municipality, neighborhood, postalCode]
writer.writerow(radek)
print(json.dumps(cache, default=str), file=open('location-cache.json', 'w', encoding="utf-8"))
Nakonec jsme nebyly schopny identifikovat pouze 618 adres. Tyto objednávky jsme nakonec z našeho výstupního souboru vyloučily.
Další úpravy, které jsme provedly:
- vytvoření kategorií produktů,
- vytvoření nadkategorií způsobu doručení,
- tabulku product_variants jsme obohatily o atributy product_color (regex vytažení barvy z hodně divokého textového pole),
- vytvořily jsme číselník zákazníků,
- doplnily chybějící ceny produktů
- v pythonu prostřednictvím importu balíčku gender guesser https://pypi.org/project/gender-guesser/ a políčka “name” a následným doplněním údajů v SQL jsme doplnily údaje o zákazníkovi o atribut pohlaví.
import pandas as pd
import gender_guesser.detector as gender
# Načtení tabulky z CSV souboru
customer_tableau = pd.read_csv('customers.csv')
# Vytvoření instance detektoru pohlaví
detector = gender.Detector()
# Iterace přes záznamy v tabulce
for index, row in customer_tableau.iterrows():
first_name = row['name']
# Odhadnutí pohlaví na základě křestního jména
gender = detector.get_gender(first_name)
# Aktualizace hodnoty pohlaví ve sloupci
customer_tableau.loc[index, 'pohlavi'] = gender
# Uložení aktualizované tabulky zpět do CSV souboru
customer_tableau.to_csv('customer_gender.csv', index=False)ad 3 | Metodika a zpracování tématu Haló efektu
- Vymezení oblasti vlivu (catchment area — CA): určily jsme 4 oblasti — Hradec Králové, Liberec, Olomouc a České Budějovice
- Kontrolní skupina (KS): určily jsme 4 oblasti — Karlovy Vary, Ústí nad Labem, Zlín a Jihlava.
- Oblast vlivu jsme určily radiusem 20 km
- Zbytková skupina (ZS): Stejné testování provedeme na objednávkách ze zbytku republiky.
- Porovnání CA, KS a ZS v čase 1 rok před a po otevření nové prodejny nám pomůže izolovat účinky otevření prodejny od jiných faktorů, které mohou mít vliv na prodej.
- Podíváme se blíže na kategorie, u kterých je efekt silnější.
Ukázka skriptu určení 20km okruhu kolem prodejny:
--Hradec Králové 50.7703463 15.0611876
CREATE OR REPLACE TABLE CATCHMENT_AREA_HK AS (
-- Set the coordinates of the center point
WITH center_point_hk AS (
SELECT 50.2113735 AS center_lat, 15.8185508 AS center_lon
),
-- Convert the radius from kilometers to degrees (approximately)
radius_deg AS (
SELECT 7 / 111.045 AS radius_deg
),
-- Calculate the boundaries of the search area
boundaries AS (
SELECT
center_lat - radius_deg AS min_lat,
center_lat + radius_deg AS max_lat,
center_lon - radius_deg AS min_lon,
center_lon + radius_deg AS max_lon
FROM center_point_hk, radius_deg
)
-- Query to retrieve records within the specified radius
SELECT ORDERS_GEO.LAT, ORDERS_GEO.LONG, ORDERS_GEO.ID, ORDERS_GEO.CITY, 'HRADEC KRÁLOVÉ' AS CATCHMENT_AREA, 'IN' AS IN_OUT_CA, DATE('2017-11-15') AS STORE_OPENING_DATE
FROM ORDERS_GEO, boundaries, center_point_hk, radius_deg
WHERE (ORDERS_GEO.LAT != '' AND ORDERS_GEO.LONG != '') AND ORDERS_GEO.LAT BETWEEN min_lat AND max_lat
AND ORDERS_GEO.LONG BETWEEN min_lon AND max_lon
AND (6371 * ACOS(
COS(RADIANS(center_lat)) *
COS(RADIANS(ORDERS_GEO.LAT)) *
COS(RADIANS(ORDERS_GEO.LONG) - RADIANS(center_lon)) +
SIN(RADIANS(center_lat)) *
SIN(RADIANS(ORDERS_GEO.LAT))
)) <= 20);Následně jsme všechny tabulky s CA a KS spojily jedním velkám UNIONem.
ad 4| Zpracování výstupu — vizualizace
Výsledný datový model včetně vrstvy pro Tableau:
Náhled našeho reportu v Tableau:
Analýza objednávek:
- Analýza na základě vybraných dimenzí a metrik geograficky na úrovni krajů a v čase.
Haló efekt:
Pro vybrané prodejny sledujeme:
- podíl CA, KS a ZS na celku sledované metriky rok před otevřením prodejny a rok po otevření prodejny. Toto lze sledovat na úrovni jednotlivých kategorií.
- absolutní hodnoty v roce před a po otevření prodejny
- relativní změnu těchto hodnot před a po otevření prodejny
Tyto údaje můžeme pozorovat na základě jednotlivých metrik a pro různé kategorie.
Můžeme tedy například vypozorovat, že největší efekt má otevření prodejny na kategorii Oblečení a Boty, menší efekt na kategorii Spacáky a Stany. Nicméně i tam je efekt pozitivní.
Zajímavostí je, že průměrná hodnota objednávky se s otevřením prodejny snižuje. Nicméně tento pokles nebyl větší než nárůst počtu objednávek, takže haló efekt na tržbách je opět pozitivní.
Zároveň jsme znázornily situace i graficky — kdy jsme do jednoho grafu promítly hodnoty pro KS a CA a zobrazily referenční hodnotu s datem otevření prodejny.
Jednoznačnou kvantifikaci Haló efektu jsme neprovedly. Nicméně odpovědi na otázky ohledně haló efektu jsou z dashboardu čitelné.
Určitě ale ještě chceme na dashboardu haló efektu zapracovat. Pro haló efekt jsme zvolily vizualizaci prostřednictvím quick calculation — což není ideální (jsme si vědomi všech nedostatků, např. zobrazení podílu na celku průměrné hodnoty), ale bohužel nezbyl čas na to, dotáhnout verzi s kalkulovanými poli (obdoba řešení KPI).
Doporučení:
Poslední část dashboardu by uživatele měla navést na další možná místa, kde otevřít prodejnu.
Určitě by ještě stálo za to obohatit datový model o údaje z marketingových kampaní — vouchery, akce, bannery, info z newsletterů a zapracovat do reportu.
ad 5| Google Analytics
Tím, že Luca měla přístup i k Google Analytics 4campingu, podívaly jsme se i na tyto data.
Zkusily jsme Google Analytics napojit do Tableau a idea byla zpracovat a přidat tyto data do našeho dashboardu. Nicméně to se nepodařilo, tak jsme zkusily data z GA zpracovat v Looker Studiu.
Primárně nás zajímal konverzní poměr a počet transakcí před a po otevření vybraných prodejen v městech, kde byla prodejna otevřena a v městech z KS.
Rozdělení práce:
Čištění a transformaci dat jsme dělaly společně. Na začátku nám trochu trvalo si zkoordinovat práci v Keboole. S nadšením jsme se pustily nekoordinovaně do transformací a tvorby tabulek, že jsme se pak docela začaly ztrácet. Po prozření na prvním hackatonu, že tudy cesta nevede pak spolupráce probíhala koordinovaně a obě jsme se i v tranformacích té druhé dobře orientovaly. Proběhlo i několik společných soukromých hackatonů.
Takže jak frustraci, např. z našeho boje s geolokací, tak i radost z povedených joinů, unionů a updatů tabulek jsme si rozdělily rovným dílem :)
Úkoly, které jsme si rozdělily:
Luca — hlavní nahravač dat do Kebooly, GA a Looker Studio
Poté, co jsme z databáze 4campingu identifikovaly tabulky, které budeme chtít použít, Luca je exportovala na google drive a pak nahrála do Kebooly. Zároveň jen Luca měla přístup ke GA — takže práce s GA a Looker Studiem byla pod její taktovkou.
Anna — Tableau
Práce v Tableau není úplně komfortní pro sdílenou práci, práci na reportu tak dělala pouze Anna, zatímco Luca ji kryla záda odstraňováním nedostatků ve zdroji (např. nullové hodnoty, které jsme objevily až v Tableau).
Poděkování:
Děkujeme firmě 4camping za poskytnutá data.
Moc děkujeme Czechitas za neuvěřitelné 3 měsíce.
Děkujeme našim rodinám, pro které to taky musely být neuvěřitelné 3 měsíce :-D.
Děkujeme všem mentorům.
Děkujeme všem lektorům a koučům. Nesmírně si ceníme jejich znalostí a hlavně jejich superschopnosti tyto znalosti s nadšením předávat účastnicím kurzu.
Děkujeme spolubojovnicím za úžasnou atmosféru, která panovala po celou dobu akademie.
A speciální poděkování jde Terce Szkatulové.