Digital Analytics Review — Week 6
Hello everyone! Today ends my sixth week at the Digital Analytics Minidegree thanks to CXL. During these days I learned more about the great potential that Excel, Spreadsheets and Google Data Studio have. Together with Professors Fred Pike and Michele Kiss I learned how to find actionable marketing insights using Excel and Google Sheets, and how to create impressive, time-saving reports with Data Studio. These days were definitely filled with a lot of information and I discovered how to process information to make better decisions.
Everyone thinks that we know Excel, but this dismissive assumption may be costing us a lot of growth opportunities and wholly avoidable headaches. In the first lesson, we looked at the sort and filter commands. At some point point in our life as an analyst or digital marketer, we are going to download some data. In this lesson, we downloaded data from Google Search Console. We did some initial analysis using the sort and the filter command. Google Search Console is a great tool if we are interested in the organic traffic being driven to our website. One of the things Google Search Console shows is queries which people actually search for on Google and then the pages for which our website showed up. Once it was installed, we opened up a new sheet and look under the add-ons menu.
We have to make a copy of the worksheet we just downloaded. We called one Original and the second one Working. That way, if we screw anything up, we’ll be able to go back to the original spreadsheet and look at the data.
In Lesson two, we looked at some variations of the SUM command, specifically at some variations that make it a bit more powerful.We talked about absolute versus relative cell references, a key concept to understand in spreadsheets. In this lesson, we looked at the SUM commands, and we saw that there are three different types of SUM commands. We also talked about using variables in formulas and some best practices around that. And finally, we spent a bit of time talking about absolute versus relative cell references, a key concept in Excel and Sheets.
There are three different SUM commands, the SUM, which will sum everything that we 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 wanted to know how many impressions were served up in the time period that we were looking at. We were 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 did is column G, all the impressions. If we wanted to format that a little bit, so went to my Home, format that with a comma and then remove the number of decimals.
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 we start creating a formula, it tells us what it’s looking for. So for this formula, we looked at the range first, and that’s column D, the country because we were going to be looking for USA, then the criteria, which is going to be USA.
In Lesson 3 and 4 we covered the Counta command, which counts the number of text items. CountIF, counts based on one condition, and CountIFS, counts based on multiple conditions. Then, we started looking at Vlookup. If pivot tables are one of the things that differentiate the pro from the amateur in Excel, then Vlookup also differentiates between professional and amateurs in Excel and Sheets. Also, we learned about Vlookup and we did it in Sheets, because there’s a replacement command for Excel that we covered in lesson 12. It’s a very powerful command.
We had four quarters worth of data with the page name, the page title, page views, page value, etc. In a previous lesson, we got the page names. And what looked for is a sheet that looks like this where we’re going to have the page name, the page title, the page views from each of the quarters, we’re going to do a sparkline, and then the page value from each of the quarters. The one thing that’s unique in this whole final spreadsheet that we were trying to get to is the page name, which we had there. And I realized this is really the URI. If we looked in quarter four, we were going to then look across and find the page title, the page views and the page value. That’s what the Vlookup command let us do. The process and start getting from this sheet to this sheet.
In the first video with Michele Kiss, we got an overview of the Data Studio interface and an introduction to the types of data we can use. We looked at Data Studio’s basic functions and settings and discuss a fundamental principle of data visualization that underpins the entire course. We learned Data sources, how to turn an explore into a report and report basics. Next we discovered the different charts available. We saw how to use various types of visualizations to convey distinct messages and how different chart types can work together to paint a more complete picture. We also learned how to best customize our charts to communicate our data well. In this lesson we covered Community visualizations, Multiple chart types together and additional chart settings.
This week’s Excel and Sheets for Marketers, and Google Data Studio chapters has definitely opened my eyes to better utilize these tools. Definitely, all that I have learned in these days will help me make good decisions in my organization and decipher what our users want to tell us through our website. Each of the sessions that I had with the teachers and the resources that they offered me at the end of the class helped me to complement the information and to recognize each of the new actions that we can do through Google Data Studio, Excel and Spreadsheets. Thank you very much CXL Institute for the opportunity to continue growing and being a better professional. Never stop learning!
