您的“从线索到现金” (Lead to Cash) 数据模板

import requests
import pandas as pd
import datetime
import time
import os

class=class="hl-string">"hl-comment"># --- CONFIGURATION SECTION --- #
class=class="hl-string">"hl-comment"># 1. HubSpot Private App Access Token
ACCESS_TOKEN = class="hl-string">"YOUR_HUBSPOT_ACCESS_TOKEN" class=class="hl-string">"hl-comment"># Replace with your token

class=class="hl-string">"hl-comment"># 2. Date range for the extraction
START_DATE = class="hl-string">"2023-01-01" class=class="hl-string">"hl-comment"># Format: YYYY-MM-DD
END_DATE = class="hl-string">"2023-12-31" class=class="hl-string">"hl-comment"># Format: YYYY-MM-DD

class=class="hl-string">"hl-comment"># 3. Critical: Map your Deal Stage IDs to Process Mining Activities
class=class="hl-string">"hl-comment"># You must find your internal HubSpot stage IDs and map them here.
class=class="hl-string">"hl-comment"># Example: class="hl-string">'123456': class="hl-string">'Opportunity Qualified'
PIPELINE_STAGE_MAPPING = {
    class="hl-string">'[Your Qualified Stage ID]': class="hl-string">'Opportunity Qualified',
    class="hl-string">'[Your Discovery Stage ID]': class="hl-string">'Requirements Gathered',
    class="hl-string">'[Your Presentation Stage ID]': class="hl-string">'Solution Proposed',
    class="hl-string">'[Your Contract Sent Stage ID]': class="hl-string">'Contract Signed',
    class="hl-string">'[Your Order Processing Stage ID]': class="hl-string">'Sales Order Created',
    class="hl-string">'[Your Order Fulfilled Stage ID]': class="hl-string">'Order Fulfilled',
    class="hl-string">'[Your Invoice Sent Stage ID]': class="hl-string">'Invoice Generated',
    class="hl-string">'[Your Payment Received Stage ID]': class="hl-string">'Payment Received',
    class=class="hl-string">"hl-comment"># Standard closed won/lost stages are handled separately by their internal names
    class="hl-string">'closedwon': class="hl-string">'Opportunity Closed Won',
    class="hl-string">'closedlost': class="hl-string">'Opportunity Closed Lost'
}
class=class="hl-string">"hl-comment"># --- END CONFIGURATION SECTION --- #

API_BASE_URL = class="hl-string">"https://api.hubapi.com"
AUTH_HEADER = {class="hl-string">'Authorization': fclass="hl-string">'Bearer {ACCESS_TOKEN}'}

def fetch_paginated_data(url, params=None, body=None):
    class="hl-string">"""Helper function to handle paginated API responses."""
    results = []
    after = None
    while True:
        if body:
            if after:
                body[class="hl-string">'after'] = after
            response = requests.post(url, headers=AUTH_HEADER, json=body)
        else:
            if after:
                params[class="hl-string">'after'] = after
            response = requests.get(url, headers=AUTH_HEADER, params=params)
        
        if response.status_code == 429:
            print(class="hl-string">"Rate limit hit, sleeping for 10 seconds...")
            time.sleep(10)
            continue
        
        response.raise_for_status() class=class="hl-string">"hl-comment"># Raise an exception for bad status codes
        data = response.json()
        results.extend(data.get(class="hl-string">'results', []))
        
        paging = data.get(class="hl-string">'paging')
        if paging and paging.get(class="hl-string">'next'):
            after = paging[class="hl-string">'next'][class="hl-string">'after']
        else:
            break
    return results

def format_timestamp(ts_string):
    class="hl-string">"""Converts HubSpot timestamps to ISO 8601 format."""
    if not ts_string:
        return None
    return pd.to_datetime(ts_string).isoformat()


