Uw Klantenservice Data Template

### Part 1: Freshdesk Export Configuration

This process uses the user interface for extraction. In the **Export Tickets** dialog, configure the following settings:

```json
{
  "filter_by": {
    "created": "[Specify Date Range, e.g., Last 6 Months]",
    "status": ["Open", "Pending", "Resolved", "Closed"],
    "// additional_filters": "Optionally filter by Group, Agent, Type, etc."
  },
  "export_fields": [
    "Ticket ID",
    "Created at",
    "First responded at",
    "Resolved at",
    "Closed at",
    "Group",
    "Agent",
    "Status",
    "Priority",
    "Type",
    "// include_custom_fields": "[Select any relevant custom fields]"
  ]
}
```

### Part 2: Data Transformation Script (Python with Pandas)

The exported CSV is a snapshot and must be transformed into an event log. The following script reshapes the data to create a row for each key activity.

```python
import pandas as pd

# --- Configuration ---
# Replace with the actual path to your downloaded Freshdesk CSV export
input_csv_path = 'freshdesk_export.csv'
output_csv_path = 'freshdesk_event_log.csv'

# --- Load Data ---
def transform_freshdesk_export(file_path):
    """Loads the Freshdesk export and transforms it into an event log."""
    try:
        df_raw = pd.read_csv(file_path)
    except FileNotFoundError:
        print(f"Error: The file {file_path} was not found.")
        return None

    # --- Rename and Select Columns ---
    # Adjust column names based on your specific export file
    column_mapping = {
        'Ticket ID': 'ServiceRequest',
        'Agent': 'AssignedAgent',
        'Type': 'ServiceRequestType',
        'Status': 'Status',
        'Priority': 'Priority',
        'Created at': 'Time_Ticket_Created',
        'First responded at': 'Time_First_Response_Sent',
        'Resolved at': 'Time_Ticket_Resolved',
        'Closed at': 'Time_Ticket_Closed'
    }
    df = df_raw.rename(columns=column_mapping)

    # Define the columns that are common to all events for a given ticket
    base_columns = ['ServiceRequest', 'AssignedAgent', 'ServiceRequestType', 'Status', 'Priority']
    # Ensure all required base columns exist
    for col in base_columns:
        if col not in df.columns:
            df[col] = None # Add missing columns as null

    # --- Unpivot Timestamp Columns to Create Events ---
    events = []
    activity_map = {
        'Time_Ticket_Created': 'Ticket Created',
        'Time_First_Response_Sent': 'First Response Sent',
        'Time_Ticket_Resolved': 'Ticket Resolved',
        'Time_Ticket_Closed': 'Ticket Closed'
    }

    for time_col, activity_name in activity_map.items():
        if time_col in df.columns:
            # Select rows where the timestamp exists
            df_event = df[df[time_col].notna()].copy()
            if not df_event.empty:
                df_event['Activity'] = activity_name
                df_event['EventTime'] = pd.to_datetime(df_event[time_col])
                events.append(df_event[base_columns + ['Activity', 'EventTime']])

    if not events:
        print("No events could be generated. Please check your column names and data.")
        return None

    # --- Combine, Sort, and Finalize ---
    df_event_log = pd.concat(events, ignore_index=True)

    # Calculate Resolution Time (optional, as an attribute)
    if 'Time_Ticket_Created' in df.columns and 'Time_Ticket_Resolved' in df.columns:
        df_res_time = df[['ServiceRequest', 'Time_Ticket_Created', 'Time_Ticket_Resolved']].dropna()
        df_res_time['ResolutionTime'] = (pd.to_datetime(df_res_time['Time_Ticket_Resolved']) - pd.to_datetime(df_res_time['Time_Ticket_Created'])).dt.total_seconds() / 3600.0 # In hours
        df_event_log = df_event_log.merge(df_res_time[['ServiceRequest', 'ResolutionTime']], on='ServiceRequest', how='left')

    # Sort the event log for proper ordering
    df_event_log = df_event_log.sort_values(by=['ServiceRequest', 'EventTime'], ascending=[True, True])

    return df_event_log

# --- Execute Transformation ---
if __name__ == "__main__":
    event_log = transform_freshdesk_export(input_csv_path)
    if event_log is not None:
        event_log.to_csv(output_csv_path, index=False)
        print(f"Event log successfully created at: {output_csv_path}")
```

