Hizmet Talebi Yönetimi Veri Şablonunuz

/*
This extraction method uses the Freshservice UI. The following describes the configuration
and post-processing logic required to generate the event log.
*/

-- Step 1: Configuration for Report 1 (Creation Events)
MODULE: Tickets
WIDGET_TYPE: Data Table
COLUMNS:
  - Ticket ID
  - Created at
  - Assigned to
  - Group
  - Priority
  - Status
  - Category
FILTERS:
  - Created at IS WITHIN [Specify Start Date] and [Specify End Date]
  - Type EQUALS 'Service Request'

-- Step 2: Configuration for Report 2 (Update Events)
MODULE: Ticket Activities
WIDGET_TYPE: Data Table
COLUMNS:
  - Ticket ID
  - Activity Date
  - Activity Type
  - Field (from Changes)
  - Old Value (from Changes)
  - New Value (from Changes)
  - Performed by
  - Group (ticket attribute)
  - Assigned to (ticket attribute)
  - Priority (ticket attribute)
  - Status (ticket attribute)
  - Category (ticket attribute)
FILTERS:
  - Activity Date IS WITHIN [Specify Start Date] and [Specify End Date]
  - Ticket Type EQUALS 'Service Request'
  - Activity Type IN ('Status changed', 'Priority changed', 'Group changed', 'Agent changed', 'Private Note added', 'Public Note added')

-- Step 3: Post-Processing Logic to create the final event log file
/* 
Apply this logic to each row of the combined data from the two exported CSV files.
The logic uses a CASE statement structure to map raw data to the final ActivityName.
*/

-- Column Mapping Logic:
`ServiceRequestId` = `Ticket ID`
`EventTime` = IF row is from creation_events.csv THEN `Created at` ELSE `Activity Date`
`SourceSystem` = 'Freshservice'
`LastDataUpdate` = CURRENT_TIMESTAMP()
`AssignedAgent` = `Assigned to`
`AssignedTeam` = `Group`
`Priority` = `Priority`
`Status` = `Status`
`ServiceType` = `Category`

-- ActivityName Mapping Logic:
CASE
  WHEN row is from creation_events.csv THEN 'Service Request Created'
  WHEN `Activity Type` = 'Group changed' AND `Old Value` IS NULL THEN 'Request Triaged'
  WHEN `Activity Type` = 'Priority changed' THEN 'Request Prioritized'
  WHEN `Activity Type` = 'Agent changed' AND `Old Value` IS NULL THEN 'Request Assigned to Agent'
  WHEN `Activity Type` IN ('Agent changed', 'Group changed') AND `Old Value` IS NOT NULL THEN 'Request Reassigned'
  WHEN `Activity Type` = 'Status changed' AND `New Value` IN ('Pending Customer', 'Awaiting Customer Response') THEN 'Information Requested from Requestor'
  WHEN `Activity Type` = 'Status changed' AND `Old Value` IN ('Pending Customer', 'Awaiting Customer Response') AND `New Value` = 'Open' THEN 'Information Provided by Requestor'
  WHEN `Activity Type` = 'Status changed' AND `New Value` IN ('Pending Vendor', 'Awaiting Third Party') THEN 'External Vendor Engaged'
  WHEN `Activity Type` = 'Status changed' AND `New Value` IN ('Pending Approval', 'Internal Review') THEN 'Internal Review Performed'
  WHEN `Activity Type` = 'Private Note added' AND `Note Content` CONTAINS 'SLA Breached' THEN 'SLA Target Breached' -- Requires custom automation
  WHEN `Activity Type` IN ('Private Note added', 'Public Note added') THEN 'Note Added'
  WHEN `Activity Type` = 'Status changed' AND `Old Value` IN ('Resolved', 'Closed') AND `New Value` IN ('Open', 'In Progress') THEN 'Service Request Reopened'
  WHEN `Activity Type` = 'Status changed' AND `New Value` = 'Resolved' THEN 'Service Request Resolved'
  WHEN `Activity Type` = 'Private Note added' AND `Note Content` CONTAINS 'Resolution Confirmed' THEN 'Resolution Confirmed by Requestor' -- Requires custom automation
  WHEN `Activity Type` = 'Status changed' AND `New Value` = 'Closed' THEN 'Service Request Closed'
  ELSE NULL -- Filter out these rows
