Office Hours – January 16, 2018

Recording of Office Hours hosted by Chris Davis on January 16, 2018.

In this session, we covered how to create a referral tracking system using ActiveCampaign, Google Sheets, and Zapier.


Chris Davis: All right so I see a few people on that asked a question via email so I’m going to go in the order in which I received them. And then we’ll from there. First up I have Sandy, hey Sandy, welcome back. Sandy I remember you. Can’t remember which week but I remember you. Let me, sorry I’m just formatting my email [00:00:30] on my second monitor so I can read it and read your comments.

Let’s get down to business everyone and remember you can ask your question at any time, don’t hesitate. Make sure you get them in there. Let me do this Sandy. Sandy says, “On Monday my automation did not go out. I had not set up something right in the bulk editor for the [00:01:00] contacts. Can you please look at it and make sure it is all set now for the remainder of the automation.” Yes, great. Sandy, actually the link that you’ll want to give me since I can’t log into your account live like this, you’ll want to go to that automation and select the down arrow and hit share. This is the link. This is the link that you’ll [00:01:30] want to send me so I can look at your automation. Let me see, I should have some of the names. I didn’t have an automation for you so you can refresh my memory of what we did for you.

While I do that I want to pull up your site. Did I do that right? Future homes online. Just give me a refresher of [00:02:00] what we were trying to do. What communication we were trying to send or like I said, send me the specific automation in question. Nice website by the way. Let me close out some of these windows. There we go. Oh no, I’m sorry, Sandy it’s this link. ‘Cause the one you’re sending me is like the actual automation [00:02:30] like this where it has the number at the end. What you’ll want to do is before you click into the automation, click this drop down and hit share. This is the link. It should say, “TPO share.” That’s the link that I need in order to pull it in and go over it. Cool. And while you’re doing that, I’ll go to the next one.

[00:03:00] Rajesh, hopefully I said this, said it right, just going to queue yours up still. I’m still working on you Sandy. Just queuing this one up. There we go, perfect. Look at that. Rajesh I’ll come back to you. I [00:03:30] think that the dollar sign, I sent you automation. Here we go. Import automation. Everybody this is how you import automations if you didn’t know. So that dollar sign probably was part of it.

I’m going to import this. Oh Eileen, okay, okay. Sandy I [00:04:00] get it. You’re working with Eileen. I do remember Eileen. Yeah, ’cause you came on after Eileen had left and asked a follow up question. That’s why I remember you. Oops sorry, spelled that wrong. Let’s see if I saved hers. Spelled that wrong too. [00:04:30] There we go. Okay, so here are the three that we worked on. Let me import the one that you sent me. Just to make sure. There we go, this is why I like to use the names. You’ve gotta stay organized. In fact the blog post on naming conventions everybody it will be published today we had to [00:05:00] revise it a bit ’cause I didn’t want it to be confusing to you all. I think this one is in a really good spot to where it’s not confusing and you all will be able consume it and would love to hear the different types of naming conventions you all are using in your application. It’s not ActiveCampaign specific it’s throughout.

In fact the most, the expertise, the area of expertise that I know use naming conventions the most when it comes [00:05:30] to digital marketing, are paid media folks. Everything it has to have a naming convention ’cause they have to keep of all of their ad sources, which campaign, which ad they clicked. And if you don’t name things appropriately you’ll get lost in data. So now that I have this imported, so what we have, I remember Braveheart. When the contact subscribes to any list and is it this, which email [00:06:00] didn’t go out? Let me know Sandy.

Let me see. This is all right. Current day of the month is the 15th. The current month is January 15th at nine AM. This email here should have gone out yesterday at nine AM for everybody [00:06:30] that had subscribed. What you’re saying is it didn’t. Tell me this, Sandy, do you have people waiting at this goal or this goal? ‘Cause they have a little number to the right here. ‘Cause you do have this configured correctly. ‘Cause we’re essentially saying, “Wait until [00:07:00] the current day is the 15th and the month is January and the time is nine AM.” Yes, that should have of went out. I do at goal two. They’re here but they’re not waiting here at goal one. The first one finally went out at 10. Is that 10 AM? Or is it 10 PM? Just to make sure [00:07:30] so we can track what the delay could have been. Yes, just wanted to make sure from here. Okay, AM. Just wanted to make sure from here. Here I am all set.

