Roadblocks Unblocked: Maximizing Your HubSpot Data's Potential with Coefficient

Foreign. Thanks so much for having me. First of all, honored to be joining a hug event. Been a big. It's my first time actually meeting Kyle in a call, but I've been a big fan on LinkedIn of all of those videos. I definitely would not be as up to date on HubSpot if it wasn't for you. So it's awesome to be sharing this platform and really excited to talk to some of your audience about Code Coefficient and how we can make life a little bit easier for HubSpot admins, data analysts and operations folks in your company. There we go. So our agenda today, we're doing some quick introductions and then we'll jump right into it with a demo of Coefficient in action with some real life use cases from many of our companies that are using HubSpot to import data into Google Sheets and sometimes back into HubSpot as well. We're going to go left, left to right. I'm guessing everyone on this call knows you by now, Kyle, but did you want to say a word?

Sure. Hi, I'm Kyle. I talk about HubSpot stuff. Yeah, Frank, take it away.

Awesome. And yeah, so I'm a senior manager of the product specialist team here at Coefficient. So I've been working in data and especially in spreadsheets for 10 years. Actually got a very early start to HubSpot. I started my own startup 9 or 10 years ago and was using HubSpot as our own team CRM. So I was doing everything from the admin side to some sales myself and all of that good stuff. Unfortunately moved into the Salesforce world. But don't worry, I came back. I was working for a Salesforce implementation partner Traction on Demand, constantly exporting Salesforce CSVs in this Excel and then later Google Sheets at that time desperately needed a tool like Coefficient to save my time exporting CSVs. So then I was very lucky. Almost three years ago now I discovered Coefficient when they launched on Product Hunt and I was just checking out this tool because I thought this could save a lot of time for myself and my own job and I was just so blown away by exporting. Like how Coefficient compared to exporting CSVs or compared to other tools that I was trying that the team here convinced me to quit that job within a week and join Coefficient and it's been a really fun journey here since then. Obviously one of the best parts is coefficient uses HubSpot as their own CRM. So I'm back on the good side and basically my job at Coefficient is to represent our users and customers because I speak the same language. I've been in operations and data roles before. I understand the pain points people are coming to Coefficient to solve. So I love working with a lot of our larger customers on strategic use cases giving demos like this. And of course I end up doing a lot of our internal analytics and reporting at Coefficient because our team is spreadsheet wizards internally. Deanna, I know everyone here knows you as well, but since the picture is here, you want to say hi?

Sure, sure. I mean I'm Deanna Giloff. I'm Kyle's sidekick. I love community adoption, all of those good things. So it's my job to bring amazing speakers like Frank and Coefficient to you guys. So enjoy. Take it away Frank.

