Checkout Flow Optimization Analysis

I recently completed 365's Checkout Flow Optimization Analysis with SQL and Tableau Project and decided to share publicly what my analysis is.
Tech Stack: MySQL, Tableau Story.

Table of Contents

1. Client Background
2. Northstar Metrics
3. Total checkout attempts/ Monthly checkout success rates
4. Count Total Carts/ Monthly Abandonment Rate
5. Error Messages and Devices
6. Hypothesis for A/B Testing
7. Recommendations

Client Background

365 DataScience is a Bulgarian based educational platform whose courses are mainly focused on Data Science and AI. The company makes a claim to have more than 3 million students and 132 courses in these 2 areas.
They have 2 pricing plans and a free plan that allows any person who has signed up access to their projects. All activities performed by users on the website are logged and stored in their database for further analysis.

They have been generous enough to share 2 tables: checkout_actions and checkout_carts from their 365_checkout_database. They both capture details such as the user’s id, date of activity, specific activity performed, error message encountered (if any), device the user accessed the website on, and the date they were able to complete purchase of a plan.
This analysis is intended to be shared with the Head of Marketing on checkout details spanning 1st of July 2022 to the 31st of January 2023.

Northstar Metrics

• Total carts created
• Total checkout attempts
• Monthly:
  • Checkout success rates
  • Cart abandonment rates
• Error Messages
  • And device types from which the error messages were generated from

Total checkout attempts/ Monthly checkout success rates

• Checkout attempt is the final stage, where a user with a selected plan in their cart, initiates the process to pay for the plan.
• Total Checkout attempts encompass both successful and unsuccessful payment of a plan after
• A successful checkout is one where a user was able to successfully pay for the selected pricing plan.
• On a monthly level, 1 out of 3 carts are cleared without any issues
• A surprising finding is that November, which had ~5x of January’s total checkout attempts, does not have a corresponding high successful checkout rate.
  • From the video, it mentioned there was a campaign (think Black Friday, Pre-Christmas sales, etc.) hence the sudden increase in total checkout attempts can be seen as an outlier and not the norm.

Count Total Carts/ Monthly Abandonment Rate

• A cart is abandoned when a user selects their preferred pricing plan but does not get to the stage to provide their payment details but leaves the 365 DataScience website.
• The key difference between an abandoned cart and an unsuccessful checkout attempt is that the unsuccessful attempt had a user providing their payment details.
  • But due to issues either from their end or 365’s, the payment was not successful.
• Back to the analysis, there is an interesting story that has been told of where October 2022 is the month with the highest percentage of abandoned carts.
  • Juxtapose this with the data in Checkout Success Attempt Rate.
  • In that screen, September 2022 had the lowest success attempt rate.
  • Whatever blocker been faced by customers bled into October thereby causing most of them to not finalize their checkout process
  • This needs its own independent investigation
• The highest bar that corresponds to the total count of carts in November 2022 proves the assertion of a campaign that was held as discussed in the earlier section.

Error Messages and Devices

• The error messages are those shown to the user when the checkout process fails.
• From this analysis, there are 2 different messages shown based on the device used.
  • Desktop: “year field is required”
  • Mobile: “number field is required”
• 80% of the error messages are from users who used Desktop to access the website.
• Unfortunately, the data associated with successful attempts does not include device information hence we cannot draw a conclusion about which of the devices we get most purchases from.
• Fixing both issues will use the same technique hence it will be cost effective and reasonable to do so at the same time.
  • Additional emphasis should be placed on resolving the number field as it directly influences the revenue.
• The issues can come from myriads of sources: small input fields on screens, real-time card verification as soon as the user completes filling in the number field in the form.

Hypothesis for A/B Testing

Adding real-time inline validation to the card-number field on mobile /desktop checkout pages will reduce failed attempts caused by missing card details, which in turn will increase the checkout success rates for both devices.

Recommendations

• As soon as the user fills any other field outside Card number, and Card number is empty, highlight it.
  
  

• Make it visible when the user moves to a CVC field and Expiry field is empty.