サービスリクエスト管理データテンプレート

This method uses the Zendesk Explore UI, where queries are built visually. The following describes the configuration for the two required reports.

**Report 1: Ticket Events Configuration**
* **Dataset**: `Support: Ticket updates`
* **Metrics**: `COUNT(Tickets)`
* **Rows**:
  * `Ticket ID`
  * `ActivityName` (This is the custom calculated attribute defined below)
  * `Update - Timestamp`
  * `Updater name`
  * `Assignee name`
  * `Group name`
  * `Ticket priority`
  * `Ticket status`
  * `Submission channel`
  * `Ticket type`
  * `Ticket tags`
* **Standard Calculated Attribute (`ActivityName`) Formula**:
```
IF [Update - Field name] = "assignee_id" AND ([Update - Previous value] = "" OR [Update - Previous value] = NULL) THEN
    "Request Assigned to Agent"
ELIF [Update - Field name] = "assignee_id" AND [Update - Previous value] != "" AND [Update - Previous value] != NULL THEN
    "Agent Reassigned"
ELIF [Update - Field name] = "group_id" THEN
    "Request Escalated"
ELIF [Update - Field name] = "priority" AND [Update - New value] != [Update - Previous value] THEN
    "Priority Changed"
ELIF [Update - Field name] = "status" AND [Update - New value] = "hold" THEN
    "Request Placed On-Hold"
ELIF [Update - Field name] = "status" AND [Update - New value] = "solved" THEN
    "Service Request Resolved"
ELIF [Update - Field name] = "status" AND [Update - New value] = "closed" THEN
    "Service Request Closed"
ELIF [Update - Field name] = "status" AND [Update - New value] = "open" AND [Update - Previous value] = "solved" THEN
    "Service Request Reopened"
ELIF [Update - Field name] = "requester_id" AND [Update - Previous value] = NULL THEN
    "Service Request Created"
ELSE
    NULL
ENDIF
```
* **Note on Comment Activities**: Capturing `Public Reply Sent` and `Internal Note Added` from the `Ticket updates` dataset is not reliably possible as the public vs. internal distinction is often missing. For these events, creating a third report on the `Support: Comments` dataset and merging it is the recommended, though more complex, approach.

**Report 2: SLA Events Configuration**
* **Dataset**: `Support: SLAs`
* **Metrics**: `COUNT(SLA tickets)`
* **Rows**:
  * `Ticket ID`
  * `SLA updated - Timestamp`
  * `SLA status`
  * `Updater name`
  * `Assignee name`
  * `Group name`
  * `Ticket priority`
* **Note**: The `ActivityName` for this report (`SLA Target Applied`, `SLA Target Breached`) must be generated after export based on the value in the `SLA status` column.

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

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># Replace with your Zendesk subdomain, admin email, and API token
ZENDESK_SUBDOMAIN = class="hl-string">'[Your Subdomain]'
ZENDESK_EMAIL = class="hl-string">'[Your Zendesk Admin Email]'
ZENDESK_API_TOKEN = class="hl-string">'[Your Zendesk API Token]'

class=class="hl-string">"hl-comment"># Set the start date for the data extraction (YYYY-MM-DD)
START_TIME_STR = class="hl-string">'2023-01-01'

class=class="hl-string">"hl-comment"># --- End of Configuration ---

class=class="hl-string">"hl-comment"># Set up constants and session
BASE_URL = fclass="hl-string">'https://{ZENDESK_SUBDOMAIN}.zendesk.com/api/v2'
AUTH = (fclass="hl-string">'{ZENDESK_EMAIL}/token', ZENDESK_API_TOKEN)
SOURCE_SYSTEM = class="hl-string">'Zendesk Support'
LAST_DATA_UPDATE = datetime.now(timezone.utc).isoformat()

session = requests.Session()
session.auth = AUTH

class=class="hl-string">"hl-comment"># Caches for user and group names to reduce API calls
user_cache = {}
group_cache = {}

def get_user_name(user_id):
    if not user_id:
        return None
    if user_id in user_cache:
        return user_cache[user_id]
    try:
        response = session.get(fclass="hl-string">'{BASE_URL}/users/{user_id}.json')
        response.raise_for_status()
        user_cache[user_id] = response.json()[class="hl-string">'user'][class="hl-string">'name']
        return user_cache[user_id]
    except requests.exceptions.RequestException:
        return fclass="hl-string">'User ID {user_id}'