Awesome. So quick introduction to Coefficient for anyone who hasn't come across us so far, we are Our core products are free extensions for the Google Sheets and Excel marketplaces, so you can go to either of those marketplaces and install instantly. We're a product led growth company so we make it as easy as possible. You don't need a credit card, anything like that. Just come to our website and click get started and you'll start seeing your data live in a spreadsheet in less than a minute. You can use Coefficient to automatically import data from many of these systems I'm talking about. We still work closely with Salesforce, also HubSpot, Tableau, QuickBooks, Shopify, and as you can see on the slide, 50 other data sources. And very recently we've released a custom API connector. This actually lets you and your teams also connect to private internal tools that your company might have set up. As long as you have an API accessible, you can get that data into your spreadsheet, set it up to automatically refresh every hour, once a day, whatever you need. We'll get into this in the product demo, but then we've since innovated a bit, basically just trying to make the spreadsheet experience better for all of you. So we've introduced some GPT functions you can use in your spreadsheets. If you want to get slack or email alerts from your spreadsheets to those platforms, any of that's possible. So really just bringing spreadsheets into the 21st century is our goal. Yeah, this is largely what I just covered. So what you can do Connect to any of these systems. Very user friendly, easy to connect. You basically just log into HubSpot or whatever tool it is with your credentials once and then you'll never have to log in again. You can just set up everything to be on a refresh schedule and here's a little bit of what we're going to be covering today. So some common use cases we see with HubSpot there's data cleanup. So of course you can import data from HubSpot to Google Sheets. I mentioned briefly. You can also push data back into HubSpot so you can run spreadsheet formulas, maybe to clean up the data, maybe to run some calculations and push those values back to HubSpot properties, which is pretty cool blended lead scoring. So it's very common to want to combine multiple data sources. I'll do an example of putting Snowflake data into HubSpot. We'll go through that GPT powered lead scoring, HubSpot write backs and then we have some pre built spreadsheet dashboards that will connect directly to your HubSpot. Org and I'll show you some of that as well. And we'll keep this live and interactive. So I know Kyle, Indiana will let me know if there's any good questions coming in. And if you folks have any real time questions, feel free to interrupt me. So to get started, as soon as you install Coefficient for free from the marketplace, we'll always live in this top Extensions menu and you just click Launch to start getting your data into your spreadsheet. There we go. You can see on this spreadsheet I already have a handful of imports already set up, but let's start with a new one from scratch. So our core action here is import from and obviously we're going to choose HubSpot. We'll go from objects and fields and this will basically let us get any object from HubSpot, whatever properties we want from that into our spreadsheet. So in this case we can do contacts. As a good example, if you have custom objects, those would also appear in this list. Engagement objects are also there as well. So if you want to get tasks, meetings, any of those kinds of details, live in your spreadsheet. Sticking with contacts, we'll just select a handful of fields here. Everyone knows it's a best practice to include your record IDs so that you know what you're updating, especially if you're going to do write backs. And then email is obviously useful if working with contacts. And I'm just going to pull in another handful of these fields that I know will help me out a lot. With this demo we're going to pull in the owner id, Contact, Owner Name, Create, date So we have a great date example. We'll get the user's company name so you can also get any properties from associated objects, of course, like company name in this case. And then I've added in some custom field app usage and that's the one that we're going to try to update that value directly from. Snowflake import, which is also in this spreadsheet. Again, meant to be very user friendly, so I was just searching to grab fields. It's also drag and drop, reorganize things the way you want. Apply filters. If you're looking at date filters, it's very dynamic like you would expect in HubSpot filtering. So dynamic date ranges are all available, for example. But if we're happy with the setup, I'll just click import and here we'll see coefficient will automatically insert a new tab into our spreadsheet. So there we go, a new tab named after the object. So contacts in this case. And we're immediately prompted to set up an automatic refresh schedule over here on the right quickly I'll show this is what every coefficient import will look like. So if you're using other data systems, we'll always have a similar look and feel so that you and your users can navigate very easily between different sources. So we have all of our data coming in here, metadata, but when it was last updated in row one, everything coming in down below over here we get to set up our refresh schedule. You can do it as frequently as once an hour, so that can be great if you want a really real time report internally. We often do this daily at something like 5am, so it's always live when we need it, but it's not constantly changing throughout the day. So let's just do that. As a good example, once a day, 5am, set that a refresh. That's how you get data into your spreadsheet. And of course there's tons you can do already with this. A lot of our users are not using the write back functionality. They're only trying to get data into their spreadsheet. And then they're building out some really fancy advanced dashboards, pivot tables, whatever kind of analysis they need. And the benefit of course of having it connected is then you can just refresh this HubSpot import and your pivot tables, dashboards, other calculations you've built on other tabs, everything will always be live as long as you schedule those refreshes. But in this case, I've heard a lot of the group is excited about write backs, so let's dive into what a real life example of that will look like. We're going to start. We have this app usage field which is already filled in with numbers. This is meant to represent how many times a user has opened our app in the past 60 days. Let's say. So if we wanted to calculate this based on Snowflake and you can see I've preconfigured a Snowflake import here. When you're importing from Snowflake, this will basically just look like a RAW SQL query most of the time that you're writing, but you can end up getting a nice clean import looking something like this. And now what we get to do is we get a benefit from the beauty and simplicity of spreadsheet formulas to kind of join these two data sources together. So over here, if I want to add app usage, I'm sure you all are familiar with Vlookup. Small side note is I've completely killed Vlookup from my formula vocabulary now that Google Sheets has offered xlookup. If you haven't tried it yet, highly recommend. It can do everything Vlookup does and then some. So we're going to do it works very similar to Vlookup though, if you're following along. We're going to look up this email address, look it up on our Snowflake data set, which of course has a similar list of emails. And let's say we want to get this month number of app opens as the field we want to return and we'll drag this value down. So like that I've filled in this app usage field and now if we want to push this back into our CRM, maybe we want to run workflows based off of this value. Maybe we want to reference this value in emails that we're sending from HubSpot. Let's look at what that looks like. So that'll bring us into our second core feature, which is export to and this is our write back functionality. So we're writing back to HubSpot, of course. And you can use coefficient write backs to either update existing record values or you can even insert new records as well to any object. In this case, we're working with our Contacts tab. Our headers are in row two, so let's click Continue. Of course, our object we're working with is still the Contacts object and we're going to run an update as opposed to insert or delete process. Since we're working with an existing coefficient import tab, we've already done the work of kind of mapping all your fields together. You could of course change this around if you wanted to map one of these values or pushing back to a new custom field. But in this case we have the field app usage and we're just writing back to that same one. So I can say save export. I use an entire spreadsheet. In this case, I know that there were some. Some of these emails weren't matched, so these will have a hard time writing back. Normally in my xlookup function I would probably put a if error, you know, just send a blank value. But in this case, let's just say we want to do a specific set of these. I can actually just simply select cells of any rows that I want to push back. And there we go. I've selected 19 rows. Those are the ones that we're going to update. I'll just click Update 19 and it's going to push this right back in. I see we got a question coming in about write backs for custom fields. So Coefficient can't create new fields for you in HubSpot. So you'll always want to create field in HubSpot first to get your HubSpot admin to do it. But you can definitely write back values to custom fields that you have there. Perfect. So you can see that Write back was successful. We get this nice little confirmation output just showing the timestamp and the result. If there ever was an error, this result column would show that error message and it would be the same as if you tried to make that change in HubSpot itself. And maybe you have a validation rule or it's a pick list field, then you didn't put in the exact right text. Something like that. Excellent. Since we're going through real life use cases today, I thought what better of a use case than what Hannah, who's also in the background there on the chat. Hannah and I had to solve a little tricky challenge this week and Hannah asked me if there was a good way to do it and I said, oh, have you heard of this great tool called Coefficient? So this is just one of these nice examples where it's just so simple. I know there's some very smart HubSpot people on this call, so you all might have a better solution of how to do this directly in HubSpot. And that's fine, but this is kind of the point is, even if there is a great solution there, sometimes, if you're more comfortable in a spreadsheet like I am, this could be a great, great workaround for you. So the challenge Hannah and I were tackling, we wanted to add. We just added a new property to our HubSpot contacts. Again, real life example called first contact from domain and basically we want to know all the contacts for a particular company in our HubSpot CRM who was the very first contact that signed up for our product, filled out a form on our website. We whatever they did, we want to know exactly who that first one was. And especially being a PLG company, we love to get into a domain and then we kind of spread virally once people see coefficient in action. So it's very important for us to track whenever we get a new user from a brand new company. So Hannah's amazing at marketing. She did the heavy work in HubSpot setting up a beautiful workflow that figures out if a new contract gets created and at the time they get created the number of contacts for that company, the associated company is one, meaning that single contact is the only one, then they get tagged as true and if not then it's false. So that's perfect for us going forward. We'll have that field covered. But we have 190,000 or so contacts historically that we needed to update this value for. And I couldn't think of an easy way to do it directly in HubSpot. So what I did was I imported all of our contacts into a spreadsheet. Not a problem doing 190,000 of them at a time that needed to be updated, added in our new field and then just use the beauty and easiness of spreadsheet formulas once again. So in this case, and some of you I know will follow along better than others because we're getting into a bit of a countifs scenario here, but basically I have all of my contacts coming in, they're already sorted in order of creation date or contact id. Both would work. So then I basically just want to know, is this row, row three, is that the first time that company name HubSpot shows up in our import? So my range is going to be I'm always going to start my countifs from F2, but it's always going to be going down to the current row we're looking at. And I just want to know how many occurrences of F3 are there between F2 and F3 or whatever the current row is. And if it's one, it's a perfect match and that means it's the first contact. There we go, fill that one down. Many trues, I think. Okay, we're working with a smaller data set here. Obviously we would have some falses as the new sandbox business. So great News, all of our, all of our companies are new in this case, so this is a nice simple way to do it. In our case we added a little bit more complexity. We also said we want to ignore non marketing contacts because we really only cared about the marketing ones in this example. And once we were happy with this setup, we did a new export. And there we go. Coefficients already detected that this new field I typed in is matching a field property that I've created in this HubSpot sandbox as well. And so we'll do an export again. We'll do just specific rows on the sheet. Do this handful this time and I'm doing 15 rows at a time to make this demo a little bit quicker. But again did hundreds of thousands of rows and this was a really elegant solution for what we had told there.

