1.4 KiB
Data quality assessment: Verification Requests with Payment but without Bookings
This is a brief explanation of some edge cases that could incur into data quality problems - or potentially, be linked to a bug.
Problem
During the initiative of creating business KPIs for the Guest Squad, we are in need of computing a rate between Guest Journeys with Payment and Guest Journeys Created. The assumption we had was that though there are verification requests without an associated booking (for API requests), these shouldn’t have any payments. However, we have found that these cases indeed exist, though they are relatively rare and represent a very small percentage of the total amount paid (0.3% for payments on 2024).
We need to see if this is a possible error, maybe somehow the booking records got deleted or if there is an explanation where these cases are correct.
Backend Snippet
For further investigation on DWH side
SELECT b.id_booking,
vp.id_verification_to_payment,
vp.payment_paid_date_utc,
vp.amount_in_gbp,
vp.payment_status,
vp.id_verification_request
FROM intermediate.int_core__verification_payments vp
LEFT JOIN intermediate.int_core__bookings b
ON b.id_verification_request = vp.id_verification_request
WHERE vp.payment_paid_date_utc IS NOT NULL
AND b.id_booking IS NULL
AND vp.payment_status = 'Paid'
ORDER BY vp.payment_paid_date_utc DESC
