This shows you the differences between two versions of the page.
Both sides previous revision Previous revision | |||
crm_attribution [2021/07/08 04:36] windsor_ai |
crm_attribution [2022/02/17 01:33] (current) windsor_ai |
||
---|---|---|---|
Line 34: | Line 34: | ||
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; | ||
+ | </ |