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?