> Continuous Process Improvement > Customer Service > Salesforce Service Cloud > Data Template

Your Customer Service Data Template

Salesforce Service Cloud

Customer Service Attributes (21) Customer Service Activities (15) Extraction Guides (2)

Your Customer Service Data Template

This template provides a comprehensive guide to preparing your customer service data for process mining. It outlines the essential data attributes to collect, the key activities to track, and practical guidance on how to extract this information from your source system. Use this resource to ensure your data is ready for insightful analysis and optimization.

Recommended attributes to collect
Key activities to track
Extraction guidance

New to event logs? Learn how to create a process mining event log.

Download Template

Customer Service Attributes

These are the recommended data fields to include in your event log for comprehensive analysis of your customer service process.

5 Required 10 Recommended 6 Optional

	Name	Description
	Service Request ServiceRequest	The unique identifier for a customer service case or ticket, linking all related activities.
Description The Service Request, often referred to as a Ticket or Case, serves as the primary identifier, linking all activities related to a single customer inquiry or issue. In Salesforce Service Cloud, this corresponds to the Case Number. This attribute is essential for reconstructing the end-to-end journey of each customer interaction. It allows the process mining tool to group all related events, such as 'Case Created', 'Agent Investigates Issue', and 'Case Resolved', into a single process instance, enabling holistic analysis from start to finish. Why it matters It is the fundamental building block for process mining, as it connects all events belonging to the same customer issue into a cohesive process instance. Where to get Salesforce Case object, field: CaseNumber Examples 000010240000105800001193
	Activity Name ActivityName	The name of a specific business event or step that occurred within the customer service process.
Description This attribute describes a single step or milestone in the lifecycle of a service request. Activities are the nodes in the process map and represent actions taken by agents, automated systems, or customers. Analyzing the sequence and frequency of activities like 'Case Created', 'Internal Escalation Triggered', and 'Case Closed' is central to process mining. It helps uncover the actual process flow, identify bottlenecks, and spot deviations from the standard operating procedure. Why it matters Activities define the steps of the process, and their sequence forms the basis of the process map and all subsequent analysis. Where to get Typically derived from a combination of fields, such as status changes on the Case object (Status field) or records in the CaseHistory or EmailMessage objects. Examples Case CreatedCase Assigned To AgentSolution Proposed To CustomerCase Closed
	Event Time EventTime	The precise timestamp indicating when an activity occurred.
Description Event Time captures the date and time that a specific activity was recorded in the system. It provides the temporal context for the entire process, allowing for the calculation of durations, waiting times, and cycle times. In analysis, this timestamp is used to order events chronologically for each service request, forming the basis for process discovery. It is critical for performance-related KPIs, such as measuring the time between 'Case Created' and 'Initial Acknowledgment Sent' or calculating the total resolution time. Why it matters This timestamp is crucial for understanding the process timeline, calculating performance metrics, and identifying delays between activities. Where to get Derived from various date fields corresponding to specific events, such as CreatedDate on the Case object or the CreatedDate on CaseHistory records. Examples 2023-04-15T10:00:00Z2023-04-15T11:23:14Z2023-04-16T09:05:30Z
	Last Data Update LastDataUpdateTime	Timestamp indicating when the data for this process was last refreshed.
Description This attribute records the timestamp of the last data extraction or update from the source system. It is a metadata field that applies to the entire dataset rather than individual events. Its primary use is to inform users about the freshness of the data they are analyzing. This is critical for ensuring that insights and decisions are based on current and relevant information, and it helps manage expectations about the data's timeliness. Why it matters It ensures transparency about the data's freshness, allowing users to understand how current their analysis is. Where to get This value is typically generated and stored during the data extraction, transformation, and loading (ETL) process. Examples 2023-10-27T04:00:00Z
	Source System SourceSystemName	The system from which the process data originated.
