How to Download Historical Tracking Extract Data of an SFMC Instance

Recently Salesforce announced changes to its data retention policy for Marketing Cloud Engagement. Starting January 15, 2025, subscriber and journey engagement data will be retained and accessible for 180 days. This policy applies to data you retrieve using the Email Studio Reports in Analytics Builder, the tracking reports in Email Studio, Tracking Extracts in Automation Studio, or requests to SOAP API. 

When this policy takes effect, subscriber and journey engagement data over 180 days old will no longer be accessible using these reports. Here is the announcement

Most of the Marketing Cloud instances I have worked on in the past, typically have automation that extracts previous days' tracking data, imports it in a data extension and also transfers it to external reporting or data storage service. The most common and important tracking extract data are - Sent, Not Sent, Open, Clicks, Unsubscribes, Subscribers, Send Jobs and Bounces.

In case you do not have this historical tracking extract data saved anywhere, I would recommend fetching and storing it for future reporting and analytical needs. You should also do this before the deadline of January 15, 2025.

How to Get Tracking Extract Data

Step 1 - Create Automation: 

1. Go to Automation Studio and Create an Automation. Select Schedule as the Starting Source.

Step 2 - Create Data Extract Activity: 

1. Drag the Data Extract activity on the canvas as the step 1. 
2. Give it a Name, leave the External key blank, and give it a file name pattern (something identifiable). It is also a good idea to add a Wildcard specifier (year, date and day stamp) so that when the zipped files are transferred in the FTP they are not overwritten by the new file. In case you do not need to retain the previous file, you can skip adding a wildcard. 
3. In the Extract Type, select Tracking Extract from the dropdown. Click Next. 
4. For the date Period, you can select either a Rolling Range (1 day, 7 days, 30 days, 60 days or 90 days) or a Specific Range which cannot exceed 30 days. If you are scheduling automation you can set the schedule so that the automation runs either daily, weekly or monthly and accordingly, it can pull the previous 1 day, 7 days or 30 days of data respectively. If you are trying to extract historical data, you can select the time ranges of 30 days each and keep running the extract again and again to get all the data you need.
5. In the Account IDs box, if you do not specify anything, it will pull data from the current business units. If you are running the extract from the parent business unit, you can specify the MIDs of all the business units that you are interested in and separate them by a comma. If you just add an asterisk(*), the extract will contain the data from all business units in that account.
6. On the right side of this screen, you can select the checkbox for the kind of data you are interested in - For example Bounces, Clicks, Unsubs, Sends Jobs, Sent, Opens, Unique Opens, Unique Clicks etc. Click Next
7. On the final screen, check that everything is correct and click Finish. This completes your data extract activity.

Step 3 - Create File Transfer Activity (Move from Safehouse): 

1. Drag the File Transfer activity to the canvas as the step 2.
2. Give it a name. I would suggest using the same name as the data extract activity and adding the suffix of the file transfer. In the File Action, select Move a File from Safehouse. Click Next
3. On the next screen, enter the File Naming Pattern. It should be the same as the File Naming Pattern in the Data Extract activity. In the Destination, select the FTP location where you need to send the file. The FTP has to be set up as a file location in your instance. You can Encrypt the file if you want. Click Next.
4. Check if everything looks good and click Finish. This completes your File Transfer activity.

The File Transfer Activity (move from safehouse) transfers the tracking data CSV files (Clicks, Opens, Sent etc) to the FTP in zip file format. If you have an integration with an external platform (storage application or reporting) and if it can consume the data in zip format, you are good. However, if that external system cannot ingest the zip file and need the data to be extracted or if you need the data imported back to Marketing Cloud in data extensions, you will need another step in the automation for File Transfer Activity (Unzip).

Step 4 - Create File Transfer Activity (Manage File): 

1. Drag the File Transfer activity to the canvas as the step 3.
2. Give it a name. I would suggest using the same name as the previous File Transfer activity and adding the suffix of the file transfer unzip. In the File Action, select Manage File from Safehouse. Click Next.
3. On the next screen, enter the File Naming Pattern. It should be the same as the File Naming Pattern in the Data Extract and the previous File Transfer activity. In the Source File Location, select the FTP location that you selected as a destination in the previous File Transfer Activity. In the File Management Actions, select Unzip compressed file.
4. In the Qualifications section, there are some additional options that you can select depending on your need. Click Next.
5. On the final screen, check that everything is correct and click Finish. This completes your File Transfer (Unzip) activity. Once complete, this activity will create individual tracking data CSV files in the required FTP folder.

Step 5 - Create File Transfer Activity (Manage File): 

1. Drag the Data Copy or Import activity to the canvas as the step 4.
2. Give it a name. I would suggest using the same name as the data extract activity and adding the suffix of the file import. Leave the External Key blank. You can add a notification email address if you need an email notification that informs you when the import is completed. Click Next.
3. On the screen, in Data Source, select the File Location checkbox and select the File Location folder where you transferred the file in the previous step. Give the file naming pattern, for example, it could be click.csv, sent.csv, unsubs.csv, or bounce.csv. etc. There are some other options on the right side of this screen that you can select depending on your needs. Click Next.
4. On this screen, you can select the data extension where you want to import the data from the tracking extract file. This data extension should have the necessary fields that are there in the tracking extract file. Click Next.
5. On this screen you can specify the Data Action you want - Add only, Update Only, Add and Update and Overwrite. For something like Tracking extract data, you would most probably like to select Add and Update. Select Map by Header Row if your data extension has the fields that match the fields in the track extract CSV file. Otherwise, you can select Map Manually and map the fields manually. Click Finish.
6. On the final screen, check that everything is correct and click Finish. This completes your File Import activity. You will have to create separate File Import Activities and add in Step 4 of your automation for each CSV file. So an import activity for Sent data, an import activity for open data etc. 

How to Get Historical Tracking Extract Data

For the Historical tracking data, you can do it by repeating the above steps several times. So for example, if you need the last 2 years of historical data, you will have only to modify the first activity 

(Data Extract) for the date period in the above automation and run it 24+ times. This is because there are 24 months in 2 years and for each period you cannot have more than 30 days. Also, the last day for each period will be the start date for the next period as the time ranges are 12 AM to 12 AM.

However, doing this manually so many times is not easy. It will take too much time and there are chances of errors and mistakes. And if your send volume is too high, you may encounter issues while running the automation. The extract activity may take too much time and may even timeout. I would suggest a smaller period of 15 days or 10 days each in case you encounter timeout errors.

If you need to extract the historical tracking data for more than 6 months or so, I would suggest automating this work. This can be achieved by using SSJS activity as the first step at the start of the automation. You can use SSJS and configure it to look at a data extension for the period (start and end date, data extract activity name, filename pattern and processed Boolean value). You can populate this data extension with start and end dates for the whole historical period you need the tracking extract history in different rows, data extract activity, and filename pattern name will remain the same for all the rows. Set the Processed boolean value as default false for all the rows. 

Design the SSJS activity so that it looks at the data extension and if the value of the Processed Boolean field is False it updates the data extract activity with start and end dates, and filename pattern and changes the value of the Boolean field (Processed) to False. So you can continue running all the steps in the automation without worrying about updating the data extract activity as it will be taken care of by the SSJS script activity. 

If you want to fully automate this process you can do that by duplicating this automation and adding a SSJS activity as the last step in both automations to invoke each other. This way both the automation will keep running until all the data is transferred and until you manually stop them.