> Continuous Process Improvement > Lead to Cash > Pipedrive > Data Template

Your Lead to Cash Data Template

Pipedrive

Lead to Cash Attributes (21) Lead to Cash Activities (13) Extraction Guides (3)

Your Lead to Cash Data Template

This template provides a clear roadmap for collecting the crucial data needed to analyze your Lead to Cash process in Pipedrive. It outlines the essential attributes and key activities to track, along with practical guidance on how to extract this information directly from your Pipedrive system. Leverage this resource to prepare your data for comprehensive process mining and optimization.

Recommended attributes to collect
Key activities to track
Pipedrive extraction guidance

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

Download Template

Lead to Cash Attributes

These recommended data fields are essential for building a robust event log, enabling a comprehensive analysis of your Lead to Cash process.

3 Required 4 Recommended 14 Optional

	Name	Description
	Activity Name ActivityName	The name of the specific business event or task that occurred within the sales process.
Description This attribute describes a single step in the Lead to Cash process, such as 'Deal Created', 'Proposal Sent', or 'Payment Received'. Activities are the fundamental building blocks used to visualize the process flow. Analyzing the sequence and frequency of activities helps identify the most common process paths, bottlenecks where work queues up, and any deviations from the standard sales procedure. This is essential for understanding how the process is actually executed. Why it matters It defines the steps in the process, allowing for the visualization and analysis of the process flow, identification of bottlenecks, and conformance checking. Where to get This is typically derived by mapping Pipedrive events, such as deal stage changes or the creation of related objects like invoices, to a defined list of activities. Examples Deal CreatedProposal SentDeal WonInvoice SentPayment Received
	Event Time EventTime	The timestamp indicating when the activity occurred.
Description The Event Time, or Start Time, captures the precise date and time that a specific activity was recorded. This temporal data is essential for ordering events chronologically and calculating durations between different process steps. In analysis, this timestamp is used to calculate key performance indicators like cycle time, processing time, and wait time. It enables the creation of time-based dashboards that monitor performance and identify delays within the sales process. Why it matters This timestamp is critical for ordering events, calculating process durations, and analyzing performance against time-based targets. Where to get Retrieved from fields like 'add_time' or 'update_time' on Deal, Activity, or related objects in the Pipedrive API. Examples 2023-04-15T10:00:00Z2023-04-16T14:30:00Z2023-05-01T09:05:00Z
	Sales Opportunity SalesOpportunityId	The unique identifier for a sales opportunity, serving as the case ID for tracking the entire Lead to Cash process.
Description The Sales Opportunity ID is the primary key that connects all related activities, from initial lead creation to final payment. It represents a single potential sale being tracked in Pipedrive. In process analysis, this identifier is crucial for reconstructing the end-to-end journey of each deal. It allows for the grouping of all events, such as 'Deal Created', 'Proposal Sent', and 'Payment Received', into a coherent case, enabling the analysis of process variants, cycle times, and conversion rates. Why it matters This identifier is the foundation of the process view, linking all related events to form a complete case history for each sales deal. Where to get Retrieved from the 'id' field of the Deal object via the Pipedrive API. Examples 12543125441254512546
	Deal Owner DealOwner	The sales representative responsible for managing the sales opportunity.
Description The Deal Owner is the user assigned to a specific deal. This attribute is key to understanding resource allocation and performance within the sales team. This attribute enables performance analysis at the individual or team level. Dashboards can be filtered by Deal Owner to compare metrics like sales cycle time, conversion rates, and average deal value, helping to identify top performers and areas for coaching. Why it matters Enables performance analysis by sales representative, helping to identify top performers and areas needing improvement. Where to get Retrieved from the 'user_id' field on the Deal object, which links to the User object in the Pipedrive API. Examples John SmithJane DoePeter Jones
	Deal Status DealStatus	The current outcome or state of the deal, such as won, lost, or open.
Description Deal Status indicates the final outcome or current high-level state of a sales opportunity. This is a primary dimension for analyzing sales success rates and process efficiency. This attribute is fundamental for calculating win/loss rates and for filtering the process map to compare the journeys of won deals versus lost deals. This comparison often reveals key differences in process execution that contribute to success or failure, providing valuable insights for process improvement. Why it matters It is essential for calculating win/loss rates and comparing the process flows of successful versus unsuccessful deals. Where to get Retrieved from the 'status' field of the Deal object in the Pipedrive API. Examples wonlostopen
	Deal Value DealValue	The total monetary value of the sales opportunity.
Description Deal Value represents the potential or actual revenue associated with a sales opportunity. It is a fundamental metric for prioritizing deals and assessing the financial impact of the sales pipeline. In process analysis, this attribute is used to segment cases by financial size, enabling comparisons of process execution for high-value versus low-value deals. It is also a core component of dashboards that analyze sales performance, discounting effectiveness, and overall revenue generation. Why it matters It allows for financial analysis of the sales process, helping to prioritize high-value deals and understand revenue impact. Where to get Retrieved from the 'value' field of the Deal object in the Pipedrive API. Examples 5000.0025000.501500.75
	Discount Percentage DiscountPercentage	The discount percentage applied to the deal.
Description This attribute represents the percentage discount given on a sales deal, if any. It is a critical factor in understanding pricing strategy and its impact on profitability. Analyzing the Discount Percentage helps in evaluating the effectiveness of discounting strategies. It can be correlated with other attributes like Deal Value, Sales Representative, or Product to see where discounts are most common and whether they lead to higher win rates or simply erode margins. Why it matters This is crucial for analyzing pricing strategies, sales margins, and the overall profitability of deals. Where to get This is often a custom field on the Deal object in Pipedrive. Consult Pipedrive documentation for custom field setup. Examples 0.050.100.150.0
	Deal Currency DealCurrency	The currency associated with the deal value.
Description This attribute specifies the currency, for example, USD, EUR, or GBP, in which the Deal Value is denominated. It provides necessary context for all financial metrics. When analyzing deals from multiple regions or countries, this attribute is essential for correct financial reporting and aggregation. It ensures that currency conversions can be applied correctly before summarizing financial data, preventing inaccurate conclusions. Why it matters Provides essential context for the Deal Value, ensuring accurate financial analysis and reporting across different regions. Where to get Retrieved from the 'currency' field of the Deal object in the Pipedrive API. Examples USDEURGBP
	Deal Stage DealStage	The specific stage of the sales pipeline the deal is currently in.
Description The Deal Stage represents a specific step in a company's defined sales pipeline, such as 'Qualified', 'Proposal Made', or 'Negotiation'. It provides a more granular view of a deal's progress than the overall Deal Status. Analyzing deal stages is crucial for understanding the sales funnel and identifying where deals tend to stall. By tracking the time spent in each stage, teams can pinpoint bottlenecks and focus improvement efforts on the most problematic parts of the sales cycle. Why it matters Provides a granular view of a deal's progress through the sales funnel, helping to identify bottlenecks and conversion rates between stages. Where to get Retrieved from the 'stage_id' field on the Deal object, which links to the Stage object in the Pipedrive API. Examples Lead InContact MadeDemo ScheduledProposal MadeNegotiations Started
	Event End Time EventEndTime	The timestamp indicating when an activity with a duration was completed.
