Question

Photo of Jeremy Turgeon

0

Unopened Emails Data View

2

I'm trying to create a data view that shows people who haven't opened a weekly email for the last 4 weeks. Then, I need to send a communication to those people.


This first problem is that I can create a data view (using the Communication Recipient) and format it with a report, but can't actually communicate or bulk update. The second is that I can't figure out how to combine data views to display only people who didn't open any of the emails but did receive them.

  • Photo of Michael Garrison

    1

     OK, with the understanding that this is a "one-off" process for you, rather than something you'll run regularly, we can hard-code in the communications ids. Unfortunately my SQL is inelegant, but for a one-time process, it should do the trick for you:

    SELECT DISTINCT
    p.[Id],
    p.[NickName] + ' ' + p.[LastName] "Name"
FROM [CommunicationRecipient] cr
    LEFT JOIN [PersonAlias] pa ON pa.[Id]=cr.[PersonAliasId]
    LEFT JOIN [Person] p ON pa.[PersonId]=p.[Id]
WHERE
        cr.[PersonAliasId] IN (SELECT [PersonAliasId] FROM [CommunicationRecipient] WHERE [CommunicationId]=7500 AND [Status]=1)
    AND cr.[PersonAliasId] IN (SELECT [PersonAliasId] FROM [CommunicationRecipient] WHERE [CommunicationId]=7600 AND [Status]=1)
    AND cr.[PersonAliasId] IN (SELECT [PersonAliasId] FROM [CommunicationRecipient] WHERE [CommunicationId]=7700 AND [Status]=1)
    AND cr.[PersonAliasId] IN (SELECT [PersonAliasId] FROM [CommunicationRecipient] WHERE [CommunicationId]=7800 AND [Status]=1)
GROUP BY p.[Id],p.[FirstName] + ' ' + p.[LastName]

     Use that as the Query in a DynamicData block and be sure to tell it that it's a person report and provide the `Id` column so it hooks up the communications buttons properly. Then you should be able to bulk update and/or communicate with the list of people.

     Let me know how that goes!

  • Photo of Michael Garrison

    0

     Jeremy, you can't bulk update or communicate from that DataView or report because it's a list of communications, not a list of people. You can add a Lava type column to your report with `<a href="/Person/{{ PersonAliasId }}" class="btn btn-default"><i class="fa fa-user"></i></a>` that will let you go to the profile of the person that communication went to, but that's rather more manual than I think you're looking for.

     

     I feel like this will be possible with other means once v6 comes out, but for now I think you may be looking at relying on a DynamicData block, rather than a report.

     

    Depending on exactly how you set up your DataView, you might be able to use something like this for your DynamicData query:

    SELECT * FROM (
    SELECT
        p.[Id],
        p.[FirstName] + ' ' + p.[LastName] "Name",
        count(*) "Unopened"
    FROM [CommunicationRecipient] cr
        LEFT JOIN [PersonAlias] pa ON pa.[Id]=cr.[PersonAliasId]
        LEFT JOIN [Person] p ON pa.[PersonId]=p.[Id]
    WHERE
        cr.[OpenedDateTime] IS NULL
    GROUP BY p.[Id],p.[FirstName] + ' ' + p.[LastName]
) x WHERE x.[Unopened] > 4

    In the DynamicData block, you can tell it that it's a person report and that the link should be based on the [Id] column, thus being able to enable such actions on the output as "Merge, Communicate and bulk Update".

     

    Give that a shot and let me know if we're on the right path- we'll get you the rest of the way ;-) 

    • Jeremy Turgeon

      Jeremy Turgeon

      I see what you mean, though my understanding of SQL is limited. What I'm trying to pull out of the data is specific communications and the people who haven't opened any but have received all of them.
      Something like:
      SELECT *
      FROM [CommunicationRecipient]
      WHERE
      [CommunicationId] = '7500'
      OR
      [CommunicationId] = '7600'
      OR
      [CommunicationId] = '7700'
      OR
      [CommunicationId] = '7800'
      AND
      [OpenedDateTime] IS NULL


      Plus the fancy left joining ;)

      • Michael Garrison

        Michael Garrison

        hmmmm, so you want your report to give you a list of recent communications so you can indicate which messages you want to check to see who didn't open any?

        • Jeremy Turgeon

          Jeremy Turgeon

          What you posted earlier was much closer to the mark. I was just trying to illustrate the concept. A list of people who received several specific communications and didn't open them. The end goal being to communicate with said people.