====== CRM Attribution ======

{{ :crm_attribution.png?nolink&600 |}}

===== 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 [[https://www.windsor.ai/how-to-connect-google-analytics-to-your-crm/|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  ===

<code>https://connectors.windsor.ai/salesforce?api_key=[your API key]&date_preset=last_7d&fields=contact_firstname,contact_lastname,contact_email,contact_phone </code>

=== Sample query to get data from HubSpot ====

<code>https://connectors.windsor.ai/hubspot?fields=contact_firstname,contact_lastname,contact_email,contact_company,contact_phone,contact_lifecyclestage&api_key=[your API key]

</code>

You can automate the streaming of the data from your connector URL to your Windsor.ai Cloud Database on your [[https://onboard.windsor.ai/|Windsor.ai configuration]]. Here is a sample screenshot \\

{{ :pushing_crm_data-prest.png?nolink&800 |}}

Once this is done the data can then be joined in [[https://charts.windsor.ai/w/sqllab?|SQL Lab]] with the data in the conversions table.

<code>BEGIN;
TRUNCATE TABLE crm_conversions;
INSERT INTO crm_conversions (datasource, transaction_id, crm_type)
SELECT
    '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;
COMMIT;</code>


=== Sample query to get data from Stripe ====

In destinations create a new windsor.ai cloud database destination and create a table with the destination table name custom_stripe_data. \\ Use the connector URL 

<code>https://connectors.windsor.ai/stripe?api_key=[your API key]&date_preset=last_7d&fields=date,email,amount,currency,status</code>

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 charts.windsor.ai and use this script.

<code>
BEGIN;
TRUNCATE TABLE crm_conversions;
INSERT INTO crm_conversions (datasource, transaction_id, crm_type,order_date, net_revenue)
SELECT
    '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;
COMMIT;
</code>