Your Loan Origination Data Template

#!/bin/bash

# === Configuration ===
# Replace with your Blend API credentials and desired date range.
CLIENT_ID="[Your Blend Client ID]"
CLIENT_SECRET="[Your Blend Client Secret]"

# Date range for the report in YYYY-MM-DD format.
START_DATE="[YYYY-MM-DD]"
END_DATE="[YYYY-MM-DD]"

# Blend API Environment URL
BASE_URL="https://api.blend.com"

# === Script Body ===
echo "Starting Blend data extraction..."

# 1. Authenticate and get a bearer token
echo "Authenticating with Blend API..."
TOKEN_RESPONSE=$(curl -s -X POST "${BASE_URL}/oauth/token" \
  -H "Content-Type: application/json" \
  -d '{
    "client_id": "'"${CLIENT_ID}"'",
    "client_secret": "'"${CLIENT_SECRET}"'",
    "grant_type": "client_credentials"
  }')

ACCESS_TOKEN=$(echo ${TOKEN_RESPONSE} | sed -E 's/.*"access_token":"([^"]+)".*/\1/')

if [ -z "${ACCESS_TOKEN}" ] || [ "${ACCESS_TOKEN}" == "null" ]; then
  echo "Authentication failed. Check your credentials."
  echo "Response: ${TOKEN_RESPONSE}"
  exit 1
fi
echo "Authentication successful."

# Function to request, poll, and download a report
fetch_report() {
  local report_type=$1
  echo "\nRequesting report: ${report_type}"

  # 2. Request the report
  REQUEST_RESPONSE=$(curl -s -X POST "${BASE_URL}/reporting/reports" \
    -H "Authorization: Bearer ${ACCESS_TOKEN}" \
    -H "Content-Type: application/json" \
    -d '{
      "type": "'"${report_type}"'",
      "startDate": "'"${START_DATE}"'",
      "endDate": "'"${END_DATE}"'"
    }')

  REPORT_ID=$(echo ${REQUEST_RESPONSE} | sed -E 's/.*"id":"([^"]+)".*/\1/')

  if [ -z "${REPORT_ID}" ] || [ "${REPORT_ID}" == "null" ]; then
    echo "Failed to request report ${report_type}."
    echo "Response: ${REQUEST_RESPONSE}"
    return 1
  fi
  echo "Report requested successfully. Report ID: ${REPORT_ID}"

  # 3. Poll for report completion
  while true; do
    STATUS_RESPONSE=$(curl -s -X GET "${BASE_URL}/reporting/reports/${REPORT_ID}" \
      -H "Authorization: Bearer ${ACCESS_TOKEN}")
    
    REPORT_STATUS=$(echo ${STATUS_RESPONSE} | sed -E 's/.*"status":"([^"]+)".*/\1/')
    echo "Current status for ${report_type}: ${REPORT_STATUS}"

    if [ "${REPORT_STATUS}" == "complete" ]; then
      DOWNLOAD_URL=$(echo ${STATUS_RESPONSE} | sed -E 's/.*"url":"([^"]+)".*/\1/')
      break
    elif [ "${REPORT_STATUS}" == "failed" ]; then
      echo "Report generation failed for ${report_type}."
      return 1
    fi
    sleep 30 # Wait for 30 seconds before checking again
  done

  # 4. Download the report
  echo "Report is ready. Downloading from ${DOWNLOAD_URL}"
  curl -s -o "${report_type}.csv.gz" "${DOWNLOAD_URL}"

  if [ $? -eq 0 ]; then
    echo "Successfully downloaded ${report_type}.csv.gz"
  else
    echo "Download failed for ${report_type}."
    return 1
  fi
}

# Fetch both required reports
fetch_report "loan_applications"
fetch_report "milestones"

echo "\nExtraction script finished."
echo "Next steps: Decompress the .csv.gz files and use a transformation tool to create the final event log."

/*
This is a conceptual transformation query executed by an ETL tool (e.g., AWS Glue, Python script)
on the raw data files (e.g., loan_applications.json) delivered by Blend to your S3 bucket.
The source 'blend_raw_data' represents the parsed file content.
*/
SELECT
    applicationId AS LoanApplicationId,
    'Application Started' AS ActivityName,
    createdAt AS EventStartTime,
    createdAt AS EventEndTime,
    loanOfficerName AS AssignedLoanOfficer,
    productType AS LoanProduct,
    finalDecision AS DecisionOutcome,
    submissionChannel AS ApplicationChannel,
    'Blend' AS SourceSystemName,
    GETDATE() AS LastDataUpdateTime
