Expand my Community achievements bar.

We are excited to introduce our latest innovation to enhance the Adobe Campaign user experience — the Adobe Campaign v8 Web User Interface!
SOLVED

Distinct opens and clicks

Avatar

Level 4

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 Accepted Solution

Avatar

Correct answer by
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.

View solution in original post

6 Replies

Avatar

Level 4

Awaiting an answer pls!

Regards,

Priyanka

Avatar

Correct answer by
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.

Avatar

Level 4

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

Avatar

Level 10

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

Avatar

Level 4

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

Avatar

Level 10

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