# Stats consolidation for SFMC

## Investigation
- [x] check that logs are in DEBUG
- [x] access cloudwatch grou ca-kkt on staging-eu
- [x] run a journey
- [x] retrieve the logs

### Journey payload
#### First journey:
**First contact:**

{
    "inArguments": [
        {
            "context": "CqXVzDmjkuP5_XVEYXOHpt8aJQi0rh-rBcaGM4q7CBHU2gpu-_L2FJuOqNeqcLdRUdrniW9AJ7vfiXoqPlozT40_oiZsxftIaIs-SqoQpLYwIDNGA6h2fjz3o4yIL6YeGL3vmliU_cGcuSzeQ08Awdwo7FjHYiLPl21jLHfbU7NI2wl-dEwwcxkXXY17wkENSRMvJjtv7KmVoAezY0IGWUCZipmGNFYlJw2c99Nu_NtXrF48uN2Lpf-iGtTiZvofK3omN5k1NknGrNCiB5kG3YOM9z4O7H-yqKuPlmW-hcvTjQ1QScT3PPb11VaAgq948xpie2PGakQu69XNYw-eMrCySx0OUqEBfxfs0l1YE8PJrBrFemSr5eeTQblTk50z",
            "channel": "KKT",
            "selectedTemplate": "01G2CDG5VKG7QHXBTR8ZGE8JZM",
            "selectedType": "F",
            "fields": {
                "_msisdn_": "33761268020"
            },
            "selectedLanguage": "en",
            "testedValues": null,
            "testTag": "init",
            "selectedTemplateName": "A Test Template "
        }
    ],
    "outArguments": [],
    "activityObjectID": "058c81e0-7522-4a01-a9fe-430f2c57372c",
    "journeyId": "72595a48-ddf5-4ff5-a3f8-e17d64aa41c3",
    "activityId": "058c81e0-7522-4a01-a9fe-430f2c57372c",
    "definitionInstanceId": "de7d6993-4389-4cb1-b1ff-91b6d568f1c2",
    "activityInstanceId": "f213ca76-237e-415f-a80f-07fcd8b87964",
    "keyValue": "Aurélien",
    "mode": 0
}

#### Second journey, Version 1 (set with re entry):
**First contact:**

{
    "inArguments": [
        {
            "context": "CqXVzDmjkuP5_XVEYXOHpt8aJQi0rh-rBcaGM4q7CBHU2gpu-_L2FJuOqNeqcLdRUdrniW9AJ7vfiXoqPlozT40_oiZsxftIaIs-SqoQpLYwIDNGA6h2fjz3o4yIL6YeGL3vmliU_cGcuSzeQ08Awdwo7FjHYiLPl21jLHfbU7NI2wl-dEwwcxkXXY17wkENSRMvJjtv7KmVoAezY0IGWUCZipmGNFYlJw2c99Nu_NtXrF48uN2Lpf-iGtTiZvofK3omN5k1NknGrNCiB5kG3YOM9z4O7H-yqKuPlmW-hcvTjQ1QScT3PPb11VaAgq948xpie2PGakQu69XNYw-eMrCySx0OUqEBfxfs0l1YE8PJrBrFemSr5eeTQblTk50z",
            "channel": "KKT",
            "selectedTemplate": "01G2CDG5VKG7QHXBTR8ZGE8JZM",
            "selectedType": "F",
            "fields": {
                "_msisdn_": "33761268020"
            },
            "selectedLanguage": "en",
            "testedValues": null,
            "testTag": "init",
            "selectedTemplateName": "A Test Template "
        }
    ],
    "outArguments": [],
    "activityObjectID": "0aa6f405-8450-453e-a3a1-2b0406770204",
    "journeyId": "c8462ad2-9400-44d1-90ff-0282f522674b",
    "activityId": "0aa6f405-8450-453e-a3a1-2b0406770204",
    "definitionInstanceId": "2fb95ae6-94d9-4f84-acf2-eb572d891fd8",
    "activityInstanceId": "db97369e-5e0a-4c91-a788-4df2530a4c5f",
    "keyValue": "Aurélien",
    "mode": 0
}

**Second contact:**