We’ll just go through it, that’s perfectly fine. This one was configured correctly and if it did go out at 10 one thing I would say is you’re using [00:08:00] the contacts’ timezone so you may want to switch this to the current time is in a specific timezone. Because if that’s the case then it will also go out at different times relative to when the contacts subscribe. Which is fine if that’s what you want. That’s perfectly fine, I’m not saying you have to change this, I’m just making a note here that you’ll see that email go out at [00:08:30] different times based on the timezone that they’re set for.

And then Mark says, “I find the send at hour or later is more consistent than at a specific hour.” Yeah, you could also, that’s a good one Mark. You can also to be safe, say, “Is greater.” [00:09:00] Or at hour or later is more consistent. Is greater than. Greater or equal to. Greater than or equal to, you can try using this as well. Configuring it to this. Greater than or equal to nine AM. That will send, that one might be tricky Mark because it’ll send, no it’ll send all day [00:09:30] for the 15th. That’s a good catch all. If you want to just make sure that it does go out. You have an option there and then I just want to make sure I’m following you. Only four people received it at the correct time. I would say if four people received it, it may be due to the contacts’ timezone because they may be in another timezone where they just receive it later or earlier.

Check. Check to see what timezone they’re in just to be safe. But if you want it to go at the [00:10:00] same time, just like you’ve done with using the New York time, I would go and set it. Date and time at the current time instead of contacts’ timezone, you use your timezone and then everything will go out at that one. It had to do with adding the contacts in the bulk editor. Either way, Sandy, just to know going forward, as long as you add [00:10:30] them before this is true, they should all execute correctly. Just so you know. But like I said, if they’re going out at multiple times, I believe it’s because this was in the contacts’ timezone like this is showing. Let me just, go over these really quick.

This one, the current year is 2018. That’s a good differentiate. You probably [00:11:00] don’t need the 2018, doesn’t hurt. This one looks good. This one looks good. These look good. These look good. I’m just assuming you’re just going every month here. Or every week. Last week, this one should be February, second week in February. This [00:11:30] looks good, you’ve got it at five AM. This should work, this should work Sandy. Like I said, if you want to make sure everybody gets it at the same time, change it to your timezone and you’ll see them all go out at the same time. Good job, good job. And thank you, thank you for reporting back Sandy. Like I said, anybody who’s on here please report back. You don’t have to be here live. If you’ve asked a question or if you’ve done something or [00:12:00] tried something that I’ve mentioned, please, please, please report back and let me know how it’s working. If it didn’t work, let me know what happened. If it’s working well, let us know. Please share with the community everybody.

Mark says, “The strategy of using a goal versus a wait state is you get the analytics in reporting.” Yes, absolutely and you get the ability to jump. You get [00:12:30] the ability to jump as Sandy has outlined.

Sandy you’re very welcome, thank you for asking your question today.

Rajesh, I hope I’m saying it right. Let me save this one. I’m going to go through these fairly quickly. There’s a chance you’ll find these in previous office hours. But I’m going to go through. [00:13:00] Should I move my contacts through different lists or different stages in the CRM? Good question. It will depend totally on how your account is set up. I would never use lists to replaces stages though. The best practice is to move contacts through stages once they subscribe to a list. I wouldn’t have five lists, one is like [00:13:30] two contact, another list that’s in contact with another list that says proposal sent. Then another list that says proposal accepted. You know like the whole process, no I would most definitely manage that in a CRM. Have one sales pipeline and instead of using lists I would use stages.

How can I add multiple contacts per deal? Can I track email messages if I sent to anyone of those contacts in a deal? Yeah, so [00:14:00] if I believe what you’re asking is, how can I add a deal to multiple contacts? And how you would do that, the easiest way and this is good to know everybody. I’m going to start from scratch here. I would start an automation, if you want to do this manually with no start trigger and I’m just going to go CRM and add a deal. [00:14:30] Give it a value, whatever it is. And a pipeline and where it exists. I should have, let me do sales process to contact and hit save. When you activate it, now this is the deal that you’ll have created. [00:15:00] You can do multiple things. You can have multiple deals in here or right now I’m just doing one and each automation would be specific to that deal. Keep them singular and then you can go into your bulk editor and highlight all of the contacts that you want to send it to, if there’s a specific criteria, use the advanced search.

