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/08 04:35] windsor_ai |
crm_attribution [2022/02/17 01:33] (current) windsor_ai |
||
---|---|---|---|
Line 24: | Line 24: | ||
Once this is done the data can then be joined in [[https:// | Once this is done the data can then be joined in [[https:// | ||
- | < | + | < |
- | + | ||
- | BEGIN; | + | |
TRUNCATE TABLE crm_conversions; | TRUNCATE TABLE crm_conversions; | ||
INSERT INTO crm_conversions (datasource, | INSERT INTO crm_conversions (datasource, | ||
Line 35: | Line 33: | ||
FROM hubspot_data | FROM hubspot_data | ||
ORDER BY transaction_id; | ORDER BY transaction_id; | ||
- | COMMIT; | + | 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; | ||
</ | </ |