docs/journey_tracking_guide.md

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

-- 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

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.