顧客サービスデータテンプレート

/* Case Created */
SELECT
  c.CaseNumber AS ServiceRequest,
  'Case Created' AS ActivityName,
  c.CreatedDate AS EventTime,
  'Salesforce Service Cloud' AS SourceSystemName,
  NOW() AS LastDataUpdateTime,
  NULL AS EndTime,
  c.Owner.Name AS AssignedAgent,
  c.Priority AS CasePriority,
  c.Origin AS CommunicationChannel,
  c.Status AS CaseStatus,
  c.Type AS CaseType,
  c.SlaExitDate AS SlaTargetResolutionTime,
  (c.ClosedDate - c.CreatedDate) * 24 * 60 AS CaseResolutionTime,
  NULL AS SlaStatus,
  c.IsFirstContactResolution__c AS IsFirstContactResolution
FROM Case c
WHERE c.CreatedDate >= :startDate AND c.CreatedDate <= :endDate

UNION ALL

/* Initial Acknowledgment Sent - Approximated by first outbound email */
SELECT
  e.Case.CaseNumber AS ServiceRequest,
  'Initial Acknowledgment Sent' AS ActivityName,
  e.MessageDate AS EventTime,
  'Salesforce Service Cloud' AS SourceSystemName,
  NOW() AS LastDataUpdateTime,
  NULL AS EndTime,
  e.Case.Owner.Name AS AssignedAgent,
  e.Case.Priority AS CasePriority,
  e.Case.Origin AS CommunicationChannel,
  e.Case.Status AS CaseStatus,
  e.Case.Type AS CaseType,
  e.Case.SlaExitDate AS SlaTargetResolutionTime,
  (e.Case.ClosedDate - e.Case.CreatedDate) * 24 * 60 AS CaseResolutionTime,
  NULL AS SlaStatus,
  e.Case.IsFirstContactResolution__c AS IsFirstContactResolution
FROM EmailMessage e
WHERE e.Case.CreatedDate >= :startDate AND e.Case.CreatedDate <= :endDate
  AND e.IsOutgoing = true
  AND e.Id IN (SELECT MIN(Id) FROM EmailMessage WHERE IsOutgoing = true GROUP BY CaseId)

UNION ALL

/* Case Categorized And Prioritized - Approximated by first change to Priority */
SELECT 
  h.Case.CaseNumber AS ServiceRequest,
  'Case Categorized And Prioritized' AS ActivityName,
  h.CreatedDate AS EventTime,
  'Salesforce Service Cloud' AS SourceSystemName,
  NOW() AS LastDataUpdateTime,
  NULL AS EndTime,
  h.Case.Owner.Name AS AssignedAgent,
  h.NewValue AS CasePriority,
  h.Case.Origin AS CommunicationChannel,
  h.Case.Status AS CaseStatus,
  h.Case.Type AS CaseType,
  h.Case.SlaExitDate AS SlaTargetResolutionTime,
  (h.Case.ClosedDate - h.Case.CreatedDate) * 24 * 60 AS CaseResolutionTime,
  NULL AS SlaStatus,
  h.Case.IsFirstContactResolution__c AS IsFirstContactResolution
FROM CaseHistory h 
WHERE h.Case.CreatedDate >= :startDate AND h.Case.CreatedDate <= :endDate
  AND h.Field = 'Priority'
  AND h.OldValue = NULL

UNION ALL

/* Case Assigned To Agent - First Owner Change after Creation */
SELECT
  h.Case.CaseNumber AS ServiceRequest,
  'Case Assigned To Agent' AS ActivityName,
  h.CreatedDate AS EventTime,
  'Salesforce Service Cloud' AS SourceSystemName,
  NOW() AS LastDataUpdateTime,
  NULL AS EndTime,
  u.Name AS AssignedAgent, 
  h.Case.Priority AS CasePriority,
  h.Case.Origin AS CommunicationChannel,
  h.Case.Status AS CaseStatus,
  h.Case.Type AS CaseType,
  h.Case.SlaExitDate AS SlaTargetResolutionTime,
  (h.Case.ClosedDate - h.Case.CreatedDate) * 24 * 60 AS CaseResolutionTime,
  NULL AS SlaStatus,
  h.Case.IsFirstContactResolution__c AS IsFirstContactResolution