But highlight all of the contacts and add to automation and [00:15:30] then there and when I add them to the automation it’s automatically going to assign all of those contacts that deal on the appropriate pipeline and the appropriate stage. Now if you wanted to track emails that you sent to people, you can do that as well. If I go here, and if I go into the deal record, any deal, you see where it says emails? This is [00:16:00] all of the emails that I’ve sent relative to this deal. You’ll see them all here and you can also send a personal one from here that will show. That’s that one. I see Rajesh isn’t on anymore so I’ll just keep answering these.

How can I find out the contacts that are part of multiple lists or contacts [00:16:30] in no list? Sure. To do that you would search, you would go to contacts. Let me just clear that out. You’ll go to contacts and use the advanced search and look for that exact criteria you’re talking about. Let’s say I wanted to say, “I want to search for somebody who’s in list main and they are in list [00:17:00] lifecycle lead.” Then I would hit search. That’s going to show you how can you find contacts that are in multiple lists and then if we wanted to flip it and say, “Contacts in no list.” Which you should not in list, I don’t want to do it, every contact should be on a list but if you wanted to do not in a list, in list [00:17:30] nothing. I would do in list and have nothing selected. And hit search and this is going to show who’s not on a list.

Which again, best practice, you should have your contacts on a list and the reason why you need them on a list is because you can’t send them an automated email. So they can’t go into an automation and actually get an email if they’re not on a list because the list determines what physical address [00:18:00] is added to the bottom and the bottom of your email must have a physical address to abide by the CAN-SPAM Act. It is illegal to send an automated email without the address. As you see here, all of my contacts are on list. Hopefully this is the same for you.

When I send out emails, would the contacts see what the list name is? No. No they would not. Only you see the list name. Unless you choose to tell them they’re on a specific list but [00:18:30] we don’t do that at all.

Can I add attachments to email campaigns so they can download a lead magnet. Yes you can but not as attachments. In fact, I don’t recommend attachments at all ever when sending emails from a platform like ActiveCampaign. The reason being is because some of those attachments can get really big and you don’t even realize it. Sometimes I save a screenshot and when I go and attach it to an email, it’s like three megabytes. I’m like Jesus, [00:19:00] to me it seemed like a small square. Or a PDF that you’ve create or a document, you don’t realize how big it is until you upload it and that is going to impact your deliverability. Depending on the size and the size of the person’s email, their inbox may be full. Their inbox may not accept attachments over a certain megabyte size. I never ever, ever, ever recommend [00:19:30] uploading the lead magnet to the email in a platform. I always use the link. So instead I would upload it somewhere like Dropbox or Google Drive, Amazon S3, any file hosting platform and then take the URL of the link there.

For instance this is a Google Doc. Let’s say this [00:20:00] presentation I was giving it away, I would just do this, I would copy this URL and when I go into a campaign, instead of saving this out as a PDF and then uploading it, I understand that makes sense when you’re using regular one to one email but here where I do something like this. Here’s the presentation I promised [00:20:30] you. And I just highlight it, I’d highlight it, select the link and then paste it. See that. And now it’s the same thing. When they click this link, it’s going to take them to the Google slide or the PDF or whatnot. The same is if you uploaded it and they clicked upload. The functionality of is the exact same but guess what, you don’t add to the file size of your email. Now you don’t run [00:21:00] the risk of your deliverability taking a hit or the email not being delivered to the inbox because it’s too big. This is the standard way. This is the standard way.

Plus you get link clicking. If they click this link I can also add actions. I can add them to an automation, subscribe them, removing ad tags, it’s just so many reasons to do it this way. That’s my recommendation.

