Setting up Data Export to BigQuery of CRM Properties
Updated
by
Mikkel Settnes
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).
Examples 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.
The revenue_attribution have these fields pre-joined: company_properties, contact_properties, and stage_properties, corresponding to the custom_properties in companies, contacts and revenue, respectively.
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 event 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:
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 events 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].events` AS r, UNNEST(stages) as s
LEFT JOIN opps_property AS c
ON s.dealId = c.dealId
AND s.revenueModel = c.revenueModel
3) To add properties from the contacts table to the events table, we join using both email and companyId as join keys
WITH contact_property AS (
SELECT
, companyId
, JSON_EXTRACT_SCALAR(custom_properties, "$.field_1") AS field_1
FROM
`dreamdata.[name].contacts`
)
SELECT
r.*
, c.field_1
FROM
`dreamdata.[name].events` AS r
LEFT JOIN contact_property AS c
ON r.email = c.email
AND r.companyId = c.companyId