Frank, we have a question. Is there any limitation on API calls made to HubSpot or HubSpot impact pricing if Coefficient does frequent API calls like this?

Quite confident. There's no. No charges for API hits. I've never heard of any of our customers hitting that. There are some Kyle, you might be on or. And here you said something there.

Sorry, just side conversation here.

Okay, good. There are some API limitations that we're pretty transparent about importing from most objects there's often a 10,000 row limit. The exception is using the contact list object which has no limit. We love it. So that's what I use for my hundreds of thousands of rows example. But we're actually very excited. We haven't told anyone yet this, but we're releasing HubSpot V2 connector in a week or so and we've actually built some fancy workarounds to get past some of those 10,000 row import limits by essentially batching some of your imports together. And then on the right back side, like the hundreds of thousands of rows example I did, there's no hard limit. You might get some Google Sheets processing errors. That's why I did that one in batches of hundreds of thousands. But really that's usually more than more than enough. Oh, we might have some more questions coming in.

Is there a way to field to avoid HubSpot users making changes while working on it in Coefficient?

Good question. Yeah, we get these questions about permissions a lot because write backs, depending on who you ask, are either the best feature in the world or a little bit scary. So first thing I'll note is whoever is using Coefficient, they'll be logging in and authenticating with their own personal HubSpot credentials. So anyone using Coefficient Writebacks first of all can only make edits to properties that they would be able to edit directly if they went into HubSpot. So it's exact same permissions in that sense. Otherwise they're so the first way to do it is to Change the user's HubSpot permissions if you don't want them to be mass editing something. Otherwise. What we see a lot is for some of our larger enterprise customers, we'll enable this write backs feature for only certain users. So maybe you don't want every sales rep to necessarily have the power to make those write backs. So that's another way to manage it.

