> Continuous Process Improvement > Expense Management > Ramp > Data Template

Your Expense Management Data Template

Ramp

Expense Management Attributes (23) Expense Management Activities (12) Extraction Guides (3)

Your Expense Management Data Template

This template provides a clear roadmap for collecting the necessary data to analyze and optimize your expense management process. It outlines essential attributes, key activities to track, and practical guidance for extracting your data from Ramp. Use this resource to ensure you gather all the information needed for insightful process discovery and improvement.

Recommended attributes to collect
Key activities to track
Extraction guidance for Ramp

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

Download Template

Expense Management Attributes

These are the essential data fields to include in your event log for a comprehensive analysis of your expense management process.

3 Required 6 Recommended 14 Optional

	Name	Description
	Activity Name ActivityName	The name of the specific event or task that occurred at a point in time within the expense management process.
Description This attribute describes a single step in the expense report lifecycle, such as 'Expense Submitted', 'Manager Approved', or 'Reimbursement Executed'. These activities form the nodes in the process map, allowing for visualization and analysis of the process flow. Analyzing activities helps identify which steps are most frequent, where bottlenecks occur, and how different workflows vary. It is a core component for understanding the sequence of operations and measuring performance at each stage. Why it matters It defines the steps in the process map, enabling the visualization and analysis of the process flow from start to finish. Where to get This is typically derived from event logs or status change records associated with each expense report in Ramp. Examples Expense SubmittedManager ApprovedFinance Review PendingReimbursement Executed
	Event Timestamp EventTimestamp	The exact date and time when the activity occurred.
Description Each activity in the process has a corresponding timestamp that records when it happened. This temporal data is used to order events chronologically and is the basis for all time-related analysis. In process mining, timestamps are used to calculate cycle times between activities, measure the total duration of a case, and identify delays. This information is critical for performance monitoring and identifying opportunities for process improvement. Why it matters This attribute provides the chronological order of events and is essential for all duration calculations and performance analysis. Where to get This information is typically found alongside the activity or status records in Ramp's event logs or transaction data. Examples 2023-10-26T10:00:00Z2023-10-26T14:30:00Z2023-10-27T09:00:00Z
	Expense Report ID ExpenseReportId	The unique identifier for each expense report, serving as the primary case identifier for the process.
Description The Expense Report ID groups all events and activities related to a single submission of expenses. It allows for a complete, chronological tracking of an expense claim from its initial entry to its final payment. In process mining, this attribute is essential for reconstructing the end-to-end journey of each expense report. By using it as the case ID, analyses can accurately calculate cycle times, identify bottlenecks, and visualize the different paths reports take through the approval process. Why it matters This is the fundamental attribute that links all related activities into a single process instance, making end-to-end analysis possible. Where to get This identifier should be available in the main expense report or transactions table within Ramp. Examples ER-2023-08-1123ER-2023-09-4591ER-2023-10-0024
	Employee Department EmployeeDepartment	The department of the employee who submitted the expense report.
Description This attribute indicates the business unit or department to which the expense-submitting employee belongs, such as 'Sales', 'Engineering', or 'Marketing'. This is a critical dimension for analysis, as it allows for filtering and comparing process performance across different parts of the organization. It can reveal variations in approval times, revision rates, and policy compliance between departments, supporting targeted improvement initiatives. Why it matters It enables comparison of process metrics across different business units, highlighting variations in efficiency, compliance, and spending. Where to get This information is likely associated with the employee's user profile in Ramp or an integrated HR system. Examples SalesMarketingEngineeringFinance
	Expense Category ExpenseCategory	The category assigned to an expense, such as 'Travel', 'Software', or 'Meals'.
Description This attribute classifies expenses into predefined categories, which helps in tracking and controlling company spending. A single expense report may contain items from multiple categories. Analyzing by expense category is essential for the 'Spend Category Analysis' dashboard. It helps finance teams understand where money is being spent, monitor budget adherence, and identify trends or anomalies in spending over time. Why it matters It allows for detailed spend analysis, helping to identify major cost drivers and opportunities for budget optimization. Where to get This is typically a line-item level detail on the expense report in Ramp. Data may need to be aggregated to the report level for some analyses. Examples AirfareMeals and EntertainmentSoftware SubscriptionOffice Supplies
	Policy Violation Flag PolicyViolationFlag	A flag indicating whether the expense report was flagged for a policy violation.
Description This boolean attribute is set to true if the system's automated checks detect a potential violation of company expense policies, such as exceeding a spending limit or submitting a duplicate expense. This flag is critical for the 'Policy Violation Detection' dashboard and the associated KPI. It helps measure the effectiveness of policy controls and identify common areas of non-compliance, which can inform policy updates or employee training. Why it matters It directly measures policy compliance, helping to identify and reduce non-compliant spending and associated risks. Where to get This is likely a system-generated flag within Ramp, triggered by automated policy checks during the submission or approval process. Examples truefalse
	Report Total Amount ReportTotalAmount	The total monetary value of the expense report.
Description This attribute represents the sum of all expenses included in a single report. It is a key financial metric for understanding spending patterns. In process analysis, the report amount can be used to segment cases and investigate whether high-value reports follow different approval paths or take longer to process. It is fundamental for spend analysis dashboards and for identifying cost-saving opportunities. Why it matters It provides a crucial financial dimension for analysis, allowing for segmentation of reports by value and tracking overall spend. Where to get This is a primary field on the expense report object in Ramp. Examples 150.752500.0089.99
	Revision Reason RevisionReason	The reason provided when an expense report is sent back to the employee for revision.
Description When an approver rejects or sends back an expense report, they typically provide a reason. This attribute captures that reason, such as 'Missing Receipt', 'Incorrect Category', or 'Out of Policy'. This information is invaluable for the 'Expense Revision Rate & Causes' dashboard. By analyzing the most common reasons for rework, organizations can identify systemic issues in the submission process and implement targeted training or system improvements to reduce errors. Why it matters It provides direct insight into the root causes of rework, enabling targeted actions to improve first-time submission quality. Where to get This data would be captured in the comments or rejection details when the 'Expense Sent Back for Revision' activity occurs in Ramp. Examples Missing itemized receiptExpense exceeds policy limitIncorrect expense category selectedDuplicate transaction
	User Name UserName	The name or ID of the user who performed the activity, including the employee submitting the expense or the approver.
Description This attribute identifies the individual responsible for a particular event in the process, such as submitting, approving, or reviewing an expense report. This can be an employee's name or a unique user ID. Analyzing by user helps to understand workload distribution, identify top performers, and pinpoint individuals who may require additional training. It is key for dashboards related to approval performance and resource management. Why it matters It attributes process activities to specific individuals, enabling performance analysis at a resource level and identifying training needs. Where to get User information is typically logged in the audit trail or transaction history for each expense report in Ramp. Examples Alice JohnsonBob SmithCharlie BrownSystem Automation
	Accounting Posting Time AccountingPostingTime	The time from when a reimbursement is executed until the transaction is posted to the accounting system.
