Slide 1:  

It’s been awesome to connect with some old colleagues and to be exposed to so many tips and tricks and amazing content.

I’ve been a bit all over the place in my career. I started in music full time, professionally. My band was one of the first bands to get one million downloads on the internet so I learned to really see the importance of digital marketing because that was something we were doing without even knowing it at the time, but really learned to enjoy it.

Even though I’m currently working in the data governance space with ObservePoint, Creativity and beauty are still two really important things that I find myself using and striving to understand. In order for data to be visualized into something insightful or beautiful, it needs to be organized and set up in a way that makes this dashboarding or visualization dynamic. The less time we spend manually updating data and dashboards—we’ve all been there before—the more time we can spend being creative or gleaning insights or acting on them.

Slide 2:

If we’re starting any kind of dashboard or visualization, it’s critical to think about what questions we’re trying to answer. What business objectives are we looking at? If we aren’t answering a business question or providing insight, there’s really no sense in putting a lot of effort into making a dashboard dynamic—or even making a dashboard. We want to make sure that this is very specific to a question that answers a need, drives toward a goal. A dashboard needs to be insightful and impactful. To create information that is insightful, it has to be relevant to the current period or task at hand. When we take a number of individual metrics and aggregate them into key performance indicators, KPIs, we want to do this so we elicit action.

Slide 3:

I’m going to use a very basic example. When you think of a car's dashboard, you see several KPIs. Let’s just take miles or kilometers by hour, and let’s say we had a goal to drive to a certain destination before the sun sets or even before we run out of gas. So let’s say the destination is 200 kilometers away. With simple math we know that if we went 200 kilometers, and it takes about 4 hours to do so, our average speed would have to be about 50 kilometers per hour. This is very simple and we almost do these thing automatically. But let’s say we knew that the sun was going to set in about four hours and that we had to maintain a speed of about 50 kilometers to get to the 200 kilometers.

These are just some of the things a dashboard does. If we were to measure our average speed and did all that math after we reached our destination, we very well may have gotten there after the sun sets because maybe we stopped to get a snack or we end up running out of gas because we’re not keeping an eye on how much gas we have in the tank and we run out of gas and we’re stranded. So it’s important to have KPIs that are tied to a specific timeframe so that we know if we’re on track to meet our goals. I know that’s very, very basic, but we do some of these things in our day to day lives. We need to aggregate multiple metrics and this allows us to know if we are on track to meet our goals.

Slide 4:

Let’s just dive right in and build something together. For the sake of this quick webinar, I’m going to use Google Sheets that gets Google Analytics data using the google sheets extension. This is a great way to start. Both of these tools are free and relatively easy to use, so we’ll go with that, but these principles can be applied to Excel, to Adobe Analytics using Report Builder, they really can do whatever you want as long as you’re following the basic principles. The first step that we’ll need to do is connect our data to a google sheet, and let me just demonstrate where that is and how to do that.

Demo Screen 1:

You’ll need to go to the Chrome extension for Google Analytics. I think I need to say that I need Google Sheets. Here’s the documentation, it’s pretty straightforward, but here’s the actual extension that you’ll need to add. I have it installed, and what this does is it allows us to—let’s kind of do a preview here.

Demo Screen 2:

Let’s give it a name, select an account. This is what I have currently tied to my Gmail login. You can say what metrics you’re after and what dimensions. I would create the report, and what this is going to do is create a separate sheet that has all of these different parameters. I could then edit it further. I could put segments, filters, I could sort descending or whatever I wanted to do. Then I could copy these, put it over here. What that’s going to do is create a new sheet for the data I’m after. If I go here and run reports, it should actually give me two different sheets. And I have a duplicate name, so it didn’t allow me to do that. So for this particular test suite on ObservePoint, we have this many new users for this date range that I have set. This is the basic mechanism for importing data. You get the results breakdown here. I’m going to go into more depth about how to make this more scalable and get different data. Again, I’ll be using test data, but let me pull that over from another window here.

