Hi, I'm Alistair!
I Help You Understand Data
If you’re managing a business, you have lots of day to day data. I help you understand what the data tells us about your operation in a fun, visual format.
See some of the projects I've worked on:
No Cleaning or Presentations. Only Queries & Visualizations.
Supply Chain Delay Analysis
NB: All data and insights are purely hypothetical, the data is fake, there are no mentions of any Comapny or Organization, any similarities to any Organization are purely coincidental.
This project focused on looking at The Supply Chain from a management perspective.
To understand our process, let’s quickly walk through it step by step:
Sit with the client to understand the project and their expectations, get all the questions they want answered and in what format.
- Create a plan to gather all the required tools and information, creating a project dashboard, schedules and sharing all information with relevant parties.
- Gather only relevant data in different formats (CSV, Streams, XML, API, Databases, etc) – Update shared project file.
- Isolate and scan data files for security threats using relevant tools – update shared project file.
- Clean Data (this step we left out in this project, as an example only) – update shared project file.
- Store Data into local and cloud based repositories/Databases to query later – update shared project file.
- Make a Copy of data, clearly labelling each version and documenting each step – update shared project file.
- Write queries in the relevant language, ie Python, SQL or simple Excel commands. Document the queries – update shared project file.
- Create visualizations from results of queries – update shared project file.
- Compile all data insights and visualizations into the required presentation format (Slide Presentation, Video or step by step walkthrough) – update shared project file.
- Deliver presentation to client, getting feedback.
Go through data to find any other recommendations to make outside of their requests, offering more value for money.
Insights Requested From This Data
- Who are our top suppliers?
- Whats the ratio of deliveries across items?
- What are the top items bought?
- Whats the volume of deliveries by category?
- Whats the status of deliveries?
I used ChatGPT to help me formulate some data table headers.
I then used Mockaroo to create the fake data and downloaded it in CSV format.
I scanned the downloaded .csv file with my local Spybot softwre to check for any threats, there were none.
The data was saved into my local SQlite database.
I then used ChatGPT to write code and inserted the resulting code into my Jupyter Notebook, running on Annaconda.
The resulting code can be found below, in the embedded PDF.
I also used ChatGPT to write a brief Report about the Analysis, the Report is below.
Let's Help You Understand Data
01.
Understand
You explain your business so we understand what you want to now
02.
Plan
We create a plan to collect the necessary information to do the analysis.
03.
Investigate
We investigate and uncover the sources of data that will be needed to do the analysis
04.
Collect Data
Data is collected from various sources and in multiple formats as necessary
05.
Scanned
Collected data is scanned for viruses and other threats before being moved
06.
Clean, Format
Data is cleaned and refotmatted so that it can be safely stored in a database
07.
Query
We use multiple tools from SQL to excel to query the data and uncover answers
08.
Presentation
A complete presentation is delivered answering questions
Supply Chain Delay Analysis Report
NB: All data and insights are purely hypothetical, the data is fake, there are no mentions of any Comapny or Organization, any similarities to any Organization are purely coincidental.
Introduction
This report provides a detailed description of the analyses and queries conducted on the Deliveries database. The database contains records of deliveries made by various suppliers, detailing order dates, expected and actual delivery dates, item names, quantities, and the delivery status.
The primary objective of this analysis was to explore delivery patterns, identify trends, and provide insights that can be used to optimize delivery operations and improve on-time performance.
The following sections outline the methodology, key queries, data transformation processes, and visualizations employed during the analysis.
Database Overview
The Deliveries database consists of a single table named ‘Deliveries’ with the following columns:
– Delivery_ID (INTEGER): A unique identifier for each delivery.
– Supplier (TEXT): The name of the supplier responsible for the delivery.
– Order_Date (TEXT): The date the order was placed.
– Expected_Delivery (TEXT): The date the delivery was expected to arrive.
– Actual_Delivery (TEXT): The date the delivery actually arrived.
– Item_Name (TEXT): The name of the item being delivered.
– Quantity (INTEGER): The quantity of items in the delivery.
– Status (TEXT): The delivery status, indicating whether it was completed, delayed, or failed.
These columns form the foundation for performing both descriptive and visual analyses to understand delivery patterns and assess operational efficiency.
Queries and Analysis
The analyses conducted on the Deliveries database focused on several key aspects, each corresponding to operational metrics that are important for logistics and supply chain management.
The primary queries and their intended outcomes are described below.
1. Total Deliveries per Month
One of the first analyses performed involved aggregating the total number of deliveries made each month. This query helps identify trends in delivery volume over time, including peak periods and slow months.
By grouping the data by month and counting the number of deliveries, it is possible to understand the overall workload and resource allocation required to manage deliveries efficiently.
2. Deliveries per Supplier
Another important query involved analyzing deliveries per supplier. By grouping deliveries based on the supplier column, we were able to identify which suppliers are responsible for the largest volumes of deliveries and which suppliers might be contributing to delays or failures.
This analysis can inform strategic decisions regarding supplier selection and contract management.
3. Monthly On-Time vs. Delayed Deliveries
A critical metric for operational performance is the on-time delivery rate. We created queries to calculate the number of deliveries that arrived on time versus those that were delayed, broken down by month.
This allows managers to pinpoint trends in delivery reliability, assess the effectiveness of current logistics practices, and implement corrective measures where needed.
4. Average Delay Duration
To further understand delays, we computed the difference between the Actual_Delivery and Expected_Delivery dates for each delivery.
Aggregating these delays monthly provides insights into the average duration of delays and highlights periods where delays were more severe. This metric is critical for improving customer satisfaction and planning for contingency resources.
5. Deliveries by Item
We also analyzed deliveries based on the Item_Name column. This involved counting the number of deliveries for each item per month.
This type of analysis is helpful for inventory management, ensuring that frequently ordered items are prioritized and adequately stocked, and for identifying items that may frequently cause delays.
6. Quantity Distribution
A distribution analysis of the Quantity column provides insights into the size of deliveries being handled on a monthly basis.
By examining total quantities delivered per month, or average quantity per delivery, management can make informed decisions about logistics capacity, vehicle allocation, and workforce planning.
7. Visualizations
All the above queries were complemented with monthly charts and visualizations to provide a clearer understanding of trends and patterns.
Key visualizations included line charts for monthly delivery volumes, bar charts for supplier contributions, and heatmaps to show delay patterns.
Additionally, we created on-time delivery percentage charts to monitor monthly performance and highlight periods requiring attention.
Visual representations enable stakeholders to quickly grasp insights that are difficult to detect in raw tabular data.
Conclusion
The comprehensive analyses conducted on the Deliveries database offer actionable insights into operational efficiency and performance.
By examining delivery volumes, supplier contributions, on-time rates, item trends, and quantities, management can make data-driven decisions to optimize logistics processes, improve customer satisfaction, and reduce operational bottlenecks.
Visualizations complement these analyses by highlighting patterns over time and enabling quick identification of problem areas.
This report serves as a detailed reference for understanding the types of queries and analyses that can be applied to a deliveries database for improved operational management.
About Me
Your Data, Your Business!
Every second, your business generates data.
To most people, the data is a pile of numbers and letters. To the trained analyst though, the data tells a story.
I help uncover the stories and present it in a fun visual format, that’s easy for anyone to understand.