[00:21:30] Any way to validate email before adding to list? If you want to validate, I’m assuming you’re talking about ensure that it’s a real email address. There’s two ways to do it. One way is probably not ideal and will kill your conversions the amount of people who fill out the form. But that may be okay with you depending on your audience. It may be more important to have capture [00:22:00] on there. If I add a capture, you see that? Now they have to check this box. To validate they’re a real person. Now that’s not going to validate the email but this is a step in getting closer. If we wanted to use the two layer approach, we could say, “I’m not a robot.” Click okay, I know this is a real person now next I want to validate the email, well you can do that by clicking on one of the lists that the form adds them [00:22:30] to and using the opt in confirmation.

If you do that, in fact,, we have one of our latest guides are how to customize the confirmation opt in. I’m going to place this in the chat. [00:23:00] For that link. You can customize this message, click edit. So now if we double up, we can double up. Look at this. Thank you for requesting whatever they requested. Please click the link below to download your resource. Click here your download your resource. And what can do? I’m merging your two questions into one. Delivering a lead magnet and verifying an email address. So right here, [00:23:30] oh, I don’t want to do it here. Right here this is going to be the confirmation link and it says, “When you do you’ll be providing with permission to send you updates and ongoing communication.”

At this point, when they click this link, you have validated that’s it’s a real email address. The URL that they will go to once they click that link, as you saw it say, “Confirm link,” As the personalization tag. But the URL that they’ll go to [00:24:00] is right here. I can redirect to a URL and now I’m going to put in the link to my offer. You see that? They’ll fill out the form, they’ll get this confirmation opt in email, I’ve customized it, they’ll click the link. The link will then deliver what they asked for, thus validating that it’s real email address. These are all steps [00:24:30] to validate if it’s a person and if the email is real. If you want to validate if the email even exists, if it’s a real email, you have to use a third party service as Mark mentioned.

Mark says, “Third party services can verify email addresses.” That’s how would handle that. All right great, thanks. Thank you for asking Rajesh.

Mark you’re up. I’ve [00:25:00] got everybody hopefully I didn’t miss anybody. Let me grab this Mark. I’m grabbing it from the email, I know you posted it in the chat. Mark, this should be a good one. I should have posted it from the chat now I’ve got all these ugly spaces. Here’s Mark’s use case and remember [00:25:30] everybody, ask your question at any time, any time. You don’t have to wait for me to finish answering any questions here.

Existing AC contact fills out a form with their name and email plus their referral’s information in custom fields as follows. The contact fills out the form and the contact is submitting [00:26:00] who referred them, their name, their email, their phone number and whatever notes. What Mark’s saying is, he’d like to able to create a new AC contact using the data captured in the custom fields on the form submission. Is it possible via a web hook, Zap? If yes, how? Wow, Mark this is a good one. This is a really good use case. And [00:26:30] hey everybody, we’re at the halfway mark. If you don’t have any questions, I’m going to work through this until you have a question. But I want to work through this to show you all how to do this ’cause this is going to be enlightening and showcase some of the possibilities of the application. Let’s do it and I’m going to keep an eye out on chat. Ask a question at any time I will pause and answer your question but let’s [00:27:00] work on this.

Ideally what Mark wants to do is create a, allow contacts to create a contact. This is, Mark this is great. Just think about this everybody. If you’ve got a referral program, which chances are there are not many businesses that should not have a referral program. It’s an additional lead generator for you. In fact, a lot of service based businesses, that is their [00:27:30] lead generation is referrals. Whether it’s a doctor, dentist, whatnot, they’re like, “Hey we’ll give you 50 bucks every time you refer somebody.” So now, it would be nice to be able to create that contact. Mark I’ll take it a step further, we should be, it would be nice to tag them with the contact’s name.

Anyways, I’m adding a lot to my plate here but we’ll see. Let’s just see if this is possible. [00:28:00] This is a fun one. Plus I haven’t got a chance to build out an automation. Or build out a flow. This is more of a flow. Referral name. I’m creating custom fields everybody. Mark has a custom form and he’s using custom fields to display on that form. Email. And I’m just [00:28:30] creating them all right here. Just bear with me. This is the process of creating custom fields and I’ll show you what they look like. These are always good, I’m going to do a text area. These are always good refreshers ’cause I never want to assume people know more than they do. Now that I’ve created all of them, custom fields are added to the bottom so you’ll see, name, email, phone and notes. There are all of my [00:29:00] merge fields or personalization tags.

