KYC 고객 온보딩 데이터 템플릿

/*
This is a configuration and mapping guide, not an executable script. 
The process relies on manually exporting two reports from World-Check One and combining them with data from an upstream system (e.g., CRM).
*/

-- Step 1: Export 'Case Management Report' as case_report.csv
-- Step 2: Export 'Audit Trail Report' as audit_trail.csv
-- Step 3: Export relevant events from CRM/core system as source_system_events.csv

-- Step 4: Combine and map the files using a script or ETL tool to create the final event log.

-- Mapping logic for final_event_log.csv

-- Part A: Data from source_system_events.csv
SELECT
  ApplicationID AS CustomerApplication,
  'Application Submitted' AS ActivityName,
  SubmissionTimestamp AS EventTime,
  NULL AS EventEndTime,
  OriginatorID AS ReviewerId,
  OriginatingDept AS DepartmentName,
  RiskRating AS RiskLevel,
  SlaDate AS SlaTargetDate
FROM source_system_events.csv
WHERE EventType = 'Submission'

UNION ALL

SELECT
  ApplicationID AS CustomerApplication,
  'Application Approved' AS ActivityName,
  ApprovalTimestamp AS EventTime,
  NULL AS EventEndTime,
  ApproverID AS ReviewerId,
  ApprovingDept AS DepartmentName,
  RiskRating AS RiskLevel,
  SlaDate AS SlaTargetDate
FROM source_system_events.csv
WHERE EventType = 'Approval'

UNION ALL

SELECT
  ApplicationID AS CustomerApplication,
  'Account Activated' AS ActivityName,
  ActivationTimestamp AS EventTime,
  NULL AS EventEndTime,
  'SYSTEM' AS ReviewerId,
  'Operations' AS DepartmentName,
  RiskRating AS RiskLevel,
  SlaDate AS SlaTargetDate
FROM source_system_events.csv
WHERE EventType = 'Activation'

UNION ALL

SELECT
  ApplicationID AS CustomerApplication,
  'Application Rejected' AS ActivityName,
  RejectionTimestamp AS EventTime,
  NULL AS EventEndTime,
  ReviewerID AS ReviewerId,
  'Compliance' AS DepartmentName,
  RiskRating AS RiskLevel,
  SlaDate AS SlaTargetDate
FROM source_system_events.csv
WHERE EventType = 'Rejection'

-- Part B: Data from World-Check audit_trail.csv
UNION ALL

SELECT
  CaseID AS CustomerApplication,
  'Screening Request Created' AS ActivityName,
  EventTimestamp AS EventTime,
  NULL AS EventEndTime,
  UserID AS ReviewerId,
  'Compliance' AS DepartmentName,
  NULL AS RiskLevel,
  NULL AS SlaTargetDate
FROM audit_trail.csv
WHERE EventDescription LIKE '%Case Created%'

UNION ALL

SELECT
  CaseID AS CustomerApplication,
  'Automated Screening Performed' AS ActivityName,
  EventTimestamp AS EventTime,
  NULL AS EventEndTime,
  'SYSTEM' AS ReviewerId,
  'Compliance' AS DepartmentName,
  NULL AS RiskLevel,
  NULL AS SlaTargetDate
FROM audit_trail.csv
WHERE EventDescription LIKE '%Automated Screening Completed%'

UNION ALL

SELECT
  CaseID AS CustomerApplication,
  'Potential Match Identified' AS ActivityName,
  EventTimestamp AS EventTime,
  NULL AS EventEndTime,
  'SYSTEM' AS ReviewerId,
  'Compliance' AS DepartmentName,
  NULL AS RiskLevel,
  NULL AS SlaTargetDate
FROM audit_trail.csv
WHERE EventDescription LIKE '%Potential Match Found%'

UNION ALL

SELECT
  CaseID AS CustomerApplication,
  'Analyst Review Started' AS ActivityName,
  EventTimestamp AS EventTime,
  NULL AS EventEndTime,
  UserID AS ReviewerId,
  'Compliance' AS DepartmentName,
  NULL AS RiskLevel,
  NULL AS SlaTargetDate
FROM audit_trail.csv
WHERE EventDescription LIKE '%Case Assigned%' OR EventDescription LIKE '%Review Started%'

UNION ALL