END AS ActivityName

import requests
import pandas as pd
import time
import os
from datetime import datetime

class=class="hl-string">"hl-comment"># --- CONFIGURATION ---
class=class="hl-string">"hl-comment"># Replace with your Freshservice domain and API key
FRESHSERVICE_DOMAIN = class="hl-string">"[your_domain].freshservice.com" 
API_KEY = class="hl-string">"[YOUR_API_KEY]"

class=class="hl-string">"hl-comment"># Set the date range for the extraction (YYYY-MM-DD)
START_DATE = class="hl-string">"2023-01-01"
END_DATE = class="hl-string">"2023-12-31"

class=class="hl-string">"hl-comment"># --- Map your custom status names to their numeric IDs in Freshservice ---
class=class="hl-string">"hl-comment"># To find these IDs, you may need to inspect API responses or admin settings
class=class="hl-string">"hl-comment"># Common defaults: Open: 2, Pending: 3, Resolved: 4, Closed: 5
STATUS_MAPPING = {
    class="hl-string">'Open': 2,
    class="hl-string">'Pending': 3, 
    class="hl-string">'Resolved': 4, 
    class="hl-string">'Closed': 5,
    class="hl-string">'Awaiting Customer': 3, class=class="hl-string">"hl-comment"># Example, often maps to Pending
    class="hl-string">'Pending Vendor': [6], class=class="hl-string">"hl-comment"># Replace with your actual ID(s) for this status
    class="hl-string">'Pending Approval': [7]  class=class="hl-string">"hl-comment"># Replace with your actual ID(s) for this status
}
class=class="hl-string">"hl-comment"># --- END CONFIGURATION ---

BASE_URL = fclass="hl-string">"https://{FRESHSERVICE_DOMAIN}/api/v2"
AUTH = (API_KEY, class="hl-string">"X")

class=class="hl-string">"hl-comment"># Caching for agent and group lookups to reduce API calls
agent_cache = {}
group_cache = {}

def get_api_data(url):
    class="hl-string">"""Helper function to handle API requests and rate limiting."""
    while True:
        try:
            response = requests.get(url, auth=AUTH)
            response.raise_for_status() class=class="hl-string">"hl-comment"># Raise an exception for bad status codes
            return response.json()
        except requests.exceptions.HTTPError as e:
            if e.response.status_code == 429: class=class="hl-string">"hl-comment"># Rate limit exceeded
                print(class="hl-string">"Rate limit exceeded. Waiting for 60 seconds...")
                time.sleep(60)
                continue
            else:
                print(fclass="hl-string">"HTTP Error: {e}")
                return None
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Request Error: {e}")
            return None

def get_agent_name(agent_id):
    class="hl-string">"""Fetch agent name by ID with caching."""
    if not agent_id: return None
    if agent_id in agent_cache:
        return agent_cache[agent_id]
    data = get_api_data(fclass="hl-string">"{BASE_URL}/agents/{agent_id}")
    if data and class="hl-string">'agent' in data:
        name = data[class="hl-string">'agent'][class="hl-string">'first_name'] + class="hl-string">' ' + data[class="hl-string">'agent'][class="hl-string">'last_name']
        agent_cache[agent_id] = name
        return name
    return None

def get_group_name(group_id):
    class="hl-string">"""Fetch group name by ID with caching."""
    if not group_id: return None
    if group_id in group_cache:
        return group_cache[group_id]
    data = get_api_data(fclass="hl-string">"{BASE_URL}/groups/{group_id}")
    if data and class="hl-string">'group' in data:
        name = data[class="hl-string">'group'][class="hl-string">'name']
        group_cache[group_id] = name
        return name
    return None

def parse_iso_datetime(dt_string):
    if not dt_string: return None
    return datetime.fromisoformat(dt_string.replace(class="hl-string">'Z', class="hl-string">'+00:00'))

