Analyzing Google Sheets Data in Python | The Right Way to Query Tables

Very excited to be here today and to walk all of you through a live coding example of how you can analyze Google Sheets data in Python. So going up immediately into the meat of what we'll do in this case study or in this code along is we will be working with Google Analytics data from a web store. It's basically data from one day in August of 2017, so quite a while ago. But E commerce data hasn't changed that much recently is basically number of people visiting websites, how many people end up visiting web pages, how many people end up buying something. We're going to analyze that data and actually that data is going to be stored, will be stored inside a Google Sheets file or Google Sheets and we will be using Workspace and the features that Workspace provides to analyze that data straight with Python without having to export data imported back into Workspace without having to do any manual steps. We'll basically do this all from Insights, our data notebook. So the primary goal of this code along is indeed to analyze Google Sheets data directly with Python without switching between tools. And of course, to get our hands dirty some more, we'll also practice with typical EDA tasks and then specifically for this data set that is on E commerce data. So it's a very common data set like type of data. So it's super useful to also have some experience with this in the context of doing exploratory data analysis. Now the big why I think Richie couldn't have introduced it better. Python is extremely powerful. It can do things that something like Google can do, some things that Google Sheets can't. For example, if you want to do advanced modeling of some data and if the data is provided to you in sheets, you want to avoid all of these steps. So this is basically a way to leverage the power of Python even when the data that you work in lives in spreadsheets. All right, so with that, Ruiz has just shared a link in the chat that says code along with us, which is a shortened bit link bit ly link. And you should be able to just click that link. And I'm going to do exactly that as well. Just a moment. For some reason, let me see. I'm going to create a new Chrome tab. No, just a moment. And I'm going to do like this. I basically fill in the bitly link. And what this does, this launches me straight into a new datacamp workspace, which is the name for a project inside Data Camp Workspace. This launches me straight into a notebook environment which already has all of the content that we will need today to work through our code along here. So you basically hope Reece asked me to make this somewhat bigger. I changed to 150%. Hope this is better. This is all clear for you. Who is following along. You should see the same as what I'm seeing. If you clicked on this bitly link, I'll take a second. If anybody's having issues, definitely let us know so we can help. So this basically launches you into a notebook inside workspace. So this notebook is basically, hopefully you're familiar with that is basically a collection of text cells and code cells. We have a title here, a bit of introduction, like what I said sample data contains obfuscated data from some Google Analytics360 data from Google Merchandise Store. So this is real data like from an actual e commerce store from seven years ago now, and then contains some setup and then basically all of the tasks that together we will work through. Right. So as I'm working through this challenge, as I'm working through this notebook, you can code along with me. I'll make sure to take some pauses here and then so you can basically follow along with me, type the code as well so you can really get a feel for what we're trying to do here right now. The first thing in any analysis when this analysis, what we're basically going to try to figure out is like, are there specific patterns in the traffic that we get here from the single day of people visiting this Google Merchandise store? Do we see a difference in the location that people are visiting this website from? Do we see a difference in what kind of device people are visiting this from? Do we see a difference in like what's the division of sessions in terms of like how many people come from direct like organic search, which is basically people filling it in into Google. How many people come through advertisement? How many people come in through socials? How many people come in through referral links by for example, influencers online? So these are some analysis we're going to do. And at the end if we still have time, we'll also try to figure out which channel is driving most of the sales on the platform. So which is basically like the golden channel. So to say what's the channel that's best driving sales for this e commerce store? Right. But of course before we can do any of that, we first have to make sure we have access to our data sets. Right. And because the code along is called analyzing Google Sheets data in Python, we will get this data from a Google Sheet. Right. So I prepared a Google sheet for all of you and if you click the link here in task zero setup, what this is going to do, if you follow that link, it's going to ask you do you want to make a copy of GA sample data? So it's basically going to ask you to create a copy inside your Google account so that in the next step you can connect to that Google data. So I'm going to click on make a copy and hopefully that works. Not sure what's happening here. Yeah, there we go. So I clicked on copy and it basically gave me basically created a file copy of GA sample data. I'll give that a name GA sampledata code along like you can give this any name. You don't have to use underscores. You can keep the name copy of of course as well. But you should see this as well here on the right hand side it's showing my avatar. But of course in your case it should be your Google account avatar that you that you should see here because it should be a copy that's created in your account. So what you immediately see is that we have a couple of columns here. So we have the visit id which is a unique identifier for that visit to the website. The start time of the visits, which is probably like a long timestamp in seconds the visit number to how many visits. This was for this specific user channel grouping. That's basically like the channel through which somebody came in which browser did people visit on, what operating system, what device category, what continent, subcontinent and country that people visit from. And then finally also transactions like from this visit how many transactions originally resulted from that? Right? Can be one transaction, two transaction, five transactions. That's basically how many people how often or how many things, how often people bought stuff from this visit. Going back to our. I have a lot of Google Sheets here. I see now going to close this one. Just a moment. I have to work through all of my tabs. I'm not used to going full screen here. So I'm going to quickly go back to my previous tab here. I'm going to go large again. So I go back to my workspace and as a first step what we need to do is set up a connection to this Google Sheets data. And how we do that, we basically do that through a so called SQL cell. That's a cell that's specific to data Camp Workspace is basically this notebook you're working in. What we do we click on over here and we click on connect to database. Then we can select Google Sheets. So I'll go back Basically the source right now is Data frames and CSVs. We're going to change that and we're going to say I want to a new database. Click on Connect new database and among the databases. So datacamp workspace supports native connections to all kinds of databases. You can also select Google Sheets. So I'll do that. And what this will do, it basically goes through an entire authorization flow. In your case, the authorization can be a bit longer. In my case, because I'm working on datacamp workspace all the time. I already given Google permission to look at the spreadsheets in my Google Drive. You may have to explicitly authorize DataCamp to access your Google Drive data. Right. So what I can do right now is to ga. What is it? GA sample data, I think I called it. And there's like a gasampledata codelong. You should be able to find your spreadsheets, the spreadsheet that you're interested in. And all you need to do is click on that spreadsheet. I'll take a moment so that everybody can get up to this step. But basically you should get up to the step where you can select a file and then you should find that whatever you named it, basically that Google Sheet that you copied over from my sheet to start with, you should click on that and then you need to click select. And this launches me straight back into my codelong. And if I close my database tab again, what could I select right now? I can now select JSAMpledata code along. So that's now the source of. That's like a connection that I make to my Google Sheets. And that's something that I can start querying right now immediately. Workspace has suggested to me a query that I could run, but that's not the query that I necessarily need to run. So I can still change that. If we have a look here and we click on this Browse Tables icon, we can see that there's actually every sheet inside the Google Sheets. Every tab inside the Google Sheets is turned into a so called table and we can basically query from that table. So if we go back here, what do we see? GA sampledata. So this GA Sessions is the name of this sheet, a sheet name inside the Google Sheets. And that's the thing that we also see appearing here. So what we can do right now, if that all was successful for you, what we can do is basically write a very simple SQL query, SQL Central Structured Query Language. If you're not familiar with it, it's a very common way of like basically talking to databases. And we can write a very small simple SQL query to basically fetch all of the data straight from Google Sheets, right? So let's do that. Do a select star from and instead of sheet one, which is like the default sheet name for like one of, for a Google sheet that you create, we can say GA Sessions, right? And I can get rid of all of this because that's a comment that's not important. We basically have the simplest SQL statement possible. Saying select star star means select all of the columns from this GA Sessions date. If I click on run what it's doing right now, it's basically contacting this Google sheet that's living somewhere else and it's fetching all of the data and pulling it straight into my notebook, right? So this Visit ID visit Start time that you see here, that's exactly the data that we see here as well, right? Visit ID 1500-159-15-0159 right? So it pulls all of that in and it's as easy as that basically, right? You just say I want to connect to a Google sheet. You click a couple of times, you select Google Sheets, you want to contact, you pull that data in and you write a SQL statement. This is basically a SQL statement that gets all of the data select star from jsessions like get me all the columns, get me all the rows. Of course this is a full flexed, there's full fledged SQL support. You can also go further. You could say, for example, and let's just do this for the sake of experimentation, we will undo these changes. So you don't necessarily have to code this along with me to continue the rest of the code along. But we can do these, we can do some changes and say for example, limit them, which is a way of saying I only want to get the first 10 rows. So instead of getting 2,556 rows, right now we get 10 rows. We can also say get only some sessions where the operating system is Windows. What we did here, we basically built a wrapper around this Google Sheets and you can talk to this Google sheet using SQL. You can ask it questions with the SQL language and you can get the end like you can basically get the result of that. You can even go more crazy and say I want to select by device category, I want to select account from GA Sessions and then I want to group by one. For example, this is not the subject of this code long learning all the SQL code. This is me just showing to you that this is a full fledged SQL integration and you can use progressively more complex SQL to answer some of your questions. Here we immediately see that of the 2,500 something records or the 2,500 something Google Analytics sessions that were recorded, we have 1742 visits from desktop. Right, that's back in 2017. I would guess that if you'd rerun this analysis from data that you have from 2023, that probably it would be the other way around. There would be way more mobile visits. Right, okay. But that just as an aside, let's change it back to what we had before can do. Just select Star from GA Sessions, which is just a blanket statement to just get everything in. Right, so that's what we'll run here. All right, So I hope that all went perfectly fine for everyone. What's also nice is that basically the result of the SQL query and all of this output you see here, that is being stored as a Pandas data frame, and that is now available as a Pandas data frame df. So what you'll see right now is that there's a variable. We can create a new Python cell here. And if we type DF and we run this Python cell, what do we see that this data frame that we just pulled in, that it's available as variable in our notebook that we can continue our analysis with. That's pretty neat. So I'll go ahead and delete that again. And this actually allows you to make your notebook some sort of like polyglot and basically turn this into combine SQL and Python all inside the same notebook. All right, cool. So that was like the first task, importing the session data in the first place. We all have that from Google Sheets now. And now this is basically where we enjoy to be in our beloved Python environment. But with a nice touch that we didn't have to do. Manual exports, manual imports and uploads, et cetera, we basically got the data straight inside Google Sheets. That also means that if a colleague of yours or somebody you collaborate with, they update that Google Sheets, or you update that Google Sheets and you come back a day later and you update this notebook from top to bottom, that of course, automatically your notebook will take in those last those latest changes. So if you have a Google Sheet that is like a living thing that constantly gets updated based on like new information or something that you're keeping up to date, you can make this integration and you build this integration like this, you basically are constantly getting updated data inside your notebook, inside your analysis, without having to do additional manual steps. So that's actually also pretty. Then all right, so so far we basically have the data. As a next step, let's start doing some analysis. Let's start doing some exploratory data analysis as a first step. Very typically inside Python, if you start your analysis is importing packages, right? We'll use Pandas and Plotly here. So let's import these two. We'll do import Pandas as PDF and we'll do also import Plotly Express. That's like a sub module of Plotly, which is useful for like, very quick visualizations, which is perfect in this case, which is Plotly Express. And we'll import that under the alias px, which is a very common alias for importing Plotly. All right, good. If you look at the green dot, that ran perfectly fine. And now as a next step, let's see, right, like we did a very simple select star from statement, but we already see that it's Visit start time is just a number. And visit start time, it actually should denote like a certain time as a certain time in the. It was like August 1, 2017, if I recall correctly. So let's see if the types of our different data frames are in good shape. So let's do DF D types, which is very common way to basically check what are the types of my data frame. And what do I see? Visit id, that's an integer, which is basically like a whole number. That's fine. But then Visit start time is also an integer. Right? Now, Python has parsed this as just like an amount of seconds, it probably means, but it didn't automatically turn this into a date, so that's something we need to fix. Then we also have visit number, which is something we won't use in this code long, which is an integer, which is totally fine. Then channel grouping, browser, operating system, all the way up to country, that is object, so that means it's a piece of text, and that's fine. And then transactions, that's also a number, that's float 64, so that's supposedly a whole number. I wouldn't understand why that needs to be a float, but I think that's not a problem either. So the only thing we need to fix is this visit start time thing here. And what we need to do basically is, is turning that into a timestamp column rather than being an integer column. So the first thing we'll do here, and that's typically a good step when you're cleaning data, is turning your data frame into basically copying over all of the contents of your data frame into a new data frame so that if you're making changes to that data frame, you're not basically overriding your original import and you don't have to redo that import because that import can also sometimes be pretty expensive. In this case, also, it took like five, six seconds. And this is basically a good way to make sure that you're not overriding stuff in a way that breaks things. So the first thing we do is DF Clean. We're creating a new data frame, DF Clean, which is going to house our clean data frame. And we're going to start by making a copy of Data Frame. We basically take a full copy of those data frame contents, which was this data frame of 2,556 rows, and we're storing it into DF Clean. Then as a next step, we can basically start cleaning up this data. Right? So we're going to say DF Clean resume in on visit start time, like this, because that's the data frame we want to update. And we are going to do BD2 datetime of DF clean visit start time. So we're basically telling pandas, turn this visit time into a date. Right. Let's also print DF Clean to see what we're dealing with here. So let's click run here. And what do we see? These are all dates on the 1st of January 1970. So there's definitely something that went wrong here. If we're looking a bit higher, we see that the visitor start time. It's typically like when visitor start time is like a number of. It's just a number. It's typically like the number of milliseconds or the number of seconds since the start of like when computers started counting. And that's the 1st of January 1970. And something went wrong here. I think the resolution so to say of visit start time is seconds only. But by default, the two date time column has understood this resolution to be milliseconds or even less than that. Right. So what we need to do is being very explicit and say, you know what? DF Clean is actually the number of seconds since the 1st of January 1970. So we need to say units equals seconds. And if we do that, what do we see then? All right, then we see indeed, now we're dealing with something which is on the 1st of August in 2017. Right? So this is the statement where we basically took DF Clean visit start time and we turned that into a date time. And then we do the. And then we assign that result again to DF Clean Visit Start time. Right. And if we now in our next cell to DF Clean Dtypes again, what do we see? We see that very neatly right now. Visit Start time is a datetime column. So I would say our data right now is clean enough so that we can start exploring this. Okay, so just a real quick recap of what we did so far. I'll keep this zoomed in for a second. So if you still wanted to type along. By the way, if you feel like sometimes this is going a tad bit too fast or you want to avoid doing all the typing yourself, you can head over to the file step here. Click on Notebook Solution. That should give you a fully filled in solution key of the notebook that we're working through. So if you feel like, hey, this is going a bit too fast or like I got distracted for a second or something, you can always head over to the notebook solution, copy some bits of code and move them over to your notebook IPYMB and catch up again. Okay. All right, so just real quick recap of what we did so far. First off, we're dealing with data from an E commerce called the Google Merchandise Store. This is a real store. We had some Google Analytics data, something I skipped, but Google Analytics data. Google Analytics is basically like a tracking platform by Google where you can very closely track how people are like landing on your website, what they're doing on your website so that you can better understand how you can drive conversions. Which is like a fancy word of saying like basically people paying you how you're driving conversions on that website. So we have some data from August 1st, 2017. It was stored inside this Google Sheets and what we did, we took a couple of like it took us a couple of clicks to say like you know what, I want to connect a new database and I'm selecting GA sampledata code along and I am doing a simple SQL query select star from gasessions which is getting me all of the contents of this Google Sheets. The nice thing about this is whenever this Google Sheet ever updates and I rerun the cell, it always gets the latest data without me having to do any manual steps. In addition, right, something important to note. This connection that you create to the Google Sheets right now is a read only connection. So you can do a select star from GA sessions you cannot do if you're familiar with that, update statements like in SQL you also have update statements to write data back to a database that does not work in this case. So in this case the Google Sheets integration that you built into workspace is Only for fetching data from Google Sheets and pulling it into the workspace. It is not for going from the workspace or from the notebook. So basically from Python back to data you put back into Google Sheets. There is a way to do that. Rhys can share you a documentation article that goes through the steps to do just that. Because in some cases, like you have colleagues that are working in G Sheets, you want to pull it in from G Sheets, you want to do some calculation and you want to push it back to G Sheets, but you can, you can do that too. But there the steps are slightly more involved. You need to set up a so called service account on Google, you need to store that service account details safely inside workspace, et cetera, et cetera. So that's a tad bit more complex and that's also not in the scope of this code. But just as soon as you want to fetch data, like if you just want to fetch data from Google Sheets, this Google Sheets integration is by far the easiest way that I know of today if you want to continue working on this data impact. And then in the next step, we imported some packages that shouldn't be anything new to somebody who's first in Python. And then in the third step we cleaned the data. So we basically had a look at the dtypes, looked at the visit start time, and then we noticed that actually the visit start time is still a number and we converted into a date time, being very specific that this is like a timestamp in seconds rather than milliseconds, so that our visit start time ends up proper right here. Something else that I suggest you add is localizing this data. So with time zones, I don't want to go too much into details. Time zones is like a super gnarly thing inside Python. But what you can do here is dot dt and then do TZ localize UTC to basically say this timestamp, convert it to like a UTC timestamp for now. Right. So that's not super important to do, but I think it's always a good idea to attach timestamp to attach a time zone to your timestamp so that you know basically what the frame of reference is for the timestamp. Right. Because for me it's 5:30 here in Belgium where I am. But there's people following along from wherever in the world and if they look at the top right of their computer, they'll see a different date. Right. All right, cool. I'm going to pause here for a second already. If there's any questions I'll happily answer those. If not, I more than happy to already head over to the next task which is on exploring data.

Yeah, we just have one question so far from Rich who's just asking, how is this different from the Google Sheets talk on I'm not sure where you're from. This is either the 9th of November or. Or it's the 11th of September, one of the two. I look back, we didn't do a code along on either of those dates, but we did do a couple of spreadsheets related code alongs around that sort of time. So perhaps Reece can post a link in the chat. The difference is we're doing work from Workspace with spreadsheets data rather than the spreadsheets themselves for anyone else. If you've got a question for Philip, please do ask in the chat. Now I see one that's just come through through from William. So William asks can I do this from another environment like Google Colab? So we occasionally do do these code along to the Colab. Colab's horrible, which is sort of why we invented Workspace. But Philip, do you know whether Colab offers a similar functionality?

No. So Colab does not offer like native data connectors. Colab does offer like an integration with Google Drive, but I don't think it works well for Google Sheets. And so Colab does not offer these smoother ways, so to say of connecting your Google Sheets data to your Data Camp workspace. I mentioned before, like this is only a read only connection. There's also like a version of like connecting to Google Sheets that's slightly more tedious but that allows both reading and writing back to Google Sheets. That version basically just depends on the support for environment variables, basically secrets. And that's something that both Workspace and Colab has. But definitely the smoother way to work with data from inside your Python notebook is through Data Camp Workspace.

