Where should a drinks company run promotions?
📖 Background
Your company owns a chain of stores across Russia that sell a variety of alcoholic drinks. The company recently ran a wine promotion in Saint Petersburg that was very successful. Due to the cost to the business, it isn’t possible to run the promotion in all regions. The marketing team would like to target 10 other regions that have similar buying habits to Saint Petersburg where they would expect the promotion to be similarly successful.
The data
The marketing team has sourced you with historical sales volumes per capita for several different drinks types.
- "year" - year (1998-2016)
- "region" - name of a federal subject of Russia. It could be oblast, republic, krai, autonomous okrug, federal city and a single autonomous oblast
- "wine" - sale of wine in litres by year per capita
- "beer" - sale of beer in litres by year per capita
- "vodka" - sale of vodka in litres by year per capita
- "champagne" - sale of champagne in litres by year per capita
- "brandy" - sale of brandy in litres by year per capita
2 hidden cells
List of all regions
Republic of Adygea, Altai Krai, Amur Oblast, Arkhangelsk Oblast, Astrakhan Oblast, Republic of Bashkortostan, Belgorod Oblast, Bryansk Oblast, Republic of Buryatia, Vladimir Oblast, Volgograd Oblast, Vologda Oblast, Voronezh Oblast, Republic of Dagestan, Jewish Autonomous Oblast, Zabaykalsky Krai, Ivanovo Oblast, Republic of Ingushetia, Irkutsk Oblast, Kabardino-Balkar Republic, Kaliningrad Oblast, Republic of Kalmykia, Kaluga Oblast, Kamchatka Krai, Karachay-Cherkess Republic, Republic of Karelia, Kemerovo Oblast, Kirov Oblast, Kostroma Oblast, Krasnodar Krai, Krasnoyarsk Krai, Republic of Crimea, Kurgan Oblast, Kursk Oblast, Leningrad Oblast, Lipetsk Oblast, Magadan Oblast, Mari El Republic, Republic of Mordovia, Moscow, Moscow Oblast, Murmansk Oblast, Nenets Autonomous Okrug, Nizhny Novgorod Oblast, Novgorod Oblast, Novosibirsk Oblast, Omsk Oblast, Orenburg Oblast, Oryol Oblast, Penza Oblast, Perm Krai, Primorsky Krai, Pskov Oblast, Altai Republic, Komi Republic, Tuva Republic, Rostov Oblast, Ryazan Oblast, Samara Oblast, Saint Petersburg, Saratov Oblast, Sakhalin Oblast, Sverdlovsk Oblast, Sevastopol, Republic of North Ossetia-Alania, Smolensk Oblast, Stavropol Krai, Tambov Oblast, Republic of Tatarstan, Tver Oblast, Tomsk Oblast, Tula Oblast, Tyumen Oblast, Udmurt Republic, Ulyanovsk Oblast, Khabarovsk Krai, Republic of Khakassia, Khanty–Mansi Autonomous Okrug – Yugra, Chelyabinsk Oblast, Chechen Republic, Chuvash Republic, Chukotka Autonomous Okrug, Sakha (Yakutia) Republic, Yamalo-Nenets Autonomous Okrug, Yaroslavl Oblast,
number of unique region 85
# 22 Republics
Republic of Adygea, Republic of Bashkortostan, Republic of Buryatia, Republic of Dagestan, Republic of Ingushetia, Kabardino-Balkar Republic, Republic of Kalmykia, Karachay-Cherkess Republic, Republic of Karelia, Republic of Crimea, Mari El Republic, Republic of Mordovia, Altai Republic, Komi Republic, Tuva Republic, Republic of North Ossetia-Alania, Republic of Tatarstan, Udmurt Republic, Republic of Khakassia, Chechen Republic, Chuvash Republic, Sakha (Yakutia) Republic,
# 9 Krai
Altai Krai, Zabaykalsky Krai, Kamchatka Krai, Krasnodar Krai, Krasnoyarsk Krai, Perm Krai, Primorsky Krai, Stavropol Krai, Khabarovsk Krai,
# 47 Oblast
Amur Oblast, Arkhangelsk Oblast, Astrakhan Oblast, Belgorod Oblast, Bryansk Oblast, Vladimir Oblast, Volgograd Oblast, Vologda Oblast, Voronezh Oblast, Jewish Autonomous Oblast, Ivanovo Oblast, Irkutsk Oblast, Kaliningrad Oblast, Kaluga Oblast, Kemerovo Oblast, Kirov Oblast, Kostroma Oblast, Kurgan Oblast, Kursk Oblast, Leningrad Oblast, Lipetsk Oblast, Magadan Oblast, Moscow Oblast, Murmansk Oblast, Nizhny Novgorod Oblast, Novgorod Oblast, Novosibirsk Oblast, Omsk Oblast, Orenburg Oblast, Oryol Oblast, Penza Oblast, Pskov Oblast, Rostov Oblast, Ryazan Oblast, Samara Oblast, Saratov Oblast, Sakhalin Oblast, Sverdlovsk Oblast, Smolensk Oblast, Tambov Oblast, Tver Oblast, Tomsk Oblast, Tula Oblast, Tyumen Oblast, Ulyanovsk Oblast, Chelyabinsk Oblast, Yaroslavl Oblast,
# 4 Okrug
Nenets Autonomous Okrug, Khanty–Mansi Autonomous Okrug – Yugra, Chukotka Autonomous Okrug, Yamalo-Nenets Autonomous Okrug,
# 3 Fedral city
Moscow, Sevastopol, Saint Petersburg
Separating data for Saint Petersburg
Creating dataframes of average year consumption for all regions
1 hidden cell
Historical view
Consumption change a lot over years 1998-2016
We have increased consumption of Wine, Beer, Champagne and Brandy over 1998-2016 years
but decreased consumption of Vodka.
Consumption trend was change for Wine, Beer , Champagne and Brandy over last few years, Vodka Trand not.
We look in our expertise in last 3 year where new trand have place.
Differences between Saint Petersburg - "SP" abd the rest of Russia regions.
Vodka consumption is almost the same.
Wine, Champagne, Brandy is greater.
Beer consumption is lower than average of whole Russia.
Selection of regions for promotion campaign.
In each row ther is average drink consumption from 1998-2016 years and data is sorted. It give us oportunity to choose nearby values.
In execution we get our 10 regions. We don't know wich type of drink is most important for promotion so we get two from every type of drink.
Saint Petersburgi is in red color and chosen regions are in green.