SELECT
  CaseID AS CustomerApplication,
  'Additional Information Requested' AS ActivityName,
  EventTimestamp AS EventTime,
  NULL AS EventEndTime,
  UserID AS ReviewerId,
  'Compliance' AS DepartmentName,
  NULL AS RiskLevel,
  NULL AS SlaTargetDate
FROM audit_trail.csv
WHERE EventDescription LIKE '%Information Requested%'

UNION ALL

SELECT
  CaseID AS CustomerApplication,
  'Case Escalated for Review' AS ActivityName,
  EventTimestamp AS EventTime,
  NULL AS EventEndTime,
  UserID AS ReviewerId,
  'Compliance' AS DepartmentName,
  NULL AS RiskLevel,
  NULL AS SlaTargetDate
FROM audit_trail.csv
WHERE EventDescription LIKE '%Case Escalated%'

UNION ALL

SELECT
  CaseID AS CustomerApplication,
  'False Positive Confirmed' AS ActivityName,
  EventTimestamp AS EventTime,
  NULL AS EventEndTime,
  UserID AS ReviewerId,
  'Compliance' AS DepartmentName,
  NULL AS RiskLevel,
  NULL AS SlaTargetDate
FROM audit_trail.csv
WHERE EventDescription LIKE '%Match Dismissed as False Positive%'

UNION ALL

SELECT
  CaseID AS CustomerApplication,
  'True Match Confirmed' AS ActivityName,
  EventTimestamp AS EventTime,
  NULL AS EventEndTime,
  UserID AS ReviewerId,
  'Compliance' AS DepartmentName,
  NULL AS RiskLevel,
  NULL AS SlaTargetDate
FROM audit_trail.csv
WHERE EventDescription LIKE '%Match Confirmed as True%'

-- Part C: Data from World-Check case_report.csv
UNION ALL

SELECT
  CaseID AS CustomerApplication,
  'Screening Completed - Clear' AS ActivityName,
  ResolutionTimestamp AS EventTime,
  NULL AS EventEndTime,
  ResolverID AS ReviewerId,
  'Compliance' AS DepartmentName,
  RiskLevel AS RiskLevel,
  NULL AS SlaTargetDate
FROM case_report.csv
WHERE FinalStatus = 'Clear'

UNION ALL

SELECT
  CaseID AS CustomerApplication,
  'Screening Completed - Match Found' AS ActivityName,
  ResolutionTimestamp AS EventTime,
  NULL AS EventEndTime,
  ResolverID AS ReviewerId,
  'Compliance' AS DepartmentName,
  RiskLevel AS RiskLevel,
  NULL AS SlaTargetDate
FROM case_report.csv
WHERE FinalStatus = 'Match Found'

import requests
import pandas as pd
from datetime import datetime, timedelta

class=class="hl-string">"hl-comment"># --- Configuration and Placeholders ---
WC_API_KEY = class="hl-string">'[Your World-Check API Key]'
WC_API_SECRET = class="hl-string">'[Your World-Check API Secret]'
WC_API_BASE_URL = class="hl-string">'https://api.world-check.com/v2' class=class="hl-string">"hl-comment"># Use correct URL for your environment

class=class="hl-string">"hl-comment"># Date range for the extraction
START_DATE = (datetime.now() - timedelta(days=90)).strftime(class="hl-string">'%Y-%m-%dT%H:%M:%SZ')
END_DATE = datetime.now().strftime(class="hl-string">'%Y-%m-%dT%H:%M:%SZ')

class=class="hl-string">"hl-comment"># --- MOCK FUNCTIONS (Replace with actual data source connections) ---

def get_source_system_events(application_ids):
    class="hl-string">"""Simulates fetching pre- and post-screening events from a CRM or core system."""
    print(fclass="hl-string">'Fetching source system events for {len(application_ids)} applications...')
    mock_events = []
    for i, app_id in enumerate(application_ids):
        class=class="hl-string">"hl-comment"># Simulate initial application events
        mock_events.append({class="hl-string">'CustomerApplication': app_id, class="hl-string">'ActivityName': class="hl-string">'Application Submitted', class="hl-string">'EventTime': (datetime.now() - timedelta(days=i+5)).isoformat() + class="hl-string">'Z', class="hl-string">'DepartmentName': class="hl-string">'Sales'})
        class=class="hl-string">"hl-comment"># Simulate final outcome events
        if i % 3 == 0:
            mock_events.append({class="hl-string">'CustomerApplication': app_id, class="hl-string">'ActivityName': class="hl-string">'Application Approved', class="hl-string">'EventTime': (datetime.now() - timedelta(days=i)).isoformat() + class="hl-string">'Z', class="hl-string">'DepartmentName': class="hl-string">'Onboarding'})
            mock_events.append({class="hl-string">'CustomerApplication': app_id, class="hl-string">'ActivityName': class="hl-string">'Account Activated', class="hl-string">'EventTime': (datetime.now() - timedelta(days=i-1)).isoformat() + class="hl-string">'Z', class="hl-string">'DepartmentName': class="hl-string">'Operations'})
        else:
            mock_events.append({class="hl-string">'CustomerApplication': app_id, class="hl-string">'ActivityName': class="hl-string">'Application Rejected', class="hl-string">'EventTime': (datetime.now() - timedelta(days=i)).isoformat() + class="hl-string">'Z', class="hl-string">'DepartmentName': class="hl-string">'Compliance'})
    return mock_events

def get_worldcheck_cases(token):
    class="hl-string">"""Simulates fetching a list of cases from the World-Check API."""
    print(class="hl-string">'Fetching World-Check cases...')
    class=class="hl-string">"hl-comment"># In a real implementation, you would call GET /cases with date filters and handle pagination
    mock_case_list = [
        {class="hl-string">'caseId': class="hl-string">'0a1b2c3d-case-001', class="hl-string">'name': class="hl-string">'John Doe', class="hl-string">'caseSystemId': class="hl-string">'WC-001', class="hl-string">'customReference': class="hl-string">'APP-1001', class="hl-string">'creationDate': class="hl-string">'2023-10-01T10:00:00Z', class="hl-string">'riskLevel': class="hl-string">'Medium'},
        {class="hl-string">'caseId': class="hl-string">'0a1b2c3d-case-002', class="hl-string">'name': class="hl-string">'Jane Smith', class="hl-string">'caseSystemId': class="hl-string">'WC-002', class="hl-string">'customReference': class="hl-string">'APP-1002', class="hl-string">'creationDate': class="hl-string">'2023-10-02T11:30:00Z', class="hl-string">'riskLevel': class="hl-string">'Low'},
        {class="hl-string">'caseId': class="hl-string">'0a1b2c3d-case-003', class="hl-string">'name': class="hl-string">'Peter Jones', class="hl-string">'caseSystemId': class="hl-string">'WC-003', class="hl-string">'customReference': class="hl-string">'APP-1003', class="hl-string">'creationDate': class="hl-string">'2023-10-03T09:05:00Z', class="hl-string">'riskLevel': class="hl-string">'High'}
    ]
    return mock_case_list

def get_worldcheck_case_details(case_system_id, token):
    class="hl-string">"""Simulates fetching the audit trail and results for a single World-Check case."""
    print(fclass="hl-string">'Fetching details for case {case_system_id}...')
    class=class="hl-string">"hl-comment"># This mock data simulates a complex history for one case to generate multiple activities
    if case_system_id == class="hl-string">'WC-001':
        return {
            class="hl-string">'auditTrail': [
                {class="hl-string">'timestamp': class="hl-string">'2023-10-01T10:05:00Z', class="hl-string">'event': class="hl-string">'AUTOMATED_SCREENING', class="hl-string">'details': class="hl-string">'Screening performed', class="hl-string">'user': class="hl-string">'SYSTEM'},
                {class="hl-string">'timestamp': class="hl-string">'2023-10-01T10:06:00Z', class="hl-string">'event': class="hl-string">'MATCH_FOUND', class="hl-string">'details': class="hl-string">'Potential match identified', class="hl-string">'user': class="hl-string">'SYSTEM'},
                {class="hl-string">'timestamp': class="hl-string">'2023-10-02T14:00:00Z', class="hl-string">'event': class="hl-string">'REVIEW_STARTED', class="hl-string">'details': class="hl-string">'Case assigned', class="hl-string">'user': class="hl-string">'analyst1'},
                {class="hl-string">'timestamp': class="hl-string">'2023-10-02T15:30:00Z', class="hl-string">'event': class="hl-string">'MATCH_RESOLVED', class="hl-string">'details': class="hl-string">'Match resolved as False Positive', class="hl-string">'user': class="hl-string">'analyst1'},
                {class="hl-string">'timestamp': class="hl-string">'2023-10-03T11:00:00Z', class="hl-string">'event': class="hl-string">'ESCALATED', class="hl-string">'details': class="hl-string">'Case escalated to senior analyst', class="hl-string">'user': class="hl-string">'analyst1'},
                {class="hl-string">'timestamp': class="hl-string">'2023-10-04T09:00:00Z', class="hl-string">'event': class="hl-string">'INFORMATION_REQUESTED', class="hl-string">'details': class="hl-string">'Additional documents requested', class="hl-string">'user': class="hl-string">'senior_analyst'},
                {class="hl-string">'timestamp': class="hl-string">'2023-10-05T16:00:00Z', class="hl-string">'event': class="hl-string">'CASE_CLOSED', class="hl-string">'details': class="hl-string">'Case closed as Clear', class="hl-string">'user': class="hl-string">'senior_analyst'}
            ]
        }
    elif case_system_id == class="hl-string">'WC-003':
        return {
            class="hl-string">'auditTrail': [
                {class="hl-string">'timestamp': class="hl-string">'2023-10-03T09:10:00Z', class="hl-string">'event': class="hl-string">'AUTOMATED_SCREENING', class="hl-string">'details': class="hl-string">'Screening performed', class="hl-string">'user': class="hl-string">'SYSTEM'},
                {class="hl-string">'timestamp': class="hl-string">'2023-10-03T09:11:00Z', class="hl-string">'event': class="hl-string">'MATCH_FOUND', class="hl-string">'details': class="hl-string">'Potential match identified', class="hl-string">'user': class="hl-string">'SYSTEM'},
                {class="hl-string">'timestamp': class="hl-string">'2023-10-04T10:00:00Z', class="hl-string">'event': class="hl-string">'REVIEW_STARTED', class="hl-string">'details': class="hl-string">'Case assigned', class="hl-string">'user': class="hl-string">'analyst2'},
                {class="hl-string">'timestamp': class="hl-string">'2023-10-04T11:25:00Z', class="hl-string">'event': class="hl-string">'MATCH_RESOLVED', class="hl-string">'details': class="hl-string">'Match resolved as True Positive', class="hl-string">'user': class="hl-string">'analyst2'},
                {class="hl-string">'timestamp': class="hl-string">'2023-10-04T12:00:00Z', class="hl-string">'event': class="hl-string">'CASE_CLOSED', class="hl-string">'details': class="hl-string">'Case closed with matches', class="hl-string">'user': class="hl-string">'analyst2'}
            ]
        }
    else: class=class="hl-string">"hl-comment"># Simple class="hl-string">'Clear' case
        return {
            class="hl-string">'auditTrail': [
                {class="hl-string">'timestamp': class="hl-string">'2023-10-02T11:35:00Z', class="hl-string">'event': class="hl-string">'AUTOMATED_SCREENING', class="hl-string">'details': class="hl-string">'Screening performed', class="hl-string">'user': class="hl-string">'SYSTEM'},
                {class="hl-string">'timestamp': class="hl-string">'2023-10-02T11:36:00Z', class="hl-string">'event': class="hl-string">'CASE_CLOSED', class="hl-string">'details': class="hl-string">'Case closed as Clear', class="hl-string">'user': class="hl-string">'SYSTEM'}
            ]
        }

class=class="hl-string">"hl-comment"># --- Main Extraction Logic ---