Description The Event End Time marks the completion of an activity that has a measurable duration, such as a meeting or a task. It complements the Start Time by allowing for precise calculation of how long specific activities took. This attribute is essential for calculating the processing time of individual activities. Analyzing this duration helps identify which specific tasks are most time-consuming and offers opportunities for optimization, automation, or resource reallocation. Why it matters Allows for the precise calculation of individual activity durations, which is key to identifying time-consuming tasks and bottlenecks. Where to get Retrieved from fields like 'due_date' or a completion timestamp on Activity objects in the Pipedrive API. Examples 2023-04-15T11:00:00Z2023-04-16T15:00:00Z2023-05-01T09:35:00Z
	Invoice Payment Time InvoicePaymentTime	The duration between when an invoice was sent and when the payment was received.
Description Invoice Payment Time measures the efficiency of the accounts receivable process. It specifically tracks the time elapsed from the 'Invoice Sent' activity to the 'Payment Received' activity. This metric is crucial for monitoring cash flow and identifying delays in payment collection. Analyzing this duration helps businesses understand customer payment behavior and the effectiveness of their dunning and collection strategies, directly impacting the Quote to Payment cycle. Why it matters Directly measures the efficiency of the cash collection process and is vital for managing cash flow. Where to get Calculated as the difference between the timestamps of the 'Payment Received' and 'Invoice Sent' activities for a given case. Examples 28 days45 days10 days
	Is On Time Payment IsOnTimePayment	A boolean flag indicating if a payment was received within the agreed payment terms.
Description This attribute is a binary flag (True/False) that indicates whether a payment was made on time. It is derived by comparing the actual Invoice Payment Time against the specified Payment Terms. This flag simplifies the analysis of payment behavior. It can be used to quickly calculate the On-Time Payment Rate KPI and to filter for late payments, allowing for a focused root cause analysis on why certain customers or types of deals result in payment delays. Why it matters Simplifies the analysis of payment adherence and is used to calculate the On-Time Payment Rate KPI. Where to get Calculated by comparing 'InvoicePaymentTime' with the 'PaymentTerms' attribute. For example, if PaymentTerms is 'Net 30' and InvoicePaymentTime is <= 30 days, the value is True. Examples truefalse
	Is Rework IsRework	A flag that identifies activities that are part of a rework loop.
Description Is Rework is a boolean flag that is typically calculated by the process mining tool to highlight instances where the process deviates from a linear flow and repeats previous steps. For example, a deal moving from 'Negotiation' back to 'Proposal Sent' would be considered rework. Identifying rework is critical for process optimization as it represents inefficiency, wasted effort, and potential confusion for the customer. Analyzing activities flagged as rework helps pinpoint areas where the process is breaking down, enabling targeted improvements. Why it matters Helps to quantify process inefficiency by identifying and flagging activities that are part of a repeated or circular process path. Where to get This is calculated by the process mining software by analyzing the sequence of activities within each case. Examples truefalse
	Last Data Update LastDataUpdate	The timestamp of the most recent data refresh from the source system.
Description This attribute indicates the last time the data was extracted and updated in the process mining dataset. It provides a clear reference point for the freshness of the data being analyzed. For any analysis, knowing the data's timeliness is critical. This attribute helps users understand if the insights they are viewing reflect the most current state of the process, which is essential for operational monitoring and decision-making. Why it matters Indicates the freshness of the data, ensuring users are aware of how current the process analysis is. Where to get This value is generated and recorded during the data ingestion or ETL process. Examples 2023-10-27T02:00:00Z2023-10-28T02:00:00Z
	Lead Source LeadSource	The source or channel through which the lead was acquired.
Description Lead Source identifies how a potential customer was first identified, for example, 'Webinar', 'Cold Call', or 'Partner Referral'. This information is vital for marketing and sales strategy. Analyzing the Lead Source helps determine the effectiveness of different marketing channels. It can be used to compare conversion rates and sales cycle times for leads from various sources, allowing the business to optimize its marketing spend and sales efforts. Why it matters Helps measure the effectiveness of different marketing channels by analyzing the conversion rates and process performance of leads from each source. Where to get Retrieved from the 'source_name' field on the Lead object, which is then associated with the Deal. Examples Organic SearchPaid SocialReferralTrade Show
	Organization Name OrganizationName	The name of the customer's company or organization associated with the deal.
Description This attribute identifies the corporate customer linked to the sales opportunity. It provides context about which companies are in the sales pipeline. Analyzing data by Organization Name helps in understanding customer behavior and identifying key accounts. It can be used to segment the process analysis to see if processes differ for strategic customers versus smaller clients, or to track the entire sales history with a specific company. Why it matters Identifies the customer, enabling customer-centric analysis and segmentation of the sales process. Where to get Retrieved from the 'org_id' field on the Deal object, which links to the Organization object in the Pipedrive API. Examples ABC CorporationGlobal Tech Inc.Innovate Solutions
	Payment Terms PaymentTerms	The agreed-upon terms for payment, such as 'Net 30' or 'Net 60'.
Description Payment Terms define the timeframe within which a customer is expected to pay an invoice after it has been sent. This attribute is critical for managing accounts receivable and cash flow. In process mining, this attribute serves as the baseline for evaluating payment performance. It is used to calculate KPIs like the On-Time Payment Rate by comparing the actual payment date against the due date derived from the invoice date and these terms. Why it matters Provides the baseline for measuring on-time payment performance and analyzing delays in the cash collection process. Where to get This is typically a custom field on the Deal or Organization object in Pipedrive, or it may come from an integrated accounting system. Examples Net 30Net 60Due on Receipt
	Product ProductAttached	The product or service associated with the sales opportunity.
Description This attribute specifies the product or service being sold in the deal. It allows for a more detailed analysis of sales activities related to specific offerings. By including the product, analysis can be segmented to compare sales processes across different product lines. This can reveal if certain products have longer sales cycles, different conversion rates, or require different process steps, leading to more tailored sales strategies. Why it matters Enables analysis of the sales process by product line, helping to identify variations in sales cycles or success rates for different offerings. Where to get Retrieved from the Product object that is linked to a Deal in the Pipedrive API. Examples Standard SaaS SubscriptionEnterprise Platform LicenseProfessional Services Package
	Region Region	The geographic region associated with the customer or sales opportunity.
Description This attribute categorizes deals into geographical areas, such as North America, EMEA, or APAC. It allows for location-based analysis of sales performance and processes. By segmenting the process by region, businesses can uncover regional differences in sales cycle times, win rates, and process variants. These insights can inform regional sales strategies, resource allocation, and process standardization efforts. Why it matters Enables geographical analysis of the sales process, highlighting regional differences in performance and customer behavior. Where to get This is typically a custom field on the Deal or Organization object. The address fields of the Organization can also be used to derive this. Examples North AmericaEMEAAPACLATAM
	Sales Cycle Time SalesCycleTime	The total duration from the creation of a deal to when it was closed (won or lost).
Description Sales Cycle Time is a key performance indicator that measures the total time elapsed from the first event, 'Deal Created', to the final outcome event, such as 'Deal Won' or 'Deal Lost'. This calculated metric provides a high-level overview of sales velocity. Analyzing its average and distribution helps identify trends and systemic delays in the overall process. It is a primary KPI for dashboards focused on sales efficiency and performance. Why it matters This is a critical KPI for measuring the overall efficiency and speed of the sales process from start to finish. Where to get Calculated by finding the difference between the timestamp of the first and last activity for each SalesOpportunityId. Examples 30 days 4 hours92 days 8 hours15 days 2 hours
	Source System SourceSystem	Identifies the system from which the data was extracted.