def main():
    print(class="hl-string">"Starting HubSpot data extraction...")
    event_log = []
    last_data_update = datetime.datetime.now(datetime.timezone.utc).isoformat()

    class=class="hl-string">"hl-comment"># 1. Fetch all deals within the date range
    print(class="hl-string">"Fetching deals...")
    deal_properties = [
        class="hl-string">'dealname', class="hl-string">'createdate', class="hl-string">'dealstage', class="hl-string">'amount', 
        class="hl-string">'closedate', class="hl-string">'dealownerid', class="hl-string">'hs_lead_source', class="hl-string">'region', class="hl-string">'pipeline'
    ]
    search_body = {
        class="hl-string">'filterGroups': [{
            class="hl-string">'filters': [{
                class="hl-string">'propertyName': class="hl-string">'createdate',
                class="hl-string">'operator': class="hl-string">'BETWEEN',
                class="hl-string">'highValue': int(datetime.datetime.strptime(END_DATE, class="hl-string">'%Y-%m-%d').timestamp() * 1000),
                class="hl-string">'value': int(datetime.datetime.strptime(START_DATE, class="hl-string">'%Y-%m-%d').timestamp() * 1000)
            }]
        }],
        class="hl-string">'properties': deal_properties,
        class="hl-string">'limit': 100
    }
    all_deals = fetch_paginated_data(fclass="hl-string">"{API_BASE_URL}/crm/v3/objects/deals/search", body=search_body)
    print(fclass="hl-string">"Found {len(all_deals)} deals.")

    class=class="hl-string">"hl-comment"># Fetch owners for mapping
    owners_map = {owner[class="hl-string">'id']: fclass="hl-string">"{owner['firstName']} {owner['lastName']}" for owner in fetch_paginated_data(fclass="hl-string">"{API_BASE_URL}/crm/v3/owners/")}

    class=class="hl-string">"hl-comment"># 2. Process each deal to create events
    for i, deal in enumerate(all_deals):
        print(fclass="hl-string">"Processing deal {i+1}/{len(all_deals)}: {deal['id']}")
        deal_id = deal[class="hl-string">'id']
        props = deal.get(class="hl-string">'properties', {})
        
        base_event = {
            class="hl-string">'SalesOpportunityId': deal_id,
            class="hl-string">'SourceSystem': class="hl-string">'HubSpot CRM',
            class="hl-string">'LastDataUpdate': last_data_update,
            class="hl-string">'OpportunityAmount': props.get(class="hl-string">'amount'),
            class="hl-string">'OpportunityCloseDate': format_timestamp(props.get(class="hl-string">'closedate')),
            class="hl-string">'LeadSource': props.get(class="hl-string">'hs_lead_source'),
            class="hl-string">'Region': props.get(class="hl-string">'region'),
            class="hl-string">'OpportunityOwner': owners_map.get(props.get(class="hl-string">'dealownerid'))
        }

        class=class="hl-string">"hl-comment"># Activity: Opportunity Created
        event_log.append({
            **base_event,
            class="hl-string">'ActivityName': class="hl-string">'Opportunity Created',
            class="hl-string">'EventTime': format_timestamp(props.get(class="hl-string">'createdate')),
            class="hl-string">'SalesStage': class="hl-string">'Created',
            class="hl-string">'OpportunityOutcome': None
        })

        class=class="hl-string">"hl-comment"># Activities from Stage History
        try:
            stage_history_url = fclass="hl-string">"{API_BASE_URL}/crm/v3/objects/deals/{deal_id}?properties=dealstage&archived=false"
            history_response = requests.get(stage_history_url, headers=AUTH_HEADER)
            history_response.raise_for_status()
            history_data = history_response.json()
            for version in history_data.get(class="hl-string">'propertiesWithHistory', {}).get(class="hl-string">'dealstage', []):
                stage_id = version[class="hl-string">'value']
                if stage_id in PIPELINE_STAGE_MAPPING:
                    event_log.append({
                        **base_event,
                        class="hl-string">'ActivityName': PIPELINE_STAGE_MAPPING[stage_id],
                        class="hl-string">'EventTime': format_timestamp(version[class="hl-string">'timestamp']),
                        class="hl-string">'SalesStage': stage_id,
                        class="hl-string">'OpportunityOutcome': None
                    })
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Could not fetch stage history for deal {deal_id}: {e}")
        
        class=class="hl-string">"hl-comment"># Activities: Closed Won / Closed Lost
        final_stage = props.get(class="hl-string">'dealstage')
        if final_stage in [class="hl-string">'closedwon', class="hl-string">'closedlost']:
             event_log.append({
                **base_event,
                class="hl-string">'ActivityName': PIPELINE_STAGE_MAPPING[final_stage],
                class="hl-string">'EventTime': format_timestamp(props.get(class="hl-string">'closedate')),
                class="hl-string">'SalesStage': class="hl-string">'Closed',
                class="hl-string">'OpportunityOutcome': class="hl-string">'Won' if final_stage == class="hl-string">'closedwon' else class="hl-string">'Lost'
            })

        class=class="hl-string">"hl-comment"># Activities from Associated Objects (Engagements, Quotes)
        try:
            class=class="hl-string">"hl-comment"># Engagements for class="hl-string">'Initial Contact Made'
            assoc_engagements_url = fclass="hl-string">"{API_BASE_URL}/crm/v3/objects/deals/{deal_id}/associations/engagements"
            engagements = fetch_paginated_data(assoc_engagements_url)
            if engagements:
                first_engagement_time = None
                for engagement_assoc in engagements:
                    eng_id = engagement_assoc[class="hl-string">'id']
                    eng_details_url = fclass="hl-string">"{API_BASE_URL}/crm/v3/objects/engagements/{eng_id}?properties=hs_timestamp"
                    eng_res = requests.get(eng_details_url, headers=AUTH_HEADER)
                    if eng_res.ok:
                        eng_ts = eng_res.json()[class="hl-string">'properties'][class="hl-string">'hs_timestamp']
                        if not first_engagement_time or eng_ts < first_engagement_time:
                            first_engagement_time = eng_ts
                if first_engagement_time:
                    event_log.append({
                        **base_event,
                        class="hl-string">'ActivityName': class="hl-string">'Initial Contact Made',
                        class="hl-string">'EventTime': format_timestamp(first_engagement_time),
                        class="hl-string">'SalesStage': props.get(class="hl-string">'dealstage'),
                        class="hl-string">'OpportunityOutcome': None
                    })

            class=class="hl-string">"hl-comment"># Quotes for class="hl-string">'Quote Issued' and class="hl-string">'Quote Accepted'
            assoc_quotes_url = fclass="hl-string">"{API_BASE_URL}/crm/v3/objects/deals/{deal_id}/associations/quotes"
            quotes = fetch_paginated_data(assoc_quotes_url)
            for quote_assoc in quotes:
                quote_id = quote_assoc[class="hl-string">'id']
                quote_details_url = fclass="hl-string">"{API_BASE_URL}/crm/v3/objects/quotes/{quote_id}?properties=hs_createdate,hs_quote_status,hs_acceptance_date"
                quote_res = requests.get(quote_details_url, headers=AUTH_HEADER)
                if quote_res.ok:
                    quote_props = quote_res.json()[class="hl-string">'properties']
                    event_log.append({
                        **base_event,
                        class="hl-string">'ActivityName': class="hl-string">'Quote Issued',
                        class="hl-string">'EventTime': format_timestamp(quote_props[class="hl-string">'hs_createdate']),
                        class="hl-string">'SalesStage': props.get(class="hl-string">'dealstage'),
                        class="hl-string">'OpportunityOutcome': None
                    })
                    if quote_props.get(class="hl-string">'hs_quote_status') == class="hl-string">'SIGNED' and quote_props.get(class="hl-string">'hs_acceptance_date'):
                        event_log.append({
                            **base_event,
                            class="hl-string">'ActivityName': class="hl-string">'Quote Accepted',
                            class="hl-string">'EventTime': format_timestamp(quote_props[class="hl-string">'hs_acceptance_date']),
                            class="hl-string">'SalesStage': props.get(class="hl-string">'dealstage'),
                            class="hl-string">'OpportunityOutcome': None
                        })
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Could not fetch associations for deal {deal_id}: {e}")

    class=class="hl-string">"hl-comment"># 3. Create DataFrame and export to CSV
    print(class="hl-string">"Finalizing event log...")
    df = pd.DataFrame(event_log)
    df = df.dropna(subset=[class="hl-string">'EventTime'])
    df = df.sort_values(by=[class="hl-string">'SalesOpportunityId', class="hl-string">'EventTime'], ascending=[True, True])
    
    class=class="hl-string">"hl-comment"># Reorder and select final columns
    final_columns = [
        class="hl-string">'SalesOpportunityId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate',
        class="hl-string">'OpportunityOwner', class="hl-string">'SalesStage', class="hl-string">'OpportunityAmount', class="hl-string">'OpportunityCloseDate',
        class="hl-string">'OpportunityOutcome', class="hl-string">'Region', class="hl-string">'LeadSource'
    ]
    df = df[final_columns]

    output_filename = class="hl-string">'hubspot_event_log.csv'
    df.to_csv(output_filename, index=False)
    print(fclass="hl-string">"Extraction complete. Event log saved to {output_filename}")

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

query getLeadToCashData($after: String, $startTimestamp: DateTime!, $endTimestamp: DateTime!) {
  CRM {
    deal_collection(
      limit: 100
      after: $after
      filter: {
        createdate: { gte: $startTimestamp, lte: $endTimestamp }
      }
    ) {
      items {
        hs_object_id
        createdate
        closedate
        amount
        dealstage
        hs_is_closed_won
        properties_with_history(properties: ["dealstage"]) {
          ... on PropertyValuesWithHistoryDealDealstage {
            values {
              value
              source
              source_id
              updated_at
            }
          }
        }
        associations {
          quote_collection__deal_to_quote(limit: 100) {
            items {
              hs_object_id
              createdate
              hs_quote_accepted_date
              hs_status
            }
          }
          call_collection__deal_to_call(limit: 100, sort: [{ propertyName: "hs_created_date", direction: ASC }]) {
            items {
              hs_created_date
            }
          }
          email_collection__deal_to_email(limit: 100, sort: [{ propertyName: "hs_created_date", direction: ASC }]) {
            items {
              hs_created_date
            }
          }
          meeting_collection__deal_to_meeting(limit: 100, sort: [{ propertyName: "hs_created_date", direction: ASC }]) {
            items {
              hs_created_date
            }
          }
        }
        dealowner {
          email
        }
        properties {
          your_lead_source_property: [your_lead_source_property]
          your_region_property: [your_region_property]
        }
      }
      paging {
        next {
          after
        }
      }
    }
  }
}