FROM blend_raw_data
WHERE createdAt IS NOT NULL

UNION ALL

SELECT
    applicationId AS LoanApplicationId,
    'Application Submitted' AS ActivityName,
    submittedAt AS EventStartTime,
    submittedAt AS EventEndTime,
    loanOfficerName AS AssignedLoanOfficer,
    productType AS LoanProduct,
    finalDecision AS DecisionOutcome,
    submissionChannel AS ApplicationChannel,
    'Blend' AS SourceSystemName,
    GETDATE() AS LastDataUpdateTime
FROM blend_raw_data
WHERE submittedAt IS NOT NULL

UNION ALL

SELECT
    applicationId AS LoanApplicationId,
    'Initial Disclosures Sent' AS ActivityName,
    milestones.initialDisclosuresSentAt AS EventStartTime,
    milestones.initialDisclosuresSentAt AS EventEndTime,
    loanOfficerName AS AssignedLoanOfficer,
    productType AS LoanProduct,
    finalDecision AS DecisionOutcome,
    submissionChannel AS ApplicationChannel,
    'Blend' AS SourceSystemName,
    GETDATE() AS LastDataUpdateTime
FROM blend_raw_data
WHERE milestones.initialDisclosuresSentAt IS NOT NULL

UNION ALL

SELECT
    applicationId AS LoanApplicationId,
    'Documents Requested' AS ActivityName,
    milestones.documentsFirstRequestedAt AS EventStartTime,
    milestones.documentsFirstRequestedAt AS EventEndTime,
    loanOfficerName AS AssignedLoanOfficer,
    productType AS LoanProduct,
    finalDecision AS DecisionOutcome,
    submissionChannel AS ApplicationChannel,
    'Blend' AS SourceSystemName,
    GETDATE() AS LastDataUpdateTime
FROM blend_raw_data
WHERE milestones.documentsFirstRequestedAt IS NOT NULL

UNION ALL

SELECT
    applicationId AS LoanApplicationId,
    'Documents Received' AS ActivityName,
    milestones.documentsAllReceivedAt AS EventStartTime,
    milestones.documentsAllReceivedAt AS EventEndTime,
    loanOfficerName AS AssignedLoanOfficer,
    productType AS LoanProduct,
    finalDecision AS DecisionOutcome,
    submissionChannel AS ApplicationChannel,
    'Blend' AS SourceSystemName,
    GETDATE() AS LastDataUpdateTime
FROM blend_raw_data
WHERE milestones.documentsAllReceivedAt IS NOT NULL

UNION ALL

SELECT
    applicationId AS LoanApplicationId,
    'Credit Check Initiated' AS ActivityName,
    milestones.creditCheckInitiatedAt AS EventStartTime,
    milestones.creditCheckInitiatedAt AS EventEndTime,
    loanOfficerName AS AssignedLoanOfficer,
    productType AS LoanProduct,
    finalDecision AS DecisionOutcome,
    submissionChannel AS ApplicationChannel,
    'Blend' AS SourceSystemName,
    GETDATE() AS LastDataUpdateTime
FROM blend_raw_data
WHERE milestones.creditCheckInitiatedAt IS NOT NULL

UNION ALL

SELECT
    applicationId AS LoanApplicationId,
    'Credit Check Completed' AS ActivityName,
    milestones.creditCheckCompletedAt AS EventStartTime,
    milestones.creditCheckCompletedAt AS EventEndTime,
    loanOfficerName AS AssignedLoanOfficer,
    productType AS LoanProduct,
    finalDecision AS DecisionOutcome,
    submissionChannel AS ApplicationChannel,
    'Blend' AS SourceSystemName,
    GETDATE() AS LastDataUpdateTime
FROM blend_raw_data
WHERE milestones.creditCheckCompletedAt IS NOT NULL

UNION ALL

SELECT
    applicationId AS LoanApplicationId,
    'Underwriting Commenced' AS ActivityName,
    milestones.underwritingStartedAt AS EventStartTime,
    milestones.underwritingStartedAt AS EventEndTime,
    underwriterName AS AssignedLoanOfficer, -- Assumes an underwriter field exists
    productType AS LoanProduct,
    finalDecision AS DecisionOutcome,
    submissionChannel AS ApplicationChannel,
    'Blend' AS SourceSystemName,
    GETDATE() AS LastDataUpdateTime
FROM blend_raw_data
WHERE milestones.underwritingStartedAt IS NOT NULL

UNION ALL

