Like I said, I joined as an attendee my first time last year and I really hope that you guys all are all finding the same. I know we have a ton of incredible presenters, so I'll do my best to to maintain pace with all of them that have preceded me and those were after me. But I'm really excited to be talking about my my ongoing journey here to in helping emerging alcohol brands better understand their businesses, make better strategic decisions. Allowing them to spend time working on their business and not just caught up in the day-to-day really by driving.
Them towards more accurate and efficient access to their data. So I'd like to kind of start everything off by describing some of the early early challenges that we that we that we met as we were going. So I'm going to describe this as the data hangover self-proclaimed. Just something I threw in here because I thought it'd be fun and tie and tie into our our overall topic. Lots of data accessible to them. In fact in some cases too much data, no easy way to put it all.
Together like alcohol. You know, too much consumed without guide rails or responsibility can lead to headaches or in our case of hangover. So we have multiple different systems that we're using here. They had Nielsen for retail sales information, VIP for distribution data and then as well as a bunch of internal forecasting like mostly based out of Excel. The, you know, the reporting that is available to them is really restrictive. They're charged by the number of reports that they run..
The reports have limits on the columns and the rows. I think it should be very prohibitive. Outside of that, once you did actually pull the information down, there's a single person that's responsible for putting this all together. So they're pulling it, they're curating it. I mean, the guy was really, really good at doing Excel, but he's still doing this every time, you know, which led to inconsistencies with reports. So you know you might see certain things come up one and.
So you may ask yourself, OK, there's problems. We get it. We all had it. Was it really that bad? So this is just, this is a real example. I've I've modified all the data so it's not there. But this is a single report that was run. I've got a couple different spreadsheets, but this is something that their Excel guru was responsible for putting together on a daily, weekly, monthly basis. And you know, he'd just be losing hours and hours just putting the reports together. And then let's not even think about the change request that.
Comes in or the common, hey, this is really good. But I I'd like to include this other factor. You know, you you have static data like this, you got to go back to the drawing boards and do it again. So where were we able to take it? We started off with these Excel spreadsheets, one guy curating everything. No model could be great. Excel is a wonderful tool, but in this case, it wasn't the best application of the product. So we had some conversations to move to Power BI..
So this is kind of an example. I just want to give a a quick snapshot at a couple of the reports that they're actively using today. We'll get more into them a little bit later and talk about how they're transforming the way that they're using data now purposely as a tool to drive their business. So here's just a quick sample of a handful of the reports that we transitioned from Excel over to Power BI to give the put the control in the user's hands. So I'll take a quick second and just introduce myself. So my name is Mike Casey, happily married 13 years..
Somehow I got her to she's she's stuck. Stuck with me so far, my biggest champion and and and partner father of two amazing little girls, 10 and seven. It's a journey. I live in Michigan now by way of Florida for about four years and Ohio born and raised in Cincinnati. I've spent a little over 18 years in healthcare IT working multiple different roles from, you know, the help desk guy, the field tech doing engineer, lead engineer all the way up to my most recent position which was the Chief Technology Officer at.
A healthcare practice. You know, deeply experienced in, you know, technical and business focused roles. So I kind of always sat somewhere in between the two worlds. I'm not full techie. I'm not full business, just kind of that in between. Guy, another big piece about me is that I love a lot of things. I can you know love technology, outdoors, sports, camping, fishing, hiking. You know I I mentioned it here specifically you know I find my.
- New Logo Mockup PSD Files Free Download For Photoshop Sheri Sk Photoshop Tutorial
- VBA to import data from SharePoint to Excel. SharePoint Automation using VBA - 4
- SQL Server Queries Part 2 - ORDER BY (Sorting in Queries)
Center outdoors and and you know just sitting around a campfire looking at the looking at the the light and it's just there's - ID Card Make
Something different about it really helps you kind of connect with yourself connect with nature and really find who you are. So this year as the next one kind of says there I stepped out and started my own my own consultant company Purpose guided Solutions. It's been an amazing kind of jump Donald that's that's a good eye we're we're close to Frankenbuth there..So you know I'm very passionate about what drives me and what really drove me to to starting purpose guided solutions I'll get into a little bit more but it it's certainly surrounded by my family and you know getting to you know start taking practical steps to that life of freedom just passionate about helping people and organizations maximizing their potential. So taking something super complicated making it something accessible so that people have the opportunity to succeed. So I have all this experience in healthcare built my entire career around it and I'm here talking to you about alcohol.
Beverage that makes sense right. I want to give you just a quick a quick heading here. So I hope I host a bourbon night. We started it here when we're when we moved out here to Michigan, got a very small neighbourhood of consists of about 11:50 houses, you know, so I started just inviting guys over and once a month during the fall and spring, we kind of get together, hang out just cigars, bourbons, we get to taste different bourbon, You know the good ones, the bad ones, the expensive ones..
And I would say just like business intelligence solutions and consultants for that matter. Most of the expensive ones aren't always the best. They're just, you know, sometimes the $12.00 bottle is much better than the the $200.00 bottle. And you know, so as we're doing this, I want to just kind of tee up a story, and this is a true story, which is how everything kind of started falling into place. So I moved up to Michigan, took this new position and started seeing kind of some of the things that were going on just.
Wasn't aligned with it. So I kind of want to set the tone by describing bourbonite. And you know, we're in that setting, you know, and it's, you know, cool fall night campfires roaring, everyone's having cigars and conversations. And then all of a sudden something completely unexpected happened. A data analytics conversation broke out because you know, that's normal, right? Like bourbon cigars, guys sitting on a fire, let's talk.
Data. So it was, you know, generally just engaging with a friend, having a conversation with him. He's the Excel guru now that I was referencing earlier. And he started talking and I was like, hey, man, there's this really cool tool that I've been using in healthcare practices, helping to, you know, shape some of the revenue cycle management and things like that. So it's just, it's going to change. It's going to make things so much easier for you. Why don't you give it a try?.
So that continued. I told him that I mentioned it to him every time I saw him for about five or six weeks, until finally I kind of saw the things where I knew I needed a change and I had no idea how how much this night would would really change my entire trajectory. You know, it started with just a general natural conversation. Trying to offer some help to somebody turned into me asking, hey, why don't you let me try do a proof of concept for your company. So let's just let me show you how much this can help you. And then that eventually is turned into project and.
Contract, contract work. It's just it's been really a a cool ride to be on. You know, I thought I moved here to Michigan for my dream job, you know, using all my experience in the field I've been focused on. I felt, you know, I finally made it. You know, I was, I was ACTO at 35. It was like what else do you want? And you know it turns out that that God had different plans for me. So on this next one, this is, this is the proof of concept that we built just at at the beginning. I knew nothing about the industry, nothing about the.
Data. We were just trying to pull it together and say hey what what's possible. And I'll be honest, I spent way too many hours on this proof of concept. I know Avi's mentioned a couple times and even the the bonus project after is choose the right project to start. I I did not. I did not. This is a very complex project. There were a lot of different things. High focus on Excel, like basically all the things on the not list they were in this you know, it was all free doing proof of concepts. I was just trying to pave a new path for me and my family..
You know, I was doing all these work after hours in an already extremely demanding job.
For those of you who have worked in healthcare at all, you know that doctors don't do their business during the day. They do their business on evenings and weekends and all the all the places that it's not super convenient for pretty much anybody else. You know, my family was incredibly supportive of me during this, understood kind of the the why I was doing it. They didn't really understand that they were the reason why, you know, working towards that life of freedom and the change..You know, at this point I had just also started Avi's Learn Power BI program. So I was, you know, I had been using Power BI for a couple years, for about four or five years. But it was just more one off, hey, I ran into this problem. Let me look up this video and figure out how to do this one thing. And, you know, I ran into an issue on the proof of concept and I threw something out on the Facebook group. And Raul Jimenez, he's going to be talking, I believe, tomorrow. He jumped in..
And I I've got to say, I was just absolutely floored by his willingness to just jump in and help. Absolutely no strings attached. It was funny. I was, I hopped on a video call with him and we were actively working on something for about two hours. I came out and told my wife what just happened to like, man, this community is really great and she's like, Are you sure this isn't a scam? Like you just shared this information with this guy? I said yeah, he genuinely just wanted to help. So, you know, later I've continued on and joined the Pro.
Plus community and had a ton of support and help from a lot of members in the community and you know Avi and the team, just a really fantastic group. But I just want to jump back to the, I want to jump back to the the the story here at hand. So we did the proof of concept. It was well received. So what do I do next, you know with the, the proof of concept was kind of my sales process if you will in this case..
So I had to go back and kind of look at things in a little more detail. So we're stepping all the way back before any technical work is even a thought, you know we had to 1st understand more specifically what challenges are we having. In other words, what am I going to do to help the company? You know, we found some of the main focuses that they really needed some help with were you know, eliminating inefficiencies, you know, the ability to essentially access certain reporting, giving users and the executive team access to.
Be able to manipulate the ports themselves. You know they they need things to be more dynamic and respond to the business questions that they have right now and they just didn't have that access with with the the current, the former version of the Excel reports. So really putting the control in their hands, you know after that we needed to actually review the current systems and reports. I had been given just a handful of of of the Excel files that he gave me as a sample for the proof of concept and kind of figured things out mostly on my own..
And then we really jumped in once we understood that we kind of had to understand what's our plan of attack. You know, how are we going to be going about this, what systems we're going to focus on. You know, this is, you know, we need to know what and how. But this is also not mentioned, not to mention going to give me some idea of a framework that I can scope my project around so that I could actually provide them a proposal with, hey, this is what I'm going to do. So we agreed that you know we're going to target their, their.
Three biggest things right out of the gate, VIP Nielsen data and they're forecasting from spreadsheets. So you know we built the the scope of work the the big push there was speed to market. We wanted to like we wanted to transition from the proof of concept to getting something good in their hands quickly. And so that was the the really the the driving focus. Now we have the scope and we have the project kicked off and there had to be a lot of work to understand, you know, where does the data exist?.
How are we going to connect to it? You know, how are we going to take all these flat files and turn them into a data model? And you know, we're going through. And as we're doing that, we've found some pretty glaring holes in the data because I have the only client in in the world that had messy data. I say jokingly because there's every, every data is messy. You're never going to find the perfect one. So if you think you're alone, you're not, you know, we jumped in..
VIP had had columns where they were supposed to be unique columns and they weren't. We found after we're building forecasting wasn't the same level. They were looking at state information, but they couldn't go any further than that. And I had channel information which is another step down. So it was just kind of tricky trying to put all those pieces together and then Nielsen as well, hey you know they're they they report things out on a weekly basis for the entire week. So every Saturday we just get an end of week date..
We don't have the the daily date and the company is using a standard calendar. So fast forwarding a little bit as we're cruising through here now, it was time to make it real. We spent a little bit that you know of that data model, data model, data model, which is, I can't emphasize enough, if you've ever seen the large building being built, they spend 3/4 of the time on the foundation and there's there's intentionality behind that. I suggest you do the same as your approach, Power BI..
But we started presenting our first reports. So we, we did it iteratively. We started off with our, you know, our executive team. Then we had those conversations, took their feedback and then the directors and then we moved on to the team members once everything was kind of solidified. So you know, we really spent a lot of time working through those and trying to do it quickly but in in, you know, in a way that was accessible. So I'm going to really oversimplify some of those challenges here. You know, these were some of the things that we found as we were.
Going. You know, we had inconsistencies with the columns like I was stating it should have been key columns that we could use. If you're not familiar with that you just that's our our one, our one side of the one to many and there was just there was nothing there that was unique. We kept getting a bunch of errors because there were duplicates and it just it it was causing a lot of chaos. You know we're trying to build our dimension tables like the product table and account table and so on..
This presented a huge, a huge obstacle and then again the calendar I come from the healthcare world who typically doesn't operate off of that same fiscal calendar unless you're in the larger health systems. So they currently, the current client was using a standard calendar for all of their operations. However, like I was stating, the Nielsen data is coming in once a week. So we had to really kind of focus and stretch and think about right how are we going to address this the right way to make it effectively used..
And then much later in the process this, this may not be you know necessarily overly technical and and it's value but you know most of the users were they used to pulling in spreadsheets, being handed. OK this is the couple pieces that you need to look at or you need to piece together your information from a report. And much later in the process this we came across this data visualization challenge. Never thought that this was going to be a a challenge. So we started putting things in different graphs and bar charts and you know all the fun things and they they just never worked.
With it before So they didn't know how to how to operate with it and function with it. And I mean I can tell you anything is you take something away as it doesn't matter if it's pretty if they can't use it. You know you have the prettiest piece of garbage at the top of the garbage can or you can have that chewed up pencil that's in your hand every day. You know, it just it doesn't work unless they can really use it. So we started presenting with some of them, they were struggling with it. So we had to kind of think through how are we going to to change our approach to really go to that next that next level with them. So solving you know what do we do to solve some of these.
Challenges Where did we go with it? You know so our first one was that that you know non unique identifiers leveraged Power Query and created some of the custom key columns. You know, they it's easy to do. There's lots of different ways to approach it and made it very approachable in Power BI would have been if there this was replacing multiple lines of different V lookups and things like that that he was doing in his system to try to manually do this and curate it and then he'd run his new report and be stuck.
Again. So we built some custom keys and Power Query that allowed us to separate it and start working towards that star schema to really make it usable and functional for the Nielsen, The Nielsen concern or challenge we had no like we really needed to go to that weekly context for this specific model. So we ended up splitting the models where we have a Nielsen model and we have AVIP and forecasting model because we really wanted to leverage this very specific issue. So we put in a custom 445 calendar, give us kind of some.
Of the weekly context that's very traditional in the in the retail space and especially the alcohol Bev space. And then for the the visualizations we we started looking at, OK, you know what Mike put put aside your pride, let's let's start incorporating some more matrix. Everything doesn't have to be a visual. It it's, you know, it's quick, it's easy, but it necessarily doesn't always tell the whole story. So we started blending a little bit more Matrix visuals. There's some report pages that are completely completely matrix or tables and that's totally fine..
You know it's it's about what the end user is doing with the data not how I perceive the the glamour of my report. So with that I'm going to kind of just jump in. I'll, I'm going to walk through just a couple of the reports I'll try to go. I have I have a handful that I want to go through and I've got some notes on it. I do want to check real quick here as I pull this up. Avi, are you are you guys able to see the the. No problem with that. Perfect. Just want to make sure that that came up OK. So this is our our VIP model..
So this is again this is focused on our on more of the depletion side of things. So distribution tracking, you know we started with the focus on you know how we're doing versus our forecast. So this is our quick our monthly view. This is intended just to give a quick snapshot of you know what did we forecast, how have we done month to date. This percent in is just the progress towards forecast based on where we're at in the month and then we show some last year metrics and again depletions that's what's actually been been.
Pulled from inventory. And we've also got the shipments that shows like what's actually been sent out, some quick trackers for their their leadership team to keep an eye on things. You know red is bad, green is good, shows where they're at towards the progress in their in their forecast. We broke it out by our focus markets and secondary markets. So they had some, they have some states that are this is key, this is where we're putting a lot of, this is where we're putting a lot of effort and time into. So we've split those out so they can quickly kind of see how are.
They doing in those states versus the others. And then again we have different levels of data where it goes from down to chains and the clubs and then independent. So we're kind of bringing out what the retail looks like and then we broke everything out by state. So that was one of the challenges and I I'll walk through that in just a moment. But we we wanted to layer in our forecasting data which again doesn't go down to the same level as all of our VIP data. So we had to figure out what visuals made sense on what pages.
So that data could interact with each other. You know, as we're going through here, we, you know, we're able to quickly slice everything down by by individual state. I'll choose one that might actually have some sales, sorry Alaska, you probably do not. So we can flip through some of these different ones and it just filters everything to context of what's going on in that state. You can see how the different chains are performing in that state gives you some quick visual indicators of what's actually happening as we're moving on..
I basically that brought up the question, OK, so we know how we're performing on a monthly basis. So this is essentially the same but year to date where it just shows over time so far this year. Here's how we're performing, here's where we're going. The top here is this is all the individual client data. So we're really focused in on them, how they're performing. One that was developed a little bit later, it doesn't have the, the, the sexy flair if you will. I mentioned it doesn't have to be visual to be important here is this unsold accounts, this one was has been hugely impactful on there on the organization we identify this.
Account sold status. So this indicates if an account has had a sale in the past 18 months but hasn't purchased in the last 90 days. So we can say, yes, they're an active client because they had a sale, but no, they're not there. They haven't purchased anything recently. This is really helping drive their sales team to, hey, why aren't they purchasing. So this is kind of more of an overall view of what they've got, what their different accounts that they're selling to. Again, it's all dummy data that's on here, but we can see, hey, this, this account has had a sale in the last 18 months,.
But they haven't bought it in the last six months. What's going on there? We gave them the ability to actually just also for building this with the intent of our sales team in mind where they can drill through and look at their details by address and this takes them specifically to that account. They can see what products were purchased, when they were purchased. So they can say, hey, you know what, you haven't bought anything since February. I noticed last time that you bought you know product one and product two, how are you doing on that inventory? Hey, we just have this new product, I noticed you haven't.
Purchased product 12 before. Would you like to try to give it a give it a try but it also gives them context to you know geographically we added in you know where they're at. We included their addresses so they could actually build sales routes around what they're looking at. Again kind of moving in that same direction. Is that the address details, which is the same thing, but this is designed more specifically for the folks, the sales folks to really engage. This actually works really well on mobile despite having a handful of the different sliders..
But if I'm a sales Rep, I can come in and just select myself out of this group and see, I think I did I choose someone in Florida? Yes. OK, perfect. Again, fake name. So I was trying to tie it in, make sure I chose the right fake name. But so these are all the sales associated with this sales Rep and this is you can kind of see his territory is down this and down out into the keys. But then I can also at the same time I can see what addresses are there when their last purchase date was. As you zoom in, it'll filter out the data..
