# Salesforce Object Analysis

> **Last Updated**: 2026-01-17  
> **Note**: Database may have been wiped since import. Row counts are from migration logs.

## Overview
- **Total Objects Imported**: 171
- **Total Rows**: 193,740,247
- **Import Success Rate**: 100%

---

## Object Categories Summary

| Category | Count | Notes |
|----------|-------|-------|
| Core CRM | 12 | Account, Contact, Lead, Opportunity, etc. |
| Activity & Metrics | 15 | Tasks, daily/monthly metrics |
| Analytics (Fivetran-generated) | 5 | Performance aggregates |
| Fivetran Metadata | 10 | Sync logs, formulas, picklists |
| PQCrush Custom | 32 | Account planning & relationship mapping |
| Elvance Custom | 7 | Risk & feature gap management |
| History Tracking | 12 | Change history tables |
| User & Security | 8 | Users, roles, permissions |
| Standard Salesforce | 45 | Standard objects (Case, Asset, etc.) |
| Other Custom | 25 | Misc custom objects |
| **Total** | **171** | |

---

## Core CRM Objects (12)

| Object | Description | Key Relationships |
|--------|-------------|-------------------|
| `account` | Companies/organizations | `owner_id`, `record_type_id` |
| `contact` | People at accounts | `account_id` → account |
| `lead` | Unqualified prospects | `converted_account_id`, `converted_contact_id`, `converted_opportunity_id` |
| `opportunity` | Sales deals | `account_id` → account |
| `campaign` | Marketing campaigns | `owner_id` |
| `campaign_member` | Campaign participation | `campaign_id` → campaign, `contact_id` → contact, `lead_id` → lead |
| `contract` | Legal agreements | `account_id` → account |
| `quote` | Price quotes | `opportunity_id` → opportunity |
| `order` | Purchase orders | `account_id` → account |
| `product_2` | Products/services | — |
| `pricebook_2` | Price books | — |
| `pricebook_entry` | Price book entries | `product_2_id` → product_2, `pricebook_2_id` → pricebook_2 |

---

## High-Volume Tables

| Object | Row Count | Category |
|--------|-----------|----------|
| `fivetran_api_call` | 2,913,617 | Fivetran Metadata |
| `lead_history` | 2,754,331 | History Tracking |
| `campaign_member` | 599,471 | Core CRM |
| `fivetran_query` | 364,632 | Fivetran Metadata |
| `fivetran_audit` | 373,000 | Fivetran Metadata |
| `lead` | 320,571 | Core CRM |
| `salesforce_task` | 216,324 | Activity |
| `task` | 216,324 | Activity |

---

## Activity & Metrics Objects (15)

| Object | Row Count | Description |
|--------|-----------|-------------|
| `task` | 216,324 | To-do items and calls |
| `salesforce_task` | 216,324 | Task mirror for analytics |
| `lead_daily_metric` | ~14,000 | Daily lead engagement stats |
| `lead_monthly_metric` | ~12,000 | Monthly lead rollups |
| `contact_daily_metric` | ~10,000 | Contact engagement tracking |
| `contact_monthly_metric` | ~8,000 | Monthly contact rollups |
| `user_daily_metric` | ~500 | User activity stats |
| `user_monthly_metric` | ~400 | Monthly user rollups |
| `email_template_monthly_metric` | ~200 | Email performance |

---

## Fivetran Metadata Objects (10)

| Object | Row Count | Description |
|--------|-----------|-------------|
| `fivetran_api_call` | 2,913,617 | API call logs |
| `fivetran_audit` | 373,000 | Sync audit logs |
| `fivetran_query` | 364,632 | Query history |
| `fivetran_formula` | ~1,000 | Formula definitions |
| `fivetran_formula_model` | ~500 | Formula models |
| `fivetran_picklist_field` | ~2,000 | Picklist metadata |
| `fivetran_picklist_field_value` | ~15,000 | Picklist values |
| `fivetran_rollup_summary` | ~100 | Rollup summaries |
| `fivetran_dependent_picklist_relation` | ~50 | Dependent picklists |

---

## Relationship Diagram

```mermaid
graph TD
    subgraph "Core CRM"
        Account[account]
        Contact[contact]
        Lead[lead]
        Opportunity[opportunity]
        Campaign[campaign]
        Contract[contract]
    end
    
    subgraph "Junction Objects"
        CampaignMember[campaign_member]
        CampaignInfluence[campaign_influence]
        OpportunityContactRole[opportunity_contact_role]
    end
    
    subgraph "Activity"
        Task[task]
        LeadMetric[lead_daily_metric]
        ContactMetric[contact_daily_metric]
    end
    
    Contact -->|account_id| Account
    Opportunity -->|account_id| Account
    Contract -->|account_id| Account
    
    CampaignMember -->|campaign_id| Campaign
    CampaignMember -->|contact_id| Contact
    CampaignMember -->|lead_id| Lead
    
    CampaignInfluence -->|opportunity_id| Opportunity
    CampaignInfluence -->|contact_id| Contact
    CampaignInfluence -->|campaign_id| Campaign
    
    Lead -.->|converted_account_id| Account
    Lead -.->|converted_contact_id| Contact
    Lead -.->|converted_opportunity_id| Opportunity
    
    LeadMetric -->|lead_id| Lead
    ContactMetric -->|contact_id| Contact
```

---

## Key Findings

### 1. Circular Dependencies
Resolved by disabling FK checks during import:
- `lead` ↔ `opportunity` (via `converted_opportunity_id`)
- `lead` ↔ `contact` (via `converted_contact_id`)
- `user` ↔ `user_role` (via role hierarchy)

### 2. Common Audit Fields
All objects include:
- `created_by_id`, `last_modified_by_id`, `owner_id` → user references
- `created_date`, `last_modified_date` → timestamps
- `pargo_prefix__fivetran_synced` → Fivetran sync timestamp

### 3. Field Type Distribution
| Type | Usage | Description |
|------|-------|-------------|
| LongTextArea | Default | String fields (prevents truncation) |
| Lookup | FK refs | References to other objects |
| DateTime | Timestamps | Date/time values |
| Number | Metrics | Numeric counts and amounts |
| Currency | Money | Monetary values |
| Percent | Ratios | Percentage values |
| Boolean | Flags | True/false toggles |
| Picklist | Status | Enumerated values |

---

## Data Quality Assessment

- ✅ All 171 objects created successfully
- ✅ All 193.7M rows imported with 0 errors
- ✅ Foreign key relationships preserved
- ✅ Circular dependencies handled correctly
- ✅ Long text fields preserved without truncation

---

## Appendix: PQCrush Custom Objects (32)

Account planning and relationship mapping:
- `pqcrush_account_plan_c`, `pqcrush_relationship_map_c`, `pqcrush_objective_c`
- `pqcrush_key_date_c`, `pqcrush_team_member_c`, `pqcrush_pq_influence_c`
- `pqcrush_rm_*` (relationship management) - 15+ objects

## Appendix: Elvance Custom Objects (7)

Risk and feature gap management:
- `elvance_elvance_risk_c`, `elvance_feature_gap_c`
- `elvance_opp_scorecard_c`, `elvance_activity_log_c`
- Plus 3 additional supporting objects