This week on the Boagworld Show we have talks on the enigma that are regular expressions and consider how we approach our businesses.
Paul: Hello and welcome to the Boagworld show the podcast about all aspects of digital design, development and strategy. My name is Paul Boag and joining me on this week’s show is Marcus. Hello Marcus.
Marcus: Marcus the judge.
Paul: Yes, that is the new role that you have taken on. No, you are to be nice to our guests.
Marcus: I am nice to everybody, mostly, sometimes.
Paul: I kind of…
Marcus: Compared to you I am nice, come on.
Paul: Well, that is true. I had this idea of having this nice altruistic conference where all men are equal, all people are… You see immediately I’m being sexist. All people are equal and everyone gets an equal go at things but no, you just want to tear people down. You want to turn it into pop idol.
Marcus: Yeah, we can have prizes at the end of it, people would love that.
Paul: Yeah, no, because I would have to pay for prizes and I am too tight.
Marcus: No, but they can be Paul’s gold star and things like that.
Paul: Oh, okay. We could do that. Yeah, I like that idea. Did you have a nice bank holiday Marcus?
Marcus: I did. I’m trying to remember what I did, it was mostly drinking which is probably why I can’t remember what I did. No, I did a lot of walking which is always nice went out with my dogs. I got wet a lot, it poured with rain when it was supposed to be the hottest most beautiful bank holiday in history…
Paul: Well I very wisely worked on the bank holiday and took the Thursday and Friday off when it was gorgeous sunshine.
Marcus: Friday was particularly nice wasn’t it. I did slip off a bit early. But yeah.
Paul: Yeah, unfortunately I then managed to burn my big balding head, which was rather unfortunate!
Marcus: Well, yeah. One forgets, I went to… As you know, I went to Barbados recently and I went out and like you do I went out in my swimming trunks and you resemble a milk bottle and you burn, unsurprisingly. I managed to burn my right shoulder twice because it was so nice in the sea, I put all the cream on like you do and I sat in the shade most of the time. Then it would be like “Oh, go into sea again.” So you’d sit bobbing around in the sea for about half an hour and then wonder why your shoulders are burnt. And your nose, that’s always the one for me.
Paul: Yes, nose for me as well. So there you go, and then on Saturday night, while you were out drinking and being rock-and-roll I spent until 2 AM reorganising my contacts.
Paul: I don’t know. It’s just kind of happened. And then…
Marcus: Reorganising, what does that mean?
Marcus: Deleting people, it does doesn’t it?
Paul: … I’ve got all of these. What does it mean? Sorry?
Marcus: It means deleting people.
Paul: I did delete quite a lot yes. But also I had people all over the place. I had people in Pipedrive, my CRM, I had them in my contacts. It was all just a mess. They were everywhere. So I sorted them all out and then for some reason people mocked me on Twitter about it.
Marcus: Perfectly reasonable I would have said.
Paul: So, I doubled down spent Sunday evening reorganising my bookmarks. So there we go, that was my exciting weekend. Hey, I tell you what mind, I found a really good tool for organising bookmarks.
Paul: No, this is an important issue because since things like Delicious all fell out of fashion and I get so many articles and cool apps and stuff and I wanted a way… And I found something called raindrop.io. I highly recommend it, you should check it out.
Marcus: I will.
Paul: I know that you want to organise your bookmarks too.
Marcus: I’m not the world’s biggest organiser. I tend to work… I really like when we went from, in the olden days with email and you used to like have like little folders that you would create with all your different names of clients and you would set them to go into a different folder, when it sort of went to the more Google kind of model of massive dump of stuff…
Marcus: …that you can search on, that’s much better in my way. That’s kind of how I do my files at home generally. Huge box that I have to look through once a year. And it’s kind of like, well, I would spend much more time organising it into little files with labels on them have that once a year “delve in” so.
Paul: Yeah, no, you’ve got a point. I’ve got to say… My bookmarks, just into some rough categories and tagged them a little bit and just, you know…
Marcus: Tags are good because obviously that helps search.
Paul: I mean yeah, it’s searching. Because I read all this stuff and then later on I go where did I read that? And I can never find it again.
Marcus: Well, at least you actually bookmark it because quite a lot of the time I read stuff and I don’t bookmark it and I think “Where did I read that?”
Paul: That’s good, yep.
Marcus: And then I have to use Google again to try and find the thing. And try and think of a word that might have been in the article that is kind of, you know, individual or different.
Paul: Hey, before we get into the first talk can I pimp something?
Marcus: Go on then. Would I like it?
Paul: Well, I think you will actually. I think it will be a subject that appeals to you. To be honest it is a thing that you could do as well, you would be just as capable of doing it. I am running an online workshop. You know I did one of the persuasive design…
Marcus: I’ve seen you advertise this, yes. I watched your video Paul, well most of it.
Paul: Oh, did you?
Marcus: I couldn’t stand all, you know, right to the end!
Paul: Well, no obviously not. I mean there are limits to how much you want to see of me, or hear of me. So yes, this is how to convince clients and colleagues the right way. Which is basically a four-part online workshop that I am running. So it is done over four weeks, an hour and 1/2 each part. It’s really, kind of, just taking all those little bits of techniques and things that you learn over the years from working with clients and kind of putting it all together into a kind of coherent course. Because it strikes me that the biggest problem most people struggle with is getting other people to agree to what needs to be done. So I’ve put a bit of a course together. I was originally going to give it, well, I am still giving it at smashing conference in Barcelona but unfortunately not everybody in the world can get to Barcelona.
Marcus: When are you doing that by the way?
Paul: October. Are you going to come?
Marcus: What is the date?
Paul: Let me have a look. Err, I should probably know this.
Marcus: As long as it’s not the first or second then I think I have to go!
Paul: Do you?
Marcus: For business reasons.
Paul: For business reasons you haven’t quite worked out. It’s the 18th of the 10th.
Marcus: I see no reason at all by can’t do that.
Paul: I think that would be awesome. I’ll give you a discount on attending the workshop stop
Marcus: Great. Smashing… Com… A full 100% discount obviously Paul.
Paul: Well, if I could I wouldn’t!
Marcus: 18th. Is that before the conference or after it?
Paul: Oh, I don’t know. Don’t ask me questions, you’ve got Google, use it man. I mean what do you want from me? So anyway…
Marcus: Okay, I’m excited, I love Barcelona.
Paul: Well I’ve never been, I’m very excited.
Marcus: I’ll have to go now just to show you around then.
Paul: Exactly, it would be rude not to Marcus.
Marcus: Consider it done.
Paul: So yeah, so I’m doing an online version basically where we are getting into some… part one is on how to understand your colleagues and clients better and what motivates them. Then we look at creating winning arguments. Then we get into how… Better ways to collaborate with people. Then the last session is how to recover when things go wrong. So, I’m so confident in the advice that I give you in the first three sections that I have to do a fourth section saying that when all of my advice doesn’t work this is how you recover! I’m really selling it well arn’t I. Anyway.
Marcus: I always think the hardest thing, I suspect, just to be that kind of annoying person at the back in the workshop, the hardest thing to make things happen is for people who don’t want that thing or whatever to happen for their own political reasons. And if they are just a bit mad. That’s the hardest thing.
Paul: The political one, that’s okay, we actually get into that and working with the politics. The little bit mad, the unpredictable person, is more tricky. It is a difficult one. But I suspect we will get onto that as well. Now some smart alec who signs up for the course is going to ask that because you’ve mentioned it on the podcast.
Marcus: I didn’t think that at all as I was saying it.
Paul: No! So now I am going to have to come up with an answer. How do you deal with insane people?"
Marcus: No, but you should thank me for that Paul because you will be going in with your eyes open now.
Paul: Anyway, if you want to sign up and ask that annoying question you can do it by going to Boag.world/convince. If you sign up using the code podcast I will give you 25% off. I’ve just made that up. Add… Code… To… Course… For… Podcast. I’ve just taken that as a task. That’s how well-prepared I am for these things. So there we go. Shall we do our first talk?
Marcus: Yes please.
Paul: So this is from Paolo and he is talking today about whether you are a freelancer or a business owner which is a really interesting concept and I very much enjoyed his talk so this is Paolo’s talk now.
Are you a freelancer or a business owner?
Play talk at: 10:19 – A talk focussed on providing insight into where you might fit on the freelance-to-owner spectrum. Presented as a mix of pointed questions with related back-stories that ask the listener to question how honest with themselves about how they run their business.
Hello, my name is Palo Fernandez. Since the 2015 I’ve been the co-owner of this small business called luscious orange focused primarily on graphic design and web development. We are based in Winnipeg, Canada. However, for the 10 years before I merged with my current partner I was on my own and I would always tell myself that I was running a small business and truthfully, looking back, I certainly looked the part of a business when in reality I was operating quite a bit as a freelancer. So this conversation is to discuss some of those nuances both in terms of making that transition from going from freelancer to a business owner or perhaps just help people identify if you are walking that line and what you are really doing. So this is coming up because quite often, including myself, I told myself “I’m a business owner.” And I was but I wasn’t really operating like a business. I was sort of operating in between and that led to a lot of hard lessons that I learnt eventually leading to starting a new company and merging with a partner. So what I want to talk about is helping you identify if you are in that same boat and giving you some suggestions or ideas to get through it. I’m pretty much going to go through four different topics, it’s going to… I’m going to give a brief background in terms of how I came about it and where I am coming from and give you some recommendations or suggestions to help you assess. Now, this conversation won’t involve any discussions on design, programming or anything really technical. This is a business operations and soft skills conversation. So obviously, if you are rolling your eyes, which I completely understand, let me tell you about why you might want to listen to this and not just turn this off right now. The truth is no matter big or small in what you’re doing, if you are making money it is not just about the work. I know every designer and every programmer would want that pure approach to “No, it’s all about the work.” But unfortunately it is not, there is a reality to doing work that needs to be assessed. Everyone pays taxes, everyone sends invoices, everyone creates estimates. So there is more to the work then just doing the work. For the rest of the conversation I will be posing questions that might help you identify where you land on the freelancer versus the business owner spectrum. Let’s be clear, this isn’t a one or the other type of conversation, there is a range and what might work for one person might not work for the other. So this isn’t about judging this is about helping you truly assess how you actually operate and if there are things that you can do to improve.
So with that in mind I’m going to jump right in and talk about the first one. This is kind of talking about how you deal, like I said, with all those things other than work. So going to ask you sort of a question. When you talk about, internally, in your head, with you or your partner or by yourself or wherever you are, when you talk about taxes and billing and quoting do you consider that to be the headache that comes with the work or is it something that you consider core to your success? If you consider it a headache, it’s a thing that you tend to always put off or the ting you resent then you are probably more on the freelancer spectrum and here’s why. If you are the owner of a business you actually really value those areas. Those things that are a headache to some people are actually what keep your business moving. They frame how you get work done, right? There are processes and all these other things but in general it is not just about work coming in the door it is how you handle all that stuff and keep the bigger machine running. Even if you are on your own you still have to deal with these things so if you resent them, you don’t give them the respect that they deserve to keep your business running. So, as a good example for years I would always put that off to the end and I sort of just hated it. When I found that I merged with my partner we started realising that he actually really enjoyed that stuff. So now I actually have the benefit of not having to do that anymore and my life is so much better because of it! But I did realise that I certainly wasn’t giving it the attention or respect that it deserved. So, that’s the first point, how do you deal with those other things because it is important.
So the second topic I am going to talk about will feel very similar but it is actually a smaller nuance. This isn’t necessarily about how you deal with that stuff but talking more about how you pay yourself, okay? So this is something anyone who does design or development would have to face and this is actually quite generic to business in general. You need to keep that money coming in the door but what we want to talk about is how you deal with that. So, for years, once again on my side of things, I would work on bigger projects, they would come in and I would feel like I was having more success but I was, I am from Canada so I was a sole proprietor which means the money I built was money coming to me the human not to my company. There are different ways in which that goes about and this is very much varies between region, country and all that kind of stuff. But what we want to sort of step back and say is “Is my income directly tied to the given project or is it tied to the company?” So in other words do you pay yourself a salary or you just eat what you kill? Okay? Because for years I would eat what I killed and money came in the door and it was good times and bad times and it was very inconsistent. However, when you are a business owner and you have that set up there is a much more of a framework around you. Okay? So now I get a paycheck and if our company has a really good month I don’t all of a sudden make more money, the company made more money and it has a bank account that hopefully gets filled with money for those bad days. When there are good days you feel the bank account. That is an important differentiator. If you aren’t there yet you are definitely landing on the freelancer side because that stability is critical to growth and being happy. Okay? And this means more than just bills coming in, this includes paying for stuff like software, Google, Adobe, whatever you are paying for. All those five dollars a month kind of services out there. There are all these things that have to happen and if you don’t deal with them all the time, if you’re going back to that first point that end of year old just deal with it all, then you are not separating your personal success from the company’s success. There is an important topic here, what’s called a burn rate. Essentially how much does it cost per month to keep the lights on? If you don’t know that number then you are a freelancer because you are sort of just wild West sort of approaching to it. There is nothing necessarily wrong with that and that may work for you but if you want to transition to being a business owner you have to be able to separate your personal success from the company’s success, at least somewhat.
So, the third topic is robustness and capacity which sounds really dull and lame, I understand. When I talk about robustness and capacity I am not talking about sort of the, you know, the ability to take on more work. That’s… Always find a way to take on more work, if more work comes in the door you can always find somebody to help you maybe, you are collective or a group of people, a partnership that kind of stuff. But what you want to step back and assess is how well can you deal with setbacks and how well can you deal with things as they change. A common term that is used is something called a bus number. If you have never heard this term you won’t forget it. What it indicates is that for any given task or job or thing that has to happen in your company, in your organisation, how many people have to literally be hit by a bus, it’s not a very perky topic but it’s true, how many people have to be hit by a bus before no one can do that job. Okay? So, for a lot of freelancers their bus number is one. For almost everything, okay, and that was what it was like for me for many years. So what you want to make sure you can assess is you want your bus number to get higher and not just tell yourself that it got better but actually find ways to solve it. For me personally I really identified when this became a problem when a couple of years ago I went on a week-long vacation to an area that had no cell phone service. It was literally off the grid for a week. Now, besides the fact that I recommend this for everyone in this day and age is just a good thing to experience, it also made me realise that I was a lot more alone in my business than I thought. It was one of the main reasons I merged with my partner to form luscious orange because we wanted to have that stability, you want someone else to have your back. And, you know, a lot of the times the bus number is still one for many things and that is okay but if you are not even asking yourself the question that is where you can land in some problems. So step back and say “Is there someone else?” You know, sometimes you might be working with partners of contractors or collaborators and that is great, and that is an important part. That might be how you grow into forming a business but asking yourself the question is the important part.
So the fourth topic in the last topic we are going to talk about is accountability. Now, let’s be clear I am not talking about accountability to your clients. If you are a reasonably nice human being and you care about your work you are going to be accountable to your clients and are going to try to do right by them. I am talking about accountability to yourself and/or your company. So when it comes to design and programming what I am really talking about is time tracking because one of the most common problems in this industry is that you are always happy to make an estimate because people want estimates and if you want to get paid you want to give an estimate. And so most of you have probably at some point made an estimate and said “You know what, let’s say it’s going to take 20 hours.” I want you to tell me if this story sounds familiar because I certainly did it plenty of times. Sit down with the clients, you have a conversation and they ask you… You learn a little bit about them, you say “Yeah, let’s go with 20 hours.” Then you go ahead and start doing the work and frequently I personally would spend more time than I estimated but I was always able to rationalise it. I was always able to give it justification. Things like I really like the client, they are good people, I am really enjoying the work so I’m okay with spending more time, I really want to make sure that they are happy. All the sort of things were all good things but they are also justifications and excuses to rationalise why you spent more time than you thought. In the end, if you’re like me, a lot of the time you would still bill them for 20 hours because you felt it would be wrong or perhaps that’s the number you promised them. Or, you actually aren’t sure and you kind of feel that you were probably on time. If you still do this you are missing a critical part in understanding how you work. I did that personally all the time and now I have a business partner and even an employee we all track our time and it made me realise just how off the mark I was so often. You will realise how crucial it is to holding yourself accountable, being able to identify how long you spend on things. If you are a freelancer you certainly have that freedom and within any company there should be some level of that freedom but what we are talking about is tracking your time to make sure you can accurately assess, not just convince yourself it was good. And we are talking about tracking time for everything, accounting, invoicing, taxes, overheads. If you don’t track that stuff you don’t know how long it takes and you will be shocked by how much time it really takes up.
So those are the four main points, hopefully this has given you some perspective on how to identify where you are in that spectrum. So ask yourself and my business owner or am I a freelancer? Where do I sit? It’s okay to be both as long as you are honest with yourself. Have a great day.
Paul: Okay, so that was Paolo. Marcus, what did you think did you enjoy that one? I thought…
Marcus: I did, that was very much up my street. I couldn’t quite get over the fact that he kept talking about merging with his partner so I imagined this kind of robot made up of two people.
Paul: Oh, I was thinking more of a Siamese twin!
Marcus: Yeah, well something like that. Yes, he merged, merged together.
Paul: Yes, messy.
Marcus: So it took me a while to get past that one.
Paul: Is that your judgement? (Laughter)
Marcus: No, he said lots of really cool things like there’s more to work than just doing the work. Which I thought was very good because that is so true. What else was there… Just looking at my notes. The comment about whether you see doing the business-y stuff is a headache, that is so true. That sort of harks back to what I was saying in the last series about the value of having good sales people. All that means is that people like doing stuff like that. Because if your role is… You know, if you are a designer or developer or whatever and that is your thing you tend not to want to do sales you tend to not want to do tax returns and things like that. So wanting to do that, or finding somebody who wants to do that, is huge. I think it is possibly the difference between success and failure. Well, the difference between being a sort of on your own freelancer and running a successful business as he described it.
Paul: That whole business about being honest with yourself about what it is that you enjoy and what it is that you don’t enjoy and giving that non-chargeable work stuff just the attention it deserves. It always makes me think about, you know, how I think you are so much better off just outsourcing as much of that crap as you can and actually just getting someone to help with it rather than battling on. Even if you are a single person like I am, you know, I don’t try and do my accounts myself. Some of the admin I get help with and support with. So anything that I don’t enjoy, you know, I get someone else to do basically and then I can charge my time out at a higher rate for the stuff that I do actually enjoy. Do you see what I mean? It’s kind of a no-brainer isn’t it?
Marcus: Absolutely, yes. What I think the bottom line is is don’t ignore it.
Paul: Oh yes.!
Marcus: You’ve got to make sure you know about this stuff and if you quite like it you are probably all right doing it yourself. If you really hate it or it is getting in the way of the more important parts of your job then yes, get someone somebody else to do it.
Paul: There is one thing that you should ignore. Which is time tracking. I disagree with him over that. Well, I don’t disagree, no. I agree with him but I hate time tracking! It’s horrible.
Marcus: It’s a tricky one isn’t it. I mean, we’ve been… Obviously running an agency for 15 years now it is a constant playing in the back of your mind of should we be doing more time tracking? And we think, “Yes we must” then we can, as Paolo was saying, you can know whether you are ahead or behind and all those kinds of things. You’ve got the data and all that kind of stuff. But you can equally end up just fretting about the time tracking or it is just fantasy, time tracking isn’t accurate. So yeah, I don’t agree or disagree. We time track time and materials work obviously. But we tend not to time track fixed price work because it is just wrong. It is my view! It comes out as like…
Paul: Oh, not that it is wrong to do but as it is inaccurate you mean?
Marcus: Yes, sorry, yes. The figures are wrong and I don’t think that you learn a lot but maybe that’s just me being lazy I don’t know.
Paul: The way I work instead is that I know how much I need to earn to breakeven and so I do it on money rather than on time spent. I guess the downside of that is that you end up working weekends and evenings without realising it but I think I would realise. I don’t think that would creep up on me.
Marcus: Yeah, definitely. And we at Headscape are exactly the same have been for years. The little talk that I have given for this series is a kind of a miniature version of that. I talk about design iteration and the fact that we tend to charge more or less the same for all design work and we say the number of design iterations we do are unlimited. That basically comes down to the same thing of if we are not going under, if we are making enough money then that is a legitimate thing to do. Obviously I go into a lot more detail than that in the talk. But it is the same thing. Is the bottom line healthy? Yes, ergo our processes are therefore working. That’s maybe a bit too simplistic but yes I am the same. One thing he did say about money, sorry, that is hugely important and I thank my lucky stars for having Chris Scott to look after this aspect of our business, is having enough money to fall back on if things aren’t going well, basically having a cushion. Chris has been sort of squirreling away for years with us which when we had our blip in 2014 meant that we didn’t die. So that is, again, hugely important. I suppose if you are doing the, how did he describe it? If you are eating what you kill then you will never put anything away.
Paul: Yes. And that whole idea of that there are people out there that are, you know, basically not taking a salary but just taking out of the business as money comes in. That just blew my mind, it never even occurred to me to work in that kind of way because in my mind it’s the business that earns the money not me. So the money goes in to the business. Now, I take a salary out of that and I take occasional bonuses but there is that kind of buffer, it’s not me that is being paid for the work if that makes sense.
Marcus: Yeah, but you could argue that why not? You could get run over by a bus tomorrow Paul. If you’ve got hundreds of thousands in the bank to spend, why not?
Paul: Oh yeah, I don’t disagree with that!
Marcus: But it’s different for me, I can’t do that because I’ve got other partners, you being one of them. But you could, you are on your own you could do what you like.
Paul: Yeah, and I could, absolutely. But I like the idea… Yes, and I do dip into the bank every now and again and help myself! But I couldn’t imagine not having a regular salary if that makes sense.
Marcus: I was only playing devil’s advocate.
Paul: Yeah, yeah, yeah. And I do know where you are coming from and it is… When you’re by yourself it is somewhere in between. There is no way you could have run Headscape like that. But when you are by yourself, yes you could do that but even so I think that that security of knowing that there is money sitting in the bank that will pay my salary for six months or whatever…
Marcus: Yeah, that’s exactly the point.
Paul: It’s much more predictable.
Paul: So yeah, really good talk. I really enjoyed it.
Marcus: One more point he made that I really liked was the bus number. So how many people can get run over by a bus before… Which I quite liked. Yours is one because there is only you. And we at Headscape, we try and be two. We try and have backups in every discipline which is yes. Just an interesting… And never heard it called that before.
Paul: No, I had never heard it called the bus number but absolutely the principle I talk about all the time, especially with the internal web teams that I work with. They often have one person that is a critical person and if that person left they would be stuffed. And in fact I saw it happen once with one of our clients that their head of IT decided to walk out one day and just didn’t come back. And he was the only one that even new all the passwords. There was no documentation, no nothing. So yeah, it is something you need to be very aware of. So yeah, great talk. Really pleased with the talks so far. So that is Paolo who is the co-founder of luscious orange. Now that sounds like a proper designer development kind of web site company doesn’t it. Take a colour, take a random word, there you go.
Marcus: Rock bands are exactly the same.
Paul: Oh yeah, that’s true. Yeah, yeah. So they are a design and development company in Winnipeg in Canada, very nice. And he is the primary web developer, he is also responsible for scope and discovery for all their web projects. He’s got a background in computer engineering and spent 10 years working on his own so yes, I think he’s learned many of the lessons he shared in the talk the hard way. So you can find out more about them at lusciousorange.com or you can find out more about Paolo by going on Twitter. And again I am going to spell it p a u l o a f e r n a n d e s.
Marcus: Z. Isn’t it?
Paul: No, not his Twitter ID.
Marcus: Oh, okay.
Paul: Which is fair enough I suppose.
Marcus: I stand corrected.
Paul: He can do what he wants it’s his Twitter ID Marcus, get off his back. (Laughter). Let’s quickly talk about our first sponsor which is Resource Guru. So it’s great to have them supporting the show. It is a fast simple way to schedule your resources. I bet they do time tracking properly! I know many of you that listen to this have got first-hand experience of projects that get completely out of hand, you know, they run well over budget and time and all the rest of it. They are the kind of projects that you lose sleep over, you end up working late and weekends to overcome what seems like impossible deadlines. One of the causes of these disaster projects can be poor project scheduling. Obviously it’s not the only reason but it is one of them. If you don’t have the resources available when you need them then it is going to be impossible to complete a project on time and that is going to impact other projects and so on and so on. So to avoid that problem you can use something like Resource Guru. So it’s really just a very simple and fast way to schedule the different people that you have got in your company. It is a tool that is used by all kinds of companies, we talked last week about a few of them didn’t we and Headscape was one of them. So it enables you to start taking back control of your projects. So if you want to give it a go you can give it a go for 30 days for free by going to ResourceGuru.io/Boag, B O A G. If you use the coupon code Boag2017 when you are ready to subscribe, so at the end of that 30 days you are also going to get 20% of the lifetime of the account. So it is very much worth doing. So yes, check that out.
Okay, next talk. This is poor old Bob who got bumped from last week.
Paul: We’ve got a problem Marcus.
Marcus: Have we?
Paul: Yes, I think we might have to try and speed things up a little bit and get three in a show because the number of talks coming in now is really mounting up.
Marcus: I did notice that. (Laughter) It’s like last weeks panic, panic, panic please send us a talk, please send us a talk. And it’s like “Oh, there’s 27 in there.” Or whatever it is, that’s enough.
Paul: Yes, we do have some space for some more but we might have to move to 3 in a show and us talk less in between them but that’s fine.
Marcus: No problem at all, of course we can talk less Paul! Never in a million years but yeah, we’ll try.
Paul: We can try, we can be disciplined. But anyway, Bob was one of the first people to send in a talk but he didn’t make it onto the first show because we didn’t do three. And I think if we are honest in our heart of hearts me and Marcus may well have bumped it to the second show because some of his talk did go over our heads just slightly.
Marcus: Well, I deliberately really pulled lots of things out of Paolo’s talk because I didn’t get much out of Bob’s.
Paul: Now that’s no reflection on Bob,
Marcus: No, no not at all! It’s just beyond me. Well not beyond me but it’s just not really my thing.
Paul: So he was talking about data processing so you can see why me and Marcus felt a little out of our depth but I actually did get one or two things out of it. So this is Bob with his gentle introduction… I like that, gentle introduction to data processing.
A Gentle Introduction to Data Processing
Play talk at: 36:58 – How to do hopefully useful things with tools that either are free or you probably already have. No coding, database theory or other scary stuff like that.
Hi, I’m Bob Salmon and this is a gentle introduction to data processing. Don’t worry – I’m not expecting that you are, or even want to be, a stats guru or a Big Data expert. You might not think of yourself as a data person at all, but you still might have data-related problems that you could fix using tools that either are free or you’re likely to have already.
What do I mean by that?
You might have a large lump of JSON, HTML or XML that’s all on one incredibly long line and so it’s hard to do anything with.
Maybe there’s a log file of users doing something (like buying products or looking at articles), and each one is tagged with an id, for the supplier or the category. Your boss wants a chart showing how the different suppliers or categories are doing, but the data will be meaningless without names for the ids.
Finally, you might have a log file that you think has useful information in, but there’s so much guff in there as well that you can’t see the wood for the trees.
The first tool I’m going to talk about is Notepad++. It’s a free text editor, but it’s available only for Windows. The important thing is what it can do rather than the thing itself, and you can find alternatives for other platforms online (there will be a link in show notes). The important features are macros, and find and replace using extended or special characters, and using regular expressions.
I’m going to start with the everything-on-one-line example. The important thing to think about here is that, in some ways, the text editor sees your file as just a very long series of characters. What you want to do is insert special characters, called line breaks, at the correct places. These will act as instructions to the editor to display it as a series of shorter lines. They don’t show up on the screen, but they’re represented by the special character backslash N. which is just those 2 characters one after each other.
So, you need to think about a pair of things that surround the point where you want the line break to go, for instance the end of one tag and the start of the next. You then replace that pair with the same thing but with line break or backslash N in the middle. Test it out a few times manually, then when you’re happy do Replace All and it’s done!
Excellent! We’ve got the large lump of HTML or XML done, but what about the log file full of guff? For this you use Find and Replace as Find and Delete, so it deletes only the specific things you want to get rid of. To do this, you leave the Replace field empty, so that whatever matches the contents of the Find field is replaced with absolutely nothing, which means it will get deleted.
This is also where we’ll use find and replace using regular expressions. Regular expressions are a talk in themselves, so I won’t go into the details here and there will be links in the show notes for that. One is a tutorial, and the other is an interactive site where you can enter some text and a regular expression and test in real time whether they match. It also breaks the regular expression down into its bits and explains them for you, which can help when things aren’t doing what you expect them to do. Something to watch out for is that unfortunately regular expressions is something where there are dialects – basically different programming languages, operating systems or tools have their own small differences on top of the core ideas.
Having said all that, here’s a quick summary. A regular expression looks like you’ve just hit random keys on the keyboard because it’s a jumble of letters, numbers and punctuation. What it does is encode lots of different bits of text, that are all variations on a theme or pattern. You can say: these things or those things, but not these other things. You can vary how many times something’s repeated. You can have magic characters to anchor the match to the start of a line or the end of a line. It can get much more complicated than that, so the trick is usually to start small, test it and then add to it gradually.
The way I find myself using regular expressions is to delete the bits of a file that I don’t want, because they can adapt to cope with many variations in the stuff I want to get rid of. So you put the regular expression that matches the guff in the Find field of Find and Replace, and then leave the Replace field empty, as I mentioned earlier. Often it’s simpler to do it in several goes, each with its own regular expression, rather than trying to craft one regular expression that does absolutely everything.
The final feature in Notepad++ is macros. These let you press Record, do stuff like type, delete, find something, move around and so on, then press Stop. Everything you did between Record and Stop can then be replayed as a single thing. Notepad++ lets you say do that macro 17 times, or even as many times as you can until you get to the end of the file. I won’t go into it now, but it’s worth looking into.
The next tool is Excel. You might think of Excel as a thing for doing maths or drawing charts, and it is. However, you can also use it to treat your data as a series of columns or a series of rows. So, if you have last name followed by first name for some people, it’s trivial to switch the columns around in Excel. And much easier than it would be in Notepad++. You can hide columns too. You can also filter rows, so you see only the rows that have certain values in particular columns. You can sort rows to change their order. If you do change the order of the rows, there’s a small trick you might want to use to put the spread sheet back to how it was at the beginning. Before you change the order, add in an extra first column. Fill this column with 1, 2, 3 and so on. Then do whatever you need to do to change the order. When you want to put things back you sort everything by your new column and that will put it back into its original order then you can delete that column and it was exactly how it was when you started.
You can combine sorting and filtering with formulae to make them even more flexible. So you could create a formula that has the value 1 if either an order’s date is in a particular date range or the order value is greater than a particular amount. If you then filter rows on the column with the formula in, you will see only orders in that date range or greater than the amount.
Regardless of how you get there when the screen shows exactly what you want and nothing else, you can use Goto Special to select only visible cells then yo can copy them and then paste them into a new spreadsheet.
The ids to names problem I mentioned earlier could be tricky if you’ve got lots of different suppliers or categories – find and replace for each one would take too long and you might make mistakes. So instead you could use the Excel function VLOOKUP. Which is letter V and lookup. In one workbook you want one sheet with the log file, and one sheet with the list of id to name pairs for all your suppliers or categories. You add VLOOKUP to each row in the log file sheet. What it does is take a value, in this case the id from the log file, look for it in a block of cells somewhere else – the list of suppliers or categories – and if it finds a match it returns a value from another column on the same line, in this case the supplier or category name.
The downside of using Excel is that your data needs to be in a fairly rigid structure before you can use it. Notepad++ doesn’t need this structure, so you could use it to get the data ready for Excel. The combination of Notepad++ and Excel can be very powerful – your data can move to whichever tool is better at doing the next job.
After a point, text editors and Excel start to be too small for the amount of data you’ve got. They just can’t cope. At this stage it’s worth looking at something like Talend. Talend is an ETL tool – ETL stands for Extract, Transform and Load. There’s a free version and a paid-for version, and there are many alternatives from other suppliers.
An ETL tool gives you a visual editor where you can arrange boxes with lines to join them up, and the different kinds of boxes tell the tool how to Extract (or read) data from an input like a spreadsheet or a database table, Transform it in some way, and then Load (or write) the outputs somewhere like another spreadsheet or a table.
The transformations could be all kinds of things – filtering, grouping, joining, sorting, calculating things and so on. At BestCourse4Me we use Talend to read census data in flat files, clean it, collate it and then summarise it into a database so that we can produce charts on our website and app.
Behind the scenes, Talend is actually generating Java that corresponds to the boxes and lines, and you can add special boxes that are simply containers for your own Java if you want to do something beyond its standard set of operations. However, you can get quite a way without coding, and as usual there’s a lot of stuff you can read online about it.
So that’s my gentle introduction to data processing, using text editors, Excel and ETL tools. I hope you found it useful. If you want to contact me, please use the Boagworld Slack channels. Bye, and thanks for listening.
Paul: So yeah,
Marcus: I have one note!
Paul: Oh, go on then, I want to hear this.
Marcus: He’s got a lovely cosy voice.
Paul: Lovely cosy voice! Right. That’s it?
Marcus: Wouldn’t it be nice to listen to Bob all day.
Paul: Yeah, he’s all also a big user of the Boagworld slack channel so yes, I like Bob. We had a really good conversation in there today didn’t we, about agencies and the future of agencies and the type of work you win. It’s a good place.
Marcus: It is, yes. Let’s not go there. Let’s talk about… Well maybe we should go there because I haven’t got anything more to add…
Paul: I have got a couple of things.
Marcus: You do that.
Paul: But I will say, if you want to join the slack channel you can do so by going to Boagworld.com/slacking. And you can sign up there but anyway, back to Bob’s talk. Yeah, for me actually this was relatively helpful because he talked about something called regular expressions. Do you know about regular expressions markers?
Marcus: Yes I do, yes.
Paul: Yes. So they are this kind of arcane witchcraft language that allows you to do very complicated find and replace functions, amongst other things. I have to occasionally use them and I cry every time I do because I never manage to get it right and actually, ironically as we were just talking about the slack channel, the last time that I had to write one of these things I ended up going to the slack channel and getting grown-ups to write it for me. So it’s a… It’s just evil there’s got to be a better way than these things. But one part of it that I wasn’t aware of that Bob’s talk helped me with it with is that there are various variations of regular expressions which makes sense because there have been occasions where I could have sworn blind I was doing it right but was probably was just using the wrong form of regular expression. So… And best of all, and this made his talk you know, worth including, was that he mentioned that really good tool Regex101.com which helps you build regular expressions and check them. And it was like “Yes, I love this. This makes my life so much easier!” Although admittedly it did confuse me when I actually went to it because regular expressions are so annoyingly complicated that even the tools to make them simpler are still complicated. Stupid bloody things. Anyway, thank you Bob for that tool.
Marcus: Yes. Thanks Bob! Even though I didn’t really get a lot from it. I’m sure other people will. More so than the rubbish I normally shout about.
Paul: Yes, I’m actually really glad that we are getting a few more develop-y ones because let’s be honest, we are a bit rubbish on the development side. We do try and keep this show as rounded as possible but yes, we are a bit weak on that. So if you want to talk about something develop-y then by all means send in a talk. The only thing I would say is that you’ve got to remember that people can’t see it. Because we had this conversation with Bob and it was actually quite hard for him to try and come up with a talk when he can’t show any code. Which is a fair point I guess.
Marcus: Just, that’s the thing I found the hardest about doing the little one that I’ve done is that there is just nothing to fall back on. It’s weird, no bullet points or anything.
Paul: No, no.
Marcus: In the end you’ve got to view it just like a little chat.
Paul: No, you haven’t got to view it as a little chat. You’ve got to view it as a serious and professional.
Marcus: Yes Paul.
Paul: I need to check yours I haven’t listened to it yet. Is it up to standard?
Marcus: It’s probably the best of all of them. I will get Paul’s Gold star.
Paul: Will I? Will you? Right. You sound like Trump. It’s the best talk ever.
Marcus: Really! You could not throw a bigger insult at me.
Paul: I know, that’s why I said it. So, Bob works at bestcourse4me where he tries to help young people make well-informed decisions about their future. What a cool job. Before he worked on systems for telecom and utility companies. No, you’ve definitely got a better job now. And during the last millennium, ahh, that’s a cool line, he did speech recognition for banks and small businesses.
Paul: What an interesting career. I like it, that’s a real mixture of stuff. Stop typing Marcus.
Paul: If you want to find out more about Bob you can go to bestcourse4me.com. You can find out about being well informed about your decisions for the future. But only if you are a young person! I wonder what the line is. I still feel young does that count?
Marcus: Well, so do I. In my head I am 18. I look in the mirror and it’s like huh…
Paul: Who’s that? Old grandad? Literally in your case.
Marcus: Exactly. Literally, I am literally a grandad.
Marcus: There you go.
Paul: Ooo, I’ve just been to his website. “Is Uni worth it?” That’s really good question these days.
Marcus: Depends. Like the answer to everything else.
Paul: Oh, it takes you through your options. This is a really good site I like it.
Paul: So if you don’t want to go to uni what the options available to you, you can look by subject… Oh! That’s a very useful site. Bob’s gone up in my estimations. Right, let’s do our second sponsor. The second sponsor is Fullstory the next generation analytics tool. And no a marketer didn’t write that line, I did. See, I can write marketing bullshit too. So, I really like Fullstory. It’s a great product it is used by companies like dropbox, shopify, me, and many more. So if you want to know what’s happening on your site you could just use Google analytics but that’s a bit rubbish right? Because you have to pick through it all. What if you could Google what was happening on your site. So you could literally just type in what you want to know. “How is the newsletter button performing” or whatever. Well Fullstory has something called omni-search so all you need to do is type in an element on the page that you want to know about. Or you can type in whether you care about clicks or what ever. Whatever you want to know about. As you start typing the name of an element or the information that you want Fullstory auto completes and finds it for you. So you could type in click or visit or user or any element on the page or any other kind of phrase that you want to find out about. So there’s no kind of complicated filtering. Do you know what I mean? Where you have to kind of go into menus and sub menus and create segments and all this kind of stuff that you end up doing in things like Google analytics, it’s just a pain in the arse. You just say what you want and it builds your search for you which I think is brilliant. It does loads of other things as well which we will talk about in the next coming weeks but that is a feature that I really, really like about it. You can sign up today and get a free month of the pro account for free and you don’t need to enter a credit card which is wonderful. So if at the end of that free month for the pro account for free you can still continue using it. You can use their free account which gives you a thousand sessions per month but if you need more than that you can enter your credit card and sign up. So you can find out more about that at fullstory.com/boag.
So, that is about it for this week. Marcus do you have a joke for us?
Marcus: I have a joke from our very own Dan Edwards. There you go. I think my local dry cleaners can repair my trousers or so it seems.
Paul: Ahh. (Laughter) People are really… I feel like listeners have now finally after however many hundreds of episodes really got your sense of humour now. Do you know what I mean? They seem to hit the perfect kind of type of joke every time, it’s very good. Wasn’t it someone in slack that says there is now a website dedicated to your type of jokes?
Marcus: Hmm, yeah. I’ve seen it before. It’s been around, but it’s not the right type of jokes. It’s more sort of dad jokes but mine are more sort of wordplay jokes I guess.
Paul: Yes, yes. Well there you go. So just to remind you that you can submit talks for this season at Boag.world/season18 and also remind you about that workshop that I mentioned at the beginning which is boag.world/convince. Do keep sending them in, don’t be put off by the fact that we are filling up because we will cram in as many as we possibly can. And if we can’t fit them all in to this season itself either we will do a second season on it or I’m going to publish all of the talks on the website. So you can go in and listen to them there as well. So they will get aired in some regards I promise you. But until next week thanks for listening and goodbye.
Links mentioned in the show
- Alternatives to Notepad++ (including for non-Windows platforms)
- Regular expressions tutorial
- Regex101.com – Regular expressions interactive tool
- ETL tools