🐾 Clinipet - HealthTail Analysis Project

Role: Business Intelligence Analyst

Client: HealthTail Veterinary Hospital

Organization: Clinipet – IT & Analytics Solutions for Veterinary Clinics and Pet Hotels

Timeline: Early 2026

Tools: Big Query & Looker Studio

📘 Project Overview

HealthTail, one of the largest veterinary hospitals in the city, partnered with Clinipet to modernize their manual medication auditing processes and gain deeper insights into disease and diagnosis trends among their patients.

As a BI Analyst at Clinipet, I led the data integration, transformation, and visualization efforts to deliver actionable insights via an interactive dashboard. This project combines ETL pipelines in BigQuery and interactive reporting in Looker Studio to support data-driven decisions in veterinary care.

🎯 Project Goals

HealthTail faced two major challenges:

1. Audit Medication Purchases and Expenses

• Automate the tracking of annual medication procurement and usage.
• Provide insights into medication costs and usage efficiency.

2. Monitor Diagnoses and Disease Trends

• Identify common diagnoses segmented by pet type and breed.

Use trends to inform staffing, medication planning, and inventory management.

✅ Deliverables

✔️ Upload and structur source .csv files in BigQuery

✔️ Clean and transform raw data to correct errors and inconsistencies

✔️ Create unified, analysis-ready datasets

✔️ Build an interactive Looker Studio dashboard displaying key metrics

✔️ Present findings in a live client presentation

📊 Dataset Description

healthtail_reg_cards.csv – Patient Registration Data

Column Name	Type	Description
patient_id	String	Unique pet ID
owner_id	Integer	Unique owner ID
owner_name	String	Owner's full name
pet_type	String	Species (e.g., Dog, Cat)
breed	String	Breed of the pet
patient_name	String	Name of the pet
gender	String	Gender of the pet
patient_age	Integer	Age in years
date_registration	Date	Registration date
owner_phone	String	Contact number (incl. country code)

⚠️ Note: Contains manual entry errors, missing values, and inconsistent formats.

visits.csv – Medical Visit Logs

Column Name	Type	Description
visit_id	String	Unique ID per visit
patient_id	String	Pet associated with the visit
visit_datetime	DateTime	Timestamp of visit
doctor	String	Attending veterinarian
diagnosis	String	Diagnosis given
med_prescribed	String	Medication prescribed
med_dosage	Float	Dosage (as a share of a full package)
med_cost	Float	Cost of prescribed medication

---

invoices.csv – Medication Purchase Records

Column Name	Type	Description
month_invoice	Date	Month and year of invoice
invoice_id	String	Unique invoice identifier
supplier	String	Supplier name
med_name	String	Name of medication purchased
packs	Float	Number of packs purchased
price	Float	Price per pack
total_price	Float	Total transaction cost (packs × price)

---

⚙️ Tools & Technologies

• Google BigQuery – Data warehousing, SQL analysis, ETL

• Looker Studio – Interactive dashboard creation

🛠️ Step 1 – Creating Clean and Aggregated Data

1. 🔧 Data Cleaning

To ensure the data was analysis-ready, the following cleaning steps were applied:

• Standardized inconsistent phone numbers and names
• Replaced null or missing breed values with "Unknown"
• Removed titles like Mr, Mrs from owner_name for consistency
• Standardized patient_name to lowercase and capitalized format

---

2. 📦 Aggregated Table: healthtail_med_audit

HealthTail requested a dedicated monthly medication tracking table that includes both:

• Purchases (from the invoices table) - stock in
• Usage (from the visits table) - stock out

A query was written to aggregate this information and output a new table called healthtail_med_audit, saved in the integrations folder.

📌 Below is a preview of the first 10 rows of the healthtail_med_audit table.

View the SQL queries in Step 1

---

📊 Step 2 – Answering Research Questions with SQL

The following business questions were provided by HealthTail. For each, I wrote SQL queries in BigQuery to extract insights.

1️⃣ What medication did we spend the most money on in total?

2️⃣ What medication had the highest monthly total_value spent on patients? At what month?

3️⃣ What month was the highest in packs of meds spent in the vet clinic?

4️⃣ What’s the average monthly amount of packs spent on the med that generated the most revenue?

View the SQL queries in Step 2

📈 Step 3 – Creating Interactive Report in Looker Studio

HealthTail management required a powerful, user-friendly dashboard to:

• Monitor common diagnoses
• Track disease prevalence by breed
• Analyze medication spending trends

The dashboard was designed to be interactive and support:

• 📌 Drill-down functionality
• 📊 A variety of visualization types (bar charts, line graphs, heatmaps, etc.)

---

📌 HealthTail Key Concerns Addressed in Dashboard

Each concern is paired with an appropriate chart in the Looker Studio dashboard:

1. What are the most common diagnoses and diseases overall?
   ➤ Breakdowns provided by pet type (e.g., dog vs. cat vs. hamster)

2. Which diseases are most prevalent among specific breeds?
   ➤ Enables tailored care and better inventory planning

3. Which diseases incur the highest spending, and how frequently do they appear in the data?
   ➤ Highlights costly recurring conditions

4. Are certain pet types (e.g., dogs, cats, hamsters) more susceptible to specific diseases or associated with higher treatment costs?
   ➤ Supports budgeting and veterinary resourcing

5. How does age influence the prevalence of certain diseases?
   ➤ Age-group trends help guide preventive care

6. How does spending on medications change over time?
   ➤ Tracks inventory and supplier patterns

7. Is there an increase in diagnoses of certain types over time?
   ➤ Flags potential seasonal trends or outbreaks

🧠 Lessons Learned

The importance of clean, structured source data in building meaningful dashboards

How to design scalable schemas for real-world analytical use cases

Leveraging Looker Studio for clear communication with non-technical stakeholders

🎥 Demo & Dashboard

• 📽️ Video Presentation: Watch here
• 📊 Interactive Dashboard: View Looker Studio Report