Description This attribute specifies the origin of the data, which in this case is Pipedrive. It is particularly useful in environments where data from multiple systems is being consolidated for a holistic process view. While often a static value in a single-system analysis, it provides essential context and aids in data governance and traceability. It confirms that the events and attributes are related to the Pipedrive instance. Why it matters Provides crucial context about the data's origin, ensuring traceability and clarity, especially when data from multiple systems is combined. Where to get This is typically a static value added during the data transformation process. Examples Pipedrive

Required Recommended Optional

Lead to Cash Activities

These key process steps and milestones should be captured in your event log to ensure accurate process discovery and effective optimization.

5 Recommended 8 Optional

	Activity	Description
	Deal Created	Marks the official start of a sales opportunity. This event is triggered when a new deal is manually created or automatically generated from a converted lead.
Why it matters This is the primary start activity for the sales cycle. Analyzing the time from this event to 'Deal Won' or 'Deal Lost' provides the overall sales cycle duration. Where to get This is an explicit event captured in the Pipedrive Deals entity. The creation timestamp ('add_time') for the deal record serves as the event time. Capture Use the 'add_time' field from the Deals entity. Event type explicit
	Deal Lost	Marks the unsuccessful closure of a deal. This occurs when the sales process is terminated because the customer chose a competitor, is no longer interested, or for other reasons.
Why it matters This is the primary failure end-point. Analyzing when and why deals are lost provides critical insights for improving sales strategy, training, and qualification processes. Where to get This is an explicit event captured when a deal's status is changed to 'lost'. The 'lost_time' field in the Deals entity records the timestamp. Capture Use the 'lost_time' timestamp from the Deals entity. Event type explicit
	Deal Won	Marks the successful closure of a deal, signifying verbal or written agreement from the customer. This is a standard, explicit status change within Pipedrive.
Why it matters This is the most important success milestone. It is crucial for calculating win rates, sales cycle length for successful deals, and overall sales performance. Where to get This is an explicit event captured when a deal's status is changed to 'won'. The 'won_time' field in the Deals entity provides the precise timestamp. Capture Use the 'won_time' timestamp from the Deals entity. Event type explicit
	Payment Received	Marks the successful receipt of payment from the customer, completing the cash cycle for the opportunity. This is the final event in the Lead to Cash process and originates from a financial system.
Why it matters This is the final, value-realizing step. It is the true end of the Lead to Cash process and is essential for measuring the end-to-end cycle time and analyzing payment delays. Where to get Captured from an accounting or payment processing system. The payment confirmation must be linked back to the Pipedrive deal. Capture Event data from an external financial system, linked to the deal via a unique identifier. Event type explicit
	Proposal Sent	Represents the point where a formal quote or proposal is delivered to the customer. This is often inferred by the deal moving into a specific pipeline stage, such as 'Proposal Made' or 'Quote Sent'.
Why it matters This is a key milestone in the sales process. Analyzing the time from this event to 'Deal Won' helps understand the length of the decision-making and negotiation phase. Where to get Inferred from the Deal changelog when the 'stage_id' changes to a stage designated for proposals. The timestamp of the stage change is used. Capture Detect when a Deal's 'stage_id' is updated to a specific 'Proposal' stage. Event type inferred
	Deal Qualified	Indicates that the deal has met predefined criteria and is considered a viable opportunity worth pursuing. This is typically inferred when a deal moves from an early pipeline stage, like 'Lead In', to a more qualified stage, like 'Contact Made' or 'Needs Defined'.
Why it matters This milestone helps analyze the effectiveness of the lead qualification process and pinpoints how long deals spend in initial, unqualified stages. Where to get Inferred from the Deal changelog or history when the 'stage_id' field changes to a stage that signifies qualification. Capture Identify stage change to a pre-defined 'qualified' stage from the deal's history. Event type inferred
	Deal Reopened	Represents a deal that was previously marked as lost but has been reactivated for further sales activities. This event is inferred when a deal's status changes from 'lost' back to 'open'.
Why it matters This activity highlights rework loops and re-engagement efforts. Analyzing these instances can reveal patterns in customer behavior or sales follow-up effectiveness. Where to get Inferred from the Deal changelog. It is detected when the 'status' field changes from 'lost' to 'open'. Capture Identify a status change from 'lost' to 'open' in the deal's history. Event type inferred
	Initial Contact Made	Represents the first meaningful interaction with the potential customer associated with the deal. This is typically logged as a completed activity, such as a call, email, or meeting, linked to the deal.
Why it matters Tracking this helps measure the time to first contact and ensures leads are engaged promptly. It provides insight into the responsiveness of the sales team. Where to get Captured from the Pipedrive Activities entity. It is identified by finding the earliest completed activity linked to the deal ID. Capture Find the first completed Activity associated with a Deal, based on the 'done_time' field. Event type explicit
	Invoice Sent	Represents the moment an invoice is sent to the customer for payment. This event is usually triggered in an external accounting or billing system after order fulfillment.
Why it matters This marks the start of the payment cycle. The time between this event and 'Payment Received' is critical for monitoring accounts receivable and cash flow. Where to get Originates from an external accounting or ERP system like Xero or QuickBooks. The event data needs to be integrated and associated with the Pipedrive deal. Capture Event data from an external billing system, associated with the deal via API integration. Event type explicit
	Negotiation Started	Signals the beginning of active negotiation on price, terms, or scope. This is typically inferred when a deal moves into a 'Negotiation' pipeline stage after a proposal has been sent.
Why it matters Isolating the negotiation phase helps identify how long this specific part of the cycle takes and which deals involve lengthy negotiations. Where to get Inferred from the Deal changelog when the 'stage_id' is updated to a stage explicitly named for negotiation. Capture Identify stage change to a pre-defined 'Negotiation' stage from the deal's history. Event type inferred
	Order Fulfilled	Indicates that the product has been shipped or the service has been delivered to the customer. This event originates from an external fulfillment or operational system.
Why it matters Tracking fulfillment is key to understanding the order-to-delivery cycle time. Delays here directly impact customer satisfaction and revenue recognition. Where to get Captured from an external ERP, inventory, or service management system. This data must be linked back to the corresponding Pipedrive deal. Capture Event data from an external system, linked via deal ID or another common identifier. Event type explicit
	Proposal Follow-Up	Captures any sales activity, such as a call or email, that occurs after a proposal has been sent to the customer. This event indicates active engagement during the consideration phase.
Why it matters Measures the timeliness and frequency of follow-up actions, which is critical for maintaining momentum and closing deals. It supports the 'Proposal Follow-up Timeliness' dashboard. Where to get Captured from the Pipedrive Activities entity. It's identified as a completed activity that occurs after the 'Proposal Sent' event for the same deal. Capture Identify a completed Activity with a 'done_time' that is later than the timestamp of the 'Proposal Sent' activity. Event type explicit
	Sales Order Created	Represents the creation of a formal sales order in a downstream system, such as an ERP or accounting platform, after a deal is won. This activity is not native to Pipedrive and relies on data from an integrated system.
Why it matters This event marks the handoff from Sales to Operations or Finance. Analyzing the time from 'Deal Won' to this activity reveals potential delays in order processing. Where to get Typically captured from a connected ERP or finance system. The data would need to be ingested and linked to the Pipedrive deal ID. Capture Event data pushed from an external system via API, often updating a custom field on the Pipedrive deal. Event type explicit

Recommended Optional

Extraction Guides

How to get your data from Pipedrive

Steps

