CXL WEEK SIX- DIGITAL ANALYTICS REVIEW

Finished week five with an interesting course in the CXL mini degree program.
The next course in this mini degree program was about Excel and Sheets for marketers.

Lesson one in Excel and Sheets for Marketers. We’re going to start right away by downloading some data from Google Search Console. It’s interesting I was at a virtual conference recently and several of the speakers talked about the great data that’s in Google Search Console but how bad the UI is and how you have to export the data to really be able to do anything with the data. So, we’re going to look at exporting the data and bring it down into Sheets and Excel. We’re going to use a Sheets add-on that I’m going to talk about, and this will be the beginning of our adventure downloading data into Excel and into Sheets, and then sorting and filtering it. Before we begin, I want to cover two things that are very important about the course. The first is, and you may already know this, but you can always adjust the speed of the video. Anytime the video is playing, if you go down to the gear and then go up to the clock, you can change the speed. And personally, I think I sound a lot smarter at 1.25X maybe even 1.5X.I got a real kick recently about something that a student posted on the Facebook CXL page.

The second thing I want to cover, is that we’ll be talking about Excel and Sheets in the videos obviously and typically the first half of a video will be about Excel. And you’ll see a little flag in the corner saying that we’re in the Excel portion of the class. When we moved to the Sheets portion, you’ll see a page that says Sheets and then when we go into that section, you’ll see Sheets in the bottom right hand corner. There are a few lessons that are just Excel or just Sheets, so that won’t show up then. But otherwise, if you’re trying to skim through a lesson quickly, you can tell at a glance whether you’re in the Excel or the Sheets portion of the class. Let’s dive right in. Lesson one in the Excel and Sheets for Marketer’s class. Starting off with some basics. In this lesson, we’ll look at the sort and filter commands. At some point point in your life as an analyst or digital marketer, you’re going to want to download some data. Luckily, most tools have some way to do this. In this lesson, we’re going to download data from Google Search Console or GSC. Then, once we’ve downloaded that data, we’re going to do some initial analyse is using the sort and the filter command. Google Search Console is a great tool if you’re interested in the organic traffic being driven to your website but of course you already know that. One of the things GSC shows, is a queries which people actually search for on Google and then the pages for which your website showed up.

For Lessons two and three, we are going to continue with the Google Search Console or GSC data that we downloaded in Lesson one. In Lesson two, we’re going to look at some variations of the SUM command. You’ve probably use the SUM command if you’ve been in any spreadsheet before, but we’ll look at some variations that make it a bit more powerful. We’ll also talk about absolute versus relative cell references, a key concept to understand in spreadsheets. Lesson two in Excel and Sheets for marketers. We continue in part one covering some of the basics in Excel and Sheets. For those of you who are just working in Sheets, I would go through the Excel portion because I’m going to go into great detail on some topics that I won’t really cover in the Sheets version. The commands in this lesson are identical between Excel and Sheets, so I’m primarily going to cover them in Excel and then run through them very quickly in Sheets. So I’ll use Sheets, people, please see through the Excel portion. In this lesson, we’re going to look at the SUM commands, and we’ll see that there are three different types of SUM commands. We’re also going to talk about using variables in formulas and some best practices around that. And finally, we’re going to spend a bit of time talking about absolute versus relative cell references, a key concept in Excel and Sheets. We’ll see that there are three different SUM commands, the SUM, which will sum everything that you tell it to, the SUMIF, which will sum based on one condition or the SUMIFS, which will sum based on multiple conditions. And this will all make sense as we work through our examples. We’ll be back in the spreadsheet that we worked within the prior lesson, which is the data from the Google Search Console. Last time, we looked at sort and filtering, this time, we’re going to look at summing. So let’s say we want to know how many impressions were served up in the time period that we’re looking at. We can do that fairly easily, let’s just specify what we’re going to be looking for. And we’re going to sum up the number of impressions. So to do that, we could either start typing or we could use the formula’s command and the AutoSum, which is a nice shortcut. And what we’re going to sum is column G, all the impressions. We’ll press Enter, and there are the number of impressions. Now I’d like to format that a little bit, so I’m going to go to my Home, format that with a comma and then remove the number of decimals. So, I’ll be formatting with a comma and no decimals. And since I’m going to be using this space quite a bit, I think I’ll actually format a larger area and click back into my cell. So what we have here is a total number of impressions and let’s actually add something that tells us that’s what the total is. Let’s say, we’re curious, how many of those impressions were served up in the U.S.? That is where we use the SUMIF command, where we get to sum based on one condition in this case, country being USA. We’ll start that command by typing SUMIF, left paren, and one of the great things about Excel is that once you start creating a formula, it tells you what it’s looking for. So for this formula, we’re going to be looking at the range first, and that’s column D, the country,’ cause we’re going to be looking for USA, then the criteria, which is going to be USA. And note that it’s not case sensitive, so you could do upper case, lower case, it doesn’t matter. And then finally, the sum range and that’s column G, our impressions. So we close that formula with a right paren and press enter, there we go.

We will continue in part two covering pivot tables. In this lesson, the Excel people and the Sheets people come back together. This is a short lesson. So the Sheets people should really go through the whole lesson, because there’s a portion of the explanation in the Excel section that won’t be repeated in the Sheets section. In this lesson, we’re going to learn how to create a calculated field in a pivot table. And more importantly, why we would want to create that. Let’s dive right in. We’re back in the spreadsheet we’ve been using with data from Google Search Console. And what we’ve done so far is set up a pivot table, looking at clicks and impressions. But we’ve not done anything with CTR yet, the clickthrough rate. Let’s set that up again really quickly. Let’s go ahead and add the click through rate, and format it as percentage as well. Almost immediately, we can tell that something is squirrely. For example, 27 clicks divided by 2,300 impressions, is not 126%.So what’s going on? Oh, we’re using the sum of the click through rate. Well, it shouldn’t be the sum. What should it be? Hmm, it’s not going to be the count. Is it going to be the average? Average doesn’t look right either. Going down the line a bit, I know that 12 divided by 49 is not 33%.It’s 24.5%.So what’s going on here? The best thing we can do is look at how the CTR is derived. So let’s break into a little spreadsheet, and show an example of what Google is doing in the Search Console. On Monday for a sample page, we had one click, one impression. So our click through rate is 100%.One divided by one. On Tuesday, we had one click and five impressions, so 20%.On Wednesday, one out of 10.So 10%, et cetera. If we add up the clicks and the impressions, we get real numbers. But we can’t do the same with the CTR. If we add that up, for example, we get 146%.That makes no sense whatsoever. If we take the average,(keys clacking)we get 24%, which is not six divided by 76.What we want actually is 7.9%.So we can’t sum this. We can’t average it. We have to recalculate what the actual average is across these six days. And that calculation is what we’ll have to do in the pivot table, as a calculated field. Going back to the pivot table, we are going to remove CTR, and we’re going to add a new field, a calculated field. The way we do that, let’s go up to Analyse, Field, Items, Calculated Field. Let’s call this CTR Calc. And now we are going to create the formula. Pretty much anything you can use in an Excel formula, you can use in the formula bar here. What we actually want, so clicks divided by impressions.

This lesson is just for Sheets and will be covering filters and slicers. There is no timeline in Sheets. In this lesson, we’re going to look at segmenting the data in your pivot table. If you’ve worked with GA before, or if you’ve worked with data sets before, you know how important it is to segment and dive down into the data itself. You don’t want to look just at the top level numbers, which can be important, you want to dive down deep and to look at the default channel group, for example, in GA or the source medium, and keep diving down and segmenting your data. It’s the same thing with a pivot table. You want to be able to segment, and we’re going to look at segmenting our pivot table through two different options. The first is through a filter and the second is through a very powerful and fairly new feature in Sheets called a slicer. So let’s dive into the data. We’re back in our normal spreadsheet, which we’ve been working with so far with information from Google search council. So let me quickly set up the pivot table. There we have the pivot table we’ve been using. Let’s start adding some filtering capabilities onto here to start segmenting. We do that by the filter commands down here. Let’s start with the device category. I’m going to clear everything and just look at the queries that came from mobile. So now I’m just looking at the mobile traffic and I don’t really know that, but I know that something has changed, for example, this went down to 1,068 from…2,321.So I know that something changed and I don’t really know exactly what, because the filter that I set shows up down here, but it doesn’t really tell me what it is. Let’s add one more filter, country.