Description This attribute identifies the source application where the data was generated, which in this context is Salesforce Service Cloud. It is particularly useful in environments where data from multiple systems is combined for a holistic process view. Even when analyzing a single system, this attribute provides essential context about the data's origin. It helps in data governance, troubleshooting, and ensuring that analyses are based on the correct dataset. Why it matters It provides crucial context about the data's origin, which is essential for data governance and for analyses that may combine data from multiple systems. Where to get This is typically a static value added during the data extraction and transformation process to label the dataset's origin. Examples Salesforce Service CloudSFSC
	Assigned Agent AssignedAgent	The user or queue to whom the service request is currently assigned.
Description This attribute identifies the individual agent or team responsible for handling the service request at a given point in time. In Salesforce, this is typically the Case Owner. Tracking changes to this attribute is fundamental for analyzing agent performance, workload distribution, and handoffs. It helps answer questions like how many times a case is reassigned, which agents handle the most complex cases, and whether workloads are balanced across the team. This is a key attribute for the Agent Handoffs and Workload Distribution dashboards. Why it matters It is essential for analyzing agent workload, performance, and the frequency of handoffs, which directly impacts efficiency and resolution time. Where to get Salesforce Case object, field: OwnerId. This field contains the ID of a User or a Queue. Examples Sarah JonesDavid ChenTier 2 Support Queue
	Case Priority CasePriority	The priority level assigned to the service request, such as High, Medium, or Low.
Description Case Priority is a standard classification used to determine the urgency of a service request. This designation often dictates the target response and resolution times according to Service Level Agreements (SLAs). Analyzing process performance based on priority is critical for evaluating the effectiveness of the prioritization strategy. It helps determine if high-priority cases are actually resolved faster and meet their aggressive SLA targets, directly supporting the Case Prioritization and Impact dashboard. Why it matters It enables analysis of whether the process effectively prioritizes urgent cases and meets different service levels based on case importance. Where to get Salesforce Case object, field: Priority Examples HighMediumLow
	Case Resolution Time CaseResolutionTime	The total time elapsed from when a case was created until it was resolved.
Description This metric measures the total duration of a service request, from the 'Case Created' timestamp to the 'Case Resolved' timestamp. It is one of the most fundamental KPIs for any customer service process. This calculated attribute directly supports the Average Case Resolution Time KPI and is a core metric in the Case SLA Adherence and Resolution dashboard. It provides a clear measure of overall process efficiency and customer experience. Analyzing this metric across different dimensions like case type or priority helps pinpoint areas for improvement. Why it matters This is a primary KPI for measuring overall process efficiency and is essential for understanding the customer's end-to-end experience. Where to get Calculated during data transformation by finding the time difference between the timestamp of the 'Case Created' activity and the 'Case Resolved' activity for each Service Request. Examples 25920000086400000604800000
	Case Status CaseStatus	The current status of the service request in its lifecycle, such as New, In Progress, or Closed.
Description The Case Status indicates the current state of a service request. Status changes are often the primary source for identifying activities in the process, for example, a change from 'New' to 'Working' can be mapped to the 'Agent Investigates Issue' activity. Analyzing the final status of cases helps in understanding resolution outcomes. It is also used to filter for open or closed cases and to identify cases that may be stalled in a particular state for too long. Why it matters It provides insight into the case's current state and outcome, and status changes are often used to derive the sequence of activities. Where to get Salesforce Case object, field: Status Examples NewWorkingWaiting on CustomerClosed
	Case Type CaseType	The classification of the service request, such as Question, Problem, or Feature Request.
Description Case Type is a categorical attribute that helps classify the nature of the customer's inquiry or issue. This allows for segmentation of service requests to understand how different types of issues are handled. Analyzing the process based on Case Type can reveal that certain types of requests follow different paths, have longer resolution times, or require more handoffs. This insight is valuable for tailoring and improving the process for specific categories of work. Why it matters It enables segmentation of the process to understand if different types of requests are handled differently and to identify areas for specialized improvement. Where to get Salesforce Case object, field: Type Examples ProblemFeature RequestQuestion
	Communication Channel CommunicationChannel	The channel through which the service request was initiated, such as email, phone, or web.
Description This attribute specifies the method of communication used by the customer to submit their service request. In Salesforce, this is often captured in the Case Origin field. Understanding the communication channel is key to optimizing resource allocation and improving service efficiency. By analyzing resolution times and process flows by channel, organizations can identify which channels are most effective for different types of issues, supporting the Communication Channel Efficiency dashboard. Why it matters It allows for performance comparison across different customer contact channels, helping to optimize channel strategy and resource allocation. Where to get Salesforce Case object, field: Origin Examples EmailPhoneWebChat
	End Time EndTime	The timestamp indicating when an activity was completed.
Description The End Time represents the completion time of an activity. When combined with the Start Time, it allows for the precise calculation of activity processing times. For many events in Salesforce, the event is instantaneous, meaning the Start Time and End Time are identical. However, for long-running activities, having a distinct End Time is crucial for accurately measuring the time spent on a task. It directly supports performance analysis by distinguishing active processing time from idle waiting time between steps. Why it matters It enables the calculation of an activity's true duration, which is essential for analyzing agent performance and identifying time-consuming steps. Where to get This is often the same as the StartTime for instantaneous events. For activities with a duration, it must be sourced from a specific field indicating completion, which may require custom configuration. Examples 2023-04-15T10:00:00Z2023-04-15T11:55:00Z2023-04-16T14:20:00Z
	Is First Contact Resolution IsFirstContactResolution	A flag indicating if the case was resolved during the first interaction with the customer.
Description This boolean attribute identifies service requests that were resolved without requiring any follow-up after the initial customer contact. The logic for determining this often involves analyzing the sequence and timing of activities. A high First Contact Resolution (FCR) rate is a strong indicator of an efficient and effective customer service process. This calculated attribute is the basis for the First Contact Resolution Rate KPI. Analyzing the cases that are not resolved on first contact can reveal opportunities for improving agent training, knowledge base content, and process design. Why it matters It directly measures a key aspect of service efficiency and customer satisfaction, helping to identify drivers of effective problem-solving. Where to get Calculated during data transformation by analyzing the event log for a given case. A common rule is to check if 'Case Resolved' occurs before any 'Information Requested From Customer' or subsequent customer contact activities. Examples truefalse
	SLA Status SlaStatus	Indicates whether the case was resolved within its SLA target.
Description This attribute categorizes each resolved case based on its adherence to the defined Service Level Agreement. It is calculated by comparing the actual resolution timestamp to the 'SLA Target Resolution Time'. As a direct input to the SLA Adherence Rate KPI, this attribute simplifies performance monitoring. It allows for quick filtering and analysis of all non-compliant cases to identify common root causes for SLA breaches, such as specific case types, products, or process bottlenecks. Why it matters It provides a clear, binary outcome for SLA compliance on a per-case basis, which is fundamental for performance monitoring and root cause analysis of delays. Where to get Calculated in the data transformation layer by comparing the 'Case Resolved' timestamp against the 'SlaTargetResolutionTime' attribute. Examples MetViolated
	SLA Target Resolution Time SlaTargetResolutionTime	The target date and time by which the service request should be resolved, according to the SLA.
Description This attribute defines the service level agreement (SLA) deadline for resolving a case. It is typically determined based on factors like case priority, type, and customer tier. Salesforce can automatically calculate this using its entitlement and milestone features. This timestamp is essential for measuring SLA adherence. By comparing the actual resolution time with this target, the organization can calculate its SLA Adherence Rate KPI and identify cases that are at risk of breaching their SLA, directly supporting the Case SLA Adherence dashboard. Why it matters It provides the benchmark against which actual performance is measured, making it critical for calculating SLA compliance and identifying at-risk cases. Where to get This is often stored in the SlaExitDate field on the Case Milestone object, which is part of Salesforce's Entitlement Management feature. Examples 2023-04-16T17:00:00Z2023-04-18T09:00:00Z2023-05-01T12:00:00Z
	Agent Handoff Count AgentHandoffCount	The number of times a case was reassigned from one agent or queue to another.
