STAP 2: Laad CSV als dataframes
Klik met de rechtermuisknop op het bestand dat je wilt importeren en klik "query with sql"
SELECT *
FROM 'adidas-keywords-all (1).csv'
SELECT *
FROM 'nike-keywords-all (1).csv'
STAP 3: Union All - a.k.a Merge
Een union all werkt zo lang de geladen tabellen (df_2 en df_1) exact dezelfde kolommen hebben.
SELECT * FROM df_2
UNION ALL
SELECT * FROM df_1
STAP 4: Filter de tabel
Nu verwijderen we duplicate keyword data. Dat is wat het woordje DISTINCT doet voor de kolomnaam Keyword. Dan wordt op basis van deze kolom de duplicates gefilterd. Ook passen we de benamingen aan zodat het ons eigen lower case format gebruikt. De kolom current url wordt aangepast naar current_url zodat het op de SQL manier is geschreven.
SELECT
DISTINCT Keyword as keyword,
Volume as search_volume,
KD AS kd,
"Current URL" AS current_url
FROM df_3
STAP 5: Categoriseer
SELECT * FROM (
SELECT
keyword,
search_volume,
kd,
current_url,
CASE
WHEN keyword LIKE '%ajax%' then true
WHEN keyword LIKE '%barcel%' then true
WHEN keyword LIKE '%madrid%' then true
ELSE false
END AS irrelevant,
CASE
WHEN keyword LIKE '%adidas%' then 'adidas'
WHEN keyword LIKE '%addidas%' then 'adidas'
WHEN keyword LIKE '%nike%' then 'nike'
WHEN keyword LIKE '%zalando%' then 'zalando'
ELSE 'other'
END AS brand,
CASE
WHEN keyword LIKE '%heren%' then 'heren'
WHEN keyword LIKE '%dames%' then 'dames'
WHEN keyword LIKE '%vrouw%' then 'dames'
WHEN keyword LIKE '%meis%' then 'dames'
WHEN keyword LIKE '%girl%' then 'dames'
WHEN keyword LIKE '%jongen%' then 'heren'
WHEN keyword LIKE '%man%' then 'heren'
WHEN keyword LIKE '%men%' then 'heren'
WHEN keyword LIKE '%boy%' then 'heren'
WHEN keyword LIKE '%unis%' then 'unisex'
ELSE 'other'
END AS sexe,
CASE
WHEN keyword LIKE '%kleding%' then 'kleding'
ELSE 'other'
END AS general,
CASE
WHEN keyword LIKE '%lopen%' then 'hardlopen'
WHEN keyword LIKE '%loop%' then 'hardlopen'
WHEN keyword LIKE '%marathon%' then 'hardlopen'
WHEN keyword LIKE '%runn%' then 'hardlopen'
WHEN keyword LIKE '%bal%' then 'balsport'
WHEN keyword LIKE '%tennis%' then 'balsport'
WHEN keyword LIKE '%bench%' then 'fitness'
WHEN keyword LIKE '%gewicht%' then 'fitness'
WHEN keyword LIKE '%lung%' then 'fitness'
WHEN keyword LIKE '%pull%' then 'fitness'
WHEN keyword LIKE '%dips%' then 'fitness'
WHEN keyword LIKE '%burp%' then 'fitness'
WHEN keyword LIKE '%fitnes%' then 'fitness'
WHEN keyword LIKE '%golf%' then 'golf'
WHEN keyword LIKE '%sport%' then 'general'
WHEN keyword LIKE '%skate%' then 'skateboarding'
WHEN keyword LIKE '%board%' then 'skateboarding'
WHEN keyword LIKE '%zwem%' then 'zwemmen'
WHEN keyword LIKE '%swim%' then 'zwemmen'
WHEN keyword LIKE '%wiel%' then 'wielrennen'
ELSE 'other'
END AS sports,
CASE
WHEN keyword LIKE '%schoen%' then 'schoenen'
WHEN keyword LIKE '%shoe%' then 'schoenen'
WHEN keyword LIKE '%sanda%' then 'schoenen'
WHEN keyword LIKE '%star%' then 'schoenen'
WHEN keyword LIKE '%boot%' then 'schoenen'
WHEN keyword LIKE '%slip%' then 'schoenen'
WHEN keyword LIKE '%flip%' then 'schoenen'
WHEN keyword LIKE '%veter%' then 'schoenen'
WHEN keyword LIKE '%lace%' then 'schoenen'
WHEN keyword LIKE '%sneak%' then 'schoenen'
WHEN keyword LIKE '%schoen%' then 'schoenen'
WHEN keyword LIKE '%train%' then 'schoenen'
WHEN keyword LIKE '%yeezy%' then 'schoenen'
WHEN keyword LIKE '%broek%' then 'broeken'
WHEN keyword LIKE '%pant%' then 'broeken'
WHEN keyword LIKE '%korte%' then 'broeken'
WHEN keyword LIKE '%short%' then 'broeken'
WHEN keyword LIKE '%baby%' then 'baby'
WHEN keyword LIKE '%shirt%' then 'shirts'
WHEN keyword LIKE '%top%' then 'shirts'
WHEN keyword LIKE '%tenue%' then 'shirts'
WHEN keyword LIKE '%sleeve%' then 'shirts'
WHEN keyword LIKE '%polo%' then 'shirts'
WHEN keyword LIKE '%jers%' then 'shirts'
WHEN keyword LIKE '%jack%' then 'jassen'
WHEN keyword LIKE '%jas%' then 'jassen'
WHEN keyword LIKE '%park%' then 'jassen'
WHEN keyword LIKE '%jurk%' then 'jurk & rokken'
WHEN keyword LIKE '%rok%' then 'jurk & rokken'
WHEN keyword LIKE '%dres%' then 'jurk & rokken'
WHEN keyword LIKE '%hood%' then 'hoodies'
WHEN keyword LIKE '%sweat%' then 'hoodies'
WHEN keyword LIKE '%vest%' then 'hoodies'
WHEN keyword LIKE '%training%' then 'trainingspakken'
WHEN keyword LIKE '%suit%' then 'trainingspakken'
WHEN keyword LIKE '%pak%' then 'trainingspakken'
WHEN keyword LIKE '%body%' then 'bodywarmers'
WHEN keyword LIKE '%blazer%' then 'blazers'
WHEN keyword LIKE '%trui%' then 'truien'
WHEN keyword LIKE '%tas%' then 'tassen & rugzak'
WHEN keyword LIKE '%zak%' then 'tassen & rugzak'
WHEN keyword LIKE '%bag%' then 'tassen & rugzak'
WHEN keyword LIKE '%bh%' then 'ondergoed'
WHEN keyword LIKE '%beha%' then 'ondergoed'
WHEN keyword LIKE '%onder%' then 'ondergoed'
WHEN keyword LIKE '%box%' then 'ondergoed'
WHEN keyword LIKE '%crop%' then 'ondergoed'
WHEN keyword LIKE '%sok%' then 'ondergoed'
WHEN keyword LIKE '%sock%' then 'ondergoed'
WHEN keyword LIKE '%legg%' then 'ondergoed'
WHEN keyword LIKE '%bra%' then 'ondergoed'
WHEN keyword LIKE '%trun%' then 'ondergoed'
WHEN keyword LIKE '%bikini%' then 'beach'
WHEN keyword LIKE '%bad%' then 'beach'
WHEN keyword LIKE '%yoga%' then 'yoga'
WHEN keyword LIKE '%muts%' then 'mutsen & caps'
WHEN keyword LIKE '%cap%' then 'mutsen & caps'
WHEN keyword LIKE '%pet%' then 'mutsen & caps'
WHEN keyword LIKE '%hat%' then 'mutsen & caps'
WHEN keyword LIKE '%bril%' then 'brillen'
WHEN keyword LIKE '%kini%' then 'beach'
WHEN keyword LIKE '%zwembroek%' then 'beach'
ELSE 'other'
END as category,
CASE
WHEN keyword LIKE '%jordan%' then 'air'
WHEN keyword LIKE '%air%' then 'air'
WHEN keyword LIKE '%yeezy%' then 'yeezy'
WHEN keyword LIKE '%jeezy%' then 'yeezy'
ELSE 'other'
END as sub_category,
FROM df_4
-- where search_volume BETWEEN 0 AND 250
ORDER BY keyword ASC
)
WHERE category != 'other'
and irrelevant = false
-- sub_category = 'other' and
-- sex = 'other' and
-- brand = 'other' and
-- sports = 'other' and
-- general = 'other'
STAP 6: Visualiseer
Stap 7: Exporteer
SELECT * FROM df_5