Print Looker-on Signed with airSlate SignNow

Print looker-on signed

[Music] [Music] hi everyone and welcome to this breakout session i hope you're enjoying joining so far uh today on this particular session it's going to be a technical one so we're going to be deep diving into how you can build out a sophisticated alerting system on locker so we're not using the uh the alerting feature that's that's built in we're actually going a lot deeper a lot more sophistication there and we're building that out uh with look amel so i'm jamie fry i lead the look of practice at datatonic uh we're a long-standed looker and google cloud partner based in the uk switzerland and sweden and here with me is uh ben powis who's from eminem directs another long long-standing looker customer and ben gets involved in all sorts of cool stuff uh data science uh they run a lot of machine learning pipelines they use looker for all their analytics and self-service needs and there's a lot of data to engineer cool stuff that they do as well i'll let ben introduce eminem direct but the agenda for today will be ben will take you through the journey that they've had with looker and how they utilize it throughout their organization then i will do a bit of a a deep dive into the alerting solution that we've built and it will be a quite a sophisticated tech demo so this is very much for people who are um looking to see how they can use looker in a bit more sophisticated way how they can expand their uses of looker if you're new to looker this will be interesting but you might get lost during some of the technical demos but i will now pass on to ben who can introduce eminentdirect [Music] thanks jeremy and hi everyone so my name is ben i head up the data science team at eminem direct and we're here today to talk about the alerting system that we work with datatonic on before i go into that i just want to give a bit of background on eminem for those of you who aren't familiar with us and what we do so eminem direct have been trading for over 30 years we're so we're an online off-price fashion retailer we sell some of the biggest brands in clothing and footwear to customers directly at big discounts we started as a catalog retailer so over 30 years ago and for many years the catalog was our shop window we would print physical catalogs and post them out to our customers who would phone us up to place their orders and that was the only way they could order from us we now have over three million active customers actually a milestone that we passed in the last few weeks so really kind of exciting uh landmark for the business we have a core business in the uk but we do sell throughout europe we have seven localized websites in european markets like france and germany and we ship to countries all over europe to give you an idea of the scale of eminemdirect.com the website gets over 100 million visits per year we sell over 300 brands in any given year at m m so as mentioned some of the biggest brands that you would know from clothing and footwear we send over 400 million emails out to our customers every year and on eminemdirect.com alone we show over 600 million pages of product every year so i mentioned that we started as a catalog business over 30 years ago actually about five years ago we sent our final catalog and we decided to stop printing catalogs and refocus our marketing efforts into digital customer acquisition and digital customer experiences so today the only way you can order through m m direct is through one of our websites what that means from a data perspective is that we get some data on every interaction our customers make with us whether that's a visit to one of the websites or an order we get to see you know our customers are telling us what they like to interact with what they like to buy whereas if somebody's flicking through a catalog it's much harder to derive that kind of information in terms of our data journey if we look back at the time we sent our final catalog five years ago our data looked very much like this slide you see in front of you so a typical kind of siloed data environment we had on-site engagement data in one place we had product stock information in another we had different teams working on these data silos and they were all experts in their own particular area of data but there was no way to join it all up and paint a kind of full picture of the m m experience that our customers were getting what that meant from an analytics and reporting perspective you can see in this slide so we had situations where a couple of analysts might be asked the same question and they could return different answers by getting different data from different data sources this is obviously quite frustrating we want everybody to be working from the same data and certainly to come to the same answers if we're giving um analysis that drives a business decision we want it to be a clear way forward so we looked at this and said what can we do to kind of help this data process how can we make things better we decided that the way forward would be to bring all of this data together into one place for us that was google bigquery we chose bigquery as big google analytics users it was a logical next step for us to use the kind of one-click google analytics importer into bigquery and then once we had that initial big source of data there there it made sense to add everything everything else to that so today what looker gives us is a platform to sit on top of all of that data in bigquery um obviously if we open bigquery out to the business it's a technical interface and our business users would struggle getting insight from it looker critically gives us that semantic layer where we can define a business-wide dictionary of dimensions and measures for eminem and it means our business users can get stuck into getting value from building reports dashboards running queries without having to worry about whether they're using the right definition of revenue or the right definition of a particular product type so we essentially manage all of that as a small team and then that really frees up all this data then to be used by anyone across the business so now we come on to what we're all here today to talk about which is real-time alerting so we have an environment today where all of our business data is collected in bigquery um in real time so every day we're collecting millions and millions of rows of end of interaction data on how our customers are moving through our website my team are always looking for ways to leverage this data for business value one project that was proposed by our e-commerce team was can we use this information to alert on the performance of the website in real time to leverage this data to show if there are any issues if our customers are experiencing any pain points any anything that's stopping our customers making a purchase or getting the best experience possible when we're collecting such a huge amount of data that's really hard to do in real time so we spoke to datatonic we outlined this this problem and we asked for their help in kind of giving real-time alerting to the business so that we could react more proactively to any bad experiences our customers were having and fix them before they became big issues thanks ben so that brings us to the problem um so let's talk a little bit around that the ask itself was um we needed to deal with live data so streaming data we needed to look at some key metrics so things like ads to the basket orders are processed whether people actually hitting the order screen itself so lots of key gateways that we need to just check and we need to be checking that on a periodic basis very very quickly so ideally we were checking this every five minutes so i had to think about a number of things one of those was what value do we expect and we also need to take into account seasonality and not just seasonality over uh days so for example christmas period versus the summer period we also need to think about time of day so at what hour would we expect that value to be so uh what we needed to do was allow a business user to set up an alert based on a key performance metric so we're going to be looking at the the demo today which we built out to show a replica of what we built and we're going to be using uh account of orders so the key metric would be what's my count of order should look like and if it doesn't look like that we need to send an alert out but that number that we need to uh what we expect needs to change throughout the day this isn't a hard-coded number that we need if we expect two orders to be coming in um at midnight but actually by the time we get to midday we would have expected that day's worth of orders to be two thousand uh we need to account for that so every five minutes we're checking what the order should be uh for that day up to that point in time so 2000 orders up till midday by the time we get to one o'clock it should be 2 100 orders uh and we need to check that we're hitting those metrics and we're not far off if we happen to be quite substantially away from those metrics either up or down then we want to be sending an alert and the way that we do that is we look at weighted averages so we look at what the value was at the same time yesterday and we can also look at it same time same day last week and also same time same day last year and that gives us that seasonality trend we can look back and see what previous values were and make an assumption on how far away we should be from those and we can also tweak the sensitivities of those so we can put more emphasis on the value yesterday compared to the value last year you could also flip it on its side and do a more predictive approach so we also put in the ability if you wanted to to select a linear regression model which would predict what your value should be and if you're quite substantially away from that then again it will alert so the user is able to set these alerts up themselves so they can say give me an alert on order account i want to check this every five minutes of the day and i want to use either weighted averages or a linear regression model in order to predict and i'm going to tweak my sensitivities and my numbers based on a couple of dials so for the end user very easy to do they can set it up themselves without anyone needing to do it but the complication of course comes in how you model it underneath and that's where the hard work is um but luckily looker gives you some good tooling to do that so let's just have a very quick look at the tech stack involved a bit of a deep dive into that so the components are we have bigquery and bigquery is a warehouse where as ben said all of our data is and that's where all the live streaming data is going in then we have looker and only other thing we need to do here is just use the command that's it and then once the modeling is done we're actually using look as scheduler in order to send the alerts out so that's what the end user sees they see the explore and then the scheduling features which they can then set up so that's it from a component perspective so very simple we don't really need to bring in anything else outside we're not fiddling with the apis we're just writing pure looka ml and there's nothing we need to do in the warehouse either because the streaming data is already been set up so we're just popping looker on top okay so when the user first enters into looker we expect them to come into an explore so the user will actually set their alerts up through an explore menu so that's the experience that we've created for them now you'll see in this particular explorer we have a number of options uh most of these you don't actually need to show your user but for demo purposes so i can show you how everything's working i've uh basically allowed all of the dimensions and measures that we need for the demo for you to understand how it works so the user comes in and they see you have this reference value here and we have a time range so the very first reference value is um as i explained we've set up either a weighted average or a linear regression to be used to compare your your current values against so the user might decide they want a weighted average and they want to check this um 5 minutes every 15 minutes 20 30 or hourly so we've decided on these time scales so you can decide yourself i wouldn't recommend anything below five minutes but anything above is perfectly doable and then they just bring in their metric name that they would like such as all account um and that basically is all they have to do they just have to say this is the metric name i want this is the uh the reference value and this is the time range but there are a number of other options that they can they can bring in as well so let's just jump into an example here i have a look saved and what this is doing is it's showing me order count the black is the order count for that particular time and i'm just looking at a particular day here just to kind of give you an example so you can see here at two o'clock the order was 17 and then at uh two o'clock the reference value was 18.33 so our weighted average from same day last same day same time yesterday last week and last year is 18.3 and our today's value is 17. that's not too different from what we expect so there's no trigger there's no alert sent whereas you can see at three uh one o'clock there is quite a substantial difference uh 29 being the current value and 18.66 being the the reference and you can see there there was a trigger so there would have been an email sent to the end user to say hey this value is quite a bit different to what we would expect maybe you should come in and have a look at this so in this case we would allow operations to say we've got some pretty big orders coming through on that hour are you able to take these on if i flip to the filters you'll see this is all of the metrics that we have to play with so we've selected order count as our kpi weighted average is our reference and then we have a sensitivity here as well this is actually how many standard deviations away from the mean so when we're using weighted average that's what our sensitivity is doing if we flip to linear regression then sensitivity would be set up in a different way but for this particular use case using weighted average i could say send me an alert if it's one standard deviation away from the weighted average or i can say send me another if it's three standard deviations away from the uh the average free is typically what you would do because it it's quite an ex anything in about three standard deviations away is generally a statistical significance so it's tends to be quite extreme so something's gone very wrong or something's gone very very right for you but for this example we set it at one just so you can see some some triggers uh we set this as an hourly time interval which is why you can see the graph is set hourly and then these are the weights that we're going to apply for last week last year and yesterday so a one means i'm going to be applying equal weighting but i could also apply uh you know a 50 rating to that 25 to that and then add 25 to that so you can kind of decide on how you want your weightings to be applied on comparison on creating that uh weighted average mean if we have a look at the data you can see here this is generically what we are using the end user doesn't need to see this they don't need to select this and create this as a look this is more just for us to show you how it's working underneath um but you can see we're looking at the yesterday's value last week's value last year's value and then we're computing a weighted average on that and then comparing it the way the user would use this is uh they go into the explorer and they pick through all these filters what they want and they kind of pick on the metrics they what they require they can have a browse of of the numbers as they see but if they actually want to set an alert up actually all you have to do is go into the into the explorer or a look that you have set up for them and they just click on here this up here and they click schedule that's it so if i click create schedule uh i'm going to send an email to myself i'm going to send it to me and i'm going to format it as a data table or visualization i'm going to send the visualization it's going to be a repeating interval it's going to be daily and this is where things get a little bit different to how you would usually use a schedule you need to send this every day after the same time interval that you would for the uh the time interval here so if i send this as every hour i need to set this to be scheduled every hour as well and if i set this every five minutes i need to then schedule this every five minutes um the reason we do that is the schedule is actually running a query and checking the same time period so every hour uh it's running that query to check these numbers so if there is a trigger there the schedule goes oh okay there's some data here to return i'm going to send this across so you actually set all your weightings and everything up here you don't do it in the explore menu you do it here in the scheduling menu and the key thing here is you only schedule this if there are results so if there is a trigger you want to set a filter up in your look to say filter only when trigger is yes and therefore it's going to return results in the look and if i just click on cancel for a second and i'm just going to go into explore what this would look like is is alert triggered and you need to filter that to yes and then what happens is it will only ever return data when that alert is triggered so we would only return these two rows if there are if there is nothing triggered then it's not going to return anything and therefore it's not going to send you an email so that's how we get around um whether an email is sent to you or not and of course you can set all sorts of other filters up here on top of that so you can add whatever you want to this model so that's pretty cool i'm going to flip back to the schedule again the beauty of this is i can set as many schedules as i want so i can call this uh we only want to see if there's a results call this alert example one and i could uh save that and then i could create a left example too and so forth or i can say this is a jamie's alert and the cool thing about this is uh they're all here in this explore so you can come back to this screen and you can manage your alerts from uh the look itself or from the uh the explore menu so that's really cool um you don't have to go to the admin menu and and manage your alerts there end users can come in and manage them from this page so um that's going to probably send me lots of emails now so i'll have to go back and correct that later but that gives you a good example of how that works from a user perspective we just look at this visualization again um this is me just showing you where we can see the alerts triggered and if i bring in last week yesterday and last year's value you can clearly see how these make up that reference value here in the middle so it's a combination of all of these values which give us this nice weighted average okay so let's have a look underneath at the model itself um so i'm going to flip over to the uh project and you can see i'm in develop mode here we're going to be playing around uh have a look on the left here in the fro file browser and you'll see we have five files so we've split this out into five uh separate files uh no reason why you can't do it all in one but this is a good way of layering at your code and then we have one model here and one explore so this is the explore for the alerting and you can see here we have a some always filters in here based on the time range to bring in and of course that reference value so you need to pick whether you're bringing in the linear regression or the weighted average so the explorer itself is pretty simple um but let's let's cut this down into sections first so the very first section is going to be parameters so you saw we had all of those little buttons for the user to play with set the weight yesterday weight last week weight last year and we have some default values for those and we want someone to also set the sensitivity so that'll be x number of deviations away from the the mean and the default for that's going to be free because that's our recommendation and then we also have a parameter for the time range so we're allowing the user to set alerts up in five different time ranges and then we also have to have a selection for them to pick the uh we're calling this the metric names but this is basically the kpis so in this demo piece of code i've only got one that's old account but you could have anything you want there and the very last one is of course the the reference value itself and this is quite important actually and you can see we've got a type unquoted here as well and type unquoted for metric name and that's because we're actually going to be passing these values through into the code elsewhere so we need to be passing them through uh without any quotations so that's uh that's the first thing and we hold this in a in a view object by itself and uh as you can see there's nothing else happening with it so we actually extend this code uh elsewhere the second thing is the time dimensions so as you can probably imagine there's a lot of sophistication underneath on how we actually calculate the point in time that we're looking at the data and again this is just holding a load of dimensions as objects we're not putting a sql table here we're not bringing in our data in any way shape or form we're just defining the time objects which we can then use as a template in other areas of our code so you could use this not just for this alerting but you could use this elsewhere so we need a dimension for 5 minutes 15 20 30 and hourly and we need that for the the current time stamp so the current point in time and you can see we're utilizing look looker's type metric there to get that five minute interval we've done that so you don't have to mess around with database dialects so this could be used for any dialect you have um we then i just do that for a second we then um have the same for looking at uh five minutes from now so what was the time five minutes from now uh sorry five minutes before uh 30 minutes before 15 minutes before so that's a requirement that we need as well and we then do the same for uh our actual data itself so we also need to figure out that five minute time interval or that hourly time interval for the um the time stamp in the data that we're actually looking at now we reference table here because we don't actually know what the sql table is going to be at this point so we're just doing a generic substitution operator um but the actual time stamp field we're going to be using we're going to call it time so wherever your data is coming from we're going to rename it to that in a drive table which you will see in a bit so if we just unfold all of that code again you'll see i'm then setting up various different dimensions here to work out whether we're using the hourly now hourly or five minute dimensions and and whatnot so uh this is code which uh will open source so you can go through this in a bit more detail yourself but the takeaway here is we're just setting up all of the different time dimensions we need and we're driving that with the parameter selection which the users will use so that'll be the time range parameter that they select and we also have a couple of dimensions down here which will be used as flags so is yesterday is last week is last year and those will be flags which we will which will uh come in against the rows of your data so we can identify if that row of data is yesterday's date or wherever that row of data happens to be last year because of course that's dynamic that changes every time we run the query a row of data could be yesterday and then suddenly it's last week when we look at it a week later so we have to do that in a dynamic way and that's why those dimensions exist and then the very third section here before we start to actually tie in our data is we then have our measures and our dims for the weighted average and you would also have another view file if you were doing the linear regression which i'm not going to go into because it's using bigquery machine learning which is a whole other topic and not everyone also uses bigquery so the takeaway here is you will have a view for each way that you want to calculate your reference point so we're extending the uh the time dimensions in here so they're coming into this weighted average file and then we'll create we're working out what our today's value is uh by looking at uh the the metric name itself so the metric name will decide on what field we're going to be pulling through and then we have that filter that is today filter to actually then pinpoint the row of data that's today's value and we do the same for all of the periods that we're looking at and then right at the bottom you'll see here we have the weighted average i just zoom in a bit there we have this weighted average and that is our calculation for actually working out the weighted average whilst taking into account the uh the the sensitivities that uh people might be wanting to use okay so uh we need to now pull this all together so you can see i'm in the metrics file and what we're actually doing here is we're creating a kpi um so we have our old account here that's our kpi and we're looking at that on an hourly basis um obviously we want to look at that as well on a 5 minute 15 and 30. so at most we need the uh the hourly timestamp and we're pulling this through as its own derived table um because that's all i need i just need the order count and the timestamp and you would do this for each kpi that you want to have an alert on um then you'll see we have an explore here called the alerting metrics explore and we're actually joining in the parameters in order to make them available i'm also joining in the linear regression metrics as well because that's done very differently in doing linear models we're not going to cover that off and the explore alerting metrics we're actually exposing that view here in this explore and what this is doing is it's extending our weighted average and so all of those dimensions in the weighted average file and the time dimensions file will flow through into this view and then we're using just uh navigate to a bit better here as you can see we're then we're then using the metric name parameter to decide on what table we're actually hitting so if someone selects a word account we're going to hit that order count derived table that we created up here and we're also going to extract time from that timestamp that we have in there and we're going to add in a 15-minute interval there so we're actually filtering all the data for each day to match that specific point in time for the day as well so that's that's quite a quite important to make sure that we're matching those time periods now this is an inefficient piece of code i've left this in here for demo purposes it's easier to run through ideally you would write this in a different way where we're not doing a select tool and you're also passing through whatever dialect you're using you need to also pass through things like partition filtering and use of clustering and stuff so this will very much change the look depending on your database dialect and how your your data is actually set up so that's quite important and then the very last thing we do is we actually uh expose this as a as an a derived table itself so we have an explore source for that alerting metrics as you saw uh here and that explore source then goes into a derived table and the reason we do this is we just want to dimensionalize all of these measures and we also need to bind those parameters together so those parameters need to be binded to themselves so that they can operate in all of those sub queries so that's quite an important step to do and um this is also where the magic happens with the um the alerting itself so this is actually where we then define the the alert is triggered measure which is where we're we're taking the reference value um and we're basically minusing off today's value the mean and then comparing that to the the mean less the standard deviation uh times uh the sensitivity which is quite important and we're also defining the threshold uh metric there as well and um we also have some what we call time window filters that's quite important too we can give the ability for the user to decide whether they're looking at the current time window so the so if we're for example it's um one minute to five and we're looking every five minutes the current time window would be 455 to 5 or are we looking at the previous time window which would be 450 to 455 so that's quite important to to node as well so that's the code um quite can get a little complicated at the end there um we're going to open source this so you can go through and try it out yourself you don't need to change anything from number one to free uh where you start adding in your own uh metrics or information is gonna be file four and file five and you can customize that to how you want so let's just jump back to the slides and we'll uh have a little talk about lessons land thanks jeremy it's great to look back now with all the hard work that went into this project and uh and see the kind of value we're getting from it so in terms of what this project has been able to do for the business it's obviously given us that real-time reporting system that we wanted and we're able to alert on any issues or any friction that our customers are experiencing on the website in almost real time and and the value that adds to us especially during a period like our christmas trading period or a peak trading period where literally you know every hour we're taking thousands of orders so any kind of small issue that lasts even half an hour can cost the business a considerable amount of money so being able to react to them really quickly has really been invaluable and i think leveraging the power of looker has been amazing too for this project so without having to build anything or spend a lot of time developing our own solution we've been able to leverage the um the tools within looker like the email alerting and just being able to tackle this with look ml has been really great so we're able to build on what we've got without having to kind of reinvent our whole solution so well done [Music] so the final takeaways will be it's quite a sophisticated solution but for the end user quite a nice experience because they don't see any of the modeling underneath they just had that nice explore menu to manage their alerts and create them it's also a near time warning system and we're not having to use any other tools outside of what eminem direct already had so we worked with what we had we had looker let's make it in looker let's not try and create any other uh plugging systems together we're using lots of different services there's just no need and of course um it's customizable exactly to how you require it your analysts can set it up it doesn't require a data engineer to go and do and it allows you to action on your operations so in this case we're monitoring operations and we're getting insight into what's going on very quickly whereas other tools will actually have a larger lag to actually notifying you on what's going on so with that i just want to say thank you if you want more information you can contact either me or ben uh you can contact us on twitter or linkedin or through our emails you can visit our website to find out more there's lots of technical blogs on there as well i did mention bqml with a looker wrapper that is a pretty good solution to pair with this alerting block which you can view on how to do that on our blog posts on datatonic.com and this code for which i've just shown you through will be posted on the community so please do look out for that community post and i hope you enjoy the rest of your day and the rest have joined