Description This metric quantifies the number of reassignments a service request undergoes before it is resolved. It is calculated by counting the number of distinct changes in the 'AssignedAgent' attribute within the lifecycle of a single case. This attribute is the basis for the Average Agent Handoffs per Case KPI and is visualized in the Agent Handoffs and Rework Patterns dashboard. A high handoff count often signals process inefficiencies, unclear ownership, or knowledge gaps, all of which can lead to longer resolution times and customer frustration. Why it matters It quantifies internal friction in the process, as excessive handoffs are a common source of delays and customer dissatisfaction. Where to get Calculated at the case level during data transformation by counting the unique number of values in the 'AssignedAgent' field for each 'ServiceRequest', minus one. Examples 0135
	Case Subject CaseSubject	A brief summary or title of the customer's issue or request.
Description The Case Subject provides a concise, free-text summary of the service request. It is often the email subject line or a title entered by the agent or customer. While not a structured field for direct process analysis, the subject line contains rich contextual information. It can be used with text mining techniques to categorize cases, identify emerging issues, or add qualitative context to quantitative process analysis. Why it matters It offers quick, high-level context about the case, which is useful for drill-down analysis and can be leveraged for text mining. Where to get Salesforce Case object, field: Subject Examples Unable to login to portalQuestion about billing statementFeature request for reporting dashboard
	Customer Name CustomerName	The name of the customer or account associated with the service request.
Description This attribute identifies the customer who initiated the service request. In Salesforce, this could be a Contact (an individual) or an Account (a company). Analyzing the process from a customer view can provide valuable insights. For example, it can help identify if certain customers experience more issues or longer resolution times. This can inform customer relationship management strategies and highlight opportunities for proactive support. Why it matters It allows for customer-centric analysis, helping to identify patterns and performance variations for specific customers or customer segments. Where to get Salesforce Case object, fields: ContactId or AccountId, which link to the Contact and Account objects respectively. Examples Global Tech Inc.Emily WhiteInnovate Solutions
	Is Escalated IsEscalated	A flag indicating whether the case has been escalated.
Description This boolean attribute indicates if a service request has undergone an escalation. In Salesforce, this can be tracked via the standard IsEscalated field, which is automatically set based on Escalation Rules. This flag is a direct input for the Internal Escalation Analysis dashboard and the Internal Escalation Rate KPI. It allows for easy filtering and aggregation of all escalated cases, helping to quantify their frequency and analyze their impact on overall resolution times and process complexity. Why it matters It directly measures case escalations, a key indicator of process friction, complexity, or agent knowledge gaps. Where to get Salesforce Case object, field: IsEscalated Examples truefalse
	Is Rework IsRework	A flag indicating if a case involved rework or looping back to a previous stage.
Description This boolean attribute flags cases that exhibit patterns of rework, such as being reopened after closure or cycling repeatedly between 'Agent Investigates' and 'Solution Proposed'. The logic to identify rework requires analyzing the sequence of activities for undesirable loops. This attribute directly supports the Rework Rate KPI and the Agent Handoffs and Rework Patterns dashboard. Pinpointing cases with rework allows analysts to investigate the root causes, which could range from inadequate solutions to incomplete information gathering, and take corrective action. Why it matters It highlights process inefficiency and wasted effort, allowing for targeted analysis of activities that are frequently repeated. Where to get Calculated during data transformation by analyzing activity sequences. For example, a sequence like 'Case Closed' -> 'Case Reopened' or 'Solution Proposed' -> 'Agent Investigates Issue' within the same case would flag it as rework. Examples truefalse
	Product Involved ProductInvolved	The specific product or service that the customer's request is about.
