Skip to main content
Loading Explore guides…
Dirty Data Classification · Acedra
Home
Explore
Computer Science
Dirty Data Classification
Sign in to save
Chat
If YouTube asks you to sign in, open the video on YouTube.
Watch on YouTube
Reload player
Transcript
113 segments
Copy
Hide video
0:00
thank you everyone for joining us today my name is one of the marketing managers at data science dojo i'm joined here by susan lodge she is founder and managing director classification guru and she's going to be presenting on between the spreadsheets classifying and fixing dirty data for data science so i'll hand it over to her now and if you have questions feel free to use q an idea thank you guys thanks so much and hi everybody thank you for joining this session i'm really
0:33
excited to be talking to you about between the spreadsheets classifying and fixing dirty data for data science if you don't know me i like to make the topic of data data science data cleaning fun and how do i do that well i do it with things like this if the buttons work when you think of superheroes what's the first thing that comes to mind men in tights women with superhuman strength capes lots of superheroes wear capes but only one wears a coat a data coat by day
1:09
she's susan walsh but when dirty data rears its ugly head she becomes the classification guru what are the classification guru super powers consistency organization accuracy and trust with help from her good friend in trusty sidekick the data whisperer hi scott the classification guru wages war against dirty data fighting her sworn enemies misclassification duplicate and the typonator it's the classification guru's job to clean up the mean streets of excel city that was bang out of order so when
1:47
you're in excel hell don't let dirty data get you down call the classification guru [Music] so that gives you a little bit of a flavor around what this session is going to be like and if you're really lucky there might be another video at the end now what we're going to talk about well i'm going to tell you a little bit about myself first where i came from what my background is how i ended up here and then i'm going to talk about what is dirty data because we're all working
2:18
with different types of dirty data so let's actually define what some of those might be and then what the consequences of that so we might be you know working away in our little data bubbles and we don't always realize what happens to the the data that we produce or the charts or analytics that we produce that then go on to someone else so i'm going to talk about what the consequences are there and then of course we have to talk about how we can try and ensure some data
2:44
accuracy and you know it's not infallible but we can certainly take steps to help make it better secret sauce tips is maintenance of data so i am going to show you some simple tips and tricks on how to maintain spot to check some data in excel it's not a tool that everybody likes but it's a tool that we all use so you can take the tips that i give you and you can translate them into whatever method you're using in your own office and then other options are available for doing this
3:21
and then finally i'm going to share with you the dirty data maturity model now you might struggle within your organizations to get funding for data or explain to non-data people the issues that you're having with the data this is something i created to help tackle that challenge and then of course we'll finish off with summary at the end and i will check your questions going through but also we're going to leave some time at the end now there will be some audience
3:50
participation required not much but i will be expecting some answers in the chat so keep your eyes peeled but first of all let's talk about my favorite subject me just kidding um but i think it's really important to explain to you what my background is and where i came from because it wasn't conventional in the least i completely fell into data i had a life as a salesperson for a while then i had a women's clothing shop here in guilford sorry and then that completely and i was so
4:27
broke that i couldn't even afford to go bankrupt and i was desperate for a job so i found an ad online to do some spend data classification for a spend analytics company and i'd never done that before but i'd worked for companies i knew what they were spending their money on and and that's really when i found my love for data and and classifying data and spotting patterns in it but of course after five years there i wanted to move on and i didn't know where i could get a job doing the same
5:00
thing i'm not an analyst i don't code i don't build dashboards but i'm very good at classifying data and so i decided to set up my own business and so i'm coming up for my five year anniversary now and in that time i have worked across all industries countries and languages i have classified and normalized multiple data sets millions and millions of rows of data i have built customized taxonomies i have cleaned suppliers for p2p implementations i have cleansed the messiest of databases
5:33
and i flipping love it and not only that i found that i have a unique uh perspective having a business background and then partially working in data and and i could see that there wasn't really much in the space talking around cleaning data classifying data so i wrote a book and again i didn't want it to just be another data book i wanted it to be a cheeky fun data book so it's called between the spreadsheets classifying and fixing dirty data and you can find that
6:04
on amazon and all other good bookshops if you want to take a peek um it is very much based on this presentation but goes into obviously a lot more detail and then i would just like to share with you that this year i was also listed as one of the data iq 100 most influential people in data i'm up there with chief data officers and some of the most well-known companies in the globe so that was a huge achievement for me to be recognized by my peers so i feel like
6:35
i'm qualified to sit here today and talk to you about dirty data but what is dirty data because my idea of it might be very different from yours so the first thing i'm going to say is is things like misspelt words and that could be in descriptions and names and anything you can think of and then of course there's incorrect descriptions too you know when people have accidentally cut and paste something or they can you know in in the the financing procurement world it's
7:10
purchase orders and invoices and people just put the random things on the invoice that don't make any sense it could be product codes or product descriptions and it could be more uh because of more of a problem for the business than you realize it could be missing codes from products or items as well and then we've got data in the wrong columns and i don't know about you but i see this all the time when it comes to addresses you have the zip code in the city column
7:43
you've got the state in the zip code you've got the country and the zip code you've got everything in the zip code column apart from the actual zip code and it can get really messy and it can be really cumbersome to actually tidy up we are in a global world now where businesses are across continents uh working not in silos anymore but together they might have siloed data still but there are other issues um in the uk you know we're talking maybe miles but in europe it's kilometers
8:20
and then we've got liters and how do you spell liters because there's the uk versus the us version uh centimeters meters or are we talking something else it can get really confusing very quickly if we don't have some standardization across our data sets which also leads to currency issue problems and i have been there i have been that person who has calculated the currency wrong and ended up with 500 taxes instead of 50 taxis so it can be so easily done but it's having these checks in place to
8:59
look in and help guide you that will help prevent the error getting further uh along in the business process and then this is my favorite one formatting issues so again we are across the globe and even within our own departments and companies we are formatting things completely differently so what i would love for you to do right now is type into the chat box how you format your date is it day day month month year year is it month month day day year are you
9:33
using dashes slashes dots are you typing out the months there are a whole different range and i think we're about to see them in the chat now i'm just having a look yup see we've got them all the different types and that what i'm looking at now is exactly what i would expect
10:02
different for every single person so let's figure out a way to work on that and and everyone's got their own opinion on this as well there is an iso format but nobody seems to want to do that um so and i've heard of different formats for different types so in excel it might be one in a file system it might be something else it's um i'm loving see i'm loving the discussion going on in the chat already um because this is exactly what happens now as well things that we've just talked about will
10:39
lead us to our good old friends the duplicates and we see so many of these in data or what i like to call near duplicates if you're working with say names so you might have robert smith and bob smith and maybe even rob smith and guess what they're actually all the same person because three different people typed in their information into the crm system and now we have three versions with different bits of information in each record happens all the time now in my world of spend data
11:13
classification and think of it like your own um [Music] bank statement you know there's a supplier name and what you spent some money on but on a much grander scale and a lot more money the first thing that i have seen people do is classify linkedin that well-known social networking training and recruitment site as a restaurant because the description said restaurant what happened in this instance is the person classifying had not looked at the data as a whole in context you know
11:47
looking at one cell one column or two columns is not enough you need to look at the whole row the whole data set to get context for what's going on um and also a little bit common sense wouldn't part either but this happens and i've seen it happen a few times i've also seen linkedin classified as software it breaks my heart when i see it because honestly it's one of the easiest things to classify but then also with that comes things like surveymonkey we know we do online surveys market research
12:19
no someone thinks it's consulting and this one it might be a foreign company but guess what the name the hint is in the name it's speed courier and yet somebody put it as equipment maintenance and again road runner towing it's not consultancy they're fixing cars but it happens all the time and finally this is my favorite example tinder corporation don't ever assume that you think you know what something is in the data because it will bite you in the behind i
12:52
was classifying for a company in the uk they're a charity um very highly thought of and tinder corporation came up in their data and i'm thinking they can't no not that tinder no that can't be right and and then i saw the description it said data cabling and i was like okay well that makes more sense so i did a little bit of googling and searching and found out that there is a tinder corporation limited here in the uk that is an i.t services company and so the
13:23
classification was correct on this one but had we just looked at the supplier name we would have assumed it was the wrong thing so again don't assume use the context of your data and it doesn't have to be spend data it could be product data and product codes it could be anything you like there will be patterns in the data that will help guide you when you're looking at it and it's the same when i do normalization of suppliers and you might do this as well i found some granger now
13:54
there's granger in the us that is a well-known uh parts supplier nuts bolt screws all that kind of hardware and then there's granger in the uk that's a house builder and i accidentally normalized them all to the same thing and so again had i had the context of the full set of data i would have known that there were two different companies i've obviously learnt my list and now this was a very long time ago but again it just proves never assume always use the context of the data and
14:24
that will really help guide you to know if it's right or wrong and of course you know it's easy for me to show you a couple examples that stick out like a sore thumb but when you're looking at rows and rows of data in an excel file or a table and click or tableau or power bi how are you going to be able to spot those things that stick out well fear not i've got some tips for you later so you'll be able to take those away and practice those afterwards
14:54
how can we prevent this kind of thing well again like i said you cannot assume that you know something always double check use the context if it's classification look at the name of the vendor and the description it could be if you're selling something what's the name of the supplier and what you're selling to them you know there could be instances where you're selling something to a supplier that you're not supposed to so it's really important to know that and then think about what we're
15:22
looking for buy or sell this product to this person or this service does this seem reasonable and if not then you know look at the values see if they help you guide in a hotel classification for example uh you might have spend of fifty thousand dollars that's not hotel rooms that's venue higher for an event so you need to just know that you know there's things that can be different but if you're working with your data regularly that will help you and you know what google you know they
15:58
say google is a skill now i am all for that i'm the queen of google there's not much i cannot find for you um always just double check it could save you so much time in the long run and spot check your data if you're using it in it this month and it's great it's fine it's perfect wonderful when you go back next month it might not be perfect anymore someone might have done something to it so always just spot check to make sure it still looks okay uh and why should we bother
16:28
well this one's popped up first uh wasted money it really is um you're gonna spend less time fixing dirty data um who's who in the the chat is is just a yes is is having to fix dirty data at the moment yeah see you could save yourself so much time i'm going to give you some tips yeah i feel for you i love it but i get paid to do it i'm guessing you guys is probably not what you signed up for also it's costly to rectify i mean you might have to get someone else in and it
17:08
costs even more than the initial project it affects decision-making processes as well you know businesses are making decision on this data if it's wrong that could affect multiple things within the business including bonuses um head count the future of the business and the direction that it's going to take um things like imagine clean data going into your models you wouldn't have to rerun them because something was wrong and you didn't know what it was and you had to check and it
17:41
was the dirty data imagine that bliss that will never happen but we can make it easier maybe you can run it fewer times and then things like privacy laws and we might not often think about that but if we've got dirty data within people's names and addresses that can be against the law and then you could be subject to legal uh action and things like that which leads to reputational damage so that can be harder to come back from than paying a fine or paying someone to fix your data
18:18
and let's face it deity dirty data makes your life more difficult makes all of our lives more difficult keeps me in business i'm very thankful for that but you know i dream of a world where you don't need me anymore these are all quite negative things and you know i'm quite a positive person so i want to end with the fact that actually you know what we should bother because it could increase profitability and obviously you might think well why that doesn't help
18:43
me that helps the business but it could help you if you get a better bonus or a promotion um if you're spending less time on a project but the client you your your company is still billing your client the same amount of money then they've saved time and your time and money so why not do it
19:08
and then there's the consequences of it so what actually happens [Music] well there could be cost savings that would be nice if you did fixed it or you because losses you could improve your business negotiations they might not be so great right now you might have multiple product codes sku skus various different versions of the same thing it can help with your forecasting your budgets targeting the right customers if you've got a crm system full of dirty data
19:52
are you mailing stuff out to someone who doesn't even live at that address or exist because i bet that happens more you think and the cost of the postage the printing of the materials etc it all adds up and even things like shipping the correct products i heard a story that um someone had set the dimensions of a tv like a 40 inch tv to one centimeter by one centimeter and so when they were doing the loading plans the computer thought it could fit hundreds of these tvs onto a truck and
20:23
of course the reality was very different that can lead to things like well delivery is being missed you can get penalized for that from certain retailers all kinds of things can happen you know there might not be enough space in the warehouse it just goes on and on and on and then it affects analytics as well so here are some unnormalized suppliers uh obviously you know you can see there's quite a few of the same companies here they're just spell different ways depending on which region or
20:58
you're in and they have a certain amount of value associated with them but this could be um units this could be things sold things bought it could be anything when you normalize it suddenly you find that you've actually only got four suppliers and that spend with pwc's a lot higher than you thought it was so there are real benefits of having that data clean and tidy and you can really see what's going on and again it's those things of saving yourself time you know that's the most important thing
21:37
and you know i'm preaching to the converted here when it comes to technology implementation and ai you know data clear cleansing is is very often neglected um people within organizations who are decision makers really don't seem to see the value in investing and cleaning your data straight up for a small fee up front you could have a nice clean data set you can implement it and things will run fairly smoothly if you don't clean it nothing works the project takes longer
22:07
you spend more time on it and it costs twice three times as much it just doesn't make sense to me and that's one of my lifelong ambitions is to change that mindset from the decision makers at the top you know generally these errors are only found post implementation if it's some kind of software um and you find that staff lose faith in that data or the tool they become disengaged they claim it doesn't work they don't trust it because it's wrong and they all go back to their siloed spreadsheets
22:38
and then it cost a lot of money to fix a lot of money and even then you know there's no guarantee that your staff will adopt that technology again you may well end up having to abandon the project and i have heard of projects and instances where millions and millions of dollars have been thrown at a project and it's been abandoned it's it's crazy
23:05
and our good old friends ai machine learning and automation you have to clean and prepare that data beforehand it's just so important your ai is learning from training data sets where are your training data sets coming from do you know the source have you checked it do you know the data that you're working with are you familiar with it would you know if it was wrong there are really important questions to think about when you're working with it because just taking your
23:34
time to familiarize it yourself with that data even for an hour before running something could really help you and improve your models and save you some time same with things like machine learning and coding you know it's based on rules from data but if you're looking at incorrect data it's just going to breed more bad rules and cause you more problems and it really is true what they say the old adage of um i guess in the uk we see rubbish in our garbage in and rubbish out garbage out
24:10
that will not change and i like to think of myself as the trash lady of the data world you know it's a job that nobody wants to do but we but it has to be done because everything would be in chaos if it didn't get done
24:28
now let's talk about ensuring your data accuracy that's quite a bold statement i'm not saying that you could 100 do that however we can definitely take steps to improve it and the first thing that you need to know is there is no quick fix there is no magic button there is no software in the world that is going to solve all your problems you will have to review data yourself or by or someone else at some point and get the organization involved it's it's not just data people's
24:58
responsibility to manage data there are people who do not work in data who are inputting data on a daily basis and they also need to understand the impact and the consequences of what happens if if they don't get it right and how it affects their colleagues and get everybody to agree some common standards processes terms procedures dates all those good things uh because it will make it so much easier and then you know don't just leave it once it's fixed you have to keep it and maintain
25:36
it and refresh it and update it constantly and like i was saying earlier spot check it too because that data you worked with last month that was correct it might not be right in another month's time because someone's accidentally deleted something or they've cut and pasted over something or they just have a different opinion about what it could be but it's hard to get people to [Music] come on this journey if they don't work in detail and so i created a philosophy
26:06
that everybody can remember and everyone could work with to try and help with better data accuracy and that's about making sure that your data has its coat on
26:21
so the first thing is c consistent data consistent standards consistent terms consistent processes make sure everybody's on the same page doing things the same way to minimize those errors and then make sure it's organized so think of your closet you just throw a top in there and then you go back to get it the next week and it's not creased and you can't find it you've got to rummage through if you'd hung it up nicely in order with all your other tops by color
26:52
you could have just gone in pulled it out wouldn't have to iron it and off you go data is exactly the same as that categorize it into things like departments regions and divisions you know category by spend if you want have that in place so that whenever someone comes up to you goes oh how much did we sell in the uk last year you've got it all categorized and labeled already so you can just quickly pull something off you're not spending two days trying to pull a whole load of
27:25
spreadsheets together it would save yourself so much time and of course it has to be accurate or as accurate as you can get it so that people eventually once you have your consistency your organized data your accurate data you have your trustworthy data you want to know that when you are generating some ai new models or you're doing dashboards or analytics for your your business that when you give them that data it's the best it can be you're really confident in it you don't want them to
27:57
have any doubts about the accuracy of the data when they're interrogating the numbers or anything like that
28:09
so that was that's all great in theory uh but what about the practical bits of it susan what are you gonna do about that well i'm gonna show you some tips that i use and that will hopefully help you with whatever kind of data you're working with um when you get the output it's just visual checks sometimes you just need to do a visual check there's no other way around it and again it comes down to knowing the data understanding it and being familiar with it that will all
28:40
help and make it easier for you so first of all i gotta say like it's excel it is by no means function however it is an option and we're all using different tools so it's the common one that i can show you my process in you can take this away and adapt it to however you're working so the first thing i've done is i've done a pivot table and in this instance i've got my normalized supplier and i've got my categorization but it looks a bit rubbish so i need to put it into a tabular form
29:15
and then i want to take all the subtotals off because i'm not interested in those and then i can start to scroll for anything that doesn't look right so in this instance we've got dell we all know dell that it computer company uh what they are not is a courier service or company um and this often happens uh in spence data classification you get a number of items on an invoice list and one might be laptop and then printer monitor and then you'll have the carriage or delivery charge as a line on
29:50
the invoice if people are not paying attention when they classify or if if they're using some kind of automation ai ml and they've just looked for searched for delivery in the description and it automatically gets classified as delivery they've not used the context of the data to help them so in this instance they're not they're not paying dell for a courier service they're paying dell for a laptop and the delivery charge just happens to be part of the cost of the laptop so
30:22
hopefully that makes sense um but it's you can't just blindly use keywords and auto classify them because there can be context involved that's basically what i'm saying [Music]
30:40
then it's really hard to flip between the tabs when you're working with excel so i have used this process i click on new window i select arrange all and then i have them vertically so they'll be side by side and what that means is that i can have my pivot table on one side
31:07
and i can have my raw data on the other and what i can start to do is methodically go down the pivot table with the aggregated data and start to look at anything that's not right and change it in the raw data tab and then as i'm going down um i would then refresh all and i've got a question i can see it does not scale well um i will cover a little bit off about that later on um there there is something you can do um if you've got it aggregated it helps um as i say it's it's not foolproof i do
31:45
admit that um you can also add in extra layers of data too so i might want to look at supplier and description for you that might be customer and item sold product and product code could be a number of different things and again just keep scrolling looking for anything and you can also flip it as well so in this instance you could look at categories first and then look at for any suppliers that stick out that shouldn't be in that category
32:19
and i didn't put it in there so i'll just tell you now the other thing that you can do to scale when you have large data sets is you can check them or chop up the data if you've categorized it like i was saying if it's all organized you could look at it but do it by country you could do it by division you could do it by product group um you could do it by department so sales and marketing then finance then procurement then i t group similar things together um that's i i have a tool i use that
32:59
i use in a different way and i'll go on to that but if you just need something quick that you need to look at then pivot tables are are you ready to go so there are other other tools you could take what i've shown you and you could do that in bi you could do that in tableau you could have aggregated tables um the difference is i use the tool called omniscope which is at the bottom that's a visualization and model data modeling tool in one so i can actually
33:31
aggregate my data at the top and have an editable table at the bottom and i can fix my data live in real time so that's how i get around it it can also handle millions of rows of data so that's the way i work generally um you will find a way but it just gives you an idea of different ways that you could spot check your data you could do keyword searches in the supplier name the description name the product and description name if it's numbers you might want to check that it's within a range
34:04
you know that there's not too many extra zeros etc if you haven't already thought of putting some um safeguards in place so there's lots of different things you could think about just depending on the type of data that you're working with um you know if it was say manufacturing and you wanted to manufacture like a thousand products and you knew that you only made a thousand or sold a thousand in a year but someone would put ten thousand in then there's a red flag for you straight away
34:37
but again the key is looking at it getting to know it familiarizing yourself with it
34:48
and then i promise you the dirty data maturity model so this comes from my book between spreadsheets and this along with the data horror stories chapter is a favorite of a lot of people that is real life anonymously donated data horror stories that have happened to them so like i said it can be really hard as data people to explain to the rest of the business the issues that we have so i created this dirty data maturity model as a bit of fun interesting and a really simple visualization to get
35:23
people to actually look at things
35:28
to data and i describe this as the equivalent of turning your underwear inside out to wear it for one more day you know that you shouldn't do it but you think you can get away with it for one more day and then what happens you have an accident and you end up at the hospital and you get found out dirty date is pretty much like that you keep you know it's there you know it's a problem but you'll fix it another day and then that day that you go to fix it never comes
36:00
because some massive disaster has happened so trying to get on top of it from the start is a really good plan even if it's just small steps doing something towards checking it regularly getting people who are inputting it to understand the importance of accuracy all those things are small steps that can help you and then you've got d-class data so at this point you know we're maybe got a few rules and procedures in place we're maybe checking a few things but it's still pretty
36:34
unstructured and a bit random and we're just hoping for the best at this point you might not even have anything more than excel as a tool distributed data so this point is slightly more organized there is a structure to it we might have some tools we might even be doing some analytics on it who knows it's starting to look like it's more manageable
37:03
and then it becomes disorder data so we go to the next steps we have the tools we're just not using them regularly we're not cleaning the data checking it properly it's in there but it's just not in the right order what we're really aiming for is the pinnacle and that is dirt free data and quite honestly this is the hardest stage to be at because once you're up there um you have to work really hard to keep it up there you are you've got well-trained staff you're
37:36
using the right tools you're using automation you're checking you're spot checking you're maintaining you're doing all the things you're supposed to be doing and yet people are still making mistakes and you're still having to fix them but at least there are a lot less of them and you have the tools in place to actually go and fix them so that is the dirty data maturity model feel free to share that with your organization and say look we're here and we need to get to here
38:03
visually people get that people get dirty laundry
38:10
so that's been my whistle stop tour of between the spreadsheets classifying and fixing dirty data uh what we've talked about today is basically there are no shortcuts i'm sorry it's it's just the way it is you have to put in some manual work to get that data right and you have to know and maintain your data but by knowing and maintaining your data it makes everything else so much easier for you to spot those things and then you've got to agree those common standards i mean i've heard you
38:46
know that a customer within an organization can be different things in different departments you might have internal customers external customers it can mean a whole range of things so define what that is define your dates define your units of measure um otherwise you're going to end up with multiple versions of the same thing [Music] and get everybody involved go i know as as data creatures we are not the most uh extroverted of people apart from myself obviously kind of exception to the rule
39:20
but talk to other people you know if if you know that someone is um doing something in another department and you're spending two hours a week fixing it go talk to them show them the consequences of their work let them understand and then maybe they'll do something to change what they're doing at the point of input make it more human and make it more kind of what's you know what what's my impact on other people and of course i'm going to say again and again and again
39:55
make sure your data has its coat on and if you need a little reminder of that here we go are you leaving your data out in the cold you wouldn't go out into sub-zero temperatures without the correct protection so why do so many companies forget to protect their data unprotected data can lead to bad business decisions that cost money and put jobs at risk that's why your data needs its coat on this means making sure your data is consistent organized accurate and
40:27
trustworthy consistent means using the same standards terms and classifications company-wide organized data makes it easier to find think of it like a messy closet it can be difficult to find what you're after even when you know it's in there when organized correctly it becomes easier to find what we want data should be accurate which can mean different things to different people but at its most basic level accurate data is correct and finally data should be trustworthy
40:55
this is critical if your team can't trust the data they might not use it and business decisions around jobs staffing and budgets are all based on data so it's got to be right like any code there are differences in the quality of data services out there if you buy a cheat code it might not be waterproof or protect you enough and it won't last more than one season if you don't invest in good quality data services you will end up paying twice as much if not more in the long run to fix
41:23
the earlier mistakes if you'd like to speak to an expert about data in your own business visit theclassificationguru.com to find out more [Music] and that has been me um i am very active on linkedin that is my qr code if you scan that now you'll be able to connect with me um very much uh post comment uh content very similar to to what you've just experienced i i hope that that has been enjoyable and and informative for you um i'd love to answer some questions and also
42:01
once we've done that have a think about if you've got any data horror stories of your own that you'd like to share or any problems that you might have that you might you might like a bit of advice about so let's do that now uh so i can see there's one question in the q and a and that is will the slide that be available um i can share that i don't know how they would get that out to you otherwise you can contact me on linkedin and i can send that to you that's the other one
42:28
yeah we'll make it available on the on the recording once this webinar is finished we'll have a recording available on youtube and our social media will include the link to the slide link perfect um i've also just seen a question around tips on date formatting honestly the best tip i can give you is just choose a format and get everyone to use it because if you want to change it at a later date to something else if it's all the same it's super easy to change it all if you have multiple date
42:59
formats trying to change them is an absolute nightmare um i can i know from my point of view that we are based here in the uk but we have us clients and every time we open a csv file it corrupts the dates one of my team has had to change his pc settings or laptop settings to the us so that so that when we open files we don't completely ruin the date format so um that that's and again it's that consistency thing it's the same with classification if it's all wrong it's much easier to
43:32
change because you just do it once if it's in bits and bobs then it takes forever to go through and make and change everything and let's have a look what else can i see here uh we've got even after creating glossary or agreeing terms and definition to keep the record consistent how do you make sure that people are choosing the right option for a field just for training and stating any other ways oh that's a good one because people love to just click that first box down
44:00
don't they i think that's one of the data horror stories in my my book actually someone everyone was just choosing the first option um it so it's um there's two answers to this it depends on who the user is if it's within your organization then you know you've got training you can like i say you could show them the consequences further down the organization what happens when they don't take the the right select the right box so that's a way of making them understand how important it is
44:35
um you could even maybe do like league tables um if you can track it like which department has got the most best accuracy or which person has got the best accuracy each month give them a little prize um unfortunately if you're dealing with the general public then you have got no chance my friends um they will do what they want and i have absolutely no idea how we you could win them over um it's it's it's a thankless and challenging task um
45:13
so normalization uh we've got a question is it ideal to normalize student cleaning i normalize before i do anything else with my data sets the reason being i see a lot of things like ibm iw.m ibm inc ibm limited or pwc pricewaterhousecoopers p.w.c etc by creating a new column and normalizing it all to the same company certainly instead of maybe 10 000 suppliers i've got seven thousand because i've normalized them that means i've got three thousand less suppliers to classify
45:52
so that saved me a whole chunk of time already so that's that's why i would always do it at the start um is a really helpful um tool and you can do it not just with names but with products product groups you know or how many screws have we manufactured this month how many nails have we made or how many toilet rolls have we sold by grouping it all together um or normalizing it it's much easier and oh what does it mean to normalize that is a great question so it's a standard it's a grouping of
46:29
standard names or terms or or or things so we talked about ibm i.b.m ibm inc i would group them all together as ibm in a new column so rather than having five different versions of ibm that i'd have to individually classify i could all i could just have one version and classify everything at once um and you get and then another reason is things like you get hilton hotel or the hilton hotel by by normalizing it taking out the the you you get to see um a truer picture of
47:06
how much you're spending how much you're selling how much you're using etc in terms of normalizing product groups um you could do so say it was clothing for example you could normalize all the jackets jackets all the trousers to trousers and you know trousers could be called pants cargo pants uh safety pants all those kinds of things you can normalize them all to just pants just just so that you know how much we've used we've sold we've bought etc so hopefully that answers your question
47:46
and i think that is all the questions so i am going to put it out to you guys now have you got any data stories to share
48:00
are you all going to go quiet on me now
48:07
oh look at that they've all gone quiet they don't want to admit they're all going to pretend that the date is perfect i'm just kidding and i did do a session um at an event the other week and i asked all these senior data people to put their hands up if they had problems with their data and nobody wanted to put their hand up um this is something i see in in my life all the time like everyone has data problems but nobody wants to admit they've got problems not publicly anyway
48:42
let's see um yeah unfortunately using data fraudulently happens um again as long as you're being careful and you don't get into trouble um that that's a tough one um oh mel you all your daisy cleaning escapes so far have been assignments in your class that that scares me because they've probably gone easy on you when you get into the real world it's chaos that's what i've heard i'm i'm wishing you well uh [Music] so someone has got reformatting the column and physician names that are
49:31
formatted in three different ways first last last yep uh that is an absolute nightmare um again you kind of have to try and break it up um
49:48
i can't i can't think without visually seeing what you could do with that i have a few ideas but i think it's quite a manual process i don't think there's a quick way around that one unfortunately
50:02
um i could use natural language understanding techniques to understand semantics um absolutely and and i genuinely believe that in different areas this works a lot better than others so things like nuts boats and screws clothing very good in the procurement world where i might have a description that says a taxi from the hotel to the restaurant you have to be really clear about you know is it a hotel a taxi or a restaurant so it's a bit more complicated or again the context of what
50:40
the spend might be you know sometimes you see the word cleaning in the description and you would automatically assume that that is janitorial cleaning but sometimes it's it computer cleaning so what you know you have to know the supplier name to be able to know that so it really it can it's it's it can be very subjective the area that i work in but there are other areas where it's much more effective um and then what's the best use of tool for visualization i don't think there's
51:10
one best tool i think it really depends on what what you need what your objectives are and and what you get on with because everybody kind of naturally find something that they work better with so as long as it can give you the results that you're looking for um it's the one that works best for you and most of them you can get a free trial so i'd highly recommend you know testing things out if you can um oh what great question what is the best experience through my career i
51:46
could not have imagined five years ago when i started this that i would be sitting here talking to you now doing a webinar um i'm going to helsinki to talk at an event next week and i'm flying out to canada later in the year to talk at another event and then las vegas later in the year that was never in my plans i could never have imagined that people would want to listen to what i have to say i mean for a while when i first started that i felt like a complete fraud because i didn't
52:17
sit in the data world and i didn't sit in the procurement world either but i had this very specific knowledge that kind of straddles both worlds um and i've kind of learned to own that getting the data iq 100 was really great and and also writing the book because i'm not a great reader and i don't really enjoy reading and and so for me to write a book was was pretty huge um so so yeah it's just this whole last five years has been so challenging and so tough and but also
52:54
so rewarding i mean there's been a couple of times where i've nearly had to give up the business um but but sitting here today talking to you it's been so worth it and and that's that's what i would say especially if you're starting out like i s at the start of this data journey i started at the very very bottom and worked my way up and because of that i know all the aspects of it and i see how it all fits in and it gives me a really great perspective so be patient and just learn your craft
53:22
and and the other thing is you know because i i felt like you know i don't code and all that things i could have tried to do all that and be mediocre at it but what i did instead was i focused in on the things i was really good at and made them better and i let someone else do coding and all that kind of stuff i i focused in on the things that i i'm really good at and i really enjoy so that would be my advice uh oh look at that someone is doing subway stores well before that um ah
53:58
there's so many cool multiple databases yep um i see multiple databases with different names different date formats all that good stuff and my book is on ebooks but i don't know about apple i'd have to speak to the publisher about that um someone's been through a data migration process from three different legacy systems yep i did a crm system i pulled together 28 different data sources there was 80 000 records and we managed to deduplicate that down to about 27 000.
54:34
another one i did there was 2.8 million rows and i got that down to 1.3 million rows so uh i have been through my fair share of that um get on the website i've got a youtube channel as well i have so much more content planned for this year including including some online training courses um if any of you have your companies have a plural site membership um i've just had a course launched on there as well um oh my goodness teachers they should know better that's terrible um
55:13
no as a new analyst you are not the problem uh you will soon find that everybody says it's a nightmare um or or that again they want me to admit it publicly but they will behind closed doors or after they've had a drink um
55:34
else has got issues with multiple different locations of files and making reports um someone likes tableau that's great is anyone else using any other tools uh the most beautiful data i've ever gotten to clean up i love um really messy normalized suppliers that have like uh dot inc or comma space inc limited sa um incorporated you get gmbh if you're in germany making that clean is such a beautiful thing and then i love um massive data sets with lots of good keywords that you can
56:20
search like nuts bolt screws paper toner um rulers pens all that good stuff i mean i can of a hundred thousand draw a day to say i could get fifty thousand rows in a day easily just by some keyword searches perfect love it
56:39
uh canada event um follow my linkedin and i will tell you more about it i know we're just about coming up to time here i'm trying to get through my courses on pluralsight at the moment but i will be having some through my website when i've eventually built them and yeah brenda says setting common ontology taxonomy how's how walmart keeps data tidy and that is so true again there's a whole i've got a whole other course on building a taxonomy um as again if you're global you've i've
57:09
just done one for a global client we call them chips here in the uk but they're fries to most other places in the world so you have to make sure your language is universally friendly and on that note i will say thank you so much for your time it's been an absolute pleasure so thank you so much susan for joining us today thank you for this uh lovely presentation and thank you everyone for all your questions and interaction and if you weren't able to get your questions feel free to connect with
57:38
susan on linkedin and we'll also share our website and other links when we have the recording available so thank you so much for joining and we do have a webinar tomorrow if you are interested we'll have are in python the best of both worlds by boyan angeloff who's the chief technology officer at vam so make sure to rsvp and join us tomorrow um furthermore share the link please go ahead and rsvp for our upcoming webinars and thank you everyone for joining today special thanks to
58:12
susan and also fatima for arranging everything have a good day thanks everyone
Here to help you learn
Sign in to keep a private conversation with your course tutor.
Ask me anything about the material…
Sign in to chat