Now what I want to do is go and create a form and add those. I’m going subscribe them to a list. And go to create. And from here I want to capture your name. Here [00:29:30] we’ll say something like, “Thank you in advance for your referral.” I’m just being descriptive here. Enter the information of the contact you would like to refer to our company below. This is I’m going to say your [00:30:00] name, do I want their email address? Their name and email okay, you’re right. Your email. What we could do is oh you got me going Mark. What we could do is standard HTML put this here and say, ” [00:30:30] Your contact information.” And what I’m going to do is I’m going to style this a little bit. See I’m just showing off, sorry everybody. I told you, I don’t get to do much of this anymore.

Font style or it size? Font weight. Weight bold. Sorry, this is fun for me. But you all get [00:31:00] to learn. I want to make it smaller though. I don’t want to make it as big. There we go. Your contact information. I’ll put it at 15, I’ll put it at 14, 13. And then why not, color. Do E1. That’s too light. I wanted to go the other way. A5. Just a little light. [00:31:30] I’m going to copy that. This is their contact information and then what I can do is add another section that says, “Your referral information.” Look at that. This is all native to ActiveCampaign I’m just going in there [00:32:00] and I’m essentially doing what the builder allows me. This is why I like to work through these just so you can all see the capabilities.

I’ve got quite a few custom fields, know way I’m scrolling through all of these, so what am I going to do? Back to the importance of naming. Got everything named appropriate so I can just easily grab [00:32:30] and drop it all. Can you imagine if I just had it named name, email, phone and notes? I’d have been crazy to figure that out. I’m going to expand this a little bit. A lot of people don’t know you can make this bigger or smaller right in the builder. And now look at what we have here. We have a nice form that’s saying, “Thank in advance for your referral. Enter your contact information.” You know what? I’m just [00:33:00] going to be more descriptive. Contact information, just put an enter in here. Enter. ‘Cause you know, you want to guide people as much as possible. You don’t want to leave anything for them to try to figure out. Like what am I supposed to put here? I could put some default text here but I’m not going to go through all of that right now.

So now we’ve got the form. Just as Mark has outlined. Not going to do any styling, I’m just going to say, ” [00:33:30] Thank you for your …” Mark I know you have a URL you’re sending them to. Continual referrals. Just do something like that. And then hit integrate. I’m going to grab the link, copy it. Now we have our form. That’s one. Next is we need to capture this information and create a contact [00:34:00] out of it. How we create a contact is we’re going to have to extract this information to a external database that can then turn around and send the information back to ActiveCampaign in the form of a contact. We’re going to do some database, no I don’t want to be technical, we’re going to do some data entry. We’re going to do some data exporting and then do some data [00:34:30] importing. Mark you already know. Google Doc it is.

What I would do though is what I do I want to do? Do I want to set up a Zap? I want to set up a Zap, I believe I do. I think I want to set up a Zap. Do I want to do an exporting web hook though? And then catch it on the Zap side? Or do I want to … [00:35:00] this is the question. How do I want to do it? How do I want to do it? First off, I want to send all of this information to a spreadsheet. Let me think how I want to do it though. When this is filled out, right. Mark says, “Why I’m asking [00:35:30] you.” Everybody what I want to do is I want to send this information out and then I want to send it back. Here’s what I will do. I’m going to populate. We’re going to do it this way.

Let me create a new spreadsheet. We’re going to create a, [00:36:00] whoa, where did my spreadsheet go? We’ll say we’re going to call this referrals. And then we’re going to say, “Name, email, phone, notes and referrer.” Got it. We’ll do that for now [00:36:30] and I always, sorry, I can’t create a spreadsheet without doing this, sorry everybody. Now that I’m trying to rush I can’t find. ‘Cause you know you always end up adding more rows than you need and scrolling. Anyways, let’s go into Zap here. We’re going to figure this out on the fly. Remember the floor is open. I have to go to a meeting. No problem, I’ll finish this out for you Mark. Yep, the replay will be up today. [00:37:00] Tomorrow at the latest.