Description This calculated metric measures the delay in financial record-keeping. It is the time difference between the 'Reimbursement Executed' event and the 'Synced with Accounting System' event. This attribute is used for the 'Average Accounting Posting Time' KPI and the 'Accounting Posting Delays' dashboard. It helps ensure that financial records are updated promptly, which is important for accurate and timely financial reporting. Why it matters It highlights delays in financial record-keeping, enabling actions to accelerate the accounting close process. Where to get This is calculated from the event timestamps in the data log for reimbursement and accounting sync events. Examples 2 hours1 day5 minutes
	Approval Step Count ApprovalStepCount	The total number of formal approval steps an expense report has passed through.
Description This calculated metric counts the number of distinct approval activities, such as 'Manager Approved' and 'Finance Approved', that have occurred for a single expense report. It helps quantify the complexity of the approval workflow. This attribute is used for the 'Average Approval Steps per Report' KPI and the 'Simple Expense Approval Paths' dashboard. By analyzing this count, especially in relation to the report's value or category, organizations can identify if simple, low-value expenses are subject to overly complex approval processes. Why it matters It quantifies workflow complexity, helping to identify opportunities for simplification, especially for low-risk reports. Where to get This is calculated by counting the occurrences of specific approval-related activities within each case in the event log. Examples 123
	Approving Manager ApprovingManager	The name of the manager who performed the approval step.
Description This attribute identifies the manager responsible for reviewing and approving an employee's expense report. It is distinct from the user who submitted the report. Tracking the approving manager is essential for the 'Manager Approval Cycle Time' dashboard. It allows for analysis of approval workloads and performance, highlighting managers who approve quickly versus those who are bottlenecks. This can help in balancing workloads or providing additional support. Why it matters It enables performance analysis of individual approvers, helping to identify and address bottlenecks in the approval workflow. Where to get This information is part of the approval workflow data in Ramp, logged when a manager takes action on a report. Examples Jane DoeJohn MillerSusan Chen
	Audit Outcome AuditOutcome	The result of an internal or external audit of the expense report.
Description For expense reports that undergo a formal audit, this attribute records the final outcome, such as 'Approved', 'Partially Denied', or 'Further Information Required'. This data is central to the 'Expense Audit Performance' dashboard. It helps in evaluating the effectiveness of the audit process, tracking the frequency of different outcomes, and understanding the financial impact of audit findings. Why it matters It measures the effectiveness and results of the audit process, providing insights into compliance and control weaknesses. Where to get This would be recorded in Ramp's audit module or an integrated system if one is used for detailed expense auditing. Examples PassedPassed with RemarksRejectedEscalated
	Event End Time EventEndTime	The timestamp indicating when an activity with a duration concluded.
Description While many activities are instantaneous, some, like 'Policy Check Performed', might have a measurable duration. This attribute captures the end time of such activities, complementing the StartTime. Having both a start and end time allows for the precise calculation of activity processing times. This is essential for KPIs like 'Average Policy Check Duration' and for identifying exactly how long specific automated or manual tasks take to complete within the overall process. Why it matters It enables the precise calculation of individual activity durations, which is crucial for identifying inefficient process steps. Where to get For activities with a duration, this would be logged in Ramp's event data. For instantaneous events, it can be the same as the StartTime. Examples 2023-10-26T10:00:05Z2023-10-26T14:35:10Z2023-10-27T09:10:00Z
	Finance Approver FinanceApprover	The user from the finance team who approved the expense report.
Description For workflows that include a finance review step, this attribute identifies the specific person or team within the finance department who performed the final approval. This attribute supports the 'Finance Review Efficiency' dashboard by allowing performance to be analyzed at the individual or team level. It helps identify bottlenecks, assess workload distribution, and measure the effectiveness of the finance review stage. Why it matters It allows for detailed performance analysis of the finance review step, helping to optimize a critical control point in the process. Where to get This would be captured in the approval history of the expense report in Ramp during the finance review stage. Examples Finance Team ADavid LeeFinance Automation Bot
	Finance Review Time FinanceReviewTime	The time an expense report spends in the finance review stage.
Description This metric measures the duration from when a report enters the finance queue ('Finance Review Pending') until a finance approver takes action ('Finance Approved' or 'Finance Rejected'). It is the primary measure for the 'Average Finance Review Time' KPI and the 'Finance Review Efficiency' dashboard. Analyzing this duration helps identify opportunities to streamline or automate the finance review process, reducing manual effort and speeding up the overall cycle. Why it matters It measures the efficiency of the finance department's review process, highlighting opportunities for automation and streamlining. Where to get This value is calculated from the event timestamps for the start and end of the finance review activities. Examples 4 hours1 day 2 hours18 hours
	Is Rework IsRework	A calculated flag that indicates if an expense report has been sent back for revision at least once.
Description This boolean attribute is derived by checking if the 'Expense Sent Back for Revision' activity has occurred for a given case. It is set to true for any report that has undergone at least one revision cycle. This flag simplifies the calculation of the 'Expense Report Revision Rate' KPI and allows for easy filtering and comparison between reports that were approved on the first pass and those that required rework. Analyzing these two cohorts can reveal the time and cost impact of revisions. Why it matters It easily segments the process for rework analysis, helping to quantify the frequency and impact of reports being sent back for correction. Where to get This is a calculated field, derived during data transformation by checking for the existence of a revision activity within the case. Examples truefalse
	Last Data Update LastDataUpdate	The timestamp indicating the last time the data was refreshed from the source system.
Description This attribute records the date and time of the most recent data extraction. It provides context on the freshness of the data being analyzed. In any analysis, knowing the recency of the data is crucial for interpreting the results correctly. This attribute helps users understand if they are looking at up-to-date information. Why it matters It informs users about the timeliness of the data, ensuring analyses are based on current and relevant information. Where to get This timestamp is generated and added during the data extraction process. Examples 2023-11-01T06:00:00Z
	Manager Approval Time ManagerApprovalTime	The time taken from when a report is pending manager review to when the manager approves or rejects it.
Description This metric calculates the duration of the manager approval stage. It is the time difference between the 'Manager Review Pending' activity and the subsequent 'Manager Approved' or 'Manager Rejected' activity. This duration is used to calculate the 'Average Manager Approval Time' KPI and power the 'Manager Approval Cycle Time' dashboard. It helps pinpoint delays in the first level of approval, which is often a significant bottleneck. Why it matters It isolates the duration of a critical approval step, helping to identify and address bottlenecks caused by manager reviews. Where to get This is calculated from the event log by finding the time difference between the manager review pending and completion events. Examples 1 hour 15 minutes2 days 3 hours5 hours 30 minutes
	Reimbursement Method ReimbursementMethod	The method used to execute the reimbursement payment, such as ACH or wire transfer.
Description This attribute specifies the payment channel through which the employee was reimbursed. Different methods may have different processing times and costs associated with them. Analyzing reimbursement performance by method helps to identify the most efficient payment channels. The 'Reimbursement Method Performance' dashboard uses this data to compare cycle times and reliability, potentially leading to the optimization of payment strategies. Why it matters It allows for performance comparison between different payment channels, helping to optimize for speed and reliability. Where to get This information should be available in the payment or reimbursement records within Ramp. Examples ACH TransferCorporate Card CreditDirect Deposit
	Source System SourceSystem	Identifies the source application from which the data was extracted.
Description This attribute specifies the system of record for the process data, which in this case is Ramp. It is useful in environments where data might be blended from multiple systems, ensuring clear data lineage. For analysis, it helps in filtering data from a specific source and can be used for data validation and governance purposes. Why it matters It provides context about the data's origin, which is crucial for data governance and when integrating data from multiple systems. Where to get This is typically a static value ('Ramp') added during the data extraction and transformation process. Examples Ramp
	Submission Method SubmissionMethod	The channel through which the expense report was submitted, such as mobile app or web portal.
Description This attribute indicates how the employee submitted their expense report. Common methods include using a mobile application, a desktop web browser, or email forwarding. Analyzing the submission method can provide insights into user behavior and technology adoption. For example, a high rate of revisions for reports submitted via a certain channel might indicate usability issues with that channel's interface. Why it matters It provides context on user behavior and can help identify if certain submission channels are associated with higher error rates or delays. Where to get This information may be captured in the metadata of the submission event in Ramp's system logs. Examples Mobile AppWeb PortalEmail
	Total Reimbursement Cycle Time TotalReimbursementCycleTime	The total time elapsed from when an expense report was submitted to when the reimbursement was executed.
Description This calculated metric measures the end-to-end duration of the reimbursement process from the employee's point of view. It is calculated as the time difference between the 'Expense Submitted' and 'Reimbursement Executed' events for each case. This attribute is the basis for the 'Average Reimbursement Cycle Time' KPI and the 'End-to-End Reimbursement Cycle' dashboard. It provides a high-level measure of process efficiency and is a key indicator of employee satisfaction. Why it matters It quantifies the overall process duration, providing a key performance indicator for measuring end-to-end efficiency. Where to get This is calculated during data transformation by subtracting the timestamp of the first submission event from the final reimbursement event. Examples 3 days 4 hours10 days 1 hour1 day 8 hours

Required Recommended Optional

Expense Management Activities

These are the key process steps and milestones to capture in your event log for accurate process discovery and performance measurement.

5 Recommended 7 Optional

	Activity	Description
	Expense Incurred	Marks the creation of an expense, typically initiated automatically when a corporate card is used or when an employee manually creates an out-of-pocket expense record. This event is usually captured from transaction data feeds or user interface actions.
Why it matters This is the primary start event for the expense lifecycle. Analyzing the time from this event helps understand submission delays and overall process velocity. Where to get Generated from Ramp card transaction logs or the creation timestamp of a manually entered expense object. Look for the initial record creation event in the expense or transaction table. Capture Directly logged when a card transaction is processed or a user creates a new expense entry. Event type explicit
	Expense Submitted	An employee confirms that all necessary information for an expense is complete and submits it for the approval process. This is an explicit user action that moves the expense from a 'draft' or 'needs attention' state to a 'pending approval' state.
Why it matters This activity is a critical milestone that officially starts the approval and reimbursement cycle. It is the baseline for measuring approval and reimbursement SLAs. Where to get Captured from the expense object's status history. This corresponds to the user action of submitting the transaction for review. Capture Logged when the user clicks the 'Submit' button, triggering a status change. Event type explicit
	Manager Approved	The manager has reviewed the expense and approved it, allowing it to proceed to the next step, such as finance review or reimbursement. This is captured through an explicit user action.
Why it matters This is a key milestone indicating successful completion of the first level of approval. It's crucial for analyzing approval workflows and identifying bottlenecks. Where to get Recorded as an event in the expense's approval history when the manager clicks 'Approve'. The event log should contain the approver's ID and a timestamp. Capture An event is logged upon the 'Approve' action by a user with manager permissions. Event type explicit
	Reimbursement Executed	The reimbursement payment has been successfully processed and sent to the employee. This is typically the final step for the employee and marks the end of the payment cycle.
Why it matters This is the primary end event for the reimbursement process. The duration from submission to this point is a critical KPI for employee satisfaction and process efficiency. Where to get Captured from payment processing logs or an integration with the payment provider. The expense status in Ramp would be updated to 'Reimbursed' or 'Paid'. Capture An event is logged when the payment system confirms successful payment transfer. Event type explicit
	Synced with Accounting System	The expense transaction data has been successfully posted to the integrated accounting system, such as NetSuite, QuickBooks, or Xero. This event marks the completion of the financial record-keeping part of the process.
Why it matters This is the final activity in the end-to-end process. Delays here can impact the accuracy of financial reporting and the speed of the financial close. Where to get Recorded in an integration log or as a status update on the expense object in Ramp. Look for a status like 'Synced', 'Posted', or 'Exported'. Capture A log entry is created by the accounting integration service upon successful data synchronization. Event type explicit
	Expense Sent Back for Revision	An approver, either a manager or finance reviewer, has rejected the expense and sent it back to the employee for correction. This is captured by a status change to a 'Needs Revision' or 'Rejected' state.
Why it matters This activity signifies a rework loop in the process, which directly increases cycle time. Tracking these events helps identify common submission errors and improve first-pass yield. Where to get Inferred from a status change on the expense object to 'Needs Revision' or a similar state. The event should be linked to the approver who initiated the action. Capture Derived from the timestamp when the expense status becomes 'Needs Revision' or 'Rejected'. Event type inferred
	Finance Approved	The finance team has reviewed and given final approval for the expense, clearing it for reimbursement and accounting sync. This is captured as an explicit user action by a member of the finance team.
Why it matters Represents the final approval gate before payment. Analyzing this activity helps understand the end-to-end approval cycle and finance team efficiency. Where to get Logged as an event in the expense's approval history. Look for an approval event associated with a user from the finance department. Capture An event is logged upon the 'Approve' action by a user with finance permissions. Event type explicit
	Finance Review Pending	An approved expense has been escalated and is now awaiting review from the finance or accounting team. This typically happens for high-value expenses or those with policy flags. The activity is inferred from a status change.
Why it matters Marks the beginning of the finance review cycle. Measuring this stage's duration helps assess finance team workload and efficiency, and identifies opportunities for automation. Where to get Inferred from a status change on the expense object to 'Pending Finance Approval' after manager approval. This requires access to the expense's status history. Capture Derived from the timestamp when the expense status updates to 'Pending Finance Review'. Event type inferred
	Manager Review Pending	The expense has been submitted and is now awaiting review from the employee's direct manager. This state is inferred when the expense status changes to 'Pending Manager Approval' or a similar value after submission.
Why it matters Identifies the start of the manager approval stage. Analyzing the time spent in this state is key to measuring and improving manager approval cycle times. Where to get Inferred from a status change on the expense object to a state like 'Pending Approval' and assignment to a manager's queue. Requires tracking status history. Capture Derived from the timestamp when the expense status becomes 'Pending Manager Approval'. Event type inferred
	Policy Check Performed	The system automatically audits the expense against configured company policies, flagging any potential violations. This is typically a system-generated event that occurs shortly after submission.