Yeah, definitely. I've played around this sort of thing for messing about the Python packages and you always like, well there's a key somewhere and it gets fiddly. So I have to say I do appreciate the fact that it is just a case of select your Google Sheet from the SQL menu. So much easier. All right, we'll do more Q and A at the end, but for now.

I think you can carry on. All right, great. So that's it for the recap then Task force, let's start exploring this data. We have some data on our hands. I already did a rogue SQL query back then to look at like different counts for different like browser types or what was it? What I did for this task, like the best way to explore data in my way, in my view is like to go visual just to like to see it for yourself. Like puts things into perspective much more than just looking at a bunch of numbers. What I did is I used a couple of pre. Like I created a couple of plotting function basically to explore different group session counts as either a bar chart or a pie chart. So it's this function. You don't have to change anything about this if all is well. It's called plot sessions per group. You can just pass it a data frame, you have to pass it a group as like as a string and then you can specify the visualization type. If you don't specify anything, it will generate a bar chart. And what this will do, it will basically show you a grouped count as basically saying like how many sessions do we have along a certain group? Right? So as a first step, what can we do? First off, make sure to click on run that you basically have this function available as a variable as a thing in your session so that you can call this user defined function. And then as a next step what we can say is plot sessions per group, which is basically our function call. We then can call clean DF on it. Then we say can you do a grouped visualization for every continent? Basically for every separate value of continent, check how many sessions originated from that continent. So you click on run clean. DF is not defined. It's probably because I use DF clean. That's my bad. So I have to change it to DF clean. I used the wrong name. And if I then click on run, what do I see? I see that by far the most sessions are coming from the America. So from South America and North America and probably a lot of north from North America. Because this is a merchandise store for Google which has like big presidents, North America and then second most visited from Europe and then Asia, right? That's number of sessions per continent. So it's pretty cool to see how this plot function basically did a bunch of stuff like for you. So it basically set the X axis properly, it's at the title properly and all of these things right now as a next step and I'll leave this on to you, can you do the same? But I would like to see a grouped count for channel grouping. So if you go up again you'll see that there's like a channel grouping column here, a channel grouping variable in a way in the data frame. Can you do a similar Call to this function, but this time do a grouped visualization for channel grouping. I'll pause for a couple of minutes so that you can try to figure this out. And then if you see the channel grouping and you have this visualization, try to see if whether it's a good idea to do this as a bar chart or whether it's better as a pie chart. Right? You can set both bar or pie here to generate a pie chart. Instead, try to figure out for yourself whether a bar chart or a pie chart is the the preferred method, so to say. Right, I'll pause for a second. All right, so if you want to do the same thing, you can basically say plot sessions per group. Then we do DF clean and instead of saying continent this time we say channel grouping. It's basically as simple as that. By default it's going to generate me a bar chart and seeing display affiliates, paid search, social, direct referral, organic search. I could also say this type equals pie and what do we see then we see a pie chart, right? In this case, I don't think the pie chart is a good idea, to be honest. There's also a comment from Recognitive. I'm not sure if that's their real name, which is saying like pie charts are bad according to datavis experts, indeed, they have very good points. Pie charts are not that great because it's pretty hard to compare the size of things if you can't like horizontally scans, like, okay, is this twice as big, is this half as big, etc. And as soon as you have more than three categories, it's actually not a good idea to use pie charts because it's pretty hard to compare all of this. Right? So it's a better way, like you can get a better sense for your data by just using bar charts. And then you immediately see, okay, organic search is like three times more common than referral. Direct and referral is somewhat the same. And then all of these combined are basically not even coming close to social, right? So you can horizontally scan so you have a better frame to refer to. Better frame of reference to refer to the relative importance of these different categories. So taking a pause here, we just use this utility function here. But what do we see? By far, most of the session comes, Most of the sessions come from the Americas. By far most of the sessions come from organic search. Okay, so that's already something that we know of interesting. But we also seen, it's important to remember later, Referral is the second most common. But it's like 1/3 less in terms of volume. Okay, that's something we have to keep in mind. And then finally let's do a similar exercise. We can just copy this over because we're always a bit lazy around typing as programmers. Let's do the same, but this time also let's have a look at device category. We run this one, I just use the shortcut Shift enter Command enter or Control enter. I think it's on Windows. And what do I see here? I see tablet, mobile, desktop. Again the bulk of all sessions are on desktop. However here, which something that re cognitive might not like, I would prefer actually pie charts because there's like a very little categories. They all sum up to like 100%. So we get a sense of like what's the total share of, of desktop users. So if you change to this type by here, I actually prefer this way of watching it. This also gives you a percentage count. If you, if you hover over it, you also get like the absolute numbers of sessions of 1742, but it's 68.2%. So almost 70% of the people that visited this workshop on the 1st of August of 2017 have done so from a desktop computer. And then 28% is mobile. Right. All right, so that was like the first like dipping our toes into exploratory data analysis. Again this is all data that comes straight from Google Sheets. We didn't have to do any manual copy paste. We didn't have to go to Google Sheets and say download this as a file. It didn't have to do any of that. Just pulled it in with some simple SQL and we continue our analysis right here in workspace. All right, then distribution of sessions per device category. The next thing we will do is show hourly visits per channel grouping. And actually there is already some code here, so if you click run, this should already give you something. But basically this code is doing is doing some more cleanup on visit start time. Basically turning the time zone into Los Angeles because that's the location of this store so that all of the Data fits into the 1st of August 2018 and it's not shifted because of a different timestamp. And then what we're doing is we're filtering out the basically the hour part of the date so we can do a grouping based on that hour. Try to bear with me this bit like complex Python code. That's why I already provided the sample code. And then finally we do a group by that hour. Then we do a size count which is basically saying count all of the Sessions per hour. And now we're resetting the index so that we get rid of the index and it's basically the visits column becomes a column on its own. And then visits per month, hour, we convert that into a string so that it looks nice on our graph. And then we create a line chart in plotly, P exop line, it says visits per month X equals hours, basically, hour on the X axis, visits on the Y axis. And then we give it a meaningful title. And we're also doing the markers thing, which is basically adding these cute little dots here to our plot, Right? So if you run this, this should work beautifully out of the gate. Your job in this case. And I'll take a pause for a second again. It's like, okay, we see that clearly, like, as of 6:00am, number of visits to the sites grow, and that as of, like, 3 to 6, 6pm These visits die down again. And basically, like, during the evening and during the night are, like, especially low, right? During the evening is still fine, but during the night, when everybody's asleep, they're especially low. So less people are visiting during the night, which makes total sense. Unless you're a night owl. That's basically for that specific day. That was like the traffic pattern. Now, if we had more days, we could have basically analyzed this and basically overlaid this with different patterns for the day. But today we just have the data for one day. Your job in this case. And I'll give you a second again, try to adjust this code so that you not only show the hourly visits, that you not only show, like, how many visits you had every hour. So, for example, like in the eighth hour that started at seven, ended at 838 visits, I would like you to split all of this stuff up and basically also show this graph for every separate channel grouping. So if you think back to the channel grouping, we have display, affiliate, paid search, social. I'd like to see this line chart for every different value of channel grouping. So what's the traffic pattern of people that came in through socials? What's the traffic pattern of people that came in through organic search? What's the traffic pattern of people that came in through referrals, et cetera, et cetera? Right, so I'll take a pause again and leave it up to you. So you have to do some minimal changes to this Python code right here and try to convert it so that it shows line charts per channel group grouping. All right, So I hope you all figured it out. Basically what this means is we want to do not grouping only by the hour. We want to do grouping by the hour and by the channel grouping. So what we need to do is inside our group by call here we can just add channel grouping, like so. And then what we can also do is rather than just saying X as like a dimension along which we want to show, and Y as a dimension on which we want to show visits, we can add a third dimension which is color, right? And we can just say color equals channel grouping. So those are the changes we need to do. It's like this thing that I highlighted here. I basically add an additional string to Groupon, additional column to Groupon, and then I add that column inside channel grouping as well. And if I run this data, what do I see now? Very neatly, I see channel grouping. Like basically see this traffic pattern for different channel groupings. It's possible that some of these things are all also readily available inside Google Analytics. But the nice thing here is you have full control, right? If you want to leave out some of this data, you can. Right. There's also this AI feature. I'm not sure if you know about it in workspace. It's super useful. Like basically this code long. I managed to prepare it in record time because of the help of AI. A lot of the code written here was like first written by the AI and then reviewed by me. Right. So definitely also use the AI if you want to, but actually as a. As a way of testing this. Let me see if the AI would have figured this out. Right. So I go back to what I had. I'm going to say like this. I'm going to do Q again and I'm going to click on the AI. I'm going to say adjust the code below to show the hourly visit throughout the day per channel grouping. All right, so it's saying per hour. That's not entirely correct. But then the other stuff I think it got right. So it's channel grouping like this. And then here I had to do. It's not entirely. It should be two periods, right? Two period H so that it didn't get entirely right. But if I run this. Ah, no, doesn't work. Dang. All right, that's bad publicity for the AI inside workspace. No, but the AI can be wrong in subtle images, obvious ways. No, not serializable. DT2 period. Not sure what it did wrong then. To period age. That's the only thing that changed, I think anyways. But it got close. At least I can tell. We'll try it again in a second. Maybe the AI can help us better next time. Okay. Andreas Napset. I screwed something up. Pardon the language. I'm just quoting Andreas here. And the AI assistant worked for me. Brilliant. So that's great to hear. Right. The AI can be like. Can help you out in a lot of cases. Not always, but it's super, super helpful to get you going. All right, cool. So I was very focused on writing the code. Time to interpret what we see here. Right. We see visits per hour throughout the day. Again, we see clearly organic search because it's like a very big portion here. It's also like high up in the graphs. They're here. And we see a traffic pattern. We see it take a dip at 9am I'm not 100% sure why that. Maybe it's because of commute or something. But we don't see it anywhere else. We see more than others. We see that referrals are pretty strong during the day, but die off completely at night. And that then direct traffic takes a peak here at midnight. I'm not sure why that would be. That's basically people just visiting. Direct basically means people just fill in the name of the store in the address bar. Right. They don't ask it on Google. They don't click on a referral link. They basically just fill it into the address bar. Right. All right. Okay. Finally, I think we have some time for the extra challenge here. Wouldn't it be interesting? Like this is all talking about, okay, how many visits do we have does our website get? But, like, how many of these people are actually buying something on the web store? Right. How many transactions do we get as a result of these visits? Because that's what it's all about. Like, you can have a bunch of people visiting, but if nobody buys products on your website. Yeah. Then it's not the best situation. Right. So wouldn't it be interesting to figure out, like, what are the channels? And these channels are the ones that you see here, this channel grouping column. What are the channels that lead to the most transactions per session? Right. Channel grouping stats is already a piece of code that I provided where we basically do on a per channel grouping. Per channel grouping, we basically calculate transactions. So the total number of transactions that were done through that. And then visit ID is basically how many visits we had. And if we print that out, we can type channel grouping stats like this. Let's print this out. And what do we see? We see that affiliates had 52 visits, but then it's zero transactions. Perhaps direct traffic had 400 visits and eight transactions, whereas social had 213 visits but no transactions, right? So we see that there's a different pattern going on here, right? Like some are like maybe low in number of visits. For example organic search, a very good example, organic search 1346, that's the same as what the number you see here, but only six transactions. So even like a lot of people find it on Google, not a lot of people that day went on to buy something on the Internet. E commerce store. Then paid search 70 basically paid search is like an ad. If you look for something and then say like, yeah, this is like an advertisement, but only one transaction. So wouldn't it be interesting? Like you can say, oh, referral is clearly the winner. But it's only a fair comparison if you divide it by the number of visits, which is this Visit ID column. If you divide it by the number of visits that we had from this channel. Right, so what we can do basically is do a calculation here. What we basically need to do is a sort of like a transactions per. Like the average transactions per session here, which is basically dividing this column by this column, right? We do 0 divided by 52, 8 divided by 501 divided by 39 and then we know for every channel grouping, what's the average number of transactions that happens per visit. So that's what exactly what we'll do. We can do channel grouping stats and then we do transactions trans per session and then we can do channel grouping sets. Let's call this something differently. CPG or something CGS CGS Transfer session CGS Transactions divided by CGS Visit id. So we basically create a new column called transactions per session which is Transactions divided by Visit id. And if we run that and we then do cgs, what do we see? Indeed we see a new variable transaction per session, which is basically this divided by this. So what Pandas did is like a piecewise division. Great. And now all that's left for us to do is show this on a bar chart. So we already did PX line before. In this case I think a bar chart is better. So we can do PX bar. Then we pass CGS on the X axis. We don't put anything on the X axis. I think we can put channel grouping. Of course, we basically want to see these things on the X axis. The values of different channel grouping. And then as Y we put transper session. Great. And then we can say title is transactions per session. You don't have to set a title. Let's maybe start without setting the title. It's not necessary. To create the visualization. Okay, we see that referral by far has the most transaction per session. So if there's like an influencer or somebody online or there's like an article that's talking about like Google merchandising, it's like, hey, you should buy this, you can go here. That's basically the concept of like referral as a source. And that's by far the percentage wise the highest grossing channel. Whereas direct and or no, I think it was organic search, which was by far the biggest here. It actually has a very low conversion rate, so to say. Right, it has a very low conversion to people actually buying something on this web store. This is not great yet. I think we can do some sorting. Maybe we can ask the AI to say add a line of code that sorts cgs, The CGS data frame from low to high transfer session, see if the AI can sort this out for us. All right, great. So it basically suggests to add a line here, which is exactly what I want in this case, I think. So if we click accept and run. If you run this again we get a nice chart where basically it goes from low to high to affiliates. We had no conversions whatsoever. So no transactions on social, also no transactions. Organic search, even though it's like the, the biggest channel in terms of volume of visits, it's a very low grossing channel, so to say percentage wise, whereas referral, it's the second biggest here, but it's by far the highest grossing channel. So referrals are a great tactic for this web store. Of course if we had more data we would know how much would it cost for this referral to come in versus how much would it cost for this organic search to come in. And you can basically do a true return on investment calculation where you say, okay, these referrals, they actually cost a dollar for everyone that comes in, what's our margin on these sales, etc. Etc. But that's outside of the scope here. What we basically did here, we hooked up to a Google sheet super seamlessly. We did some imports, cleaned the data and that's basically like where we feel at home inside Python. And we can do all of these analyses inside Python. We explore the data with these visualizations. We create a pie chart even. Even though some people don't like those, but they have their value. We did an hourly visits visualization, we basically did spur channel grouping and at the end we basically looked at what are the highest grossing channel here. And we did all of that in Python even though the data was in Google Sheets if this Google sheets tomorrow updates to computer contain data on yesterday's E commerce data and it follows the same schema so it has the same columns. The only thing I need to do is come back here, click on run and basically update this analysis. I can even go into datacamp workspace here, click on Schedule run and basically say you know what? Run this notebook because I know this Google sheet is going to get new data every day. Run this notebook to run on a schedule, right? Have it run every morning at 9am because that's when there's no data inside the Google sheet. Have it run every week on Monday at 5pm have it run every weekend for example. Those kind of things you can also do inside workspace, right? And all of the this is making sure that you avoid copy pasting stuff between programs. You bring all of your workflows into a same into one notebook that's easily configurable, that you can schedule, that you can share with others, that you can run from top to bottom super easily that you can access from any device to basically like make your basically automate the tedious tasks. Avoid manual steps so that you have more time to do stuff that's actually. That actually needs a human to be in the loop rather than you have to set reminder to drag things from place to place and remembering to rerun stuff, et cetera, et cetera. Right, so that's what we're here about like automating workflows, right? That's what it's all about about in this case. I'm gonna pause there for a second. This concludes the session. We still have five minutes left. I'm happy to answer some more questions if there are. Rhys?

Yeah, there are indeed a lot of questions. So thank you for that Philip. That was brilliant. Okay, so we've got loads of questions. We've got five minutes left. We're probably going to run a couple of minutes over. If you have to jump then please do register and catch up with with the recording afterwards because it's gonna be an exciting Q A. So the next question is actually wonderful timing. This comes from Wadi saying I'm liking, I'm wanting to switch my career into data analytics. What do I need to do first? We have a webinar on how to start a career in data coming on Friday. I think Reece can post a link in the chat and please do go to datacamp.comwebinars to sign up future events. We've actually got a session tomorrow on data governance. Data governance is a big growth area. It sounds boring, but it's actually really, really incredibly important. If you want to, like, do AI properly, then you need really good quality data governance. So please do come along for that session On Thursday, we've got another session on building a data skills academy. So if anyone in the audience is in management or you want your boss to, to help get some data training for you, then please do get them to show up for that on Thursday. And then next Tuesday the code along is going to be on managing machine learning experiments. All right, so back to questions related to the. To this code along. So Andreas asks, can you talk through that function used to generate the visualizations? You had a function for creating the bar plots and the pipelines.

Here it is. It's a good question. So I scanned through this pretty quickly. But basically what I could have also done here, like let me comment this one out, but I could have also done is do sessions per group is like this. I could have done like this for Continents and then I could have done PX bar. And what I decided is like this. Actually, when I was preparing this, like, this was an awful amount of repetition, right? So what I did, I just turned this into a function that I could easily reuse. And if we walk through this function, what do we see? I basically define a function plot. Sessions per group takes three arguments. Df, which is a required argument, which is the data frame that you want to visualize the group, which is the group by which you want to group the number of. Like, by which you want to count the number of sessions. So that's. So in this case it's continents. So it's basically then how many visits did it have for every separate value of confidence. Then finally the VIS type, that's bar, which is like the default value. So if you don't specify anything in a function call like here, you're automatically creating a bar chart like here. If you specify explicitly pie, then you will create a pie chart. If you specify anything else, it will basically complain and say, okay, I've only implemented bar and pie. You can't ask for anything else. And what it's doing then is doing session perfect group on the group by. It's formatted a bit weirdly because of the zooming. So sessions per group is doing DF groupby. So you first group by group and then the dot size function is basically say, count how many records that I have for every separate value of group, right? So count how many records I have for the Africa group. Count how many records I have for the Oceania group. In this case, if you pass counter, that basically depends, right? That's a variable that you pass along and then you reset the index. That's a bit of a Python thing where if you do group by calculation you turned into an index, resetting the index basically turns it into a normal column, so to say. Feel free to ask AI for some explanation about that one. And then you can sort the value by sessions where you basically say sort it from low to high basically. And then if the visualization type is bar, I call a pxbar function with the right arguments. Put something on the x axis, put something on the Y axis, put something on the title. If the visualization type is pie, I do a pie chart with some different arguments because it needs different arguments. And then what you get out of it is this good looking plot that is easy for you to interpret, that immediately has the right titles, that does the right grouping without having to repeat your code, without having to repeat a bunch of steps for every visualization you want to make.

I think it's a really important takeaway that if you are writing the same kind of code over and over again with just small variations, then that's when you need to start making use of functions to avoid that repetition.

Excellent.

All right, Andrew asks, is workspace viable for business or production use?

Definitely. We are doing this right now on semi toy data. Even though it's like real e commerce data that you'd also see in the real world. Just so you know, like for example us internally, we use datacamp workspace to analyze how datacamp as a business is doing. Right. So we have connections, so I had plenty of databases here. You can connect databases to Google BigQuery, which is like data warehouse that we happen to use internally. But you also have support for Redshift, Snowflake, so all the common data warehouses, you can hook this up to your common company data source. You can start writing SQL queries, you can continue your analysis in Python. Workspace is definitely a fit for production use. Fit for use inside enterprises large and small.

Absolutely, yeah. I mean the whole, our whole company functions on it. All right, next question comes from Herano saying, is the data available publicly? I think, was there a link at the start of the work?

So there's a link at the start, there's a source link here. If you click on that, you'll basically see where I found this data with a deeper description of what this data is about. But this is indeed publicly available data. Excellent.

And on from Michael on, why do you have to use Reset index And.

Group by statement somebody's going there. Still tough one to explain, but I'll try. So let me quickly create a Python cell here. Let me close up this databases tab so we have some more room. We have like the session per group. I'll do session per continent. Give me a second. And that's basically like the group by statement we're doing here. Right? By sessions or by sessions. So if I run this, I'm not even going to going to do like this. What do we see? Let's first do it without Reset Index. So with these dots you can basically combine.