Log in to Pipedrive: Access your Pipedrive account with a user profile that has permissions to export data. Typically, this is an Admin user.
Navigate to Data Export: Click on your profile picture in the top right corner, then select 'Settings'. In the left-hand navigation menu, find the 'Tools and apps' section and click on 'Export data'.
Export Deals Data: In the 'Export data' view, select 'Deals'. Choose the 'CSV' file format. It is recommended to apply a filter for a specific date range, for example, the last 6 months, to keep the file size manageable. Click 'Export' and wait for the file to be generated and downloaded. This file will contain key deal information including creation time, won time, and lost time.
Export Activities Data: Repeat the process for 'Activities'. Select 'Activities', choose 'CSV', apply the same date range filter as for deals, and export the file. This file contains records of calls, emails, meetings, and custom activities linked to deals.
Gather External Data: Obtain corresponding data from your downstream systems. You will need exports from your ERP, accounting, or fulfillment systems for events like 'Sales Order Created', 'Order Fulfilled', 'Invoice Sent', and 'Payment Received'. Ensure these files contain a common identifier that can be mapped back to the Pipedrive Deal ID.
Set up a Data Processing Environment: Use a tool capable of handling and merging multiple CSV files, such as Python with the pandas library, Microsoft Excel with Power Query, or another data preparation tool.
Load and Combine Data: Load the Deals.csv and Activities.csv files. Also, load the external data files gathered in the previous step.
Construct the Event Log: Create a unified event log by scripting the transformation of raw data into the required activity format. For each of the 13 required activities, create a corresponding set of rows. For example:
- 'Deal Created' events are generated from the add_time column in the Deals.csv file.
- 'Deal Won' events are generated from the won_time column in the Deals.csv file.
- 'Initial Contact Made' is the earliest completed activity for each deal from the Activities.csv file.
- Other activities like 'Proposal Sent' or 'Negotiation Started' are inferred from specific activity subjects or types in Activities.csv.
Map Attributes: For each event row, ensure all required columns are populated: SalesOpportunityId, ActivityName, and EventTime. Add recommended attributes like DealValue, DealOwner, and DealStatus by joining with the deals data.
Final Export: Once the event log is fully constructed and validated, save the final table as a single CSV file with UTF-8 encoding. This file is now ready to be uploaded to ProcessMind for analysis.

Configuration

Export Objects: You must export at least 'Deals' and 'Activities'. You may also need to export 'Organizations' or 'People' if you need additional dimensional data.
File Format: Choose CSV for maximum compatibility with data processing tools like Python or Power Query.
Date Range Filter: Use the 'Filter by' option to select a specific time period, for example, 'Deal add time' is within the 'last 180 days'. This is critical for managing export performance and file size.
User Permissions: You need to be an Admin user or have a permission set that explicitly allows exporting data from the Pipedrive account.
Custom Activity Types: The successful extraction of process steps like 'Deal Qualified' or 'Proposal Sent' relies heavily on your organization's use of custom Activity Types in Pipedrive. Ensure these are configured and used consistently by your sales team.

a Sample Query config

import pandas as pd

# --- 1. Load Data ---
# Load Pipedrive exports. Replace 'deals.csv' and 'activities.csv' with your actual file paths.
deals_df = pd.read_csv('deals.csv')
activities_df = pd.read_csv('activities.csv')

# Placeholder: Load data from external systems. These files should contain a Deal ID column.
# Replace with actual file loads from your ERP, Accounting, or Fulfillment systems.
sales_orders_df = pd.DataFrame({'Deal ID': [], 'Order Created Date': []}) # Example from ERP
fulfillments_df = pd.DataFrame({'Deal ID': [], 'Fulfillment Date': []})   # Example from Fulfillment System
invoices_df = pd.DataFrame({'Deal ID': [], 'Invoice Sent Date': []})         # Example from Accounting System
payments_df = pd.DataFrame({'Deal ID': [], 'Payment Received Date': []})     # Example from Accounting System

# --- 2. Prepare and Clean Data ---
# Rename columns for consistency and convert date columns to datetime objects
deals_df.rename(columns={'ID': 'SalesOpportunityId', 'Value': 'DealValue', 'Owner name': 'DealOwner', 'Status': 'DealStatus', 'Add time': 'EventTime_Created', 'Won time': 'EventTime_Won', 'Lost time': 'EventTime_Lost'}, inplace=True)
activities_df.rename(columns={'Deal ID': 'SalesOpportunityId', 'Subject': 'ActivityName', 'Done time': 'EventTime'}, inplace=True)

for col in ['EventTime_Created', 'EventTime_Won', 'EventTime_Lost']: 
    deals_df[col] = pd.to_datetime(deals_df[col], errors='coerce')
activities_df['EventTime'] = pd.to_datetime(activities_df['EventTime'], errors='coerce')


# --- 3. Extract Activities from Pipedrive Data ---

# Activity: Deal Created
event_created = deals_df[['SalesOpportunityId', 'EventTime_Created']].copy()
event_created.rename(columns={'EventTime_Created': 'EventTime'}, inplace=True)
event_created['ActivityName'] = 'Deal Created'

# Activity: Deal Won
event_won = deals_df[deals_df['EventTime_Won'].notna()][['SalesOpportunityId', 'EventTime_Won']].copy()
event_won.rename(columns={'EventTime_Won': 'EventTime'}, inplace=True)
event_won['ActivityName'] = 'Deal Won'

# Activity: Deal Lost
event_lost = deals_df[deals_df['EventTime_Lost'].notna()][['SalesOpportunityId', 'EventTime_Lost']].copy()
event_lost.rename(columns={'EventTime_Lost': 'EventTime'}, inplace=True)
event_lost['ActivityName'] = 'Deal Lost'

# Filter for relevant, completed activities linked to a deal
completed_activities = activities_df[activities_df['EventTime'].notna() & activities_df['SalesOpportunityId'].notna()].copy()
completed_activities['SalesOpportunityId'] = completed_activities['SalesOpportunityId'].astype(int)

# Activity: Initial Contact Made (first completed activity for each deal)
event_initial_contact = completed_activities.sort_values('EventTime', ascending=True).drop_duplicates('SalesOpportunityId')
event_initial_contact = event_initial_contact[['SalesOpportunityId', 'EventTime']].copy()
event_initial_contact['ActivityName'] = 'Initial Contact Made'

# Infer other activities based on activity subject keywords. Adjust keywords as needed.
event_qualified = completed_activities[completed_activities['ActivityName'].str.contains('qualify|qualification', case=False, na=False)].copy()
event_qualified['ActivityName'] = 'Deal Qualified'

event_proposal_sent = completed_activities[completed_activities['ActivityName'].str.contains('proposal|quote', case=False, na=False)].copy()
event_proposal_sent['ActivityName'] = 'Proposal Sent'

# Proposal Follow-Up: Any call or email activity after a proposal was sent
if not event_proposal_sent.empty:
    merged_followup = pd.merge(completed_activities, event_proposal_sent[['SalesOpportunityId', 'EventTime']].rename(columns={'EventTime':'ProposalTime'}), on='SalesOpportunityId')
    follow_ups = merged_followup[merged_followup['EventTime'] > merged_followup['ProposalTime']]
    event_follow_up = follow_ups[follow_ups['ActivityName'].str.contains('call|email|follow-up', case=False, na=False)].copy()
    event_follow_up['ActivityName'] = 'Proposal Follow-Up'
