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.
Loss Prevention 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 loss prevention in a logistics (FMCG) setting.
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
- How many incidents are there per month?
- How many incidents are there by type?
- Which employees are involved in losses?
- Where are most incidents taking place?
- How many losses are there per day?
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
Loss Prevention Analysis Report
Introduction
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 report provides a comprehensive overview of the analyses and queries performed on the LossPrevention database.
The purpose of these queries was to understand, monitor, and visualize various metrics related to loss prevention activities, such as incidents of theft, shrinkage, security interventions, and procedural adherence.
Through structured data queries and subsequent visualizations, stakeholders can gain insights into patterns, trends, and areas of concern within the operational environment.
Methodology
The methodology for exploring the LossPrevention database involved several steps.
Initially, the database structure was inspected to identify key tables, columns, and data types. Columns such as Incident_ID, Date, Time, Employee_ID, Location, Incident_Type, Loss_Amount, and Resolution_Status were noted as critical for analysis.
Subsequent steps involved planning queries that could extract meaningful insights, aggregate metrics by time periods (daily, monthly, and yearly), and segment data by relevant dimensions such as incident type, location, or employee involvement.
Queries and Analysis Performed
Several key analyses were designed for the LossPrevention database.
The queries included:
1) Counting total incidents per month to understand temporal trends in loss prevention activities;
2) Categorizing incidents by type (e.g., theft, procedural violations, equipment damage) to identify patterns in losses;
3) Calculating total monetary loss per month and per location to highlight areas with significant financial impact;
4) Examining incidents by employee involvement to monitor procedural adherence and identify potential risk contributors;
5) Evaluating resolution status distributions to assess how quickly and effectively incidents were addressed.
These queries were designed to provide both quantitative metrics and categorical insights for management and operational teams.
Additional advanced queries were considered to enhance understanding of loss patterns.
These included joins across related tables (such as Employee or AccessLogs) to correlate incidents with access activity, shifts, or operational schedules.
Aggregations by week, quarter, and year were also performed to detect seasonal or cyclical trends.
We also used filtering and grouping techniques which were applied to focus on high-priority incidents, significant losses, or repeated patterns of concern.
Visualization and Dashboard Design
Following the extraction and aggregation of data, the results were prepared for visualization.
The aim was to present complex datasets in intuitive monthly charts, heatmaps, and trend graphs. Visualizations included line charts for monthly incident counts, bar charts comparing incident types, stacked charts for resolution statuses, and heatmaps highlighting locations or employees associated with high-frequency incidents.
These visual dashboards enabled stakeholders to quickly identify risk hotspots, track improvements over time, and make data-driven decisions for policy enforcement and preventative measures.
Insights and Practical Applications
The insights derived from the LossPrevention database queries are actionable for both operational and strategic purposes.
Identifying recurring incidents allows management to refine security protocols, enhance employee training, and allocate resources to high-risk locations.
Temporal patterns reveal periods of increased vulnerability, guiding scheduling and staffing decisions. By analyzing monetary impact and resolution efficiency, the findings support financial accountability and prioritization of corrective actions.
Through these analyses, organizations can adopt a proactive approach to loss prevention, reducing financial exposure and fostering a culture of accountability and vigilance.
Conclusion
In summary, the LossPrevention database provided a rich source of structured data to analyze security incidents, financial losses, and procedural adherence within an operational environment.
Through a combination of structured queries, aggregation techniques, and visual dashboards, comprehensive insights were obtained without querying live sensitive data for this report.
The methodologies and visualizations outlined provide a framework for continuous monitoring, trend analysis, and decision-making, ensuring that loss prevention efforts are data-driven, targeted, and effective in reducing both operational and financial risks.
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.