Over in the Q and A, Ali is asking about exploring the GPT actions. Hannah sort of teased it in the chat. I don't know if that's something you're.

Getting to, but I just want to.

Call it out as something people want to see.

Love the enthusiasm. Yeah, I'll make sure we get to that. Definitely. Yeah, we're excited about those features so we won't miss it, don't worry. Awesome. Some other quick examples so we won't do all because I know everyone wants to get into GPT. I'll just kind of highlight some of the other areas you could play with with coefficient write backs. So besides doing these simple write backs to properties, you could just as easily do a write back to a field like owner ID and this will actually reassign these contacts to a different owner in your org. So here I have a list of owner IDs. I could either just take these and enter them as I please over here and then push this as a writeback. More commonly, what we see is folks writing spreadsheet formulas here. Maybe there's some logic based on how you're assigning territories. For example, so maybe if this company's country is in a certain country, assign it to owner X, otherwise owner Y and so on. But this will work exactly the same once you push that right back. And then besides doing these kind of vlookup or countifs functions, you can always just type in formulas again so we see this often. Another common use case is for data cleanup. So maybe you just want to find write some formula that'll find any close dates that are in the past and update those dates to be seven days in the future. Or maybe depending on the forecast category of a deal, make those dates some point in the future relative to that and then we will start with a little bit of GPT now, might come back to GPT later in this case. What we're going to do is contact lead scoring and this is a really cool use case. I saw a customer doing something similar to this, so I thought it would be something fun to show off. Here we have another HubSpot contacts import coming in, fairly similar set of fields, couple extra ones to make this example better. So now we're also pulling in the industry, the state or region and the country of the company, as well as the company name here. Now what we're doing, we're going to use a few different coefficient GPT functions to run this process. Basically what we want to do is look at all the information we've imported and again, this would be even more powerful if we had some custom properties in here that really matter to your business, maybe something like a lead score or product usage metric. But in this case we're working with what we have and we're going to try to turn all the information in this row into a score from 1 to 10 on how good of a ideal customer profile ICP is. Each contact in our import. So to get this up and running on this other tab here in the background I have this ICP details and we're going to do. We're going to. Basically we'll use Coefficient as the example, but we're going to get GPT to actually write a value proposition for us. Obviously in the real world, I would probably want to write this myself just to optimize exactly what I'm feeding to the GPT engine. But here we can say write a value prop and explain the main product or service sold by this company. Then again, since we're working with spreadsheet functions, this GPT function from Coefficient and as soon as you install and launch Coefficient on any spreadsheet, this function basically becomes available as well as a few other GPTX functions. And since it's a formula or function, we can use regular spreadsheet tricks. So I can put some quotes here for my main prompt and then I can put an ampersand symbol and then I can actually select a cell. So now I can choose this domain coefficient IO hit enter and here coefficient's working with OpenAI's API. So this will be very similar to if you're talking to ChatGPT directly and sent it that same prompt, it did a pretty decent job and and then did a similar formula over here to convert this value prop into what our ICP definition might look like. So what types of roles are we probably targeting and any other information that GPT thought was important based on our company domain and our value prop so looks like a pretty good match. We do work a lot with operations managers, data analysts, CTOs. Yeah, I'm pretty happy with that match. So now we'll go back to that contacts import and we're going to tell it based on this definition of our ICP. How does each contact rank on a scale of 1 to 10? First of all, I already wrote this summarizedetails function not quite necessary but it's kind of an in between step to keep things clean and organized. It's just going to reorganize our fields here and put labels such as industry location, company name so that the ChatGPT engine knows all the data set included. We can drag that down if we're happy with it. And then our ICP score over here, I've already written this one out, a little bit of extra parts to it, but basically it's going to convert the score into a score from 1 to 10. If you're very explicit with GPT, tell it only a number value, nothing else. You don't want any text or story about the number it comes up with and it's saying how well does it match that ICP details definition on the other tab. So this first contact was an 8 and I just drag this down. It'll run all of these into the GPT engine, do that nice little lead matching for us. And now this can be something that we can actually write back to HubSpot. So if we're happy with how we've set up these GPT prompts, we could send these as final scores back to HubSpot again, great way to run workflows off of it. Include that in email, send different emails based on how well someone scores anything like that. What's really nice when you're using this, an example with either GPT or we did that Snowflake example earlier is since you're going to set everything up to automatically refresh probably every hour, every day, whatever it is. You can also set up your Write backs to HubSpot to automatically export. So a nice way to do it is maybe schedule all of your imports to refresh once a day at 5am and then schedule your exports to automatically push that data back to HubSpot once a day at 6am, something like that, so that your data from all of your sources is always live and then you don't even have to open the spreadsheet so if you get sick you're on holiday, that process will keep running, it'll keep piping that data back into HubSpot whether you're online or not. Obviously there are cases where it's a good practice to get that, you know, if it's from Snowflake, you might want to get a pump directly from Snowflake into HubSpot. But if you don't want to wait a week for a data engineering team, you want to be able to troubleshoot it yourself, which is what I love to do. This can be a great, great way to get some kind of pipeline set up in a few minutes rather than a week. I see lots of chat. Deanna and Kyle, you guys let me know if there's anything important I should.