FROM CaseHistory h
LEFT JOIN User u ON (h.NewValue = u.Id)
WHERE h.Case.CreatedDate >= :startDate AND h.Case.CreatedDate <= :endDate
  AND h.Field = 'Owner'
  AND h.Id IN (SELECT MIN(Id) FROM CaseHistory WHERE Field = 'Owner' GROUP BY CaseId)

UNION ALL

/* Agent Investigates Issue */
SELECT 
  h.Case.CaseNumber AS ServiceRequest,
  'Agent Investigates Issue' AS ActivityName,
  h.CreatedDate AS EventTime,
  'Salesforce Service Cloud' AS SourceSystemName,
  NOW() AS LastDataUpdateTime,
  NULL AS EndTime,
  h.Case.Owner.Name AS AssignedAgent,
  h.Case.Priority AS CasePriority,
  h.Case.Origin AS CommunicationChannel,
  h.NewValue AS CaseStatus,
  h.Case.Type AS CaseType,
  h.Case.SlaExitDate AS SlaTargetResolutionTime,
  (h.Case.ClosedDate - h.Case.CreatedDate) * 24 * 60 AS CaseResolutionTime,
  NULL AS SlaStatus,
  h.Case.IsFirstContactResolution__c AS IsFirstContactResolution
FROM CaseHistory h 
WHERE h.Case.CreatedDate >= :startDate AND h.Case.CreatedDate <= :endDate
  AND h.Field = 'Status' AND h.NewValue = '[Your In Progress Status]'

UNION ALL

/* Information Requested From Customer */
SELECT 
  h.Case.CaseNumber AS ServiceRequest,
  'Information Requested From Customer' AS ActivityName,
  h.CreatedDate AS EventTime,
  'Salesforce Service Cloud' AS SourceSystemName,
  NOW() AS LastDataUpdateTime,
  NULL AS EndTime,
  h.Case.Owner.Name AS AssignedAgent,
  h.Case.Priority AS CasePriority,
  h.Case.Origin AS CommunicationChannel,
  h.NewValue AS CaseStatus,
  h.Case.Type AS CaseType,
  h.Case.SlaExitDate AS SlaTargetResolutionTime,
  (h.Case.ClosedDate - h.Case.CreatedDate) * 24 * 60 AS CaseResolutionTime,
  NULL AS SlaStatus,
  h.Case.IsFirstContactResolution__c AS IsFirstContactResolution
FROM CaseHistory h 
WHERE h.Case.CreatedDate >= :startDate AND h.Case.CreatedDate <= :endDate
  AND h.Field = 'Status' AND h.NewValue = '[Your Waiting on Customer Status]'

UNION ALL

/* Information Received From Customer */
SELECT 
  h.Case.CaseNumber AS ServiceRequest,
  'Information Received From Customer' AS ActivityName,
  h.CreatedDate AS EventTime,
  'Salesforce Service Cloud' AS SourceSystemName,
  NOW() AS LastDataUpdateTime,
  NULL AS EndTime,
  h.Case.Owner.Name AS AssignedAgent,
  h.Case.Priority AS CasePriority,
  h.Case.Origin AS CommunicationChannel,
  h.NewValue AS CaseStatus,
  h.Case.Type AS CaseType,
  h.Case.SlaExitDate AS SlaTargetResolutionTime,
  (h.Case.ClosedDate - h.Case.CreatedDate) * 24 * 60 AS CaseResolutionTime,
  NULL AS SlaStatus,
  h.Case.IsFirstContactResolution__c AS IsFirstContactResolution
FROM CaseHistory h 
WHERE h.Case.CreatedDate >= :startDate AND h.Case.CreatedDate <= :endDate
  AND h.Field = 'Status' AND h.OldValue = '[Your Waiting on Customer Status]'

UNION ALL

/* Case Reassigned */
SELECT
  h.Case.CaseNumber AS ServiceRequest,
  'Case Reassigned' AS ActivityName,
  h.CreatedDate AS EventTime,
  'Salesforce Service Cloud' AS SourceSystemName,
  NOW() AS LastDataUpdateTime,
  NULL AS EndTime,
  u.Name AS AssignedAgent,
  h.Case.Priority AS CasePriority,
  h.Case.Origin AS CommunicationChannel,
  h.Case.Status AS CaseStatus,
  h.Case.Type AS CaseType,
  h.Case.SlaExitDate AS SlaTargetResolutionTime,
  (h.Case.ClosedDate - h.Case.CreatedDate) * 24 * 60 AS CaseResolutionTime,
  NULL AS SlaStatus,
  h.Case.IsFirstContactResolution__c AS IsFirstContactResolution
