MemberJunction / MemberJunction/MJ
Pull back Messaging Delivery/Open/Click Data into MJ
- 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
Assessment
This issue has not been assessed yet.