Demo Screen 3:

Just as I mentioned earlier, I wanted to show a more robust example of what this report configuration sheet looks like and what you can do. We have different groups of data, or different time periods, that are set up in different reports. When you set it up this way, Google creates a sheet for each of these data sets so we’ve got last week, we’ve got previous week, monthly, and so on, so you could do whatever granularity you need. Then I just grabbed the metrics and definitions. This is using the API and there’s documentation on this or you can take what I’ve done, but it’s very easy to set-up. I’ve also got some filters there. What I’ve done here is created formulas using Google Sheets to create these time periods dynamic.

Again, that’s what I’m pointing towards is; everything that we’re doing, we want to make sure that we set it up once and don’t have to do it again. That’s what makes it dynamic. Here, we’ve got the start date of last week and then the end date of last week. Then I have the week before that. All I’m doing is keying off of that and subtracting seven. Then for monthly it’s a little bit more complicated—we’ve got the start of last month, regardless of what day it is currently, and then the end date of that.

Look at what I’m doing, leverage it, copy it, modify it—whatever you need to fit your scenario. I was after weekly data where I was comparing pervious versus last, just to see the change. I was doing the same for monthly and then I was even doing that for yearly. If you have that much historic data, it’s great to look back and just make sure that you are improving. I know that’s a lot of information, but all that we’re doing is setting up datasets so that the raw data is imported to Google Sheets.

Demo Screen 4:

The next step would be to run the reports. Here we are with the report configuration. You could do one simple one just to see how it behaves and then scale it out and make different columns for those different requests by changing the start and end date or different profiles. To do that, you would just go to your add-ons here and you would say, “Run Reports.” When you run a report, it creates a separate sheet—whatever this report’s name is, it makes a sheet—then it copies data from your analytics instance and it puts it into this sheet. We’ve got some raw test data here. Right now, it was the amount of days I had specified, so if this was a whole year, it would do 365 entries, if it was a month, it would do 30 or 31. Here we have a week, and what I can do is I can do all kinds of aggregation using pivot tables, and I’m going to show you an example of that too.

For the sake of this saw data here, I just wanted to show you what this output looks like. Now we’ll go over what to do next. Again, to recap, we’ve designed the report based on business requirements. So say we need a certain set of metrics or KPIs, I need to make sure I get those, I need to make sure the date ranges are dynamic, I need to see the output, and then the last step would be to put this into a position or a range that is scalable, and I’ll show you what that means right here.

Demo Screen 5:

What I’ve done, is I’ve built a bunch of pivot tables that aggregate information. As those data sets are refreshed, these pivot tables are going to refresh. I’m going to be graphing off of these. Again, this is all test data. I wish it were accurate in come cases and I’m glad it’s not accurate in other cases. What I’ve done is built these pivot tables.

Let’s take for example, revenue and conversion rate. Outside of this pivot table, I’ve done a bit of formatting to make it more friendly. I’m taking the month here and then I’m taking the year, and that’s just how I would read it. The results will return this way, and that’s what is so great about Google Sheets is you can edit and transform a lot of this stuff here to make it very, very digestible and discernable. We’ve got this information that I’m summarized from the pivot table, from the raw data, and then I’ve also got revenue information. Basically what this looks like is a graph like this. For each of these business requirements, I want to make sure I’ve got the right data and that I’ve got a nice, graphable range that, as I update, will not break.

Let’s look at another one here. Another option you can do is a KPI that shows change over time. This is like a weekly change, and what this is doing is it’s taking my pivot table here that’s Sessions. This is the previous week and then that’s the last week. Just doing some math, we can see that 46 thousand is an improvement over 43 thousand and that change is 8.21 percent. So that is one thing that I’m graphing.

