0
select
d.envios,
x.aberturas,
d.data_envio,
d.nome_email,
d.jobid,
pct_abertura = cast((x.aberturas*100/d.envios) as decimal(5,2))


from DE_BK_CV_DASHBOARD_JORNADA_FOPA d 

left join (
        select
        count(o.subscriberkey) as aberturas,
        cast(o.EventDate as date) as Data_envio,
        j.EmailName as Nome_Email,
        j.jobid
        
        from _Job j
        join _Open o on o.jobid = j.jobid
        Join _JourneyActivity ja on o.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID and lower(ja.activitytype) like '%email%'
        join _Journey jn on ja.VersionID = jn.VersionID and jn.JourneyName like 'JB_BK_CV_Fopa_PrimeiroAcesso'
        where o.isunique = 'true'
        
        group by cast(o.EventDate as date), j.EmailName,j.jobid
    ) x on x.Data_envio = d.data_envio and d.nome_email = x.nome_email

My automation is still getting the not unique opens, did I do something wrong?

1 Answer 1

1

A few things:

  • Underscores are wildcards in LIKE conditionals in T-SQL. You'll need to escape those with square brackets.
  • lower() isn't needed as the underlying SQL Server database is case-insensitive
  • isUnique is a Boolean data type, which is a bit. It can be represented with a 1 for true or 0 for false.
select
  d.envios
, x.aberturas
, d.data_envio
, d.nome_email
, d.jobid
, pct_abertura = cast((x.aberturas*100/d.envios) as decimal(5,2))
from DE_BK_CV_DASHBOARD_JORNADA_FOPA d 
left join (

  select
    count(o.subscriberkey) as aberturas
  , cast(o.EventDate as date) as Data_envio
  , j.EmailName as Nome_Email
  , j.jobid
  from _Job j
  inner join _Open o on o.jobid = j.jobid
  inner Join _JourneyActivity ja on o.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID  
  inner join _Journey jn on ja.VersionID = jn.VersionID 
  where o.isunique = 1
  and ja.activitytype like '%email%'
  and jn.JourneyName like '%JB[_]BK[_]CV[_]Fopa[_]PrimeiroAcesso%'
  group by cast(o.EventDate as date), j.EmailName, j.jobid

) x on x.Data_envio = d.data_envio and d.nome_email = x.nome_email

If your looking to return values from a specific JourneyName, you many not need the like at all.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.