Setting up Data Export to BigQuery of CRM Properties

Iker Camara Updated by Iker Camara

Dreamdata pulls information from standard objects in your CRM.

Properties on these objects can be made available in Dreamdata's Data Product in BigQuery (or connected cloud buckets).

Example of properties could be custom dates, amount fields, account status or in general any field available on the main CRM objects.

You can select the properties that you want to be exported on the integrations page of your CRM. The path inside the app is Data Platform > Sources.

Why is this useful?

The information that you have stored in your CRM will be available in Dreamdata's Data Product, which removes the need to use external tables to perform analysis.

Where will I find my custom fields?

Once you have selected the data you want to be exported, we will add an extra column custom_properties in one of the tables: companies, contacts and revenue, depending on which object you selected properties from.

Every object in the CRM has a corresponding Dreamdata table where we add the field. For example, properties of Deals in HubSpot or Opportunities in Salesforce will be added to the revenue table.

How do I use custom_properties?

First, you will need to extract them as the custom_properties column is of type JSON string. Here is a SQL query as an example.

It is a good practice to cast those properties that represent a date or a numeric value for later use.

SELECT 
  *, 
  JSON_EXTRACT_SCALAR(custom_properties, "$.[property_name]") AS property_name,                     
  CAST( 
    JSON_EXTRACT_SCALAR(custom_properties, "$.[property_date]") AS TIMESTAMP 
  ) AS property_date
FROM 
  `dreamdata.[name].revenue`

Second, if you want to add the new fields to one of our activity tables like revenue_attribution or session_performance you will have to perform a join between the tables.

To join with an activity table the following join keys should be used to be sure that you pass the information correctly:

  • revenue: dealId and revenueModel
  • companies: companyId
  • contacts: email and companyId

Example:

1) The query below passes field_1 to the events table, so we can do a segmentation analysis based on the classification in field_1.

WITH custom_companies AS (
    SELECT 
        companyId 
      , JSON_EXTRACT_SCALAR(custom_properties, "$.field_1") AS field_1                     
    FROM 
      `dreamdata.[company_name].companies`
)
SELECT 
    r.*
  , c.field_1
FROM 
  `dreamdata.[company_name].events` AS r
  LEFT JOIN custom_companies AS c ON r.companyId = c.companyId

2) To add a property from an opportunity to the data available in the revenue_attribution table, we join the property from the revenue table where the opportunities using the join key's dealId and revenueModel

WITH opps_property AS (
    SELECT 
        dealId 
      , revenueModel
      , JSON_EXTRACT_SCALAR(custom_properties, "$.field_1") AS field_1                     
    FROM 
      `dreamdata.[name].revenue`
)
SELECT 
    r.*
  , c.field_1
FROM 
  `dreamdata.[name].revenue_attribution` AS r
  LEFT JOIN opps_property AS c 
ON r.dealId = c.dealId
AND r.revenueModel = c.revenueModel

3) To add properties from the contacts table to the revenue_attribution table, we join using both email and companyId as join keys

WITH contact_property AS (
    SELECT 
        email 
      , companyId
      , JSON_EXTRACT_SCALAR(custom_properties, "$.field_1") AS field_1                     
    FROM 
      `dreamdata.[name].contacts`
)
SELECT 
    r.*
  , c.field_1
FROM 
  `dreamdata.[name].revenue_attribution` AS r
  LEFT JOIN contact_property AS c 
ON r.email = c.email
AND r.companyId = c.companyId

How did we do?

Contact