And everybody remember you can always ask your questions at any time. Here’s what I’m going to do. I’m going to say when someone fills out this form, populate a sheet. I want to make a Zap and the first thing I want to do, let me think, is ActiveCampaign. You know [00:37:30] what I want to do? I want to also create a referral list. ‘Cause that’s going to make it, well no, because they probably want to refer multiple times. Let’s look at the triggers we have. When an existing contact details are updated. New or updated contact. Let’s do this. Let’s do this. My ActiveCampaign account and then it doesn’t matter at this point. General [00:38:00] updates. Now when a contact fills out the form they should be added to general updates. I want forms.

Thank you for hanging in there with me everybody. And remember this entire thing is probably not what you need. [00:38:30] General updates. But there’s bits and pieces of this that are probably that are going to be like, hey wait a minute, I didn’t know you could do that. They’re saying, “General communications and product updates.” Is that what I used? Got too many open now. General communication and where did it go? Where did it go? Right here. Is that what I did? General communication and product. Okay, great. So we’re good there. And now [00:39:00] make sure you have at least one contact, let’s fill this out. Let’s fill out the form. I’m going to type in my fake name, Phil C. Davis plus Mark Binda, 555 123 there. [00:39:30] This is only a test. And I’m going to submit it.

At that point Zap should catch it. If I hit fetch. There it is. It was successful so let me go on and now what I want to do is select Google Sheets, create a row. I want to send all of that data to a row in a spreadsheet and [00:40:00] this one is. Whoa, what the heck is this? This is new, sneak peek your setup. Whatever that means. I’m going to select referrals and the worksheet is sheet number one. Now it’s going to ask me to map. Look at this, the name, email, phone, notes and referral. Now I can go down here and I’m looking for the referral. Oh, [00:40:30] I can’t do it like that. I’m looking for the referral field. Where is it? Referral notes. Referral email. Referral name. Right there. Now I need the referral email. You see that? I’m just grabbing the custom fields so that I can populate this field. So I can populate this spreadsheet. Referral name. [00:41:00] Referral email. We’ll grab the referral phone now. Referral phone. And we’ll grab the referral notes. Referral notes. And then the referrer we’ll say is the contact.

See that? Now I can keep track, this is, oh, I’m liking this example more and more ’cause now in this database I can keep track of who’s referring who. [00:41:30] Just in a simple spreadsheet. I don’t have to do anything. As long as I have this form set up and I can say, “Hey if you have a referral go here.” Whatever. I can even tell them how I’ll give them bonus or free whatever ’cause I can track it. I can track who’s referring who right here in the spreadsheet. With that being said, let’s hit continue. Now watch this, there’s no empty fields, I’m going to send [00:42:00] test to Google Sheets. Let’s see. Let’s see what just happened. If I go here, you’re just going to do custom fields. I’m going to have to do this myself. Zapier I don’t know what they’re doing. And then from here, you know what? You know what I think we can do? Is from here I think [00:42:30] I can, nope I want to do it separate, I’m sorry I don’t want it. We’re done.

Referral tracking. Turn it on. And it says this Zap will run instantly. See in my dashboard. So now let’s go back to my form and fill it out. My name, Phil Davis, davisplusrefer@ [00:43:00], referral name is Mark, I’ll change it up, Lewis. Mark Lewis. 555 dash, same phone number. This is only a test. Let’s [00:43:30] hit submit. At that point I should be able to go into my task history and see a Zap hopefully. Did it fire? I don’t see it. Is is on? Where is it? Referral tracking is on. It was filled out. History. It didn’t, it didn’t [00:44:00] go everybody. I don’t know why.

New, data contact. General communications and products. It could be do I have double opt in on? I do have it on. Let me turn it off. This means I need to go to my email and that’s why I have it as the tip of the week. I need to confirm my subscription. [00:44:30] Bear with me. I’m confirming my subscription ’cause double opt in or confirmation opt in was turned on. So now what should happen I’ll probably get two, I’ll probably get two rows populated. Look at that, of course. I wonder why it did four? [00:45:00] I wonder why it did four? I’m not sure. Probably ’cause I’m doing this ad hoc. But look at this, look at how well this worked. Phil referred Mark and Phil, I’m not going to do anything. Phil referred Mark and look at that, Phil referred, let me do it one more time clean now that I have that turned off.

