# Complete Customer Journey Data Capture — Implementation Guide

## Overview
This guide maps the Léa & Thomas personas to your database schema and outlines what needs to be implemented to capture their full journey.

---

## Phase 1: Direct Data Capture (Your App & Backend Control)

### ✅ BEFORE THE FAIR

| Persona Data | DB Table | Status | Action |
|---|---|---|---|
| Discovery source (Instagram, school) | `discovery_sources` | ❌ NEW | Add `source_channel` parameter to registration form |
| Registration device type | `discovery_sources` | ❌ NEW | Capture `User-Agent` on registration |
| Registration time | `discovery_sources` | ❌ NEW | Measure form completion duration |
| Pre-fair app opens | `pre_fair_engagement` | ❌ NEW | Track app session events |
| Bookmarks & flags | `pre_fair_engagement` | ✓ EXISTS | Already tracked in `derived_features.bookmarks_count` |
| Pre-visit guide opened | `pre_fair_engagement` | ❌ NEW | Add in-app guide view events |
| Pre-visit questions | `pre_fair_engagement` | ❌ NEW | Add form to collect pre-arrival questions |
| Parcoursup stage inference | `pre_fair_engagement` | ❌ NEW | Optional: Let user declare status |
| Pre-fair email engagement | Extend `events` table | ⚠️ PARTIAL | Currently tracked but not contextual |

### ✅ DURING THE FAIR

| Persona Data | DB Table | Status | Action |
|---|---|---|---|
| Arrival timestamp & entry | `stand_visits` | ⚠️ PARTIAL | Add entry gate scan or check-in event |
| Stand dwell time (12 min vs 8 min) | `stand_visits.dwell_time_seconds` | ❌ NEW | Capture visit start/end timestamps |
| Conversation happened | `stand_visits.conversation_happened` | ❌ NEW | Exhibitor marks checkbox or NFC badge triggers |
| Conversation duration | `stand_visits.conversation_duration_seconds` | ❌ NEW | Manual entry by exhibitor or badge duration |
| Conversation topics | `stand_visits.conversation_topics` | ❌ NEW | Quick-select buttons at exhibitor stand |
| Photos of materials | `stand_visits.materials_photographed` | ❌ NEW | Integrate with phone camera or manual flag |
| Brochure taken vs photo | `stand_visits.brochure_taken` | ✓ EXISTS | Extend payload_json in events |
| Comparative shopping | `stand_visits.comparative_visit` | ❌ NEW | Track multiple visits to competing stands |
| Conference early departure | `conference_attendance.early_departure` | ❌ NEW | Capture session exit time vs duration |
| Peer conversations | `peer_interactions` | ❌ NEW | NFC badge detection or manual form |
| Fatigue/engagement drop | `engagement_state` | ❌ NEW | Detect via activity patterns |
| Primary vs secondary browsing | `stand_visits.primary_vs_secondary` | ❌ NEW | Tag via exhibitor or implicit from dwell |

### ✅ AFTER THE FAIR (Day 1-7)

| Persona Data | DB Table | Status | Action |
|---|---|---|---|
| App review of bookmarks | `post_fair_app_review` | ❌ NEW | Track app session + bookmark interactions |
| Stands missed (regret signal) | `post_fair_app_review.stands_not_visited_count` | ❌ NEW | Compare bookmarks to actual visits |
| Post-visit app engagement | `content_consumption` | ❌ NEW | Track masterclass views, programme pages |
| Shares to family/peers | `network_signals` | ❌ NEW | Track programmatic exports or deep links |
| Post-fair email opens/clicks | Extend `events` table | ✓ EXISTS | Already tracked |
| Return to platform | `churn_signals` | ✓ PARTIAL | Track via `site_pageviews_7d` |

---

## Phase 2: External Data Integration (Requires Partnerships)

### ⚠️ SCHOOL WEBSITES & EXTERNAL RETARGETING

These require **pixel tracking** or **API partnerships**:

| Data Point | Source | Method | Priority |
|---|---|---|---|
| School website visits after fair | External schools | Pixel tag on school domains | HIGH |
| Specific pages viewed | External schools | Pixel parameters | MEDIUM |
| Time spent on programme page | External schools | Pixel tracking + analytics | MEDIUM |
| Form interactions | External schools | Pixel event tracking | HIGH |

**Implementation:** Insert tracking pixel in follow-up emails or generate unique short URLs that redirect to school sites with UTM parameters.

### ⚠️ PARCOURSUP API INTEGRATION

| Data Point | Source | Method | Priority |
|---|---|---|---|
| Programmes on student's list | Parcoursup API | OAuth + API call after fair | HIGH |
| List changes post-fair | Parcoursup API | Periodic sync or event webhook | HIGH |
| Deadline proximity | Parcoursup API | Calendar event sync | MEDIUM |
| Confidence assessment | Parcoursup API | (Not available — must infer) | LOW |

**Implementation:** Request Parcoursup API access; sync on Day 3 post-fair and before deadline.

### ⚠️ DIRECT EMAIL SIGNALS

| Data Point | Source | Method | Priority |
|---|---|---|---|
| Email sent to exhibitor | CRM/Email headers | Forwarding rule or email notification | HIGH |
| Email content analysis | CRM/Gmail API | Automated extraction of intent | MEDIUM |

**Implementation:** Add automations in Gmail or CRM to capture inbound emails matching student profiles.

---

## Phase 3: Implementation Roadmap