SELECT
    applicationId AS LoanApplicationId,
    'Underwriting Decision Rendered' AS ActivityName,
    decision.timestamp AS EventStartTime,
    decision.timestamp AS EventEndTime,
    underwriterName AS AssignedLoanOfficer,
    productType AS LoanProduct,
    decision.outcome AS DecisionOutcome,
    submissionChannel AS ApplicationChannel,
    'Blend' AS SourceSystemName,
    GETDATE() AS LastDataUpdateTime
FROM blend_raw_data
WHERE decision.timestamp IS NOT NULL AND decision.outcome IN ('Approved', 'Approved with Conditions', 'Denied')

UNION ALL

SELECT
    applicationId AS LoanApplicationId,
    'Loan Offer Generated' AS ActivityName,
    milestones.loanOfferGeneratedAt AS EventStartTime,
    milestones.loanOfferGeneratedAt AS EventEndTime,
    loanOfficerName AS AssignedLoanOfficer,
    productType AS LoanProduct,
    finalDecision AS DecisionOutcome,
    submissionChannel AS ApplicationChannel,
    'Blend' AS SourceSystemName,
    GETDATE() AS LastDataUpdateTime
FROM blend_raw_data
WHERE milestones.loanOfferGeneratedAt IS NOT NULL

UNION ALL

SELECT
    applicationId AS LoanApplicationId,
    'Closing Disclosures Sent' AS ActivityName,
    milestones.closingDisclosuresSentAt AS EventStartTime,
    milestones.closingDisclosuresSentAt AS EventEndTime,
    loanOfficerName AS AssignedLoanOfficer,
    productType AS LoanProduct,
    finalDecision AS DecisionOutcome,
    submissionChannel AS ApplicationChannel,
    'Blend' AS SourceSystemName,
    GETDATE() AS LastDataUpdateTime
FROM blend_raw_data
WHERE milestones.closingDisclosuresSentAt IS NOT NULL

UNION ALL

SELECT
    applicationId AS LoanApplicationId,
    'Loan Offer Accepted' AS ActivityName,
    milestones.loanOfferAcceptedAt AS EventStartTime,
    milestones.loanOfferAcceptedAt AS EventEndTime,
    loanOfficerName AS AssignedLoanOfficer,
    productType AS LoanProduct,
    finalDecision AS DecisionOutcome,
    submissionChannel AS ApplicationChannel,
    'Blend' AS SourceSystemName,
    GETDATE() AS LastDataUpdateTime
FROM blend_raw_data
WHERE milestones.loanOfferAcceptedAt IS NOT NULL

UNION ALL

SELECT
    applicationId AS LoanApplicationId,
    'Loan Agreement Signed' AS ActivityName,
    milestones.closingCompletedAt AS EventStartTime,
    milestones.closingCompletedAt AS EventEndTime,
    loanOfficerName AS AssignedLoanOfficer,
    productType AS LoanProduct,
    finalDecision AS DecisionOutcome,
    submissionChannel AS ApplicationChannel,
    'Blend' AS SourceSystemName,
    GETDATE() AS LastDataUpdateTime
FROM blend_raw_data
WHERE milestones.closingCompletedAt IS NOT NULL

UNION ALL

SELECT
    applicationId AS LoanApplicationId,
    'Funds Disbursed' AS ActivityName,
    fundedAt AS EventStartTime,
    fundedAt AS EventEndTime,
    loanOfficerName AS AssignedLoanOfficer,
    productType AS LoanProduct,
    finalDecision AS DecisionOutcome,
    submissionChannel AS ApplicationChannel,
    'Blend' AS SourceSystemName,
    GETDATE() AS LastDataUpdateTime
FROM blend_raw_data
WHERE fundedAt IS NOT NULL

UNION ALL

SELECT
    applicationId AS LoanApplicationId,
    'Application Withdrawn' AS ActivityName,
    withdrawnAt AS EventStartTime,
    withdrawnAt AS EventEndTime,
    loanOfficerName AS AssignedLoanOfficer,
    productType AS LoanProduct,
    'Withdrawn' AS DecisionOutcome,
    submissionChannel AS ApplicationChannel,
    'Blend' AS SourceSystemName,
    GETDATE() AS LastDataUpdateTime
FROM blend_raw_data
WHERE withdrawnAt IS NOT NULL

UNION ALL

SELECT
    applicationId AS LoanApplicationId,
    'Application Denied' AS ActivityName,
    decision.timestamp AS EventStartTime,
    decision.timestamp AS EventEndTime,
    underwriterName AS AssignedLoanOfficer,
    productType AS LoanProduct,
    'Denied' AS DecisionOutcome,
    submissionChannel AS ApplicationChannel,
    'Blend' AS SourceSystemName,
    GETDATE() AS LastDataUpdateTime
