Alexander’s Data Analyst Portfolio
My fascination with the data world began in university whilst studying sociology, criminology, and statistics. I graduated from Royal Holloway, University of London in the summer of 2021. At that point, I have already managed to get a bit of practical experience in the data world by doing an internship at a digital marketing agency in London. In my last year of university, I worked with the biggest sociological agency in Sofia, dabbling my feet in the social and political side of data analytics.
Following my graduation, landed a job in a renowned non-for-profit that championed on behalf of the UK hospitality industry in parliament. My role there was Data and CRM Executive which gave me the opportunity to work with unique data tools such as Power BI and started learning my first language SQL. Here is where I found the aspect of storytelling in data analysis and immediately grew fond of it. As a non-for-profit business – member engagement and retention were crucial, so my job was to find weaknesses in our email campaigns, website activity and industry event engagement through the power of data analytics.
As my analytical and technical skills evolved, decided that I wanted to work for an IT vendor in Sofia, which will expose me to more and more challenging and interesting data projects. Started working with bigger data sets whilst creating engaging and interactive dashboards and reports. This was the time when working with and consulting clients was a major part of my role as a data analyst. Worked along with major stakeholders and technical experts from the client’s side to create powerful analytical tools such as apps, dataflows, and interactive dashboards.
Currently focusing on expanding my technical skills set within the realms of AI Data Analytics. Prompt Engineering is one of the technical skills I’m currently focusing on. Mastering Azure Databricks and overall manipulation and integration into different environments and systems.
Tools and Platform
Milestones and Achievements
Worked with over 15 Clients
+5 Years Experience Working with Data
50 Projects Finished
Data Visualization
Interactions Report
Build for: Pharma client
Data Source: Azure Databricks
Goals: The goal of this report is to showcase total vs targeted interactions as well as to display the differences in channels, segments, therapeutic areas, etc. It has plenty of helpful slicers which the user can utilize to retrieve powerful data.
Internal Regional Sales
Build for: Pharma client
Data Source: Azure Databricks
Goal: This was one of the visuals used in the Internal Regional Sales dashboard where I was tasked to determine whether the sales amount and quantity were on target. Ultimately, this led to regional managers restructuring their sales approach to better facilitate customer needs.
National Sales
Build for: Pharma client
Data Source: Azure Databricks
Goal: The goal of this report was mainly to compare client vs competitor sales and growth overtime. It also showcased some regional financial developments. This tool enabled sales representatives to track which of their products was falling behind the competition.
Internal Interactions Report
Build for: Pharma client
Data Source: Azure Databricks
Goal: The goal of this report was to sketch out the basic level of interactions per KPI in the most simplistic way possible.
Recent Project Details
National Interactions and Coverage
In this project I was tasked with exploring the client’s data regarding managers’ interactions -frequency, targets and potential reach. The data was provided within a cloud based database and before creating the Power BI report I began with creating 3 views in SQL Databricks. Since we have had information from a 3rd party vendor referring to customer information, regional keys as well as unified product names were used to merge the external data and my client’s records.
First a SQL view was created to define the regions:
CREATE VIEW nat_sales_external_region_data (PROD_FAMILY_NAME, PROD_PACK_NAME, PRODUCT, SALES_QTY, SALES_CHF, SALES_CHANNEL, TERRITORY, DATE_KEY, SOURCE) AS
SELECT CASE
WHEN PROD_PACK_NAME = ‘Product1’ THEN ‘Product1A’
ELSE PROD_PACK_NAME
END AS PROD_FAMILY_NAME,
PROD_PACK_NAME,
CASE
WHEN PROD_PACK_NAME = ‘Product1’ THEN ‘Product1A’
ELSE PROD_PACK_NAME
END AS PRODUCT,
CASE
WHEN PROD_PACK_NAME = ‘Product2’ THEN SUM(STD_UNIT) /WHEN PROD_PACK_NAME = ‘Product1’ THEN SUM(SALES_QTY)/4/
ELSE SUM(SALES_QTY)
END AS SALES_QTY,
SUM(SALES_CHF_AMOUNT) AS SALES_CHF,
SALES_CHANNEL,
CASE
WHEN PROD_PACK_NAME = ‘Product3’
AND REGION_KEY IN (‘B3001’,
‘B3002’,
‘B3004’) THEN ‘R-West’
WHEN PROD_PACK_NAME = ‘Product3’
AND REGION_KEY = ‘B3008’ THEN ‘R-Central’
WHEN PROD_PACK_NAME = ‘Product3’
AND REGION_KEY IN (‘B3003’,
‘B3005’,
‘B3006’,
‘B3007’) THEN ‘R-East’
WHEN PROD_PACK_NAME = “Product4”
AND REGION_KEY IN (‘B3001’,
‘B3002’) THEN ‘R-West’
WHEN PROD_PACK_NAME = “Product4”
AND REGION_KEY IN (‘B3003’,
‘B3004’,
‘B3005’) THEN ‘R-Central’
WHEN PROD_PACK_NAME = “Product4”
AND REGION_KEY IN (‘B3007’,
‘B3006’) THEN ‘R-East’
WHEN PROD_PACK_NAME = ‘Product4’
AND REGION_KEY = ‘B3008’ THEN ‘R-South’
WHEN PROD_PACK_NAME = ‘Product1’
AND REGION_KEY IN (‘B3003’,
‘B3005’,
‘B3006’,
‘B3007’) THEN ‘R-East’
WHEN PROD_PACK_NAME = ‘Product1’
AND REGION_KEY IN (‘B3001’,
‘B3002’) THEN ‘R-West’
WHEN PROD_PACK_NAME = ‘Product1’
AND REGION_KEY IN (‘B3003’,
‘B3004’,
‘B3005’) THEN ‘R-Central’
WHEN PROD_PACK_NAME = ‘Product1’
AND REGION_KEY IN (‘B3007’,
‘B3006’) THEN ‘R-East’
WHEN PROD_PACK_NAME = ‘Product1’
AND REGION_KEY = ‘B3008’ THEN ‘R-South’
END AS TERRITORY,
DATE_KEY,
‘national_external_sales’ AS SOURCE
FROM database_set_1.national_external_sales
WHERE PROD_PACK_NAME in (‘Product3’,
‘Product2’,
‘Product1’)
AND PROD_NAME_EXTENSION IS NULL
AND DATE_KEY > 201900
GROUP BY CASE
WHEN PROD_PACK_NAME = ‘Product5’ THEN ‘Product5’
ELSE PROD_PACK_NAME
END,
PROD_PACK_NAME,
CASE
WHEN PROD_PACK_NAME = ‘Product5’ THEN ‘Product5’
ELSE PROD_PACK_NAME
END,
SALES_CHANNEL,
CASE
WHEN PROD_PACK_NAME = ‘Product6’
AND REGION_KEY IN (‘B3001’,
‘B3002’,
‘B3004’) THEN ‘R-West’
WHEN PROD_PACK_NAME = ‘Product6’
AND REGION_KEY = ‘B3008’ THEN ‘R-South’
WHEN PROD_PACK_NAME = ‘Product6’
AND REGION_KEY IN (‘B3003’,
‘B3005’,
‘B3006’,
‘B3007’) THEN ‘R-East’
WHEN PROD_PACK_NAME = “Product7”
AND REGION_KEY IN (‘B3001’,
‘B3002’) THEN ‘R-West’
WHEN PROD_PACK_NAME = “Product7”
AND REGION_KEY IN (‘B3003’,
‘B3004’,
‘B3005’) THEN ‘R-Central’
WHEN PROD_PACK_NAME = “Product7”
AND REGION_KEY IN (‘B3007’,
‘B3006’) THEN ‘R-East’
WHEN PROD_PACK_NAME = ‘Product7’
AND REGION_KEY = ‘B3008’ THEN ‘R-SouthEast’
WHEN PROD_PACK_NAME = ‘Product5’
AND REGION_KEY IN (‘B3003’,
‘B3005’,
‘B3006’,
‘B3007’) THEN ‘R-East’
WHEN PROD_PACK_NAME = ‘Product5’
AND REGION_KEY IN (‘B3001’,
‘B3002’) THEN ‘R-West’
WHEN PROD_PACK_NAME = ‘Product5’
AND REGION_KEY IN (‘B3003’,
‘B3004’,
‘B3005’) THEN ‘R-Central’
WHEN PROD_PACK_NAME = ‘Product5’
AND REGION_KEY IN (‘B3007’,
‘B3006’) THEN ‘R-East’
WHEN PROD_PACK_NAME = ‘Product5’
AND REGION_KEY = ‘B3008’ THEN ‘R-North’
END
After linking every product to their respective region within the external 3rd party vendor data, another SQL view was created in order to UNION the external data with the client’s own sales table.
CREATE VIEW all_sales_data_view (PROD_FAMILY_NAME, PROD_PACK_NAME, PRODUCT, SALES_QTY, SALES_CHF, CUST_NAME, CUST_GROUP, TERRITORY, DATE_KEY, SOURCE) TBLPROPERTIES (‘transient_lastDdlTime’ = ‘1707487649’) AS
(SELECT exs.PROD_FAMILY_NAME,
exs.PROD_PACK_NAME,
exs.PRODUCT,
exs.SALES_QTY,
exs.SALES_CHF,
exs.CUST_NAME,
exs.SAB_CUST_GROUP AS CUST_GROUP,
exs.TERRITORY,
exs.DATE_KEY,
exs.source
FROM internal_actuals_sales exs
UNION SELECT ims.PROD_FAMILY_NAME,
ims.PROD_PACK_NAME,
ims.PRODUCT,
ims.SALES_QTY,
ims.SALES_CHF,
ims.SALES_CHANNEL AS CUST_NAME,
ims.SALES_CHANNEL AS CUST_GROUP,
ims.TERRITORY,
ims.DATE_KEY,
ims.source
FROM nat_sales_external_region_data ims
UNION SELECT market.MARKET AS PROD_FAMILY_NAME,
market.MARKET AS PROD_PACK_NAME,
market.MARKET AS PRODUCT,
market.SALES_QTY,
market.SALES_CHF,
market.SALES_CHANNEL AS CUST_NAME,
market.SALES_CHANNEL AS CUST_GROUP,
market.TERRITORY,
market.DATE_KEY,
market.source
FROM market_sales market)
Now that we have united both the external and the internal datasets visualization can begin in Power BI.
