Shopify Report

active_customer_no_conversions.sql

WITH 
paid_shops AS (
    SELECT DISTINCT ON(shopify_domain) shopify_domain,shop_plan, plans.name as plan_name, currency 
    FROM shops 
    INNER JOIN charges on shops.id = charges.shop_id
    INNER JOIN plans ON charges.plan_id = plans.id
    WHERE charges.processor = 'shopify' AND (commenced_at <= now()) 
          AND (cancelled_at IS NULL OR cancelled_at >= now())
    -- order by shopify_domain, charges.amount DESC
),


shop_aggregators AS (
    SELECT 
		paid_shops.shopify_domain,
		DATE_TRUNC('month', shop_statistics.date) as "Month", 
        
		SUM(cross_sell_clicks) as "Total Clicks", 
		SUM(cross_sell_order_count) AS "Conversions Count",
        COALESCE( SUM(cross_sell_order_value::float), 0 ) as "Conversion Total"
		
    FROM paid_shops
		
    INNER JOIN shop_statistics ON shop_statistics.shop_domain = paid_shops.shopify_domain
    -- Current month 
    -- WHERE shop_statistics.date >= date_trunc('month', CURRENT_DATE)
    WHERE shop_statistics.date >= {start_time} AND shop_statistics.date < {end_time}
    GROUP BY shopify_domain, DATE_TRUNC('month', shop_statistics.date)
    ORDER by "Month" DESC
),

reports AS ( 
  SELECT paid_shops.shopify_domain AS "Shop URL", 
         paid_shops.shop_plan AS "Shop Plan",
         paid_shops.plan_name AS "Plan Name",
         "Total Clicks",
         "Conversions Count", 
         round("Conversion Total"::numeric, 2) AS "Conversion Total", 
         paid_shops.currency, 
         shop_aggregators."Month"
  FROM paid_shops INNER JOIN shop_aggregators
  ON shop_aggregators.shopify_domain = paid_shops.shopify_domain
)

-- SELECT * FROM active_paid_charges;
-- SELECT * FROM paid_shops;
-- SELECT * FROM shop_aggregators;
SELECT * FROM reports ORDER BY "Conversions Count" DESC;

on_trial.sql

WITH 
shops_with_plan AS (
    SELECT shops.id, shopify_domain, charges.processor, plans.name as plan_name, commenced_at,
    
    CASE
    WHEN processor = 'trial' THEN 'yes'
    ELSE 'no'
    END AS on_trial
    
    
    FROM shops 
    INNER JOIN charges on shops.id = charges.shop_id
    INNER JOIN plans on plans.id = charges.plan_id
    WHERE  ( commenced_at <= now() ) 
    AND (cancelled_at IS NULL OR cancelled_at >= now())
)

-- SELECT * FROM stats;
SELECT id AS "ID",
       shopify_domain AS "Shop",
       on_trial AS "Trial",
       plan_name AS "Plan",
       commenced_at AS "Start time"
FROM shops_with_plan order by shopify_domain ASC;

remaining_click.sql

SELECT
  shops.*
	plan_name,
	total_click,
	plan_clicks,
	shops.shopify_domain,
	
FROM shops 
LEFT JOIN(
	SELECT 
			plans.name AS plan_name,
			total_click AS total_click,
			plans.clicks AS plan_clicks,
			shops.shopify_domain
		FROM "shops" 
		INNER JOIN charges ON charges.shop_id = shops.id
		INNER JOIN plans ON charges.plan_id = plans.id
		LEFT JOIN (
			SELECT 
				shop_statistics.shop_domain, 
				DATE_TRUNC('month', shop_statistics.date),
				COALESCE( SUM(shop_statistics.clicks),  0 ) AS total_click 
			FROM "shop_statistics" 
			INNER JOIN shops ON shops.shopify_domain = shop_statistics.shop_domain 
			WHERE (shop_statistics.date >= date_trunc('month', CURRENT_DATE)) 
			GROUP BY shop_statistics.shop_domain, DATE_TRUNC('month', shop_statistics.date) 
		) AS shop_with_total_clicks ON shop_with_total_clicks.shop_domain = shops.shopify_domain 

		WHERE (commenced_at <= now()) AND (( cancelled_at IS NULL OR cancelled_at >= now() ))
) AS shop_with_clicks
ON shop_with_clicks.shopify_domain = shops.shopify_domain;






-- 
FROM "shops" 
LEFT JOIN (
	SELECT DISTINCT ON(shops.shopify_domain) shopify_domain, plans.name AS plan_name, plans.clicks AS plan_clicks, charges.amount as charge_amount  
	FROM "shops" 
	INNER JOIN charges ON charges.shop_id = shops.id 
	INNER JOIN plans ON charges.plan_id = plans.id 
	WHERE (commenced_at <= now() AND ( cancelled_at IS NULL OR cancelled_at >= now() ))
) AS shops_with_plan ON  shops.shopify_domain = shops_with_plan.shopify_domain
LEFT JOIN (
		SELECT shop_statistics.shop_domain, DATE_TRUNC('month', shop_statistics.date), COALESCE( SUM(shop_statistics.clicks),  0 ) AS total_clicks 
		FROM "shop_statistics" 
		INNER JOIN shops ON shops.shopify_domain = shop_statistics.shop_domain 
		WHERE (shop_statistics.date >= date_trunc('month', CURRENT_DATE)) 
		GROUP BY shop_statistics.shop_domain, DATE_TRUNC('month', shop_statistics.date) 
) AS shop_with_total_clicks ON shop_with_total_clicks.shop_domain = shops.shopify_domain 
order by total_clicks DESC


