Generic System Error when I use a Left Join

Question

I'm creating an automation that uses a series of SQL queries to identify people who were potentially targeted for a dynamic content block in an email, determine if they were included in a job that uses that content block, and then counts that segment's total and unique opens and clicks.

Since my use case targeted people in the Nashville area, my first query selects those people and stores them in a data extension called "Geo-Location" (I can't just query my larger member table because there are 2 million records and the query times out). This part runs without issues.

Select AccountID, DMAID from Members
where DMAID = 'Nashville'

Next step is a query that joins in the _send data view and checks to see if the people in Geo-Location were sent a list of Job IDs. While I'm at it, I count the total sends. This uses an overwrite data action and stores the data in a new data extension called "DCA". This query also runs without issues.

Select count(m.AccountID) as Sent, m.DMAID, s.JobID, j.EmailName 
from [Geo-Location] m
join _sent s on m.AccountID = s.SubscriberKey
join _job j on s.JobID = j.JobID
where s.JobID in (136979,136978,136993,136994,130530,130510,134162,134605,134598,136918,136911)
group by s.JobID, m.DMAID, j.EmailName

The trouble comes with this next step. I'm just going back to the "Geo-Location" data extension, joining in the opens, and counting them. I only want to count the opens for this subset of people, not for the whole job, so I've used an Update data action combined with a left join, but I don't think I'm using the left join correctly.

Select o.JobID, count(m.AccountID) as TOpens 
from [Geo-Location] m
left join _Open o on m.AccountID = o.SubscriberKey
group by o.JobID

On this step, I get the generic "Automation failed due to system error". Thing is, the TOpens field is populated after the query fails. I'm just worried that the rest of my queries in the automation won't run after this one fails.

Sure, I could just use an inner join and a "where" clause in my third query to isolate the job IDs again, but I'm writing this for reuse by someone who isn't comfortable with SQL, and the chances for errors go up if he has to replace the Job IDs in multiple queries. What's the best way for me to get around this?

Answer 1

I'm still not sure why the error happens, but I found a solution that doesn't require left join.

First I used a query to gather the members in the correct location that were sent the emails:

Select m.AccountID, m.DMAID, s.JobID from members m
join _Sent s on s.SubscriberKey = m.AccountID
where m.DMAID = 'Nashville' and s.JobID in (136979,136978,136993,136994,130530,130510,134162,134605,134598,136918,136911)

Then I can use a "where" clause to make sure that I'm only selecting sends/opens/clicks from jobIDs that match the jobIDs in the Geo-Location table I created.

Select count(s.SubscriberKey) as Sent, g.DMAID, g.JobID from [Geo-Location] g
join _sent s on g.AccountID = s.SubscriberKey
where g.JobID = s.JobID
group by g.JobID, g.DMAID