Why it matters Measures the efficiency of automated compliance checks and their impact on the process. Helps identify common policy violations and areas for employee training. Where to get Likely recorded in an audit trail or log associated with the expense transaction. Look for system events related to 'policy_check' or 'compliance_scan'. Capture A system log entry is created after the automated policy engine runs on the transaction. Event type explicit
	Receipt Attached	Represents the moment a receipt is associated with an expense, either automatically via OCR matching or manually by the user. This is captured when the receipt file is successfully linked to the transaction record.
Why it matters Tracking this activity helps identify delays caused by missing documentation. It is a key step for ensuring compliance and audit readiness. Where to get Logged in the expense or transaction history when a receipt is uploaded or matched. Check for an attachment creation event or a flag indicating 'receipt_attached'. Capture Event is created when the system links a receipt image or file to the expense record. Event type explicit
	Reimbursement Scheduled	For out-of-pocket expenses, the approved amount has been queued for payment processing. This event indicates that the expense has passed all approvals and is ready to be paid out.
Why it matters This milestone separates the approval process from the payment execution process. It helps isolate delays that occur in payment processing versus those in approvals. Where to get Likely inferred from the expense status changing to 'Pending Reimbursement' or 'Ready for Payment' after final approval. May also be an explicit event if payments are batched. Capture Derived from a status change to 'Ready for Payout' or from the creation of a record in a payment batch table. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from Ramp

Steps

Log in to your Ramp account with Admin or Bookkeeper privileges.
Navigate to the Transactions section from the left-hand menu. This view contains all corporate card spending.
Apply the desired filters to the data. At a minimum, set a Date Range for the period you wish to analyze. You can also filter by other properties like Status, Category, or Department to narrow the scope if needed.
Click the Export button located at the top right of the transaction list.
In the export modal, select Transactions as the export type and CSV as the format. Download the file and name it transactions_export.csv.
Next, navigate to the Reimbursements section from the left-hand menu. This view contains all out-of-pocket expenses submitted by employees.
Apply the same date range filter used for the transaction export to ensure data consistency.
Click the Export button and download the reimbursements data as a CSV file. Name this file reimbursements_export.csv.
Place both exported CSV files (transactions_export.csv and reimbursements_export.csv) in the same directory as the provided Python script.
Execute the Python script. The script will load both files, transform the data by creating a distinct row for each business event, and merge them into a single, structured event log.
The script will output a file named ramp_event_log.csv. This file is formatted according to ProcessMind requirements.
Upload the ramp_event_log.csv file to ProcessMind for analysis.

Configuration

Permissions: You must have 'Admin' or 'Bookkeeper' roles in Ramp to access and export the complete transaction and reimbursement datasets.
Date Range: For an initial analysis, select a date range of at least 6 to 12 months to capture enough process variations. For ongoing monitoring, you can use a shorter, more recent range.
Data Consistency: Ensure you use the exact same date range and filters when exporting both the Transactions and Reimbursements files to maintain a consistent dataset.
Export Format: Always choose the CSV format for both exports, as the transformation script is designed to process comma-separated files.
File Naming: Adhere to the file names transactions_export.csv and reimbursements_export.csv or update the file path variables in the transformation script accordingly.

a Sample Query config

import pandas as pd
import numpy as np

# --- Configuration ---
TRANSACTIONS_FILE = 'transactions_export.csv'
REIMBURSEMENTS_FILE = 'reimbursements_export.csv'
OUTPUT_FILE = 'ramp_event_log.csv'

# --- Load Data ---
def load_data():
    try:
        transactions_df = pd.read_csv(TRANSACTIONS_FILE)
        reimbursements_df = pd.read_csv(REIMBURSEMENTS_FILE)
        print("Successfully loaded transactions and reimbursements files.")
        return transactions_df, reimbursements_df
    except FileNotFoundError as e:
        print(f"Error: {e}. Make sure '{TRANSACTIONS_FILE}' and '{REIMBURSEMENTS_FILE}' are in the same directory.")
        return None, None

# --- Main Transformation Logic ---
def transform_to_event_log(transactions_df, reimbursements_df):
    events = []

    # Standardize column names for easier processing
    # These names are based on typical Ramp CSV exports, adjust if your export has different headers
    transactions_df.rename(columns={
        'Transaction ID': 'ExpenseReportId',
        'User Name': 'UserName',
        'Department': 'EmployeeDepartment',
        'Amount': 'ReportTotalAmount',
        'Category Name': 'ExpenseCategory',
        'Transaction Date': 'ExpenseIncurredTimestamp',
        'Receipts Attached At': 'ReceiptAttachedTimestamp',
        'Submitted At': 'ExpenseSubmittedTimestamp',
        'Manager Approved At': 'ManagerApprovedTimestamp',
        'Needs Review At': 'FinanceReviewPendingTimestamp',
        'Admin Approved At': 'FinanceApprovedTimestamp',
        'Accounting Sync At': 'SyncedTimestamp',
        'Memo': 'RevisionReason',
        'State': 'Status'
    }, inplace=True)

    reimbursements_df.rename(columns={
        'Reimbursement ID': 'ExpenseReportId',
        'User Name': 'UserName',
        'Department': 'EmployeeDepartment',
        'Amount': 'ReportTotalAmount',
        'Category Name': 'ExpenseCategory',
        'Created At': 'ReimbursementScheduledTimestamp',
        'Paid Out At': 'ReimbursementExecutedTimestamp',
        'Memo': 'RevisionReason',
        'State': 'Status'
    }, inplace=True)

    # Combine card transactions and reimbursements into a single dataframe for processing common events
    # Note: Reimbursements will be processed separately for reimbursement-specific events
    combined_df = transactions_df

    # Process activities from the combined dataframe
    for _, row in combined_df.iterrows():
        base_event = {
            'ExpenseReportId': row.get('ExpenseReportId'),
            'UserName': row.get('UserName'),
            'EmployeeDepartment': row.get('EmployeeDepartment'),
            'ReportTotalAmount': row.get('ReportTotalAmount'),
            'ExpenseCategory': row.get('ExpenseCategory'),
            'PolicyViolationFlag': 'FLAGGED' in str(row.get('Flags')), # Infer from 'Flags' column
            'RevisionReason': row.get('RevisionReason')
        }

        # Activity: Expense Incurred
        if pd.notna(row.get('ExpenseIncurredTimestamp')):
            events.append({**base_event, 'ActivityName': 'Expense Incurred', 'EventTimestamp': row.get('ExpenseIncurredTimestamp')})

        # Activity: Receipt Attached
        if pd.notna(row.get('ReceiptAttachedTimestamp')):
            events.append({**base_event, 'ActivityName': 'Receipt Attached', 'EventTimestamp': row.get('ReceiptAttachedTimestamp')})

        # Activity: Expense Submitted
        if pd.notna(row.get('ExpenseSubmittedTimestamp')):
            events.append({**base_event, 'ActivityName': 'Expense Submitted', 'EventTimestamp': row.get('ExpenseSubmittedTimestamp')})
            # Inferred Activity: Policy Check Performed (assumed to happen right after submission)
            policy_check_ts = pd.to_datetime(row.get('ExpenseSubmittedTimestamp')) + pd.Timedelta(seconds=1)
            events.append({**base_event, 'ActivityName': 'Policy Check Performed', 'EventTimestamp': policy_check_ts.isoformat()})
            # Inferred Activity: Manager Review Pending (starts when submitted)
            events.append({**base_event, 'ActivityName': 'Manager Review Pending', 'EventTimestamp': row.get('ExpenseSubmittedTimestamp')})

        # Activity: Manager Approved
        if pd.notna(row.get('ManagerApprovedTimestamp')):
            events.append({**base_event, 'ActivityName': 'Manager Approved', 'EventTimestamp': row.get('ManagerApprovedTimestamp')})

        # Activity: Finance Review Pending
        if pd.notna(row.get('FinanceReviewPendingTimestamp')):
            events.append({**base_event, 'ActivityName': 'Finance Review Pending', 'EventTimestamp': row.get('FinanceReviewPendingTimestamp')})

        # Activity: Finance Approved
        if pd.notna(row.get('FinanceApprovedTimestamp')):
            events.append({**base_event, 'ActivityName': 'Finance Approved', 'EventTimestamp': row.get('FinanceApprovedTimestamp')})

        # Activity: Synced with Accounting System
        if pd.notna(row.get('SyncedTimestamp')):
            events.append({**base_event, 'ActivityName': 'Synced with Accounting System', 'EventTimestamp': row.get('SyncedTimestamp')})

        # Activity: Expense Sent Back for Revision
        if row.get('Status') == 'REJECTED' and pd.notna(row.get('Updated At')):
             events.append({**base_event, 'ActivityName': 'Expense Sent Back for Revision', 'EventTimestamp': row.get('Updated At')}) # Using last updated time for rejection

    # Process reimbursement-specific activities
    for _, row in reimbursements_df.iterrows():
        base_event = {
            'ExpenseReportId': row.get('ExpenseReportId'),
            'UserName': row.get('UserName'),
            'EmployeeDepartment': row.get('EmployeeDepartment'),
            'ReportTotalAmount': row.get('ReportTotalAmount'),
            'ExpenseCategory': row.get('ExpenseCategory'),
            'PolicyViolationFlag': False, # Typically less flagging on reimbursements
            'RevisionReason': row.get('RevisionReason')
        }

        # Activity: Reimbursement Scheduled
        if row.get('Status') == 'APPROVED' and pd.notna(row.get('ReimbursementScheduledTimestamp')):
            events.append({**base_event, 'ActivityName': 'Reimbursement Scheduled', 'EventTimestamp': row.get('ReimbursementScheduledTimestamp')})

        # Activity: Reimbursement Executed
        if row.get('Status') == 'PAID' and pd.notna(row.get('ReimbursementExecutedTimestamp')):
            events.append({**base_event, 'ActivityName': 'Reimbursement Executed', 'EventTimestamp': row.get('ReimbursementExecutedTimestamp')})

    if not events:
        print("Warning: No events were generated. Check the input files and column names.")
        return None

    # Create final DataFrame
    event_log_df = pd.DataFrame(events)
    event_log_df['EventTimestamp'] = pd.to_datetime(event_log_df['EventTimestamp'])
    event_log_df.sort_values(by=['ExpenseReportId', 'EventTimestamp'], inplace=True)
    
    return event_log_df