FROM CaseHistory h
LEFT JOIN User u ON (h.NewValue = u.Id)
WHERE h.Case.CreatedDate >= :startDate AND h.Case.CreatedDate <= :endDate
  AND h.Field = 'Owner'
  AND h.Id NOT IN (SELECT MIN(Id) FROM CaseHistory WHERE Field = 'Owner' GROUP BY CaseId)

UNION ALL

/* Internal Escalation Triggered */
SELECT 
  h.Case.CaseNumber AS ServiceRequest,
  'Internal Escalation Triggered' AS ActivityName,
  h.CreatedDate AS EventTime,
  'Salesforce Service Cloud' AS SourceSystemName,
  NOW() AS LastDataUpdateTime,
  NULL AS EndTime,
  h.Case.Owner.Name AS AssignedAgent,
  h.Case.Priority AS CasePriority,
  h.Case.Origin AS CommunicationChannel,
  h.Case.Status AS CaseStatus,
  h.Case.Type AS CaseType,
  h.Case.SlaExitDate AS SlaTargetResolutionTime,
  (h.Case.ClosedDate - h.Case.CreatedDate) * 24 * 60 AS CaseResolutionTime,
  NULL AS SlaStatus,
  h.Case.IsFirstContactResolution__c AS IsFirstContactResolution
FROM CaseHistory h 
WHERE h.Case.CreatedDate >= :startDate AND h.Case.CreatedDate <= :endDate
  AND h.Field = 'IsEscalated' AND h.NewValue = 'true'

UNION ALL

/* SLA Milestone Violated */
SELECT
  m.Case.CaseNumber AS ServiceRequest,
  'SLA Milestone Violated' AS ActivityName,
  m.CompletionDate AS EventTime,
  'Salesforce Service Cloud' AS SourceSystemName,
  NOW() AS LastDataUpdateTime,
  NULL AS EndTime,
  m.Case.Owner.Name AS AssignedAgent,
  m.Case.Priority AS CasePriority,
  m.Case.Origin AS CommunicationChannel,
  m.Case.Status AS CaseStatus,
  m.Case.Type AS CaseType,
  m.Case.SlaExitDate AS SlaTargetResolutionTime,
  (m.Case.ClosedDate - m.Case.CreatedDate) * 24 * 60 AS CaseResolutionTime,
  'Violated' AS SlaStatus,
  m.Case.IsFirstContactResolution__c AS IsFirstContactResolution
FROM CaseMilestone m
WHERE m.Case.CreatedDate >= :startDate AND m.Case.CreatedDate <= :endDate
  AND m.IsViolated = true

UNION ALL

/* Solution Proposed To Customer */
SELECT 
  h.Case.CaseNumber AS ServiceRequest,
  'Solution Proposed To Customer' AS ActivityName,
  h.CreatedDate AS EventTime,
  'Salesforce Service Cloud' AS SourceSystemName,
  NOW() AS LastDataUpdateTime,
  NULL AS EndTime,
  h.Case.Owner.Name AS AssignedAgent,
  h.Case.Priority AS CasePriority,
  h.Case.Origin AS CommunicationChannel,
  h.NewValue AS CaseStatus,
  h.Case.Type AS CaseType,
  h.Case.SlaExitDate AS SlaTargetResolutionTime,
  (h.Case.ClosedDate - h.Case.CreatedDate) * 24 * 60 AS CaseResolutionTime,
  NULL AS SlaStatus,
  h.Case.IsFirstContactResolution__c AS IsFirstContactResolution
FROM CaseHistory h 
WHERE h.Case.CreatedDate >= :startDate AND h.Case.CreatedDate <= :endDate
  AND h.Field = 'Status' AND h.NewValue = '[Your Solution Proposed Status]'

UNION ALL

