Datasets Deep Dive | HubSpot Admins HUG | November 10, 2022
Stephen McMillion from HubSpot walks through the datasets tool for admins, covering data joins, derived fields, conditional fields, and aggregations. The session includes live demos and Q&A to help power users build better reports.
Wait for it. We are now recording. Welcome to this Datasets deep dive through the HubSpot admin hug. I am Kyle Jephson, the host and I am losing my voice. It's a great thing that we have Stephen McMillion here to take over and show you the ins and outs of this incredible tool. I'm super excited to have him here. Steven, I will be monitoring the chat. If any questions come through, I will let you know. But I will let you take it from here.
Cool. Thanks for inviting me. Thanks for doing this while you're sick. I saw your Slack notification saying you were sick and you're still here but please take the rest of the day off. My name is Stephen. I'm the PM for datasets. I've been at HubSpot for a little under two years. I was with the Datasets team from the first meeting to launch through. Now I know a lot about datasets obviously, but also just reporting at HubSpot. So happy to tie some. Some pattern matching about how data sets works the rest of the platform and maybe answer some more pressing questions about any confusions around how everything works together generally. Today I was thinking about going through a walkthrough of the tool specifically on joins property curation, deriving data and data sets, grouping data and data sets, which is part of derivations but some of a little really cool feature people use the most. And then the connection between data sets and report builder. And finally a beta that we are in private beta that we'd love to show you all and see. Kind of like what you think, see who might be interested in joining. So a decent agenda. Please interrupt me and I'll try to stop for questions as well. But if that sounds good, I'm going to share my screen, go for it.
On the Q and A feature. So if anyone wants to drop questions in there, that's fine or just use the chat. I will be monitoring it. And Steven, you might notice once you start sharing your screen, you probably won't be able to see the chat anymore. But I will let you know if questions come up.
Cool. Literally just interrupt me. Thanks. Share screen. What a weird. Okay, let's see. Can you guys see my other screen? What screen are you guys seeing?
We're seeing the dashboard. Test dashboard. We're seeing HubSpot.
Okay, yeah, I literally lost. This is different than Zoom. So.
Yeah, no, it's. It's super disorienting. I promise you're not all alone just talking to yourself. We are here. We can hear you. We can see your screen.
Like not seeing myself, like I'm just like talking to myself in a room. Anyway, so this is a dashboard to navigate to data sets. You go under the reporting dropdown and then you go to data management data sets. So here you are, data listing page. Everything you see here is something that someone in your portal has created. If no one in your portal has created anything, it'll be an empty state. The first step will be creating a data set then or trying to demo. But I'm demoing for you all. Here is the source selection page. If you've ever used the custom report builder it should be a familiar page. We've tried to maintain parity. The purpose of this page is to select the different source tables that you want to report on what's actually happening. This page is we're doing a join, we're doing a join between the primary data source you select. Right now it says contacts gonna change the deals and then it's doing a left join with any other table you select. So right now it says I'm in a deals table and select all 22 deals in my portal. This is my portal. I have like pretty sparse data. But if I join the companies to this, it's doing a left join and is looking for every single deal here and saying if there is a company associated, pull those companies. It helps you understand how many companies there are, how many deals there are, how. But then it shows you there are only four companies associated deals. So it's trying to help you profile what you're making in this stage. Just give you understanding of like when you make a data set of this, you make a report off this, what should you expect to see? So for this example I'm just going to do deals to primary source and company and second source, secondary source. But I actually just want to pause right here on the joins page. Are there any questions at all?
One question I have while we give other people a chance to think for a minute, I know some of these features, the table preview at the bottom and the information the right hand panel recently moved from just being in data sets to also being in the custom report builder. Are they exactly the same or are there differences between the two?
Yeah, I think the engineer my team shipped parity between these this week so they should be exactly the same today. The biggest difference, the only difference I know is that there might be a knowledge based article linked off in the report builder experience so you could go straight to the documentation of how to build a report. And because that doesn't apply to us. We removed it. But I think there should be parity between these two experiences. Cool.
Then Amber is wondering if you can just zoom in on your screen a little bit.
Let's see. How do I zoom in? Zoom. All right. I'm at 125.
I definitely think that's better.
Okay, I'm going to move next step. So next step here is curation. And this is where you're doing like a lot of the property curation. This is different than what you would see if you were to go to next in the report builder, where you would be dropped right into the process of building a visual report. Instead, here you're constituting a data table that's going to be used for future reporting other people to report on what you're trying to do or what we hope customers have the ability to do. This stage, it's like reduce the noise for their users and yourself. So if I have deal name, like this deal owner amount. Let's see. Create date amount. So right now what I'm doing is I'm actually making the columns that are going to be available for reporting later. So if I were to make a save this dates at this moment and then go into the customer report builder to make a report, I'd have four columns to work with and only four columns. As opposed to the long tail of properties that you get in your portal. This might be hundreds of properties depending on how big your portal is and how many properties you have on any given object. So right now I'm just curating properties. One thing I can do in this curation step is recontextualize like what a property or column means to you. So just like in Excel, if for some reason we don't really call our deal owners deal owners, but in our portal we have it as deal owner, I can use this moment to say these are actually account executives. That way, when someone's using this common report, they can be like, if someone's in the reporting tool and they're looking for account executives, they'll actually index that and they can search account executives to get this account executive. The other big thing here at this stage is you can create new columns of data. So not only can you select the data columns that are already in your system and properties, you can also create new columns. These columns are just in this reporting context. We're not putting new fields onto your records and muddying at the system. This is just something that, like, it's important for you to create just to answer some question reporting. So A good example of this might be imagine this amount is monthly recurring revenue mrr. But I also need to understand really quickly what ARR is annual recurring revenue. And that's not my system. I can do ARR. That's a MRR times 12. And this populates a new calculated column that just takes the MRR multiplies by 12. The goal here was to create some experience that kind of was reminiscent of Excel. So if I'm creating a different field and I want to know what is the date diff between today and the creation day of this deal? For example, there's all these functions to give you so absolute value average. It's not average. I have some things in here. There's beta, but or like we're baiting or trying out. So not everything is actually parody. Should actually definitely take the sonic portal. But date diff day part. So I'm saying I want to know how many days between the creation day of this deal and today. Today is actually like a function in itself. So what I'm demonstrating here is that you can nest functions together to use them in unique ways. So the difference between the create date and now. This will derive a number that looks ridiculous because this is fake data, but it's taking the number of days between 10, 6, 20, 21 and today and saying that it is 400. That can name this property, this column, which be, let's see, days since free. I'll pause here. Any questions on like what I just showed you? Because I'm going to give another example on grouping data, which is another derived data field, but it's a little different. Any questions?
Question that hops immediately to my mind. I will be very transparent with you and everyone here. Reporting is my Achilles heel inside of HubSpot. I am not good at reporting. One question I get a lot is how do I represent today's date in a report? So could you just make a field that is that now function and then you would have today's date and it would just always be today's date.
Yeah but as you see, it's returning something. It looks like a date timestamp. Do this if you wanted today's date. I probably use the date from parts ability and figuring probably I'll use the now function. Each of these different fields basically say the year from now, give me the month from now, give me the day from now. And that would constitute something that looked a little more like what you're expecting to see. This like date field.
Cool. I feel like that opens a lot of doors for People.
Yeah. And the beta at the end to show you even more cool stuff about how we can use this for other use cases. Before I get into if Functions.
So one person has asked, what's the key difference between Create field and Create Conditional field? So I think they're cluing in on the fact that there are those two different plus buttons in the middle column there and Create conditional field as a beta. If you're getting to that in a minute, we can wait.
So there's no other questions. I can hop right into that.
The one other question is, will we be able to change the format of the output when using calculated fields? Do you need more context on that one?
A bit change the format means different things. Like, it can mean make it a currency, it can mean make it a percentage. Those are all different problems for our team. I'm curious what, like, that question was aimed at. If it was.
Okay. Yeah. So, Clayton, if you could give us a little more color there. Those are the. Those are the two main questions right now. So if you want to.
Okay, I'm going to put this function and I'll come back to that question. If he has any deal.
Cool.
So with if functions, someone pointed out there's like Create field and there's Create conditional fields. The Create conditional field does the exact same thing as the if function transparently. It's our team trying to figure out what is the preferred input method for our users. Do they want us to give them this free range text editor? Or if we added some things that are a little more like guided and let people work in blocks and different sort of like maybe templatized methods or which one people preferred. And we're actively tracking this to get a preference marker. See, like how we should think about these fields in the future. But I'm going to show the syntax first. So imagine that you want to understand your business in terms of priority, and the priority of your business is based on the mrr, the monthly recurring revenue potential of the deal. So I say this is a field that's telling you that priority. I use if function. Say if the mrr, the deal is greater than. Let's pick. Oh my Portals might not be in US dollars. Let's use ARR. Okay, so let's actually change the arrow. If this is greater than 60,000, what's a good marker? Let's make it 150,000. I think 150,000. Then label the row as high priority. Otherwise. Also, these quotation marks are just a string. So it's saying I want to Label this a string instead of a number. A string is any sort of text that you might want to label something as or no priority. As you can see, what it's doing is looking at every single row in this data set and it's evaluating this condition. It's saying if the ARR of this row was greater than 150,000, so this is less than, or this is actually greater than sorry, then label as high priority. Otherwise if it was less than 150,000, default to no priority. And this allows for you to take every single deal in your portal and say it's either high priority or it's no priority. And this has implications about what you might do in reporting and how you might break down your graphs, which I'll show later. The same thing is possible using the conditional field. Kyle, was there any specification about formatting? I just want to see hit that point real quick.
No, I don't think so. Haven't seen a follow up on that.
Cool. So the create conditional field, same thing here. I'm going to do the same exact example, priority two. I can't use the same field name. So if ARR is greater than 150,000, then default to high priority Priority. There's a problem with your input because you need quotation marks also. Yeah, we do our best and we're continuous. This is one of the things we like that's so behind the scenes we put time, effort into is trying to help people understand, like if something does not resolve correctly what was wrong. If anyone's ever worked with SQL, this is like the most painful thing ever where they just say there's an error around this line. Figure it out. We're doing the best to kind of give more contextual understanding of what is wrong with this. It's not surrounded by quotation marks and we are expecting a shrink. So do this to resolve your error. If it's not high priority, it resolves the else statement, which is. What was it called? No priority. And then we should see these resolve the same outcomes either way. But as you can see, it's just a way to do the same thing. The thing that we believe customers like more about this experience is when you have many conditions that need to be resolved together. Like adding these blocks and being able to move them around is just a much easier way to get more. Get more complex nested conditions into the system. So, any questions about what I just saw? I will show more examples of nested if statements with the beta sample.
Okay, that was my request when you said add more Statements and be able to move them around. I was like, man, I want to see that in action. Lots of questions coming in. Can you use emojis in your output text? I love the use of color in October winter's presentation. So I recently discovered that text fields inside HubSpot support emojis. And I've been seeing examples of people doing color coding on deals and ticket boards using emojis. Can you like instead of high priority, could you have like smiley face or thumbs up or something? Does that work?
I don't think so, but I'm not even really sure. Kyle, how do you do it? Is it like a. Is there like a keyboard drop down?
Yeah, it is. So here is a. My favorite keyboard shortcut in the world. If you are on Mac, if you do control command space, it opens your emoji keyboard.
Control command space if you're on.
No, no, it didn't work.
Not for me. What it might, but it's because it might be something that's about our input field and what's allowable, so.
Oh, interesting. Yeah, maybe not, but anyway. Yeah. Control command space or on Windows Windows key period. Opens your emoji field.
Yeah, that didn't work for me. Okay. Whoa, whoa, whoa. What am I looking at?
There it is. Hey. Ta da. It's just slow.
I might be learning something on this call. Let's see. What is a high priority emoji? Oh my God. I have no idea if it's works or not. Okay, but like.
Like click over in the middle column. That is there. What about in the outputs like inside your conditional statement? If you changed high priority into an emoji, would that this would blow my.
Mind if it actually works because has to be able to reconcile this. Let's see.
We might just break stuff. Who knows was it. I also find my emoji keyboard. Sometimes I have to prompt it a few times to come up. Yeah, I wonder if you could just copy paste from the field name since you have an emoji there. Invite Control C Control view. Oh, it worked.
Okay, let's. Are there any questions? This is hilarious to me.
Shout out to Amanda for just changing the total trajectory of this call.
Yes, there are other questions on this call.
Carter says in the future will you be able to query data sets with the API or GraphQL or will it only ever be used with reporting?
Good question. I'm actively talking about this a lot with leadership. I would love to talk to you about your use case. Our data sets exist in Snowflake Snowflake. So right now we give customers ability to access our Snowflake instance through our data chair which you would get through Ops Hub Enterprise and open question in my mind is like a data set is just this neat relational table that we're giving to customers and giving them belgia shape. What if we allowed them to access this through a GraphQL sort of interface or federated layer or some SQL system? I just haven't heard that from. That'd be a really pressing need from customers. So I'd love to connect on your use cases.
Okay. Carter McKay, I assume you're a Carter McKay at Pivot and we are already on an email thread about something else. So Stephen, I'll intro you to over email so you can discuss more. All right. Are you able to do if and or example. Let's say you want to say if ARR is greater than X dollars and create date was X than high priority if not low priority. So.
And what was the example? Whoa. It just popped out of nowhere.
It's a squirrely thing. Yeah. If greater than x dollars and create date was X. I don't know if that's.
Yep. And create date. What's a good create day here? So 8312, 2831 and create dates equal. What is wrong with this statement? The function provided you provide a number. So I need to provide an actual work. If I put a string around will not it's looking for. Yeah, this should work if I do the date from parts thing that I explained before. What's happening in this right now it's just thinking that this is a number I have to constitute as a date. I think this works we put from a date from parts. If we make it a date from parts. I'm not going to try in this moment but I can get back.
But you can't and is an operator that works here.
Oh yeah, yeah. So. And so I said ARR and MRR is greater than 50. But yeah, you can do. And you can do ors. You can do multiple ands and ors. You can use parenthetical. You can use parentheses to do sort of like a. What's that called algebraic. What's the order of operations type stuff?
Oh yeah, yeah.
What needs to be executed first? All that stuff that's similar to algebra.
Cool. So we do have some clarification on the formatting outputs thing. So for instance, so the original question was will we be able to change the format of the output when using calculated fields? And then the clarification is so for instance the date field that you showed. So if we say give me today's date, it gives us a long strand of numbers and we want to convert the number into the month, month, day, day, year, year, year, or month, month, day, year, year format. Or give us month, day, comma, year. Another example is if we want to showcase the percent of a margin on a product, how can we format that to show percent versus just the decimals? So I'm imagining kind of like in Excel when you format a cell, you can say like this is a percentage or this is a date and I want the date formatted in this particular way. I know there are like something like this basically.
Like there's all these different formatting things you can do.
Yeah. Yes, this, yeah. So they want basically this drop down.
There's no way to do this. So for example, the date thing I showed was the date from parts. We're basically asking you to use a function to make that a date. It's like that's so cumbersome. It's not something that I think is a great customer experience. The report builder where you would use this data set is thinking more in depth about making that quick action to change the format of the output. That's something that we could probably share with them as we just work on a very similar code base. I'm saying is that we're thinking about how to make that easier. The challenge about this, this is all data that exists in HubSpot compared to something like Excel where you can just quickly change the format. The column changes. Everything we're pulling is in your CRM and it has a data type to it. This is not dollars. When we pull this from the system, we're pulling what the system tells us to us. And we're not changing all the data in your system this moment. That's why it's weird for us to format data in a different way than exist in the system. We realize there's really good reasons for that, but something we're thinking about actively. I think we'll have some solution soon. On it. No date to that chair though.
Okay. And then just two more questions here. I may have missed this earlier in the conversation, but I'd like to better understand what are the best use cases for using data sets if we can't leverage data sets and custom reporting in combination with like contact company, object count fields. So Beth, I think this will make more sense to you once we move on from here to actually generating a report. The story is not completed yet. I'm going to mark this question as answered Beth. But if we get to the end and it's not answered, feel free to poke me again and tell me I was premature in closing it.
Her question one more time I can hit the notes.
Mitch wants to know I'm not seeing marketing email data sets in any of my Ops Hub Enterprise clients portal. Is that not out to the public yet? I saw that in your available data sets earlier. So when you're. So when you're selecting your data sources in the first step there's marketing email data apparently in your portal. Mitch is wondering why he doesn't have access to that. Yeah, that one there. This might be a good kit question.
It's not customers yet. We're thinking about beta about this. Basically we're trying to figure out like there's all these questions customers wants me to answer about like their marketing email interactions and there's just a ton of events. Not to belabor this conversation with technical stuff but every time a customer clicks, opens, clicks a link, does X, Y and Z and it's like a new row. So there's millions of rows when we're doing emails and basically trying to figure out a way to make that like a really usable easy source table for our customers and are working through it. So this is my portal where we test a lot of things, not just customers but yeah sneak peek about how we're trying to feel like make that easier to report on your marketing emails.
Cool. I think if you want to continue on there will be more questions but I'd love to give you a chance to talk for a bit before we put more on you.
Cool. We're for an hour, right?
Yes, indeed. So we're about halfway through.
Okay, what should I name this webinar Test? Review. Review step Basically just shows you some information about. Again, someone asked about formatting like data types. We're pulling these data types in the system and we're just helping you understand what this data type is after you create a data set. So move next. Save, save. Got a good question about what's the point of this if I can't make a report? This is where I get into that build a report Again, if you're building report from scratch without a data set, the biggest difference you see here is you see all of the properties in your system and you would not have the derived fields that we made together in this demo. I made a pretty small condensed data set but this could be more. I think their current limits are about 20 different properties in 10 days at Fields and I'm working with legal to try increase that limit for our enterprise users to make it allow you to give more flexibility and make a data set a little bit bigger so you can have more use cases off that dataset. Something you've heard from more power users that they just want more fields like give me more fields. But as I'm making a report I can use any of the visualization types that you would in custom report builder. What is the most intuitive thing to work with? Let's work with bar chart I'm looking at. The question I want to answer from this report would be tell me how much money we're making from our priority deals. If you remember, we defined priority as greater than $150,000 in annual recurring revenue. I think so. I'll say I want to bucket my deals in the X axis in priority. Boom, no priority. And the emoji that works. That's crazy. I got a kid screenshot of an emoji. Okay, so the emoji and then I want to understand the annual recurring revenue in these priority segments. This shows me that in the no priority segment we actually have a pretty small opportunity, about 840k of revenue. But in the mind exploding high priority, we have $10 million revenue. I'm not. This might not be. I don't think my portals in American dollars. So sorry, this weird decimal point here. And then I can use a breakdown field to understand which account executives are handling these deals. So Stephen is accounting for 67% this pipeline of mine exploding deals. And then Chris Tina is about $3 million deals. Or I could use something like a deal name as the breakdown to understand like what are the actual deals contributing to this segment. But the big hopefully the aha moment here is the ability to make a create a column in data sets allows for you to completely recontextualize your data. Instead of looking at your data based on deal names, whatever else on the x axis of this report, you have no priority. And you have priority based on the conditional field you were able to create in data sets. We can also look at something. I'm going to move to pivot table. So just a little bit more expressive to be honest. Here I have all of the deals in priority. I can see all of the deal names, all of the ARR if I want to, I can see the account executives on these deals split the data up by that. And then besides with this I can even see like how many days since creation have been open for. So when I'm looking at this pivot table might be a little busy there's ways to clean this up. But I see a couple of answers to questions. Here are our priority deals. They're all assigned to our deal reps. That's good. In the no priority category, Christina and Steven are only counting for two deals. There's three, there's like however many deals are here are like unassigned, they're in the nether, they must be actioned on. And that's kind of information I'm getting from this. So hopefully that bridges the gap between like what's the point of what's happening in data sets? And it's curation of fields and derivation of new information that helps you reach new answers. And. And then in the report builder you're building it all together. I think you asked me a specific question about like if I can't do counts or aggregations, what's the point of all this? Well, here you have sum, you can do average, min, max distinct count. This is where you do the aggregations or the summaries of your data to get the very specific questions that you need at top level view. Hopefully that makes sense. I didn't use too much jargon. Any questions on this.
I feel like you answered the question. Beth, who asked the question that you've been trying so hard to answer, asked a follow up question, but I feel like maybe you covered it. She asked specifically about a report of contacts by conditional property. What you have here is a report of deals by conditional property. But everything you said would apply to other objects as well, right?
Yeah, totally. Join. We select our source table. If I select the contact table, my data set would have each line representing a contact. In the same way I did something with priority and I said if X is true, label this row as Y. I could do that with contacts. If contact is from region, is from the south region, then mark is high priority. If it's from the north region, we may mark it as like priority two. And then we're looking at a report built of contacts. We'd be able to contextualize it similar to how we're looking at now just with contacts.
Cool. Then Jay has a question here that I think you might be getting to a little later. Apologies if I miss this, but can we group data in the data sets? For example, I have a customer who wants to see the first deal amount, average deal amount, most recent deal amount, smallest deal amount, et cetera per company for all closed one deals. We're evaluating if data sets can work for them or not. So you just talked a little bit about aggregations. Already you mentioned in passing that some of the beta stuff you're going to show has grouping capabilities. I don't know if this is in line with what you're.
The question one more time for me though, like what was the motivation?
I have a customer who wants to see the first deal amount, average deal amount, most recent deal amount, smallest deal amount, etc. So I think for a set of data they want like max, min, average, mean, median mode, all the different things laid out together, would each of those just be a different derived field? I see. So Jay, let us know if this.
Is so basically what's happening here is it depends on what you want to take a summary statistic of. You're asking about a sum, a median, a max, etc. If the median you want to understand is your revenue, I put the measure revenue ARR and then I change the summarization method to be average. As you can see, I think this is actually not very intuitive for many people using this. It wasn't intuitive when I was learning this. If you're not super familiar with BI tools, it's like this is something that is a dynamic field. I dragged this in and I was able to change this to one of multiple summary statistics. Distinct count, sum, average, min, max. But that's all just based off this field. So here we're looking at a grouping of data based on priority. And then I have summary statistics on this group and some average men do max and just account.
Cool. And then Logan asks can we see how some of those aggregations work with the conditional fields?
Yep. So I mean this is a conditional field. It might be not. Let's see, might be account executive is deal owner, but like the conditional field is priority. So I made this conditional field in data sets and it's actually doing the sum of ARR, the average of ARR of the minimum ARR for priority deals that are assigned to Stephen and mind blowing deals that are assigned to. And it's breaking out these in different lines. So he's like, it's answering questions. The question in this line are saying what's the average ARR of mind blowing deals assigned to Stephen assigned to Christina? If I wanted to do some totals here, I could do show subtotals. I could see like for the whole thing, for this whole grouping of Stephen, the subtotal is 691 is the average. So it's basically taking every deal is in these two categories and doing average. So the real aha moment here should be a lot of data analysis happens in the Report Builder and the data sets and the tool is really about prepping your data for that analysis moment. And as I'm going to show you in the beta, maybe prepping it for other places besides the report Builder, but really being the home for data prep and making that prep process visual compared to preparing your data in a property settings page.
Cool. Jay, who was asking about max min and all the things, is also wondering how date could be pulled into this. So if there's a way to see if we were including close date or create date, since that's what you have here, could we see the first one? Could we see the most recent one?
Is this going to work? I have no idea. Let's see.
Do it live.
He's asking about the first one. I probably should put in values. Then let's take out some of this noise real quick. Let's get account executives, let's make this simpler to see if I put it in values, this distinct count, could I do the first one? Min max. Let's see if I apply this, will this give me the first create date? It does. So what you're asking is can you see the minimum create date in a set of deals or set of whatever object we're talking about. So in the priority here again, custom grouping based on conditions. We made this field in data sets priority. I'm seeing a summary statistic on the revenue in this column and then this column I'm looking at the create date and I'm seeing within this column what was the first create date that I can do for your context? The min max. No aggregation and distinct count for date fields. Again, report builder for analysis.
Cool. So that would tell you for create date or close date. The first one. The last one. Jay is asking also now if we could go one level deeper and for that mind blowing deal that closed on 831, is there a way to see what the amount of that specific deal was.
To contextualize what you're seeing here? And the answer is yes. The mind blowing column here is showing you a grouping of deals because I'm using a pivot table. This is actually a summary table that has layers to it. This is kind of confusing. So what I'm looking here, if I click this, I actually see all of the deals that are in that thing we were visualizing. My data is horrible here. So I clearly did a bulk import. They just had a bunch of things831 but let's assume you guys had a real pipeline of flow where there was like one or two deals at 8:31 and only these two are here. You'd be able to see this specific deal. What's notably missing here is like what is this deal name? I have no idea. We can change that. I can put this deal name into the field section and then when I do this drill down, it will show me the actual deal name. This undergraduate open house. The only thing that's missing here that I think is the obvious next step because Jay is getting to the core of problems here is can I actually click this and go right to the record? So can I hyperlink this? And that's something our team's thinking about. Cool.
But the data is there and if there's other things, as long as they're in field in properties somewhere, you could add them as fields here and yeah, also fields.
But I mean obviously we could also just do it here and like you just let me look at it. You can make it.
Oh yeah. True. Yeah. Yeah.
So it's just always depends.
Logan points out that you could add a field or a row or whatever for record id, then that wouldn't quite be a hyperlink, but it would be pretty close.
There's a bunch of ways. My job and the product team is make as easy as possible. But there's a bunch of ways to help you get there. I've even seen customers use a concatenation data sets. We have the ability to use the function concat. If anyone doesn't know that helps you take if I had first name and last name. Can cats like can I just mush these together and make the same thing into a new column? And I've seen people hyperlinks so they can just have hyperlink fields basically. Wow. I've seen lots of things done.
Cool. John wants to know, would it be possible to build a data set to track total pipeline open deal value, weighted amount, week over week to see change.
Yeah. Let's move into the beta because I have a good example actually shows name very much like that. Cool.
Just a minute before you hop in, I would like to mention too that there is the sales analytics tool that shows some of this stuff. So there are a lot of good standard reports in there. John, if you haven't explored, there are deal waterfalls and things that show how your pipeline increases decreases over whatever time.
Period you're interested in.
Steven, show us the beta stuff. For people who love everything custom.
Great call. This is something. It's called the oink oink test because the customer was like if you could do this, I'd be happier than A pig rolling my own crap. And I was like, cool, let me show you something. But this is a custom data set that I made, like in regards to a customer trying to like do something. I think the question they had here was to set the context. What are you looking at? Like they had different pipelines, they had different deals and they wanted to understand, here's the deal amount that we have for today, like $1000. We have forecasted a total of 1500, sorry, $15,000 of deal amount for this company this year. I want to know what is the amount pro rata at this time of the year. So we're looking at this saying, all right, right now we have $10,000 on the year. We want to make a total of $15,000 in November. We should expect to be at this number. What is the percent to target? And they wanted me to show how we can do some weighted forecasts based on different assumptions and conditional logic. I'm going to walk you through this setup and then show you one cool thing at the end. Amount. I just did deal. Amount. Amount. Parada little bit more complicated. This shows how you can really nest some things together and use parenthetical expressions to say, what should we execute first? The ceiling function. This is just because when I did this expression, it had live trailing decimal places and I just wanted to chop it off and make it a round number. That's all this is doing. So if I look inside this expression, what's really happening here is I'm saying 15,000 divided by 12. I'm doing this because I have bad data, but I'm saying whatever your actual forecasted amount is should be probably captured in a property. If your forecast amount for a company is like some number, you know, put it in a property, reference it here. I use 15,000 because I have nothing like that. My portal and just wanted to have some round number. I'm dividing by 12 because there are 12 months in the year. Then I multiply the date part. So then I look at today and I say, what is the month of today? And when I look at the month of today, I'm like, okay, it's telling me it's the 11th month of the year and I'm multiplying by however many months in the year have passed by. Gives me like 11 months times 15,000 divided by 12, which gives me a 13, 7:50. Then what I do now I want to know what's the percent difference like the percent of total between the amount and amount par rata and then just say amount divided by amount per rata multiplied by 100 to get the percentage target. This is a percentage field. I actually this is something I was showing them because I wanted to show them how they can use the beta to take like to solve. They needed to in the moment. I think there's probably better practices here that our team will work to to make it so that you don't have to do this all in data sets. I don't think data sets the best place to do all this. Some of this just gets into best practice around reporting. But I want to show this in the last because like it's something that helps people today and like if it can help people today, you should help them achieve it. So then the last step, this whole like wonky data set is how did I make some sort of like unweighted or weighted forecast? This is an if function. So basically what I did here was this customer told me they had different kind of conditions based on stage of the deal. So if the deal stage was qualified to buy, then they weighted the deal of 50%. If the deal stage that proposal is sent, then they weigh the deal at some higher percentage, like 85%. We're almost across the line. I could nest a third if statement here to do. If the deal stage is close, one boom, 100% and we move forward. But basically I'm showing you how you can just combine all these different things you're talking about with amount. It's just a direct reference. You're using date part here plus the now function. You are using just simple arithmetic here to get this divided by this times this and. And then you're doing some sort of actual forecasted weight based on conditions of the if statements. It can get complex, it can be simple. Like the goal of our team is kind of like do the data prep you need and give you the tools to do it and then try to simplify. Like once you try to give me the tools right now I think we're more in the stage of like give you the tools and then we're going to really start like ramping up on the simplification part. But like what does this do and why is this a beta? I've only showed you stuff that's actually already possible. So let me go back actually, any questions of just the same, I was.
Going to ask that same thing people are asking how do I get access to this beta? But everything you've shown so far is live today in data sets, right? Like all of that is just you're not to the beta yet.
I'm just like heralding all of my knowledge because I have to look at this all day to you guys. But everything here YouTube back data sets. Where is my oink oink data set oink oink dataset. This is the actual data. It's when you go into the manage page for a data set that you've saved. We're giving you the ability to save a field back to a property. This stage weighted forecast. I have the way this will work by the way. I'm not going to do this for this property. But it's literally so simple. Take the data set, field name, amount. This obviously already exists my system. It's just deal amount, create property name the property, save, boom. Done Singer system. It's like that simple. Like you're just creating the label of what you want the property called make a description. If you want to save a map, you're good.
And then all the just based on what data is in the field. It will choose the field type and everything what object it's on.
All of that is automatically field type that you already can see here. And you know what it is. It's the data source in your. I'll show you. So actually look at this but like stage weighted. I've done this exact thing and then for this I can click this in the property settings. It's a deal property. It just exists in your system. Just like a property. One caveat here, two caveats. This is a beta and one of the ways we scope this down to start, kind of like getting proof of concept out there and getting feedback is data sets built on one data source can do this. The very beginning of this, I showed you how you can do all these joins and stuff for this if you want to do this. Dataset one source table. Second thing, this is using a snow. I don't know if technical deals matter. We're using a system that just has. It can do a lot of fancy query stuff and it's expensive. We've reduced the number of times refreshed. Most properties change every single time there's a change in any of the underlying properties or whatever. Like rapid, rapid, rapid. It could change a thousand times a day. We're refreshing this twice a day to start and seeing how people use that, seeing how they act. Does that reach their needs? Is it good enough? So our idea is that this is for stuff that you probably don't need to filter your ticket pipeline by. So you're for your support team. It's probably a field you need for like analysis that you probably aren't doing more than twice a day. Like, no one's doing their forecasting more than twice a day or even like once a day. What can this do for you? Like, that's the exciting part with this. This is a property. You can do anything with this. You can use it in a workflow to create tasks based on the weighted amount I just said you can use it in filters for your boards. You can make lists off it. Like, you can use it like a property. And what's really exciting to me, like, because I just think, like, we have all of its ability to create these rich, like, data fields and the problem that we have, the customers, like, don't really ask for. But in my mind, I'm like, man, wouldn't it be cool? We use it everywhere. Like, this is it. So I have another example for a superstore that really had, like, I can walk you through this more, but it's the same principle. So any questions before I go through one more example of this?
People are saying, oh, that's awesome. This is amazing. What? Mic drop. That is awesome. So people are getting it, I think, just so that if you create one of these properties, you don't have to go populated anywhere, right? Similar to a calculated property. It's just going to be populated on all of the relevant records, but just twice a day instead of continuously. That makes sense. Let's see what's in the Q and A here.
Okay.
There are kind of a lot of general data sets questions I would rather let you finish your list you want to cover. These need to be answered asynchronously afterward. That would be fine. But we're at. We've got like six minutes left here. Okay, while. While. Last bit of your presentation here, everyone. I'm just dropping into the Chat SurveyMonkey link. I would love any feedback you have on this session. Give it. Give me a rating out of 10. Leave any qualitative feedback you have. I'll pass the nice ones on to Steven and take the mean ones.
Pass the mean ones on to me. I don't know. Pass the mean ones on and the nice ones just pass me their hub IDs and I'll help them get in the beta.
Cool. All right.
For this one, it's a different portal. I don't want to spend that much time going through this. But basically it's a superstore. A lot of different systems use superstores to help you understand just like a bunch about their data in this superstore. It's like a Commerce. So there are products we're selling, they have sales, there's profits, products of categories. The people that bought them are in cities. There's order dates. It's a really rich data set that we use internally to try to make it easy to understand some concepts. But I want to show this. So imagine you have this E commerce store and there's a product name. You have all this rich information. What's a practical way of how you can use this data set? I want to walk through this with you. I have it cool. This way I can actually use this name. Overdue shift date. What I want to understand is how many of these orders are going out to customers outside sla. Before I worked at HubSpot, I worked at Walmart E Commerce that owned a P and L products. And one of the things we have SLA is our ship dates where you can like we were trying to give things people like 2 to 3 days, maximum X number of days. Anything out of SLA was like a problem. We had actual in it from each like P and L owner. So I want to know if the. How did I use before? I think I had to use a nested date diff. So if the date diff. This is me trying to really tie things together. So date diff. So if the days between the order day of a deal order date of an order, the ship day of an order, Oops, I didn't even break this bracket. Ship date exceeds something like this is bad data because it's a fake store, but let's just call it 50. Then I want to say out of SLA, otherwise in SLA and so theme that kind of pulls together. I'm taking the date diff, the if function, and I'm saying inside SLA or outside SLA based on how many days it took to actually get to the customer. God hopes that you're getting your stuff to your customers inside 50 days. So there's why everything's like in SLA here. Or actually because I get it backwards. This should be inside SLA is actually saying that it was above that. And. And outside SLA is this. I think I might have gotten it backwards. If it's greater than 50, then it's in. Nope, I got it wrong, guys. But it's outside SLA Inside sla, I'm gonna leave it. And the point here is like, what do you do with this, though? Well, one thing you do is like you send it to your P and L owner or whoever actually owns the product categories for this and you send them Say, hey, here's your report. This is how many of your orders would, which I just showed you back at the customer port builder, are outside of sla. You're especially missing SLA in the categories of like, office supplies in Los Angeles. That's probably because your distributor is just like backed up beyond belief. Like, COVID ruined their supply chain. You need to action on that. But also you want to do something that's more automated. Like, I want to go here, I want to review this. Save, save, save. And I say, okay, overdue ship date, create property. Overdue ship date, save. Oh, I should have called it dataset. That's confusing. That was not what I meant to do. But then you go to workflows and you say in workflows, create workflow from scratch. I know, I'm just like diving into workflows. I have no idea if anyone's familiar with workflows or how many people are, but I'm going to try to go slow. You're going to pick the object makes sense. I did this in a custom object called orders. We could do this on deals, companies, whatever makes sense for how this stays in your system. Order next. And I say set up trigger order. What did I call this? Overdue ship date. If it equal to. Say if it is known, apply filter, save. And then I do a quick branch. If then order overdue. Oops, Overdue. If it is equal to. I believe I said in sla, you get the point. Actually, this is wrong. I need to do it without the quotation marks in sla. And then what you can do here is in sla, you do nothing. If it's I have sla, you send them an email saying, so sorry, like, here's a discount code for your next time shopping with us. Something that's actually operational. And the point that I'm trying to make here, like, hopefully this is like, ooh, a cool moment where it's like, the data set allows for you to govern your data definitions, be super expressable using IF functions, date diffs. And that allows you two things. One, be sure the data you're using is actually accurate. This is all being governed in central place to the data set, but it's also powering the reports that you're using to provide insights to your team. And it's powering all the actions that you're using in the workflows. If you want to change like these different things and keep them in order, you go into your dataset and you change your definitions, make adjustments, make flow to the system is essential thing that's keeping the logic of your data intact. That's all I got. I know I'm one minute over.
No, that was great. Thank you so much. For those of you who have asked questions in the Q and A that we haven't gotten to, I have collected them together. The reason you are hearing Steven's slack sound so much is because I'm slacking them to him for safekeeping. We'll work through those, we'll get them answered. I'll include answers to those in the follow up I send up send out after this event. Steven, thank you so much. People seem to really like data sets. Now, if they're interested in participating in betas and things, do you have instructions for them on how they can get involved?
We did a private closed beta for this first round of what I showed, so I would need the hub IDs before we just make the. What you call it. I know customers can sign up for them now, but it's not open to that yet. So I'll just need hub IDs and emails.
Okay, cool. Friends, if you're interested in that beta, I'll be the gatekeeper for this. There's my email address in the chat. Feel free to hit me up. I can connect you to Steven. Send me your hub IDs and we'll get it sorted out.
Cool, thanks Kyle. I hope you feel better.
Yeah, thanks and have a good day.
