Distinct opens and clicks

priyankah586599

08-02-2018

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

Accepted Solutions (1)

Accepted Solutions (1)

Adhiyan

Employee

09-02-2018

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.

Answers (5)

Answers (5)

Amit_Kumar

MVP

10-02-2018

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

priyankah586599

10-02-2018

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

Amit_Kumar

MVP

09-02-2018

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

priyankah586599

09-02-2018

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