else:
    event_follow_up = pd.DataFrame(columns=['SalesOpportunityId', 'EventTime', 'ActivityName'])

event_negotiation = completed_activities[completed_activities['ActivityName'].str.contains('negotiation|negotiate', case=False, na=False)].copy()
event_negotiation['ActivityName'] = 'Negotiation Started'

# Placeholder for Deal Reopened: Logic is complex and not reliably available from standard export.
event_reopened = pd.DataFrame(columns=['SalesOpportunityId', 'EventTime', 'ActivityName'])

# --- 4. Extract Activities from External Data (Placeholders) ---

# Rename columns and convert dates for external data
# This section needs to be adapted to your specific file structures
event_so_created = sales_orders_df.rename(columns={'Deal ID': 'SalesOpportunityId', 'Order Created Date': 'EventTime'}).assign(ActivityName='Sales Order Created')
event_fulfilled = fulfillments_df.rename(columns={'Deal ID': 'SalesOpportunityId', 'Fulfillment Date': 'EventTime'}).assign(ActivityName='Order Fulfilled')
event_invoice_sent = invoices_df.rename(columns={'Deal ID': 'SalesOpportunityId', 'Invoice Sent Date': 'EventTime'}).assign(ActivityName='Invoice Sent')
event_payment_received = payments_df.rename(columns={'Deal ID': 'SalesOpportunityId', 'Payment Received Date': 'EventTime'}).assign(ActivityName='Payment Received')


# --- 5. Combine and Finalize Event Log ---

event_log_frames = [
    event_created,
    event_won,
    event_lost,
    event_initial_contact,
    event_qualified[['SalesOpportunityId', 'EventTime', 'ActivityName']],
    event_proposal_sent[['SalesOpportunityId', 'EventTime', 'ActivityName']],
    event_follow_up[['SalesOpportunityId', 'EventTime', 'ActivityName']],
    event_negotiation[['SalesOpportunityId', 'EventTime', 'ActivityName']],
    event_reopened,
    event_so_created,
    event_fulfilled,
    event_invoice_sent,
    event_payment_received
]

event_log = pd.concat(event_log_frames)

# Merge with deal attributes
final_event_log = pd.merge(event_log, deals_df[['SalesOpportunityId', 'DealValue', 'DealOwner', 'Status']], on='SalesOpportunityId', how='left')

# Clean up and select final columns
final_event_log.rename(columns={'Status':'DealStatus'}, inplace=True)
final_event_log.dropna(subset=['EventTime', 'SalesOpportunityId'], inplace=True)

# Placeholder for DiscountPercentage - Assuming it's a custom field in your deals export
if 'Discount percentage' in deals_df.columns:
    final_event_log = pd.merge(final_event_log, deals_df[['SalesOpportunityId', 'Discount percentage']], on='SalesOpportunityId', how='left')
    final_event_log.rename(columns={'Discount percentage':'DiscountPercentage'}, inplace=True)
else:
    final_event_log['DiscountPercentage'] = None

# Reorder columns to match requirements
output_columns = ['SalesOpportunityId', 'ActivityName', 'EventTime', 'DealValue', 'DealOwner', 'DealStatus', 'DiscountPercentage']
final_event_log = final_event_log[output_columns]

# --- 6. Save Final Event Log ---
final_event_log.to_csv('pipedrive_event_log.csv', index=False, date_format='%Y-%m-%d %H:%M:%S')

print('Event log created successfully.')

Steps

Set Up the Python Environment: Ensure you have Python 3.6 or newer installed on your system. Open a terminal or command prompt and install the necessary Python libraries by running the following command: pip install requests pandas.
Obtain Pipedrive API Credentials: Log in to your Pipedrive account. Navigate to Settings by clicking your profile picture, then select 'Personal preferences' and go to the 'API' tab. Copy your personal API token. You will also need your company domain, which is the prefix in your Pipedrive URL, for example, your-company in your-company.pipedrive.com.
Configure the Extraction Script: Open the provided Python script in a text editor or IDE. Locate the configuration section at the top and replace the placeholder values for API_TOKEN and COMPANY_DOMAIN with your credentials.
Define the Extraction Period: In the script's configuration section, set the START_DATE variable to the earliest creation date for the deals you want to include in the analysis. The format should be 'YYYY-MM-DD'.
Map Your Pipeline and Stage IDs: The script infers several activities based on deals moving between stages. You must provide the specific IDs for your sales pipeline and key stages. You can find these IDs by navigating to the stage in the Pipedrive UI and looking at the URL, or by using the API. Update the PIPELINE_ID, QUALIFIED_STAGE_ID, PROPOSAL_STAGE_ID, and NEGOTIATION_STAGE_ID variables in the script.
Execute the Script: Run the script from your terminal using the command: python your_script_name.py. The script will begin making calls to the Pipedrive API to fetch deals, activities, and change logs.
Monitor the Process: The script will print progress updates to the console, indicating which data is being fetched. The process may take several minutes, depending on the volume of data and API rate limits.
Implement External Data Extraction: For activities like 'Sales Order Created' and 'Payment Received', the script contains placeholder functions. You must modify these functions to connect to your external systems, such as an ERP or accounting platform, and retrieve the relevant event data. Map the Pipedrive SalesOpportunityId to the records in these other systems.
Review the Output: Once the script completes, it will generate a CSV file named pipedrive_event_log.csv. This file contains the structured event log with all extracted activities.
Prepare for Upload: Open the CSV file to ensure it has been created correctly. The columns should include SalesOpportunityId, ActivityName, EventTime, and other recommended attributes. This file is now ready to be uploaded to ProcessMind for analysis.

Configuration

API Credentials: The script requires a valid API_TOKEN and your COMPANY_DOMAIN for authenticating with the Pipedrive API. Ensure the user associated with the token has sufficient permissions to read deals, activities, and users.
Date Range: The START_DATE variable filters deals created on or after the specified date. For an initial analysis, a period of 3 to 6 months is recommended to ensure a manageable data volume.
Pipeline and Stage IDs: To accurately infer process steps like 'Deal Qualified' or 'Proposal Sent', you must configure the script with the correct numeric IDs for your primary sales PIPELINE_ID and the specific STAGE_ID values for each key milestone. Mismatched IDs will result in missing activities.
Performance and Rate Limiting: The script includes a short delay between API calls to respect Pipedrive's rate limits. For organizations with a very high volume of deals, this delay may need to be adjusted, or a more sophisticated backoff strategy implemented to avoid API errors.
Custom Field Mapping: If you use custom fields for important attributes like DiscountPercentage, you will need to find the API key for that custom field (a long hash string, not its display name) and reference it in the data transformation logic.
External System Integration: The extraction of events originating from outside Pipedrive, such as invoicing or fulfillment, is handled by placeholder functions. You must customize these sections to connect to your specific external data sources.

a Sample Query python

import requests
import pandas as pd
import time
import datetime

class=class="hl-string">"hl-comment"># --- CONFIGURATION ---
class=class="hl-string">"hl-comment"># Replace with your Pipedrive API token and company domain
API_TOKEN = class="hl-string">'YOUR_API_TOKEN'
COMPANY_DOMAIN = class="hl-string">'YOUR_COMPANY_DOMAIN' class=class="hl-string">"hl-comment"># e.g., class="hl-string">'your-company-name'

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