/* Case Resolved */
SELECT 
  h.Case.CaseNumber AS ServiceRequest,
  'Case Resolved' AS ActivityName,
  h.CreatedDate AS EventTime,
  'Salesforce Service Cloud' AS SourceSystemName,
  NOW() AS LastDataUpdateTime,
  NULL AS EndTime,
  h.Case.Owner.Name AS AssignedAgent,
  h.Case.Priority AS CasePriority,
  h.Case.Origin AS CommunicationChannel,
  h.NewValue AS CaseStatus,
  h.Case.Type AS CaseType,
  h.Case.SlaExitDate AS SlaTargetResolutionTime,
  (h.Case.ClosedDate - h.Case.CreatedDate) * 24 * 60 AS CaseResolutionTime,
  NULL AS SlaStatus,
  h.Case.IsFirstContactResolution__c AS IsFirstContactResolution
FROM CaseHistory h 
WHERE h.Case.CreatedDate >= :startDate AND h.Case.Createddate <= :endDate
  AND h.Field = 'Status' AND h.NewValue = '[Your Resolved Status]'

UNION ALL

/* Satisfaction Survey Sent */
SELECT
  e.Case.CaseNumber AS ServiceRequest,
  'Satisfaction Survey Sent' AS ActivityName,
  e.MessageDate AS EventTime,
  'Salesforce Service Cloud' AS SourceSystemName,
  NOW() AS LastDataUpdateTime,
  NULL AS EndTime,
  e.Case.Owner.Name AS AssignedAgent,
  e.Case.Priority AS CasePriority,
  e.Case.Origin AS CommunicationChannel,
  e.Case.Status AS CaseStatus,
  e.Case.Type AS CaseType,
  e.Case.SlaExitDate AS SlaTargetResolutionTime,
  (e.Case.ClosedDate - e.Case.CreatedDate) * 24 * 60 AS CaseResolutionTime,
  NULL AS SlaStatus,
  e.Case.IsFirstContactResolution__c AS IsFirstContactResolution
FROM EmailMessage e
WHERE e.Case.CreatedDate >= :startDate AND e.Case.CreatedDate <= :endDate
  AND e.IsOutgoing = true AND e.Subject LIKE '[Your Survey Subject Pattern]%'

UNION ALL

/* Case Closed */
SELECT
  c.CaseNumber AS ServiceRequest,
  'Case Closed' AS ActivityName,
  c.ClosedDate AS EventTime,
  'Salesforce Service Cloud' AS SourceSystemName,
  NOW() AS LastDataUpdateTime,
  NULL AS EndTime,
  c.Owner.Name AS AssignedAgent,
  c.Priority AS CasePriority,
  c.Origin AS CommunicationChannel,
  c.Status AS CaseStatus,
  c.Type AS CaseType,
  c.SlaExitDate AS SlaTargetResolutionTime,
  (c.ClosedDate - c.CreatedDate) * 24 * 60 AS CaseResolutionTime,
  NULL AS SlaStatus,
  c.IsFirstContactResolution__c AS IsFirstContactResolution
FROM Case c
WHERE c.CreatedDate >= :startDate AND c.CreatedDate <= :endDate
  AND c.IsClosed = true

UNION ALL

/* Case Reopened */
SELECT
  h.Case.CaseNumber AS ServiceRequest,
  'Case Reopened' AS ActivityName,
  h.CreatedDate AS EventTime,
  'Salesforce Service Cloud' AS SourceSystemName,
  NOW() AS LastDataUpdateTime,
  NULL AS EndTime,
  h.Case.Owner.Name AS AssignedAgent,
  h.Case.Priority AS CasePriority,
  h.Case.Origin AS CommunicationChannel,
  h.NewValue AS CaseStatus,
  h.Case.Type AS CaseType,
  h.Case.SlaExitDate AS SlaTargetResolutionTime,
  (h.Case.ClosedDate - h.Case.CreatedDate) * 24 * 60 AS CaseResolutionTime,
  NULL AS SlaStatus,
  h.Case.IsFirstContactResolution__c AS IsFirstContactResolution
FROM CaseHistory h
WHERE h.Case.CreatedDate >= :startDate AND h.Case.CreatedDate <= :endDate
  AND h.Field = 'IsClosed' AND h.OldValue = 'true' AND h.NewValue = 'false'

import pandas as pd
from simple_salesforce import Salesforce
from datetime import datetime
import os