def get_group_name(group_id):
    if not group_id:
        return None
    if group_id in group_cache:
        return group_cache[group_id]
    try:
        response = session.get(fclass="hl-string">'{BASE_URL}/groups/{group_id}.json')
        response.raise_for_status()
        group_cache[group_id] = response.json()[class="hl-string">'group'][class="hl-string">'name']
        return group_cache[group_id]
    except requests.exceptions.RequestException:
        return fclass="hl-string">'Group ID {group_id}'

def handle_rate_limit(response):
    if response.status_code == 429:
        retry_after = int(response.headers.get(class="hl-string">'Retry-After', 60))
        print(fclass="hl-string">'Rate limit reached. Waiting for {retry_after} seconds...')
        time.sleep(retry_after)
        return True
    response.raise_for_status()
    return False

def fetch_paginated_data(url):
    while url:
        try:
            response = session.get(url)
            if handle_rate_limit(response):
                continue
            data = response.json()
            yield data
            url = data.get(class="hl-string">'next_page')
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">'Error fetching data from {url}: {e}')
            break

def process_ticket(ticket, event_log):
    ticket_id = ticket[class="hl-string">'id']
    print(fclass="hl-string">'Processing Ticket ID: {ticket_id}')

    class=class="hl-string">"hl-comment"># --- 1. Fetch Ticket Audits ---
    audits_url = fclass="hl-string">'{BASE_URL}/tickets/{ticket_id}/audits.json'
    all_events = [] class=class="hl-string">"hl-comment"># A temporary list to hold all events for sorting

    for audit_page in fetch_paginated_data(audits_url):
        for audit in audit_page.get(class="hl-string">'audits', []):
            author_id = audit.get(class="hl-string">'author_id')
            timestamp = audit[class="hl-string">'created_at']

            class=class="hl-string">"hl-comment"># Store ticket state at the time of the audit
            ticket_state = {}
            for event in audit.get(class="hl-string">'events', []):
                if event.get(class="hl-string">'field_name') in [class="hl-string">'assignee_id', class="hl-string">'group_id', class="hl-string">'priority', class="hl-string">'status', class="hl-string">'channel', class="hl-string">'type', class="hl-string">'tags']:
                    ticket_state[event[class="hl-string">'field_name']] = event.get(class="hl-string">'value')

            for event in audit.get(class="hl-string">'events', []):
                event[class="hl-string">'timestamp'] = timestamp
                event[class="hl-string">'author_id'] = author_id
                event[class="hl-string">'ticket_state'] = ticket_state
                all_events.append(event)

    class=class="hl-string">"hl-comment"># --- 2. Fetch Comments (which are not fully detailed in audits) ---
    comments_url = fclass="hl-string">'{BASE_URL}/tickets/{ticket_id}/comments.json'
    for comment_page in fetch_paginated_data(comments_url):
        for comment in comment_page.get(class="hl-string">'comments', []):
            comment[class="hl-string">'type'] = class="hl-string">'Comment'
            comment[class="hl-string">'timestamp'] = comment[class="hl-string">'created_at']
            class=class="hl-string">"hl-comment"># Add ticket_state placeholder, though it's less relevant for comments
            comment[class="hl-string">'ticket_state'] = {}
            all_events.append(comment)

    class=class="hl-string">"hl-comment"># --- 3. Fetch SLA Metrics ---
    metrics_url = fclass="hl-string">'{BASE_URL}/tickets/{ticket_id}/metrics.json'
    try:
        response = session.get(metrics_url)
        if not handle_rate_limit(response):
            metrics_data = response.json().get(class="hl-string">'ticket_metric', {})
            if metrics_data.get(class="hl-string">'sla_policy'):
                for sla in metrics_data[class="hl-string">'sla_policy'][class="hl-string">'policy_metrics']:
                    all_events.append({
                        class="hl-string">'type': class="hl-string">'SlaApply',
                        class="hl-string">'timestamp': metrics_data[class="hl-string">'created_at'],
                        class="hl-string">'policy_name': sla[class="hl-string">'policy'][class="hl-string">'title'],
                        class="hl-string">'ticket_state': {}
                    })
                    if sla.get(class="hl-string">'breach_at') and datetime.fromisoformat(sla[class="hl-string">'breach_at'].replace(class="hl-string">'Z', class="hl-string">'+00:00')) < datetime.now(timezone.utc):
                        all_events.append({
                            class="hl-string">'type': class="hl-string">'SlaBreach',
                            class="hl-string">'timestamp': sla[class="hl-string">'breach_at'],
                            class="hl-string">'policy_name': sla[class="hl-string">'policy'][class="hl-string">'title'],
                            class="hl-string">'metric': sla[class="hl-string">'metric'],
                            class="hl-string">'ticket_state': {}
                        })
    except requests.exceptions.RequestException as e:
        print(fclass="hl-string">'Could not fetch SLA metrics for ticket {ticket_id}: {e}')


    class=class="hl-string">"hl-comment"># --- 4. Sort all collected events by timestamp ---
    all_events.sort(key=lambda x: x[class="hl-string">'timestamp'])

    class=class="hl-string">"hl-comment"># --- 5. Process sorted events to create event log rows ---
    last_assignee = None

    for event in all_events:
        activity_name = None
        timestamp = event[class="hl-string">'timestamp']

        event_type = event.get(class="hl-string">'type')
        field_name = event.get(class="hl-string">'field_name')

        if event_type == class="hl-string">'Create':
            activity_name = class="hl-string">'Service Request Created'
            last_assignee = event.get(class="hl-string">'value') if field_name == class="hl-string">'assignee_id' else None

        elif event_type == class="hl-string">'Change':
            prev_value = event.get(class="hl-string">'previous_value')
            new_value = event.get(class="hl-string">'value')

            if field_name == class="hl-string">'status':
                if new_value == class="hl-string">'solved': activity_name = class="hl-string">'Service Request Resolved'
                elif new_value == class="hl-string">'closed': activity_name = class="hl-string">'Service Request Closed'
                elif new_value == class="hl-string">'hold': activity_name = class="hl-string">'Request Placed On-Hold'
                elif new_value == class="hl-string">'open' and prev_value == class="hl-string">'solved': activity_name = class="hl-string">'Service Request Reopened'
            elif field_name == class="hl-string">'priority': activity_name = class="hl-string">'Priority Changed'
            elif field_name == class="hl-string">'assignee_id':
                if prev_value is None and new_value is not None:
                    activity_name = class="hl-string">'Request Assigned to Agent'
                elif prev_value is not None and new_value is not None:
                    activity_name = class="hl-string">'Agent Reassigned'
                last_assignee = new_value
            elif field_name == class="hl-string">'group_id':
                if prev_value is not None and new_value is not None:
                    activity_name = class="hl-string">'Request Escalated'

        elif event_type == class="hl-string">'Comment':
            if event.get(class="hl-string">'public', False):
                activity_name = class="hl-string">'Public Reply Sent'
            else:
                activity_name = class="hl-string">'Internal Note Added'

        elif event_type == class="hl-string">'SlaApply':
            activity_name = class="hl-string">'SLA Target Applied'
        elif event_type == class="hl-string">'SlaBreach':
            activity_name = class="hl-string">'SLA Target Breached'

        if activity_name:
            class=class="hl-string">"hl-comment"># Get attribute values from the ticket object itself as a baseline
            agent_id = ticket.get(class="hl-string">'assignee_id')
            group_id = ticket.get(class="hl-string">'group_id')
            priority = ticket.get(class="hl-string">'priority')
            status = ticket.get(class="hl-string">'status')
            
            class=class="hl-string">"hl-comment"># Override with values at the time of the event if available
            state = event.get(class="hl-string">'ticket_state', {})
            agent_id = state.get(class="hl-string">'assignee_id', agent_id)
            group_id = state.get(class="hl-string">'group_id', group_id)
            priority = state.get(class="hl-string">'priority', priority)
            status = state.get(class="hl-string">'status', status)
            
            event_log.append({
                class="hl-string">'ServiceRequestId': ticket_id,
                class="hl-string">'ActivityName': activity_name,
                class="hl-string">'EventTimestamp': timestamp,
                class="hl-string">'SourceSystem': SOURCE_SYSTEM,
                class="hl-string">'LastDataUpdate': LAST_DATA_UPDATE,
                class="hl-string">'AgentName': get_user_name(agent_id),
                class="hl-string">'AssignedTeam': get_group_name(group_id),
                class="hl-string">'RequestPriority': priority,
                class="hl-string">'RequestStatus': status,
                class="hl-string">'RequestChannel': ticket.get(class="hl-string">'via', {}).get(class="hl-string">'channel'),
                class="hl-string">'ServiceType': ticket.get(class="hl-string">'type'),
                class="hl-string">'TicketTags': class="hl-string">', '.join(ticket.get(class="hl-string">'tags', [])),
            })