Description This attribute links a service request to a specific product or service. In Salesforce, this is often managed through a lookup to the Product object. Segmenting process data by product is crucial for product quality analysis. It helps identify which products generate the most support requests, what kind of issues they have, and if the resolution process differs across product lines. This information is vital feedback for product development teams. Why it matters It enables product-based process analysis, which can uncover product quality issues or support gaps for specific products. Where to get Salesforce Case object, field: ProductId. This is a standard but optional lookup field. Examples Alpha CRM SuiteBeta Analytics ToolGamma Data Connector

Required Recommended Optional

Customer Service Activities

These are the key process steps and milestones to capture in your event log for accurate discovery and analysis of your customer service workflows.

6 Recommended 9 Optional

	Activity	Description
	Case Assigned To Agent	Marks the point when a service request is assigned to a specific agent or queue for handling. This is a fundamental step in the process, captured by a change in the case ownership field.
Why it matters This activity is essential for tracking agent workload, assignment delays, and handoff frequency. It is the basis for analyzing agent performance and identifying bottlenecks in the assignment process. Where to get Inferred from a change to the OwnerId field on the Case object. The timestamp of this change is recorded in the CaseHistory object. Capture Identify the timestamp of the first update to the OwnerId field. Event type inferred
	Case Closed	This activity represents the final, definitive end of the service request lifecycle. Once a case is closed, no further work is expected unless it is reopened.
Why it matters As the terminal event, this activity provides the final endpoint for calculating the full case duration. The time between 'Resolved' and 'Closed' can also reveal inefficiencies in the closing process. Where to get Inferred from the timestamp when the IsClosed field on the Case object is set to true. The ClosedDate field is also populated at this time. Capture Timestamp when the IsClosed field changes to true, logged in CaseHistory. Event type inferred
	Case Created	This activity marks the official start of the customer service process when a new service request, or case, is logged in Salesforce. This event is explicitly captured when a new record is created in the Case object, with a precise timestamp.
Why it matters As the primary start event, this activity is essential for calculating the overall case lifecycle and resolution times. It provides the baseline for measuring performance against SLAs and understanding case volume. Where to get This event corresponds to the record creation event for the Case object. The timestamp is captured in the standard CreatedDate field. Capture Directly from the creation timestamp of the Case record. Event type explicit
	Case Resolved	This key milestone signifies that the agent has completed the work required to resolve the customer's issue. The case is now pending final closure, which may be automated after a certain period.
Why it matters This is a primary endpoint for measuring resolution time and is critical for calculating SLA adherence. It marks the end of active work on the case. Where to get Inferred from the CaseHistory object when the Status field is changed to 'Resolved'. The IsClosed field may still be false at this point. Capture Timestamp of Case Status change to 'Resolved'. Event type inferred
	Internal Escalation Triggered	This activity occurs when a case requires a higher level of support or expertise and is formally escalated. This can be captured explicitly by Salesforce's escalation rules or inferred from a field change.
Why it matters Analyzing escalations helps identify complex case types, training needs for front-line agents, and systemic issues. It is a key indicator of process friction and its impact on resolution time. Where to get This can be an explicit event from CaseEscalation records. More commonly, it is inferred from the CaseHistory when a checkbox field like 'IsEscalated' is set to true. Capture Timestamp of the 'IsEscalated' field changing to true. Event type inferred
	SLA Milestone Violated	This event is triggered when a service commitment, such as a first response or resolution time, is not met. Salesforce tracks this using entitlement processes and milestone records.
Why it matters This activity is critical for SLA adherence analysis and compliance monitoring. It directly highlights cases that failed to meet customer expectations, enabling targeted process improvement. Where to get Captured from the CaseMilestone object. An event is generated when the IsViolated field on a milestone record is set to true. Capture Event is the timestamp when the IsViolated flag becomes true on a CaseMilestone record. Event type explicit
	Agent Investigates Issue	Represents the phase where an agent actively works on diagnosing and understanding the customer's issue. This is not an explicit event but is inferred when the case status changes from a new or open state to an in-progress state.
Why it matters Understanding the duration of the investigation phase helps identify complexities in service requests and areas where agents may need more training or resources. It separates active work time from wait time. Where to get Inferred from the CaseHistory object when the Status field is updated to a value indicating active work, such as 'In Progress' or 'Working'. Capture Timestamp of Case Status change to an 'in-progress' value. Event type inferred
	Case Categorized And Prioritized	This activity occurs when a case is classified with a type, category, and priority level. This is often done by a triage team or automation rules and is inferred from changes to relevant case fields.
Why it matters Categorization is key to understanding case distribution and routing effectiveness. Analyzing this step helps in optimizing workload distribution and ensuring high-priority cases are handled promptly. Where to get Inferred from the CaseHistory object by tracking the first time the Priority, Type, or other classification fields are populated or changed from their default values. Capture Track updates to fields like Priority or Type in the CaseHistory. Event type inferred
	Case Reassigned	Represents a handoff where a case is transferred from one agent or queue to another after the initial assignment. This is inferred by subsequent changes to the case ownership field.
Why it matters Frequent reassignments can indicate incorrect initial routing, process inefficiencies, or knowledge gaps, leading to delays and poor customer experience. This activity helps quantify agent handoffs. Where to get Inferred from any change to the OwnerId field in the CaseHistory object after the initial assignment. Capture Identify all updates to the OwnerId field after the first one. Event type inferred
	Case Reopened	Occurs when a previously resolved or closed case is reactivated due to the issue recurring or the solution being ineffective. This is a key indicator of rework.
Why it matters Reopened cases are a strong signal of low-quality resolutions or First Contact Resolution failures. Analyzing their frequency and root causes is essential for improving service quality. Where to get Inferred from the CaseHistory object when the IsClosed field changes from true to false, or the Status changes from a closed/resolved state back to an open state. Capture Timestamp of Status or IsClosed field changing from a terminal to an active state. Event type inferred
	Information Received From Customer	This activity marks the end of a customer wait time, when the customer provides the requested information. It's often inferred when a case status is changed back to an active state following an inbound communication.
Why it matters Tracking this event is crucial for understanding customer response times and their impact on the overall case lifecycle. It signals the resumption of active work by the agent. Where to get Inferred by the timestamp of an inbound EmailMessage associated with the case, or by a status change from 'Waiting on Customer' back to 'In Progress' in the CaseHistory object. Capture Timestamp of inbound email or status change from 'waiting'. Event type inferred
	Information Requested From Customer	This activity occurs when an agent requires additional information from the customer to proceed with the case. It is typically inferred from a change in the case status to a 'waiting on customer' state.
Why it matters This activity marks the beginning of a wait time, which is not under the agent's control. Isolating this time is critical for accurately measuring internal process efficiency and agent performance. Where to get Inferred from the CaseHistory object when the Status field is updated to a value like 'Waiting on Customer Response' or 'Pending'. Capture Timestamp of Case Status change to a 'waiting' value. Event type inferred
	Initial Acknowledgment Sent	Represents the first communication sent to the customer, acknowledging receipt of their service request. This is typically an automated email triggered by case creation rules, and is inferred by identifying the first outbound communication.
Why it matters Tracking this activity is crucial for measuring initial response times and ensuring customers are promptly informed. It helps analyze the 'Initial Acknowledgment Latency' KPI to improve customer communication. Where to get Inferred from the timestamp of the first outbound EmailMessage record associated with the Case. Can also be inferred from a custom field update triggered by an automation rule. Capture Identify first outbound EmailMessage timestamp linked to the Case. Event type inferred
	Satisfaction Survey Sent	Represents the sending of a customer satisfaction (CSAT) or Net Promoter Score (NPS) survey. This is typically an automated action that follows case resolution or closure.
