Distinct opens and clicks

[#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

[#2 · Accepted solution]

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.

[#3]

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

[#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

[#5]

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

• NmsDeliveryLogStats

Label	Description
Amount	Amount
Item	Item
Canceled	Canceled
Messages that were clicked	Messages that were clicked
Foreign key of the link 'Delivery' (field 'id')	Foreign key of the link 'Delivery' (field 'id')
Disabled account	Disabled account
Errors	Errors
Invalid domain	Invalid domain
Inbox full	Inbox full
Number of messages to send	Number of messages to send
Mirror pages	Mirror pages
Not connected	Not connected
Opened messages	Opened messages
Unsubscriptions	Unsubscriptions
People who clicked	People who clicked
Prepared	Prepared
Quarantine	Quarantine
Recipients who clicked	Recipients who clicked
Distinct opens	Distinct opens
Rejected	Rejected
Complaints	Complaints
Number of messages sent	Number of messages sent
Deliveries	Deliveries
Deliveries excluding seed addresses	Deliveries excluding seed addresses
Total number of recipients who clicked	Total number of recipients who clicked
Opens	Opens
Web pages	Web pages
Transaction	Transaction
User unknown	User unknown
Unreachable	Unreachable
Pending	Pending
Distinct web pages	Distinct web pages
Email domain	Email domain

Regards,

Amit

[#6]

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

[#7]

Awaiting an answer pls!

Regards,

Priyanka