def main():
    print(class="hl-string">'Starting Zendesk data extraction...')
    event_log = []
    start_unix_time = int(datetime.strptime(START_TIME_STR, class="hl-string">'%Y-%m-%d').timestamp())
    
    class=class="hl-string">"hl-comment"># Use the incremental export endpoint for efficiency
    url = fclass="hl-string">'{BASE_URL}/incremental/tickets/cursor.json?start_time={start_unix_time}'

    while url:
        try:
            response = session.get(url)
            if handle_rate_limit(response):
                continue
            data = response.json()
            tickets = data.get(class="hl-string">'tickets', [])
            print(fclass="hl-string">'Fetched {len(tickets)} tickets...')
            for ticket in tickets:
                process_ticket(ticket, event_log)
            
            if not data.get(class="hl-string">'after_cursor'):
                break
            url = data[class="hl-string">'next_page']

        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">'A critical error occurred: {e}')
            break

    print(fclass="hl-string">'Extraction complete. Total events found: {len(event_log)}')
    
    if not event_log:
        print(class="hl-string">'No events were extracted. Please check your configuration and date range.')
        return

    df = pd.DataFrame(event_log)

    class=class="hl-string">"hl-comment"># Reorder columns to match requirements
    columns = [
        class="hl-string">'ServiceRequestId', class="hl-string">'ActivityName', class="hl-string">'EventTimestamp', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate',
        class="hl-string">'AgentName', class="hl-string">'AssignedTeam', class="hl-string">'RequestPriority', class="hl-string">'RequestStatus',
        class="hl-string">'RequestChannel', class="hl-string">'ServiceType', class="hl-string">'TicketTags'
    ]
    df = df[columns]
    df = df.sort_values(by=[class="hl-string">'ServiceRequestId', class="hl-string">'EventTimestamp']).reset_index(drop=True)

    class=class="hl-string">"hl-comment"># Save to CSV
    output_filename = class="hl-string">'zendesk_event_log.csv'
    df.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%SZ')
    print(fclass="hl-string">'Event log saved to {output_filename}')

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

