Neo4j笛卡尔积查询优化及用户旅程分析连续事件查询构建

Great approach modeling individual events as nodes to get around Neo4j's limitation on indexing relationship properties—this will make your user journey queries much more efficient. Let's build out that query to find three consecutive events of specific types for your exit analysis.

Quick Assumptions About Your Data Model #

I'm filling in a few gaps based on your snippet to make the query work:

• Each EventNode has a userId (to track which user performed the event) and a timestamp (to order events chronologically).
• The relationship between EventNode and EventType is :HAS_TYPE (since your snippet uses <--, meaning (event)-[:HAS_TYPE]->(eventType)).

Complete Query to Find Consecutive Events #

MATCH (e1:EventNode)-[:HAS_TYPE]->(et1:EventType {eventName: 'viewStart-home'}),
      (e2:EventNode)-[:HAS_TYPE]->(et2:EventType {eventName: 'your-second-event-name'}),
      (e3:EventNode)-[:HAS_TYPE]->(et3:EventType {eventName: 'your-third-event-name'})
WHERE 
  // All events belong to the same user
  e1.userId = e2.userId AND e2.userId = e3.userId
  // Events are in the correct chronological order
  AND e1.timestamp < e2.timestamp AND e2.timestamp < e3.timestamp
  // Ensure no other events happened between e1 and e2 (truly consecutive)
  AND NOT EXISTS {
    MATCH (mid:EventNode)
    WHERE mid.userId = e1.userId
    AND mid.timestamp > e1.timestamp AND mid.timestamp < e2.timestamp
  }
  // Ensure no other events happened between e2 and e3
  AND NOT EXISTS {
    MATCH (mid:EventNode)
    WHERE mid.userId = e2.userId
    AND mid.timestamp > e2.timestamp AND mid.timestamp < e3.timestamp
  }

RETURN 
  e1.userId AS user_id,
  et1.eventName AS first_event, e1.timestamp AS first_event_time,
  et2.eventName AS second_event, e2.timestamp AS second_event_time,
  et3.eventName AS third_event, e3.timestamp AS third_event_time
ORDER BY user_id, first_event_time

Customization Tips #

1. Replace Placeholder Event Names: Swap 'your-second-event-name' and 'your-third-event-name' with the actual event types you're targeting.
2. Adjust "Consecutive" Definition: If you don't need strictly immediate events (just events in sequence, even with others in between), remove the two NOT EXISTS blocks.
3. Optimize with Indexes: Speed up the query by adding indexes on the properties we're filtering and sorting on:

   CREATE INDEX idx_event_user FOR (n:EventNode) ON (n.userId);
   CREATE INDEX idx_event_timestamp FOR (n:EventNode) ON (n.timestamp);
   

4. Identify Exit Points: To find users who exited after the third event, add this clause to the WHERE block:

   AND NOT EXISTS {
     MATCH (postExit:EventNode)
     WHERE postExit.userId = e3.userId
     AND postExit.timestamp > e3.timestamp
   }
   

This query will give you a clear view of which users followed the exact three-event sequence you're analyzing, which is perfect for identifying drop-off points in your app.

内容的提问来源于stack exchange，提问作者talhaocakci