Getting the most recent data in Data Designer

Good news @hunterjoseph12 ...this is very doable in Data Designer. I’ve done exactly this.

Tactically, I do this using steps like this:

1. Fetch all the possible records in scope.
2. Create a Transform step, where you group records by Company and then also take the MAX Activity Date. (if the goal is most recent Timeline entry for each of your customers, in your example). Don’t pull in any other fields in this Transform step.
3. Create a Merge (with a common, inner join) where you merge the Fetch in Step 1 with the Transform in Step 2.
4. Now you’ll have a dataset that includes only the record with the most recent (ie, the MAX) Activity Date for each Company, and you’ll also have all its metadata like Author, Type, Subject, Description, etc.
5. You can further Transform this, using your example, to mark if records fit certain criteria. You might create a new field with a CASE statement: If Activity Date within the last x months, then TRUE, else FALSE.

I’m attaching an image. Pay less attention to the specific names (I had a slight different use case when I built this Data Designer), but more attention to the shape and steps that will get you to your goal.

You’ll get a DD shape where you pull data, transform it, then merge it back onto itself. Try it, and good luck!

Hey Matthew,

This has helped a ton! I am still having a few questions on my end when doing this though! 

So i did the transform where the only fields i have are the Clients Name (rather than company as this i am looking for most recent meeting with each person) and the Max Activity Date - preview of that data set is showing just a single instance as it should -, and when i merged it back into that first fetch field all the clients are duplicated - same Activity but just has two rows of same information, any idea why that would be occuring? 

We’re getting there, @hunterjoseph12 .

Hard to know exactly what’s happening without seeing your data, but…

If you’re trying to find individual people, and some meetings have more than 1 external attendee, I could see where Gainsight would duplicate the Timeline Entry for each attendee.

Also in your merge, be sure you’re merging on all the fields in your Transform, and you’re configuring a Common (inner) join.

hmm hard when its working with client data right ! 

did the steps as you stated and still is duplicating the rows - weird! ill try some things to see if i can iron this out but i appreciate you getting me about 90% of the way there! 

One more idea...then let’s do the weekend.

Because it sounds like your focus is on individuals, you might start--if you haven’t already--with the Activity Attendee object, where you could then reach the Activity Timeline records and the Person records via Lookup. In your Transform, group by the Person ID (or Person Email, which I like less because I try to stick with IDs in these scenarios) and take a MAX of the Activity Timeline. Then merge back with the Activity Attendee object, and see what you get.

What an amazing discussion! ⭐

Thank you for jumping in to help out @matthew_lind. 

Moved this to a more suitable category.

I’m having a similar issue with your instructions ​@matthew_lind (thanks by the way!).

I created a transform with the company GSID that groups by company GSID and when previewing the dataset, I see only one record. When I merge it back with the original data set selecting a inner merge I now see multiple records which is weird.

To make it stranger, I even added a second transform after the first to have a boolean true for the record I am selecting (thinking that the group was being misinterpreted) but no matter what I do, it still shows multiple records with the same company GSID after the merge.

Screenshots:

 

First Transform
 

Second Transform

Preview of second transform
 

Preview after merge

Starting to think this might be a product bug/feature… Keen to see if ​@hunterjoseph12 you figured it out?

Always an adventure, yes ​@aaronhatton?

First, I don’t trust Preview to show all records, and never have. This is a sticky wicket, especially because one cannot inspect any intermediate dataset in a DD. So depending on how important the troubleshooting is, you might create a “Rule version” and run in Test Mode, because then you can inspect complete outputs of any intermediate step.

Next, I could see DD offering multiple records per “Group by” a Company ID, where you are passing in Campaign Members. If there are multiple Campaign Members within a Company who all have the same Modified By Date, I believe Gainsight would pass all MAX rows back out in the Merge Step.

If this is my original fetch,

and I transform Group By Company with a MAX of Last Modified Date.

I would get this many records per Company

Thanks ​@matthew_lind - I did a rule to test and you’re right in what results I see. Strange it handles it that way.

Any suggestions on how to get only one record which is the most recent?

Any suggestions on how to get only one record which is the most recent?

I hesitate to solution too heavily unless I understand your desired end state. That said, I think you could daisy-chain 2 Transforms--similar to what you did in your screenshot--like so:

• Transform A: Show MAX of Last Modified Date, Group by Company ID.
• Transform B: Show MIN of Name (or any other String field that has unique values), Group by MAX of Last Modified Date & Company ID

Because Strings can also be MAXed or MINed, that would usually get you down to a single value per Company ID. In theory, that gives you a single record per Company which represents one and only one of the MAX of the Last Modified Date.

That would work if you didn’t care strongly which record “breaks the tie” within a Company ID.

I think. 😀