Transcripts
1. CoHse 0 Welcome: Hey, how are you doing? My name is Angela Merkel and
I'm a programmer by trade, but I've spent
more than a decade working in digital
marketing as well. So I'm a lady with
lots of talent, so I can flip from
technical things, programming websites, programming mobile
apps, to wearing my entrepreneurial hat and developing
marketing strategies. And that's what we're
going to be talking about in today's videos. This series that
I've put together, it's going to help you formulate your own powerful mailing list that you can use
in your business. And it's going to be
less than a day's work. It's to use the records that are available publicly
on Companies House, which in the UK is
the organization that manages and all businesses
have to register with, not sole traders but
limited companies. And above. This is all public daily data. So there is no GDPR problems
to worry about this, maybe a couple of
moral and ethics, which I'll also give
you some caveats and walk around in some
of the later videos. But essentially everything
that we're about to do in this whole video series is publicly available data
that you can go and download and work
to your businesses, marketing, promotional strategies
whenever you have in mind. Now, I will be talking
through this series using a real case scenario for
my business and punitive that you have an anchor point of recognition on how you can work this data and use it
in a real-world example. So without further ado, if you want to go and
find out more about me, my name is Angela Merkel. I run a small business called
McCord Media Group Limited. You can go Google
it. I'm based in Milton Keynes and there's various different
arms and branches do my business and one of which is the marketing and
design studios that I will be using this data for
in this series of videos.
2. CoHse 1 SicCodes: Okay, so the very first step in this whole series of activities
that we're gonna do today to build that mailing list is to work with
something called the standard industrial
classifications, or SIC codes. Now in the UK, every time a business registers
with the UK government, it has to tell them what kind of genre of business
they operate in. They can actually pick
four different categories like how the first,
second, third, fourth, position
and primary focus of what there SIC codes are. The SIC codes are
available on the internet. So we're going to work with
this data today and then actually filter this
data down and work with those genres
of business that are relevant to your
marketing activity. Now, as I said in my
intro, the example, real-world example
I'm going to use is the fact that in my
business at the moment I'm going to be holding
my first business to business conference
and accept Expo, which pretty much covers every business
insights sort of like an hour's drive of Milton
Keynes where I'm living. And therefore, I'm
gonna be working with quite a large number
of these SIC codes. I think it's about 60 in total, but in real, real sort
of well-practiced, say for example,
you wanted to run an advertising campaign
just to accountants. You'll end up with about
three or four SIC code. So you can repeat
this activity for every different marketing
needs of your business and promotional activity
that you want to go into. Left-hand monitor, we are going
to find this information. So you can see there I've
done a Google search on Companies House, which is where the information
lives and their SIC codes. And that's brought up
this website page here, and it's the company's
house, actual main website. So I'm going to click on that. And what we're going
to have here is a page of all of
these SIC codes. Now there's loads of
them and with them being on the internet,
they're no use to us. We need to take this
information and basically built it into a spreadsheet that
we can manipulate. The easiest way to do that, It's literally a copy and paste. Highlight the first few rows as you've just seen me there. Do. I'm going to hold
down the Shift key and then hit page down on my PC. And I'm just going to go
all the way to the bottom. You can see on the
right-hand side the scroll bar jumping down at each increment of the button
I hit, we get to the bottom. I'm gonna do a control C
to copy this information. And then I'm just
going to open up a blank Excel spreadsheet and paste this
information into it. Okay, so let's expand that. So here I am, and I'm just going to paste that information. Now a little trick of the trade. If you click in that
top-left corner box, spread out the width
of the columns, and then double-click
the row height. It will make all of
your table nice, neat, and friendly to work with. But you can see the
information nicely. Now, as you can see, the little scroll bar
here is very small, which means there's
loads and loads and loads of information
that we've just copied. And there's way too much for
your business to handle. And let's face it,
say for example, my marketing design studio. I'm not gonna be working
with companies that are growing rice or
sugarcane or tobacco. I worked with business
to business suppliers. So the idea is that you go down this and you literally start deleting records that you don't want all the
way down this list, the k and there'll be
hundreds and hundreds. So you need to get
yourself a good cup of tea and work your way
through this list. So what you want to end up
with is a result of all of the businesses genres
that your business wants to work within a
particular marketing activity. Now, when you've done that, you still need to tidy it up. As you can see, there's
gaps everywhere. So the easiest way
to tidy this up, to select the two
columns of data. You can see here
that the first cell that's now selected is that A1, where the white marker is? If you come up to
where it says sort and you sought by the
smallest to the largest. It's just going to put
all of those salt codes. Are those SIC codes in the
correct numerical order? And all those gaps
have now disappeared. Now obviously you're not
going to end up with pages and pages and pages
just like that. But you'll end up with a nice
number of a couple of dozen worst-case scenario
of the SIC codes that you want to you
want to focus on. So let's quickly just jump into I'm not going to say
that because I'm just going to quickly show you my own SIC codes for this
particular activity. So here's my SIC code lists. Now, because I'm doing
an exponent conference, I actually want to work
with a large number of businesses that offer
services to other businesses. So my particular need
in this case is a lot more general than maybe your
targeted marketing campaign. But I've kind of gone
down my list here and I've ended up with
61 rows of data. And as you can see from this, there are no headers, there are no column titles. It's literally the
data straight in from that first record
that is available. And this is how I need you
to save your spreadsheet. So make sure it's saved
somewhere easily accessible on your computer because you
are going to use it lots over the next eight
to ten hours.
3. CoHse 2 DownloadFileManagement: Okay, so now that we've
got our CIP codes sorted, the next thing we need
to do is actually get our hands on the
company's register, which is easier said than done. So in this video, we're going to quickly look at how
you can download this, some of the caveats and things that you need to think about and actually how to get yourself
nice and organized. Because you are
going to be creating quite a few variations of these documents because
of the size of them, we have to filter them down, save and then make
some more alterations. So it's time to get really organized and structured
before you get going. So we're gonna go back
over to the Internet. You can see here now that
I've already done a search on Companies House
registered download, that's brought up this
first page result, it says free company
data product. Clicking on that
will take you to the UK government's Companies
House website where you can literally download the entire list of
businesses in the UK. Now, this is where things start to get a little bit funky. If you click on
this, first of all, you can see there
it's 421 meg of data. Now, I have a computer
that has been custom-built because of
the programming side of my activities in my business, I use a lot of data and
processing sometimes. And I have been told by an IT company that it's
like a mini server. It's that powerful. My computer can download this file and it can
unzip this file, but it cannot open this file. And if my computer
struggles the chat, so you guys are going to
hit a roadblock as well. So we are going to work with
these seven variations. So all they've done is
taken this big fall and broken it down into
seven smaller files. You can see there
that most of them are averaging around about 70 Meg. Having done this
exercise two or three times over the last few days, I can tell you hand on heart, that each one of those
seven files contains around about 850
thousand records. A case. If you think of all the
businesses who start with a number through to abc, those businesses are like in the first of those seven folders. I think it then starts from
the letters C through to about D or E or F or
something like that. That's the second part
and it goes all the way through the
alphabet right up to Z. And it is a huge amount of data that we're gonna be working with and you can get lost. And it can get
quite overwhelming if you don't get yourself
structured and organized. So what I'm gonna do is I'm just going
to quickly show you now because from my computer to download those seven files, it still took me
about 20 minutes to download them and unzip them. So I'm just going
to show you right now when I've opened up my management on how
I've organized myself. So I have a marketing folder and I've created mailing lists, and then I've just
put company's house. Now moving forward, this will probably be Companies
House July 2022, which is when I'm filming these videos because I'm
going to repeat this activity when I run my second expo and
conference next year. And so I'm gonna be collecting lots of data and it's
all gonna get messy. So just make sure that
you are organized. Now, here you can see the
seven files themselves. If I just right-click
on the first one, you can see instantly it comes up with the ability to extract, or I can extract all. When you do that and you
click on those buttons, it will create this
folder up here. And I've repeated that process
for every of those files. And now I've got the
actual files themselves. So if I take one of these now, let's say part six,
and I click on that. You can see in here that
it's just CSV file. Now the reason I started with
Part six is because I have fine-tunes this
process through love, sweat, and tears,
shall we say over the other previous
five spreadsheets. So I don't want to fiddle or use examples of data
that I fiddled with. So this is raw data that
I haven't yet touched, so that I can give you a genuine experience on how
to handle this large data. So it's exactly
the same process. You're gonna do this
for every one of those seven files
and hands-on heart. It's taken me around
about an hour per file, so it's a full days
working activity to prep these seven spreadsheets for your marketing activity. But once it's done, you
have got a very powerful, trustworthy set of data
that you can work with. Okay, so the other
thing to bear in mind is I've also got
a filtered list. Now you can see that these are the results of my
efforts so far. I have to redo 12 because
obviously I was learning the process and didn't always get the data the
way that I wanted it to. So I've got to do 12 steel, but I've already done 345. I'll explain my naming convention because
it's relevant to the promotional activity
that I'm going to be using for my business
and conference Expo. But obviously you
need to name this the way that it's useful to you. But essentially three or 74757, that represents which of the particular spreadsheets
we are working with. And then I have
basically focused my business on the Milton Keynes and the North Hampton postcode. So MK and an n, and then also micro, small and medium businesses. Now don't worry
about this too much, but I just wanted to name it to myself so that I've
got this trigger in the future when I come back
and use these files as to why and how and what
this data is all about. Obviously, you wouldn't
name yours the same way or a way
that's similar to you. But what I would
say, it's always start with the three
or four for four or five or four because you will get yourself lost
in this process. Eventually, if you don't get yourself organized
and structured. The only other thing
that I wanted to show you here is I've made
it nice and simple. There's my SIC codes. And when I was first
starting this process out, I left myself some notes
on how I could repeat this activity before I
had the brainwave of actually making
this into a little chaining calls for you guys. So basically because life takes over and six
months from now, I'll be like, I know I did this, but what was it that I
actually did specifically? And so I was leaving
myself some notes. So you might want to do
the same sort of thing and leave all of this
information together. So once you've done that, you're now kind of prepped
and ready to go. So what we're gonna
do on the next video, video number three, we're actually
going to open that very first spreadsheets now. And we're going to start
manipulating the day.
4. CoHse 3 Round1 Delete&Filter: Okay, so what we're going to do now in this
video is we're going to open up the spreadsheet
in its raw format. And we're going to delete
the unwanted data. And we're going to
perform some very, very simple filters that aren't going to crush
the spreadsheet. Now I'm going to dive on. So I'm ready primed
to go and open this. So you can see I've navigated to the file and I'm
just going to open it. Now the reason I
wanted you to see this opening process is
because like I said, I've got a powerful computer. This is one spreadsheet. It's already saying
it's not responding. It will respond,
but it will take about 60 seconds for
this file to upload. And I want you to guys to
witness this with me right now. So if you are struggling and your computer is just
it doesn't seem to open, just give it some
time because there is 850 thousand records in
this one spreadsheet. And my computer does cope
with it because I've done it on the other five
spreadsheet's already. So this is the six or seven of those files
that I'm working with. And eventually it will open. You can see at the
very bottom of the screen next to
my little picture. But it's about
three-quarters of the way through or processing
that information. So just be patient and we
will eventually get there. Okay. There we go.
It has opened. So I'm going to
just do Control End to the very bottom,
the spreadsheet. And then I'm just going to
scroll a little bit more. So I'm not worried about
the data just yet, but I just want to show you that this is where we
are starting from. So this is the very end
of the spreadsheet, because obviously
the very first row is the column titles. It means that we have got
850,001 rows of data here. So every time we
do a manipulation, I filter a lookup or whatever. There are ways there are
records and records and records that this computer
has to process and weight. Plus there's all of those
columns of data or fields of data that are not relevant
to my marketing activity. There's so much involved here
that we have to work very carefully because it will crash your computer if you're
struggling with this. And you've tried to open it
and you finally get it open, just go very slowly,
very carefully. Think before you make an action because it
will crash very quickly. And, or you might need to go and see there's someone else's
computer you can borrow. If you're a small business
owner with just a small what I call
a teenager laptop. Okay. So we've done a
control height Control Home. I'm at the top left corner now, we're going to work through
this very methodically. The first thing I'm gonna do is I'm actually going to reduce. So I'm on a 100% zoom. If you'd say if
you look down just literally where
I'm pointing here, let me move myself
out of the way. You can see that I'm on a
100% zoom and I'm going to reduce this down every time
I work with a spreadsheet, I reduce it to 70%. Experienced so far
through this process means that I think the
data on the screen, given the amount of data in the spreadsheet means
that I don't crush my PC, but I can see more
what's going on. So working over from
their first hand, left-hand column, another little cheat that
we're going to do. As you can see,
all the columns of data is quite crushed together. I'm going to move my cursor into the top-left corner that
selects the entire content. And then I'm going
to move my cursor to any line between one of those letters on
the column headers. And I'm going to double-click. Now this will take a second, but what it's going to do, it's going to take a second
because there's so much data, but it will space out
all of these columns to show all of the data
that is in each column. So there we go,
It's just done it. And again, you saw it, maybe have a moment there
it was it wasn't responding purely
because there are 850 thousand records
that we're working with. But now I can see what
I'm actually looking at. So these are the names
of businesses, okay? So obviously we want
the company name. We want the company number, but these two fields, no idea what they're not
relevant to my business, so I'm gonna delete them. So if they do contain data, It's not abuse to my need. We obviously want the address. This is very powerful
for you guys. If you're gonna be running a direct mail marketing campaign, the country now on this
particular batch of records, it doesn't look
like there's much, but you can see here
it says England, Scotland, Wales,
whatever, whatever. Now, we know we live in the UK. I know my business is
working with UK businesses, so I don't need this column of information, so I'm
going to delete it. And every time we delete data, don't forget that
file size is just reducing and it's a
little bit less mud that the computer has to
wade through when processing and filtering
the spreadsheet, which is why we're
gonna do this four times for different mounds. We obviously want to know the
postcode of the business. This is the company category. Now, this particular
spreadsheet, there's obviously
something a little bit different about this
because normally these categories will say things like private
limited. There we go. I don't know what is going on with these first
bunch of records, but we can tidy them
up, no problem. So if you look here, this is where we're going
to do our first filter. And the easiest way
to do the filters, if you notice that the first row is the title of the column, what it exists for. I'm just going to make this
a little bit nice and tidy by coloring it so
that we can see it. Also by five formatting the
text of these column titles. It doesn't just
help us as humans. It also helps the
Excel spreadsheet know what is data and
what is the title. So once we've done this, we can click either on the
filter up here and choose filter or the second
way to find it is to go to the data field
and click on Filter. Once we've done that, if you
were very visual in there, you'd have noticed that
all of these columns, header titles now have a
little drop-down arrow. When you click on it,
it will allow us to sort and see all the
different ways that, that the data in that
particular column is listed. Now, I'm going to
unselect all of this because the only
type of business I'm interested in
working with is a private limited
company. So there it is. I'm going to click on that. Click on OK. And the
moment I do all of this, many of these rows of data, we're going to hide
that. There we go. And you can see
that they're hidden because if you actually look down the left-hand side
where the numbers are, you can see that there
are loads of numbers now hiding and that these numbers aren't running sequentially. There's gaps between them, okay. They've also turned
blue in color. Now we're only working with visibly the private
limited companies. Now we could do this on the address field if there was maybe 200 records on
this spreadsheet. But obviously there are thousands of millions of
post codes and too many for this spreadsheet to handle with 850 thousand records.
It would crash. I know this because
I struggled for the very first few efforts
of working out this process. So this actual postcode salt
is the round for that we're gonna do in a couple of
videos time we will, we will come back
to our post codes. The other thing to bear
in mind is at the moment, what we visually see and
what actually exists are two different things where we've hidden a lot of this
records of data. It doesn't mean to
say it's disappeared from the actual file. It's still there, which
means any processing we do, the computer will
still wade through that result even if
we can't see it. Which is why we have to do multiple rounds of
cleansing and this process. The next field anyway over
that we're going to do is we're going to work with
only active businesses. As you can see here. Some of
them might be liquidations, that might be an
administration are about to be strike off or whatever
the situation is. We only want to work with active businesses at the moment. So let's tidy that
column up there. That was nice and simple. Again, we don't need to
worry about it showing the United Kingdom and we don't need to worry
about that column. So I'm going to delete
those two again, as much data as we don't need. It's easy to remove, remove it. Now, I don't need this particular column for
my marketing activity, but I want to take a second
to talk about it because whatever you are planning
to do, it could be useful. It's called the
incorporation date. This is the day that
the company has decided to register with the UK
government as officially trading. So you can see on
there these dates go all crazy since records began. The first one there shows
the 31st of October 2019, you've got a lot of these
are like 2022,003, whatever. If you are wanting
to work with like maybe accountants that have just opened up business
in the last six months. You could run a filter
on this column of data. In due course, this field of information might be
really useful to you. The particular marketing
activity I'm doing today, this isn't useful to me. Neither are any of these. I am going to stick with
the last made up because I want to talk you through why I'm going to keep that
one right now. From my perspective,
this business, the business conference
marketing expo that I'm doing, I want to work with
valid businesses. So if I click on this at the moment as an
option to filter, you can see all the
different years. Now if I want it to be
really specific and work with just businesses that
have opened up in March, for example, I could
click on 2022, de-select everything and
just work with the March. Now that's too specific for me. I don't know what your marketing
project or activity is. And there's another way there
that you could be able to filter this
information as it is. I'm just going to work
with businesses that are up-to-date in the
last 18 months. I'm filming in July 2022, which means there's six
months already completed in 2022 and all of last
year's 12 months. So that makes my history
being 18 months old. I'm going to click on Okay, it will take a second. And there you go. Now I've got another filter. And again, these numbers of records have changed
again with a lot less. Now showing on screen. Don't get me wrong. There's
850 thousand records in this spreadsheet,
like I keep saying. So there's still
hundreds of thousands still available to
us at the moment, but we're getting rid of as much as we
can as we go along. Now, this is also quite powerful column for
you guys to look into. If I click on this one here, you can see that it actually registers the size
of the business. Now, for my particular
event that I'm doing, I want to work with micro, small, and medium-sized
businesses. Again, depending on
the reference that you use and where you
cite what is a micro, small and medium-sized business. To give you an idea, and
FSB is a good resource. The government website
itself is a good resource, but generally a
micro businesses, generally one to ten employees. A small businesses
generally tend to about 20 or 25 employees. And then a medium-sized
businesses, roughly 20 up to
about 50 employees, depending on which source that you look at and investigate, those numbers do
differ slightly. But generally speaking, everything now that
I have selected is a business from somewhere
between 150 employees about the right type of
target audience for the Expo and the event
that I'm running. Now, the other little
thing I want to say here is in the UK, micro entities that small
business owners like Neighbor, I've got one employee. That micro entity is actually the biggest common
denominating category of businesses that
operate in UK. I can't remember the
exact statistics. I did look it up
about a week ago, but it was somewhere
in the region of about 6 million businesses, the size of mine that
are operating in the UK. So that's why there is
such a large number of micro entity showing here there
isn't a mistake in my filter is just literally
the nature of the beast. Now, moving along
from this process. Oh, please don't
have There we go. It's just having a moment.
It's having a moment. That's a prime example of how the spreadsheet just
struggles at times. Okay, I can hear the fan on my harddrive going
crazy at the moment. Now, I'm going to delete as many of these other records
that I don't want. There's one other column of
data that I definitely need. So I'm just going to delete
some of this and just explain to you why I
want that other data. This is obviously for you
guys or two other columns, I should say, for you guys to design for yourself what you do want and what you don't want. Let me just delete these. And I'm just going
to slide this over so I can see some
more stuff on screen. Now this is when we get to
that first SIC code category. Now when you register
your business, you can actually register up to four different categories
or genres of business. Now, I particularly don't
think we need to have SIC codes 234 in play with
the activity that I'm doing. I think realistically
as you can see, most of that data
is empty anyway, but most people will just list that very first
primary activity. The core reason
their business is known with Companies
House and away they go. So as you can see, I'm
just deleting those other 234 and some other details
that are not relevant. Okay? I'm mostly just going
to start tidying up these fields that you can
see it a little bit more. So I'm just going to make
this a little bit smaller. So this is my SIC codes which we'll deal
with in a minute. Now I know from the rest of
these columns I don't need, but whilst I'm deleting these, let me just talk to
you about these URIs. This is essentially a
hyperlink that will take you out to a Information
page about the company. Now, it's not
particularly useful to me in the marketing
activity that I'm doing. But I'm going to
leave it in purely because I'm gonna give this
as a research activity. And you'll see that in
one of the later videos to a virtual assistant online. And whilst they're
researching other bits of data that I want to know
about my companies. They might find this useful to go and learn more about them. So I'm just going
to quickly delete this and then I'm going to just demonstrate what those URIs, Stanford's, let me just
right-click and delete. So that's all of those
fields that we don't want. Now, I'm just going to
show you how to work with these URIs whilst we're
here talking about it. So I'm going to click
on the first one. I'm going to put my cursor in the formula bar and I'm
going to hit return. If you can see now it's
underlined and turned blue and when my
mouse hovers over it, it becomes a hyperlink. And I'm going to just hold down the Control key on my keyboard whilst I click on that
and make that pop up. So this is how if I need my assistant to get
some more information, it's easier to read the data. That way. You can see here
all the information that we saved and what's
useful on that client. So it's just something
helpful that might be useful to
your own team as well. So I'm going to leave
that as it is right now. But as you can see, we are now done as much as we
can do on this round one. So before I lose this data, I'm going to save it, but I don't want to
overstate my original file. So we're going to do the first copy paste to a new document that I need
to teach you guys to do. I'm going to click
on the actual letter of the column header. And I'm going to slide
all the way over to m. So all of my columns of data and every record they
contain is now selected. I'm right-clicking somewhere on the screen as you
can see my cursor, I am going to copy. Now when I do this, I can go to file new, create, a new workbook. Okay, I'm gonna move my cursor to that A1 field
and right-click. Now when you paste, this is
when you have to be smart. It's not just the normal paste because if you do
a normal paste, all of those hidden records
and fields will come with us. We don't want that, we
want to get rid of them, which is why we've just done
that filtering process. So if you move your cursor
to where it says values. So it's the little icon with the 123 and click Paste
or click there. Now you can see that the row
columns are seeing quenches. My little blue cursor
is still running down those hundreds of
thousands of records and we'll go check out how
many there are still. Now, all of those data, the ones that were
left visible on the previous
spreadsheet now show, now on this spreadsheet. So the first thing before I do anything else, I'm
going to save it. So this is the
spreadsheet of seven. So all I'm gonna do is
six of seven underscore. And that's the filter for first filter that we've
done some filter one. Okay, so now I've got
my new spreadsheet, saved the k, but we still got thousands and
thousands of records. You can still see that
it's taking a moment to say, let's go to the end. Let's see how far that
first data cleanse is left me with a 177,744 lines of data. So we've got rid of
like 700 thousand lines of data there, but it's still like a ridiculously huge amount
of data that we've got.
5. CoHse 4 Round2 SicCodesFilter: Okay, so we're gonna go through this whole sort of filtering
and deleting process again. So this is like round two
of our cleansing actions. But this time we're going
to focus on the SIC code. So those are the general
genre of businesses that we actually want to focus on and target with our
promotional activity. So if you remember
back in one of the first videos in this series, we were concentrating on the SIC codes that we
want to work with. That's when we're going to
use the file in this video. So let's dive on
over and now work. So as you saw in the last video, I'd just save this. So I'm just going to tidy up this file and we're going
to literally work through this and do a fee lookup
on those SIC codes, which is, isn't as
easy as you think. I know VLookups in their own right can be a
little bit fiddle soon. Okay, so we are going to use the postcode in the
next cleansing. So we're not going
to touch that. We now no longer need any of these fields of data so we can delete that
again, remember, like I said, less data we
have in the actual file, the better if you do
want to work with dates. And you've copied the values and they turned out like this. Just select the entire
column format cells, go to the date and
then click on Okay, and the numbers,
the actual dates in their date format
will reappear, but we actually don't need that. So I'm going to
delete these two. So I don't need that. There's my SIC codes which we're going to work with this timeout. And there's my a URL links. So I'm actually going to grab this entire
column and I'm going to slide it to the right because I want all of these empty
columns next to it. And that means this empty
column can now be deleted. So now I've tidied up
and worked with my data. Okay, So if you
look at and let's inspect what an actual SIC
code line of data looks like. So if you can see here
we've got five digits, a space, a dash, a space. And then we go into
the name of an, a whole bunch of characters
and character number changes with how many
titles and what's going on. But we don't need any of
that data to do the VLookup. What we need to
concentrate on is these five numerical
digits, okay? So first of all, we need to extract them from
the information. So the easiest way to do that
is to use a left formula. So I'm going to place my cursor
to the right hand side in this first column of empty information next to the cell that I'm
going to Inspect. I'm going to click up
in the formula bar and I'm going to type the word equals left open curve, like a curved bracket. So this is where we
start the formula. Now, now that the
formula be created, I'm going to just
cheap by clicking on the actual cell that
we're going to inspect. So as you can see
there, this is in column I on the second row. So this I2, put my cursor
back up in the formula, put a comma because we finished telling it
where we're looking. And then I'm going
to tell it we want five digits and then I'm
going to close the brackets. So the same style
bracket I opened with. Once I've done that,
I'm hitting Return. As you can see now, this number, which is the five, the first five digits only of this cell of information shows. Now, this is the funky Part. One way to copy this
down is to drag, which is fine, but
it's long-winded. As you can see with
the little bar there on the scroll bar, how many pages you'd be there
till Christmas scrolling. However, you may not
have any choice in the matter if your computer crashes doing what
I'm about to do. So first of all, I'm
just going to hit Save. Now the shortcut to copy
that formula all the way down is to move my cursor over. So it goes back into that for cross pointer like a plus sign. But instead of dragging,
I'm going to double-click. And it's going to take a second. It didn't crash my
computer, so I'm lucky. Some of these spreadsheets, it has actually crashed. But every SIC code now exists all the
way down these pages. Okay? Now that we've done
that, the next thing to do is understand that
what we're working with here visually and
what is actually behind the scenes in the cell
is two different things. So this cell might show us the formula
of the five digits. Sorry, the cell might
show me the five digits, but it actually contains the
formula and it's this bit of data that is used
in the VLookups. This is no good twist. So I'm just going to basically
copy this entire column. Right-click, copy, move
to the next empty column, right-click, and I'm going to paste those values just
like we did before. And now. When I click on the cell, you can see that the
data visually shows. And behind the scenes is
that the right data as well. So we now know we can
work with this data, which means this
column is redundant. We don't need it,
so let's delete it. Now here's the caveat
in this process. This is a number, but it could also be recognized right now as texts
and all sorts to the formatting of
the information in the cell plays a part
in the VLookup process. Now, I could double-click
on the cell and hit Return. As you can see, that number now move to the right-hand side. Alignment. That little green triangle and the warning has disappeared, but it still remains and
all of these others. Now, like I said,
there's still like a 150 thousand or whatever
it is backwards. You don't want to
double-click and hit return on every one
of these to get these to present correctly
as the right format of data. So we are going to cheat. Again. We're going to
select every column. We're gonna go up
to the Data tab. And we're going to
go along to where it says text to columns. Now we're going to keep
all of the defaults, which he says
delimited tab general. And I'm just going
to hit Finish. Now if you watched that data shifted to the
right alignment, every one of those
little green triangles and warning signs
have now gotten it. Now we're working with data that is in the
correct format, as well as the visual and
the actual cell data itself. So basically I'm, what
I'm trying to say is happy for us to work
with it in a VLookup. So let's get that
VLookup taking place. So again, moving my
cursor to the cell directly to the right of
the field we're looking up. Obviously this is the first
column of empty data. This time I'm going to use
the actual formula function because it's quicker and easier. As you can see, I've
done this a lot, so it's suggesting to me
the fee lookup process, but you might need
to go and look for it in order to work with it. So I'm just going
to click on, okay, It's bringing me up now. And arguments are like
a wizard box to help populate the instructions
for the VLookup. So just think of this
as a set of rules, a set of commands or set
of instructions telling the Excel spreadsheet how we want to look up this
particular data. So the first one is, what is the value that
we want to look up? Well, that's the value
living in that cell. And as you saw me clicking
on that populates with the cell located, which is j for the
column it's in, and it's on the second
line. So that's all good. Next, we actually want to look up the actual SIC
codes themselves. So I am going to click
on this little arrow. And the visit box obviously
has a smaller file. I don't know what the
word is there and I'm just going to
scroll and bring up my SIC codes document that we prepared in one
of the earlier videos. Now I'm literally just going to click on the column header a. And it's going to select every single value
in that column. And as you saw my little wizard pop-up box
whenever it is, came with me on this journey. So I'm just going to expand it back down to see the full thing. And the moment I
click in the columns, it returns me back to the
original spreadsheet. And here it's saying
to me, so what, what information in
that are we looking at? So it was the first
column of data only in that SIC code document. Now, last of all, we're going to put
the word in here, faults just because
we're obviously only wanting to work with the SIC codes that are
valid on our document. So there's gonna be a load of these SIC codes that
are not relevant to us. So when I hit on okay, the first one ironically
isn't relevant to us, so it's put in this little
not applicable message. I'm just waiting for the
spreadsheet to finish. You can see it's still
thinking about stuff. There we go. And it's saving. I'm just going to make
sure it's definitely saved before I do
the next action. So I'm going to pick up their
wallets while it's saving. We are literally going to copy this formula all
the way down again. So like we did before, I'm going to double-click
and it's going to populate. It's going to take
a second or two. And as you can see there now, all of these column is
populated all the way down of records and records of SIC codes or telling me
if it's not applicable. So now that we have this
data is 100% reliance, Reliance still on
this column so we can't delete it or do
anything with this column. So leave everything as is. But we do need to
give this a header. So I'm going to put
SIC codes valid, okay? And now we've got our header. We can turn this into a filter, whereas my filter
button, there we go. And we can click on
that down arrow. And as you can see, it's listing all of the
different SIC codes. There is. But it's also listing the not applicable which
we don't want. So I'm going to unselect that. Click on Okay. Now we have now got visually
a list of records that are 100 per cent the
types of businesses genre we want to work with
in our marketing activity. Like before, before
we do anything else, I'm now going to copy this to a new spreadsheet and save it. So Copy, open a new spreadsheet. Right-click the values. Okay? And I'm going to save
it and then we're going to go straight
into the next video.
6. CoHse 5 Round3 PostcodeFilter: Okay, So we're going straight
into this next process, which is pretty much
the same, again, deleting the data
that we don't want. But now we're going to
filter on the postcode. And there will still be
another challenge with this. So quickly diving over
to the left-hand screen, let's just tidy up our
spreadsheet again down to 70%. And I'm just going
to widen the column so I can see what's going on. Let's make them a little bit
smaller at some of these, we don't need them
to be this big. There we go, right? So we've got our SIC codes. Now that we've sorted
by that we don't actually need these
two columns of data, so let's delete it. And now we're going to
work with the postcode. So what I'm gonna
do is I'm actually going to leave this postcode
column where it is. I'm just going to copy
it for the moment. I'm going to paste it in here. Okay? So we've now got all
of our postcode bearing in mind that there are hundreds of thousands
of millions of different postcode in the UK. And this is why we've
left this filter to last. Because if we go
down to the bottom of this spreadsheet,
where are we? There we go. We are still working
with 49,527 records. So there's still
so much data here. And with so many different
variants of postcode, we would have
crashed the computer and the PC and the Excel
spreadsheet and everything. Pretty much trying to
attempt this at anytime before this third
round of cleansing. There are two ways to
filter this document, okay, now I'm gonna
show you both methods. And we're going to
talk about postcode. So you might need
to go and watch the next postcode video or the one on GB maps
before attempting this. But it's going to be
one of the videos like six or seven down
in this series. So you might have
to come back to this video depending on
your marketing activity. But I know for my event that I want to focus
on all businesses inside Milton Keynes. So that's an MK postcode zone. I know I want to focus
on all businesses inside the North Hampton,
which is adjacent to S. That's the N. N for normal postcode zone. Okay, So I'm already
a step ahead, but if you need to do your
homework on post codes, watch the other video and
come back to this one. Right? Now. There are so many post codes. The first problem we have it, it says it's not gonna be
able to show all of us, all of them in that list. And in fact, it, that list shows the first ten
thousand variations. There are so many UV
unique combinations of postcode that it would crash. So we have a problem
before we start. Now, you might be lucky enough and I'm going
to attempt it. Sometimes it's worked,
sometimes it hasn't. By de-selecting everything. Starting with Milton Keynes
postcode to MK and a star. The star is like an asterisk, meaning a wildcard character. As you can see, it has
worked which is brilliant. I'm going to select that. But I also want the NM
post codes as well. So I'm gonna go back up here. Without pushing my luck,
I'm going to type in N, N star and see if it works. Now this is sometimes on some of the spreadsheets
that hasn't happened and it has crashed the spreadsheet and it just
hangs on, hangs and hangs. Now clearly, there wasn't enough to cause a problem
here and it's worked. If this works for you, what you'd want to do now
is add this second round, third round of proof
and or ever post codes to the selection on the
filter and click on Okay. And now what you can
see here is a list of postcode and N
and M play-based. So I've achieved my goal. However, this is probably out
of all of the spreadsheets, the only one that I've actually
managed to do this on, I have had a huge problem. And if I've had that problem, you guys are going to
have that problem. So let me show you
another version. So I'm just going to save this so I don't crush the spreadsheet
and then I'm going to clear my filter.
By clearing this. I'm back to where I was
before I started anything. So essentially we're
gonna run a VLookup, but that in itself also
has a little bit of a problem compared to
when we did it before. So we need to start extracting our first two digits
on the postcode. So I'm going to type in
their left open bracket. Click on the field that has the information comma two digits this time and then close
it and hit Return. I'm going to copy that
all the way down so that action is no different to when I taught you the left
formula before. It's just working with two
digits, not five digits. Okay? Remember though that this is
not the actual proper data. It's the formula data. So we still need to copy this entire column and we
need to paste the values. And once we've done that,
we can delete this. And to be honest with
you, we don't need this column of post
codes anymore either. So let's delete it. Okay, so I'm now
gonna do the VLookup. Now before we do that,
we have to tell it what we're looking at. So I want to look up post
codes with an MK and an nn. So this is where you
can get funky and make your own list. So this is the equivalent
of that secret code lists that we wanted
to use before. All I've done is created a second spreadsheet
in the same document. Okay, now we've got our sick to letter fields
ready to look up. We can go straight
to the formula bar. Click on V lookup. I can select the field
that we want to work with. And now I can click
on this little arrow, go out to my second spreadsheet, highlight those two fields, come back column one, and then hit the word false. Now, we are going
to have a problem. So I'm going to copy
that all the way down. Everything in here is
showing us not applicable. Now, if I want it
to be really due diligent right now
and scroll down, I would find in Milton Keynes
or North Hampton share. And I know therefore that
the results are not true. Okay. The reason that
results are not true is because if we go and investigate any one
of these fields, we look at the
formula instead of working with the A1 and A2, which is going back here. So A1 and A2. If we go and look
at this formula, we're looking at
a 348, that if I, for some reason
this time around, it just sees the
data differently. So to solve and fix that problem when you're
inside your formula, we just need to go back and
change that to A1 colon A2. But I've just got, remember
which field is on. So I'm going to just
do it on the top one before I close myself a problem. Once we've got the A1 and A2, what we're gonna do is
put a dollar sign in before the a and the one. And then move our cursor to the dollar sign for
the a and the two. Now the dollar sign kind of
locks in that field as being, no matter where it's
copied down to, it, has to always
look at A1 and A2. If I come back onto there now, you can see the
dollar signs exist. And now if I hit that
and double click, it goes all the way down. And I think the next problem, there you go, There's an N-N. I was going to say the
other problem that we might have is that we
didn't format the data. So you might need
to have done that. I haven't. It's all good. They're all results now showing, so you can see them k
and so on and so forth. Now, we can filter because we don't need
to do anything else. We can actually just put in
a title so valid post code. And then we can hit
that filter button. Where has it gone off
and then turn it on. So now we've got
our little pop-up. And now you can see
that the only data in that entire column,
there's three. It's either going to be m,
k, and n are not applicable. So if we de-select
the not applicable, we've now got a list of just valid businesses in the geographical area
I want to target. And you can see that that there are loads and loads and loads of fields hidden by looking at
the column, the row numbers. So just like we've done, we're going to copy everything. And we're going to do
we're going to paste. I'm going to save it
and then we're gonna go on to the next round.
7. CoHse 6 Round4 PrepareResearch: Okay, so we're at a
point now where we have filtered out hundreds and hundreds and
thousands of records, and we are now left with
those that are correctly in the geographical areas we want to target our
marketing campaign. And they are of
the right type of business genre that
we want to focus on. And we know that they're
active businesses, that they are up-to-date
with their accounting needs and we know their address, their company number,
and their business name. So what's next? Well, that's no good to
me if I wanted to do an e-mail marketing activity or an SMS text message activity. Or if I want to give this
data to my sales team to do a code cooling activity, all of which are
completely valid, your own opinions
and morals on that. We will cover some
of those caveats, shall we say in another
video in just a moment. But I can't really work this data to my full
advantage in less, I just wanted to do a direct
mail marketing campaigns. So we're going to dive back over here and we're going to
tidy this up yet again. So let's start off by
sorting out our columns now, this time I'm going to
color them in and make the columns names stand out so that I can show you what
I'm gonna do as we go. And I'm just going to reduce
that down to 70% again. So here is the address
fields which for direct mail marketing campaign
now would be brilliant. That's all our post codes. We know how to find information on the
business if we need to. We've got our CIP codes,
I can delete this. We don't need any of that now. Okay. Now, depending on
your business needs, you might want to keep
those two records. I've already explained this one. I don't know at the moment with the particular marketing
activity that I'm doing. This is going to be
useful to me or not. So I'm going to keep
it just in case, but you could quite happily delete this
information as well. Now, if you don't need it. However, like I said to you, there was a lot of
information on here that I still need to know
about these businesses. So I'm gonna just widen
some of these columns, move along and I'm just
going to fill them in. So we need to create this now as a spreadsheet I can send
to a virtual assistant. So the first thing I want
to know is I want to know the contact or the
decision maker or the business owner's name. So that's their first name. And I want to know the
contacts last night. I also want to know the
contacts, job title. I also want to know the
contact mobile number or cell phone number,
whatever you want to call it. And I want to know the
contact e-mail address. So this is basically the
information that I want to learn or get my VA to look for, for me on a research activity. And I'm going to talk
to you about how that can happen
in just a moment. So I need another
five fields of data still for this to
be really useful, powerful information
from my business. But let's face it, not all of this
information is gonna be available on any
company website. We also need to know the
office phone number, the generic office
email address, email E-mail
address, as it were. And we could also do
with the website URL. And you could carry
on if you wanted to and say like
the Facebook page, the LinkedIn page, etc. But I'm just going
to stop there. That's enough information that I need for my business needs. So I'm just going
to color those in a little bit different as well. So realistically, there are about eight fields
of data that I personally will still need for this to be useful
and powerful to me, I'm not particularly interested in direct mail
marketing campaigns because even if I was to take this data and send it
to a mailing house, Royal Mail operates this system. There are some private
companies where you can just upload a CSV file, design your mail shop
on their system. And they will integrate
your CSV file, print the pamphlet, leaflet, postcard, whatever it is, and the person's name
and their address, and the stamp on it all in one go for you and away you go and you
can pay for that. So as we stand right now for a direct mail
marketing campaign, you could go, all systems
go and it's cost you what 45 minutes worth of time to
create this spreadsheet. However, as digital
marketing guru, this isn't useful enough for me. I want more. So I would give this now
as a spreadsheet to my VA. Now I've got a great guy that
I worked with in Argentina. He's English, married, an Argentinian woman,
lives out there. So he's just a brilliant
godsend to me. So I can send this file to him. And he will go and
spend a few days populating the fields
that I missing. And he will do that by
looking up the company named, doing a Google search
on the company name. Then from there, he will
find their website. Hence, the website URL
will be able to be populated once he's on the website page
here we'll look and see if there's a team page. Then you will see if there is, there'll be able to
find the director or the head of
marketing or whoever it is that they want to attract and fill in the contact details, name, and their maybe
their job title. That information might also include their mobile and email. But if it doesn't, now they
know who they're looking for. They can go into
LinkedIn and they could go and maybe extract
that information there. There's also the chance when
they're on the website that they can find the
office e-mail info at, hello, at sales at or
department e-mail. And they can also put a generic office phone
number on there as well. So that's how I operate this. Now my guy in Argentina
costs me £5 an hour. Now you might think, Oh, that's so, let's slave labor. But what you've got
to bear in mind is that other countries, the value of their
currencies are weaker to the power £5 and
hours like they're mining Twenty-five
pounds an hour and they're happy to
do this research. So there are loads of VAs. You can look at things
like Fiverr up work. I'm a freelance site if
that's still exists, a current member,
I normally work with Upwork Fiverr myself, but you can literally send off this CSV or Excel
spreadsheet to them and say, go find this information
and come back. Now. That's how I populate
this and it will take two or three days
and away we go. The only thing I didn't say to you guys that I've
just realized is how many records are we
actually left with now? Most of the other
spreadsheets have been, been, been between 210,200 records. This one's ended up
with 1072 companies. The biggest one out
of this filter. And this process has left me with just
what I think it was 1221 or something like that. So you can see here that
there's a thousand records. And so this is just a research
activity now and away they go and then they'll send
that back and then it's really powerful
and useful to me. So couple of things. If you need some help with
working out your post codes, maybe you're doing a bit more of a specific targeted email. I've got another
video coming up. And if you want to know about
how the GDPR aspect works, I've got another
video coming up, so this series
hasn't finished yet. We've also got one more video. So there's three more videos. How you can actually send this
data now and use it to be traffic and get around that problem of the
GDPR and all sorts. So keep watching on
the next videos.
8. CoHse 7 GDPR Usage Traffic: Okay, so before we
go any further, let's just talk
about GDPR here in the UK and what
we've done so far. Now, every single activity we've done so far
through this series of videos is on data that is publicly available from
the UK government. So we have broken no laws
how we use that data. That comes down
to a moral issue. Again, at the moment
we've broken no laws. Okay? So if you wanted to
use this data, like I said, in a direct mail
marketing campaign, we're ready to hit the ground
running Pretty much Now, you could upload this CSV
file to a mailing house like Royal Mail or other private companies where
they will do a mail merge, filter, branding,
print and post, all sort of thing in
one activity for you. And it's job done. You have broken no laws. To the best of my
knowledge anyway, because it's public
data extraction or the research of getting their email marketing
side of things. This is a little bit
more convoluted and a little bit more complex,
so I'll talk about that. And also the SMS. Text messaging is how you
could also use this data. Now, let's talk about
e-mails for a moment, okay? When my researcher goes
onto a website and find out the office or
department generic emails is because that data, again, is publicly available on their website or it could be on their Facebook page or their
LinkedIn profile, whatever. It's publicly available. That means that we can use
it and we can send to it. But how we send it is the moral issue that we
are going to talk about. The same for SMS text messages. So for example, you saw maybe
on my LinkedIn profile, I've got my mobile because I use it a lot for my business. I'm not fast. So people like me, you might be able to extract their mobile number,
which point you can. It's readily available
on the Internet. They didn't want it used. They shouldn't have
put it out there. It's them to control
their profiles and what information they
allow the public to see. But if it's being
publicly available, you can use this data. You can then upload the CSV
file of mobile numbers into a SMS marketing platform and
send them text messages. And you've broken, again, no laws as long as you've found that data through
legitimate means. When it comes to
email marketing, this is where people get there. I'm going to say knickers
in a twist a little bit. Alright, so we can use any email address that we
find in the public Internet, whether that's extracted
off of a website or we have found it
on someone's profile. And we've built it
into our mailing list, where we send that mailing list is where things get sticky. Now if we send that mailing
list, say for example, I use active campaigners
might email marketing tool. But there's things like
a web app and Mailchimp. If I was to send that CSV file
into somewhere like that, those particular companies
run their own business and they have taken the moral
high ground of not allowing unsolicited e-mail
correspondence to be populated inside
their platforms. So they will only allow you
to work with permission based email at public permission
based email marketing methods. Okay? So we're going to use this
data in the same way that we would generate a paid
advertising campaign as a traffic
generation strategy. And then we're not
stepping on anybody's toes and we're not upsetting
any marketing platforms. And all that means is you'd
copy that entire column of email addresses once
it's been populated by your various person, researcher, assistant,
whoever it was, and then you would go out to something like your
outlook software. Now there are a few caveats
to work with with Outlook. First of all, if
you was to paste that entire list of
e-mail into either the two or the CC field you are
in major, major hot water. People will be screaming
at you about breaking GDPR laws because
when that recipient, or those thousands of recipients receive that email
in their inbox, they are going to be able to see the name or the
e-mail address of all the other thousands
people that you've just copied and pasted
from your email list. That's when you are
breaking GDPR rules. From what I understand, that's when you get
into sticky hot water and everybody under the sun will be screaming at you and you'll get
a bad reputation. So if you copy that information into
the blind carbon copy, when those thousands of
people who have that email sent to them and the
recipient opens it, they will only see that they are the only person
receiving that e-mail. So they will not see the
other thousand people that you've copied
and pasted and sent in that one action, they'll only know
about themselves. So you then are not
breaking into GDPR rules. You're not upsetting anybody. It's just a personal e-mail from you to that person on a
regular Outlook email. Okay, now here's the strategy. For me personally,
I have created some signatures that are actually already contained the
email message that I want. And I just tweak this a
little bit from time to time depending on the method and
where I get my, my data from. So you can see on this line
here that I was actually working with the fact
that I'm a BMI members. So I was hitting
a whole bunch of BMI groups explaining
who I was, et cetera. But you will notice
that I've only got one link in this email, and that means this is now a
traffic generation strategy. So let me just take you
out of this process for a moment and think
about this logically. If he was to run a paid
advertising campaign on Facebook to get an advert
on somebody's timeline. The purpose of that advert
is to generate a click. That clip will send them to
your website or landing page when that visitor lands
in that destination, that page takes over this email
marketing tool right now, this process is exactly
the same thing. Anybody receiving this
one-on-one personal email seeing this link, and
they click on it. She says, and they click on it. That trigger of a click is just sending this
person as traffic, as a visitor to the
landing page destination, which happens to be, in
this case, my website. And from there on in
everything that people do, whether they've come to this
page through social media, wherever they've met me on a networking event and
taking my business card, whether they clicked
on an e-mail that I've sent them and landed
on this page. This page in its own right, is its own marketing activity, at which point they could go through and register
their ticket, which will take them
out to an Eventbrite learning page at the
bottom of the event. That will say, do you have
permission to store your data, data, data, data,
they click, yes, I have gained permission
based marketing and I've got a Zapier
connection that feeds my active campaign. And from that point forward, I have populated my active
campaign mailing lists through legitimate channels with permission based marketing, asking that person when they've signed up
for a free ticket, can I put you in my mailing list and they
would have said yes, and I have broken no rules, and I have done nothing wrong. Likewise, if they book a stand, if they go through books stand, they end up in my basket, like any other
shopping cart where you end up and then
you come down, you proceed to the checkout. Exactly the same process of
any other kind of website. It doesn't matter how that
traffic got to that website. This website is designed
to ask for consent, for permission based marketing, and that's all there is to it. So all you have to
bear in mind is that this email and sending it, you must send it through a blind carbon
copy if you don't, that is a 100 per cent on you. I've given you enough warning. People will scream at you and you might be reported to people. I don't know how it works,
but people do get really upset to see that they're
part of a thousand people. The same if you bought
a mailing list. So you go to a mailing
or data broker and say, I want to buy a thousand
names, emails that are there. And they supply this with you. If you send it
using this method, you will still be
happy days if you put that e-mail in the
two or the CC, you're going to upset a
whole bunch of people. So all of this email
process right now is just purely there to generate
traffic to a landing page. And your normal marketing
processes for gaining permission based
marketing activities into your main list takeover. So it does mean that
if you want it to work this mailing list and
center four or five emails. Using this method, you
would have to manually do it four or five times
through Outlook, four or five times using the BCC field four
or five times. Now, here's where the
funky part comes in. Outlook itself as a
software program. And the server that transition, that that message transition through and gets sent
out into the cosmos. Both will have a capped limit on how many people you
can e-mail in one go. Now, I use an IT company. You can go and Google it and down and look on the Internet. My Microsoft Office 365 account, you can go and fiddle
with the settings and you can up that you can up that cap on both an
outlook daily basis. And I think you can
test the server now I think don't quote me on this
because it was ages ago. I asked my IT guys about it, but I think any particular
email is probably 250. So if you've got 1200
people on there, you're going to have to
send this in batches of six or seven emails. You also need to
just double-check that the number of emails you can send out through this
process on a daily basis. I think it's something
like a thousand maybe. So you might also have to
spend this activity of traffic generation
through a couple of days. The other thing to bear in mind is that this is
your real email address. So you could have a
thousand people suddenly replying to you and
it will get crazy. And then those really important day-to-day emails that
you normally have to deal with would get lost in the ether with all this
craziness going on. So the other piece of
advice I'd give you, open yourself or get
your IT company to create yourself a
second email address. So for example, in my
business right now, you can see here that I've got Angela Merkel group limited. This is my regular
day-to-day e-mail address. If I use that, I'm
going to be bombarded. But what I could do is click on From and I could select
all of my others. And sometimes what I've
had is ISAs created there. That's my old alias for when
I changed my business name. But I had like Angela Merkel. So I could put
Angela Merkel app, a cogroup and basically
operate to mailing lists, to email inboxes for myself. And then that way
it kinda separates the activity and you know
that any correspondence taking place in the
second email account that you have is just for this marketing activity and then your day-to-day life
isn't affected. So that was the other
little caveat hint tip and usage that I wanted
to share with you today. So the next video is about
researching UK postcode. So if you're marketing activity
is geographically based or centered on around a certain
specific area in the UK. Here's a good go watch this video because
it's going to show you how to extract that information
and know how to do.
9. CoHse 8 PostcodeResearch: Okay, so what about
if you are doing a marketing campaign
that is very specific to people in a
specific area of the UK. You've seen so far on my
videos that I was using quite a large top-level
generic Milton Keynes postcode or an op-amp to postcode. So that's quite a
lot of people for a lot of small businesses
wanting to do a smaller, maybe direct mail
marketing campaign. That is way too much data
and it will cost them an absolute bomb and it's
not a good way to go. So you'd need to be a
little bit more specific. So how can you find
out what you need? Well, this is a website
that I've found. I'm not affiliated with
these guys whatsoever. I'll just let you
know when you feel like you've struck gold on a website that just does
what you want it today. This is one of those moments. When you go over to my Internet, you can see I've already
scrolled through and navigate it two GB maps.com. Now they've got loads
of different ways to search post codes, counties, all sorts, whether
it's NHS, I mean, you name it, they got these
guys have got it covered. But the, the tool that I want to show you for this activity
today is down here. It says distance polygons. Now I'm going to
start by putting in my office address postcode, so MK 169, y. So let's use this
as kind of like an anchor to center out from. Now. I can search by distance, now could do that by time. So for example, anybody
inside a 90-minute radius, which is what I did for my Expo conference
marketing activity. Or I could have
said anybody that's within a 50 mile radius, so I could have done
it by distance. So I went with time. And then the number
of increments presents these three boxes
and the color codes below. So basically 90 minutes in batches of three
is 30 minutes, 60 minutes, and 90 minutes out. In those color coded categories. You can do this up to ten. That's a bit overkill, I think. Again, it depends on
what you're wanting to do and how you're
wanting to research. But you can obviously select what you want and
when you're ready to go, click on Create. Now that's going to
take a few seconds. And whilst that's
doing its thing, I just want to say to you guys, this website is completely
free of charge to use. However, when you're doing these searches that it hasn't
won its logos over it. It just say it's free, but you can't really
get the information is clear and crystal clear
as I'm about to show you. But it's £20.20 pounds,
three years usage. So it's kind of
like a no brainer, really spend more of that on a ticket to the cinema
or beer or something. Just spend the 20 quid because
it'll make your life so much easier if you can freely use all of the tools
available to you. So it's now finished
and as you can see, I'm quite heavily zoomed
in on the MK postcode, so we just zoom
out just a second. And you can see here now
how the rings around my office is showing the
different driving distances. So the green here
is anybody that's inside a 30-minute driving zone. Yellow is the 60
minute driving zone, and then the red, obviously
90-minute driving. So it's really quite useful
information now here's where I think that £20 also comes into
play is worthwhile. When you click on Save. Now actually I'm just
going to zoom in so I can show you something
a little bit more. When you zoom it and then you've got your map just
how you want it. So let me say I say I wanted to focus on the 60 minutes there. Okay. And I'm happy if
you click on Save, obviously give it the
name that you want. So MK 90 minutes. But hit the Save as Adobe PDF. When you do that, it's a lot easier just to go straight to a PDF instead of doing a save. And then another side click on the download and
you can see that it's just downloading up here. I've obviously done
this two or three times while I run this as an example, you can open the PDF and
this is the beauty of there. So obviously now
we've got this data saved forever and a
day and let's face it in the UK are postcode
areas don't change that much. So this is quite valid
now for a long time. But because it's a PDF, we can zoom in, so
that's a 180 per cent. I can go in an N, an N. So if I really
wanted to target, let's just say those postcode really close to the
center of Milton Keynes. I can see them in a
lot more detail now. And I can basically target
them that way and away we go. So we can save that as a
document to use for future day. And that's why I wanted to
show you this particular tool. So like I said, I'm not affiliated with
these people at all. Depending on your
marketing activity, this could be a really
powerful tool to you in this process of it
might not be needed. But that is just
another little trick of the trade that I
wanted to show with you.
10. CoHse 9 Thanks: Hey guys, okay, so that
brings me to the end of this process of today and this
little marketing activity. So now you know how you can find all of the UK businesses that are registered
with the government. Filter that information
down based on geographical location
and the genre of business and whether
they're active or small, large, medium-sized business,
et cetera, et cetera. And then how you can
work that data using some freelancer websites to get other information
that you need. It means that even
though there's a little bit of a time delay and a little bit of effort here, you can a 100% trust the
data that you are receiving because you know that the government website
updates this monthly. So this was the July
it's three weeks old. So how no matter where you go to a Data mailing house or
whatever strategy you do, there's a chance that the
data could be out of date, but this is up-to-date
and trustworthy. And of course, you know that the information
being presented is exactly the kind of
businesses you want to work with for the marketing
activity you want to do. So that's it from
me today on this. If you want to find
out more about me, I will just quickly
throw up here my websites and my business
is called McCall Media Group. This is my parent website. If I just slide on down, there's a few different
areas to my business. So McCall Studios is my design
and marketing services. I'm a programmer by trade, so I actually publish WordPress plug-ins as
well as mobile apps. Those are the primary
two things there. The events is the news
side of my business, which is why I have created this marketing activity
so I can promote my first exposure and confidence with four different
ones planned in 2023. And the retail is for
different programs. And do it yourself
training programs that I'm going to be publishing. But that's it from me today. So thank you very much indeed for watching
this training video. I hope you found it useful. And if you do need any
more assistance or help, you can go find my
contact details out my website to that and
thank you very much. Bye bye.
Angela McCall, Multidisciplinary Digital Creative
