Advanced Use Cases

SQL Example 1: Revenue Generation Analysis by Customer Fit Score

This example demonstrates how to report revenue generation for a specific stage in your sales funnel. It illustrates segmentation using a custom property from your CRM's Companies table. The example focuses on analyzing the NewBiz stage, segmenting by the customer_fit_score property.

The goal is to showcase how to leverage Dreamdata's data warehouse solution to address business questions using custom CRM properties.

In this scenario, understanding that the customer_fit_score is a custom attribute previously defined in the CRM and synced to the data warehouse is crucial. While your CRM may include other custom properties, this approach provides a flexible model for similar data analyses.

For further guidance on enabling custom properties in the data warehouse, please refer to the Custom Properties documentation.

-- BigQuery SQL example
-- SQL query to analyze revenue generation by customer fit score in NewBiz stage
WITH company_scores AS (
    -- -- Extract Customer Fit Score
    -- -- This is a hypothetical example, the customer_fit_score may not be a real property in your CRM
    SELECT
        dd_company_id,
        JSON_VALUE(custom_properties.customer_fit_score) AS customer_fit_score
    FROM
        companies_table
    WHERE
        JSON_VALUE(custom_properties.customer_fit_score) IS NOT NULL
),
newbiz_revenue AS (
    -- -- Filter NewBiz Stage Revenue
    SELECT
        s.dd_company_id,
        s.value
    FROM
        stages_table AS s
    WHERE
        s.timestamp >= '2024-12-01'
        AND s.timestamp < '2025-01-01'
        AND s.stage_name = 'NewBiz'
)
-- -- Aggregate Revenue
SELECT
    cs.customer_fit_score,
    ROUND(SUM(nr.value), 2) AS total_revenue,
    ROUND(AVG(nr.value), 2) AS avg_revenue
FROM
    newbiz_revenue AS nr
JOIN
    company_scores AS cs ON nr.dd_company_id = cs.dd_company_id
GROUP BY
    cs.customer_fit_score
ORDER BY
    cs.customer_fit_score ASC

Explanation

• Purpose: The query calculates total and average revenue generated for the 'NewBiz' stage in 2024, segmented by the customer_fit_score custom property.

• Steps:

  1. Extract Customer Fit Score: The common table expression (CTE) company_scores extracts customer_fit_score from the JSON in the Companies table using the JSON_VALUE function.
  2. Filter NewBiz Stage Revenue: Another CTE newbiz_revenue filters the Stages table for records where stage_name is 'NewBiz' within the specified timestamp range.
  3. Aggregate Revenue: The final query joins these CTEs, groups by customer_fit_score, and calculates both total and average revenue for each segment.

• JSON_VALUE Function: The custom_properties column is a JSON column format, and the JSON_VALUE function is used to extract the customer_fit_score from the JSON column. This function is specific to BigQuery, but similar methods exist in other data warehouses, allowing users to extract and analyze JSON data effectively.

Output

The output will be a table similar to the following: