User Journey Before Booking

Date: 2026-01-20 Analysis Period: January - December 2025 Data Sources: sadb_daily_renting_listing_bookings, sadb_user_listing_event_logs

---

Executive Summary

This analysis examines user behavior patterns before making a booking, including the number of views, engagement events, and booking attempts required before conversion.

Key Metrics

Metric	Value
Average booking attempts per user	1.7
First-attempt success rate	6.1%
Peak success rate (4th-5th attempt)	20.6%
Average events before booking	61.7
Average listings explored	37.1

Top Insights

Finding	Impact
First attempts convert poorly (6.1%)	Major opportunity to improve onboarding
Same-listing retries convert 4.7x better	Enable easy re-booking flows
50+ events typical before booking	Users need extensive research support
1-4 week return window optimal	Target re-engagement campaigns here

---

1. Booking Attempts Analysis

1.1 Attempts Per User Distribution

Booking Attempts	Users	% of Users
1 attempt	9,303	71.2%
2 attempts	2,071	15.8%
3 attempts	759	5.8%
4 attempts	370	2.8%
5 attempts	209	1.6%
6+ attempts	358	2.7%

Summary Statistics:

Metric	Value
Average attempts per user	1.7
Median attempts	1
75th percentile	2
90th percentile	3
Maximum attempts	149

Key Finding: The vast majority (71.2%) of users make only one booking attempt. However, persistent users who make multiple attempts show significantly better conversion rates.

SELECT
  max_attempts_per_user,
  count(*) AS users,
  round(count(*) * 100.0 / sum(count(*)) OVER (), 1) AS pct
FROM
  (
    SELECT
      user_id,
      count(*) AS max_attempts_per_user
    FROM
      sadb_daily_renting_listing_bookings FINAL
    WHERE
      _peerdb_is_deleted = 0
      AND user_id > 0
      AND toYear(createdAt) = 2025
    GROUP BY
      user_id
  )
GROUP BY
  max_attempts_per_user
ORDER BY
  max_attempts_per_user
LIMIT
  15

1.2 Conversion by Attempt Number

Attempt #	Total Bookings	Completed	Success Rate	Avg Value (SAR)
1st attempt	13,070	797	6.1%	1,831
2nd attempt	3,767	588	15.6%	2,048
3rd attempt	1,696	334	19.7%	2,432
4th-5th attempt	1,504	310	20.6%	2,108
6th-10th attempt	1,099	203	18.5%	2,359
11+ attempt	916	95	10.4%	1,381

Key Findings:

1. First-time bookers have very low success (6.1%)
2. Success rate improves dramatically with subsequent attempts
3. Peak conversion occurs at 4th-5th attempt (20.6%) - 3.4x improvement over first attempts
4. After 10+ attempts, success drops to 10.4% (possibly frustrated users or problematic accounts)
5. Booking value tends to increase with experience (peaks at 3rd attempt: SAR 2,432)

WITH
  user_bookings_numbered AS (
    SELECT
      user_id,
      id AS booking_id,
      createdAt,
      status,
      total_amount,
      row_number() OVER (
        PARTITION BY
          user_id
        ORDER BY
          createdAt
      ) AS attempt_num
    FROM
      sadb_daily_renting_listing_bookings FINAL
    WHERE
      _peerdb_is_deleted = 0
      AND user_id > 0
      AND toYear(createdAt) = 2025
  )
SELECT
  CASE
    WHEN attempt_num = 1 THEN '1st attempt'
    WHEN attempt_num = 2 THEN '2nd attempt'
    WHEN attempt_num = 3 THEN '3rd attempt'
    WHEN attempt_num BETWEEN 4 AND 5 THEN '4th-5th attempt'
    WHEN attempt_num BETWEEN 6 AND 10 THEN '6th-10th attempt'
    ELSE '11+ attempt'
  END AS attempt_bucket,
  count(*) AS total_bookings,
  countIf (status = 'COMPLETED') AS completed,
  round(
    countIf (status IN ('COMPLETED', 'CONFIRMED')) * 100.0 / count(*),
    1
  ) AS success_rate,
  round(
    avgIf (
      total_amount,
      status IN ('COMPLETED', 'CONFIRMED')
    ),
    0
  ) AS avg_value_successful
FROM
  user_bookings_numbered
GROUP BY
  attempt_bucket
ORDER BY
  CASE attempt_bucket
    WHEN '1st attempt' THEN 1
    WHEN '2nd attempt' THEN 2
    WHEN '3rd attempt' THEN 3
    WHEN '4th-5th attempt' THEN 4
    WHEN '6th-10th attempt' THEN 5
    ELSE 6
  END

---

2. Same-Listing Retry Behavior

2.1 Conversion by Attempt on Same Listing

Attempt on Same Listing	Bookings	Successful	Success Rate
1st attempt	17,993	1,262	7.0%
2nd attempt	2,504	596	23.8%
3rd attempt	770	219	28.4%
4th attempt	330	108	32.7%
5th attempt	158	50	31.6%

Key Finding: Users who retry the same listing have dramatically higher success rates:

• 2nd attempt: 23.8% (3.4x first attempt)
• 3rd attempt: 28.4% (4.1x first attempt)
• 4th attempt: 32.7% (4.7x first attempt)

This suggests that users who return to the same listing are highly motivated and should be facilitated with easy re-booking flows.

WITH
  booking_details AS (
    SELECT
      user_id,
      listing_id,
      createdAt,
      status,
      row_number() OVER (
        PARTITION BY
          user_id,
          listing_id
        ORDER BY
          createdAt
      ) AS attempt_on_listing
    FROM
      sadb_daily_renting_listing_bookings FINAL
    WHERE
      _peerdb_is_deleted = 0
      AND user_id > 0
      AND toYear(createdAt) = 2025
  )
SELECT
  attempt_on_listing,
  count(*) AS bookings,
  countIf (status IN ('COMPLETED', 'CONFIRMED')) AS successful,
  round(
    countIf (status IN ('COMPLETED', 'CONFIRMED')) * 100.0 / count(*),
    1
  ) AS success_rate
FROM
  booking_details
WHERE
  attempt_on_listing <= 5
GROUP BY
  attempt_on_listing
ORDER BY
  attempt_on_listing

2.2 Unique Listings Attempted Per User

Unique Listings Attempted	Users	%
1 listing	10,527	80.5%
2 listings	1,650	12.6%
3 listings	461	3.5%
4 listings	197	1.5%
5+ listings	235	1.8%

Key Finding: 80.5% of users only attempt to book one listing. The remaining 19.5% who explore multiple listings represent more experienced or persistent bookers.

---

3. Time Between Attempts

3.1 Time Gap Analysis

Time Gap	Bookings	%	Success Rate
First booking	13,356	60.6%	6.2%
Same day	5,515	25.0%	16.5%
1-6 days	1,837	8.3%	17.3%
1-4 weeks	853	3.9%	20.5%
1-3 months	491	2.2%	19.3%

Key Findings:

1. 60.6% of bookings are first-time attempts (lowest success rate: 6.2%)
2. Same-day retries are common (25%) and convert well (16.5%)
3. 1-4 week return window has optimal conversion (20.5%)
4. Even 1-3 month returns maintain good success (19.3%)

Implication: Re-engagement campaigns should target the 1-4 week window for maximum effectiveness.

WITH
  user_bookings AS (
    SELECT
      user_id,
      createdAt,
      status,
      lagInFrame(createdAt) OVER (
        PARTITION BY
          user_id
        ORDER BY
          createdAt
      ) AS prev_booking_time
    FROM
      sadb_daily_renting_listing_bookings FINAL
    WHERE
      _peerdb_is_deleted = 0
      AND user_id > 0
      AND toYear(createdAt) = 2025
  )
SELECT
  CASE
    WHEN days_since_last IS NULL THEN 'First booking'
    WHEN days_since_last < 1 THEN 'Same day'
    WHEN days_since_last < 7 THEN '1-6 days'
    WHEN days_since_last < 30 THEN '1-4 weeks'
    WHEN days_since_last < 90 THEN '1-3 months'
    ELSE '3+ months'
  END AS time_gap,
  count(*) AS bookings,
  round(count(*) * 100.0 / sum(count(*)) OVER (), 1) AS pct,
  round(
    countIf (status IN ('COMPLETED', 'CONFIRMED')) * 100.0 / count(*),
    1
  ) AS success_rate
FROM
  (
    SELECT
      status,
      dateDiff('day', prev_booking_time, createdAt) AS days_since_last
    FROM
      user_bookings
  )
GROUP BY
  time_gap
ORDER BY
  CASE time_gap
    WHEN 'First booking' THEN 1
    WHEN 'Same day' THEN 2
    WHEN '1-6 days' THEN 3
    WHEN '1-4 weeks' THEN 4
    WHEN '1-3 months' THEN 5
    ELSE 6
  END

---

4. Engagement Events Before Booking

4.1 Event Volume Summary

For users with tracked engagement events before their first booking:

Metric	Value
Users with tracked events	1,291
Average events before booking	61.7
Median events	19
Average unique listings interacted	37.1
Average events on final booked listing	2.5

Key Finding: Users engage extensively before booking - averaging 61.7 events across 37 different listings before making their first booking attempt.

4.2 Event Distribution Before First Booking

Events Before Booking	Users	%
1 event	61	4.7%
2-5 events	197	15.3%
6-10 events	161	12.5%
11-20 events	246	19.1%
21-50 events	257	19.9%
50+ events	369	28.6%

Key Finding: The most common pattern is 50+ engagement events (28.6% of users) before booking, indicating significant research and comparison behavior.

WITH
  user_first_booking AS (
    SELECT
      user_id,
      min(createdAt) AS first_booking_time
    FROM
      sadb_daily_renting_listing_bookings FINAL
    WHERE
      _peerdb_is_deleted = 0
      AND user_id > 0
      AND toYear(createdAt) = 2025
    GROUP BY
      user_id
  ),
  user_events AS (
    SELECT
      ufb.user_id,
      count(*) AS events_before_booking
    FROM
      user_first_booking ufb
      JOIN sadb_user_listing_event_logs e FINAL ON ufb.user_id = e.user_id
      AND e.createdAt < ufb.first_booking_time
      AND e._peerdb_is_deleted = 0
    GROUP BY
      ufb.user_id
  )