It's mostly side conversations joking about ChatGPT being a little too chatty. When you want just a number, it gives you more than just a number.

There are a few Q&As. So one was can this tool help in deduping contact records and merge records?

So merging records is a really good question. That's something we haven't tackled yet. I don't believe it's part of HubSpot V2. That would. That could be interesting and I'll note down that feedback. In terms of deduping, there's a lot you can do, so HubSpot often makes that fairly easy for you. But if you do want a little bit more control over the process in a spreadsheet, you could do that using. Again, this would be with our export feature. You might have seen there was an option here to delete records. So yeah, we're going with contacts too. And then instead of update, I could have gone to delete. So here I could write some spreadsheet functions that would calculate if this is a record I want to delete, mark it as true, otherwise false. And then you kind of have control over the logic there. So you might say only if a company has zero contacts or and zero deals, then mark it as true and then you run that simple delete process that you do. Or maybe you'd also have the function check if there's already also a company with that same name that existed with the earlier Create date, you can definitely set that up.

Awesome. Then I know we have a little bit of time for Q and A, but I know we're also kind of getting close to time and we have quite a few questions. They also ask if we can help with formulating conditional logic as well as replace for data sets formula and write back to HubSpot in case the client's not using HubSpot. HubSpot Ops Hub.

Was that all one question?

Yeah, basically, they're trying to use conditional logic in their spreadsheet and write that back to HubSpot since they're not using HubSpot Ops Hub, which you should be able to do.

Yep. So you can write a lot of conditional logic. I mean, the basic way to do that in a spreadsheet. Many of you will be familiar with IF function as the best way to get started with conditional logic in your spreadsheet. You could write any kind of function here that would have different conditional logic to set a value based on an ICP score or based on product usage number. A lot you can do there. And then you just want to make sure that the final output is going to match exactly the expected value type that you need for that property in HubSpot. So whether that's a number, true and false, yes or no, Pick list value. I just want to get those to match up. Cool.

And then Karen asked. Basically she has a specific use case. She wants to create a report, a line graph that shows the percentage for each month for MQLs that became SQLs of the contacts that became MQLs in that month, which is the percentage that become an SQL. She wants to write that back as a percentage to use in HubSpot reports.

Cool. Yeah, that's a good thought. You can definitely do something along those lines. I probably won't get too deep in this example, but there are an interesting set of properties that I think a lot of people on this call would like, so I will show them here. So we're doing an import from. That'd probably be with the deals object we're talking about in this case. So we'll jump to deals. And a good way to set up any of that kind of funnel logic of how are deals flowing from one stage to another is you'll have all these fields that look something like date entered and then there's also date exited and these automatically exist for any stages you have. So here you can get an import of every single deal. What date did it enter? You know, negotiation. What date did it enter? Closed 1. Any of your other custom stages. So this can be a nice way to build that kind of funnel logic. You could write something back to HubSpot. I'd have to think a little bit more about, but I guess it would depend on where you're building that chart and how exactly you want the final value surface. But that's something our team's happy to help with. I think my. My email will be included somewhere, so if anyone wants to chat about that, I'D love to think through exciting use cases.

Yeah, and there also is outside of. Well, I guess I'll go to another question. So they're asking if we integrate with data enrichment tools. Ali asked. Or is there a workaround for leveraging us for data enrichment?

Yeah, we don't particularly work with data enrichment tools just yet. If you're thinking of something like fivetran or dbt, those are definitely something that we'll be exploring on a roadmap. Otherwise there's often like an underlying data source that those are connecting to. So whether it's like SQL databases or Snowflake or Looker, we connect to all of those. And then some not really considered data enrichment tools, but like Tableau, you can also import from that as a data source and there you might have calculated fields, which is somewhat like a data enrichment example.

And then Michael asked, would it be possible to score form submission data in terms of how likely it is spam? So I'm sure you could use your GPT functionality for that. It would just be about your prompt, right? So probably play around with that prompt in ChatGPT itself really quickly, figure out what works here and then pull that prompt into coefficient.

