Correct Arthur, those and the JourneyActivity data views could have been directly joined. I chose to use a sub query for the _sent count(*) group by and thought it would be easier for non-SQL proficient users if I kept the sub query method consistent for all of the joins. I’m planning to record an intro to SQL video in the future 🙂
Well spotted Adam! I also forgot to add local to system time considerations (for those wanting local time midnight-to-midnight). I think working with dates in SQL needs to move up my videos to make list!
All you need is the JourneyID or VersionID to be added as a WHERE clause in the SQL Statement. You could also use the "JourneyName" value if you think the journey may get updated or republished.
Hi! Is there a way to also get the push notification and sms used inside a journey?
I noticed this also pulls in Single Send Journeys. Is there a way to exclude Single Send Journeys?
Great video, thanks! About the query, why are you using sub queries for _job and _journey instead of just joining the tables themselves?
Correct Arthur, those and the JourneyActivity data views could have been directly joined. I chose to use a sub query for the _sent count(*) group by and thought it would be easier for non-SQL proficient users if I kept the sub query method consistent for all of the joins.
I’m planning to record an intro to SQL video in the future 🙂
I don't think the eventdate is working for me in this SQL. I'm returning records much older than 90 days.
If I want to pull all EMAIL ADDRESSES for my journeys how can I add that to this code?
Helpful thanks
Is it possible you to create video on Automation start and stop using SSJS? Which is helpful because not having any others solutions for that. Thanks
Interesting! Let me see what I can do!
@@CameronRobert Thanks 👍
Using eventDate >= convert(date, getDate()-90) would properly respect the time portion of the eventDate dateTime data type.
Well spotted Adam! I also forgot to add local to system time considerations (for those wanting local time midnight-to-midnight).
I think working with dates in SQL needs to move up my videos to make list!
How to write this query for selected journey
All you need is the JourneyID or VersionID to be added as a WHERE clause in the SQL Statement. You could also use the "JourneyName" value if you think the journey may get updated or republished.