-- Some shops have more than one active plan, distinct on and take the one with greatest charge amount
SELECT DISTINCT ON (shops.shopify_domain) shopify_domain , plans.name AS plan_name, plans.clicks AS plan_clicks, charges.amount as charge_amount , commenced_at 
FROM "shops" 
INNER JOIN charges ON charges.shop_id = shops.id 
INNER JOIN plans ON charges.plan_id = plans.id 
WHERE commenced_at <= now() AND ( cancelled_at IS NULL OR cancelled_at >= now() ) AND shops.shopify_domain = 'coral-reef-swim.myshopify.com'
ORDER BY shops.shopify_domain, charge_amount DESC

shop_with_0_recommendations_click.sql

WITH


  shop_with_plan AS (
    SELECT 
		 DISTINCT ON(shops.shopify_domain) shopify_domain AS shopify_domain,
		 plans.name AS plan_name,
		 COALESCE( plans.clicks, 0 )  AS plan_clicks,
		 commenced_at,
		 cancelled_at,
		 charges.id AS charge_id,
		 charges.status,
		 charges.amount,
		 
		 EXTRACT(DAY FROM commenced_at),
		 EXTRACT(DAY FROM CURRENT_DATE),
		 
         DATE_TRUNC('month', CURRENT_DATE - interval '1' month),
         DATE_TRUNC('month', CURRENT_DATE - interval '1' month) + ( EXTRACT(DAY FROM commenced_at) * interval '1 day'),
         
        CASE WHEN EXTRACT(DAY FROM commenced_at) < EXTRACT(DAY FROM CURRENT_DATE)  THEN  DATE_TRUNC('month', CURRENT_DATE) +  ( ( EXTRACT(DAY FROM commenced_at) -1 )* interval '1 day')
            ELSE  DATE_TRUNC('month', CURRENT_DATE - interval '1' month) + (( EXTRACT(DAY FROM commenced_at) - 1) * interval '1 day')
       END AS "commence_recur_at"
	FROM shops 
    INNER JOIN charges on charges.shop_id = shops.id
    INNER JOIN plans on charges.plan_id   = plans.id

    WHERE 
    --   charges.processor = 'shopify' AND 
      (commenced_at <= now()) 
      AND (cancelled_at IS NULL OR cancelled_at >= now())
      AND plans.clicks IS NOT NULL
    ORDER BY shops.shopify_domain, charges.amount DESC
  ),
	
  shop_with_plan_sanity AS (
		SELECT  DISTINCT ON (shopify_domain) * 
		FROM  shop_with_plan
		ORDER BY shopify_domain, amount DESC
  ),
  
  shop_with_total_clicks AS
  (
    SELECT  
	  shop_statistics.shop_domain,
	  COALESCE( SUM(shop_statistics.clicks),  0 ) AS total_click
	  
	  
    FROM shop_statistics
    INNER JOIN shop_with_plan ON shop_statistics.shop_domain = shop_with_plan.shopify_domain

    WHERE  shop_statistics.date >= shop_with_plan.commence_recur_at
      
    GROUP BY shop_statistics.shop_domain
  ),
	
  shop_with_click_remaining AS (
	SELECT 
		shopify_domain, 
		plan_name,
		plan_clicks,
		total_click,
	    (plan_clicks - total_click) AS remaining_click,
		commenced_at,
		commence_recur_at,
        cancelled_at,
        charge_id

	FROM shop_with_plan 
	INNER JOIN shop_with_total_clicks ON shop_with_total_clicks.shop_domain = shop_with_plan.shopify_domain
	)

-- SELECT * FROM shop_with_plan WHERE shopify_domain = 'coral-reef-swim.myshopify.com';
-- SELECT * FROM shop_with_plan_sanity WHERE shopify_domain = 'coral-reef-swim.myshopify.com';
-- SELECT * FROM shop_with_plan;

SELECT * FROM shop_with_click_remaining
WHERE remaining_click <= 0
ORDER BY remaining_click ASC;

shops_on_trial.sql

WITH 
shops_on_trial AS (
    SELECT shopify_domain 
    FROM shops 
    INNER JOIN charges on shops.id = charges.shop_id
    WHERE charges.processor = 'trial' AND (commenced_at <= now()) 
    AND (cancelled_at IS NULL OR cancelled_at >= now())
),

stats AS (
    SELECT
    shop_statistics.shop_domain,
    SUM(cross_sell_clicks) as clicks_count
    FROM shops_on_trial
    INNER JOIN shop_statistics ON shop_statistics.shop_domain = shops_on_trial.shopify_domain
    WHERE shop_statistics.date >= '2021-07-12T00:00:00+00:00' AND shop_statistics.date <= '2021-08-10T23:59:59+00:00'
    GROUP BY shop_statistics.shop_domain
    HAVING SUM(cross_sell_clicks) = 0
),

shops_on_trial_0_click AS (
  SELECT 
    shops.*, 
    clicks_count AS "Total clicks"

  FROM shops
  INNER JOIN stats ON shops.shopify_domain = stats.shop_domain
)

-- SELECT * FROM stats;
SELECT * FROM shops_on_trial_0_click;