Developer forum

Forum » CMS - Standard features » Tracking Abandoned Cart campaigns

Tracking Abandoned Cart campaigns

E-mail notifications
Nuno Aguiar Dynamicweb Employee
Nuno Aguiar
Reply
Posted on 28/01/2018 17:42:15

Hi,

 

I know I might have all of the data, but I am missing the connection between Submitting an Abandoned Carts campaign and analyzing the results in terms of purchased orders. Can anybody shed some light on this?

  • I know how to set up the Marketing emails
  • I know I can set up and smart search to find users who received it and purchashed a product where Last Order date is X

 

What I don't know is:

  • How to dynamically tie that date to the email date they received
    And that would only give me users, not the actual orders
  • How could I get the orders that were placed in a session that started from an email marketing campaign?
    (I guess that ends up being the right question)

 

Best Regards,

Nuno Aguiar


Replies

 
Nuno Aguiar Dynamicweb Employee
Nuno Aguiar
Posted on 04/04/2018 16:59:19
Reply

Hi,

 

Any thoughts on this?

 
Nuno Aguiar Dynamicweb Employee
Nuno Aguiar
Posted on 06/04/2018 19:00:35
Reply

Hi,

 

Let me ask for something slightly different. Can anyone help me query the DW stats to:

  • Get a list of users who
    • Received a specific newsletter (Abandoned Carts)
    • Purchased something after receiving the newsletter
      VS
      Simply received the newsletter

 

I currently don't know how to extract that data, and if I can get a that data, we can calculate the effectiveness of the Abandoned Carts "campaign"

 

If I could simply use the Smart Search rules setting a date dynamically associated to the date the user received an Marketing email I'd be home-free. Any idea if /how I can do this?

https://www.screencast.com/t/xuhdpd7VEFzi

 

Best Regards,

Nuno Aguiar

 
Nicolai Pedersen
Posted on 10/04/2018 08:44:30
Reply

You cannot use a date related to the mail was send. You would have to write code to find that date.

You would have to look in the database to find the right information. Order, EmailRecipient, EmailMessage and EmailAction could be joined and provide the right information.

BR Nicolai

 
Nuno Aguiar Dynamicweb Employee
Nuno Aguiar
Posted on 10/04/2018 13:48:24
Reply

Hi Nicolai,

 

Pointing to the DB tables helped. Thank you.

 

Nuno Aguiar

 
Nicolai Pedersen
Posted on 10/04/2018 19:49:57
Reply

Great.

If you create a brilliant SQL please feel free to share it.

BR Nicolai

 
Nuno Aguiar Dynamicweb Employee
Nuno Aguiar
Posted on 11/04/2018 11:46:08
Reply

Hi Nicolai,

 

I came up with this

SELECT * FROM EcomOrders o
WHERE o.OrderVisitorSessionID IN (
  SELECT ActionSessionId FROM EmailAction a
    WHERE
      a.ActionMessageId IN 
    (SELECT MessageId FROM EmailMessage m WHERE m.MessageRecipientsSource = 'Dynamicweb.Modules.EmailMarketing.RecipientProviders.AbandonedCartRecipientProvider')
      AND
      a.ActionType != 'EmailMarketing_Unsubscribe'
  )
AND o.OrderComplete = 'true'
AND o.OrderDeleted = 'false'
AND o.OrderCart = 'false'
 
 
It does not cover all we need for our two customers, but it's all about counting orders vs messages sent and making the messageID a selectable parameter. 
 
So essentially we tied the action's session ID with the Order's session ID and excluded the unsubscriptions from the newsletter.
 
Let me know if you find anything odd or missing from this logic.
 
Best Regards,
Nuno Aguiar

 

You must be logged in to post in the forum