1

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?

  • I'd check the data types and sizes of the target data extension. Is JobID a number? How about TOpens? – Adam Spriggs Jan 21 at 20:05
  • 1
    Also, subscribers can have multiple opens per send job, so the target DE's primary key should be able to handle that scenario. May be worth it to have a where clause specifying o.isunique = 1. – Adam Spriggs Jan 21 at 20:08
  • I recreated the fields and made sure to set my data types and lengths correctly, but the error still happens. Also, in this case I'm counting total opens. Once I get this working, an additional query will count unique opens. – K K Jan 21 at 20:39
  • What are the primary keys on your target DE? – Adam Spriggs Jan 21 at 22:19
  • There is no primary key in the Geo-Location DE. The primary key in DCA is the JobID – K K Jan 22 at 15:01
0

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

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.