def main():
    event_log = []
    last_data_update = datetime.now().isoformat()

    page = 1
    while True:
        print(fclass="hl-string">"Fetching tickets, page {page}...")
        query = fclass="hl-string">'"created_at:>\\'{START_DATE}\\class="hl-string">' AND created_at:<\\'{END_DATE}\\class="hl-string">' AND ticket_type:\\\'Service Request\\\class="hl-string">'"'
        url = fclass="hl-string">"{BASE_URL}/tickets?query={query}&page={page}&per_page=100"

        data = get_api_data(url)

        if not data or class="hl-string">'tickets' not in data or not data[class="hl-string">'tickets']:
            break

        tickets = data[class="hl-string">'tickets']

        for ticket in tickets:
            service_request_id = ticket[class="hl-string">'id']
            created_at = parse_iso_datetime(ticket[class="hl-string">'created_at'])
            due_by = parse_iso_datetime(ticket[class="hl-string">'due_by'])
            resolved_at = parse_iso_datetime(ticket[class="hl-string">'resolved_at'])
            closed_at = parse_iso_datetime(ticket[class="hl-string">'closed_at'])

            class=class="hl-string">"hl-comment"># --- Activity: Service Request Created ---
            event_log.append({
                class="hl-string">'ServiceRequestId': service_request_id,
                class="hl-string">'ActivityName': class="hl-string">'Service Request Created',
                class="hl-string">'EventTime': created_at,
                class="hl-string">'SourceSystem': class="hl-string">'Freshservice',
                class="hl-string">'LastDataUpdate': last_data_update,
                class="hl-string">'AssignedAgent': get_agent_name(ticket.get(class="hl-string">'responder_id')),
                class="hl-string">'AssignedTeam': get_group_name(ticket.get(class="hl-string">'group_id')),
                class="hl-string">'Priority': ticket[class="hl-string">'priority'],
                class="hl-string">'Status': ticket[class="hl-string">'status'],
                class="hl-string">'ServiceType': ticket.get(class="hl-string">'category')
            })
            
            class=class="hl-string">"hl-comment"># Fetch conversations for detailed history
            conv_url = fclass="hl-string">"{BASE_URL}/tickets/{service_request_id}/conversations"
            conversations = get_api_data(conv_url)
            
            if not conversations or class="hl-string">'conversations' not in conversations:
                continue
                
            class=class="hl-string">"hl-comment"># Sort conversations chronologically
            sorted_convos = sorted(conversations[class="hl-string">'conversations'], key=lambda x: parse_iso_datetime(x[class="hl-string">'created_at']))

            initial_group_assigned = False
            initial_priority_set = False
            initial_agent_assigned = False
            triaged = False

            for conv in sorted_convos:
                conv_time = parse_iso_datetime(conv[class="hl-string">'created_at'])

                class=class="hl-string">"hl-comment"># --- Activity: Note Added ---
                if conv[class="hl-string">'body_text']:
                     event_log.append({
                        class="hl-string">'ServiceRequestId': service_request_id,
                        class="hl-string">'ActivityName': class="hl-string">'Note Added',
                        class="hl-string">'EventTime': conv_time,
                        class="hl-string">'SourceSystem': class="hl-string">'Freshservice',
                        class="hl-string">'LastDataUpdate': last_data_update,
                        class="hl-string">'AssignedAgent': get_agent_name(ticket.get(class="hl-string">'responder_id')),
                        class="hl-string">'AssignedTeam': get_group_name(ticket.get(class="hl-string">'group_id')),
                        class="hl-string">'Priority': ticket[class="hl-string">'priority'],
                        class="hl-string">'Status': ticket[class="hl-string">'status'],
                        class="hl-string">'ServiceType': ticket.get(class="hl-string">'category')
                    })

                class=class="hl-string">"hl-comment"># Process system activities from the activity log within conversations
                if class="hl-string">'activities' in conv and conv[class="hl-string">'activities']:
                    for activity in conv[class="hl-string">'activities']:
                        shipping_info = activity.get(class="hl-string">'shipping_info', {})
                        changes = shipping_info.get(class="hl-string">'changes', {})
                        
                        class=class="hl-string">"hl-comment"># --- Priority Change Activities ---
                        if class="hl-string">'priority' in changes:
                            event_log.append({
                                class="hl-string">'ServiceRequestId': service_request_id,
                                class="hl-string">'ActivityName': class="hl-string">'Request Prioritized',
                                class="hl-string">'EventTime': conv_time,
                                class="hl-string">'SourceSystem': class="hl-string">'Freshservice',
                                class="hl-string">'LastDataUpdate': last_data_update,
                                class="hl-string">'AssignedAgent': get_agent_name(ticket.get(class="hl-string">'responder_id')),
                                class="hl-string">'AssignedTeam': get_group_name(ticket.get(class="hl-string">'group_id')),
                                class="hl-string">'Priority': changes[class="hl-string">'priority'][1], class=class="hl-string">"hl-comment"># New value
                                class="hl-string">'Status': ticket[class="hl-string">'status'],
                                class="hl-string">'ServiceType': ticket.get(class="hl-string">'category')
                            })
                            if not initial_priority_set and not triaged:
                                initial_priority_set = True
                                triaged = True
                                event_log.append({**event_log[-1], class="hl-string">'ActivityName': class="hl-string">'Request Triaged'})
                        
                        class=class="hl-string">"hl-comment"># --- Assignment Change Activities ---
                        if class="hl-string">'responder_id' in changes:
                            activity_name = class="hl-string">'Request Assigned to Agent' if not initial_agent_assigned else class="hl-string">'Request Reassigned'
                            initial_agent_assigned = True
                            event_log.append({
                                class="hl-string">'ServiceRequestId': service_request_id,
                                class="hl-string">'ActivityName': activity_name,
                                class="hl-string">'EventTime': conv_time,
                                class="hl-string">'SourceSystem': class="hl-string">'Freshservice',
                                class="hl-string">'LastDataUpdate': last_data_update,
                                class="hl-string">'AssignedAgent': get_agent_name(changes[class="hl-string">'responder_id'][1]),
                                class="hl-string">'AssignedTeam': get_group_name(ticket.get(class="hl-string">'group_id')),
                                class="hl-string">'Priority': ticket[class="hl-string">'priority'],
                                class="hl-string">'Status': ticket[class="hl-string">'status'],
                                class="hl-string">'ServiceType': ticket.get(class="hl-string">'category')
                            })
                        
                        if class="hl-string">'group_id' in changes:
                            activity_name = class="hl-string">'Request Reassigned' class=class="hl-string">"hl-comment"># Any group change after creation is a reassignment
                            if not initial_group_assigned and not triaged:
                                activity_name = class="hl-string">'Request Triaged'
                                initial_group_assigned = True
                                triaged = True
                            event_log.append({
                                class="hl-string">'ServiceRequestId': service_request_id,
                                class="hl-string">'ActivityName': activity_name,
                                class="hl-string">'EventTime': conv_time,
                                class="hl-string">'SourceSystem': class="hl-string">'Freshservice',
                                class="hl-string">'LastDataUpdate': last_data_update,
                                class="hl-string">'AssignedAgent': get_agent_name(ticket.get(class="hl-string">'responder_id')),
                                class="hl-string">'AssignedTeam': get_group_name(changes[class="hl-string">'group_id'][1]),
                                class="hl-string">'Priority': ticket[class="hl-string">'priority'],
                                class="hl-string">'Status': ticket[class="hl-string">'status'],
                                class="hl-string">'ServiceType': ticket.get(class="hl-string">'category')
                            })
                        
                        class=class="hl-string">"hl-comment"># --- Status Change Activities ---
                        if class="hl-string">'status' in changes:
                            old_status, new_status = changes[class="hl-string">'status']
                            
                            if new_status == STATUS_MAPPING[class="hl-string">'Resolved']:
                                event_log.append({**event_log[-1], class="hl-string">'ActivityName': class="hl-string">'Service Request Resolved', class="hl-string">'EventTime': conv_time, class="hl-string">'Status': new_status})
                            elif new_status == STATUS_MAPPING[class="hl-string">'Closed']:
                                event_log.append({**event_log[-1], class="hl-string">'ActivityName': class="hl-string">'Service Request Closed', class="hl-string">'EventTime': conv_time, class="hl-string">'Status': new_status})
                            elif new_status == STATUS_MAPPING[class="hl-string">'Awaiting Customer']:
                                event_log.append({**event_log[-1], class="hl-string">'ActivityName': class="hl-string">'Information Requested from Requestor', class="hl-string">'EventTime': conv_time, class="hl-string">'Status': new_status})
                            elif old_status == STATUS_MAPPING[class="hl-string">'Awaiting Customer'] and new_status == STATUS_MAPPING[class="hl-string">'Open']:
                                event_log.append({**event_log[-1], class="hl-string">'ActivityName': class="hl-string">'Information Provided by Requestor', class="hl-string">'EventTime': conv_time, class="hl-string">'Status': new_status})
                            elif old_status in [STATUS_MAPPING[class="hl-string">'Resolved'], STATUS_MAPPING[class="hl-string">'Closed']] and new_status == STATUS_MAPPING[class="hl-string">'Open']:
                                event_log.append({**event_log[-1], class="hl-string">'ActivityName': class="hl-string">'Service Request Reopened', class="hl-string">'EventTime': conv_time, class="hl-string">'Status': new_status})
                            elif new_status in STATUS_MAPPING[class="hl-string">'Pending Vendor']:
                                event_log.append({**event_log[-1], class="hl-string">'ActivityName': class="hl-string">'External Vendor Engaged', class="hl-string">'EventTime': conv_time, class="hl-string">'Status': new_status})
                            elif new_status in STATUS_MAPPING[class="hl-string">'Pending Approval']:
                                event_log.append({**event_log[-1], class="hl-string">'ActivityName': class="hl-string">'Internal Review Performed', class="hl-string">'EventTime': conv_time, class="hl-string">'Status': new_status})
            
            class=class="hl-string">"hl-comment"># --- Derived/Calculated Activities ---
            class=class="hl-string">"hl-comment"># --- Activity: SLA Target Breached ---
            resolution_time = closed_at or resolved_at
            if due_by and resolution_time and resolution_time > due_by:
                event_log.append({
                    class="hl-string">'ServiceRequestId': service_request_id,
                    class="hl-string">'ActivityName': class="hl-string">'SLA Target Breached',
                    class="hl-string">'EventTime': due_by,
                    class="hl-string">'SourceSystem': class="hl-string">'Freshservice',
                    class="hl-string">'LastDataUpdate': last_data_update,
                    class="hl-string">'AssignedAgent': get_agent_name(ticket.get(class="hl-string">'responder_id')),
                    class="hl-string">'AssignedTeam': get_group_name(ticket.get(class="hl-string">'group_id')),
                    class="hl-string">'Priority': ticket[class="hl-string">'priority'],
                    class="hl-string">'Status': ticket[class="hl-string">'status'],
                    class="hl-string">'ServiceType': ticket.get(class="hl-string">'category')
                })

            class=class="hl-string">"hl-comment"># --- Activity: Resolution Confirmed by Requestor (Inferred from survey) ---
            if ticket.get(class="hl-string">'satisfaction_ratings'):
                for rating in ticket[class="hl-string">'satisfaction_ratings']:
                    if rating.get(class="hl-string">'ratings'): class=class="hl-string">"hl-comment"># Check if there is a rating
                        rating_time = parse_iso_datetime(rating.get(class="hl-string">'created_at'))
                        if resolved_at and rating_time > resolved_at:
                            event_log.append({
                                class="hl-string">'ServiceRequestId': service_request_id,
                                class="hl-string">'ActivityName': class="hl-string">'Resolution Confirmed by Requestor',
                                class="hl-string">'EventTime': rating_time,
                                class="hl-string">'SourceSystem': class="hl-string">'Freshservice',
                                class="hl-string">'LastDataUpdate': last_data_update,
                                class="hl-string">'AssignedAgent': get_agent_name(ticket.get(class="hl-string">'responder_id')),
                                class="hl-string">'AssignedTeam': get_group_name(ticket.get(class="hl-string">'group_id')),
                                class="hl-string">'Priority': ticket[class="hl-string">'priority'],
                                class="hl-string">'Status': ticket[class="hl-string">'status'],
                                class="hl-string">'ServiceType': ticket.get(class="hl-string">'category')
                            })
                            break class=class="hl-string">"hl-comment"># Only add one confirmation event

        page += 1

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

    class=class="hl-string">"hl-comment"># Create DataFrame and save to CSV
    df = pd.DataFrame(event_log)
    df = df.sort_values(by=[class="hl-string">'ServiceRequestId', class="hl-string">'EventTime'])
    df[class="hl-string">'EventTime'] = df[class="hl-string">'EventTime'].dt.strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
    df[class="hl-string">'LastDataUpdate'] = pd.to_datetime(df[class="hl-string">'LastDataUpdate']).dt.strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
    output_filename = class="hl-string">'freshservice_event_log.csv'
    df.to_csv(output_filename, index=False)
    print(fclass="hl-string">"Successfully extracted {len(df)} events to {output_filename}")

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

/*
This extraction method uses the Freshservice UI. The following describes the configuration
and post-processing logic required to generate the event log.
*/

-- Step 1: Configuration for Report 1 (Creation Events)
MODULE: Tickets
WIDGET_TYPE: Data Table
COLUMNS:
  - Ticket ID
  - Created at
  - Assigned to
  - Group
  - Priority
  - Status
  - Category
FILTERS:
  - Created at IS WITHIN [Specify Start Date] and [Specify End Date]
  - Type EQUALS 'Service Request'

-- Step 2: Configuration for Report 2 (Update Events)
MODULE: Ticket Activities
WIDGET_TYPE: Data Table
COLUMNS:
  - Ticket ID
  - Activity Date
  - Activity Type
  - Field (from Changes)
  - Old Value (from Changes)
  - New Value (from Changes)
  - Performed by
  - Group (ticket attribute)
  - Assigned to (ticket attribute)
  - Priority (ticket attribute)
  - Status (ticket attribute)
  - Category (ticket attribute)
FILTERS:
  - Activity Date IS WITHIN [Specify Start Date] and [Specify End Date]
  - Ticket Type EQUALS 'Service Request'
  - Activity Type IN ('Status changed', 'Priority changed', 'Group changed', 'Agent changed', 'Private Note added', 'Public Note added')