# --- Main execution block ---
if __name__ == "__main__":
    transactions_df, reimbursements_df = load_data()
    if transactions_df is not None and reimbursements_df is not None:
        event_log = transform_to_event_log(transactions_df, reimbursements_df)
        if event_log is not None:
            event_log.to_csv(OUTPUT_FILE, index=False, date_format='%Y-%m-%dT%H:%M:%S.%fZ')
            print(f"Successfully created event log with {len(event_log)} events. Saved to '{OUTPUT_FILE}'.")

Steps

Prerequisites and Environment Setup: Ensure you have Python 3.8 or newer installed. Install the necessary Python libraries by running pip install requests pandas. You must also have administrator access to your Ramp instance to generate API credentials.
Generate Ramp API Credentials: Log in to your Ramp account. Navigate to Settings, then select Developer API from the side menu. Click 'Create App', give your application a name, and configure the required scopes. You will need transactions:read, reimbursements:read, and users:read. Once created, securely copy the Client ID and Client Secret.
Configure the Python Script: Create a new Python file, for example extract_ramp_log.py. Copy the provided Python script into this file. Set your Client ID and Client Secret as environment variables or directly in the script for testing purposes. It is highly recommended to use environment variables for security.
Set Extraction Date Range: In the script, locate the START_DATE and END_DATE variables within the main execution block. Adjust these dates to define the time window for the data extraction. A period of 3 to 6 months is recommended for an initial analysis.
Execute the Script: Run the script from your terminal using the command python extract_ramp_log.py. The script will authenticate with the Ramp API, fetch data from the users, transactions, and reimbursements endpoints, and handle pagination automatically.
Understand the API Calls: The script first fetches all user data to create a mapping of user IDs to names and departments. It then retrieves all reimbursements within the specified date range. For each reimbursement, it extracts the approval and payment history.
Transforming Data into an Event Log: The core logic of the script iterates through each reimbursement and its associated state history. It translates each state change, such as 'MANAGER_APPROVAL' or 'FINISHED', into a distinct activity like 'Manager Review Pending' or 'Reimbursement Executed'. It also fetches associated transaction data to create 'Expense Incurred' and 'Receipt Attached' events.
Handling Data Enrichment: The script enriches the event log by adding user details, department information, and expense category for each event, creating a comprehensive dataset for process analysis.
Review the Output: Once the script completes, it will generate a file named ramp_event_log.csv in the same directory. Open this CSV file in a spreadsheet program to inspect the data and ensure it has been extracted as expected.
Prepare for Upload: The generated CSV file is structured with the required columns: ExpenseReportId, ActivityName, EventTimestamp, and other recommended attributes. This file is ready for direct upload into the ProcessMind platform.

Configuration

API Credentials: A valid Client ID and Client Secret are required for authentication. These are generated within the Ramp dashboard under Settings > Developer API.
API Scopes: The script requires specific permissions to access data. Ensure the following scopes are enabled for your API key: transactions:read (for expense and receipt details), reimbursements:read (for submission, approval, and payment status), and users:read (for employee name and department).
Date Range: The extraction is governed by START_DATE and END_DATE parameters in the script. It's recommended to start with a recent period, such as the last 3-6 months, to keep the initial dataset manageable. The filter is applied to the reimbursement creation date.
Pagination: The script automatically handles API pagination using the starting_after cursor provided by the Ramp API. It fetches records in batches (up to 100 per request) to ensure all data within the date range is retrieved.
Rate Limiting: The Ramp API is subject to rate limits, typically 600 requests per minute. The provided script is generally efficient, but for extremely large datasets, you might need to introduce small delays between API calls if you encounter rate limit errors.

a Sample Query python

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

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># It is recommended to use environment variables for credentials
CLIENT_ID = os.getenv(class="hl-string">'RAMP_CLIENT_ID', class="hl-string">'[Your_Ramp_Client_ID]')
CLIENT_SECRET = os.getenv(class="hl-string">'RAMP_CLIENT_SECRET', class="hl-string">'[Your_Ramp_Client_Secret]')