Yeah, that's a great way to do it. And then if you are getting those contacts added in HubSpot already and emails are going to them, you'll also be able to pull in fields for the contacts object like number of emails bounced, something like that. So you might be able to add some other logic using those properties as well.

And then the final question was Lindsey asked, would the GPTX function work with activity data for deal scoring?

Yeah, it definitely can. It'll work again largely similar to if you're talking to ChatGPT directly. So there is a little bit of a finessing as people are discovering in the chat and talking about. But if you can get the prompt to work and make yourself confident that it's repeatedly giving you the values that you expect from it, then that's definitely a great use case.

Awesome. And then the final question, we got all the questions Pete asked. Can you import custom property options as an object? For example, a certain property has a ton of dropdown options. Import those with label and internal value columns.

Just going to reread this question.

So basically in HubSpot you have the label and then the actual internal value of that field. So can you import property? Yeah, I mean we can.

So yeah. So. So by default we'll import the label or like the visible value that you would see in HubSpot. So if you have like an internal backend name for different property values, but currently we don't show those. We show the same value you would see in HubSpot directly, but you can.

See it says like the option. So for example, certain property has tons of drop down options. So you'll, you'll see that property and the value for that option for each contact or deal. And then you could have, you can, you can also filter the import. So if you want to filter on a specific property within that drop down you can. Or you can just filter after and if you want to do charts on all of that data.

Yeah, definitely. Awesome. If we have a pausing questions, I'm going to fly through a few more features that I'd love to showcase today first. So I'll show you a couple team features that I think would be really nice. I know many of you obviously are HubSpot admins, so you're often building not just for yourself but for other teammates. There's a couple features that I always like to show off here that work great for teams. So if we open one of these imports that I've already set up, you'll remember I already went through the work of choosing all the fields I wanted. You know, maybe setting filters or sorts. It's very common that you want to import the same batch of fields every single time or you might want to apply this exact same filter every time to filter out your test opportunities or filter on your own sales region, whatever it may be. So I have the ability and coefficient here to basically save this import as what we call an import template and this will make it quicker for myself or anyone else to get started next time. So up here I have all these options related to my import. We're going to save this as a template, give us a name so I know what we're working with. Frank's contacts description. Great. Obviously if I'm going to share it with my team, the ability to keep this just private to myself, share with anyone in my company and basically what we're saving here is just this pre configuration of a set of fields and filters and maybe sorts that we always or often want to apply. So if I save this as an import template now, it's just a little bit easier the next time maybe someone new for my company signs up for coefficient and they want to create their first import, but they want to make it a little bit easier. So when they go import from HubSpot they'll see this import template. Frank's contacts this is a starting off point again so now they can come in here, they can rearrange these fields. Maybe they want to add more fields, add new filters, remove filters, they can do any of that. This is just a quick starting point. So you're not always picking and choosing the exact same 10 fields every time you create a new import from the same object. So that's our first nice team feature. Another one I always like to point out is since every coefficient import is tied to a unique tab in your spreadsheet, not only does that keep things clean and organized, but it also gives us this power where if I now want to maybe duplicate this Contacts import, maybe now I want one tab for my region, another tab for a different region. If I simply duplicate this import, and if I have Coefficient open, you'll see in just a couple seconds, we'll have a new import here in our coefficient settings called There we go, Copy of Contacts. I can then rename this New Contacts. Maybe I click in and change some of the filters or add new fields again. It's a great way to be able to quickly duplicate all those import settings. And in that exact same vein, you can also copy an entire spreadsheet. So I really love this one because sometimes we'll build a dashboard for one sales rep. We want another sales rep to have access to that. Or, you know, I want to share some important dashboard I built for the CEO. I want to give a copy to someone else in the company, and I don't want them to break the CEO's copy. I can quickly create a copy of this entire spreadsheet. It'll duplicate all the imports, so those can have their own separate refresh schedule. Just a great way to build more quickly. And yeah, a final note that's also related to that is since we are working so this is all in Google Sheets for the demo today. But you do most of what I talked about in Excel. We've had an Excel connector since January now. But one thing that stands out for Google Sheets is they have an amazing version history. This isn't even a coefficient feature. I just always highlight it because it works so beautifully with live imports. Google Sheets always keeps track of every single edit you do on your spreadsheet or anyone else does. So when this is powered with imports, this means you can open version history and come over here in the right sidebar and choose any previous version. And since we have this coefficient import refreshing once a day at 9am, that gives me the power to come back here and say, what did my dashboard look like as of June 1st at 9:00am or any previous date. And then I can make a copy of it, even restore it if I want. I'm sure many of you have come into work on Monday morning and your sales forecast has changed by $100,000. No one has any clue what happened, what deal slipped. This could be a great way to set up like what did our dashboard look like last week? And then maybe write some formulas to compare what changed. Okay. And then final core feature. So we have import, export and then automations or alerts. So this is if you want to do something with your data after you've gotten into the spreadsheet. Super powerful tool Automations right here. And we can actually create alerts to either email or Slack. If you're using Slack, definitely recommend doing that since all your employees are probably already living there all day long. You can get any data that's coming in with coefficients sent to Slack, private channels or public channels. Do create new few examples here. So the first one is a daily or weekly screenshot. So this could be a coefficient import tab. An even better example actually is we'll jump tabs here to this fancy dashboard. This is part of our pre configured sales operations dashboard templates that are available for free on our website. We've predefined all the HubSpot fields so you connect to them and it'll instantly be set up in about 20 seconds. But here, this is a great example to do an alert because this daily or weekly screenshot, many people use it on coefficient import tabs. But this can actually be on a dashboard like this. So I could do the entire range. I could say this entire dashboard, send a picture of it to my sales channel or I can choose a specific range. And what's nice with this range is it'll actually also send a picture of any images that are overlaid. So we have some charts here. So I can choose some range like this say done coefficient will turn this range into an image file and send this Again I can choose so I can say once a day, once a week on a given day of the week, let's say Mondays at 9am I want this sent to email as an option. Again great option here is to do Slack instead. So we can do Slack alert demo channel. I could also do this to myself if I just want a private message that I should take a look, I can do any of that. Click next and we'll get those images sent. Great way to kind of start a conversation in the sales channel, for example, because then people can reply to the thread of that image. About what changed. That's the first option is daily weekly screenshots and then there's when data changes. This one's quite powerful. So again here we'll choose a range but this time it'll be usually a smaller group of cells and it will set a condition. So here I'm going to choose total sales and I'll say whenever it satisfies a condition where the number is greater than 1.77 million, try to get the right number of zeros. That looks good. So as soon as this cell E16 goes over 1.7 million it'll send that alert to whatever Slack channel or email I choose. Great way to make this more actionable so that someone's aware as soon as we hit a certain KPI or as soon as an important metric drops below 50%, something like that, you can get alerted immediately. Great. Any questions or I do. I do always keep a couple spreadsheet HubSpot tips and tricks in my back pocket. So happy to go through those or if we have questions or need some time to wrap up you can do that first.

