GitLab Marketing, 15 Apr 2020

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: MPM - Sponsored (external) webcast list clean training (Prior to MOPs upload)

Description

This video outlines the step by step process on how to do the first pass at cleaning up the list for Sponsored (external) webcasts prior to passing to MOPs for upload.

Questions? Reach out on the #marketing_programs slack channel tagging @aoetama.

A

Okay, so we are going to go through how to clean an external partner webcast list before marketing ops handling.

A

So the first thing you want to do is when you get the list from the partner, you would go to this list. Upload issue. That's attached to the epic, usually.

A

And within this list upload issue, there is instructions on how to clean the lists so going to the lists clean step. One section you would open a template, then we'll clean do a first pass on the clean.

A

So what you do with this template there's and if you want to read more than there's how it works tap, but within this template there is a special tab for each of us. We can create one for you, but if you don't have one, you can also use my tab. I'm gonna delete any data, that's in the blue column. If there's any any data, it is not blank because, usually that's from a previous list, but first you want to make sure you clean that list. The own list.

A

And you start with a blank sheet.

A

Without any data, so.

A

What you would do is you would go back to your original list once you delete up all the volt big that in there and then good new columns.

A

You'd go back to your original list and.

A

You will take the data that any data that's available in the blue column from your list, so in this case its first name last name, email and country when it's enforceable. So let's go all the way down.

A

Take it to the blue column.

A

One thing you want to make sure is basically that this blue column is your original raw file. Make sure you don't delete any columns in this blue, the ones with the blue and the blue air colors cause. Otherwise it will break the formula for the green columns. These green columns are columns that sound, basically black, being data using ah then we'll clean. The do a first pass clean on the data, so yeah make sure you don't delete any of them any of the columns, not even the green one.

A

Actually, look it's like just don't do any of the columns in this sheet. One thing you want to make sure is: you want to make sure the data.

A

Say this: is there a thousand rows here make sure that that is also there's values all transposed all the way to the green columns? So sometimes this in the previous data, if they like, there's no data here. That means the formula may not copy over to the last row, so you want to make sure to copy over the formula. The last row by popping.

A

By dragging the rose to the last column, if say dragging the rosales dragging the formula to their last rose, if say, the data is not available.

A

But I validated that the data is available for this one, so yeah I was just showing you how to do that, but we aren't comfort.

A

In terms of the formula all the way down.

A

And then what you want to do is you want to copy over this data the green columns into a new tab on your original spreadsheet.

A

A

A

The coffee, the green column, into a new path on your original spreadsheet, name it or marketing ox upload.

A

A

Then even pace format, only let's see if you press top the end, just paste in my copy over just a formula, and if you are so you wanna copy and paste values only and formula, and then the first thing you want to check is the well. You can delete all the unnecessary all of the blank one that wouldn't have any data in it.

A

Then I think the first thing you want to do is you want to.

A

Make sure the email syntax is correct, so anything that is, if you go back to the guideline here and on how it works, it will say: um email sent back see if this impacts of the email is not Matt, meaning it includes and irrelevant, and the Commodore UK or the iOS or the common domains. They will be listed as false, so you need basic and an email can be updated to make it ready for upload.

A

um Sometimes, even though it is false, if it looks aweful correct, it's fine, that's um just make sure there's no misspellings, so I would do what I would do is I'll just filter for false and just quickly take a look at eyeball the email and make sure it looks correct, even though it's not cool calm. In this case it looks like it's correct, so I'll, let it be. There's no misspellings or anything like that, make sure there's no extra space.

A

And if they're instantly delete the space or correct the domain, if there's any misspellings looks like this is correct and then the next thing you want to do is you want to check for duplicates and get lab email addresses, so the way I would check for duplicates. Well, the easiest one: let's get rid of get lab. Email addresses. First.

A

So I didn't do this.

A

So you can filter by the purple color, because I think that, basically are all the good levers or, alternatively, by condition types campaigns.

