Overview
Built for a fuel distributor's marketing team, this dashboard brought Google Business Profile performance across 2,000+ locations into one operational view. Territory managers get filtered views of their own locations, executives get the full picture, and performance leaderboards support review across territories. Source data refreshes through uploaded CSV exports.
The Challenge
Managing Google Business Profile presence across thousands of locations requires:
- Centralized visibility into location performance
- Territory-level insights for regional managers
- Comparative analysis to identify underperformers
- Secure access based on organizational role
Solution Architecture
Tech Stack
- Frontend: React with TypeScript
- Backend: Supabase (PostgreSQL + Auth)
- Hosting: Vercel
- Data Pipeline: CSV uploads + automated processing
Data Model
Code · text
Locations ──┬── Performance Metrics
│
├── Territory Assignments
│
└── Manager Relationships
Key Features
Interactive Filtering
Dynamic filtering across multiple dimensions:
- Geographic region
- Territory manager
- Performance tier
- Time period
Territory Management
Code · typescript
interface Territory {
id: string;
name: string;
manager: User;
locations: Location[];
metrics: AggregatedMetrics;
}Managers see only their assigned locations, while executives get full visibility.
Performance Leaderboards
Rankings based on key GMB metrics:
- Review response rate
- Average rating
- Profile completeness
- Photo engagement
CSV Upload Workflow
Export
GMB data pulled from Google
Upload
CSV added to the dashboard
Parse & Validate
Import checks run automatically
Dashboard Refresh
New metrics available after import
Role-Based Access Control
Permission Levels
| Role | Access |
|---|---|
| Admin | Full access, user management |
| Executive | All territories, all metrics |
| Regional Manager | Assigned territories only |
| Territory Manager | Own locations only |
Implementation
Code · typescript
// Row-level security in Supabase
create policy "Users see own territory"
on locations for select
using (
territory_id in (
select territory_id from user_territories
where user_id = auth.uid()
)
);Dashboard Views
Overview Dashboard
- Total locations with health indicators
- Aggregate metrics across portfolio
- Trend charts for key performance indicators
- Alert panel for locations needing attention
Location Detail
- Individual location metrics
- Review timeline
- Photo gallery performance
- Comparison to territory average
Manager View
- Territory performance summary
- Location-by-location breakdown
- Action items for improvement
- Export capabilities for reporting
Technical Highlights
Performance Optimization
- Materialized views for aggregate calculations
- Pagination for large datasets
- Debounced filter updates
- Optimistic UI updates
Security
- Row-level security at database level
- JWT-based authentication
- Secure credential storage
- Audit logging for data access
Results
The dashboard enables:
- 2000+ locations in one reporting dashboard
- 5 territory managers with personalized views
- Weekly performance reviews using dashboard data
- Review-response tracking across managed territories