SELECT
  CASE
    WHEN events_before_booking = 0 THEN '0 events'
    WHEN events_before_booking = 1 THEN '1 event'
    WHEN events_before_booking BETWEEN 2 AND 5 THEN '2-5 events'
    WHEN events_before_booking BETWEEN 6 AND 10 THEN '6-10 events'
    WHEN events_before_booking BETWEEN 11 AND 20 THEN '11-20 events'
    WHEN events_before_booking BETWEEN 21 AND 50 THEN '21-50 events'
    ELSE '50+ events'
  END AS event_bucket,
  count(*) AS users,
  round(count(*) * 100.0 / sum(count(*)) OVER (), 1) AS pct
FROM
  user_events
GROUP BY
  event_bucket
ORDER BY
  CASE event_bucket
    WHEN '0 events' THEN 1
    WHEN '1 event' THEN 2
    WHEN '2-5 events' THEN 3
    WHEN '6-10 events' THEN 4
    WHEN '11-20 events' THEN 5
    WHEN '21-50 events' THEN 6
    ELSE 7
  END

4.3 Event Types Before Booking

Event Type	Count	Unique Users	Avg per User
View	53,796	1,252	43.0
30-sec session	13,914	1,054	13.2
60-sec session	4,980	738	6.7
90-sec session	2,261	503	4.5
WhatsApp	2,261	367	6.2
120-sec session	1,259	366	3.4
Phone reveal	1,151	279	4.1

Key Findings:

1. Users average 43 views before booking
2. Session depth matters: 84% engage for 30+ seconds, 59% for 60+ seconds
3. 29% contact hosts via WhatsApp before booking
4. 22% reveal phone numbers before booking

---

5. Post-Cancellation Behavior

5.1 Retry Patterns After Cancellation

When users cancel and try again within 30 days:

Behavior	Cases	%
Try different listing	817	92.1%
Retry same listing	70	7.9%

Key Finding: After a cancellation, 92% of users abandon that listing and try a different one. Only 8% retry the same listing.

Implication: When a booking is cancelled, proactively showing alternative listings may help retain the user.

WITH
  user_cancellations AS (
    SELECT
      user_id,
      listing_id,
      createdAt AS cancel_time
    FROM
      sadb_daily_renting_listing_bookings FINAL
    WHERE
      _peerdb_is_deleted = 0
      AND user_id > 0
      AND status LIKE 'CANCELED%'
      AND toYear(createdAt) = 2025
  ),
  follow_up_bookings AS (
    SELECT
      uc.user_id,
      uc.listing_id AS original_listing,
      b.listing_id AS retry_listing,
      b.status,
      dateDiff('day', uc.cancel_time, b.createdAt) AS days_after_cancel
    FROM
      user_cancellations uc
      JOIN sadb_daily_renting_listing_bookings b FINAL ON uc.user_id = b.user_id
      AND b.createdAt > uc.cancel_time
      AND b._peerdb_is_deleted = 0
  )
SELECT
  retry_behavior,
  count(*) AS cases,
  round(count(*) * 100.0 / sum(count(*)) OVER (), 1) AS pct
FROM
  (
    SELECT
      CASE
        WHEN original_listing = retry_listing THEN 'Retry same listing'
        ELSE 'Try different listing'
      END AS retry_behavior
    FROM
      follow_up_bookings
    WHERE
      days_after_cancel <= 30
  )
GROUP BY
  retry_behavior

---

6. Recommendations

High Priority

Action	Rationale	Expected Impact
Improve first-booking experience	6.1% success rate is the biggest leak	Could 2x first-attempt conversion
Enable easy re-booking	Same-listing retries convert 4.7x better	Reduce friction for returning users
Add comparison tools	Users view 37+ listings before booking	Help users make faster decisions

Medium Priority

Action	Rationale	Expected Impact
1-4 week re-engagement campaign	Optimal conversion window (20.5%)	Recover abandoned users
Show alternatives on cancellation	92% try different listing after cancel	Retain cancelling users
Trust signals for first-timers	Low first-attempt success suggests trust issues	Reduce abandonment

Product Suggestions

1. Saved listings / favorites: Users explore 37 listings on average - help them track options
2. “Continue booking” reminders: 25% retry same day, target with timely nudges
3. First-booking incentives: Discount or guarantee to boost 6.1% conversion
4. Post-cancellation recommendations: Automated similar listings when bookings cancel

---

Technical Notes

Data Limitations

1. Views table too large (1.26B rows) for direct user-level joins - used event logs instead
2. Event logs capture logged-in users only - anonymous user behavior not tracked
3. Stats table empty for bookable listings - pre-aggregated stats not available for daily rentals

Alternative Approaches

For full view analysis, consider:

• Materialized views aggregating user-level view counts
• Sampling approach with specific user cohorts
• Pre-computed user journey metrics table