Why it matters While not part of the core resolution process, this activity is important for understanding the feedback loop. Analyzing its timing and frequency ensures the voice of the customer is consistently captured. Where to get Inferred from an outbound EmailMessage record associated with the case that matches a survey template. Alternatively, it can be captured from the creation of a SurveyInvitation record. Capture Identify outbound survey email or SurveyInvitation record creation. Event type inferred
	Solution Proposed To Customer	Indicates the point at which an agent has provided a potential solution to the customer. This is a conceptual step that is typically inferred from an outbound communication or a specific status change.
Why it matters Tracking this milestone helps measure the time from investigation to proposed solution. It can also be the start of a confirmation loop if the solution is not accepted. Where to get Inferred from an outbound EmailMessage with specific keywords or from a status change to 'Solution Provided' in the CaseHistory object. Capture Timestamp of status change or outbound communication event. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from Salesforce Service Cloud

Steps

Log into Salesforce: Access your Salesforce instance with a user account that has API access and permission to read the required objects and fields.
Open the Developer Console: Click the gear icon in the top right corner and select 'Developer Console'.
Navigate to the Query Editor: In the Developer Console, click on the 'Query Editor' tab at the bottom of the window.
Prepare the SOQL Query: Copy the complete SOQL query provided in the 'query' section of this document.
Set Date Parameters: Before executing, replace the placeholder values :startDate and :endDate in the query with your desired date range in ISO 8601 format (e.g., 2023-01-01T00:00:00Z).
Execute the Query: Paste the modified query into the Query Editor pane and click the 'Execute' button. If the query is too large or complex, you may need to use a tool like Workbench or the Salesforce CLI which have higher limits.
Handle Large Datasets: For datasets exceeding 50,000 records, the Developer Console may be insufficient. Use Salesforce Data Loader or the Bulk API 2.0. You can submit the same query through these tools to process the extraction in asynchronous batches.
Review the Results: The query results will appear in the 'Query Results' section. You can browse the data here to ensure it looks correct.
Export the Data: Click the 'Save Rows' button to export the full result set as a CSV file.
Format for ProcessMind: Ensure the CSV file headers match the attribute names required by ProcessMind. The EventTime and other timestamp fields should be in a consistent format, such as YYYY-MM-DDTHH:MI:SS.mmmZ. The query is designed to produce the correct column names and formats directly.
Save and Upload: Save the final CSV file and upload it to your ProcessMind project.

Configuration

User Permissions: The executing user needs 'API Enabled' permission, along with read access to the Case, CaseHistory, EmailMessage, and CaseMilestone objects and all fields included in the query.
Query Tooling: While the Developer Console is suitable for smaller queries and testing, it is recommended to use Workbench, Salesforce CLI (sfdx), or Data Loader for production extractions, as they handle larger data volumes and avoid browser timeouts.
Date Range: Filter the query by Case.CreatedDate to limit the scope of the extraction. A typical range for process mining is 3 to 12 months of data. Be aware that querying a very long time frame can lead to performance issues.
Status Value Placeholders: The query contains several placeholders like [Your In Progress Status] or [Your Resolved Status]. These must be replaced with the actual 'Status' field picklist values used in your specific Salesforce configuration.
Field History Tracking: This extraction relies heavily on field history tracking for the Case object. Ensure that tracking is enabled for key fields like Status, OwnerId, Priority, and IsEscalated to capture these events accurately.
Governor Limits: Salesforce SOQL queries are subject to governor limits, such as a maximum execution time. If your query times out, you may need to reduce the date range or simplify the query by breaking it into smaller parts.

a Sample Query sql

