Which one are you um you know i'll go first i i would put myself in the amateur uh boat sometimes here in canada we're drinking tim hortons mostly um but lately i've been drinking tea in the morning i'd love to hear from from you in the chat and uh how about you avi and uh josh yeah the best i can manage in the chocolate smoothie josh i i'm expecting like a good answer from you this is it's put me right on the spot.
Here the most embarrassing thing for me is that i drink tea in the morning i don't even drink coffee till i get to the office so i usually have about 500 ml of tea in the morning it gets me gets me out of bed and then yeah coffee when i get to work hey would you you are british yeah cool okay um just wanted to jump into the talk so um we're gonna be talking about specialty coffee and uh big wave bi and the work we've done together josh.
And i um we'll introduce ourselves in a minute uh we called this webinar riding the fifth wave and so um for those of you with a coffee background there's um there was the third wave of coffee it was it was a way that focused on quality and flavor and and quality of roasting and then in the last five to ten years there was a fourth wave and so there was a focus there on processing at the farm level and just improving quality even more and then now we're finding ourselves.
You know in the fifth wave we're calling it um just with a big wave of uh data analytics and and iot we're seeing it in some of the machines that are coming out and and even just companies becoming more aware of their data just regardless of the size of the business so we're an exciting time in the industry i wanted to ask another question here in the chat um i see there's a lot of responses here just wanted to ask how.
So i'd love to hear from you um just what your mornings are like and how much you know how many problems you're dealing with every day so again a bit more about me here uh so as a project manager for 20 years in aerospace industry i live in chilliwack bc it's on the map there the blue dot um up in the west coast of canada beautiful area to live lakes and mountains and lots of camping nearby and just a beautiful spot i'm married with five kids and yeah i love working with power bi um.
It was in that role as a project manager that uh that i discovered power bi um and yeah really automation was the key to my growth and success there i when i discovered power bi kind of became the go-to guy in the office for that and and it was it was you know with all the deadlines and pressures it became the tool that really freed me and released me to do more and and work proactively there so um yeah and then started my own business.
About two years ago now and i've worked with various industries uh retail food and beverage manufacturing construction healthcare and um yeah and and work with a network of um consulting power bi specialists and and whether it be finance project management um e-commerce or or making custom connectors so um a little bit i'll turn it over to josh josh josh and i worked over work together over a year ago and or we first met over a year ago he's the director of coffee over at clifton.
Coffee roasters in bristol uk and i'll let you take it over there from there yes so um my title uh is director of coffee i'm still trying to work out what it what it entails because it seems to cover all aspects of a coffee business but we're a specialty coffee roastery that have been going almost 20 years um i had a slight experience in in manufacturing my dad's a manufacturing engineer so i sort of brought that that vis i guess vision and maybe a little.
Bit into specialty coffee but really fell in love with coffee when i was at university and um and just i've been in the industry now for for 11 years um i specialize in in sensory um when it comes to coffee so i love tasting coffee um i travel the world to source coffee um and yeah really seen or been able to just ride this wave that the industry's been growing coffee's becoming more and more popular people wanting more coffee they want to.
Posts Related:
Know more about their coffee and as a business we're just trying to give people the best possible experience whether that be in products customer
Service um and and experience really [Music] that's awesome thanks josh i was just catching up in the chat here i love it i've got tim or ian with the tim hortons i'm going back to the previous polls sorry and there's uh eric was wanting you to chat with james hoffman about your tea drink in the morning.So just moving through it um so there's a little uh snippet of some of the work we did together so just wanted to show a preview of that you know we built quite a few dashboards more than this even uh together but there was one that was particularly impactful and that that was the dashboard for the rose street so um you know again i've been around manufacturing and coffee now for a while and i've worked with quite a few business leaders financial.
Leaders whether it be ceos directors um you know business leaders in these industries and i'm learning what they get passionate about a little bit of you know as josh talked there you could hear um just the excitement about what he does and um yeah so some of the things um you know unlocking new markets um new rows um you know optimizing the batch sizes and just the whole story from crop to cup i i hear that quite a bit and um just doing that as smartly and.
Efficiently as you can and um you know we were chatting the other day josh and you know just the desire to take something really complex and take it to the everyday coffee drinker so just hearing that um those are some of the things that get them out of bed and then there are challenges for sure so what keeps them up at night so um you know i'll be mentioned supply chain so supply chain disruption disruptions has a bit has been a big thing this year um losing customers or contracts.
Coveted issues so furlough whether it be staffing turnover or recruitment and then on time delivery just making sure you're you're delivering on time so um it's it's difficult to sit down and make strategic decisions i think what's really driving a lot of this is you know it's difficult to access our data in one place you know there's different versions of the reports floating around which creates confusion and then you know part of the covet.
Effect you know we saw a strong shift to online sales subscription sales so just that lack of a robust um reporting structure there um you know was a challenge initially so and again like can can you trust the numbers or um you know not not able to make data just data-driven decisions quickly so yeah i'd love to hear from those of you in the chats more like if you're facing some of these challenges in your role.
And and i'll turn it over to josh today it covered that well or is there anything else you'd add yeah i think you summarized it for us really well i think uh the coffee industry you know i said i've been in it 11 years now and i've gone from you know pot washing in a coffee shop through to now heading up a global sourcing program and i'm really still just scratching the surface when it comes to understanding coffee the complexity around supply chains the different intricacies around different countries how we then bring that coffee into the uk and then every borough in.
The uk seemed to have their own different worlds last year when we hit the pandemic as well so where you could travel where you couldn't which coffee shops were open and i think the challenges for us is you know we're a small team when i joined i joined clifton coffee seven years ago and there were 12 employees we'd meet in the morning for a coffee we'd come up with a plan for the day and we'd go off and work uh we now have 35 employees we supply a thousand coffee shops so when the pandemic hits we got to a stage where it's like cool what what.
Do we do now how do we understand what's going on in our business quickly um how do we how can we make strategic decisions using our data how much commodity have we got moving across the uk how do we understand where that is who's got the best reports everybody's sifting through csv files trying to find the most uh or the most recent download um and it like i think that first question you asked you know how many fires do you put out i don't know what everybody else's commute is like but i have a 40 45 minutes commute.
To work and usually that's where my working day starts i get on the phone i speak to different people in different departments and find out what's going on
Um and they are the challenges really that's what i found myself reflecting over the last year of what do i spend my time doing what absorbs most of my time and more if not these are the biggest challenges uh that i have um and then yeah that that sort of age-old question right greg of can i can i trust the numbers absolutely yeah anthony's saying my commute is from the bedroom to the study that's a lot of us these days and i love.It yeah okay cool so yeah um you know regardless of the challenges and and um you know i see two types of business leaders um and um there's there's those that are trading water so um they're either making decisions using their gut and they'll either be lucky or they'll be wrong um you know these people are spending a lot of time manually prepping the data and um you know they you know.
They're the ones spending these last panicky moments before a supplier meeting prepping the data you know or they were spending a lot of time late into the evening the night before not sure if they missed anything and yeah stuck doing repetitive tasks producing same reports every week every month every quarter you know oftentimes they're they're spending hours in microsoft excel and feel that there has to be a more efficient way.
To eliminate these tasks and um i would say some of them suspect there's a faster way to do things but they just don't know any better they've never had time to fully dig in and then um the other type those that are dialed in and these these are a lot of the people that are on the journey sorry there's a lot of people that i work with that are on this journey i should say uh they've got the same big mission same challenges but they're invested uh in putting systems in place and you know now.
They're processing power their time can be invested into you know solving challenges um strategic problems so they're proactive rather than reactive they're in tune with what's happening with the business and now they're in a position to stop fires before they even start so you know with power bi you get strategic reporting and it's possible and it lets lets them do everything else better so again love to hear from people where.
Where do you think you are at in this journey i'd love to hear from everyone so um supply from benjamin here this is amazing supply and efficiency would be our big ones in the coffee company i'm involved with that's awesome um what about you josh anything you would add there yeah i think um just as i mentioned when we first got introduced to each other through avi i i think yeah straddling both at some times i see someone just written that there's is you know like there's times.
Where i feel in control um but the majority of the time it sort of feels like i'm trading water i'm constantly juggling multiple departments with different decisions and and very much reacting to problems as they arrive um and we've always had this mantra that you know we want to be proactive as a business rather than reactive we want to be set in these trends going to make that attractive for our customers um but we were looking internally and things were just becoming harder as we were growing as a business um it was.
Becoming more difficult to maintain that sort of lean um structure to be dialed in so i think when we started this process i would say i was i was treading water i didn't have much time for processing power my mind was caught up with the doing rather than the thinking and it's the thinking that gets me really excited about my job having the time to look up look around see what's going on and then perform proactive decisions or even just trying something new as well and innovative is really what our.
Industry is about so i'd like to say that as a business it's a coffee tim so i love it we're dialed in that's awesome okay um i'm gonna jump into the a little bit about the story so it was again it was early 2020 that we started working together got connected through avi um so clifton was just coming off the back of integrating teams into their business they were early adopter of teams there's a there's a case study on.
Microsoft's website about some of their work there uh with another microsoft partner uh changing social in the uk there and um so we worked together we started together we worked about three weeks together building out a small proof of concept and we built a couple simple reports uh they were connected to some csv exports from their system and even through that those three weeks it was really exciting just to start getting an overall picture of the business and seeing really seeing the possibilities i think.
That was that was the big thing um and one of the reports we initially looked at was a simple roastery report so um there's a picture of the roastery there's their head roaster tim in the back and on the far right um yeah and i've just been excited even to hear of the ways they've been improving processes um over the past year um not just through the dashboards but they've you know you guys have been doing a lot of lean implementation there and just continuous improvement initiatives in the groceries so it's been.
Just really exciting to hear this when we first started working together you know from what i was hearing from you it was a very manual process um to build the rose schedule for the week or even for the day um you know in the morning you guys would get together and have a stack of orders it sounded like and and just have to make sense of it and you know printing off multiple copies for the upstairs and the downstairs um meeting meeting downstairs every morning at 10.
And and just doing the calculations on the spot to see what you need to produce for the day um and um you know i would i would say it sounded like it was really difficult to see the big picture and plan proactively there um this year one of the initial things we looked at during that time was um where their data was coming from and and you know and then just think about the questions that josh was asking um on a regular basis so and seeking from.
These various systems um in clifton's case um you know i could see i could see your frustration josh just having to extract from multiple systems and csv files um you know just to to refresh a monthly report like this one we're looking at here um you know copy pasting consolidating in excel um creating pivot tables and things like that so um you know another thing was and it's pretty common actually is that the actuals and the budgets were coming from totally different places so budgets.
Were excel files and then the actuals were coming from sage 50 in their case um you know that's a pretty pretty common scenario combining actuals from multiple different systems and yeah again i'd love to hear from anybody in the chat like uh have you experienced any of that um love to hear from you and maybe josh what anything to add to that yeah i think one of the things that we've spoken a bit about on and looking back now i've been able to laugh at is that one of the reasons that we had two desktop monitors in our office was.
Because we were looking at multiple data sets different sources so i could have one spreadsheet up on one side trying to export csvs and then something else elsewhere and i was spending the majority of the time just consolidating these reports i think you know this table this spreadsheet that you can see here was sort of my greatest work at the time i was so proud of it but it would take hours and it was just it was really laborsome and it was becoming more and more problematic i guess because the more data you're presented with the more questions you tend to ask.
Um and that was just becoming more of a burden than it was a success for us um so i think there's a slide later on where you've you've got that sort of wheel of the doom that i used to call it just i was just wait waiting for reports to download so i could try and make sense of them to to get you know we think in our industry that because we use weigh-in scales to make coffee that we're scientists it's so far from the truth so it's it's nice to to keep this element of of craft artisan that sticks with coffee but but.
Use all this information now to help us understand it more that's awesome yeah um eric there in the chat he's saying they use forecasts in excel actually from sage 500 yeah yeah and benjamin yeah can resonate with that that's awesome we'll do mo we'll we'll come to that slide so um the first thing we did was josh sent me just a handful of exports from their system and so these were just excel exports from sage and then a couple of static files.
So in power bi using power query we built a simple data model so we added a calendar table and created some measures um one thing through this process that we discovered was there's a lot of back and forth in data cleanup you know we discovered a particular field and sage wasn't being used but we really needed that to be you know just just being used consistently so that that it would yield a good result in in the dashboard.
So there's a lot of back and forth and cleanup activity out of um this process which was really good um and then we we created um laying out some visuals and created a simple report and this was i think it had the makings of a roastery report and and it was i think honestly that it was just really rewarding to start seeing the data in one place and just and um just what the possibilities would would be but um at this point it was still static just um.
Static files that were exported of the system and so um yeah we took a break for about six months and uh when things picked up again then we started working together again so the goal when we met then was to further develop their dashboards and and the big thing was establishing connections to all you guys's um main data sources whether it be sage uh woocommerce cropster those were probably the big ones for you and our goal was to publish these reports and.
Have them available in teams particularly and and mobile reports um on this mobile and and then have it all automatically refreshed so that was the goal um and so we started looking at it so i think you know again we started looking at where their data was coming from these were the three big ones there's the the wheel of doom you call it so sometimes you were waiting there in the top left corner i don't know five minutes handsome depends how many people were logged in at the same time trying to do.
Things yeah totally rick you're saying you've definitely been at the wheel of doom waiting for vlookups to complete on excel yeah there you go um so yeah we had sales and inventory coming from three different systems so sales were coming in from sage and woocommerce so woocommerce being the the back end of their online subscription platform um and then the inventory and stock on hand and cropster so in and of itself these these tools all had their own.
Reporting um i guess modules and some better than others um but they were all in three different places and just a quick analogy you know um whenever i drive by a construction site i think i'm always amazed how much time they spend you know under the ground we have a four-story apartment going up down the road and i think they spent over a year just doing the site prep excavating uh laying out the forms and and pouring.
The concrete and and um for this project in a similar way you know we had to do a fair share of that type of prep work before we could start building our reports um so we mapped it out one one of the first things we had to do is map it out we looked at all of our options to connect to sage and woocommerce and cropster and we planned for an automatic refresh to powerbi.com you can see it on the the map there.
Using a data gateway up at the top left and again the goal was to consume the reports and teams and and uh the mobile reports and by the way uh special thanks to frank van delden who helped me create this in powerpoint workflow diagram so i thought this showed a pretty good view here um you know the kind of the process all the all the groundwork is the first the connections the data flows the central data model really at the end at the far right is.
The reports which is the what the user consumes but we had to do all that prep work so starting with the connections um this is the lineage view in power bi.com and just shows what we ended up creating um shows all that prep work um so yeah the first thing we have to do is create our connections to these data sources um for sage what we ended up doing we explored a few different methods uh we ended up using an odbc connector um and just creating a.
You know sql statement that pulled the relevant fields then we saved these um queries in a in a data flow entity i think that's called something else now data table data flow tables or queries i forget but they were called entities and then we set up an auto automatic refresh on the data flows so that was for sage and then for woocommerce again we explored a lot here um.
So we ended up creating a python script that would make an api call to the woocommerce api with pagination and then we set it up so that it would dump uh the data out as a json file and save it automatically to sharepoint so that was all automated running on a on a daily schedule um and then from there we would again pull it into a data flow entity and so then so we had sage data we had woocommerce data stage and um.