Yeah, I don't think I might start to stop the size.

Yeah, so basically stop at size if we do like this. What Pandas automatically does is it's turning it into a series and setting the indices of these new records. It's setting that to the different values for the group. And if you do Reset Index, this looks a bit odd. And this is also something that doesn't print out nicely. That's harder to visualize. If you then do Reset Index, you say name equals sessions, what you'll see happening. So basically this is already like the named index content. What you'll see happening if you do name equal Sessions. It's basically taking the index, it's putting it back into like a regular continent column and then a Sessions basically also take. And Sessions is basically the name for the numbers you had in there. And this is just in general like I think there's like people prefer different things. I tend to use Reset Index quite a lot because it's easier for me to reason about things. But Reset Index is basically turning a series with like indices into a data frame again where the content, where the continent column is just a column like any other column. Right. And then finally the sort values by Equal Sessions is basically just making sure that we sort this from low to high. So if you run this, you'll see that the lowest value is at the bottom and highest is at the top.

I'm right there with you. The fact that Pandas just shoves a lot of data into indexes, it makes things much harder to reasonably about. It's a horrible decision by the Punish creators. So yeah, you need to use Reset Index a lot if you want your code to be easy to read. All right, I actually have a question for you before we wrap up. So the data we pulled from the Google sheets was fairly nicely formatted. It had all data and columns. There were column headers, things like that. I know some people who create spreadsheets that are Abominations that they've got data scattered all over. What happens in that case? Can you just pull in arbitrary rectangles of data into workspace?