In some other graphs here, I’m showing the makeup of mobile versus desktop. I’m pulling in all these months from my monthly data and this aggregates it, and then here I am adding mobile and tablet and making it just mobile. I wanted to consolidate those two into one category. And then desktop, I am just referencing whatever it is here and I’m finding a percentage of what the total is. This gives me a nice little pie chart to show at a high level where my session data is coming from.

The sky is the limit. I’ll definitely be showing some examples of cool visualizations I’ve used, but this pivot table is kind of the mad scientist laboratory. Now you don’t want to get it too out of control. Even though this looks pretty busy and noisy, it’s been planned out so that these pivot tables do not override each other and that, as I refresh this, it will scale.

The next thing I wanted to show you is an actual example of what a dashboard would look like. Once you get these smaller graphs going, and you know they’re dynamic, then you want to start putting them together into a page or a card.

Demo Screen 6:

Here is where we start to put everything together. What I’m going to do is show you that the date for this can be dynamic using a formula. And a lot of this stuff, the last week, those are the little…

Demo Screen 5:

…KPI things we’ve done here where we compare two datasets: previous versus last week. We get a little rollup number like that. I’m using conditional formatting to show the change; if it’s good it’s green, if it’s negative, it would be red.

Demo Screen 6:

What we want to do after we’ve set some high-level KPIs is we want to bring in some of those graphs we made. That was one of the first ones I showed you, and since this is all done in kind of a grid format, I’ve adjusted the column widths to be more of a square than they usually come. Then we can start bringing in our visualizations. As you build them out, you’ll have to play around with the spacing of everything. Basically this is where everything lives, and you can play with it and have fun. This is where the beauty and creativity comes in because we know what we want to accomplish. We know what business answers we want to address. I want to make sure that our revenue is increasing, our conversion rates are increasing as well. We can see here that mobile is lower and we could say, “How do we get that higher?” We can keep an eye on that. How can we drive people to mobile versus desktop? If that was something we wanted to do, then we would be able to look at that. Again, keeping my eyes on these kinds of things and making sure that we’re making positive progression.

I’m going to share another example of a different way you can lay things out. Let me pull that up really quick.

Demo Screen 7:

Very similarly, we have the high-level KPIs across the top. Some of these graphs are the same, dome are different, but I wanted you all to start to visualize all the amazing things you can do. Again this is Google Sheets, Google Analytics and both are free tools, but you can do anything as long as the dataset is dynamic. One thing I have not addressed yet as I keep saying these date ranges are dynamic, but one thing that is amazing about Google Sheets is that they have a scripting library. So one thing that I’ve done is that I’ve told this sheet to refresh my data sources every day, and this is all done without me having to do it. So not only do the graphs stay dynamic based on the date ranges—for example when I go to the next week, everything is going to switch to the right data range—but on top of that, it all refreshes every single day without me having to do anything.

Once you have this set up for your team, they can go and look at this. They’re not bugging you to say, “Hey can you refresh this?” So this is awesome. It’s so great when you can get something like this set up. This is very high-level, very fast-paced. I’d love to answer any questions you have, provide specific examples, and do follow-up workshops. It’s something that I really enjoy doing and I’ve seen how impactful it is in the organizations who switch from doing it in Excel and only doing it every month. Just like our example, looking back isn’t as insightful as knowing you’re on track every single day because then you can make adjustments to make sure you get there.

Just like the car, don’t get stranded before sundown. Don’t get stranded without gas. Make sure that your data and your dynamic dashboards are working for you. You don’t want to waste any time doing something that could be done automatically. I really appreciate your time for jumping over here. I know we have a lot of sessions. There have been so many, I missed some. I wanted to remind everyone that you can follow-up with the rest of the sessions. They’ll all be on demand, so you can go in and review anything you might have missed. You could review this session. Again, feel free to ask questions and good luck because this stuff is a lot of fun. Thanks again to ObservePoint for putting this on and to all the speakers for sharing their time and talents.