def main():
    class="hl-string">"""Main function to run the extraction process."""
    class=class="hl-string">"hl-comment"># 1. Authenticate (real implementation)
    class=class="hl-string">"hl-comment"># token = get_wc_auth_token()
    token = class="hl-string">'mock_token' class=class="hl-string">"hl-comment"># Using a mock token for this example

    class=class="hl-string">"hl-comment"># 2. Get list of cases from World-Check
    cases = get_worldcheck_cases(token)
    if not cases:
        print(class="hl-string">'No cases found for the specified period.')
        return

    event_log = []

    class=class="hl-string">"hl-comment"># 3. Process each case from World-Check
    for case in cases:
        app_id = case.get(class="hl-string">'customReference')
        if not app_id:
            continue

        class=class="hl-string">"hl-comment"># Add class="hl-string">'Screening Request Created' event
        event_log.append({
            class="hl-string">'CustomerApplication': app_id,
            class="hl-string">'ActivityName': class="hl-string">'Screening Request Created',
            class="hl-string">'EventTime': case[class="hl-string">'creationDate'],
            class="hl-string">'ReviewerId': class="hl-string">'SYSTEM',
            class="hl-string">'DepartmentName': class="hl-string">'Compliance',
            class="hl-string">'RiskLevel': case.get(class="hl-string">'riskLevel')
        })

        details = get_worldcheck_case_details(case[class="hl-string">'caseSystemId'], token)
        for event in details.get(class="hl-string">'auditTrail', []):
            activity = None
            user = event.get(class="hl-string">'user', class="hl-string">'SYSTEM')
            department = class="hl-string">'Compliance' if user != class="hl-string">'SYSTEM' else class="hl-string">'System'
            
            class=class="hl-string">"hl-comment"># 4. Map audit trail events to activities
            if event[class="hl-string">'event'] == class="hl-string">'AUTOMATED_SCREENING':
                activity = class="hl-string">'Automated Screening Performed'
            elif event[class="hl-string">'event'] == class="hl-string">'MATCH_FOUND':
                activity = class="hl-string">'Potential Match Identified'
            elif event[class="hl-string">'event'] == class="hl-string">'REVIEW_STARTED':
                activity = class="hl-string">'Analyst Review Started'
            elif event[class="hl-string">'event'] == class="hl-string">'INFORMATION_REQUESTED':
                activity = class="hl-string">'Additional Information Requested'
            elif event[class="hl-string">'event'] == class="hl-string">'ESCALATED':
                activity = class="hl-string">'Case Escalated for Review'
            elif event[class="hl-string">'event'] == class="hl-string">'MATCH_RESOLVED' and class="hl-string">'False Positive' in event[class="hl-string">'details']:
                activity = class="hl-string">'False Positive Confirmed'
            elif event[class="hl-string">'event'] == class="hl-string">'MATCH_RESOLVED' and class="hl-string">'True Positive' in event[class="hl-string">'details']:
                activity = class="hl-string">'True Match Confirmed'
            elif event[class="hl-string">'event'] == class="hl-string">'CASE_CLOSED' and class="hl-string">'Clear' in event[class="hl-string">'details']:
                activity = class="hl-string">'Screening Completed - Clear'
            elif event[class="hl-string">'event'] == class="hl-string">'CASE_CLOSED' and class="hl-string">'matches' in event[class="hl-string">'details']:
                activity = class="hl-string">'Screening Completed - Match Found'

            if activity:
                event_log.append({
                    class="hl-string">'CustomerApplication': app_id,
                    class="hl-string">'ActivityName': activity,
                    class="hl-string">'EventTime': event[class="hl-string">'timestamp'],
                    class="hl-string">'ReviewerId': user,
                    class="hl-string">'DepartmentName': department,
                    class="hl-string">'RiskLevel': case.get(class="hl-string">'riskLevel')
                })
    
    class=class="hl-string">"hl-comment"># 5. Get events from source system
    application_ids = [case[class="hl-string">'customReference'] for case in cases if case.get(class="hl-string">'customReference')]
    source_events = get_source_system_events(application_ids)
    event_log.extend(source_events)

    class=class="hl-string">"hl-comment"># 6. Create and format DataFrame
    if not event_log:
        print(class="hl-string">'No events were generated.')
        return

    df = pd.DataFrame(event_log)
    df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime'])
    df = df.sort_values(by=[class="hl-string">'CustomerApplication', class="hl-string">'EventTime'])

    class=class="hl-string">"hl-comment"># Add EventEndTime by shifting the EventTime of the next event
    df[class="hl-string">'EventEndTime'] = df.groupby(class="hl-string">'CustomerApplication')[class="hl-string">'EventTime'].shift(-1)
    
    class=class="hl-string">"hl-comment"># Add placeholder for non-extractable attribute
    df[class="hl-string">'SlaTargetDate'] = pd.NaT

    class=class="hl-string">"hl-comment"># 7. Finalize and Export
    output_columns = [
        class="hl-string">'CustomerApplication',
        class="hl-string">'ActivityName',
        class="hl-string">'EventTime',
        class="hl-string">'EventEndTime',
        class="hl-string">'ReviewerId',
        class="hl-string">'DepartmentName',
        class="hl-string">'RiskLevel',
        class="hl-string">'SlaTargetDate'
    ]
    class=class="hl-string">"hl-comment"># Ensure all required columns exist, even if empty
    for col in output_columns:
        if col not in df.columns:
            df[col] = None
    
    df = df[output_columns]

    output_filename = class="hl-string">'event_log_worldcheck.csv'
    df.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%S.%fZ')
    print(fclass="hl-string">'Successfully exported event log to {output_filename}')

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