Seu template de dados: do Lead ao Recebimento

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.')

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}")

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.')