QuickSight Analytics Overview

Overview of Datasets in QuickSight

There are four primary datasets used for analytics and reporting:

1. Ardley-application-events-prod is used to track the most analytics from users interacting with the Ardley platform
2. Ardley-historical-application-events-prod keeps track of all historical analytic events from users interacting with the Ardley platform
3. Ardley-submitted-loan-applications-prod is the source of truth for submitted loans and the details of them
4. Ardley-latest-client offer-data-prod is the dataset used for portfolio analysis and tracks the portfolio potential of our customer’s datasets

---

Dataset: Ardley-application-events-prod

Data: 6 months —> present

Refreshed every hour

Data Organization

• Each borrower is assigned a primary borrower user ID that is shared across all campaigns and customer datasets (also called intake files)
• Collection ID further categorizes users by associating them with specific campaigns.
• Campaign ID references a specific campaign and is the umbrella over collection IDs
  • Campaign ID is also associated with campaign mnemonic, a name provided by cpnops during the campaign generation (Woodley Bank Cash Out 2.19)
• Customer ID references the ID of a specific Ardley customer. Borrower data cannot exist across two customers

Event Tracking

Event types include a wide range of actions, from email interactions to application submissions.

All event_type options in QuickSight:

• _email.send = Email was sent
• _email.open = Email was opened
• _email.click = Email was clicked on
• _email.complaint = Email marked as spam 💀
• _email.softbounce = Temporary send failure

_email.hardbounce = Permanent send failure (true bounce

Using Event Type for Email Performance

• user.experience.begin = Indicates the page has loaded completely. A difference between this and _email.click and can indicated fallout while the page loads.
• landing.app.interaction = Indicates a borrow is clicking on the landing page, or Navigator. (Think cash out drop-downs, FICO drop-down etc.)
• user.verification.email.init = Indicates the OTP email was requested by borrower. Will only show in apps with this included in the verification process
• user.verification.email.fail = Indicates an incorrect OTP was provided by the borrower
• user.verification.email.success = Indicates a correct OTP was provided by the borrower
• user.challenge.init = Indicates steps 2&3 of standard verification have been initiated by borrower
• user.challenge.fail = Indicates a failure at either of the steps
• user.challenge.success = Indicated successful verification
• loan.app.link.clicked = Indicates a link in the application was clicked, typically the phone number
• loan.app.navigation = Indicates the borrower is navigating throughout this application
  • This is useful in conjunction with the app_section_id field to display where the borrower is navigating
    • app_section_id wil report the same section title displayed on the front end navigation bar (i.e. personal info, demographics etc.)
    • Navigation can be further narrowed by also looking at app_page_id which will show which page of a section the borrower interacted with
      • Keeping in mind the first page of all sections is always ‘0’ and the second is ‘1’ and so on
• loan.app.submitted = Indicated the loan has been submitted

Custom Fields using Event_Type

Most custom fields that use event_type follow this formula:

distinct_countIf({primary_borrower_user_id},{event_type}='event_type')

This formula is counting the number of unique borrowers that have registered a specific event type. This can be modified in several ways.

For example, if you want a total count rather than a unique count:

countIf({primary_borrower_user_id},{event_type}='event_type')

This will give you the total numbers of borrowers that satisfy the condition. It will include duplicates (or borrowers that have interacted with the experience multiple times)

Alternatively, if you want a unique count, but you also want to look at the interactions within a specific campaign :

distinct_countIf({offer_collection_id},{event_type}='event_type')

By replacing primary_borrower_user_id with offer_collection_id you isolate the interactions to look at events by borrower AND campaign, rather than just borrower.

Additional examples:

• A search by borrower may report that Borrower A has only launched the experience once
• A search by borrower and campaign (collection id) may report that Borrower A has launched the experience twice. Once during campaign X and another during campaign Y

The time stamp is reported in both UTC and EST for use

---

Dataset: Ardley-historical-application-events-prod

Data: Historical

Refreshed weekly

🚨 TBD - Currently not live

Dataset: Ardley-submitted-loan-applications-prod

Data: Historical

Refreshed: Hourly

Data Organization

• Each borrower is assigned a primary borrower user ID that is shared across all campaigns and customer datasets (also called intake files)
• Collection ID further categorizes users by associating them with specific campaigns.
• Campaign ID references a specific campaign and is the umbrella over collection IDs
  • Campaign ID is also associated with campaign mnemonic, a name provided by cpnops during the campaign generation (Woodley Bank Cash Out 2.19)
• Customer ID references the ID of a specific Ardley customer.
  • Borrower data cannot exist across two customers

Changes and Additions from Ardley-application-events-prod

• primary_borrower_user_id = borrower_user_id
• Fields that start with “input_” came from the intake file and were passed through to QuickSight
• Fields that start with “loan_” came from the loan application and were passed through upon submission

---

Dataset: Ardley-latest-client offer-data-prod

Data: Snapshot in time

Refreshed: Daily

This dataset shows a customer’s entire portfolio potential as of the most recent calculation run. For most customer, this is a nightly calculation job.

Data Organization

• Customer ID references the ID of a specific Ardley customer. Borrower data cannot exist across two customers
• Dataset ID references the specific ID associated with every intake file provided by the customer

Offer Types Included in Dataset

• Cash Out Refinances
  • Max Equity available
  • Interest rate at max equity
  • Total new loan amount
• Conventional Refinances
  • Monthly savings
  • Interest rate
  • New loan payment
  • New loan amount
• Govi Refinances
  • Monthly savings
  • Interest rate
  • New loan payment
  • New loan amount
  • Home Equity Loans and/or HELOCs
    • Max Equity available
    • Interest rate at max equity
• MLS Listings (if available for the customer in question
  • Property address
  • Listing Date
  • Listing Status
  • List Price
• Universal Calculation Details
  • Home value
  • Existing interest rate
  • Remaining balance
  • Origination date