User Tools

Site Tools


crm_attribution

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
crm_attribution [2021/07/06 09:49]
windsor_ai [General Overview]
crm_attribution [2022/02/17 01:33] (current)
windsor_ai
Line 18: Line 18:
 </code> </code>
  
-The data can then be joined in [[https://charts.windsor.ai/w/sqllab?|SQL Lab]] with the data in the conversions table+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>
crm_attribution.1625561355.txt.gz · Last modified: 2021/07/06 09:49 by windsor_ai