This shows you the differences between two versions of the page.
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: | ||
</ | </ | ||
- | The data can then be joined in [[https:// | + | You can automate the streaming of the data from your connector URL to your Windsor.ai Cloud Database on your [[https:// |
+ | |||
+ | {{ : | ||
+ | |||
+ | Once this is done the data can then be joined in [[https:// | ||
+ | |||
+ | < | ||
+ | TRUNCATE TABLE crm_conversions; | ||
+ | INSERT INTO crm_conversions (datasource, | ||
+ | SELECT | ||
+ | ' | ||
+ | encode(sha256(" | ||
+ | concat(' | ||
+ | FROM hubspot_data | ||
+ | ORDER BY transaction_id; | ||
+ | COMMIT;</ | ||
+ | |||
+ | |||
+ | === 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 | ||
+ | |||
+ | < | ||
+ | |||
+ | 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. | ||
+ | |||
+ | < | ||
+ | BEGIN; | ||
+ | TRUNCATE TABLE crm_conversions; | ||
+ | INSERT INTO crm_conversions (datasource, | ||
+ | SELECT | ||
+ | ' | ||
+ | encode(sha256(email:: | ||
+ | concat(' | ||
+ | MIN(date:: | ||
+ | SUM(amount:: | ||
+ | FROM custom_stripe_data | ||
+ | WHERE email <> '' | ||
+ | GROUP BY 1,2,3; | ||
+ | COMMIT; | ||
+ | </ |