API_BASE_URL = class="hl-string">'https://api.ramp.com/v1'
TOKEN_URL = class="hl-string">'https://api.ramp.com/v1/public/customer/token'

class=class="hl-string">"hl-comment"># --- Main Functions ---

def get_access_token():
    class="hl-string">"""Authenticates with the Ramp API to get a bearer token."""
    headers = {class="hl-string">'Content-Type': class="hl-string">'application/x-www-form-urlencoded'}
    data = {
        class="hl-string">'grant_type': class="hl-string">'client_credentials',
        class="hl-string">'client_id': CLIENT_ID,
        class="hl-string">'client_secret': CLIENT_SECRET,
        class="hl-string">'scope': class="hl-string">'transactions:read reimbursements:read users:read'
    }
    try:
        response = requests.post(TOKEN_URL, headers=headers, data=data)
        response.raise_for_status()
        return response.json()[class="hl-string">'access_token']
    except requests.exceptions.RequestException as e:
        print(fclass="hl-string">"Error obtaining access token: {e}")
        if e.response is not None:
            print(fclass="hl-string">"Response body: {e.response.text}")
        return None

def fetch_paginated_data(endpoint, access_token, params=None):
    class="hl-string">"""Fetches all data from a paginated Ramp API endpoint."""
    all_data = []
    headers = {class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}'}
    url = fclass="hl-string">"{API_BASE_URL}{endpoint}"
    
    while url:
        try:
            response = requests.get(url, headers=headers, params=params)
            response.raise_for_status()
            data = response.json()
            all_data.extend(data.get(class="hl-string">'data', []))
            url = data.get(class="hl-string">'next') class=class="hl-string">"hl-comment"># Ramp uses a full URL for the next page
            params = None class=class="hl-string">"hl-comment"># Subsequent requests use the class="hl-string">'next' URL which includes params
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Error fetching data from {endpoint}: {e}")
            break
            
    return all_data

def map_state_to_activity(state, reason_code):
    class="hl-string">"""Maps reimbursement state history to a user-friendly activity name."""
    if reason_code == class="hl-string">'REJECTED':
        return class="hl-string">'Expense Sent Back for Revision'
    
    state_map = {
        class="hl-string">'SUBMITTED': class="hl-string">'Expense Submitted',
        class="hl-string">'MANAGER_APPROVAL': class="hl-string">'Manager Review Pending',
        class="hl-string">'ACCOUNTING_APPROVAL': class="hl-string">'Finance Review Pending',
        class="hl-string">'MANAGER_APPROVED': class="hl-string">'Manager Approved',
        class="hl-string">'ACCOUNTING_APPROVED': class="hl-string">'Finance Approved',
        class="hl-string">'PAYMENT_SCHEDULED': class="hl-string">'Reimbursement Scheduled',
        class="hl-string">'PAYMENT_PROCESSING': class="hl-string">'Reimbursement Executed', class=class="hl-string">"hl-comment"># Approximation
        class="hl-string">'FINISHED': class="hl-string">'Reimbursement Executed',
        class="hl-string">'SYNCED': class="hl-string">'Synced with Accounting System'
    }
    return state_map.get(state)