This is pretty cool. I am happy to let you keep sharing your tips and tricks if you got them. I think. Let's see.

I think there's one question in the Q and A that wasn't answered.

Is that from Michael?

Yeah.

Would it Is it possible with coefficient to assign contacts to marketing campaigns as new or will they only show up as influenced? For example if I upload contacts from an on site event with coefficient.

Great question. Let me maybe note that down. I would have to test this out. I haven't tried associating contacts to marketing campaigns. I have a feeling it's possible but I hate making false promises on on a call with this big of an audience. Maybe I'll note down that name and if you want to reach out to me after I'm happy to take a look with you.

Yeah Hannah dropped Frank's and hers email addresses in the chat you all so.

Open season for questions. That's what we're here for.

But yeah Frank, I think if you have a couple more.

There's a couple. You know I never get to show these tips and tricks. There's some HubSpot specific that I love because I use HubSpot in my day to day work but often our audiences are looking at all the different data sources so I don't get to focus on HubSpot only. So I'd love to show those. I should also first mention I briefly covered our HubSpot templates Here. But again, this is just a great way to get started with our product. Or if you have new users in your team that want to try out coefficient, they're a little bit intimidated. They shouldn't be because it's very easy. But our templates make it even 10 times easier because they're just so out of the box and they look beautiful. They'll make you look like the hero of your sales team if you're doing a sales analysis like this. All of these are on our website. So if you go to Coefficient IO Google Sheets templates and then here I filtered on the HubSpot category you can see we have I think 15 or 20 of these that all again, we've predefined what fields we want to import from HubSpot so you don't have to do any of that. You just connect to HubSpot once we import those fields that we need to build this dashboard and you're instantly set up. So a couple of those other ones, this is the first one we looked at. Sales operations package, Most popular starter 1 Some nice use like this. Also a sales to target dashboard. Often we hear finance teams like to manage their targets in spreadsheet anyway. So this can be a nice, nice way to go about that. Some other top ones have been this MRR dashboard. This is actually we've been using this internally a ton for our own business and we're like, let's turn this into a template. So definitely a lot of thought went into this. Just a very clean view of MRR or you can switch it in the settings to ARR and see your churn as a percentage of current revenue, net revenue retention, all those key metrics like that. And Then here a HubSpot win loss analysis that just shows win rates by every type of breakdown you might want to imagine. Okay. And then looking at these HubSpot specific tricks. So first one will be on all these templates. You'll see. I always hyperlink deals back to HubSpot and if you're building out HubSpot powered spreadsheet or with any system also I used to do this with salesforce. It's just so much more valuable to the end user if you're going to link them back into HubSpot. Because often, especially if they're a sales manager or a sales rep, definitely they often want to be able to dig deeper. You know, here I'm only servicing six or seven fields for each deal. Often someone will want to take a closer look at these deals and maybe make edits. Right. In HubSpot, I think I might have built those links with a different account. But I'll show you the trick of how we set these up. So if you open your HubSpot, if you haven't deconstructed these URLs before, you'll see a couple key things to note. This number right here is your HubSpot platform ID. So you'll always need this when you're trying to assemble a URL. And then the final set of characters down here, this will be the record id. So whether you're looking at a deal or a contact or a company. So now if we want to go back to our spreadsheet, we can actually reassemble a URL like this based on one of these import examples. So let's go back to our contacts that we're working with. So here I have Contact ID is already a field. And then if I want to make these hyperlinks instead, I can use the nice hyperlink function in Google Sheets. First I have to give it a URL. So I'm just going to paste in that URL I copied, obviously make sure that I have. This will be my own unique company's platform id. And then all I need to do is make this final ID dynamic. I'm going to get rid of it and instead I'm going to say&contact ID and then make this even a little bit better. You can actually add a link label. So if we don't want it to show up as a ugly URL, in this case, I can just make company name a label and then won't that look clean? Or if this was deal deal name and automatically hyperlinking to the exact deal. Okay, I think we're working with a couple different sandbox accounts here. This is theoretically beautiful, I promise you. And related to that, we have five minutes left. So I'll show you one other advanced way of doing this that I haven't seen people do outside of our company yet. But we love it here internally. And this works very nicely with companies. So when you're working with companies, you can do the exact same thing. So I could use company IDs from a company import that I'm pulling in with coefficient. But instead of doing that, so that would be the same as this, I would take this URL, add the ID to the end of it. Sometimes what I want is to let a user quickly open a list of all contacts or maybe all deals tied to that company. And we can do that using this view associated contacts link here Something to pay attention. You'll notice when I'm hovering over this link in the bottom left of my screen, you can see that it's. I think it's showing up on the screen share. It's going to this long HubSpot URL that includes company ID. But if I click in, then you'll notice the company ID is not part of this URL. So the trick here is to not click in, but to copy this URL, copy this link address. It'll give us a bit of a long, maybe overwhelming URL. But then if we have a. I think somewhere I have company IDs, maybe not in this example. So we'll just stick with contact IDs. Again, this won't work, but it'll be a good trick. I can go hyperlink, drop that long URL and then basically I want to find where that company ID was, which is this little middle part here. So I can then replace this with company IDs. Again, we don't have that in that spreadsheet, but that would look something like. And a three and. And then put the rest of it back in quotes. You can play around a little bit with some of these filters as well. You might have multiple contact views for each company, so you can actually choose which contact view you want to bring a user to. And they'll see a different set of fields depending on which URL you feed in this way. But then they'll end up with a list of URLs like this. Or again, we could put in the company name to make it cleaner. And then whenever someone clicks on any company name. Maybe this is from a coefficient import tab. Again, maybe this is from like a dashboard tab like this, where you just have your list of top companies and you want to click in and it'll bring you to a nice URL like this one here. List of contacts, list of deals, whatever it may be. Again, keeping people in HubSpot where they want to make those changes, whether it's updating deal details, pushing that close date, whatever it is.

