# Dashboard Content Analysis
## What Can Be Shown According to Design vs. Available Data

**Last Updated**: August 19, 2026  
**Status**: Analysis of current implementation and available modules

---

## 1. Designed Dashboard Elements

According to [public/webcrawlers-dashboard-assets/dashboard.php](./public/webcrawlers-dashboard-assets/dashboard.php), the dashboard should display:

### KPI Tiles (Top Row)
1. **Organic clicks** - Primary metric from Google Search Console
2. **Impressions** - Google Search Console metric  
3. **Conversions** - From GA4 or CRM integration
4. **Attributed revenue** - From CRM connection

### Charts & Visualizations
5. **Organic performance chart** - 14-day line chart showing clicks + impressions trend
   - Includes date range selector (28 days)
   - Location filter

### Pipeline Health Card
6. **Open opportunities** - Number of actionable SEO items (with high-impact badge)
7. **Pending approvals** - Count of items awaiting user approval
8. **Crawl coverage** - Percentage of website pages crawled (with meter visualization)
9. **Connector health** - Status of integrations (X of 4 healthy)
   - GSC (Google Search Console)
   - GA4 (Google Analytics 4)
   - GBP (Google Business Profile)
   - WebCrawlers Pixel
10. **Next scheduled job** - When the next crawl is scheduled

### Activity & Metadata
11. **Recent activity log** - Timeline of actions with tone indicators
12. **Data coverage note** - Explanation about "No data" vs. zero values

---

## 2. Currently Available Data Sources

### A. Database Tables with Data

#### `wc_websites` (Core)
- Domain, status (active/inactive)
- Pixel verification status
- Automation enabled flag
- Business details (JSON)
- **Available for Dashboard**: Number of websites, pixel install status

#### `wc_audits` (Crawling)
- Website crawl results
- Pages crawled count
- Crawl time
- Issue counts (critical, high, medium, low)
- Crawl progress
- Scheduled job info
- **Available for Dashboard**: Crawl coverage %, issue counts, crawl schedule

#### `wc_audit_pages` (Page Data)
- Per-page metrics (title, H1, meta description, word count)
- Page grading
- Issue data
- **Available for Dashboard**: Page health details (if drilling down)

#### `wc_audit_issues` (Technical Issues)
- Issue type, severity, count
- Pages affected
- **Available for Dashboard**: Issue breakdown by severity

#### `wc_pixel_views` (User Engagement)
- Page views with scroll depth and time-on-page
- Device type, referrer
- **Available for Dashboard**: Could show engagement metrics, traffic patterns

#### `wc_pixel_events` (Conversions)
- Form submissions, phone clicks, CTA clicks
- Event type and data
- **Available for Dashboard**: Conversion metrics, event tracking

#### `wc_pixel_vitals` (Performance)
- Core Web Vitals: LCP, INP, CLS, FCP, TTFB
- **Available for Dashboard**: Performance metrics

#### `wc_website_integrations` (External Services)
- Connection status for GSC, GA4, GBP
- Property IDs
- **Available for Dashboard**: Connector health status

#### `wc_rank_projects` & `wc_tracked_keywords` (Ranking Data)
- Keyword rankings by position
- Rank changes
- Location and device tracking
- **Available for Dashboard**: Could show top-performing keywords, rank changes

#### `wc_audit_issues_json` (Issue Details)
- Structured issue data from DataForSEO
- **Available for Dashboard**: Issue categories and recommendations

---

## 3. Current Implementation Status

### Dashboard Controller & API

**File**: `app/Controllers/Api/V1/DashboardController.php`

Currently returns STUB DATA with placeholders:

```php
'kpis' => [
    'organic_clicks'      => null,        // TODO: GSC data
    'impressions'         => null,        // TODO: GSC data
    'conversions'         => null,        // TODO: CRM/GA4 integration
    'attributed_revenue'  => null,        // TODO: CRM integration
],
'pipeline' => [
    'open_opportunities'  => 0,           // TODO: Calculate from issues
    'pending_approvals'   => 0,           // TODO: Draft count
    'crawl_coverage'      => null,        // TODO: Query audits
    'connector_health'    => {...},       // Hardcoded connection statuses
    'next_scheduled_job'  => null,        // TODO: Query jobs table
],
'recent_activity'         => [],          // TODO: Query audit log
```