### Week 1: Foundation
- [ ] Apply migration `004_journey_tracking.sql` to existing database
- [ ] Add `stand_visits` table and deprecate simple `events.stand_tap`
- [ ] Implement on-site dwell time tracking (capture visit start/end)
- [ ] Add exhibitor UI for "conversation happened" checkbox

### Week 2: Pre & Post Fair
- [ ] Extend registration form to capture discovery source
- [ ] Create pre-visit question form
- [ ] Implement post-fair app review tracking
- [ ] Add masterclass/content consumption logging

### Week 3: Intent Signals
- [ ] Integrate Parcoursup API (OAuth flow)
- [ ] Set up pixel tracking for external school websites
- [ ] Create "family export" feature with tracking
- [ ] Implement churn detection

### Week 4: Scoring & Recommendations
- [ ] Rebuild `lead_scores` based on new journey data
- [ ] Create `journey_completeness_score` combining all phases
- [ ] Build re-engagement email triggers based on churn signals
- [ ] Generate exhibitor-facing "high-intent lead" recommendations

---

## Example: Capturing Léa's Full Journey

```sql
-- 1. Discovery (Before Fair)
INSERT INTO discovery_sources (fair_id, student_id, source_channel, registration_device)
VALUES (1, 101, 'instagram', 'mobile');

-- 2. Pre-Fair Prep
INSERT INTO pre_fair_engagement (fair_id, student_id, app_opens_before_fair, bookmarks_count, conference_flags_count)
VALUES (1, 101, 3, 2, 1);

-- 3. Stand Visit 1: HR Master's (12 min, conversation)
INSERT INTO stand_visits
  (fair_id, student_id, stand_id, visit_start, visit_end, dwell_time_seconds,
   conversation_happened, conversation_duration_seconds, conversation_topics, primary_vs_secondary)
VALUES (1, 101, 5, '2026-04-28 14:30:00', '2026-04-28 14:42:00', 720, 1, 720,
  '["entry_requirements","application_timeline"]', 'primary');

-- 4. Stand Visit 2: UX Research (8 min, materials photographed)
INSERT INTO stand_visits
  (fair_id, student_id, stand_id, visit_start, visit_end, dwell_time_seconds,
   materials_photographed, comparative_visit, primary_vs_secondary)
VALUES (1, 101, 8, '2026-04-28 14:50:00', '2026-04-28 14:58:00', 480, 1, 1, 'primary');

-- 5. Conference attendance (attended but left early)
INSERT INTO conference_attendance
  (fair_id, student_id, conference_id, arrival_time, departure_time,
   attended_duration_seconds, session_duration_seconds, early_departure)
VALUES (1, 101, 3, '2026-04-28 15:20:00', '2026-04-28 15:35:00', 900, 1800, 1);

-- 6. Post-fair email sent to exhibitor (CRM integration)
INSERT INTO direct_contact_signals
  (fair_id, student_id, stand_id, contact_type, contact_time, initiated_by)
VALUES (1, 101, 5, 'email_to_exhibitor', '2026-04-28 19:15:00', 'student');

-- 7. Post-fair Parcoursup change
INSERT INTO parcoursup_tracking
  (fair_id, student_id, parcoursup_programmes_before_fair, parcoursup_programmes_after_fair,
   fair_influenced_decision)
VALUES (1, 101, 5, 6, 1);

-- 8. High-intent scoring
UPDATE journey_scores
SET journey_completeness_score = 0.92,
    stand_depth_score = 0.95,
    post_fair_action_score = 0.98
WHERE student_id = 101 AND fair_id = 1;
```

---

## Query: Identify High-Intent Leads Like Léa

```sql
SELECT
  s.id,
  s.fair_id,
  COALESCE(sp.full_name, 'Unknown') as name,
  COUNT(DISTINCT sv.stand_id) as stands_visited,
  SUM(sv.dwell_time_seconds) / 60.0 as total_dwell_minutes,
  SUM(CASE WHEN sv.conversation_happened = 1 THEN 1 ELSE 0 END) as conversations_had,
  SUM(CASE WHEN sv.materials_photographed = 1 THEN 1 ELSE 0 END) as materials_photographed,
  COUNT(DISTINCT dcs.id) as direct_contacts_sent,
  COALESCE(pt.fair_influenced_decision, 0) as parcoursup_influenced,
  js.journey_completeness_score
FROM students s
LEFT JOIN student_profiles sp ON s.id = sp.student_id
LEFT JOIN stand_visits sv ON s.id = sv.student_id
LEFT JOIN direct_contact_signals dcs ON s.id = dcs.student_id
LEFT JOIN parcoursup_tracking pt ON s.id = pt.student_id
LEFT JOIN journey_scores js ON s.id = js.student_id
WHERE sv.stand_id IS NOT NULL
GROUP BY s.id
HAVING
  SUM(sv.dwell_time_seconds) > 600  -- 10+ minutes on-site engagement
  AND SUM(CASE WHEN sv.conversation_happened = 1 THEN 1 ELSE 0 END) >= 1  -- Had conversations
  AND COUNT(DISTINCT dcs.id) > 0  -- Sent follow-up email
ORDER BY js.journey_completeness_score DESC;
```

---

## Next Steps

1. **Review** this schema with your backend team
2. **Prioritize** which tables to implement first (Weeks 1-2 recommended)
3. **Coordinate** with exhibitors on data capture (dwell time, conversations)
4. **Secure** Parcoursup API partnership (weeks 3-4)
5. **Rebuild** scoring engine to weight complete journey data over just registration

This transforms you from tracking **transactions** → **relationships** → **journeys**.