{
    "inArguments": [
        {
            "context": "CqXVzDmjkuP5_XVEYXOHpt8aJQi0rh-rBcaGM4q7CBHU2gpu-_L2FJuOqNeqcLdRUdrniW9AJ7vfiXoqPlozT40_oiZsxftIaIs-SqoQpLYwIDNGA6h2fjz3o4yIL6YeGL3vmliU_cGcuSzeQ08Awdwo7FjHYiLPl21jLHfbU7NI2wl-dEwwcxkXXY17wkENSRMvJjtv7KmVoAezY0IGWUCZipmGNFYlJw2c99Nu_NtXrF48uN2Lpf-iGtTiZvofK3omN5k1NknGrNCiB5kG3YOM9z4O7H-yqKuPlmW-hcvTjQ1QScT3PPb11VaAgq948xpie2PGakQu69XNYw-eMrCySx0OUqEBfxfs0l1YE8PJrBrFemSr5eeTQblTk50z",
            "channel": "KKT",
            "selectedTemplate": "01G2CDG5VKG7QHXBTR8ZGE8JZM",
            "selectedType": "F",
            "fields": {
                "_msisdn_": "33861268020"
            },
            "selectedLanguage": "en",
            "testedValues": null,
            "testTag": "init",
            "selectedTemplateName": "A Test Template "
        }
    ],
    "outArguments": [],
    "activityObjectID": "0aa6f405-8450-453e-a3a1-2b0406770204",
    "journeyId": "c8462ad2-9400-44d1-90ff-0282f522674b",
    "activityId": "0aa6f405-8450-453e-a3a1-2b0406770204",
    "definitionInstanceId": "92c0e243-4db7-4709-a216-b46884239007",
    "activityInstanceId": "db6792b6-b649-422b-abd5-95581668d276",
    "keyValue": "Roger",
    "mode": 0
}

#### Second journey, Version 2

**First contact:**

{
    "inArguments": [
        {
            "context": "CqXVzDmjkuP5_XVEYXOHpt8aJQi0rh-rBcaGM4q7CBHU2gpu-_L2FJuOqNeqcLdRUdrniW9AJ7vfiXoqPlozT40_oiZsxftIaIs-SqoQpLYwIDNGA6h2fjz3o4yIL6YeGL3vmliU_cGcuSzeQ08Awdwo7FjHYiLPl21jLHfbU7NI2wl-dEwwcxkXXY17wkENSRMvJjtv7KmVoAezY0IGWUCZipmGNFYlJw2c99Nu_NtXrF48uN2Lpf-iGtTiZvofK3omN5k1NknGrNCiB5kG3YOM9z4O7H-yqKuPlmW-hcvTjQ1QScT3PPb11VaAgq948xpie2PGakQu69XNYw-eMrCySx0OUqEBfxfs0l1YE8PJrBrFemSr5eeTQblTk50z",
            "channel": "KKT",
            "selectedTemplate": "01G2CDG5VKG7QHXBTR8ZGE8JZM",
            "selectedType": "F",
            "fields": {
                "_msisdn_": "33761268020"
            },
            "selectedLanguage": "en",
            "testedValues": null,
            "testTag": "init",
            "selectedTemplateName": "A Test Template "
        }
    ],
    "outArguments": [],
    "activityObjectID": "1ddf6893-e7f9-4617-b5aa-5a0b8aa43c30",
    "journeyId": "1e366493-8498-497d-845e-89b9217d8ef1",
    "activityId": "1ddf6893-e7f9-4617-b5aa-5a0b8aa43c30",
    "definitionInstanceId": "4e66ee7f-f466-4b2d-9b6d-fe995c4af8ef",
    "activityInstanceId": "428f3d1d-72df-49c9-b519-ab62b9c2fe53",
    "keyValue": "Aurélien",
    "mode": 0
}

**Second contact:**

{
    "inArguments": [
        {
            "context": "CqXVzDmjkuP5_XVEYXOHpt8aJQi0rh-rBcaGM4q7CBHU2gpu-_L2FJuOqNeqcLdRUdrniW9AJ7vfiXoqPlozT40_oiZsxftIaIs-SqoQpLYwIDNGA6h2fjz3o4yIL6YeGL3vmliU_cGcuSzeQ08Awdwo7FjHYiLPl21jLHfbU7NI2wl-dEwwcxkXXY17wkENSRMvJjtv7KmVoAezY0IGWUCZipmGNFYlJw2c99Nu_NtXrF48uN2Lpf-iGtTiZvofK3omN5k1NknGrNCiB5kG3YOM9z4O7H-yqKuPlmW-hcvTjQ1QScT3PPb11VaAgq948xpie2PGakQu69XNYw-eMrCySx0OUqEBfxfs0l1YE8PJrBrFemSr5eeTQblTk50z",
            "channel": "KKT",
            "selectedTemplate": "01G2CDG5VKG7QHXBTR8ZGE8JZM",
            "selectedType": "F",
            "fields": {
                "_msisdn_": "33861268020"
            },
            "selectedLanguage": "en",
            "testedValues": null,
            "testTag": "init",
            "selectedTemplateName": "A Test Template "
        }
    ],
    "outArguments": [],
    "activityObjectID": "1ddf6893-e7f9-4617-b5aa-5a0b8aa43c30",
    "journeyId": "1e366493-8498-497d-845e-89b9217d8ef1",
    "activityId": "1ddf6893-e7f9-4617-b5aa-5a0b8aa43c30",
    "definitionInstanceId": "1d26cd5c-a9af-45ca-b364-fb5417ef500b",
    "activityInstanceId": "cc41b077-78da-476e-bcd3-46e73741089c",
    "keyValue": "Roger",
    "mode": 0
}

