Differences

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

--- 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>
-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>

Knowledge Base

User Tools

Site Tools

Differences

Page Tools