Skip to main content

Stages

Stages are your main funnel KPIs, representing milestones within your sales funnel.

A stage is identified by a timestamp and the contact (dd_contact_id) or company (dd_company_id) reaching the stage (dd_stage_id). A stage can be defined through the Dreamdata Data Hub UI and the data will automatically reflect the UI settings at next data refresh.

A stage has a value indicating the revenue associated with reaching the stage.

A stage is typically built on an object from either a CRM or MAP. The properties are taken directly from those source integrations. In addition, a stage can also be directly defined using events.

Each stage has an associated journey. The stage journey is defined by the events performed by the associated company or contact happening before the stage was reached (no setting will allow events after the stage timestamp to be part of the stage journey)

Which events are included are defined by the journey type: contact level, company level or opportunity level (contacts on the opportunity).

Below, we'll explore use cases and SQL queries that help replicate the insights typically accessed in our app's Revenue Report but that can be directly accessed through the data warehouse solution that Dreamdata provide.

SQL Examples for the Stages Table​

Example 1: Querying the Revenue Report Data​

The following query demonstrates how to use the Stages table to replicate the insights from the Revenue Report available in our application. This example allows you to extract key metrics, such as stage details, company information, and deal attributes, directly from your data warehouse.

Use the query below to gain insights similar to those in the app's Revenue Report:

Revenue Report

SELECT
  s.stage_name,
  s.timestamp,
  s.dd_stage_id as stage_id,
  s.dd_company_id,
  c.properties.name as company_name,
  c.properties.country as company_country,
  c.properties.industry as company_industry,
  c.properties.number_of_employees as company_number_of_employees,
  c.properties.annual_revenue as company_annual_revenue,
  c.account_owner.name as account_owner,
  s.value,
  s.primary_owner.name as deal_owner
FROM
  stages_table as s
LEFT JOIN
  companies_table as c
  ON s.dd_company_id = c.dd_company_id

WHERE
  timestamp >= '2024-12-01'
  and timestamp < '2025-01-01'
  -- and s.stage_name = 'MQL' -- -- Uncomment to filter by stage name

Explanation

  • Filtering by Date: This query is set to filter deals completed within December 2024. Modify the date range as necessary for different time periods.
  • Company Information: The query joins with the Companies table to include attributes such as company name, country, industry, and revenue, allowing for detailed segmentation.
  • Stage Information: You can further drill down by uncommenting the stage filter line to focus on specific stages, such as 'MQL'.
  • Key Metrics: Retrieve metrics like deal value and deal owner, similar to what is presented in the Revenue Report dashboard.