Hello,
i Have this query that i want to improve
| loadjob savedsearch="myquery"
| where (strftime(_time, "%Y-%m-%d") = "2020-02-27")
| stats dc(eval(if(STEP="Sent",ID_MESSAGE,NULL))) AS sent,...
See more...
Hello,
i Have this query that i want to improve
| loadjob savedsearch="myquery"
| where (strftime(_time, "%Y-%m-%d") = "2020-02-27")
| stats dc(eval(if(STEP="Sent",ID_MESSAGE,NULL))) AS sent,
dc(eval(if(STEP="RME",ID_MESSAGE,NULL))) AS RME by ID_MESSAGE
| join type=left ID_MESSAGE [| loadjob savedsearch="myquery"
| where (strftime(_time, "%Y-%m-%d") >= "2020-02-27") AND (strftime(_time, "%Y-%m-%d") <= "2020-03-03")
|stats dc(eval(if(STEP="Click",ID_MESSAGE,NULL))) AS click by ID_MESSAGE]
|where sent>0
|stats sum(sent) AS sent,sum(RME) AS RME,sum(click) AS click
This is the result that i have today
And this is the result that i want to have
knowing that RME and click are calculated on the ID_MESSAGE sent