That's a great question, Richie. I think I can mess up this Google Sheet right now. I think I'm not affecting anybody. We can do like this. Let me just do all this right here. And suppose I have a table here. It's like name happy. It's like Richie is very happy, Philip is yes, but tired. For example. For example, it's like a very small data frame that we created here. How do I import this thing right here? Actually there is a way we can specify a specific array that we want to pull data from. We can do like E7 up to F9 and we can basically code the sim right here. So we can do J sessions and then we can use GSHEET syntax that we're used to. It was a G, G, E7 until F9. E7, F9. And if you do like this and if we run this, what do we see? Hopefully this works. What it does automatically is basically taking zooming in or specifically that little rectangle into your Google Sheets. Taking the first row of this rectangle as like the column names and taking the second and the third row as like actual records in your data frame. And that's like a way to. For you to say, okay, like there's a lot of mess that happened around this Google Sheet. There's like this small nugget of insights or this small nugget of data that I want to zoom in on a specific Google sheet. This is the way. Right. Reset Think is also going to. To share specific documentation to the workspace docs that is explaining like this in more detail, like ways to zoom in on specific parts of your Google Sheets. Wonderful.

And there are all sorts of not quite normal data types in Google Sheets. So you can have things like pivot tables and charts. Even as I ask these, I'm not even sure would you want to import those into workspace and is it possible?

We haven't even tried. I would say these are also. They're like data products on top of like data that's inside Google Sheets. But it's a good question. I should. That's a take home assignment for me. Let's.

Okay, maybe. Maybe a new feature coming soon. All right, wonderful with that. We are past time, so please do have a look at the top left. There's a QR code for signing up for Radar. If you've not signed up for our conference, the radar conference on March 21st. Please do so now. It's going to be a great day. And thank you once again, Philip. That was a really informative session. Very cool stuff. So easy to use. I love it.

Thank you.

Thank you, Rhys, for moderating. Thank you to everyone who asked a question. Thank you to everyone who showed up today. I appreciate you taking the time for yourself, showing up for these things. Hope to see you all again in future webinars. All right.