I love this so much. For whatever reason, the structure of URLs in HubSpot is just endlessly fascinating to me. One question for you. Just because the 404 you hit is one I hit all the time because I have so many different test portals, I'm juggling is there some way in. What's the easiest way in coefficient to see which portal you're pulling? Like the portal ID you're pulling stuff from. Is that visible anywhere?

Yeah, so that's way. So if you're in here, you can always go to the OR one step back. So up in this top menu we have our connected sources and there we'll be able to see all the details by clicking. Oh yeah. Oh, I think I just skipped it there.

I don't know if this is interesting.

To anyone but me, but I'm really excited in this little we kind of hide it away, Kyle, because it's only useful than you and me, right, if you need it. So just hover over this little icon. You'll see which portal it is. And again, this is great. Especially some of our consulting partners will be logged into multiple HubSpot orgs and you'll want to import from 1hubspot org 1 day, different org another day.

Amazing. Yep. So if you run into URL trouble, make sure that ID matches and then you'll be good to go. Well, we are at time Frank, thanks so much. It's been very enjoyable. Contact info, I guess is already in the chat. Thanks everyone for hanging out with us. And yeah, recording will be sent out afterward. And you know how to find Frank if you want him. So we'll end it there. Frank and Hannah, also, thanks again for being here.

Such a pleasure. Thanks for having us. And yeah, everyone feel free. Reach out LinkedIn Email we're here to solve your spreadsheet problems.

Amazing. Enjoy the rest of your Tuesday, everyone.

Bye guys.

Sam.