I hope you all are [00:45:30] seeing the power of this. My name is Joey. Let’s have a different one. Plus Joey Referral name is Troy Manafault, who is that? if there ever is somebody out there still [00:46:00] using that. Fake name, fake new, fake everything. Have a little fun with it. Hit submit. Let’s see what happens. We’ll wait here patiently . I took off double opt. There it is, see? It was because double opt in was on. Oh no, look at that. One, two, is it going to go? Three, four. No it just did two that time. It’s something in my ActiveCampaign account, [00:46:30] I’m probably adding people to this list multiple ways. I’ve got all kind of automations set up. But anyways, look at this. So the notes are here. The email, phone, name, everything is here and I can see who referred them. Even if it duplicated, it wouldn’t matter to me because you can run formulas that will remove duplicates in another sheet.

Anyways [00:47:00] so now I have the data here, I now need to create a contact in ActiveCampaign with this information. What am I going to do? Let’s head right on back to our trusty Zap. Say, “make a Zap.” This time we’re going to start with sheets. When a new or updated spreadsheet row. Let’s do new spreadsheet row. I don’t want to do updates ’cause that [00:47:30] can get tricky. I can get contacts being created all over the place. We’ll do referrals. Go to sheet one. Continue. Already have stuff in there. It should pull. Test is successful. Continue. Look at that. I’m going right back to ActiveCampaign. I’m going to create our updated contact. Save it. Select my account.

You see that everybody. I’m sending it out [00:48:00] using my Google sheet as a data processor and sending it right back in. Now look. I can grab Troy’s email. I can grab his first name. Nope, I don’t want to use first name, I want to use full name. Since it’s all in one. Full name, phone number. And then where’s the notes. I can put the notes, I don’t need [00:48:30] to put the notes anywhere. Maybe if I had a custom field that said notes. Now if I want to do a tag, look what I can do. I can tag them the referrer’s name. You see that? I wonder if I can pre pend it too? What if I do this? Watch this. I’m getting fancy now. Let’s see if the tag says referrer-Joey. Let’s see if the tag does this everybody. [00:49:00] I’m doing this all on the fly. I don’t recommend this at home everybody. I do recommend you do it at home. I don’t recommend you build in front of people on the fly. Do this at home.

A test contact was sent to ActiveCampaign just now. Finish. Referral contact creation. Turn it on. And now I should [00:49:30] see Joey in my database. I should right? Let’s see. Let’s just see here Chris. Let’s see if we pulled this off. Not right now. Are any of these Joey? Look at that. Joey. And let’s see. Let’s see. [00:50:00] Oh wait a minute. Oh no. I was already in here. That’s not, I’m looking for Troy everybody. Troy I believe is the contact. There he is. Look at that. Troy Manafault as a contact in ActiveCampaign. Phone number is there and look at that. It did. It tagged them the referrer. Oh [00:50:30] man. This is great. Name, email, phone number and if I had a notes field here I could’ve added the notes here too. But look at that and I also got to tag them, who the referrer was. Absolutely amazing. Now that it’s all set up, as we close out everybody, right [00:51:00] at the two minute warning, let’s do this one more time.

My name, I’m going to do this for Mark. I’m going to say Mark Binda and his email is This is the contact that is going to be created in my database as the referrer. And then the referral’s name, let’s do [00:51:30] Regina Blackhole, how about that., the names have been changed to protect the innocent everybody. Please do not email these people. Or think that this is a real person. I hit submit. And what should happen. [00:52:00] It should populate down here at some point. There it is. Regina Blackhole, this is so cool. It populate, right when it populated on over here in ActiveCampaign, it should have seen that and then said, “Oh, new spreadsheet row, there’s a new row, time to grab that data and send it out to ActiveCampaign.” Let’s see if it did. Is Regina in here [00:52:30] yet everybody? I don’t see Regina. Let’s give it some time. Let’s see. Regina. She’s not here. She’s not here yet. She is there. It did grab Troy.

