User Tools

Site Tools


CRM Attribution

General Overview

This article will guide you on how to match the conversions from Google Analytics to your CRM data. As prerequisite you will need to have a common join key from both your GA and CRM data. A frequently used approach of bringing hashed email address into Google Analytics can be found in our article How to connect Google Analytics to your CRM system?. Of course if you already have a custom dimension in place with an identifier you can use this dimension for the join.

Sample query to get data from Salesforce[your API key]&date_preset=last_7d&fields=contact_firstname,contact_lastname,contact_email,contact_phone 

Sample query to get data from HubSpot,contact_lastname,contact_email,contact_company,contact_phone,contact_lifecyclestage&api_key=[your API key]

You can automate the streaming of the data from your connector URL to your Cloud Database on your configuration. Here is a sample screenshot

Once this is done the data can then be joined in SQL Lab with the data in the conversions table.

TRUNCATE TABLE crm_conversions;
INSERT INTO crm_conversions (datasource, transaction_id, crm_type)
    'ds_hubspot' as datasource,
    encode(sha256("contact_email"::bytea), 'hex') as transaction_id,
    concat('Salesforce - ',contact_lifecyclestage) as crm_type
FROM hubspot_data
ORDER BY transaction_id;

Sample query to get data from Stripe

In destinations create a new cloud database destination and create a table with the destination table name custom_stripe_data.
Use the connector URL[your API key]&date_preset=last_7d&fields=date,email,amount,currency,status

Now in the sample below we look at a SaaS model where we expect a customer to have multiple transactions. We attributed the entire customer lifetime value to the initial Stripe transaction.

Go to SQL Lab on and use this script.

TRUNCATE TABLE crm_conversions;
INSERT INTO crm_conversions (datasource, transaction_id, crm_type,order_date, net_revenue)
    'custom_stripe_data' as datasource,
    encode(sha256(email::bytea), 'hex') as transaction_id,
    concat('Stripe - ',status) as crm_type,
    MIN(date::DATE) as order_date,
    SUM(amount::NUMERIC) as net_revenue
FROM custom_stripe_data
WHERE email <> ''
GROUP BY 1,2,3;
crm_attribution.txt · Last modified: 2022/02/17 01:33 by windsor_ai