Data Engineer
& Analyst
Strong
in Media

Three separate Excel files — one per store — containing daily transaction records for January through March 2025. Each file held columns for date, product category, SKU, units sold, unit price, and store ID. Around 4,800 rows total across all three stores before cleaning.

• Used Power Query to append the three store files into a single unified table, applying a Store Name column as a filter dimension.
• Standardised date formats (mixed DD/MM/YYYY and YYYY-MM-DD across files) and converted unit price from text to decimal.
• Removed 11 duplicate rows caused by system re-exports, and filtered out 3 rows with null SKUs.
• Created a Calendar table in Power BI linked via date relationship to enable proper time intelligence functions.

• Wrote DAX measures for Total Revenue, Revenue MoM %, Units Sold YTD, and a Running Total using CALCULATE + DATESYTD.
• Built a Top N product ranking using RANKX across the product table, filtered dynamically via a slicer.
• Created store-level KPI cards showing revenue vs. a fixed Q1 target, with conditional formatting to flag underperformance.

• Clustered bar chart (revenue by category) — allows quick relative comparison; sorted descending so the top performers are immediately visible without scanning.
• Line chart (monthly trend by store) — shows trajectory rather than a single snapshot, so a manager can see if a dip in February recovered or continued.
• Matrix with conditional formatting — for SKU-level drill-down, colour-scaled by margin so the highest-value products stand out without needing to read every number.
• Bookmark navigation buttons — replaced a tab interface the client already understood from Excel, reducing the learning curve for non-BI users.

Source: AdventureWorks 2019 — Microsoft's sample enterprise database, running on SQL Server. The question we were handed: "What is the relationship between the size of stores, number of employees, and revenue?" Data was spread across multiple tables: Sales.Store (store metadata), HumanResources.Employee (headcount), and Sales.SalesOrderHeader (transaction revenue). No single flat file existed — everything had to be assembled via SQL joins before any analysis could begin.

• Queried Sales.Store joined to Sales.SalesPerson and Sales.SalesOrderHeader to pull per-store annual revenue aggregated with SUM(TotalDue).
• Joined HumanResources.Employee to count staff per store using COUNT(BusinessEntityID) grouped by territory.
• Used Sales.Store.Demographics (XML column) to extract square footage — required CROSS APPLY and value() XQuery parsing, since the field is stored as XML in AdventureWorks.
• Final query returned ~700 store-level rows with: store ID, sq footage, employee count, and total yearly revenue.

• Loaded SQL output into pandas and binned employees into 4 bands (2–13, 14–30, 31–50, 51–100) using pd.cut() for categorical colouring on the scatter.
• Built a bubble chart (matplotlib): X = store size (sq ft), Y = total yearly revenue, bubble size = employee count. This allowed all three dimensions to be read simultaneously — avoiding the need for a 3D plot that would be harder to interpret.
• Identified the "High Cost / Low Return" quadrant: stores with large footprints and high headcount but below-median revenue — a clear signal of operational inefficiency.
• Calculated Pearson r for size vs revenue: weak positive. For employee count vs revenue: stronger signal — confirming that staffing density is the more meaningful lever.

• Smaller stores with fewer employees often outperformed larger, heavily-staffed locations — disproving the assumption that bigger always means more revenue.
• Mid-sized stores clustered at lower revenue despite adequate infrastructure — suggesting the problem isn't capacity but conversion efficiency.
• Large low-revenue stores represent the highest operational risk: high overhead, low return, difficult to turn around without structural change.

This project is a good example of why multi-table SQL extraction matters before any visualisation. A single flat CSV would have hidden the XML demographic fields and required manual joins that are better handled in SSMS. The bubble chart format was chosen specifically because it encodes three continuous variables without cluttering — something a bar chart or simple scatter would fail at. The "High Cost / Low Return" framing came from asking a business question first, then building the visual to answer it rather than the other way around.

The Olist Brazilian E-Commerce dataset (Kaggle, 2016–2018): 100,000 orders across multiple marketplaces, stored in 9 separate CSV files in Portuguese — reviews, orders, order items, products, sellers, customers, payments, geolocation, and category translations. No single flat file. No English. No clean joins. The schema above shows how the tables connect: olist_orders_dataset is the central fact table, linked outward to reviews, payments, items, customers, and geolocation via order_id, customer_id, and zip_code_prefix.

• Schema mapping first: studied the entity-relationship diagram to plan which tables were needed for each question before writing a single line of code — avoiding unnecessary joins later.
• 7 individual cleaning passes: each raw CSV was cleaned separately: sellers, products, order items, payments, customer data, geolocation, and review text — outputting a cleaned/ folder of validated files before any merging.
• Translation pipeline: review comments were in Portuguese. Translated olist_order_reviews_dataset → reviews_translated_Cleaned.xlsx to make NLP viable for an English-language analysis. Also mapped product_category_name_translation.csv to get English category names throughout.
• Python NLP preprocessing (phrases.py): extracted recurring phrases from review text — stripping noise, normalising, and surfacing key terms that drove positive and negative sentiment. Output fed directly into the Power BI word cloud.
• Feature engineering: created freight_ratio = freight_value / price — a derived column representing delivery cost as a proportion of product value, which turned out to be the most predictive variable for review score.
• Final merge: joined all cleaned tables on order_id using pd.merge() in a left-join chain (orders → reviews → items → products → sellers), producing a flat working dataframe of ~87,000 complete records with review text, scores, freight data, and English category labels.

• Sentiment classification: bucketed review scores — Positive (4–5), Neutral (3), Negative (1–2) — as a calculated column in Power BI. Result: ~80% positive, avg score 4.09 (above the 4.0 benchmark). Temporal trend confirmed improving satisfaction over 2016–2018.
• Category performance breakdown: stacked bar chart by category, sortable by volume. Top performers: CDs/DVDs, fashion, books. Worst: Security & Services — reflecting customer concerns around order reliability and transaction processes.
• Word cloud (negative reviews only): stripped Portuguese stopwords, ran through phrases.py, surfaced: product damage, wrong items, long waits. Built as a custom visual in Power BI for stakeholder readability — non-analysts grasp recurring complaints immediately.
• Freight impact analysis: binned freight_ratio into low / medium / high thirds. Computed Pearson and Spearman correlations (Spearman used as complement because review scores are ordinal). Finding: freight ratio correlated more strongly with low scores than absolute freight cost — a £5 delivery on an £8 item is more damaging than £15 on a £120 item.
• Delivery window: order fulfilment ranged from 0 to 210 days. Extended delivery was the second most cited driver of negative reviews after freight cost ratio.

• Built a multi-page report in Power BI Service: overview KPI page, sentiment drill-through by category, freight analysis page, and word cloud page.
• DAX measures: AVERAGEX(Reviews, Reviews[review_score]) for dynamic avg score; custom measures for positive/negative review counts filterable by category and date.
• Drill-through page: clicking any category bar opens a filtered view of raw translated review comments for that segment — designed for operations teams to read actual customer language, not just numbers.

The insight that freight ratio matters more than absolute freight cost only became visible because the feature was engineered before analysis — it didn't exist in the raw data. This project demonstrated that data preparation isn't just cleaning: it's designing the variables that make the real pattern findable. The schema diagram wasn't decoration — it was the map that made the multi-table join possible without guessing.

Built as a personal project to learn Java backend development and real-world API integration. The bot was designed to be a full AI-powered dating assistant inside Telegram — no app download, no account, just a conversation. Note: the GPT-4 integration is currently inactive due to paid API limits expiring, but the full architecture, state machine, and all six feature modes are complete and functional.

• /profile — asks 5 questions about the user, sends answers to GPT-4 with a custom prompt, returns a polished Tinder bio
• /opener — collects info about the match, generates a personalised first message that doesn't sound like a template
• /message — pastes in a conversation history, bot suggests what to say next or how to ask them out
• /date — roleplay chat with AI-simulated celebrity personas (Ariana Grande, Margot Robbie, Ryan Gosling…) for conversation practice
• /gpt — open-ended ChatGPT chat directly in Telegram
• /start — menu navigation with inline keyboard buttons

• Language: Java 17, built with Maven (pom.xml manages Telegram Bots API + HTTP client dependencies)
• Multi-session handling: MultiSessionTelegramBot extends TelegramLongPollingBot and uses ThreadLocal<Update> to isolate each user's incoming message — multiple users handled concurrently without state collision
• State machine: each user session tracks a DialogMode enum (MAIN / PROFILE / OPENER / MESSAGE / DATE / GPT) — the onUpdateEventReceived() dispatcher routes logic based on current mode, not just the message text
• GPT integration: ChatGPTService sends HTTP requests to the OpenAI API, manages conversation history, and injects per-mode system prompts loaded from /resources/prompts/*.txt files
• Prompt engineering: each mode has a hand-written prompt file defining persona, tone, and output format — e.g. the opener prompt instructs GPT to never sound like a template and to use the match's info naturally
• Content: all bot messages, images, and prompts loaded from /resources/ at runtime — easy to swap without touching Java code

At Peppercorn Solutions I build data pipelines for a US-based fintech platform serving financial advisors and asset managers — the platform's name is under NDA. The core challenge: 4 custodians and fund data vendors each expose their data through different APIs, authentication schemes, and payload structures, and all of it needs to land in a unified internal data model reliably, on schedule, and without silent failures.

• ETL pipelines for Custodial Data, Public and Private Funds — extracting raw financial data from vendor APIs, transforming it into the platform's core schema, and loading it into the database on a scheduled cadence.
• Each pipeline handles a different vendor's data contract: different authentication flows, different response shapes (nested JSON, paginated endpoints, flat CSVs), and different update frequencies. My job is to make them all behave consistently downstream.

• Built authentication and authorisation flows for all 4 vendors — OAuth2 token exchange, secure credential storage, token refresh logic, and retry handling on 401/429 responses.
• Each vendor's auth pattern is encapsulated so pipeline logic stays clean and auth concerns stay separate from data handling.

• Shared logic lives in a core/ module — base HTTP client wrappers, common data validators, error handlers, and response normalisers.
• New vendor integrations extend these rather than rewriting them, keeping the codebase consistent as the number of integrations grows.

• Pipelines orchestrated with Dagster — assets and jobs define the dependency graph, sensors trigger runs on schedule or on external events, and the Dagster UI gives full visibility into what ran, when, and what failed.
• Deployed on Railway, with environment config managed via Railway's variable system so credentials never touch the codebase.