-- Step 3: Post-Processing Logic to create the final event log file
/* 
Apply this logic to each row of the combined data from the two exported CSV files.
The logic uses a CASE statement structure to map raw data to the final ActivityName.
*/

-- Column Mapping Logic:
`ServiceRequestId` = `Ticket ID`
`EventTime` = IF row is from creation_events.csv THEN `Created at` ELSE `Activity Date`
`SourceSystem` = 'Freshservice'
`LastDataUpdate` = CURRENT_TIMESTAMP()
`AssignedAgent` = `Assigned to`
`AssignedTeam` = `Group`
`Priority` = `Priority`
`Status` = `Status`
`ServiceType` = `Category`

-- ActivityName Mapping Logic:
CASE
  WHEN row is from creation_events.csv THEN 'Service Request Created'
  WHEN `Activity Type` = 'Group changed' AND `Old Value` IS NULL THEN 'Request Triaged'
  WHEN `Activity Type` = 'Priority changed' THEN 'Request Prioritized'
  WHEN `Activity Type` = 'Agent changed' AND `Old Value` IS NULL THEN 'Request Assigned to Agent'
  WHEN `Activity Type` IN ('Agent changed', 'Group changed') AND `Old Value` IS NOT NULL THEN 'Request Reassigned'
  WHEN `Activity Type` = 'Status changed' AND `New Value` IN ('Pending Customer', 'Awaiting Customer Response') THEN 'Information Requested from Requestor'
  WHEN `Activity Type` = 'Status changed' AND `Old Value` IN ('Pending Customer', 'Awaiting Customer Response') AND `New Value` = 'Open' THEN 'Information Provided by Requestor'
  WHEN `Activity Type` = 'Status changed' AND `New Value` IN ('Pending Vendor', 'Awaiting Third Party') THEN 'External Vendor Engaged'
  WHEN `Activity Type` = 'Status changed' AND `New Value` IN ('Pending Approval', 'Internal Review') THEN 'Internal Review Performed'
  WHEN `Activity Type` = 'Private Note added' AND `Note Content` CONTAINS 'SLA Breached' THEN 'SLA Target Breached' -- Requires custom automation
  WHEN `Activity Type` IN ('Private Note added', 'Public Note added') THEN 'Note Added'
  WHEN `Activity Type` = 'Status changed' AND `Old Value` IN ('Resolved', 'Closed') AND `New Value` IN ('Open', 'In Progress') THEN 'Service Request Reopened'
  WHEN `Activity Type` = 'Status changed' AND `New Value` = 'Resolved' THEN 'Service Request Resolved'
  WHEN `Activity Type` = 'Private Note added' AND `Note Content` CONTAINS 'Resolution Confirmed' THEN 'Resolution Confirmed by Requestor' -- Requires custom automation
  WHEN `Activity Type` = 'Status changed' AND `New Value` = 'Closed' THEN 'Service Request Closed'
  ELSE NULL -- Filter out these rows
END AS ActivityName