import requests
import pandas as pd
import time
import datetime
import re

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># Replace with your Freshdesk domain
DOMAIN = class="hl-string">'[your_domain].freshdesk.com'
class=class="hl-string">"hl-comment"># Replace with your Freshdesk API Key
API_KEY = class="hl-string">'[your_api_key]'
class=class="hl-string">"hl-comment"># Set the start date for the extraction in class="hl-string">'YYYY-MM-DD' format.
START_DATE_STR = class="hl-string">'2023-01-01'

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

def make_api_request(url, params=None):
    class="hl-string">"""Makes a GET request to the Freshdesk API, handling rate limits."""
    while True:
        try:
            response = requests.get(url, auth=AUTH, params=params)
            response.raise_for_status()
            return response
        except requests.exceptions.HTTPError as err:
            if err.response.status_code == 429:
                retry_after = int(err.response.headers.get(class="hl-string">'Retry-After', 60))
                print(fclass="hl-string">"Rate limit exceeded. Retrying after {retry_after} seconds...")
                time.sleep(retry_after)
            else:
                print(fclass="hl-string">"HTTP Error: {err}")
                return None
        except requests.exceptions.RequestException as err:
            print(fclass="hl-string">"Request Error: {err}")
            return None

def parse_iso_timestamp(ts):
    class="hl-string">"""Parses Freshdesk's ISO 8601 timestamp string."""
    if not ts or not isinstance(ts, str):
        return None
    return datetime.datetime.strptime(ts, class="hl-string">'%Y-%m-%dT%H:%M:%SZ')

