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
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;</code> 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.txt ยท Last modified: 2022/02/17 01:33 by windsor_ai