**Dashboard View**: `app/Views/dashboard/index.php`

- Renders empty KPI tiles that get filled via JavaScript
- Shows empty states with connect instructions
- Has placeholders for all pipeline metrics
- Activity log shows "No recent activity yet"

---

## 4. What CAN Be Shown Now (With Existing Data)

### ✅ Currently Accessible Information

| Metric | Source | Status | Implementation |
|--------|--------|--------|-----------------|
| **Websites count** | `wc_websites` | ✅ Ready | Simple query |
| **Pixel install status** | `wc_websites.pixel_verified_at` | ✅ Ready | Query per website |
| **Crawler health** | `wc_audits` | ✅ Ready | Latest audit status |
| **Pages crawled** | `wc_audits.pages_crawled` | ✅ Ready | From latest audit |
| **Crawl coverage %** | `wc_audits` calculation | ✅ Ready | `pages_crawled / total_pages * 100` |
| **Issue counts** | `wc_audits.critical/high/medium/low_count` | ✅ Ready | From latest audit |
| **Issue breakdown** | `wc_audit_issues` | ✅ Ready | Group by severity |
| **Connector status** | `wc_website_integrations` | ✅ Ready | Join on type = ('gsc', 'ga4', 'gbp') |
| **Last crawl time** | `wc_audits.finished_at` | ✅ Ready | Latest audit timestamp |
| **Next scheduled crawl** | `wc_audits.next_scheduled_at` (or from job queue) | ⚠️ Partial | Needs job scheduling table |
| **Page metrics** | `wc_audit_pages` | ✅ Ready | Average grade, word count, images |
| **Engagement metrics** | `wc_pixel_views` | ✅ Ready | Avg scroll depth, avg time on page |
| **Conversion events** | `wc_pixel_events` | ✅ Ready | Count by event_type |
| **Performance vitals** | `wc_pixel_vitals` | ✅ Ready | LCP, INP, CLS averages |
| **Top keywords** | `wc_tracked_keywords` + `wc_keyword_rankings` | ✅ Ready | Top 5 rankings by position |
| **Rank changes** | `wc_keyword_rankings` (comparison) | ✅ Ready | Position deltas |

---

## 5. What Needs External Integration (Not Available)

### ❌ Requires Third-Party APIs

| Metric | Required Integration | Status |
|--------|---------------------|--------|
| **Organic clicks (GSC)** | Google Search Console API | ❌ Not implemented |
| **Impressions (GSC)** | Google Search Console API | ❌ Not implemented |
| **Conversions (GA4)** | Google Analytics 4 API | ❌ Not implemented |
| **Revenue attribution** | CRM API (HubSpot/Salesforce) | ❌ Not implemented |
| **Click-to-call tracking** | Phone system integration | ❌ Not implemented |

---

## 6. Recommended Dashboard Build-Out (Priority Order)

### Phase 1: Site & Crawl Metrics (Can Do Today)
✅ Show crawl health card with:
- Total pages crawled from latest audit
- Issue counts (critical/high/medium/low) from `wc_audits`
- Last crawl time
- Pages crawled vs. total pages (coverage %)
- Link to drill into each issue type

✅ Show engagement metrics with:
- Avg time on page from `wc_pixel_views`
- Avg scroll depth from `wc_pixel_views`
- Total page views (count from `wc_pixel_views`)
- Device breakdown

✅ Show connector health:
- GSC: connected/disconnected status
- GA4: connected/disconnected status  
- GBP: connected/disconnected status
- Pixel: verified/not verified status

### Phase 2: Ranking Data (Can Do Today)
✅ Show rank tracking summary:
- Total keywords tracked
- Avg ranking position across all keywords
- Keywords in top 10, top 20, top 50
- Top 5 keywords by position
- Keywords with recent position gains/losses

### Phase 3: External Integrations (Coming Soon)
- Implement GSC sync to populate organic_clicks & impressions
- Implement GA4 sync for conversion data
- Add CRM webhook for revenue attribution
- Build approval workflow for pending items