A

And then just delete all.

A

The good luck, emails.

A

A

And then the next thing you want to do is delete the duplicates.

A

By sorting it sort range you want to sort by email address and then by country.

A

You it's descending, so you want basically one the one that has values to be the first one.

A

So start my email address, ascending and then sort by then by country descending.

A

And really quickly.

A

Just want to make sure we preserve the even the country seems like it's the same anyway, but okay anyway, you can do that and then do the same exact formula that I show you earlier exact formula like that.

A

So it's matching it basically against the previous row to see if the email address is the same.

A

Copy it all the way down.

A

Copy/Paste values and then filter untrue and delete those rows.

A

Because these are essentially to do with it.

A

Once you do that, you can delete the duplicate column and then the email simplex column. You can delete that.

A

And then you want to actually call them here. Call member status.

A

A

And source, so the easy one first source will always be, or let's go from member status. So to get the member status, you need to do a vlookup based on the attendee report, so the formula will go. You look up so you're, basically matching the email address here against the email address in the attendee report to see which ones are attended versus moshos before we do that, we want to make sure the attended column is actually on the right of the email them in a way for it to or for it to under. No that.

A

There's no way to do a lookup, otherwise so I think what a quick way would be, instead of instead of copying the whole instead of moving the whole thing here, you can just copy-paste the attendees column.

A

And create a new attendees column on the right, the email column so like just pick one, maybe call him al that has nothing at night. Instead insight into space, please I'll show there's cheetah, tended column for now and then you'll do a vlookup that face.

A

If he look if and B lookup combined, so if we look up column, c2.

A

Call the all the way to L, so you select the area. You want to look up.

A

, so how many column one two, three, four, five, six, seven, eight, nine nine.

A

If you look up.

A

A

Or Jess yeah just do a vlookup actually, instead of one if I think it'll be easier. So we look up.

A

So remember status: it's a snorer, yes, so copy it over all the way down.

A

A

There needs to be: you need to put dollar signs on me.

A

Look up area to make sure that it doesn't drag all the way down.

A

Dragging it all the way down so okay, so you do that.

A

Copy it all the way down and then do a fine, so copy paste, special paste values only and then do a fine replace.

A

So if it's now replaced with so no means it's no show the place with no show, and then you want them specific range specify only this column.

A

Replace all and then you want to find yes and replace it with attended.

A

You'll replace all okay, then.

A

A

And then you want to at the campaign ID, oh yeah, you would go to the epic to get the campaign. Id there'll be a sales first campaign exception here in the epic and you can link. If you have Salesforce access, you can link to it. You can click, link, click, the link and it will take you to the campaign page and the ID is the last few last things that the last characters and numbers combined, the last 13 characters or 13 or 15 I- think 15.

A

Maybe as a pendant today sounds first URL, so you would think that it's sort of 7-0 one copy and then you would put it into the campaign ID column and for source. It will always be webcast, so just put webcasts, because this is an external webcast, so copy and paste it all the way down paste all the way down copy that formula. All the way down. I mean the value all the way down, and you are good to go. I think this is basically the finalists.

A

A

And so yeah, once that's complete you'll, take the URL of the newly of the bill lists of the spreadsheet, of the clean, spreadsheet and original spreadsheet. Basically just link the Google Doc into the.

A

You wanna follow these instructions through, like removing and a etc from title state, remove state values for countries they are not United States in Canada and sure all phone values are actual phone numbers, but since we don't have all that we're pretty much good because we only have first name last name, email and country, so I just checked all of that um not on conversation. That's okay, so like ready to upload google, doc and tech marketing up. So what I would do? Yeah it's just paste heat place.

A

It here say: hi instant is usually the pump will do this upload, so Gibson Barton. You.

A

A

A

A

A

Mops usually take about so you'll upload this list, let you know if there's anything that needs to be corrected are not clear and they usually think about 24 hours to upload the list. Yeah. That's all thanks.

A