class=class="hl-string">"hl-comment"># Configure your specific Pipeline and Stage IDs
class=class="hl-string">"hl-comment"># Find these in your Pipedrive URL or via the API /stages endpoint
PIPELINE_ID = 1 class=class="hl-string">"hl-comment"># Replace with your main sales pipeline ID
QUALIFIED_STAGE_ID = 2 class=class="hl-string">"hl-comment"># Replace with your class="hl-string">'Qualified' stage ID
PROPOSAL_STAGE_ID = 3 class=class="hl-string">"hl-comment"># Replace with your class="hl-string">'Proposal Sent' stage ID
NEGOTIATION_STAGE_ID = 4 class=class="hl-string">"hl-comment"># Replace with your class="hl-string">'Negotiation' stage ID

class=class="hl-string">"hl-comment"># --- SCRIPT --- 

BASE_URL = fclass="hl-string">'https://{COMPANY_DOMAIN}.pipedrive.com/api/v1'

def get_paginated_data(endpoint, params={}):
    class="hl-string">"""Fetches all pages of data from a Pipedrive API endpoint."""
    all_data = []
    start = 0
    limit = 500
    while True:
        request_params = {
            class="hl-string">'start': start,
            class="hl-string">'limit': limit,
            class="hl-string">'api_token': API_TOKEN
        }
        request_params.update(params)
        
        try:
            response = requests.get(fclass="hl-string">"{BASE_URL}/{endpoint}", params=request_params)
            response.raise_for_status()
            data = response.json()

            if not data.get(class="hl-string">'data'):
                break

            all_data.extend(data[class="hl-string">'data'])

            if not data.get(class="hl-string">'additional_data', {}).get(class="hl-string">'pagination', {}).get(class="hl-string">'more_items_in_collection'):
                break
            
            start += limit
            time.sleep(0.2) class=class="hl-string">"hl-comment"># Respect API rate limits
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Error fetching {endpoint}: {e}")
            break
            
    return all_data

def get_deal_flow(deal_id):
    class="hl-string">"""Fetches the change history (flow) for a single deal."""
    params = {class="hl-string">'api_token': API_TOKEN}
    try:
        response = requests.get(fclass="hl-string">"{BASE_URL}/deals/{deal_id}/flow", params=params)
        response.raise_for_status()
        data = response.json()
        return data.get(class="hl-string">'data', [])
    except requests.exceptions.RequestException as e:
        print(fclass="hl-string">"Error fetching flow for deal {deal_id}: {e}")
        return []

class=class="hl-string">"hl-comment"># Placeholder functions for external data
def get_sales_order_data(deal_ids):
    print(class="hl-string">"Placeholder: Fetching sales order data from external ERP/CRM...")
    class=class="hl-string">"hl-comment"># Example: Connect to your ERP, query for orders linked to deal_ids
    class=class="hl-string">"hl-comment"># Return a list of dicts: [{class="hl-string">'SalesOpportunityId': id, class="hl-string">'ActivityName': class="hl-string">'Sales Order Created', class="hl-string">'EventTime': timestamp}, ...]
    return []

def get_fulfillment_data(deal_ids):
    print(class="hl-string">"Placeholder: Fetching order fulfillment data...")
    return []

def get_invoice_data(deal_ids):
    print(class="hl-string">"Placeholder: Fetching invoice data from accounting system...")
    return []

def get_payment_data(deal_ids):
    print(class="hl-string">"Placeholder: Fetching payment data from financial system...")
    return []