### Phase 4: Advanced Analytics (Future)
- Trending analysis over time
- Correlation between changes and performance
- Opportunity detection algorithms
- Automated recommendations

---

## 7. Implementation Recommendations

### Quick Wins (1-2 hours each)

**1. Crawl Health Widget**
```php
// Query latest audit for each website
$audit = $this->audits
    ->where('website_id', $websiteId)
    ->where('status', 'finished')
    ->orderBy('finished_at', 'DESC')
    ->first();

// Display: pages_crawled, critical_count, high_count, finished_at
```

**2. Engagement Metrics**
```php
// Query pixel views for last 28 days
$views = $this->db->table('wc_pixel_views')
    ->where('website_id', $websiteId)
    ->where('created_at >=', date('Y-m-d', strtotime('-28 days')))
    ->selectAvg('scroll_pct')
    ->selectAvg('duration_sec')
    ->groupBy('website_id')
    ->get();

// Display: avg scroll %, avg duration, unique pages, total views
```

**3. Connector Status**
```php
// Query integrations
$integrations = $this->integrations
    ->whereIn('type', ['gsc', 'ga4', 'gbp'])
    ->where('website_id', $websiteId)
    ->findAll();

// Display: 3 of 4 healthy (with names)
// Display: Pixel verified status from wc_websites.pixel_verified_at
```

**4. Rank Tracking Summary**
```php
// Count keywords by position
$keywords = $this->db->table('wc_keyword_rankings')
    ->select('CASE 
        WHEN position <= 10 THEN "top10"
        WHEN position <= 20 THEN "top20"
        WHEN position <= 50 THEN "top50"
        ELSE "other"
    END as range, COUNT(*) as count')
    ->where('rank_project_id', $projectId)
    ->where('checked_at >=', date('Y-m-d', strtotime('-1 day')))
    ->groupBy('range')
    ->get();

// Display: X keywords in top 10, Y in top 20, Z in top 50
```

---

## 8. Database Queries Needed

### For Dashboard KPIs Section

```sql
-- Latest crawl status
SELECT website_id, status, pages_crawled, total_pages, 
       critical_count, high_count, finished_at
FROM wc_audits
WHERE website_id = ? AND status = 'finished'
ORDER BY finished_at DESC
LIMIT 1;

-- Connector health
SELECT type, status FROM wc_website_integrations
WHERE website_id = ?;

-- Pixel verification
SELECT pixel_verified_at FROM wc_websites WHERE id = ?;

-- Recent activity (approvals, deployments, issues)
SELECT type, action, description, created_at FROM audit_log
WHERE website_id = ? AND user_id = ?
ORDER BY created_at DESC
LIMIT 5;

-- Engagement metrics (last 28 days)
SELECT 
    COUNT(*) as total_views,
    AVG(scroll_pct) as avg_scroll,
    AVG(duration_sec) as avg_duration
FROM wc_pixel_views
WHERE website_id = ? 
  AND created_at >= DATE_SUB(NOW(), INTERVAL 28 DAY);

-- Top keywords (by position)
SELECT keyword, position, trend
FROM wc_keyword_rankings kr
JOIN wc_tracked_keywords tk ON kr.keyword_id = tk.id
WHERE tk.rank_project_id = ?
ORDER BY position ASC
LIMIT 5;
```

---

## 9. Summary

### What's Ready to Display
- ✅ Crawl health & coverage metrics
- ✅ Issue breakdown (critical/high/medium/low)
- ✅ Connector status (3 of 4 healthy)
- ✅ Pixel verification status
- ✅ Engagement metrics (time-on-page, scroll depth)
- ✅ Keyword ranking summary
- ✅ Page metrics (avg grade, word counts)

### What Needs GSC/GA4 Connection
- ⏳ Organic clicks
- ⏳ Impressions
- ⏳ Conversions
- ⏳ Revenue attribution

### Next Steps
1. Update `DashboardController.php` to query actual data instead of stubs
2. Build the crawl health, engagement, and connector widgets
3. Add ranking summary widget
4. Create queries for recent activity feed
5. Integrate GSC & GA4 APIs (separate project)