Help with Field Filters on a query with subqueries

1

Hello everybody.
I’m new in Metabase and until now I had no problems with date filters on dashboards using the Field Filter variables.

Now I have a query with some subqueries, that is working perfectly without the variables, but I don’t know how I can to traslate to variables for Metabase, because there different places and types of date filters that are needed in order to filter the entire query. Can somebody help me with that?

This is the base query:
SELECT s.name AS source, c.name AS campaign, IFNULL(t.conversions, 0) AS conversions, CONCAT('$', IFNULL(FORMAT(sp.spent, 2), 0)) AS spent, CONCAT('$', IFNULL(FORMAT(SUM(p.revenue), 2), 0)) AS revenue, CONCAT('$', IFNULL(FORMAT(sp.spent/t.conversions, 2), 0)) AS eCPA, IFNULL(a.cancels, 0) AS cancels, CONCAT('$', IFNULL(FORMAT(p.revenue/a.cancels, 2), 0)) AS LTV FROM campaigns c INNER JOIN sources s ON s.id=c.source LEFT JOIN ( SELECT campaign, SUM(attribution_first) AS conversions FROM touches WHERE attribution_first=1 AND conversion='payment' AND (date BETWEEN '2017-01-01 00:00:00' AND '2017-02-01 00:00:00') GROUP BY campaign ) AS t ON t.campaign=c.id LEFT JOIN (SELECT campaign, SUM(amount) AS spent FROM spending WHERE (day BETWEEN '2017-01-01 00:00:00' AND '2017-02-01 00:00:00') GROUP BY campaign) AS sp ON sp.campaign=c.id LEFT JOIN ( SELECT t.campaign AS campaign, SUM(p.amount) AS revenue FROM touches t, payment p, client c WHERE t.attribution_first=1 AND t.conversion='payment' AND c.stripe_customer_id=p.customer_id AND t.account=c.id AND (t.date BETWEEN '2017-01-01 00:00:00' AND '2017-02-01 00:00:00') AND (p.created BETWEEN '2017-01-01 00:00:00' AND '2017-02-01 00:00:00') GROUP BY t.campaign ) AS p ON p.campaign=c.id LEFT JOIN ( SELECT t.campaign, COUNT(a.id) AS cancels FROM accounts a, touches t WHERE t.account=a.id AND a.status='cancel' AND t.attribution_first=1 AND t.conversion='payment' AND (t.date BETWEEN '2017-01-01 00:00:00' AND '2017-02-01 00:00:00') GROUP BY campaign ) AS a ON a.campaign=c.id WHERE (t.conversions>0 OR sp.spent>0) GROUP BY c.id;

Many thanks in advance!

  • David
2

Though I might not have understood what you need exactly.
Correct me if I am wrong… is this what you needed?
Just make sure that date_from & date_to Variable Type field is set to Date.

s.name AS source, 
c.name AS campaign, 
IFNULL(t.conversions, 0) AS conversions, 
CONCAT('$', IFNULL(FORMAT(sp.spent, 2), 0)) AS spent, 
CONCAT('$', IFNULL(FORMAT(SUM(p.revenue), 2), 0)) AS revenue, 
CONCAT('$', IFNULL(FORMAT(sp.spent/t.conversions, 2), 0)) AS eCPA, 
IFNULL(a.cancels, 0) AS cancels, 
CONCAT('$', IFNULL(FORMAT(p.revenue/a.cancels, 2), 0)) AS LTV 
FROM campaigns c 
INNER JOIN sources s ON s.id=c.source 
LEFT JOIN ( 
SELECT campaign, SUM(attribution_first) AS conversions 
FROM touches 
WHERE attribution_first=1 AND conversion='payment' 
AND (date BETWEEN {{date_from}} AND {date_to}} 
) GROUP BY campaign ) AS t ON t.campaign=c.id 
LEFT JOIN (
SELECT campaign, SUM(amount) AS spent 
FROM spending 
WHERE (day BETWEEN {{date_from}} AND {{date_to}}) 
GROUP BY campaign) AS sp 
ON sp.campaign=c.id 
LEFT JOIN ( 
SELECT t.campaign AS campaign, SUM(p.amount) AS revenue 
FROM touches t, payment p, client c 
WHERE t.attribution_first=1 AND t.conversion='payment' 
AND c.stripe_customer_id=p.customer_id AND t.account=c.id 
AND (t.date BETWEEN {{date_from}} AND {{date_to}}) 
AND (p.created BETWEEN {{date_from}} AND {{date_to}}) 
GROUP BY t.campaign ) AS p 
ON p.campaign=c.id 
 LEFT JOIN ( SELECT t.campaign, COUNT(a.id) AS cancels 
FROM accounts a, touches t 
WHERE t.account=a.id AND a.status='cancel' 
AND t.attribution_first=1 AND t.conversion='payment' 
AND (t.date BETWEEN {{date_from}} AND {{date_to}}) 
GROUP BY campaign ) AS a ON a.campaign=c.id
WHERE (t.conversions>0 OR sp.spent>0)GROUP BY c.id;```