Differences

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

--- shopify_clv_attribution [2021/09/06 15:47]
windsor_ai created
+++ shopify_clv_attribution [2021/09/29 14:46] (current)
windsor_ai
@@ Line 7: / Line 7: @@
     - shopify_orders \\ Connector URL: <code> https://connectors.windsor.ai/shopify?api_key=[your API key]&date_preset=last_90d&fields=order_id,order_current_total_price,order_email,order_updated_at,order_name,date,order_tags </code> \\ Destination table name: shopify_orders \\ Schedule: 12h \\ Click Add New
     - shopify_customers \\ Connector URL: <code> https://connectors.windsor.ai/shopify?api_key=[your API key]&date_preset=last_90d&fields=customer_id,date,customer_email,customer_total_spent</code> \\ Destination table name: shopify_customers \\ Schedule: 12h \\ Click Add New
-  - Head to [[https://charts.windsor.ai/w/sqllab|SQL Lab]] and run the following statement \\ <code> TRUNCATE TABLE crm_conversions;
+  - Head to [[https://charts.windsor.ai/w/sqllab|SQL Lab]] and run the following statement \\ <code>BEGIN;
+TRUNCATE TABLE crm_conversions;
 INSERT INTO crm_conversions(net_revenue,transaction_id,crm_type)
-SELECT DISTINCT
+	SELECT
-sum(customers.customer_total_spent::REAL)  as net_revenue,
+		sum(customers.customer_total_spent::REAL) AS net_revenue,
-orders.order_name as transaction_id,
+		REPLACE(orders.order_name,'#','') as transaction_id,
-'shopify - net revenue' as crm_type
+		'shopify - net revenue' AS crm_type
-FROM shopify_customers as customers
+	FROM shopify_customers AS customers
-LEFT JOIN shopify_orders as orders on orders.order_email = customers.customer_email
+	LEFT JOIN shopify_orders AS orders ON orders.order_email = customers.customer_email
-GROUP BY 2;</code>
+	GROUP BY 2,3;
+COMMIT;</code>

Knowledge Base

User Tools

Site Tools

Differences

Page Tools