def get_freshdesk_event_log(start_date):
    class="hl-string">"""Fetches and processes Freshdesk tickets to create an event log."""
    events = []
    page = 1
    
    class=class="hl-string">"hl-comment"># Convert start_date to the required format for the API query
    start_datetime = datetime.datetime.strptime(start_date, class="hl-string">'%Y-%m-%d')
    query_date = start_datetime.strftime(class="hl-string">'%Y-%m-%dT%H:%M:%SZ')

    while True:
        print(fclass="hl-string">"Fetching tickets page {page}...")
        tickets_url = fclass="hl-string">'{BASE_URL}/tickets'
        class=class="hl-string">"hl-comment"># Use class="hl-string">'updated_since' for incremental loads. For a full load, consider filtering by class="hl-string">'created_at'.
        params = {class="hl-string">'page': page, class="hl-string">'per_page': 100, class="hl-string">'order_by': class="hl-string">'updated_at', class="hl-string">'order_type': class="hl-string">'asc', class="hl-string">'updated_since': query_date, class="hl-string">'include': class="hl-string">'stats,requester'}
        
        response = make_api_request(tickets_url, params=params)
        if not response:
            break
            
        tickets_data = response.json()
        if not tickets_data:
            print(class="hl-string">"No more tickets found.")
            break

        for ticket in tickets_data:
            ticket_id = ticket[class="hl-string">'id']
            created_at = parse_iso_timestamp(ticket[class="hl-string">'created_at'])
            resolved_at = parse_iso_timestamp(ticket.get(class="hl-string">'stats', {}).get(class="hl-string">'resolved_at'))
            
            resolution_time_hours = (resolved_at - created_at).total_seconds() / 3600 if resolved_at and created_at else None

            common_attrs = {
                class="hl-string">'ServiceRequest': ticket_id,
                class="hl-string">'ServiceRequestType': ticket.get(class="hl-string">'type'),
                class="hl-string">'Priority': ticket.get(class="hl-string">'priority'),
                class="hl-string">'AssignedAgent': ticket.get(class="hl-string">'responder_id'),
                class="hl-string">'ResolutionTime': resolution_time_hours
            }
            
            class=class="hl-string">"hl-comment"># 1. Ticket Created
            events.append({
                **common_attrs,
                class="hl-string">'Activity': class="hl-string">'Ticket Created',
                class="hl-string">'EventTime': created_at.strftime(class="hl-string">'%Y-%m-%d %H:%M:%S'),
                class="hl-string">'Status': class="hl-string">'Open' 
            })
            
            class=class="hl-string">"hl-comment"># 2. First Response Sent
            first_responded_at = ticket.get(class="hl-string">'stats', {}).get(class="hl-string">'first_responded_at')
            if first_responded_at:
                events.append({
                    **common_attrs,
                    class="hl-string">'Activity': class="hl-string">'First Response Sent',
                    class="hl-string">'EventTime': parse_iso_timestamp(first_responded_at).strftime(class="hl-string">'%Y-%m-%d %H:%M:%S'),
                    class="hl-string">'Status': ticket.get(class="hl-string">'status')
                })

            class=class="hl-string">"hl-comment"># Fetch conversations for detailed activities
            conv_url = fclass="hl-string">'{BASE_URL}/tickets/{ticket_id}/conversations'
            conv_response = make_api_request(conv_url)
            if conv_response:
                conversations = conv_response.json()
                for conv in conversations:
                    event_time = parse_iso_timestamp(conv[class="hl-string">'created_at']).strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
                    
                    class=class="hl-string">"hl-comment"># 3. Customer Responded vs 4. Internal Note Added
                    if conv[class="hl-string">'source_type'] == class="hl-string">'note':
                        if conv.get(class="hl-string">'private', False):
                            events.append({
                                **common_attrs,
                                class="hl-string">'Activity': class="hl-string">'Internal Note Added',
                                class="hl-string">'EventTime': event_time,
                                class="hl-string">'Status': ticket.get(class="hl-string">'status')
                            })
                        else: class=class="hl-string">"hl-comment"># System generated notes for status changes etc.
                            body_text = conv.get(class="hl-string">'body_text', class="hl-string">'')
                            class=class="hl-string">"hl-comment"># 5. Status Changed to Pending
                            if class="hl-string">'Status changed from' in body_text and class="hl-string">'to Pending' in body_text:
                                events.append({**common_attrs, class="hl-string">'Activity': class="hl-string">'Status Changed to Pending', class="hl-string">'EventTime': event_time, class="hl-string">'Status': class="hl-string">'Pending'})
                            class=class="hl-string">"hl-comment"># 6. Ticket Assigned / Reassigned
                            if class="hl-string">'Assigned to' in body_text:
                                activity_name = class="hl-string">'Ticket Assigned' if any(e[class="hl-string">'Activity'] == class="hl-string">'Ticket Assigned' for e in events if e[class="hl-string">'ServiceRequest'] == ticket_id) else class="hl-string">'Ticket Reassigned'
                                events.append({**common_attrs, class="hl-string">'Activity': activity_name, class="hl-string">'EventTime': event_time, class="hl-string">'Status': ticket.get(class="hl-string">'status')})
                            class=class="hl-string">"hl-comment"># 7. Ticket Priority Changed
                            if class="hl-string">'Priority changed from' in body_text:
                                events.append({**common_attrs, class="hl-string">'Activity': class="hl-string">'Ticket Priority Changed', class="hl-string">'EventTime': event_time, class="hl-string">'Status': ticket.get(class="hl-string">'status')})
                            class=class="hl-string">"hl-comment"># 8. Satisfaction Survey Sent
                            if class="hl-string">'Satisfaction Survey was sent to the contact' in body_text:
                                events.append({**common_attrs, class="hl-string">'Activity': class="hl-string">'Satisfaction Survey Sent', class="hl-string">'EventTime': event_time, class="hl-string">'Status': ticket.get(class="hl-string">'status')})
                    else: class=class="hl-string">"hl-comment"># Not a note, so it's a reply
                        class=class="hl-string">"hl-comment"># 9. Customer Responded
                        if conv.get(class="hl-string">'incoming', False):
                            events.append({
                                **common_attrs,
                                class="hl-string">'Activity': class="hl-string">'Customer Responded',
                                class="hl-string">'EventTime': event_time,
                                class="hl-string">'Status': class="hl-string">'Open' class=class="hl-string">"hl-comment"># Typically a customer response re-opens the ticket
                            })
            
            class=class="hl-string">"hl-comment"># 10. SLA Breached
            if ticket.get(class="hl-string">'fr_escalated', False) or ticket.get(class="hl-string">'resolution_escalated', False):
                 breach_time = ticket.get(class="hl-string">'stats', {}).get(class="hl-string">'status_updated_at', ticket.get(class="hl-string">'updated_at'))
                 events.append({
                    **common_attrs,
                    class="hl-string">'Activity': class="hl-string">'SLA Breached',
                    class="hl-string">'EventTime': parse_iso_timestamp(breach_time).strftime(class="hl-string">'%Y-%m-%d %H:%M:%S'),
                    class="hl-string">'Status': ticket.get(class="hl-string">'status')
                })

            class=class="hl-string">"hl-comment"># 11. Ticket Resolved
            if resolved_at:
                events.append({
                    **common_attrs,
                    class="hl-string">'Activity': class="hl-string">'Ticket Resolved',
                    class="hl-string">'EventTime': resolved_at.strftime(class="hl-string">'%Y-%m-%d %H:%M:%S'),
                    class="hl-string">'Status': class="hl-string">'Resolved'
                })

            class=class="hl-string">"hl-comment"># 12. Ticket Reopened
            reopened_at = ticket.get(class="hl-string">'stats', {}).get(class="hl-string">'reopened_at')
            if reopened_at:
                events.append({
                    **common_attrs,
                    class="hl-string">'Activity': class="hl-string">'Ticket Reopened',
                    class="hl-string">'EventTime': parse_iso_timestamp(reopened_at).strftime(class="hl-string">'%Y-%m-%d %H:%M:%S'),
                    class="hl-string">'Status': class="hl-string">'Open'
                })
                
            class=class="hl-string">"hl-comment"># 13. Ticket Closed
            closed_at = ticket.get(class="hl-string">'stats', {}).get(class="hl-string">'closed_at')
            if closed_at:
                events.append({
                    **common_attrs,
                    class="hl-string">'Activity': class="hl-string">'Ticket Closed',
                    class="hl-string">'EventTime': parse_iso_timestamp(closed_at).strftime(class="hl-string">'%Y-%m-%d %H:%M:%S'),
                    class="hl-string">'Status': class="hl-string">'Closed'
                })

        page += 1

    class=class="hl-string">"hl-comment"># Create DataFrame and sort by time
    df = pd.DataFrame(events)
    df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime'])
    df = df.sort_values(by=[class="hl-string">'ServiceRequest', class="hl-string">'EventTime']).reset_index(drop=True)
    df[class="hl-string">'EventTime'] = df[class="hl-string">'EventTime'].dt.strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')

    return df

