Technical Brief - Engagement Cycle

Table of Contents

1. Project Overview
2. What is an Engagement Cycle?
3. Data Sources
4. Event Definitions and Criteria
5. Event Types
6. Dimensions for Cohort Analysis
7. KPIs to be Measured
8. Implementation Plan

Project Overview

Objective

• Build Revenue Operations insights and KPIs for customers using Salesforce data.

Methodology

• 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?

1. Holistic View: It allows for tracking the entire history of engagement with an account, not just the latest status.
2. Multiple Touchpoints: Accounts often go through several cycles of engagement, especially in long sales processes or recurring business models.
3. Dynamic Tracking: Field values in Salesforce or any CRM are generally overwritten, losing the previous state. Engagement Cycles preserve this information.

Data Sources

BigQuery Tables

1. hired-393411.Hired.sfdc_account

   • 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

2. hired-393411.Hired.sfdc_account_history

   • Fields: Id, AccountId, CreatedDate, Field, OldValue, NewValue

3. hired-393411.Hired.sfdc_campaign_history

   • Fields: campaign_id, contact_id, created_Date, mql, mql_date, campaign_name, campaign_type

4. hired-393411.Hired.sfdc_contact

   • 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

5. hired-393411.Hired.sfdc_contact_history

   • Fields: con_history_id, contact_id, con_history_created_date, con_history_field_name, con_history_old_value, con_history_new_value

6. hired-393411.Hired.sfdc_opportunity

   • Fields: opportunity_id, opportunity_created_date, contact_id, opportunity_name, stage_name, opportunity_last_activity_date, won_acv, account_id, Account

7. hired-393411.Hired.sfdc_opportunity_history

   • Fields: opp_history_id, opportunity_id, opp_history_created_date, created_by_id, opp_history_field, opp_history_new_value, opp_history_old_value

8. hired-393411.Hired.sfdc_task

   • Fields: task_id, activity_date, completion_date, subject, task_subtype, account_id, who_id, what_type, what_id, ownew_name

Event Definitions and Criteria

Source Criteria

Inbound

• Latest campaign contains any of ('demo', 'signup', 'in-app').
• 90-day qualification period as Inbound after conversion.
• After 90 days, considered 'unqualified'.

Outbound

• Is a contact and account not flagged as Inbound.
• Must have a Sales Activity.

Enter Criteria

Inbound

• Initial conversion enters prospect into the funnel.
• If already in the funnel and converts again, renew the 90-day expiry date.

Outbound

• Non-inbound account and contact require a Sales activity logged.

Exit Criteria

Inbound

• 90 days since the last conversion.
• Sales rep disqualifies the account.
• Opportunity is lost.

Outbound

• 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.

Event Types

1. New Accounts
2. Worked Accounts
3. Meetings Booked
4. Qualified Opps
5. Access
6. Placement
7. Subscription

Dimensions for Cohort Analysis

1. Owner of the Engagement Cycle
2. Owner Role
3. Source of the Engagement Cycle (Inbound/Outbound)
4. Consequence of the Scraping Signal
   • If yes, what's the scraping age?
5. Account Segment
6. Campaign Type

KPIs to be Measured

Core Metrics

1. Number of New Accounts (Inbound)
2. Number of Worked Accounts (Inbound & Outbound)
3. Number of Meetings Booked (Inbound & Outbound)
4. Number of Qualified Opportunities (Inbound & Outbound)
5. Number of Access (Inbound & Outbound)
6. Number of Placements (Inbound & Outbound)
7. Number of Subscriptions (Inbound & Outbound)
8. Conversion rates between each event type
9. Time-based conversions
10. Cohort analysis

Additional Metrics

1. Number of Calls
2. Number of Emails
3. Volumes between each event stage
4. Conversions between each event stage
5. Cycles between each event stage

Implementation Plan (Updated)

Technical Implementation Steps

1. 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.

2. Schema Design:

   • 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.

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

4. 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.

5. Time-based Calculations:

   • Implement SQL logic to track time-based metrics, such as the duration of each engagement cycle, time to conversion, etc.

6. KPI Calculations:

   • Build queries to calculate the KPIs you're interested in, like the number of new accounts, worked accounts, meetings booked, etc.

7. Cohort Analysis:

   • 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.

8. 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.

9. Data Visualization:

   • Use a data visualization tool that integrates with BigQuery to create dashboards that showcase your KPIs and other metrics.

10. 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.

** Detail QA Center - Sync with Phyllis about some of Hired problems (inbound leads)