This method uses the Zendesk Explore UI, where queries are built visually. The following describes the configuration for the two required reports.

**Report 1: Ticket Events Configuration**
* **Dataset**: `Support: Ticket updates`
* **Metrics**: `COUNT(Tickets)`
* **Rows**:
  * `Ticket ID`
  * `ActivityName` (This is the custom calculated attribute defined below)
  * `Update - Timestamp`
  * `Updater name`
  * `Assignee name`
  * `Group name`
  * `Ticket priority`
  * `Ticket status`
  * `Submission channel`
  * `Ticket type`
  * `Ticket tags`
* **Standard Calculated Attribute (`ActivityName`) Formula**:
```
IF [Update - Field name] = "assignee_id" AND ([Update - Previous value] = "" OR [Update - Previous value] = NULL) THEN
    "Request Assigned to Agent"
ELIF [Update - Field name] = "assignee_id" AND [Update - Previous value] != "" AND [Update - Previous value] != NULL THEN
    "Agent Reassigned"
ELIF [Update - Field name] = "group_id" THEN
    "Request Escalated"
ELIF [Update - Field name] = "priority" AND [Update - New value] != [Update - Previous value] THEN
    "Priority Changed"
ELIF [Update - Field name] = "status" AND [Update - New value] = "hold" THEN
    "Request Placed On-Hold"
ELIF [Update - Field name] = "status" AND [Update - New value] = "solved" THEN
    "Service Request Resolved"
ELIF [Update - Field name] = "status" AND [Update - New value] = "closed" THEN
    "Service Request Closed"
ELIF [Update - Field name] = "status" AND [Update - New value] = "open" AND [Update - Previous value] = "solved" THEN
    "Service Request Reopened"
ELIF [Update - Field name] = "requester_id" AND [Update - Previous value] = NULL THEN
    "Service Request Created"
ELSE
    NULL
ENDIF
```
* **Note on Comment Activities**: Capturing `Public Reply Sent` and `Internal Note Added` from the `Ticket updates` dataset is not reliably possible as the public vs. internal distinction is often missing. For these events, creating a third report on the `Support: Comments` dataset and merging it is the recommended, though more complex, approach.

**Report 2: SLA Events Configuration**
* **Dataset**: `Support: SLAs`
* **Metrics**: `COUNT(SLA tickets)`
* **Rows**:
  * `Ticket ID`
  * `SLA updated - Timestamp`
  * `SLA status`
  * `Updater name`
  * `Assignee name`
  * `Group name`
  * `Ticket priority`
* **Note**: The `ActivityName` for this report (`SLA Target Applied`, `SLA Target Breached`) must be generated after export based on the value in the `SLA status` column.