Email Marketing - Newsletter failing

Imar Spaanjaars

Posted on 09/04/2015 08:31:55

Hi there,

Last night we sent out an e-mail to 75+K users. When I view the log, I can see the email failed as the status is failed. It also says Total sent: 75238 / 75240. So two e-mails failed somehow. This in itself is not too bad but it seems that when sending fails, Dynamicweb doesn't correctly start tracking statistics because lists like Recipients and Engagement are empty. When I view the e-mail I see the error: "Conversion failed when converting the nvarchar value '21219>' to data type int."

This leads to a whole bunch of questions:

1. Where can I find more info / logs about what happened?

2. Is there a way I can restart / regenerate the statistics now that this has failed?

3. Shouldn't Dynamicweb just continue when it fails on a single user and still register stats etc.?

4. Any idea why this error came up? 21219 could be a user ID, but I can't find a > anywhere on the user's record in the database.

Thanks for any insights into this.

Imar

Replies

Jeppe Eriksson Agger

Posted on 09/04/2015 10:05:43

This post has been marked as an answer

Hi Imar,

To answer your questions in order:

This information can be found in the database currently, though we have a backlog item to make this information available in the UI. You can check the EmailRecipient table using the RecipientMessageId and RecipientSentTime columns to filter the correct rows. In your case, you can use this SQL query: SELECT * FROM EmailRecipient WHERE RecipientMessageId = 7 AND RecipientSentTime IS NULL
Statistics should still be gathered even though you can't see all of it on the Statistics page. You can see that the Highlights sections contains summary statistics data.
It does continue. All recipients are handled even if the first recipient fails.
We have previously had issues where junk data got into the database and causing this type of exception. I have never seen the ">" character appear before though, but the most likely place for this is the OMCLinkClick table, LinkClickClickerKey column. Try this query: SELECT * FROM OMCLinkClick WHERE LinkClickClickerKey LIKE '%>'

Like I mentioned, issue 4 is related to a previously identified bug, which we thought was fixed. This seems not to be the case, and we'll look into it again. Removing the ">" from the two rows should make that exception go away, and then it should be possible to retry the failed recipients, which apparently was caused by a "Bad gateway" according to SendGrid.

I hope this provides some of the sought after insights!

- Jeppe

Votes for this answer: 1

Imar Spaanjaars

Posted on 09/04/2015 10:50:59

Hi Jeppe,

Thank you for your fast and detailed answer. That helped a lot.

I did indeed find the > character in the LinkClickClickerKey. I removed it for the two records and then tried it again It found another one where the ID was followed by a / (47406/). Once I removed that, the errors went away and the statistics page started showing the list of recipients and engagement.

>> Statistics should still be gathered even though you can't see all of it on the Statistics page.

Yep, I see that now. Thanks. It appeared as stats were unavailable but I now see that just the stats display trips over the junk characters.

We intend to upgrade to version 8.6 soon as the site still runs on an older 8.5 installation. We'll see if that prevents the problem from appearing in the first place.

>> and then it should be possible to retry the failed recipients

I assume that when I click "This email failed to be sent correctly. Click here to retry." it'll only send out to the two failed recipients, correct?

Thanks again!

Imar

Jeppe Eriksson Agger

Posted on 09/04/2015 10:58:56

>> I assume that when I click "This email failed to be sent correctly. Click here to retry." it'll only send out to the two failed recipients, correct?

Indeed.

Imar Spaanjaars

Posted on 09/04/2015 11:13:54

Thanks for confirming. Always scary to work with these large recipients lists ;-)

Imar Spaanjaars

Posted on 10/04/2015 08:45:03

Hi Jeppe,

One more question: it seems that no orders are tracked for any of the sent newsletters. They sent out 75K e-mails and saw an increase in orders for the day, yet the engagement index for orders is 0. While it's technically possible that none of the subscribers bought something and the increase in orders came from other visitors to the site, this is highly unlikely. Is there a way to validate how the orders and newsletters are linked / tracked? I'd like to investigate the issue but don't know where to start.

Thanks,

Imar

Imar Spaanjaars

Posted on 10/04/2015 08:49:47

BTW, for anyone running into this issue: I scheduled a task that runs every few minutes with the following SQL to clean up the data:

UPDATE OMCLinkClick SET LinkClickClickerKey = REPLACE(LinkClickClickerKey, '>', '') WHERE LinkClickClickerKey Like  '%>%'
UPDATE OMCLinkClick SET LinkClickClickerKey = REPLACE(LinkClickClickerKey, '/', '') WHERE LinkClickClickerKey Like  '%/%'
UPDATE OMCLinkClick SET LinkClickClickerKey = REPLACE(LinkClickClickerKey, ' ', '') WHERE LinkClickClickerKey Like  '% %'
UPDATE OMCLinkClick SET LinkClickClickerKey = REPLACE(LinkClickClickerKey, ' ', '') WHERE LinkClickClickerKey Like  '% %'

Note: the last item does not replace a space or tab but some other white space character.

Imar

Jeppe Eriksson Agger

Posted on 13/04/2015 11:26:54

Hi Imar,

I see the issue with order tracking. To my knowledge, we haven't changed anything here, but you can test it by sending an email to yourself, clicking a link and -- in the same session -- placing an order. If this order does not appear in the Statistics overview, then there is an issue. I'll have our QA department test this as well.

- Jeppe

Christian Rud Skovgaard

Posted on 14/04/2015 09:35:29

Hi Imar,

It seems that the order tracking problem is not a general issue, so we will need to do an investigation of the site in question. I remember there also were some problems with one of the ecommerce statistic nodes (frequently bought items). There might be some underlying issues with statistics on the site.

Kind regards

Christian

Imar Spaanjaars

Posted on 14/04/2015 10:52:05

Hi Christian,

Ok, that's no problem. How do you want to proceed? Do you need a copy of the site?

Imar

Christian Rud Skovgaard

Posted on 14/04/2015 11:02:09

Hi Imar,

Yes, please contact service desk with a link to this thread. And we will start an investigation on the copy of the site.

Christian

Imar Spaanjaars

Posted on 13/05/2015 23:36:03

Hi Christian,

I submitted a case and it was closed as everything seemed to look good. However, today we sent another 70K+ users an e-mail. One product that is represented in the newsletter is showing an order rate of 0% and there's no order value on any customer but the sales for that product has gone up from 19 orders to 47 in the hour after sending it out.

That's too much for a coincidence where no one from the newsletter buys something but random other visitors do.

I'll reopen the case so hopefully you can look into this.

Imar

You must be logged in to post in the forum

Developer forum

Email Marketing - Newsletter failing

Replies