class=class="hl-string">"hl-comment"># --- Configuration Settings --- #
class=class="hl-string">"hl-comment"># It is highly recommended to use environment variables for security.
SF_USERNAME = os.environ.get(class="hl-string">'SF_USERNAME', class="hl-string">'[Your Salesforce Username]')
SF_PASSWORD = os.environ.get(class="hl-string">'SF_PASSWORD', class="hl-string">'[Your Salesforce Password]')
SF_SECURITY_TOKEN = os.environ.get(class="hl-string">'SF_SECURITY_TOKEN', class="hl-string">'[Your Salesforce Security Token]')
SF_CONSUMER_KEY = os.environ.get(class="hl-string">'SF_CONSUMER_KEY', class="hl-string">'[Your Connected App Consumer Key]')
SF_CONSUMER_SECRET = os.environ.get(class="hl-string">'SF_CONSUMER_SECRET', class="hl-string">'[Your Connected App Consumer Secret]')

class=class="hl-string">"hl-comment"># Date filter for the SOQL query (e.g., last 90 days)
SOQL_DATE_FILTER = class="hl-string">'LAST_N_DAYS:90'

class=class="hl-string">"hl-comment"># IMPORTANT: Map your org's specific Case Status values here
STATUS_MAPPING = {
    class="hl-string">'investigating': [class="hl-string">'In Progress', class="hl-string">'Working', class="hl-string">'Researching'],
    class="hl-string">'waiting_on_customer': [class="hl-string">'Waiting on Customer', class="hl-string">'Pending Customer Response'],
    class="hl-string">'resolved': [class="hl-string">'Resolved', class="hl-string">'Solution Provided'],
    class="hl-string">'closed': [class="hl-string">'Closed'],
    class="hl-string">'reopened': [class="hl-string">'Reopened', class="hl-string">'Re-opened'] class=class="hl-string">"hl-comment"># Statuses indicating a case is active again after resolution
}

SOURCE_SYSTEM_NAME = class="hl-string">'Salesforce Service Cloud'

def fetch_salesforce_data(sf_conn):
    class="hl-string">"""Fetches all necessary data from Salesforce."""
    print(class="hl-string">"Fetching Case data...")
    case_query = fclass="hl-string">"""
    SELECT Id, CaseNumber, CreatedDate, ClosedDate, OwnerId, Priority, Origin, Status, Type, 
           Entitlement.SlaExitDate, (SELECT Name FROM Owner) 
    FROM Case 
    WHERE CreatedDate >= {SOQL_DATE_FILTER}
    """
    cases = sf_conn.query_all(case_query)[class="hl-string">'records']
    case_ids = [case[class="hl-string">'Id'] for case in cases]
    print(fclass="hl-string">"Found {len(case_ids)} cases.")

    if not case_ids:
        return pd.DataFrame(), pd.DataFrame(), pd.DataFrame(), pd.DataFrame()

    class=class="hl-string">"hl-comment"># Batch case IDs for subsequent queries
    case_id_str = class="hl-string">"','".join(case_ids)

    print(class="hl-string">"Fetching Case History data...")
    history_query = fclass="hl-string">"""
    SELECT CaseId, CreatedDate, Field, NewValue, OldValue, (SELECT Name FROM CreatedBy)
    FROM CaseHistory 
    WHERE CaseId IN ('{case_id_str}') AND Field IN ('Status', 'Owner', 'Priority', 'Type', 'IsEscalated')
    ORDER BY CreatedDate ASC
    """
    history = sf_conn.query_all(history_query)[class="hl-string">'records']

    print(class="hl-string">"Fetching Case Milestone data...")
    milestone_query = fclass="hl-string">"""
    SELECT CaseId, CompletionDate, IsViolated, IsCompleted, TargetDate, (SELECT Name FROM MilestoneType)
    FROM CaseMilestone
    WHERE CaseId IN ('{case_id_str}') AND IsViolated = true
    """
    milestones = sf_conn.query_all(milestone_query)[class="hl-string">'records']
    
    print(class="hl-string">"Fetching Email Message data...")
    email_query = fclass="hl-string">"""
    SELECT ParentId, CreatedDate, Status, Incoming, Subject
    FROM EmailMessage
    WHERE ParentId IN ('{case_id_str}') AND Status = 'Sent'
    ORDER BY CreatedDate ASC
    """
    emails = sf_conn.query_all(email_query)[class="hl-string">'records']

    return pd.DataFrame(cases), pd.DataFrame(history), pd.DataFrame(milestones), pd.DataFrame(emails)

