- Home
- Skills
- Data & Databases
- safari connect analytics
safari connect analytics
Clean messy Kenyan transport booking data and run PostgreSQL analytics for revenue, routes, and drivers.
$9
Works with the AI Agents you already use
safari connect analytics
Example session with this skill installed
I have the raw booking table ready. Please clean the data and show me which routes are generating the most revenue this month.
- Read your context and instructions
- Compiled the safari connect analytics
I have processed the raw data from safari_connect_dirty into safari_connect_clean_v3.
The cleaning process removed 1 duplicate booking (BK0005), standardized 14 phone numbers to the 07XXXXXXXX format, and normalized mixed-case seat classes. All dates were successfully parsed.
Route Revenue Analysis (Completed Trips)
| Route Code | Route | Total Revenue (KES) |
|---|---|---|
| RT001 | Nairobi to Mombasa | 450,500.00 |
| RT004 | Nairobi to Kisumu | 312,250.00 |
| RT007 | Nakuru to Eldoret | 185,400.00 |
| RT002 | Mombasa to Nairobi | 178,900.00 |
| RT009 | Nairobi to Nanyuki | 95,000.00 |
Key Takeaways:
- The Nairobi to Mombasa route remains the primary revenue driver, contributing over 35% of total earnings.
- Long-haul routes (Nairobi-Kisumu) show higher total revenue despite fewer daily departures compared to regional matatu routes.
Next steps
- Would you like to see the revenue per seat to determine the most profitable route by occupancy?
- Should I generate a SQL view for these results to use in your Looker Studio dashboard?
- Do you want to check if "No Show" bookings should be added to the lost revenue report?
Connects securely to your tools. The creator never sees your data.
About this skill
Raw transport booking data is often riddled with inconsistent date formats, malformed phone numbers, and duplicate entries. This skill cleans messy bus and matatu records for Safari Connect and executes PostgreSQL analytics to track revenue, route performance, and driver ratings.
What it does
- Data sanitization transforms inconsistent dates, standardizes Kenyan phone numbers, and normalizes categorical data like gender and seat class.
- Revenue analysis calculates total earnings, growth trends, and revenue per seat while excluding cancellations.
- Operational reporting identifies busiest departure times, popular routes, and peak days of the week.
- Performance tracking correlates driver ratings with passenger satisfaction levels.
- Dashboard prep generates SQL views formatted for direct integration with Looker Studio.
How it works
- Deduplication executes a
DISTINCT ONquery to remove duplicate booking IDs from the raw table. - Standardization applies regex and case-mapping logic to create a clean production-ready table.
- Verification compares row counts and checks for null values in critical fields like departure dates.
- Analysis runs specific analytical queries based on your requirements for revenue, drivers, or operations.
Frameworks & tools
This skill targets PostgreSQL and is designed for transport operators using M-Pesa, cash, and card payments.
Why this beats prompting it yourself
Writing the regex for multiple non-standard date formats and phone number normalization in PostgreSQL is time-consuming. This skill provides a pre-tested transformation pipeline and a suite of intercity transport queries that account for business-specific logic like seat occupancy and cancellation impact.
Use cases
- Auditing monthly revenue growth across specific bus and matatu routes.
- Identifying high-risk routes with elevated cancellation or no-show rates.
- Preparing clean datasets for visualization in Google Looker Studio.
- Evaluating driver performance based on passenger trip ratings.
Known limitations
Date conversion requires user confirmation if formats are ambiguous. The skill assumes a PostgreSQL environment and requires an existing safari_connect_dirty table.
Find more skills like this on the Claude Skills Marketplace, the Codex Skills Marketplace and the Gemini Skills Marketplace, or browse Claude Skills for Data & Databases.
Requires PostgreSQL. Date cleaning for ambiguous formats like DD/MM vs MM/DD needs user confirmation. Assumes specific column names exist in the raw table.
How to use
- 1
Connect your agent to the Agensi MCP
Copy the prompt and paste it into Claude, ChatGPT, Cursor or any agent that supports MCP. It sets up the connection for you. Manual setup
- 2
Sign in to Agensi
Your agent shows a sign-in link. Sign in and click Allow.
- 3
Ask for what you need
Your agent loads this skill and uses it on your task. It always asks before buying a skill.
Reviews
No reviews yet
Be one of the first to try it. Every listed skill goes through the trust checks below.
Security scanned
Checked by our 8-point scan before listing
Fresh listing
Recently published to Agensi
Money-back guarantee
Doesn't do what its page promises? You get your money back.
Trust & safety
Security scan
Security scan passed 1 day ago
Permissions
No special permissions declared or detected