FROM blend_raw_data
WHERE decision.outcome = 'Denied'

import requests
import pandas as pd
import time
import datetime

class=class="hl-string">"hl-comment"># --- Configuration Settings ---
CLIENT_ID = class="hl-string">'YOUR_CLIENT_ID'
CLIENT_SECRET = class="hl-string">'YOUR_CLIENT_SECRET'
class=class="hl-string">"hl-comment"># Use https://api.blend.com for production or the appropriate sandbox URL
API_BASE_URL = class="hl-string">'https://api.blend.com'
class=class="hl-string">"hl-comment"># Set the date range for loan creation (YYYY-MM-DD)
START_DATE = class="hl-string">'2023-01-01'
END_DATE = class="hl-string">'2023-06-30'

class=class="hl-string">"hl-comment"># --- Blend Milestone Key to Activity Name Mapping ---
class=class="hl-string">"hl-comment"># CRITICAL: These keys may differ based on your Blend configuration.
class=class="hl-string">"hl-comment"># Please verify and update them to match your instance.
MILESTONE_MAPPING = {
    class="hl-string">'sent-initial-disclosure': class="hl-string">'Initial Disclosures Sent',
    class="hl-string">'borrower-documents-requested': class="hl-string">'Documents Requested',
    class="hl-string">'borrower-documents-received': class="hl-string">'Documents Received',
    class="hl-string">'credit-report-ordered': class="hl-string">'Credit Check Initiated',
    class="hl-string">'credit-report-received': class="hl-string">'Credit Check Completed',
    class="hl-string">'underwriting-started': class="hl-string">'Underwriting Commenced',
    class="hl-string">'underwriting-decision-complete': class="hl-string">'Underwriting Decision Rendered',
    class="hl-string">'loan-terms-generated': class="hl-string">'Loan Offer Generated',
    class="hl-string">'sent-closing-disclosure': class="hl-string">'Closing Disclosures Sent',
    class="hl-string">'loan-terms-accepted': class="hl-string">'Loan Offer Accepted',
    class="hl-string">'signed-closing-documents': class="hl-string">'Loan Agreement Signed',
    class="hl-string">'funding-complete': class="hl-string">'Funds Disbursed'
}

def get_access_token():
    class="hl-string">"""Authenticates with the Blend API to get an access token."""
    auth_url = fclass="hl-string">'{API_BASE_URL}/oauth/token'
    payload = {
        class="hl-string">'grant_type': class="hl-string">'client_credentials',
        class="hl-string">'client_id': CLIENT_ID,
        class="hl-string">'client_secret': CLIENT_SECRET
    }
    response = requests.post(auth_url, json=payload)
    response.raise_for_status()
    return response.json()[class="hl-string">'access_token']

def get_loans(token, start_date, end_date):
    class="hl-string">"""Fetches all loans within a specified date range."""
    loans_url = fclass="hl-string">'{API_BASE_URL}/loans'
    headers = {class="hl-string">'Authorization': fclass="hl-string">'Bearer {token}', class="hl-string">'Content-Type': class="hl-string">'application/json'}
    params = {class="hl-string">'createdAfter': start_date, class="hl-string">'createdBefore': end_date, class="hl-string">'limit': 100}
    all_loans = []
    
    while loans_url:
        response = requests.get(loans_url, headers=headers, params=params)
        response.raise_for_status()
        data = response.json()
        all_loans.extend(data[class="hl-string">'loans'])
        loans_url = data.get(class="hl-string">'nextLink')
        params = {}
        print(fclass="hl-string">'Fetched {len(all_loans)} loans so far...')
        time.sleep(1) class=class="hl-string">"hl-comment"># Respect API rate limits
    return all_loans

def get_milestones_for_loan(token, loan_id):
    class="hl-string">"""Fetches all milestones for a specific loan ID."""
    milestones_url = fclass="hl-string">'{API_BASE_URL}/loans/{loan_id}/milestones'
    headers = {class="hl-string">'Authorization': fclass="hl-string">'Bearer {token}', class="hl-string">'Content-Type': class="hl-string">'application/json'}
    response = requests.get(milestones_url, headers=headers)
    response.raise_for_status()
    return response.json().get(class="hl-string">'milestones', [])