def transform_to_event_log(cases_df, history_df, milestones_df, emails_df):
    class="hl-string">"""Transforms raw Salesforce data into a structured event log."""
    event_log = []
    last_update_time = datetime.utcnow().isoformat() + class="hl-string">'Z'

    class=class="hl-string">"hl-comment"># Create a lookup for case attributes
    cases_df[class="hl-string">'OwnerName'] = cases_df[class="hl-string">'Owner'].apply(lambda x: x[class="hl-string">'Name'] if x else None)
    case_details = cases_df.set_index(class="hl-string">'Id').to_dict(class="hl-string">'index')
    
    class=class="hl-string">"hl-comment"># Group related data by CaseId
    history_by_case = history_df.groupby(class="hl-string">'CaseId')
    milestones_by_case = milestones_df.groupby(class="hl-string">'CaseId')
    emails_by_case = emails_df.groupby(class="hl-string">'CaseId')

    for case_id, case in case_details.items():
        base_attributes = {
            class="hl-string">'ServiceRequest': case[class="hl-string">'CaseNumber'],
            class="hl-string">'SourceSystemName': SOURCE_SYSTEM_NAME,
            class="hl-string">'LastDataUpdateTime': last_update_time,
            class="hl-string">'CasePriority': case[class="hl-string">'Priority'],
            class="hl-string">'CommunicationChannel': case[class="hl-string">'Origin'],
            class="hl-string">'CaseType': case[class="hl-string">'Type'],
            class="hl-string">'SlaTargetResolutionTime': case.get(class="hl-string">'Entitlement', {}).get(class="hl-string">'SlaExitDate')
        }

        class=class="hl-string">"hl-comment"># 1. Case Created
        event_log.append({
            **base_attributes,
            class="hl-string">'ActivityName': class="hl-string">'Case Created',
            class="hl-string">'EventTime': case[class="hl-string">'CreatedDate'],
            class="hl-string">'AssignedAgent': case[class="hl-string">'OwnerName'],
            class="hl-string">'CaseStatus': class="hl-string">'New' class=class="hl-string">"hl-comment"># Assume initial status is class="hl-string">'New'
        })
        
        case_history = history_by_case.get_group(case_id) if case_id in history_by_case.groups else pd.DataFrame()
        case_emails = emails_by_case.get_group(case_id) if case_id in emails_by_case.groups else pd.DataFrame()
        
        class=class="hl-string">"hl-comment"># 2. Initial Acknowledgment Sent (Inferred)
        outbound_emails = case_emails[case_emails[class="hl-string">'Incoming'] == False]
        if not outbound_emails.empty:
            ack_email = outbound_emails.iloc[0]
            event_log.append({
                **base_attributes,
                class="hl-string">'ActivityName': class="hl-string">'Initial Acknowledgment Sent',
                class="hl-string">'EventTime': ack_email[class="hl-string">'CreatedDate'],
                class="hl-string">'AssignedAgent': case[class="hl-string">'OwnerName'], class=class="hl-string">"hl-comment"># Agent at time of creation
                class="hl-string">'CaseStatus': class="hl-string">'New' class=class="hl-string">"hl-comment"># Status at time of acknowledgement
            })
        
        owner_changes = case_history[case_history[class="hl-string">'Field'] == class="hl-string">'Owner'].copy()
        owner_changes[class="hl-string">'NewValueName'] = owner_changes[class="hl-string">'NewValue'].apply(lambda x: x[class="hl-string">'Name'] if isinstance(x, dict) else x)

        class=class="hl-string">"hl-comment"># 4. Case Assigned To Agent & 8. Case Reassigned
        if not owner_changes.empty:
            class=class="hl-string">"hl-comment"># First owner change is the initial assignment if it differs from creator's queue
            event_log.append({
                **base_attributes,
                class="hl-string">'ActivityName': class="hl-string">'Case Assigned To Agent',
                class="hl-string">'EventTime': owner_changes.iloc[0][class="hl-string">'CreatedDate'],
                class="hl-string">'AssignedAgent': owner_changes.iloc[0][class="hl-string">'NewValueName'],
                class="hl-string">'CaseStatus': case[class="hl-string">'Status']
            })
            class=class="hl-string">"hl-comment"># Subsequent owner changes are reassignments
            for _, row in owner_changes.iloc[1:].iterrows():
                event_log.append({
                    **base_attributes,
                    class="hl-string">'ActivityName': class="hl-string">'Case Reassigned',
                    class="hl-string">'EventTime': row[class="hl-string">'CreatedDate'],
                    class="hl-string">'AssignedAgent': row[class="hl-string">'NewValueName'],
                    class="hl-string">'CaseStatus': case[class="hl-string">'Status'] 
                })

        last_status = class="hl-string">'New'
        resolved_time = None

        class=class="hl-string">"hl-comment"># Process status, priority, type, and escalation changes from history
        for _, hist in case_history.iterrows():
            event_time = hist[class="hl-string">'CreatedDate']
            current_agent = owner_changes[owner_changes[class="hl-string">'CreatedDate'] <= event_time].iloc[-1][class="hl-string">'NewValueName'] if not owner_changes[owner_changes[class="hl-string">'CreatedDate'] <= event_time].empty else case[class="hl-string">'OwnerName']

            if hist[class="hl-string">'Field'] == class="hl-string">'Status':
                new_status = hist[class="hl-string">'NewValue']
                old_status = hist[class="hl-string">'OldValue']
                last_status = new_status
                
                class=class="hl-string">"hl-comment"># 5. Agent Investigates Issue
                if new_status in STATUS_MAPPING[class="hl-string">'investigating']:
                    event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Agent Investigates Issue', class="hl-string">'EventTime': event_time, class="hl-string">'AssignedAgent': current_agent, class="hl-string">'CaseStatus': new_status})
                class=class="hl-string">"hl-comment"># 6. Information Requested From Customer
                elif new_status in STATUS_MAPPING[class="hl-string">'waiting_on_customer']:
                    event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Information Requested From Customer', class="hl-string">'EventTime': event_time, class="hl-string">'AssignedAgent': current_agent, class="hl-string">'CaseStatus': new_status})
                class=class="hl-string">"hl-comment"># 7. Information Received From Customer
                elif old_status in STATUS_MAPPING[class="hl-string">'waiting_on_customer']:
                    event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Information Received From Customer', class="hl-string">'EventTime': event_time, class="hl-string">'AssignedAgent': current_agent, class="hl-string">'CaseStatus': new_status})
                class=class="hl-string">"hl-comment"># 12. Case Resolved
                elif new_status in STATUS_MAPPING[class="hl-string">'resolved']:
                    resolved_time = event_time
                    event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Case Resolved', class="hl-string">'EventTime': event_time, class="hl-string">'AssignedAgent': current_agent, class="hl-string">'CaseStatus': new_status})
                class=class="hl-string">"hl-comment"># 15. Case Reopened
                elif (old_status in STATUS_MAPPING[class="hl-string">'resolved'] or old_status in STATUS_MAPPING[class="hl-string">'closed']) and new_status not in STATUS_MAPPING[class="hl-string">'closed']:
                    event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Case Reopened', class="hl-string">'EventTime': event_time, class="hl-string">'AssignedAgent': current_agent, class="hl-string">'CaseStatus': new_status})
            
            class=class="hl-string">"hl-comment"># 3. Case Categorized And Prioritized
            elif hist[class="hl-string">'Field'] in [class="hl-string">'Priority', class="hl-string">'Type']:
                 event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Case Categorized And Prioritized', class="hl-string">'EventTime': event_time, class="hl-string">'AssignedAgent': current_agent, class="hl-string">'CaseStatus': last_status})
            
            class=class="hl-string">"hl-comment"># 9. Internal Escalation Triggered
            elif hist[class="hl-string">'Field'] == class="hl-string">'IsEscalated' and hist[class="hl-string">'NewValue'] == True:
                 event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Internal Escalation Triggered', class="hl-string">'EventTime': event_time, class="hl-string">'AssignedAgent': current_agent, class="hl-string">'CaseStatus': last_status})

        class=class="hl-string">"hl-comment"># 10. SLA Milestone Violated
        if case_id in milestones_by_case.groups:
            case_milestones = milestones_by_case.get_group(case_id)
            for _, ms in case_milestones.iterrows():
                 event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'SLA Milestone Violated', class="hl-string">'EventTime': ms[class="hl-string">'CompletionDate'], class="hl-string">'AssignedAgent': case[class="hl-string">'OwnerName'], class="hl-string">'CaseStatus': last_status})

        class=class="hl-string">"hl-comment"># 11. Solution Proposed To Customer (Inferred)
        if resolved_time and not outbound_emails.empty:
            proposal_email = outbound_emails[outbound_emails[class="hl-string">'CreatedDate'] < resolved_time]
            if not proposal_email.empty:
                 event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Solution Proposed To Customer', class="hl-string">'EventTime': proposal_email.iloc[-1][class="hl-string">'CreatedDate'], class="hl-string">'AssignedAgent': case[class="hl-string">'OwnerName'], class="hl-string">'CaseStatus': last_status})
        
        class=class="hl-string">"hl-comment"># 13. Satisfaction Survey Sent (Inferred)
        if resolved_time and not outbound_emails.empty:
            survey_email = outbound_emails[outbound_emails[class="hl-string">'CreatedDate'] > resolved_time]
            if not survey_email.empty:
                class=class="hl-string">"hl-comment"># This logic assumes any email after resolution is a survey. Customize with Subject filter if possible.
                event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Satisfaction Survey Sent', class="hl-string">'EventTime': survey_email.iloc[0][class="hl-string">'CreatedDate'], class="hl-string">'AssignedAgent': case[class="hl-string">'OwnerName'], class="hl-string">'CaseStatus': last_status})
        
        class=class="hl-string">"hl-comment"># 14. Case Closed
        if pd.notna(case[class="hl-string">'ClosedDate']):
            event_log.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Case Closed', class="hl-string">'EventTime': case[class="hl-string">'ClosedDate'], class="hl-string">'AssignedAgent': case[class="hl-string">'OwnerName'], class="hl-string">'CaseStatus': class="hl-string">'Closed'})
    
    return pd.DataFrame(event_log)