if __name__ == class="hl-string">'__main__':
    print(class="hl-string">"Starting Freshdesk data extraction...")
    event_log_df = get_freshdesk_event_log(START_DATE_STR)
    if not event_log_df.empty:
        output_filename = class="hl-string">'freshdesk_event_log.csv'
        event_log_df.to_csv(output_filename, index=False)
        print(fclass="hl-string">"Extraction complete. Event log saved to {output_filename}")
    else:
        print(class="hl-string">"No data extracted.")

### Part 1: Freshdesk Export Configuration

This process uses the user interface for extraction. In the **Export Tickets** dialog, configure the following settings:

```json
{
  "filter_by": {
    "created": "[Specify Date Range, e.g., Last 6 Months]",
    "status": ["Open", "Pending", "Resolved", "Closed"],
    "// additional_filters": "Optionally filter by Group, Agent, Type, etc."
  },
  "export_fields": [
    "Ticket ID",
    "Created at",
    "First responded at",
    "Resolved at",
    "Closed at",
    "Group",
    "Agent",
    "Status",
    "Priority",
    "Type",
    "// include_custom_fields": "[Select any relevant custom fields]"
  ]
}
```

### Part 2: Data Transformation Script (Python with Pandas)

The exported CSV is a snapshot and must be transformed into an event log. The following script reshapes the data to create a row for each key activity.