/* 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'

Steps

Set up Salesforce Connected App: Before running the script, you must configure a Connected App in Salesforce to enable API access. Navigate to Setup > Apps > App Manager and create a new Connected App. Enable OAuth Settings, select 'Full access (full)' for OAuth Scopes, and note the Consumer Key and Consumer Secret.
Install Python Libraries: Ensure you have Python installed. Then, install the necessary libraries using pip: pip install simple-salesforce pandas.
Gather Salesforce Credentials: You will need your Salesforce username, password, and a security token. To get your security token, go to your personal settings in Salesforce and find the 'Reset My Security Token' option. Store these credentials securely, for example, as environment variables, rather than hardcoding them in the script.
Configure the Python Script: Copy the provided Python script into a file (e.g., extract_sfdc.py). Update the placeholder variables at the top of the script with your Salesforce username, password, security token, consumer key, and consumer secret.
Define Extraction Parameters: Modify the SOQL_DATE_FILTER variable in the script to specify the desired date range for the case extraction. It is recommended to start with a smaller range, such as one month, to test the script.
Customize Status Values: Review the STATUS_MAPPING dictionary in the script. This dictionary maps conceptual process steps to the actual status values used in your Salesforce instance. You must update these values to match your organization's specific configuration.
Execute the Extraction Script: Run the script from your command line using python extract_sfdc.py. The script will connect to the Salesforce API, fetch data for Cases, Case History, and Case Milestones, and then transform this data into an event log.
Review Script Output: The script will print progress updates to the console, including the number of cases and events processed. Monitor for any potential errors during execution.
Process the Generated CSV: Once the script completes successfully, it will create a file named salesforce_event_log.csv in the same directory. This file contains the structured event log.
Prepare for Upload: Before uploading to ProcessMind, open the CSV file to ensure all columns, including ServiceRequest, ActivityName, and EventTime, are populated correctly. The file is now ready for analysis.

Configuration

Prerequisites: You need a Salesforce user account with API access and read permissions for the Case, CaseHistory, CaseMilestone, User, and EmailMessage objects. A Salesforce Connected App must be configured for OAuth 2.0 authentication.
Authentication: The script uses the username, password, security token, and Connected App credentials (Consumer Key, Consumer Secret) for authentication. Storing these as environment variables is highly recommended for security.
Date Range: The data extraction period is controlled by the SOQL_DATE_FILTER variable within the script. For initial extractions, a period of 3-6 months of data is recommended to provide a meaningful dataset without excessive load times.
Key Salesforce Objects: The script primarily queries the following Salesforce objects to construct the event log:
- Case: For core case attributes and creation events.
- CaseHistory: For tracking changes to key fields like Status, OwnerId, Priority, and IsEscalated.
- CaseMilestone: To identify SLA violations.
- EmailMessage: To infer communication-related activities.
- User: To retrieve the names of assigned agents.
Customization: The STATUS_MAPPING variable in the script must be configured to match the custom status values used in your specific Salesforce implementation. Without this, activities like 'Agent Investigates Issue' or 'Case Resolved' will not be captured correctly.
Performance: Extracting data from CaseHistory for a large number of cases over a long period can be resource-intensive and slow. It may also consume a significant portion of your daily API call limits. Consider running the extraction during off-peak hours or implementing an incremental loading strategy for production environments.

a Sample Query python

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()

Your Customer Service Data Template

Your Customer Service Data Template

Customer Service Attributes

Customer Service Activities

Extraction Guides

Direct Salesforce Object Query Language (SOQL) Queries

Steps

Configuration

a Sample Query sql

Python Script Extraction via Salesforce REST API

Steps

Configuration

a Sample Query python

Ready to Get Started?

Unlock Peak Customer Service Efficiency Now

Your Customer Service Data Template

Your Customer Service Data Template

Customer Service Attributes

Customer Service Activities

Extraction Guides

Direct Salesforce Object Query Language (SOQL) Queries

Steps

Configuration

a Sample Query sql

Python Script Extraction via Salesforce REST API

Steps

Configuration

a Sample Query python

We value your privacy

Ready to Get Started?

Unlock Peak Customer Service Efficiency Now