MemberJunction / MemberJunction/MJ

Pull back Messaging Delivery/Open/Click Data into MJ

Open
#527 1 comment 0 reactions 2 assignees Claimed by @JS-BC View on GitHub
Dominant language
TSQL
Stars
29
Forks
6
Avg merge
2d 1h
Merged PRs (30d)
323

Description

* Create table(s) in MJ to store this data - see below
* Make sure that you add ms_descriptions for these new tables. Take this entire issue into ChatGPT and ask it to generate the `CREATE TABLE` sql and the ms_description sproc calls.
* Flag in provider table to determine if it supports event retrieval or not.
* Add new abstract method to the `BaseProvider` called `RetrieveEvents` that can be called anytime and will take in a `ProviderMessageID` string that the provider will use to get events. Alternatively we might see if we can do this by date range instead which might be more efficient (probably)
* Implement the new abstract methods in our existing providers
* Implement calling the providers generically in the `Engine` and store the results in the new tables, noted below
------------------------------------------------------------------------------------

We have two relevant tables in the database right now `CommunicationRun` and `CommunicationLog` will use `Run` and `Log` for short here to make it simpler.

We will add a new table called `CommunicationLogEvent` which will store "events" that are available from the provider such as `delivered`, `opened`, and `click` among others that we might receive. Providers of various types might have different events. Some won't support all of these events, some will support other kinds. We have a database schema to support this as noted here.

Fields in the `CommunicationLogEvent` table will be:
- `ID`
- `CommunicationLogID` - fkey to `CommunicationLog.ID`
- `TypeID` - fkey to below table `CommunicationProviderEventType.ID`
- EventDate - tracks the date/time that the event occured, as per the provider
- EventInfo - nvarchar(max) - could store information like the URL that was clicked on but this would be possibly different on a per CommunicatiotionProviderEventType basis

- new table: `CommunicationProviderEventType` which will store the supported event types for each provider.
- Fields will be ID, Name (nvarchar(100), Description nvarchar(max), BaseType nvarchar(20)
- BaseType will have a CHECK CONSTRAINT limiting the possible values to `Delivered`, `Opened`, `Clicked` and `Other`. We can add new BaseTypes over time if want

We will add a new column to the `CommunicationLog` table as follows
- `ProviderMessageID` which will be an nvarchar(255) and be used to store whatever unique ID the provider gives us when a message is sent. This is useful for whenever we get event information back per the above

**All New Tables will have TrackChanges = 1 which will result in the typical `CreatedAt/UpdatedAt` cols being auto-added when CodeGen runs**

**You will need to add `CommunicationProviderEventType` records to the system for `SendGrid` and `MS365` providers.**

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.