0

I have a very simple query

SELECT o.*,sl.Market,sl.Category 
FROM _Open o WITH (NOLOCK)
INNER JOIN SendLog sl WITH (NOLOCK)
ON o.JobID = sl.JobID
WHERE o.JobID IN (3103648)

Which is trying to get records from the _Open table and join them with the SendLog table to get Market and Category.

My Data Extension has 3 columns JobID, Market and Category.

The query above was more complex, but I stripped it down to the fundamentals, bare bones.

Yet that query is giving me 29+ million rows on a send that was only about 7k. I even filtered off of the SendLog for the Category column for the value from last night's send, and that yielded 29 million rows. So something is off. It seems like it's duplicating rows or something. My query seems correct, so I'm not sure what the deal is. For the record, I have NEVER had success querying the SendLog, I always have issues.

  • It sounds like JobID isn't the right join to use; you're basically performing a multiplicative join. – sfdcfox Mar 9 '18 at 20:35
  • Is the JobID for a triggered email? JobIDs are handled differently for those. – Adam Spriggs Mar 9 '18 at 20:40
  • @sfdcfox that is a good point, may be what's happening, I'll see if I can try something else – garek007 Mar 9 '18 at 21:49
  • @sfdcfox I used SubscriberID and that did the trick. thanks – garek007 Mar 9 '18 at 21:58
0

Opens are not 1:1 with Sends. Your subscribers can and typically do open an email multiple times after a send. Each open gets recorded when the tracking pixel is displayed.

I'd suggest restricting it with the unique flag:

select 
o.*
, sl.market
, sl.category 
from _Open o
inner join Sendlog sl on o.jobid = sl.jobid
where o.isunique = 1 
and o.jobid in (3103648)

Update

I get it now. Here's a query that should restrict the joins to common subscribers. I still think the isUnique = 1 is important:

select 
o.*
, sl.market
, sl.category 
from _Open o
inner join Sendlog sl on o.jobid = sl.jobid and o.subscriberid = sl.subid
where o.isunique = 1 
and o.jobid in (3103648)
  • Hi @Adam, I understand that, but this is not just a little off, it's off by millions. I don't think that IsUnique will fix it... – garek007 Mar 9 '18 at 21:49
  • Is it a trigger? – Adam Spriggs Mar 9 '18 at 21:51
  • No it's a manual, @sfdcfox's suggestion did the trick, I had to join on SubscriberID, although I'm still off by 200, but I can live with that and get closer I think. – garek007 Mar 9 '18 at 21:58
  • Updated my answer. – Adam Spriggs Mar 9 '18 at 22:30
  • Thanks @Adam, I did use isUnique for another query. I need total and unique. – garek007 Mar 10 '18 at 13:02

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.