```python
import pandas as pd

# --- Configuration ---
# Replace with the actual path to your downloaded Freshdesk CSV export
input_csv_path = 'freshdesk_export.csv'
output_csv_path = 'freshdesk_event_log.csv'

# --- Load Data ---
def transform_freshdesk_export(file_path):
    """Loads the Freshdesk export and transforms it into an event log."""
    try:
        df_raw = pd.read_csv(file_path)
    except FileNotFoundError:
        print(f"Error: The file {file_path} was not found.")
        return None

    # --- Rename and Select Columns ---
    # Adjust column names based on your specific export file
    column_mapping = {
        'Ticket ID': 'ServiceRequest',
        'Agent': 'AssignedAgent',
        'Type': 'ServiceRequestType',
        'Status': 'Status',
        'Priority': 'Priority',
        'Created at': 'Time_Ticket_Created',
        'First responded at': 'Time_First_Response_Sent',
        'Resolved at': 'Time_Ticket_Resolved',
        'Closed at': 'Time_Ticket_Closed'
    }
    df = df_raw.rename(columns=column_mapping)

    # Define the columns that are common to all events for a given ticket
    base_columns = ['ServiceRequest', 'AssignedAgent', 'ServiceRequestType', 'Status', 'Priority']
    # Ensure all required base columns exist
    for col in base_columns:
        if col not in df.columns:
            df[col] = None # Add missing columns as null

    # --- Unpivot Timestamp Columns to Create Events ---
    events = []
    activity_map = {
        'Time_Ticket_Created': 'Ticket Created',
        'Time_First_Response_Sent': 'First Response Sent',
        'Time_Ticket_Resolved': 'Ticket Resolved',
        'Time_Ticket_Closed': 'Ticket Closed'
    }

    for time_col, activity_name in activity_map.items():
        if time_col in df.columns:
            # Select rows where the timestamp exists
            df_event = df[df[time_col].notna()].copy()
            if not df_event.empty:
                df_event['Activity'] = activity_name
                df_event['EventTime'] = pd.to_datetime(df_event[time_col])
                events.append(df_event[base_columns + ['Activity', 'EventTime']])

    if not events:
        print("No events could be generated. Please check your column names and data.")
        return None

    # --- Combine, Sort, and Finalize ---
    df_event_log = pd.concat(events, ignore_index=True)

    # Calculate Resolution Time (optional, as an attribute)
    if 'Time_Ticket_Created' in df.columns and 'Time_Ticket_Resolved' in df.columns:
        df_res_time = df[['ServiceRequest', 'Time_Ticket_Created', 'Time_Ticket_Resolved']].dropna()
        df_res_time['ResolutionTime'] = (pd.to_datetime(df_res_time['Time_Ticket_Resolved']) - pd.to_datetime(df_res_time['Time_Ticket_Created'])).dt.total_seconds() / 3600.0 # In hours
        df_event_log = df_event_log.merge(df_res_time[['ServiceRequest', 'ResolutionTime']], on='ServiceRequest', how='left')

    # Sort the event log for proper ordering
    df_event_log = df_event_log.sort_values(by=['ServiceRequest', 'EventTime'], ascending=[True, True])

    return df_event_log

# --- Execute Transformation ---
if __name__ == "__main__":
    event_log = transform_freshdesk_export(input_csv_path)
    if event_log is not None:
        event_log.to_csv(output_csv_path, index=False)
        print(f"Event log successfully created at: {output_csv_path}")
```