1

I have an import that is based on a SF report, import is set to overwrite. If the new report results are empty, the original information remains in the Data Extension.

I need it to remove any prior information and write over with the new results from the report. What am I missing? import details

2
  • We encountered this behaviour as well and it also occurs with SQL queries that are set to overwrite: if either process has no rows in the source data extension or data set it doesn't really process the import/query. I assume this happens for performance reasons. What you can do depends a bit on the process scenario you have this issue in. Can you please give some details on this? Commented Nov 7, 2017 at 21:41
  • We have a date field in a campaign which indicates there is a new update that needs to be emailed to the campaign members. We have 2 imports running on reports of campaign contacts and campaign leads where the date equals the past 7 days. These 2 data extensions are merged into a sendable data extension. This week the results of both reports were null and the email went out to the same people as last week since it did not write over the sendable data extension with null values Thanks @StephandePaly I really need some guidance on this.
    – user47606
    Commented Nov 8, 2017 at 1:58

2 Answers 2

0

for the following approach you need the date field that indicates the change in the report for processing in the Marketing Cloud. The solution will use the Verification Activity in Automation Studio to alert you and abort the process before the send. It is also based on the assumption that the campaign level date field will always cause both imports to be "empty" if there has been no change within the last 7 days with the result that you only need to check one of the data extensions.

  1. Get the field in the report(s) if not already there.
  2. Create a Filter or Query activity (which one is best depends on performance requirements and technical prowess) to write records that are older than your date range into another data extension

    2.1 Filter: Create a filter for one of the import data extensions that searches for all records where the date is older than 7 days ([Your datefield] less than today -7). If it returns results as desired create an acitivity for it that you can use in an automation.

    2.2 Query: Create a data extension with two fields ([Your Lead/Contact Id field] and [Your date field]) as target data extension for your query. Then create a Query Activity after the following setup that writes to this data extension:

    SELECT [Your Lead/Contact Id field], [Your date field] FROM [Your import data extension] WHERE datediff(day,[Your date field],getdate()) > 7

  3. Setup your automation with the following sequence

    3.1 Lead Report Import, Contact Report Import

    3.2 Filter or Query activity

    3.3 Verification Activity that checks the target data extension of your filter or query for the following condition: Record count is greater than 0. As trigger actions select Stop Automation and Send Email Notification to know that the condition was met and no mails will be sent out.

    3.4 Send Activities for Leads and Contacts

The automation now runs the import, extracts records that are older than your desired date range and aborts before the send activities if it finds one or more records.

Would that work for you?

(1) Documentation, Verification Activity: http://help.marketingcloud.com/en/documentation/automation_studio/using_automation_studio_activities/verification_activity/

2
  • It is possible that one import (lead or contact) may have results where the other may not. In the import, I am only pulling in contacts and leads that contain dates within the last 7 days. I can pull in the date field but it would only include dates for the past 7 days, there is nothing to filter out. In order to make this work, do I need to pull in more data so that I have something to filter on?
    – user47606
    Commented Nov 12, 2017 at 22:05
  • If one of both can be empty you set up the filter for both to catch old data. No, you don't have to pull in older data. If there is no data for one of the imports the date of the outdated records will automatically be beyond your 7 days range as it comes from an import at least 7 days old. Commented Nov 12, 2017 at 22:23
0

From my experience for this scenario, I would clear all records in the data extension before doing the import activity. Hence you can start a fresh import.

You can clear records in the DE using Script activity with WSProxy like this:

<script runat="server">
Platform.Load("core", "1.1.1");

var prox = new Script.Util.WSProxy();
var customerKey = "Your DE cust key";
var action = "ClearData"

var result = prox.performItem('DataExtension', {"CustomerKey": customerKey}, action);
</script>

The thing I love in this way is that it will work even if the DE has a non nullable column.

And then put the script activity before the import activity in an automation

For example:

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .