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.

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

1 Answer 1

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)
5
  • 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... Mar 9, 2018 at 21:49
  • Is it a trigger? Mar 9, 2018 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. Mar 9, 2018 at 21:58
  • Updated my answer. Mar 9, 2018 at 22:30
  • Thanks @Adam, I did use isUnique for another query. I need total and unique. Mar 10, 2018 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.