Table of Contents
- Project Overview
- What is an Engagement Cycle?
- Data Sources
- Event Definitions and Criteria
- Event Types
- Dimensions for Cohort Analysis
- KPIs to be Measured
- Implementation Plan
- Build Revenue Operations insights and KPIs for customers using Salesforce data.
- Strategically address fragmented CRM data issues to optimize customer experience and data integrity.
- Create a scalable process, documenting the complex stuff that's commom to every Customer, and what needs to be collected for customization.
- Event-based approach
- Data source: Salesforce, extracted via simple_salesforce
- Data storage: BigQuery
- Data visualization: Views
What is an Engagement Cycle?
An Engagement Cycle is a pseudo-object created to consolidate and timestamp all the relevant data associated with a customer engagement period. Each time a sales representative starts working with a new or existing account, a new Engagement Cycle is initiated. This approach allows us to capture the full journey of an account through the sales funnel, rather than just a snapshot of its current status.
Why Engagement Cycles?
- Holistic View: It allows for tracking the entire history of engagement with an account, not just the latest status.
- Multiple Touchpoints: Accounts often go through several cycles of engagement, especially in long sales processes or recurring business models.
- Dynamic Tracking: Field values in Salesforce or any CRM are generally overwritten, losing the previous state. Engagement Cycles preserve this information.
- Fields: account_id, account_created_date, account_name, account_segment, job_scraping_date, job_scraping_trial_date, no_open_tech_roles, most_recent_contract_end_date, account_last_activity_date, account_score, employee_count, employee_count_source, account_hq_country, account_type, account_df_stage, website, account_owner_name
- Fields: Id, AccountId, CreatedDate, Field, OldValue, NewValue
- Fields: campaign_id, contact_id, created_Date, mql, mql_date, campaign_name, campaign_type
- Fields: contact_id, contact_created_date, contact_first_name, contact_last_name, job_function, job_level, title, contact_last_activity_date, contact_disqualified_reason, contact_status, account_id, record_type, RecordType
- Fields: con_history_id, contact_id, con_history_created_date, con_history_field_name, con_history_old_value, con_history_new_value
- Fields: opportunity_id, opportunity_created_date, contact_id, opportunity_name, stage_name, opportunity_last_activity_date, won_acv, account_id, Account
- Fields: opp_history_id, opportunity_id, opp_history_created_date, created_by_id, opp_history_field, opp_history_new_value, opp_history_old_value
- Fields: task_id, activity_date, completion_date, subject, task_subtype, account_id, who_id, what_type, what_id, ownew_name
Event Definitions and Criteria
- Latest campaign contains any of ('demo', 'signup', 'in-app').
- 90-day qualification period as Inbound after conversion.
- After 90 days, considered 'unqualified'.
- Is a contact and account not flagged as Inbound.
- Must have a Sales Activity.
- Initial conversion enters prospect into the funnel.
- If already in the funnel and converts again, renew the 90-day expiry date.
- Non-inbound account and contact require a Sales activity logged.
- 90 days since the last conversion.
- Sales rep disqualifies the account.
- Opportunity is lost.
- 30 days without activities.
- No follow-up date in the future in the account field.
- No activity scheduled for the future in the task field.
- Sales rep disqualifies the account.
- Opportunity is lost.
- New Accounts
- Worked Accounts
- Meetings Booked
- Qualified Opps
Dimensions for Cohort Analysis
- Owner of the Engagement Cycle
- Owner Role
- Source of the Engagement Cycle (Inbound/Outbound)
- Consequence of the Scraping Signal
- If yes, what's the scraping age?
- Account Segment
- Campaign Type
KPIs to be Measured
- Number of New Accounts (Inbound)
- Number of Worked Accounts (Inbound & Outbound)
- Number of Meetings Booked (Inbound & Outbound)
- Number of Qualified Opportunities (Inbound & Outbound)
- Number of Access (Inbound & Outbound)
- Number of Placements (Inbound & Outbound)
- Number of Subscriptions (Inbound & Outbound)
- Conversion rates between each event type
- Time-based conversions
- Cohort analysis
- Number of Calls
- Number of Emails
- Volumes between each event stage
- Conversions between each event stage
- Cycles between each event stage
Implementation Plan (Updated)
Technical Implementation Steps
Review Data Quality:
- Ensure that the data imported into BigQuery is clean, accurate, and up-to-date. Check for any missing values, duplicates, or inconsistencies.
- Plan the schema for the views or tables where you will store the engagement cycles and event data. This will include the fields you need for your KPIs, as well as the dimensions for cohort analysis.
SQL Queries for Event Extraction:
- Write SQL queries to extract the events based on the criteria you've defined for Inbound and Outbound leads. This will typically involve complex joins across multiple tables (like sfdc_account, sfdc_campaign_history, sfdc_opportunity, etc.).
Engagement Cycle Identification:
- Create queries or scripts that identify the start and end of each engagement cycle. This will involve tracking each account or contact as they move through the various stages, and also handling the entry/exit criteria, including disqualifications and lost opportunities.
- Implement SQL logic to track time-based metrics, such as the duration of each engagement cycle, time to conversion, etc.
- Build queries to calculate the KPIs you're interested in, like the number of new accounts, worked accounts, meetings booked, etc.
- Create queries that allow you to analyze the data in cohorts, based on the dimensions you've identified like the owner, source, account segment, etc.
Create Views or Materialized Views:
- Once your queries are ready and tested, create BigQuery views or materialized views. This will make it easier to use this data in visualizations.
- Use a data visualization tool that integrates with BigQuery to create dashboards that showcase your KPIs and other metrics.
Monitoring and Maintenance:
- Set up alerts or checks to ensure that the data pipeline is functioning correctly. This will help you catch any issues before they become bigger problems.