All right let me see. Referral contact creation. Oh I’ve got to run it. This [00:53:00] one doesn’t run instantly. Let me run it. It runs every 15 minutes. So if I had 15 minutes to wait, it would’ve ran. Now look at this. Let’s just say 15 minutes went by. And I should be able to see Regina. There it is, look at that. Regina Blackhole. The email, name and phone [00:53:30] number and the referrer is Mark. There it is. They exist in ActiveCampaign and then I have this database as well. Listen, when you do it, it will not be this sloppy. What’s happening is I have, you all know my test account, I have all kind of automations that are constantly updating and adding contacts so this is probably why I’m getting multiple ones. You will not. Yours will look a lot cleaner than this.

But now [00:54:00] if I wanted to do this and say, what if I wanted to do a filter? How do I? I’m not good with Excel yet. There’s filter somewhere. Turn on filter. I don’t know how to do it. But essentially what I was going to do was I was going filter on Joey’s name and then it would’ve shown me all of the people that Joey referred. [00:54:30] But I just don’t know how to do that. That’s fine. But if you are a spreadsheet person, you know how to do that, then you can now quickly and easily see all the people that Joey referred as well as you can go in here and do a search on the referrer tag. I can go in here. Let me clear this out. And do tags and say referrer. Referrer Mark [00:55:00] and then it’ll pull up all the contacts that Mark has referred. See that? Brilliant. Brilliant use case. I’m glad you asked this Mark. Gave me an opportunity to work through it.

And remember I’m going from ActiveCampaign, sending it out, many, many applications of sending out contact data to a spreadsheet. Because maybe you don’t want somebody to have access to your ActiveCampaign account, you just give them access to the spreadsheet and they can see the referrals coming in. They could [00:55:30] even validate some of these. They can validate these and reach out and make sure they’re a real person and you could have a row, a column that says, “Real person.” And then based on whether it’s a yes or no, it could determine whether or not the contact is created. You could do a lot of things with this spreadsheet. Let me delete that ’cause I don’t want to muddy the waters here. Now you have this spreadsheet, this is your [00:56:00] database of referrals. At any point you could pull it up, you could do whatever you want. You can add notes to it. You can do anything and then you know that everybody on this sheet is also going to be sent back to ActiveCampaign.

Now, this is good because what if I had a manual referral that came in that wasn’t through the form. I’m five minutes over, I promise I’m done here. And I can say, Gary Loveheart. Then [00:56:30] I’ll say Say 555 123 and then say he didn’t even know he was real. And who referred them. John. I can do that. At this point I manually entered this referral and 15 minutes later this [00:57:00] would’ve run. It would’ve looked and saw. Email. Oh did I do the email address wrong? No, that’s good. I’ve got the same link. The problem is I’m adding people to the same list. So I should actually have a referral list. Entirely separate list because now I’ve got this loop if I do it manually. But [00:57:30] you’ll see. You’ll see that Gary’s in here. I’m confident in that. I’m sure Gary’s in here.

Maybe I shouldn’t be so confident. Gary Loveheart. He was sent there. Referrer should have been John. I ran it. Anyways, that didn’t work out right because it says the ActiveCampaign field required field email address, email is missing. You know what? I know what it did. It’s looking at these rows [00:58:00] and it’s seeing that there’s nothing there because when I added. Okay, I got it. Anyways my setup was messy. You could do this, I’m adding people to the same list. Mark what I would suggest is that you create separate list for your referrals that they are added to. So you don’t get this crazy loop, feedback here. Feedback loop.

Anyways that’s how you do it. Thank you everybody for attending office hours. Wow, you all stuck with me till the end. Listen if you’re in the Facebook group, [00:58:30] I’m going to tag and just tell you thank you for joining today. Make sure you join the official ActiveCampaign Facebook group. I hope this use case as really useful for you. And I do office hours, we do office hours every Tuesday and Friday. Tuesday at 10 AM, Friday at one PM and you can attend as many as you would like. Hopefully this is helping you get started off, starting your week off or if you’re watching the replay, whatever day, hopefully this is helping wherever you are. [00:59:00] I’m appreciative of you attending and I will see on the next office hours. Have a good one.