class=class="hl-string">"hl-comment"># --- Main Execution ---
def main():
    class="hl-string">"""Main script to extract, transform, and save the event log."""
    print(class="hl-string">"Starting Ramp data extraction...")
    access_token = get_access_token()
    if not access_token:
        print(class="hl-string">"Failed to get access token. Exiting.")
        return

    class=class="hl-string">"hl-comment"># 1. Fetch Users for enrichment
    print(class="hl-string">"Fetching users...")
    users = fetch_paginated_data(class="hl-string">'/users', access_token)
    user_map = {u[class="hl-string">'id']: {class="hl-string">'name': fclass="hl-string">"{u['first_name']} {u['last_name']}", class="hl-string">'department': u.get(class="hl-string">'department')} for u in users}
    print(fclass="hl-string">"Found {len(user_map)} users.")

    class=class="hl-string">"hl-comment"># 2. Fetch Reimbursements within a date range
    START_DATE = class="hl-string">'2023-01-01T00:00:00Z'
    END_DATE = class="hl-string">'2023-12-31T23:59:59Z'
    print(fclass="hl-string">"Fetching reimbursements from {START_DATE} to {END_DATE}...")
    reimbursement_params = {class="hl-string">'created_at_gte': START_DATE, class="hl-string">'created_at_lte': END_DATE}
    reimbursements = fetch_paginated_data(class="hl-string">'/reimbursements', access_token, params=reimbursement_params)
    print(fclass="hl-string">"Found {len(reimbursements)} reimbursements.")

    event_log = []

    class=class="hl-string">"hl-comment"># 3. Process each reimbursement to create the event log
    for r in reimbursements:
        case_id = r[class="hl-string">'id']
        report_total = r[class="hl-string">'amount']
        submitter_id = r[class="hl-string">'user_id']
        submitter_details = user_map.get(submitter_id, {class="hl-string">'name': class="hl-string">'Unknown User', class="hl-string">'department': None})
        expense_category = r.get(class="hl-string">'merchant_name') class=class="hl-string">"hl-comment"># Best available proxy for category
        
        class=class="hl-string">"hl-comment"># Process reimbursement state history
        for history in r.get(class="hl-string">'state_history', []):
            activity_name = map_state_to_activity(history[class="hl-string">'state'], history.get(class="hl-string">'reason_code'))
            if activity_name:
                event_log.append({
                    class="hl-string">'ExpenseReportId': case_id,
                    class="hl-string">'ActivityName': activity_name,
                    class="hl-string">'EventTimestamp': history[class="hl-string">'created_at'],
                    class="hl-string">'UserName': user_map.get(history.get(class="hl-string">'user_id'), submitter_details)[class="hl-string">'name'],
                    class="hl-string">'EmployeeDepartment': submitter_details[class="hl-string">'department'],
                    class="hl-string">'ReportTotalAmount': report_total,
                    class="hl-string">'ExpenseCategory': expense_category,
                    class="hl-string">'PolicyViolationFlag': None, class=class="hl-string">"hl-comment"># Not directly available in reimbursement history
                    class="hl-string">'RevisionReason': history.get(class="hl-string">'reason_notes')
                })

        class=class="hl-string">"hl-comment"># Fetch and process associated transactions
        for tx_id in r.get(class="hl-string">'transaction_ids', []):
            try:
                tx_response = requests.get(fclass="hl-string">"{API_BASE_URL}/transactions/{tx_id}", headers={class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}'})
                tx_response.raise_for_status()
                tx = tx_response.json()

                class=class="hl-string">"hl-comment"># Activity: Expense Incurred
                event_log.append({
                    class="hl-string">'ExpenseReportId': case_id,
                    class="hl-string">'ActivityName': class="hl-string">'Expense Incurred',
                    class="hl-string">'EventTimestamp': tx[class="hl-string">'user_transaction_time'],
                    class="hl-string">'UserName': submitter_details[class="hl-string">'name'],
                    class="hl-string">'EmployeeDepartment': submitter_details[class="hl-string">'department'],
                    class="hl-string">'ReportTotalAmount': report_total,
                    class="hl-string">'ExpenseCategory': tx.get(class="hl-string">'merchant_name'),
                    class="hl-string">'PolicyViolationFlag': tx.get(class="hl-string">'flagged_for_review'),
                    class="hl-string">'RevisionReason': None
                })

                class=class="hl-string">"hl-comment"># Activity: Receipt Attached
                if tx.get(class="hl-string">'receipts') and len(tx[class="hl-string">'receipts']) > 0:
                    receipt_time = tx[class="hl-string">'receipts'][0].get(class="hl-string">'upload_time', tx[class="hl-string">'user_transaction_time']) class=class="hl-string">"hl-comment"># Use transaction time as fallback
                    event_log.append({
                        class="hl-string">'ExpenseReportId': case_id,
                        class="hl-string">'ActivityName': class="hl-string">'Receipt Attached',
                        class="hl-string">'EventTimestamp': receipt_time,
                        class="hl-string">'UserName': submitter_details[class="hl-string">'name'],
                        class="hl-string">'EmployeeDepartment': submitter_details[class="hl-string">'department'],
                        class="hl-string">'ReportTotalAmount': report_total,
                        class="hl-string">'ExpenseCategory': tx.get(class="hl-string">'merchant_name'),
                        class="hl-string">'PolicyViolationFlag': tx.get(class="hl-string">'flagged_for_review'),
                        class="hl-string">'RevisionReason': None
                    })

                class=class="hl-string">"hl-comment"># Activity: Policy Check Performed (Approximation)
                if tx.get(class="hl-string">'flagged_for_review'):
                    event_log.append({
                        class="hl-string">'ExpenseReportId': case_id,
                        class="hl-string">'ActivityName': class="hl-string">'Policy Check Performed',
                        class="hl-string">'EventTimestamp': r.get(class="hl-string">'created_at'), class=class="hl-string">"hl-comment"># Use submission time as proxy
                        class="hl-string">'UserName': class="hl-string">'System',
                        class="hl-string">'EmployeeDepartment': submitter_details[class="hl-string">'department'],
                        class="hl-string">'ReportTotalAmount': report_total,
                        class="hl-string">'ExpenseCategory': tx.get(class="hl-string">'merchant_name'),
                        class="hl-string">'PolicyViolationFlag': True,
                        class="hl-string">'RevisionReason': None
                    })

            except requests.exceptions.RequestException as e:
                print(fclass="hl-string">"Could not fetch transaction {tx_id}: {e}")

    if not event_log:
        print(class="hl-string">"No events were generated. Check the date range and API responses.")
        return

    class=class="hl-string">"hl-comment"># 4. Create and save DataFrame
    print(class="hl-string">"Finalizing event log...")
    df = pd.DataFrame(event_log)
    df[class="hl-string">'EventTimestamp'] = pd.to_datetime(df[class="hl-string">'EventTimestamp'])
    df = df.sort_values(by=[class="hl-string">'ExpenseReportId', class="hl-string">'EventTimestamp'])

    class=class="hl-string">"hl-comment"># Ensure required columns are present
    final_columns = [
        class="hl-string">'ExpenseReportId', class="hl-string">'ActivityName', class="hl-string">'EventTimestamp', class="hl-string">'UserName',
        class="hl-string">'EmployeeDepartment', class="hl-string">'ReportTotalAmount', class="hl-string">'ExpenseCategory',
        class="hl-string">'PolicyViolationFlag', class="hl-string">'RevisionReason'
    ]
    for col in final_columns:
        if col not in df.columns:
            df[col] = None
    
    df = df[final_columns]

    output_filename = class="hl-string">'ramp_event_log.csv'
    df.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%S.%f%z')
    print(fclass="hl-string">"Extraction complete. Event log saved to {output_filename}")

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

Steps

Log in to your Ramp account with Admin or Bookkeeper privileges.
Navigate to the Transactions section from the left-hand menu. This view contains all corporate card spending.
Apply the desired filters to the data. At a minimum, set a Date Range for the period you wish to analyze. You can also filter by other properties like Status, Category, or Department to narrow the scope if needed.
Click the Export button located at the top right of the transaction list.
In the export modal, select Transactions as the export type and CSV as the format. Download the file and name it transactions_export.csv.
Next, navigate to the Reimbursements section from the left-hand menu. This view contains all out-of-pocket expenses submitted by employees.
Apply the same date range filter used for the transaction export to ensure data consistency.
Click the Export button and download the reimbursements data as a CSV file. Name this file reimbursements_export.csv.
Place both exported CSV files (transactions_export.csv and reimbursements_export.csv) in the same directory as the provided Python script.
Execute the Python script. The script will load both files, transform the data by creating a distinct row for each business event, and merge them into a single, structured event log.
The script will output a file named ramp_event_log.csv. This file is formatted according to ProcessMind requirements.
Upload the ramp_event_log.csv file to ProcessMind for analysis.

Configuration

Permissions: You must have 'Admin' or 'Bookkeeper' roles in Ramp to access and export the complete transaction and reimbursement datasets.
Date Range: For an initial analysis, select a date range of at least 6 to 12 months to capture enough process variations. For ongoing monitoring, you can use a shorter, more recent range.
Data Consistency: Ensure you use the exact same date range and filters when exporting both the Transactions and Reimbursements files to maintain a consistent dataset.
Export Format: Always choose the CSV format for both exports, as the transformation script is designed to process comma-separated files.
File Naming: Adhere to the file names transactions_export.csv and reimbursements_export.csv or update the file path variables in the transformation script accordingly.

a Sample Query config

import pandas as pd
import numpy as np

# --- Configuration ---
TRANSACTIONS_FILE = 'transactions_export.csv'
REIMBURSEMENTS_FILE = 'reimbursements_export.csv'
OUTPUT_FILE = 'ramp_event_log.csv'

# --- Load Data ---
def load_data():
    try:
        transactions_df = pd.read_csv(TRANSACTIONS_FILE)
        reimbursements_df = pd.read_csv(REIMBURSEMENTS_FILE)
        print("Successfully loaded transactions and reimbursements files.")
        return transactions_df, reimbursements_df
    except FileNotFoundError as e:
        print(f"Error: {e}. Make sure '{TRANSACTIONS_FILE}' and '{REIMBURSEMENTS_FILE}' are in the same directory.")
        return None, None

# --- Main Transformation Logic ---
def transform_to_event_log(transactions_df, reimbursements_df):
    events = []

    # Standardize column names for easier processing
    # These names are based on typical Ramp CSV exports, adjust if your export has different headers
    transactions_df.rename(columns={
        'Transaction ID': 'ExpenseReportId',
        'User Name': 'UserName',
        'Department': 'EmployeeDepartment',
        'Amount': 'ReportTotalAmount',
        'Category Name': 'ExpenseCategory',
        'Transaction Date': 'ExpenseIncurredTimestamp',
        'Receipts Attached At': 'ReceiptAttachedTimestamp',
        'Submitted At': 'ExpenseSubmittedTimestamp',
        'Manager Approved At': 'ManagerApprovedTimestamp',
        'Needs Review At': 'FinanceReviewPendingTimestamp',
        'Admin Approved At': 'FinanceApprovedTimestamp',
        'Accounting Sync At': 'SyncedTimestamp',
        'Memo': 'RevisionReason',
        'State': 'Status'
    }, inplace=True)

    reimbursements_df.rename(columns={
        'Reimbursement ID': 'ExpenseReportId',
        'User Name': 'UserName',
        'Department': 'EmployeeDepartment',
        'Amount': 'ReportTotalAmount',
        'Category Name': 'ExpenseCategory',
        'Created At': 'ReimbursementScheduledTimestamp',
        'Paid Out At': 'ReimbursementExecutedTimestamp',
        'Memo': 'RevisionReason',
        'State': 'Status'
    }, inplace=True)

    # Combine card transactions and reimbursements into a single dataframe for processing common events
    # Note: Reimbursements will be processed separately for reimbursement-specific events
    combined_df = transactions_df

    # Process activities from the combined dataframe
    for _, row in combined_df.iterrows():
        base_event = {
            'ExpenseReportId': row.get('ExpenseReportId'),
            'UserName': row.get('UserName'),
            'EmployeeDepartment': row.get('EmployeeDepartment'),
            'ReportTotalAmount': row.get('ReportTotalAmount'),
            'ExpenseCategory': row.get('ExpenseCategory'),
            'PolicyViolationFlag': 'FLAGGED' in str(row.get('Flags')), # Infer from 'Flags' column
            'RevisionReason': row.get('RevisionReason')
        }

        # Activity: Expense Incurred
        if pd.notna(row.get('ExpenseIncurredTimestamp')):
            events.append({**base_event, 'ActivityName': 'Expense Incurred', 'EventTimestamp': row.get('ExpenseIncurredTimestamp')})

        # Activity: Receipt Attached
        if pd.notna(row.get('ReceiptAttachedTimestamp')):
            events.append({**base_event, 'ActivityName': 'Receipt Attached', 'EventTimestamp': row.get('ReceiptAttachedTimestamp')})

        # Activity: Expense Submitted
        if pd.notna(row.get('ExpenseSubmittedTimestamp')):
            events.append({**base_event, 'ActivityName': 'Expense Submitted', 'EventTimestamp': row.get('ExpenseSubmittedTimestamp')})
            # Inferred Activity: Policy Check Performed (assumed to happen right after submission)
            policy_check_ts = pd.to_datetime(row.get('ExpenseSubmittedTimestamp')) + pd.Timedelta(seconds=1)
            events.append({**base_event, 'ActivityName': 'Policy Check Performed', 'EventTimestamp': policy_check_ts.isoformat()})
            # Inferred Activity: Manager Review Pending (starts when submitted)
            events.append({**base_event, 'ActivityName': 'Manager Review Pending', 'EventTimestamp': row.get('ExpenseSubmittedTimestamp')})

        # Activity: Manager Approved
        if pd.notna(row.get('ManagerApprovedTimestamp')):
            events.append({**base_event, 'ActivityName': 'Manager Approved', 'EventTimestamp': row.get('ManagerApprovedTimestamp')})

        # Activity: Finance Review Pending
        if pd.notna(row.get('FinanceReviewPendingTimestamp')):
            events.append({**base_event, 'ActivityName': 'Finance Review Pending', 'EventTimestamp': row.get('FinanceReviewPendingTimestamp')})

        # Activity: Finance Approved
        if pd.notna(row.get('FinanceApprovedTimestamp')):
            events.append({**base_event, 'ActivityName': 'Finance Approved', 'EventTimestamp': row.get('FinanceApprovedTimestamp')})

        # Activity: Synced with Accounting System
        if pd.notna(row.get('SyncedTimestamp')):
            events.append({**base_event, 'ActivityName': 'Synced with Accounting System', 'EventTimestamp': row.get('SyncedTimestamp')})

        # Activity: Expense Sent Back for Revision
        if row.get('Status') == 'REJECTED' and pd.notna(row.get('Updated At')):
             events.append({**base_event, 'ActivityName': 'Expense Sent Back for Revision', 'EventTimestamp': row.get('Updated At')}) # Using last updated time for rejection

    # Process reimbursement-specific activities
    for _, row in reimbursements_df.iterrows():
        base_event = {
            'ExpenseReportId': row.get('ExpenseReportId'),
            'UserName': row.get('UserName'),
            'EmployeeDepartment': row.get('EmployeeDepartment'),
            'ReportTotalAmount': row.get('ReportTotalAmount'),
            'ExpenseCategory': row.get('ExpenseCategory'),
            'PolicyViolationFlag': False, # Typically less flagging on reimbursements
            'RevisionReason': row.get('RevisionReason')
        }

        # Activity: Reimbursement Scheduled
        if row.get('Status') == 'APPROVED' and pd.notna(row.get('ReimbursementScheduledTimestamp')):
            events.append({**base_event, 'ActivityName': 'Reimbursement Scheduled', 'EventTimestamp': row.get('ReimbursementScheduledTimestamp')})

        # Activity: Reimbursement Executed
        if row.get('Status') == 'PAID' and pd.notna(row.get('ReimbursementExecutedTimestamp')):
            events.append({**base_event, 'ActivityName': 'Reimbursement Executed', 'EventTimestamp': row.get('ReimbursementExecutedTimestamp')})

    if not events:
        print("Warning: No events were generated. Check the input files and column names.")
        return None

    # Create final DataFrame
    event_log_df = pd.DataFrame(events)
    event_log_df['EventTimestamp'] = pd.to_datetime(event_log_df['EventTimestamp'])
    event_log_df.sort_values(by=['ExpenseReportId', 'EventTimestamp'], inplace=True)
    
    return event_log_df

# --- Main execution block ---
if __name__ == "__main__":
    transactions_df, reimbursements_df = load_data()
    if transactions_df is not None and reimbursements_df is not None:
        event_log = transform_to_event_log(transactions_df, reimbursements_df)
        if event_log is not None:
            event_log.to_csv(OUTPUT_FILE, index=False, date_format='%Y-%m-%dT%H:%M:%S.%fZ')
            print(f"Successfully created event log with {len(event_log)} events. Saved to '{OUTPUT_FILE}'.")

Your Expense Management Data Template

Your Expense Management Data Template

Expense Management Attributes

Expense Management Activities

Extraction Guides

Ramp Built-in CSV Transaction and Reimbursement Export

Steps

Configuration

a Sample Query config

Python Script Extraction via the Ramp Developer API

Steps

Configuration

a Sample Query python

Ramp Data Warehouse Integration Sync (Snowflake/BigQuery)

Steps

Configuration

a Sample Query config

Ready to Get Started?

Unlock Efficiency: Optimize Ramp Expense Management Today

Your Expense Management Data Template

Your Expense Management Data Template

Expense Management Attributes

Expense Management Activities

Extraction Guides

Ramp Built-in CSV Transaction and Reimbursement Export

Steps

Configuration

a Sample Query config

Python Script Extraction via the Ramp Developer API

Steps

Configuration

a Sample Query python

Ramp Data Warehouse Integration Sync (Snowflake/BigQuery)

Steps

Configuration

a Sample Query config

We value your privacy

Ready to Get Started?

Unlock Efficiency: Optimize Ramp Expense Management Today