### Conclusion

- journeyID / interactionId are the same thing !
- interactionId is used for /save /validate /publish
- journeyID is used for /execute

- journeyID is different for each version - it represent a "JourneyVersion"

- first JourneyID is identified as: originalDefinitionId

- interactionKey is the same for all journey version

- activityObjectID identify a specific "Custom Activity" within a journey (which my be composed by multiple ones)

### Proposition

- One DE/Table per "Journey"
- Problem:
- Journeys Name change with time/version
- Name: KKT_Campaigns_stats_JOURNEY_NAME
- If the journey name updates, then multiple table
- If the journey name is already used, we add to that table and user will be able to extract information through the fields of the DE.

- ***TODO*** KKT_Campaigns_events_JOURNEY_NAME (traitement space/)
- ***TODO*** [PRIMARY] **event_id:** Unique id of the event :heavy_check_mark:
- Easy, we generate it (ULID like https://www.npmjs.com/package/ulid)
- **message_id:** ConvAPI unique id for a message sent :heavy_check_mark:
- ***TODO*** **event_time:** moment in time when the message is received, as determined by the underlying channel :heavy_check_mark:
- Easy just need to use it instead of accepted_time (maybe log accepted_time)
- ***TODO*** [PRIMARY] **keyValue:** keyValue passed along with the execute - whatever is setup on the DE by the client/sfmc user :heavy_check_mark:
- Average, We have the info in the `/execute/` need to pass it allong as meta data
- **phone_number:** phone_number to which the message was sent :heavy_check_mark:
- status: [DELIVERED/FAILED] :heavy_check_mark:
- ***TODO*** reason :heavy_check_mark:
- ***TODO*** reason_message :heavy_check_mark:
- ***TODO*** [PRIMARY] **journey_id:** UUID unique per version of the JOURNEY :heavy_check_mark:
- Easy/Average. We have the info in the `/execute` need to pass it allong as meta data
- ***TODO*** **journey_version :** INT :heavy_check_mark:
- Average, We have the info JourneyID info in the `/execute` need to pass it allong as meta data to do a REST API call and cache it in the agent
- ***TODO*** [PRIMARY] **activity_id:** UUID unique per instance of the Custom Activity (will change per version) :heavy_check_mark:
- Easy/Average. We have the info in the `/execute` need to pass it allong as meta data
- ***TODO*** **activity_name:** Activity Name :heavy_check_mark:
- Easy/Average. Have not seen yet how to retrieve this ...
- Retrieve name:
- At execute time: we have the activity ID which make it easy to retrieve it but it will have some impacts on latency
- At callback time on webhook: less simple as we may have more than one custom activity to filter to get the right one

accepted_time ISO 8601 timestamp Timestamp marking when the channel callback was accepted/received by the Conversation API.
event_time ISO 8601 timestamp Timestamp of the event as provided by the underlying channels.

#### Questions
* KKT_Campaigns_events_JOURNEY_NAME: sanitization of name (remove space and no special character) - are you ok ?
* Quels champs voulez vous voir defini comme primaire?
* Avec les clefs primaire, pouvez vous exploitez la data.
* Versioning du schema de la table/schema ??
* How to address write rights on specfic DE. :warning:

* Utilisateurs:
* Person in charge to desing Journey
* Person in charge of expoiting resuts
* Person in charge of support

# R&D
keyValue => Context.ContactKey
primary

# NOTES
Delivery Status: https://developers.sinch.com/docs/conversation/callbacks/
QUEUED
QUEUED_ON_CHANNEL
DELIVERED
READ
FAILED

MESSAGE_DELIVERY - subscribe to delivery receipts for app message sent to channels.
EVENT_DELIVERY - subscribe to delivery receipts for an event sent to channels.

- The journey ID seems to be a unique identifier that may be used to correlate the sinch campaign with the SFMC journey:
- whatever the journey is
- whatever the version of the journey is
- regarding a new version of a journey, what does the customer expect from the statistics?
- each version is different so he will have statistics for each version?
- he wants to have statistics aggregated with all the journey versions? => at first sight, it is impossible to find a common ID for different versions
- we have to see with SFMC APi to get some details regarding journey datas (see also [data binding](https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/how-data-binding-works.html#journey-context))
- We have to explore more deeply:
- what are the user needs
- what are the SFMC possibilities (have an SFMC developper or expert with us)

5.000
4949

100.000
98 833

DATA EXTENSION FILTRAGE

IF subcriber_info == DE_INPUT.FieldID AND
status == DELIVERED AND
journey_id == mycurrentJourney.ID AND
activity_id == mycurrentJourney.ID AND