def main():
    class="hl-string">"""Main function to orchestrate the extraction process."""
    print(class="hl-string">'Starting Blend data extraction...')
    try:
        access_token = get_access_token()
        loans = get_loans(access_token, START_DATE, END_DATE)
    except requests.exceptions.RequestException as e:
        print(fclass="hl-string">'Error communicating with Blend API: {e}')
        return

    event_log = []
    last_data_update_time = datetime.datetime.utcnow().isoformat() + class="hl-string">'Z'

    for i, loan in enumerate(loans):
        print(fclass="hl-string">'Processing loan {i+1}/{len(loans)}: {loan["id"]}')
        loan_id = loan[class="hl-string">'id']
        common_attributes = {
            class="hl-string">'LoanApplicationId': loan_id,
            class="hl-string">'SourceSystemName': class="hl-string">'Blend',
            class="hl-string">'LastDataUpdateTime': last_data_update_time,
            class="hl-string">'LoanProduct': loan.get(class="hl-string">'loanType'),
            class="hl-string">'ApplicationChannel': loan.get(class="hl-string">'channel'),
            class="hl-string">'AssignedLoanOfficer': loan.get(class="hl-string">'loanTeam', [{}])[0].get(class="hl-string">'name'),
            class="hl-string">'DecisionOutcome': loan.get(class="hl-string">'status')
        }

        class=class="hl-string">"hl-comment"># 1. Application Started
        if loan.get(class="hl-string">'created'):
            event_log.append({
                **common_attributes,
                class="hl-string">'ActivityName': class="hl-string">'Application Started',
                class="hl-string">'EventStartTime': loan[class="hl-string">'created'],
                class="hl-string">'EventEndTime': loan[class="hl-string">'created']
            })

        class=class="hl-string">"hl-comment"># 2. Application Submitted
        if loan.get(class="hl-string">'submitted'):
            event_log.append({
                **common_attributes,
                class="hl-string">'ActivityName': class="hl-string">'Application Submitted',
                class="hl-string">'EventStartTime': loan[class="hl-string">'submitted'],
                class="hl-string">'EventEndTime': loan[class="hl-string">'submitted']
            })
        
        class=class="hl-string">"hl-comment"># Process milestones
        try:
            milestones = get_milestones_for_loan(access_token, loan_id)
            for milestone in milestones:
                activity_name = MILESTONE_MAPPING.get(milestone.get(class="hl-string">'key'))
                if activity_name and milestone.get(class="hl-string">'completedDate'):
                    event_log.append({
                        **common_attributes,
                        class="hl-string">'ActivityName': activity_name,
                        class="hl-string">'EventStartTime': milestone[class="hl-string">'completedDate'],
                        class="hl-string">'EventEndTime': milestone[class="hl-string">'completedDate']
                    })
            time.sleep(0.5) class=class="hl-string">"hl-comment"># Respect API rate limits
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">'Could not fetch milestones for loan {loan_id}: {e}')

        class=class="hl-string">"hl-comment"># Handle terminal statuses from the loan object
        loan_status = loan.get(class="hl-string">'status')
        last_updated_time = loan.get(class="hl-string">'updated')
        if loan_status == class="hl-string">'withdrawn':
            event_log.append({
                **common_attributes,
                class="hl-string">'ActivityName': class="hl-string">'Application Withdrawn',
                class="hl-string">'EventStartTime': last_updated_time,
                class="hl-string">'EventEndTime': last_updated_time
            })
        elif loan_status == class="hl-string">'declined':
            event_log.append({
                **common_attributes,
                class="hl-string">'ActivityName': class="hl-string">'Application Denied',
                class="hl-string">'EventStartTime': last_updated_time,
                class="hl-string">'EventEndTime': last_updated_time
            })

    if not event_log:
        print(class="hl-string">'No events were generated. Please check your date range and configuration.')
        return

    class=class="hl-string">"hl-comment"># Create and save DataFrame
    df = pd.DataFrame(event_log)
    class=class="hl-string">"hl-comment"># Reorder columns for clarity
    column_order = [
        class="hl-string">'LoanApplicationId', class="hl-string">'ActivityName', class="hl-string">'EventStartTime', class="hl-string">'EventEndTime',
        class="hl-string">'SourceSystemName', class="hl-string">'LastDataUpdateTime', class="hl-string">'AssignedLoanOfficer',
        class="hl-string">'LoanProduct', class="hl-string">'DecisionOutcome', class="hl-string">'ApplicationChannel'
    ]
    df = df[column_order]
    
    output_filename = class="hl-string">'blend_event_log.csv'
    df.to_csv(output_filename, index=False)
    print(fclass="hl-string">'Extraction complete. Event log saved to {output_filename}')

if __name__ == class="hl-string">'__main__':
    main()