Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Cees Booister 18 posts 158 karma points
    Feb 02, 2023 @ 12:40
    Cees Booister
    0

    Newsletter Studio

    I'd like to export the Newsletter Studio statistics, so subscribers, newsletters bounced, who clicked on an article of the newsletter. I searched the forum and it should be in the database according to a forum article from 2016. The link in that response is not working.

    Could anyone explain me or post a link to information about the tables Newsletter Studio is using or if there is an export function in the tool itself?

  • Markus Johansson 1924 posts 5831 karma points MVP 2x c-trib
    Feb 02, 2023 @ 12:45
    Markus Johansson
    0

    Hi!

    There is no built-in feature to export stats at the moment but, all the data in available in the DB if one wants to pull it out from there.

    What version of the package are you running on?

  • Cees Booister 18 posts 158 karma points
    Feb 02, 2023 @ 13:00
    Cees Booister
    0

    We use the Umbraco 10 version. By the way, we're still in testing phase and not yet in PROD.

  • Markus Johansson 1924 posts 5831 karma points MVP 2x c-trib
    Feb 02, 2023 @ 23:09
    Markus Johansson
    100

    Edit:

    I went ahead and published a complete overview of the tables here: https://www.newsletterstudio.org/documentation/package/10.0.0/develop/database-tables/


    Hi!

    Thanks for letting me know what version you're on, I'll try to outline some information about relevant tables in the database here. We do not see the database design as a contract so we might add or change tables/columns in future releases. However, we always try to "append only".

    First of all, most tables have a column for WorkspaceKey this is used when there are multiple Workspaces in use. These are stored in the nsWorkspace table. User access to workspaces are stored in nsWorkspaceAccess.

    nsMailingList

    Contains the built-in mailing lists for the package.

    nsRecipient

    Stores recipients that are added to any of the built-in mailing lists. If you're sending to Umbraco Members this table does not hold any data about them.

    nsSubscription

    A relation table between nsMailingList and nsRecipient, holds information about which recipient is subscribed (or unsubscribed) from which list.

    nsCampaignEmail

    This table contains all campaigns created. The status field indicates if it is a draft, if it sending is in progress, or if it's sent.

    Statuses:

    • 0 = Created / Draft
    • 1 = Initializing (send out engine is creating nsTrackingCampaignEmail-rows for the campaign)
    • 2 = Sending
    • 3 = Sent
    • 4 = Error

    nsTrackingCampaignEmail

    This table acts as a queue when sending a campaign email and also as a log for statistic reports. It will contain unique recipients for the campaign and any bounce information for that recipient.

    Be aware that it's technically possible that one recipient (unique email) is represented in multiple "Recipient List Providers" used when sending the email. The send-out engine will make sure that only one unique recipient for each campaign is stored here.

    Statuses:

    • 0 = Available (sending not started to this recipient)
    • 1 = InProgress
    • 2 = Error
    • 3 = Sent

    nsTrackingCampaignEmailInteraction

    This table stores any interactions from the recipient like open & click. It also stores a URL when applicable (e.g. which URL was clicked).

    Tracking Types:

    • 1 = Open
    • 2 = Click
    • 3 = Unsubscribe Click
    • 4 = Unsubscribe Completed
    • 5 = View in browser

    Transactional

    The tables nsTransactionalEmail, nsTrackingTransactional, nsTrackingTransactionalEmail, and nsTrackingTransactionalInteraction follow the same pattern as the tables for campaigns.

    Feel free to get back if you have any questions about this.

  • Cees Booister 18 posts 158 karma points
    Feb 03, 2023 @ 08:00
    Cees Booister
    0

    Thank you very much. We'll have a look and will let you know if we have other questions. But I think we can work with this.

Please Sign in or register to post replies

Write your reply to:

Draft