/
NUTRINTG SFMC to CDP Market level integration

NUTRINTG SFMC to CDP Market level integration

Owned by Sebastian Baltruszewicz
Aug 16, 2023

Option 1 (without a slight change on SFMC side)

Data on SFMC


Enterprise sFTP
(covers all Business Units)		Brand level BU sFTPsMarket level BU sFTPs

1

ALL_EMAIL_TRK_<yyyymmdd>.zip 

<BRANDORG>_JOURNEY_TRK_<yyyymmdd>.csv 

<CountryCode>_JOURNEY_TRK_<yyyymmdd>.csv 

2

Sent.csv

<BRANDORG>_JOURNEY_ACT_TRK_<yyyymmdd>.csv 

<CountryCode>_JOURNEY_ACT_TRK_<yyyymmdd>.csv 

3

Opens.csv

<BRANDORG>_SENDLOG_TRK_<yyyymmdd>.csv 

<CountryCode>_SENDLOG_TRK_<yyyymmdd>.csv 

4

Complaints.csv

<BRANDORG>_SMS_TRK_<yyyymmdd>.csv 

<CountryCode>_SMS_TRK_<yyyymmdd>.csv 

5

Bounces.csv



6

SendJobs.csv



7

Clicks.csv



8

Unsubs.csv



9

StatusChanges.csv




Process

  • Download files from Brand level BU sFTPs and consolidate with Email files from Enterprise sFTP - DONE.
  • Download files from Market level BU sFTPs and consolidate with Email files from Enterprise sFTP - TODO. Initial time estimate: 8 man-days.

Market level files contain data for more that one Brandorgcode. File names contain CountryCode names instead of BrandOrgCodes. For BrandOrgCodes separation Sendlog file should be used, which has Brand_Org_Code field.


Option 2 (with a slight change on SFMC side)

Data on SFMC

Files contain both Market and Brand level data and are placed on one sFTP.


Enterprise sFTP
(covers all Business Units)

1

ALL_EMAIL_TRK_<yyyymmdd>.zip 

2

Sent.csv

3

Opens.csv

4

Complaints.csv

5

Bounces.csv

6

SendJobs.csv

7

Clicks.csv

8

Unsubs.csv

9

StatusChanges.csv

10

JOURNEY_TRK_<yyyymmdd>.csv 

11

JOURNEY_ACT_TRK_<yyyymmdd>.csv 

12

SENDLOG_TRK_<yyyymmdd>.csv 

13

SMS_TRK_<yyyymmdd>.csv 

Process

  • Download files from Enterprise sFTP and prepare files (C_SEND, C_RESPONSE, C_CMP-METADATA - according to CDP requirements) for each BrandOrgCode.

Data architect (Kamran Shahid) description

The change is how we combine this and surface it on the Enterprise account and this will be done using a stored procedure. I think this would be an ideal setup for you given the number of business units. The reason this was not recommended in the BU Arch document was that the build was done by RB and this wouldn't have been possible.

Advantages:
  1. Scalable as more markets/brands can be added without setting up automation per BU
  2. Single point of failure (only one error log per table)
  3. Data located at a single location
  4. Less administrative effort
Disadvantages:
  1. Less flexibility on changing the file structure
  2. If there is a change required to the schema of the file then DA will be required as this needs to be done in the stored procedure.

Questions:

  • What would happen with current integrations on brand and market level?
[Kam] Both BU and Market will collect data as usual. No change. The change is only that we will write a store procedure to combine the data and send it to ENT account
  • Would be have any period when data stop flowing?
[Kam] Only in case if the stored procedure fails but there will be an error notification part of the automation. The risk is there whatever mechanism we use to send the data.
  • What 3 or 4 new procedures you meant to be created by DA?
  1. Journey Builder
  2. Journey Builder Activity Tracking
  3. SMSMessage Tracking
  4. MobilePush (coming later)
[kam] SendLog will be sent using a background configuration.
  • How would it work if we have new BU on market level and new BU on brand level?
[kam] The stored procedure will capture the data as soon as the new brand or market BU is created and communication is sent. No need to setup automation per BU. This is because all send data is captured and stored in the backend tables and we use a stored procedure to collect it and send it to ENT account.