class=class="hl-string">"hl-comment"># --- MAIN EXTRACTION LOGIC ---
if __name__ == class="hl-string">'__main__':
    print(class="hl-string">"Starting Pipedrive data extraction...")
    events = []

    class=class="hl-string">"hl-comment"># 1. Fetch all deals created after the START_DATE and in the specified pipeline
    print(class="hl-string">"Fetching deals...")
    deals = get_paginated_data(class="hl-string">'deals', {class="hl-string">'start_date': START_DATE, class="hl-string">'pipeline_id': PIPELINE_ID})
    if not deals:
        print(class="hl-string">"No deals found for the specified criteria. Exiting.")
        exit()

    deal_ids = [d[class="hl-string">'id'] for d in deals]
    print(fclass="hl-string">"Found {len(deals)} deals.")

    class=class="hl-string">"hl-comment"># 2. Fetch all activities related to the deals
    print(class="hl-string">"Fetching activities...")
    all_activities = get_paginated_data(class="hl-string">'activities', {class="hl-string">'user_id': 0}) class=class="hl-string">"hl-comment"># Get for all users
    deal_activities = [act for act in all_activities if act.get(class="hl-string">'deal_id') in deal_ids and act[class="hl-string">'done']]

    activities_by_deal = {}
    for act in deal_activities:
        if act[class="hl-string">'deal_id'] not in activities_by_deal:
            activities_by_deal[act[class="hl-string">'deal_id']] = []
        activities_by_deal[act[class="hl-string">'deal_id']].append(act)

    class=class="hl-string">"hl-comment"># 3. Process each deal to create events
    print(class="hl-string">"Processing deals and activities to generate event log...")
    for deal in deals:
        deal_id = deal[class="hl-string">'id']
        deal_owner_name = deal.get(class="hl-string">'owner_name', class="hl-string">'')
        base_attributes = {
            class="hl-string">'SalesOpportunityId': deal_id,
            class="hl-string">'DealValue': deal.get(class="hl-string">'value', 0),
            class="hl-string">'DealOwner': deal_owner_name,
            class="hl-string">'DealStatus': deal.get(class="hl-string">'status', class="hl-string">'open'),
            class="hl-string">'DiscountPercentage': deal.get(class="hl-string">'discount_percentage', 0)
        }

        class=class="hl-string">"hl-comment"># Activity: Deal Created
        events.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Deal Created', class="hl-string">'EventTime': deal[class="hl-string">'add_time']})

        class=class="hl-string">"hl-comment"># Activity: Deal Won / Lost
        if deal[class="hl-string">'status'] == class="hl-string">'won' and deal[class="hl-string">'won_time']:
            events.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Deal Won', class="hl-string">'EventTime': deal[class="hl-string">'won_time']})
        elif deal[class="hl-string">'status'] == class="hl-string">'lost' and deal[class="hl-string">'lost_time']:
            events.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Deal Lost', class="hl-string">'EventTime': deal[class="hl-string">'lost_time']})

        class=class="hl-string">"hl-comment"># Activity: Initial Contact Made & Proposal Follow-Up
        proposal_sent_time = None
        if deal_id in activities_by_deal:
            sorted_activities = sorted(activities_by_deal[deal_id], key=lambda x: x[class="hl-string">'marked_as_done_time'])
            if sorted_activities:
                first_activity = sorted_activities[0]
                events.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Initial Contact Made', class="hl-string">'EventTime': first_activity[class="hl-string">'marked_as_done_time']})
            
            class=class="hl-string">"hl-comment"># Find proposal follow-ups (requires proposal time)
            class=class="hl-string">"hl-comment"># This part is revisited after stage changes are processed

        class=class="hl-string">"hl-comment"># Activities from Stage Changes: Qualified, Proposal Sent, Negotiation, Reopened
        time.sleep(0.2) class=class="hl-string">"hl-comment"># Rate limit for flow endpoint
        flow = get_deal_flow(deal_id)
        if not flow:
            continue

        lost_then_reopened = False
        was_lost = False

        for change in sorted(flow, key=lambda x: x[class="hl-string">'add_time']):
            class=class="hl-string">"hl-comment"># Deal Reopened
            if change.get(class="hl-string">'field_key') == class="hl-string">'status' and change.get(class="hl-string">'old_value') == class="hl-string">'lost' and change.get(class="hl-string">'new_value') == class="hl-string">'open':
                events.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Deal Reopened', class="hl-string">'EventTime': change[class="hl-string">'add_time']})
            
            if change.get(class="hl-string">'field_key') != class="hl-string">'stage_id':
                continue

            stage_change_time = change[class="hl-string">'add_time']

            class=class="hl-string">"hl-comment"># Deal Qualified
            if change[class="hl-string">'new_value'] == QUALIFIED_STAGE_ID:
                events.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Deal Qualified', class="hl-string">'EventTime': stage_change_time})

            class=class="hl-string">"hl-comment"># Proposal Sent
            if change[class="hl-string">'new_value'] == PROPOSAL_STAGE_ID:
                events.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Proposal Sent', class="hl-string">'EventTime': stage_change_time})
                proposal_sent_time = stage_change_time

            class=class="hl-string">"hl-comment"># Negotiation Started
            if change[class="hl-string">'new_value'] == NEGOTIATION_STAGE_ID:
                events.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Negotiation Started', class="hl-string">'EventTime': stage_change_time})

        class=class="hl-string">"hl-comment"># Re-check for Proposal Follow-Up now that we have proposal_sent_time
        if proposal_sent_time and deal_id in activities_by_deal:
            for act in activities_by_deal[deal_id]:
                if act[class="hl-string">'marked_as_done_time'] > proposal_sent_time:
                    events.append({**base_attributes, class="hl-string">'ActivityName': class="hl-string">'Proposal Follow-Up', class="hl-string">'EventTime': act[class="hl-string">'marked_as_done_time']})
                    break class=class="hl-string">"hl-comment"># Only capture the first follow-up

    class=class="hl-string">"hl-comment"># 4. Add events from external systems (placeholders)
    print(class="hl-string">"Adding data from external system placeholders...")
    events.extend(get_sales_order_data(deal_ids))
    events.extend(get_fulfillment_data(deal_ids))
    events.extend(get_invoice_data(deal_ids))
    events.extend(get_payment_data(deal_ids))

    class=class="hl-string">"hl-comment"># 5. Create and export the final event log
    if not events:
        print(class="hl-string">"No events were generated. The output file will be empty.")
    else:
        print(class="hl-string">"Creating CSV file...")
        df = pd.DataFrame(events)
        class=class="hl-string">"hl-comment"># Ensure required columns are present
        df = df[[class="hl-string">'SalesOpportunityId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'DealValue', class="hl-string">'DealOwner', class="hl-string">'DealStatus', class="hl-string">'DiscountPercentage']]
        df.sort_values(by=[class="hl-string">'SalesOpportunityId', class="hl-string">'EventTime'], inplace=True)
        
        output_filename = class="hl-string">'pipedrive_event_log.csv'
        df.to_csv(output_filename, index=False)
        print(fclass="hl-string">"Extraction complete. Event log saved to {output_filename}")

Steps

Log in to Pipedrive: Access your Pipedrive account with a user profile that has permissions to export data. Typically, this is an Admin user.
Navigate to Data Export: Click on your profile picture in the top right corner, then select 'Settings'. In the left-hand navigation menu, find the 'Tools and apps' section and click on 'Export data'.
Export Deals Data: In the 'Export data' view, select 'Deals'. Choose the 'CSV' file format. It is recommended to apply a filter for a specific date range, for example, the last 6 months, to keep the file size manageable. Click 'Export' and wait for the file to be generated and downloaded. This file will contain key deal information including creation time, won time, and lost time.
Export Activities Data: Repeat the process for 'Activities'. Select 'Activities', choose 'CSV', apply the same date range filter as for deals, and export the file. This file contains records of calls, emails, meetings, and custom activities linked to deals.
Gather External Data: Obtain corresponding data from your downstream systems. You will need exports from your ERP, accounting, or fulfillment systems for events like 'Sales Order Created', 'Order Fulfilled', 'Invoice Sent', and 'Payment Received'. Ensure these files contain a common identifier that can be mapped back to the Pipedrive Deal ID.
Set up a Data Processing Environment: Use a tool capable of handling and merging multiple CSV files, such as Python with the pandas library, Microsoft Excel with Power Query, or another data preparation tool.
Load and Combine Data: Load the Deals.csv and Activities.csv files. Also, load the external data files gathered in the previous step.
Construct the Event Log: Create a unified event log by scripting the transformation of raw data into the required activity format. For each of the 13 required activities, create a corresponding set of rows. For example:
- 'Deal Created' events are generated from the add_time column in the Deals.csv file.
- 'Deal Won' events are generated from the won_time column in the Deals.csv file.
- 'Initial Contact Made' is the earliest completed activity for each deal from the Activities.csv file.
- Other activities like 'Proposal Sent' or 'Negotiation Started' are inferred from specific activity subjects or types in Activities.csv.
Map Attributes: For each event row, ensure all required columns are populated: SalesOpportunityId, ActivityName, and EventTime. Add recommended attributes like DealValue, DealOwner, and DealStatus by joining with the deals data.
Final Export: Once the event log is fully constructed and validated, save the final table as a single CSV file with UTF-8 encoding. This file is now ready to be uploaded to ProcessMind for analysis.

Configuration

Export Objects: You must export at least 'Deals' and 'Activities'. You may also need to export 'Organizations' or 'People' if you need additional dimensional data.
File Format: Choose CSV for maximum compatibility with data processing tools like Python or Power Query.
Date Range Filter: Use the 'Filter by' option to select a specific time period, for example, 'Deal add time' is within the 'last 180 days'. This is critical for managing export performance and file size.
User Permissions: You need to be an Admin user or have a permission set that explicitly allows exporting data from the Pipedrive account.
Custom Activity Types: The successful extraction of process steps like 'Deal Qualified' or 'Proposal Sent' relies heavily on your organization's use of custom Activity Types in Pipedrive. Ensure these are configured and used consistently by your sales team.

a Sample Query config

import pandas as pd

# --- 1. Load Data ---
# Load Pipedrive exports. Replace 'deals.csv' and 'activities.csv' with your actual file paths.
deals_df = pd.read_csv('deals.csv')
activities_df = pd.read_csv('activities.csv')

# Placeholder: Load data from external systems. These files should contain a Deal ID column.
# Replace with actual file loads from your ERP, Accounting, or Fulfillment systems.
sales_orders_df = pd.DataFrame({'Deal ID': [], 'Order Created Date': []}) # Example from ERP
fulfillments_df = pd.DataFrame({'Deal ID': [], 'Fulfillment Date': []})   # Example from Fulfillment System
invoices_df = pd.DataFrame({'Deal ID': [], 'Invoice Sent Date': []})         # Example from Accounting System
payments_df = pd.DataFrame({'Deal ID': [], 'Payment Received Date': []})     # Example from Accounting System

# --- 2. Prepare and Clean Data ---
# Rename columns for consistency and convert date columns to datetime objects
deals_df.rename(columns={'ID': 'SalesOpportunityId', 'Value': 'DealValue', 'Owner name': 'DealOwner', 'Status': 'DealStatus', 'Add time': 'EventTime_Created', 'Won time': 'EventTime_Won', 'Lost time': 'EventTime_Lost'}, inplace=True)
activities_df.rename(columns={'Deal ID': 'SalesOpportunityId', 'Subject': 'ActivityName', 'Done time': 'EventTime'}, inplace=True)

for col in ['EventTime_Created', 'EventTime_Won', 'EventTime_Lost']: 
    deals_df[col] = pd.to_datetime(deals_df[col], errors='coerce')
activities_df['EventTime'] = pd.to_datetime(activities_df['EventTime'], errors='coerce')


# --- 3. Extract Activities from Pipedrive Data ---

# Activity: Deal Created
event_created = deals_df[['SalesOpportunityId', 'EventTime_Created']].copy()
event_created.rename(columns={'EventTime_Created': 'EventTime'}, inplace=True)
event_created['ActivityName'] = 'Deal Created'

# Activity: Deal Won
event_won = deals_df[deals_df['EventTime_Won'].notna()][['SalesOpportunityId', 'EventTime_Won']].copy()
event_won.rename(columns={'EventTime_Won': 'EventTime'}, inplace=True)
event_won['ActivityName'] = 'Deal Won'

# Activity: Deal Lost
event_lost = deals_df[deals_df['EventTime_Lost'].notna()][['SalesOpportunityId', 'EventTime_Lost']].copy()
event_lost.rename(columns={'EventTime_Lost': 'EventTime'}, inplace=True)
event_lost['ActivityName'] = 'Deal Lost'

# Filter for relevant, completed activities linked to a deal
completed_activities = activities_df[activities_df['EventTime'].notna() & activities_df['SalesOpportunityId'].notna()].copy()
completed_activities['SalesOpportunityId'] = completed_activities['SalesOpportunityId'].astype(int)

# Activity: Initial Contact Made (first completed activity for each deal)
event_initial_contact = completed_activities.sort_values('EventTime', ascending=True).drop_duplicates('SalesOpportunityId')
event_initial_contact = event_initial_contact[['SalesOpportunityId', 'EventTime']].copy()
event_initial_contact['ActivityName'] = 'Initial Contact Made'

# Infer other activities based on activity subject keywords. Adjust keywords as needed.
event_qualified = completed_activities[completed_activities['ActivityName'].str.contains('qualify|qualification', case=False, na=False)].copy()
event_qualified['ActivityName'] = 'Deal Qualified'

event_proposal_sent = completed_activities[completed_activities['ActivityName'].str.contains('proposal|quote', case=False, na=False)].copy()
event_proposal_sent['ActivityName'] = 'Proposal Sent'

# Proposal Follow-Up: Any call or email activity after a proposal was sent
if not event_proposal_sent.empty:
    merged_followup = pd.merge(completed_activities, event_proposal_sent[['SalesOpportunityId', 'EventTime']].rename(columns={'EventTime':'ProposalTime'}), on='SalesOpportunityId')
    follow_ups = merged_followup[merged_followup['EventTime'] > merged_followup['ProposalTime']]
    event_follow_up = follow_ups[follow_ups['ActivityName'].str.contains('call|email|follow-up', case=False, na=False)].copy()
    event_follow_up['ActivityName'] = 'Proposal Follow-Up'
else:
    event_follow_up = pd.DataFrame(columns=['SalesOpportunityId', 'EventTime', 'ActivityName'])

event_negotiation = completed_activities[completed_activities['ActivityName'].str.contains('negotiation|negotiate', case=False, na=False)].copy()
event_negotiation['ActivityName'] = 'Negotiation Started'

# Placeholder for Deal Reopened: Logic is complex and not reliably available from standard export.
event_reopened = pd.DataFrame(columns=['SalesOpportunityId', 'EventTime', 'ActivityName'])

# --- 4. Extract Activities from External Data (Placeholders) ---

# Rename columns and convert dates for external data
# This section needs to be adapted to your specific file structures
event_so_created = sales_orders_df.rename(columns={'Deal ID': 'SalesOpportunityId', 'Order Created Date': 'EventTime'}).assign(ActivityName='Sales Order Created')
event_fulfilled = fulfillments_df.rename(columns={'Deal ID': 'SalesOpportunityId', 'Fulfillment Date': 'EventTime'}).assign(ActivityName='Order Fulfilled')
event_invoice_sent = invoices_df.rename(columns={'Deal ID': 'SalesOpportunityId', 'Invoice Sent Date': 'EventTime'}).assign(ActivityName='Invoice Sent')
event_payment_received = payments_df.rename(columns={'Deal ID': 'SalesOpportunityId', 'Payment Received Date': 'EventTime'}).assign(ActivityName='Payment Received')


# --- 5. Combine and Finalize Event Log ---

event_log_frames = [
    event_created,
    event_won,
    event_lost,
    event_initial_contact,
    event_qualified[['SalesOpportunityId', 'EventTime', 'ActivityName']],
    event_proposal_sent[['SalesOpportunityId', 'EventTime', 'ActivityName']],
    event_follow_up[['SalesOpportunityId', 'EventTime', 'ActivityName']],
    event_negotiation[['SalesOpportunityId', 'EventTime', 'ActivityName']],
    event_reopened,
    event_so_created,
    event_fulfilled,
    event_invoice_sent,
    event_payment_received
]

event_log = pd.concat(event_log_frames)

# Merge with deal attributes
final_event_log = pd.merge(event_log, deals_df[['SalesOpportunityId', 'DealValue', 'DealOwner', 'Status']], on='SalesOpportunityId', how='left')

# Clean up and select final columns
final_event_log.rename(columns={'Status':'DealStatus'}, inplace=True)
final_event_log.dropna(subset=['EventTime', 'SalesOpportunityId'], inplace=True)

# Placeholder for DiscountPercentage - Assuming it's a custom field in your deals export
if 'Discount percentage' in deals_df.columns:
    final_event_log = pd.merge(final_event_log, deals_df[['SalesOpportunityId', 'Discount percentage']], on='SalesOpportunityId', how='left')
    final_event_log.rename(columns={'Discount percentage':'DiscountPercentage'}, inplace=True)
else:
    final_event_log['DiscountPercentage'] = None

# Reorder columns to match requirements
output_columns = ['SalesOpportunityId', 'ActivityName', 'EventTime', 'DealValue', 'DealOwner', 'DealStatus', 'DiscountPercentage']
final_event_log = final_event_log[output_columns]

# --- 6. Save Final Event Log ---
final_event_log.to_csv('pipedrive_event_log.csv', index=False, date_format='%Y-%m-%d %H:%M:%S')

print('Event log created successfully.')

Your Lead to Cash Data Template

Your Lead to Cash Data Template

Lead to Cash Attributes

Lead to Cash Activities

Extraction Guides

Pipedrive Native Data Export to CSV/Excel

Steps

Configuration

a Sample Query config

Python Script Extraction via Pipedrive REST API

Steps

Configuration

a Sample Query python

Third-Party ETL Tool with Pipedrive Connector

Steps

Configuration

a Sample Query config

Ready to Get Started?

Boost Win Rates: Optimize Pipedrive Lead to Cash Today

Your Lead to Cash Data Template

Your Lead to Cash Data Template

Lead to Cash Attributes

Lead to Cash Activities

Extraction Guides

Pipedrive Native Data Export to CSV/Excel

Steps

Configuration

a Sample Query config

Python Script Extraction via Pipedrive REST API

Steps

Configuration

a Sample Query python

Third-Party ETL Tool with Pipedrive Connector

Steps

Configuration

a Sample Query config

We value your privacy

Ready to Get Started?

Boost Win Rates: Optimize Pipedrive Lead to Cash Today