6 Replies Latest reply on Feb 10, 2018 11:48 AM by Amit_Kumar

    Distinct opens and clicks

    priyankah58659963 Level 1

      Hi,

       

      Your help would be appreciated. I have the following query to display the unique opens . Unfortunately count distinct (for broadlog id)is not working. Is there any other way to achieve this.

       

      var opensQuery = xtk.queryDef.create(

            <queryDef schema="nms:trackingLogEventHisto" operation="select">

      <select >

               

                 <node expr="countdistinct(@id)" alias="@opensCount" />   

                 <node expr="[event/@journalDistributionID]" alias="@opensJD_id"/>

                 <node expr="date([event/@created])" alias="@opensDate"/>

                                                  

                </select>

      <where>

      <condition bool-operator="AND">

      <condition expr="[url/@type]='2'" />

      <condition expr="[event/@journalDistributionID] IS NOT NULL" />

      </condition>

      </where>

      <orderBy/>

                <groupBy>

      <node expr="[event/@journalDistributionID]" />

      <node expr="date([event/@created])" />        

                </groupBy>

               

                        

            </queryDef> );

       

      Regards,

      Priyanka

        • 1. Re: Distinct opens and clicks
          priyankah58659963 Level 1

          Awaiting an answer pls!

           

          Regards,

          Priyanka

          • 2. Re: Distinct opens and clicks
            Adhiyan Adobe Employee

            Hello Priyanka,

             

            If you are just looking for the unique opens for a delivery , it present as a calculated value in nms:delivery with the name : Recipients who have opened (@recipientOpen).

             

            This gets populated from the tracking tables directly so there is no need to run a complex query to get unique opens.

            1 person found this helpful
            • 3. Re: Distinct opens and clicks
              priyankah58659963 Level 1

              Hi Adhiyan,

               

              Thanks for your kind reply. But my use case is the following.

              for a particular date  and for a particular journal id ( I'm working for an paper publishing company ), I want to display the number of emails sent, no of unique clicks happened on the email, no of unique opens in the following format.

              Date

              Journal ID

              Total Opens

              Total Clicks

              Total Deliveries sent out

              05/02/18

              A123-0102

              300

              250

              1000

              05/02/18

              B678-2598

              100

              20

              200

              06/02/18

              A123-0102

              200

              160

              500

              06/02/18

              C678-9000

              50

              10

              100

              06/02/18

              Z987-5777

              60

              20

              60

              06/02/18

              B678-2598

              2000

              500

              2600

              Unable to decide on the schemas to fetch relevant data

               

              Regards,

              Priyanka

              • 4. Re: Distinct opens and clicks
                Amit_Kumar Experience Cloud MVP

                Hi Priyanka,

                 

                Schema Name: nms:deliveryLogStats

                refer to its columns for more information, write a workflow to fetch the data from this table and process the format as per your requirements.

                Tables

                 

                LabelDescription
                AmountAmount
                ItemItem
                CanceledCanceled
                Messages that were clickedMessages that were clicked
                Foreign key of the link 'Delivery' (field 'id')Foreign key of the link 'Delivery' (field 'id')
                Disabled accountDisabled account
                ErrorsErrors
                Invalid domainInvalid domain
                Inbox fullInbox full
                Number of messages to sendNumber of messages to send
                Mirror pagesMirror pages
                Not connectedNot connected
                Opened messagesOpened messages
                UnsubscriptionsUnsubscriptions
                People who clickedPeople who clicked
                PreparedPrepared
                QuarantineQuarantine
                Recipients who clickedRecipients who clicked
                Distinct opensDistinct opens
                RejectedRejected
                ComplaintsComplaints
                Number of messages sentNumber of messages sent
                DeliveriesDeliveries
                Deliveries excluding seed addressesDeliveries excluding seed addresses
                Total number of recipients who clickedTotal number of recipients who clicked
                OpensOpens
                Web pagesWeb pages
                TransactionTransaction
                User unknownUser unknown
                UnreachableUnreachable
                PendingPending
                Distinct web pagesDistinct web pages
                Email domainEmail domain

                 

                 

                 

                Regards,

                Amit

                • 5. Re: Distinct opens and clicks
                  priyankah58659963 Level 1

                  Thanks Amit for your prompt reply. But I need to know for which emails the clicks and opens happened. For this I would need broadLog id.Any clue?

                   

                  Regards,

                  Priyanka

                  • 6. Re: Distinct opens and clicks
                    Amit_Kumar Experience Cloud MVP

                    Hi Priyanka,

                     

                    nms:deliveryLogStats is an aggregate built on top of all deliveries sent from Adobe campaign, you can use this to query per delivery basis or more than one delivery basis."Deliveries" Link from "nms:deliveryLogStats" mentioned above, will be used for filtering for your purpose.

                    You can use this link for select reports for one or many campaigns, one or many deliveries.

                    Read more about this aggregate here:

                    Indicator calculation

                     

                    Regards,

                    Amit

                    1 person found this helpful