From data modeling to clear reporting
A SQL analytics project focused on relational modeling, data validation and communicating findings through a dashboard.
My contribution
I designed the SQLite analytics model, wrote investigation and validation queries, and built an HTML/Chart.js reporting dashboard during the SQL & Database Architecture externship via Extern.
The context
Sales, cart, traffic and advertising records describe different events and reporting periods. Combining them without checking their grain and relationships can duplicate measures, hide unmatched records or produce a misleading indicator. My work connected database design, validation and reporting into a more explainable workflow.
How I worked
- Model the sources. Organized six commercial and marketing sources into relational SQLite tables. Kept sales records distinct from traffic and advertising measures, rather than assuming they could be joined at the same level.
- Investigate with SQL. Used joins, window functions and validation queries to examine table relationships, missing matches and the difference between individual records and unique sessions.
- Check the indicator. Examined whether numerator and denominator used compatible reporting windows and whether the data-quality exclusions were explicit. Treated an unmatched record as a question to investigate, not proof of no activity.
- Communicate the analysis. Built a reporting layer and an HTML/Chart.js dashboard to make the queries and their interpretation easier to follow.
Deliverables & takeaways
I produced an analytics database, SQL investigations and a reporting dashboard. The work demonstrates the full path from source modelling to an interpretable visual interface.
My main technical takeaway was to validate the structure of a query before trusting its output. Correcting a measure is an improvement in analytical reliability; it is not, by itself, evidence that the business performed better.
Scope & limitations
The analysis depends on source coverage, matching quality and consistent metric definitions. It does not establish a causal business impact.
Source records, exact business figures, identifiable internal findings and commercial recommendations are withheld. The submitted report and dashboard are not linked because they contain those details; the modelling and validation approach is described here.