def main():
    class="hl-string">"""Main function to run the extraction process."""
    try:
        sf = Salesforce(
            username=SF_USERNAME,
            password=SF_PASSWORD,
            security_token=SF_SECURITY_TOKEN,
            consumer_key=SF_CONSUMER_KEY,
            consumer_secret=SF_CONSUMER_SECRET
        )
        print(class="hl-string">"Successfully connected to Salesforce.")
    except Exception as e:
        print(fclass="hl-string">"Failed to connect to Salesforce: {e}")
        return

    cases_df, history_df, milestones_df, emails_df = fetch_salesforce_data(sf)

    if cases_df.empty:
        print(class="hl-string">"No cases found for the specified date range. Exiting.")
        return

    print(class="hl-string">"Transforming data into event log...")
    event_log_df = transform_to_event_log(cases_df, history_df, milestones_df, emails_df)
    
    class=class="hl-string">"hl-comment"># Final data cleansing and sorting
    if not event_log_df.empty:
        event_log_df = event_log_df.sort_values(by=[class="hl-string">'ServiceRequest', class="hl-string">'EventTime']).reset_index(drop=True)
        event_log_df[class="hl-string">'EndTime'] = event_log_df[class="hl-string">'EventTime'] class=class="hl-string">"hl-comment"># Set EndTime equal to EventTime
        
        class=class="hl-string">"hl-comment"># Add missing recommended columns with nulls if not present
        recommended_cols = [class="hl-string">'SlaStatus', class="hl-string">'IsFirstContactResolution', class="hl-string">'CaseResolutionTime']
        for col in recommended_cols:
            if col not in event_log_df.columns:
                event_log_df[col] = None

        class=class="hl-string">"hl-comment"># Reorder columns to match requirements
        final_cols = [
            class="hl-string">'ServiceRequest', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'SourceSystemName', class="hl-string">'LastDataUpdateTime',
            class="hl-string">'EndTime', class="hl-string">'AssignedAgent', class="hl-string">'CasePriority', class="hl-string">'CommunicationChannel', class="hl-string">'CaseStatus',
            class="hl-string">'CaseType', class="hl-string">'SlaTargetResolutionTime', class="hl-string">'CaseResolutionTime', class="hl-string">'SlaStatus', class="hl-string">'IsFirstContactResolution'
        ]
        event_log_df = event_log_df[[col for col in final_cols if col in event_log_df.columns]]

        class=class="hl-string">"hl-comment"># Save to CSV
        output_filename = class="hl-string">'salesforce_event_log.csv'
        event_log_df.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%S.%f%z')
        print(fclass="hl-string">"Successfully created event log with {len(event_log_df)} events.")
        print(fclass="hl-string">"File saved as {output_filename}")
    else:
        print(class="hl-string">"No events were generated.")

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