Excel It Helper
Auto-Export and Refresh your saved searches to Excel, automating your workflow!
When it comes to working with data, nothing beats the industry leading Excel. Getting data out of Infusionsoft and into Excel, however, can be tedious at best.
With this new Helper, we allow you to export directly from Infusionsoft straight into Excel. This allows you to do any charting or reporting work that you need to with ease.
- Integrates with Saved Searches
- Translates Column Names
Integrates with Saved Searches
The mechanism for exporting to Excel is all done through Saved Searches in Infusionsoft. So, whatever data you want to export, you need to first setup a saved search for it. Then you can connect the Helper to that saved search.
Translates Column Names
This is an option for how the saved search report column names are exported. While not technically needed, you may prefer to show the easier to read names rather than the defaults.
Above is the toolbar options you'll see for the Excel It Helper. Here are the Toolbar button options from left to right:
- Download Installer - This first blue Download Installer link button it will initiate the process of exporting to Excel.
- Configure Button - The last button on the far right is the start configuration button which allows you to View, Edit, Copy, Delete, view the Usage Report and the Usage Log.
Tips, Tricks and Shortcuts
Hello and welcome to MyFusion Helper. My name is Troy Broussard and in this short video, I'm going to show you how to use the new Excel It Helper. Now, this little tool is awesome. It follows on the footsteps of our Google Sheet It helper, which allows you to export data realtime from Infusionsoft straight into Excel.
Now, what we're going to do is first I'm going to walk through how to set it up inside of MyFusion Helper which is really straightforward and super, super easy. Then after we do that, I'm going to show you how to work with it in Infusionsoft. Then lastly, I'm going to show you how to then integrate it with Excel so that you can bring all of your data in and update it automatically, and everything else that you want to do. Let's dive in first and see how it all works.
Okay. I'm already logged in here to MyFusion Helper. The first thing we want to do is click on the ‘Create a helper' button. We're going to go grab it from the drop down. I like to just type it. It just works out easiest so we can just grab it right there. Then you can, of course, watch the video. You're definitely going to want to watch the video on this because this one you may have to go through it a couple times to see the details of how it works and how to set up the mappings properly.
All right so then we give it a name and we just say ‘Configure the helper'. There's really only one thing that we have to do and that is we have to map the saved search that we want to see in Excel. Now, it can be either a saved search or saved report from Infusionsoft. The same is how we do this in the Google Sheet helper. In order to get the data through the API, we can only access saved searches and saved reports. That is a requirement.
The first step is you're going to be setting up the data the way you want to see it. Add the columns you want, add the filters you want, get it all the way you want it set up in Infusionsoft and then save it as a saved search or saved report. Then we can come in here and I can just come down here and grab this Excel test and create the linking between Excel and MyFusion Helper which then translates to Infusionsoft. Okay?
You can choose to use these pretty names if you want, but I don't recommend it, and that is because we use the names that are the field names through the API and sometimes the reporting may break if you change that. I recommend that you don't do that. Then go ahead and click ‘Create the helper'. That's really all there is to it. There's only one option that you have to do.
Now the next part is about installing it in Excel. Before we do that, however, I just want to go show you in Infusionsoft what you would do to set this up. In Infusionsoft, it's all about creating a saved search. As you can see, I've already defined a saved search here and I just already went ahead and added some criteria. In this particular one, there's no criteria at all because this is a test data set that only has a few contacts in it so I wanted to pull something back, but I did come in here and modify the columns that I wanted to see.
What few people realize is that you can do all of this here and put all of these different features and filters inside of your search. When you've got it all just the way you want, then you can come in here and click ‘Save' and give it a name. Then you can load it here as a saved search. Now, it's not just saved searches though.
The other thing that you can do is any area that you can do a report, you can also save that report. If I wanted to look at tag applications reports like this, I can come in here, see, and I can save that report as well. Any report or any saved search that you want to export to Excel you can do that, but the first step is to save it and name it in here.
Now, once you do, you will need to come back in … When you create this, you will need to refresh the data. If I created a new one right now and I wanted to go edit and change this, it wouldn't see the list in here just yet until I refresh it. The way to do that would be just to click on the refresh button here and that would repopulate that list of saved searches.
Now, let's jump over to Excel and show you what you want to see to integrate it on that side. What we're going to do … I'm just going to back out of here for the minute. We've already set it up so now we want to download this installer file. This file is just going to be used the very first time that you set this up in Excel. You do not have to have the file after that, and in fact, you can even delete the file after you download and set it up the first time.
We're going to go ahead and do this. It will have the video for you. It says ‘Coming soon' because I'm obviously recording the video now, but you'll see the video right here so that it walks you through the steps. Now you can download. It's already downloaded there so we can go ahead and close this. Now what we want to do is we want to load up Microsoft Excel and we want to open up a blank worksheet. I'm going to just maximize this on the size here.
This is very important. When you go to ‘data', don't go to the data on this menu. For some reason, they chose not to put this option on the toolbar and you can't get to it from here. It's very, very important that you go to data in the menu at the very top. Not on the toolbar, but on the top menu.
Then we want to come down here and we want to say ‘Get external data' and we want to run a saved query. Now, when we say we want to run a saved query, then we navigate down to our downloads, we grab that file that we just did, and we bring it up here. Now we say where we want to place that. This works just the same way as it does in Google Sheets so you can have multiple queries on multiple tabs down here at the bottom and place them in multiple locations.
It's going to default to this A1 but if I wanted to go grab another sheet over here instead and place it there, I could do that. I'm just going to put it here for right now. That's going to bring that data in. It takes it a second, and you see, it executes and it's already bringing the data back from Infusionsoft.
Now, after you've done this, the linkage is now created. This file that we downloaded, we don't need that anymore. In fact, if you want to just look at one of those files to see, you'll see that all it really has is the information to connect to MyFusion Helper. That's really what it is. It's a little settings file. Now, the first time you run this query, the settings file is now saved into your Excel document and so you no longer need that little configuration file. It's just to establish that connection the very first time.
Now, the data is in here and you can go create any charts, graphs, formatting, anything you want. I've always recommended to people in the Google Sheets video and through the help desk and I recommend the same thing in Excel. If you want to format this, don't do the formatting on this sheet. Go down here, create a whole other tab, and put all of your formulas, calculations, and any math that you need to run or any charts. Put them on separate sheets. That way, you're not going to be stepping on top of the data that's coming back from Infusionsoft.
Okay, now we've got it set up but we need to set it so that we can refresh the data whenever we want. There's a couple of ways you can handle that. You can manually refresh anytime you want by just clicking the ‘refresh all' button here and that will refresh the queries. If we had three different sheets with three different queries on them, all of that data would all be refreshed. A more elegant way to do this is to go in here to the ‘Properties' and when we pull up this properties, there's this nice little ‘refresh control' where we can say, ‘Refresh data when opening the file'.
This can be very useful because now when we save this file, every time we open it, or every time your CEO or marketing department or outside operations or accounting department opens this Excel spreadsheet, it will automatically then refresh the data. That's the recommended approach that I recommend as a best practice. It will prevent you from over refreshing and it'll also prevent you from having stagnant data. That is the best practice on how to set this up.
All right. We can just talk about a few practical examples of this and how to put this to work in your organization. The first is just going to be more robust data reporting. Being able to create your own custom charts and graphs and any type of reporting that you want is really the most powerful feature and benefit for using the Excel It Helper. Another thing that you can do this is for is any kind of financial analysis or accounting reports.
A lot of times, you need to give accounting data to your accountants on a daily balance or a monthly balance and you may need to create some special reports in the format that they need to see them. You can set this up once in an Excel and then just set it up as a saved search, and just automatically distribute that to them once a month and send it to them. Each time they open it, it's going to refresh the data and everything's going to be good to go.
Another option is just distributing data outside of Infusionsoft in general. I've seen this used for affiliate promotions. We've got a lot of people that use that with Google Sheets where they have an affiliate table that lists the sales and everything and they have that all calculated, and they put it into a Google Sheet so that it can easily be distributed outside the organization without people having to look into their accounts.
That's kind of a good way to keep your affiliates excited because they can see the leaderboard. There's also a lot of other reasons. It could be just that you need to get some data in a realtime basis to a consultant or to an outside service provider or outsourcer that you really don't want to give access to Infusionsoft, you just want to give them the data that they need, and that's another great way that you can put this helper to use in your business.
Hello and welcome to MyFusion Helper. My name is Troy Broussard and in this short video we're going to talk about the Email Validate It Helper.
Now this Helper is brand new into the app and used to not just check the syntax of an email and make sure it has app sign and that kind of stuff, but this actually is a live API connection that will go out, query the email, and validate it against the servers through an external API.
I'm going to show you how to set this up and how to configure it and how to get things rolling so that you can use this in your app and then we're also going to do a live demo of how to set it up in Infusion Soft and we'll talk about how it works in the best practices for you to use in your application as well.
All right if we look at how to do this, when you go and create the Email Validate It Helper, the first thing that you're going to be brought up to is a screen asking you to enter in your public and secret API keys for a service called Mail Gun. Now, Mailgun is provided by Rack Space, and Rack Space is a highly respected industry company that has been around for years and years so this is a very solid API and there's absolutely no cost to do this.
You do need to connect you're own account if we ran all of yours through our account that would be a violation of their terms of service but you are able to create your own account and then connect it up. I'm going to show you how to do that. You'll see the green link there on the screen that says, ‘Public and private API key from your Mail Gun control panel.' Where that's highlighted you just click there and it will take you to the next screen for a log in for Mailgun.
You're not going to be able to log in yet because you don't have an account. If you just go to the bottom there were you see it says, ‘need an account, sign up here' and click on that link then you're going to be brought to this next screen where you're just going to go ahead and fill it out. It only takes a minute, you're just going to give it a name, an email, confirm your password, you don't need to put any payment information because you're not going to set it up to pay anything.
It's a free account. They do have the ability to set up a Pay It account and do things but you're not going to need that. Don't even bother to fill that out and just leave that alone. It's optional it's not necessary, you're entitled to a free account per their terms of service and we're not doing anything to violate that. You're good to go.
Now, what you're going to do is after you've done and set up your account, you're going to be taken into a screen like this. It's a little hard to find, so let me show you what you do. On this screen you're going to scroll down. See that section on the right that says, ‘API keys' that's where we're going to go next. As I zoom in here you can see this a little bit better, you'll see that there's a public API key, and a secret API key. Those are the two keys that we need you to paste into MyFusion Helper.
Make sure and click the little ‘eye' button on the right hand side to be able to view those and then you can simply copy and paste them and configure them into MyFusion Helper. Those are the two keys that you need to configure it. It's very straight forward. Once you have that everything is set up and there's nothing else that you need to do.
Now let's hop over to the app, into MyFusion Helper and show you how to set this up inside of the app once you've connected it. So we're going to go in here and use our email Validate It, type that in and pull it up and it will give you a little bit of information here and then we're just going to go ahead and name it. So, we're going to Email Validate It and go ahead and configure. Go ahead and do that. Now what you're going to see is that we select our Mailgun account, so I'm scrolling down there, we'll select the one that I just set up. We'll select an email that we want to validate against, so normally that's just going to be email.
Here's some different options that you can choose. You do not have to use these tags if you don't want. We also fire off API goals. You can just choose to ignore these tags and use the API goals instead or if you want to tag them that's fine. I'm just going to say that we're going to use API goals for all of these but you could easily select the tag that you wanted here.
Let me just walk you through the different scenarios. We have a ‘valid' and a ‘invalid.' So, if you want to tag people that are valid, you can do that. If you want to tag people that are invalid you can do that or either one you can use the API goals.
This next one is a little bit more interesting and this is to tag someone if a suggestion is present. What happens is people make typos all the time. Instead of typing gmail.com they'll type gmail.co or they'll type gmall.com they'll forget the ‘i' and they'll type gmall.com. Things like that. Well, this Mailgun integration is capable of detecting minor typos and things like that and making a suggestion. If you want to tag the contacts that suggestions are available for you can do that and then we can save that suggestion into a custom field.
Lastly, we can even automatically update the email to use the suggestion if you want. My personal preference is to not do that because I think you should review them, that's my personal feeling. I don't like that concept of automatically updating an email. We do provide it because sometimes it happens a lot and people want to have that option but it's not defaulted to that behavior. You have to specifically check for that to do that.
We can set a tag. I'm just going to use an API goal and then if I wanted to save that into for example, email three, which is very rarely used. Most people will use email one or two, but hardly ever use email three. That's kind of a best practice is save that suggestion right into the email three field and then I would not recommend automatically updating it but you do have that option if you so desire.
Then we go ahead and create the Helper, and that is all there is to it. Once it's created what I want to show you here is you have the ability to go in here and set these API goals and copy and paste them so that you can deploy them into your campaign. As I told you here you just click on the little trophy icon for the API's and you'll see that you can get these API goals for ‘valid', ‘invalid', if a suggestion was found, and if the Helper is run complete and that's good for using API testing just to know that everything is done and closed out.
Now let's jump in to InFusion Soft and pull this up. Back to InFusion Soft if we take a look into how this is laid out. I've just put a quick sample campaign together for you of how we might do this. We might have a tag to kick things off, just to start campaign maybe as something we run periodically for an admin purpose for example. Then we would go in here and we would check the email and so we would do that by dropping in a HTTP post snippet here and we do that. I like to label them ‘Myfusion Email Validate It' so that it's clear what it's doing. We're going to go back into here and close this out, we're going to copy the URL, we're going to come in here and we're going to paste that in and turn it on.
That is set up. We turn that back to … we turn it off draft mode and ready and then for each one of these, we're going to go back into MyFusion Helper and we're going to grab those goals. So, email ‘valid' let's copy that. We'll put that here on the ‘valid'. Make sure that this is labeled for an API goal ‘MyFusion Helper.' Then we paste that in.Then we do the same thing here, continue it on down. This is going to be ‘MyFusion Helper' as well and then we're going to grab the next API, this is ‘invalid' so we're going to copy that and come back over here and paste it in. Then we'll go to this third one. We'll set that up, ‘MyFusion Helper' and we'll switch over here and check for an email suggestion and put that in here as well … and ‘Boom' there you go.
Now when you run this you would have, when it's all complete, you would have all these people that you could identify through all the reports and you could manually review the invalid ones for example. You could manually review the suggestions found if you wanted by right-clicking on those and viewing the people that hit that goal.
Very simple and straight forward. So if we talk about some of the examples and best practices, one is you're going to be using this in an automated fashion at the beginning of your main welcome campaign. To ensure we have a good valid contact before we trust them and just start emailing them extensively because we need to keep our list as clean as possible. Doing this at the beginning of your campaigns is a very wise best practice.
I would probably in this case, tag people that are invalid or where there's a suggestion present and I would probably save that suggestion into the email three and do a manual review. But if by tagging them I could create a little report on the dashboard for those different tags and go straight into the dashboard, see the people that are invalid and pull them out of the sequence, stop them out of my campaign, delete the contacts, all of that, right then and there.
Just make it a weekly review process for someone in the organization and you can do some more sophisticated stuff there probably a slack notification with our Slack It Helper, or even creating a Trello Card to assign a task for somebody to then go through and clean up those emails, is what I would personally do because that way I know that it's going to get taken care of and it's an automated work flow process.
This can go really good hand in hand with either Slack or Trello. Remember that the different Slack is just purely for notifications, whereas Trello actually fires back work flow automations just like Opportunity Stage Move. As you move somebody through the different lists in a Trello, it will automatically then fire off API goals back to Infusion Soft so that we can automate around that.
The Trello is a nicer solution for a work flow based solution because now you can actually know that people are doing what you've assigned to them and treat it like a task or a project.
The second option here is for Manual Email Hygiene Review. This is probably the first thing that everybody should do is just go through and run their whole list through a Manual Email Hygiene Review and just verify where things are at and process it once and then go back and do step one, which is put it into your normal routine.
Hopefully that answers your questions if you have anymore, make sure and hit us on the Help Desk at firstname.lastname@example.org and we look forward to hearing from you. Take care.
If you need more help reach out on the help desk at email@example.com.