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. 😀

Also to expand on what ​@matthew_lind mentioned earlier re: Previews. 

In Data Designer and Horizon Rules, previews are not actually representative of your desired data set. In other words, they fail the basic promise of what a preview should provide.

Each Task will pull at most something like 2K records. It seems like that random assortment of records is pulled, which means joins that should yield data may not show in the preview. This problem gets more and more exacerbated the more fetch tasks and merges you run.

I will often have to single out a single record for every task I expect to make it from top to bottom, unless I want to fully execute my rule or design to get the real results.

​@aaronhatton ​@hunterjoseph12 ​@matthew_lind is this essentially the steps we would take if we wanted to create a report from the Relationship object to show a column that has LAST TIMELINE MEETING TYPE DATE and LAST TIMELINE EMAIL DATE.  We are trying to evaluate which relationships haven’t had a meeting in “x” days or haven’t had email communication in “x” days but there is no standard “show last activity date” field to use in a report as is.

​@nburke Let me summarize this way…

If you need the date of the most recent entry, you can aggregate with a MAX of Date in the “Show Me”, and you’re virtually done. In this scenario, the needed data point IS the aggregated data point, so you don’t need that re-merge step.

If you need some other value from the most recent entry, then you need the “split and re-merge” plan. For example, if you needed to know who authored the most recent entry, the needed data point IS NOT the aggregated data point; now you need a re-merge. (Republishing ​@aaronhatton’s image or a “split and re-merge” here for clarity.)

Also, along a slightly different topic, if you need to find Companies without data (for example, there are no timeline entries), then you’re likely destined for a LEFT join, whereby you have all Companies on the left, joining to your queried data on the right. An INNER join will drop Companies without data, so use a LEFT.