Analyze your Amazon Video Direct account with this BI Interactive Dashboard | Paula Guilfoyle | Skillshare

Analyze your Amazon Video Direct account with this BI Interactive Dashboard

Paula Guilfoyle, CPA, Online Educators, Lifelong Learner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
9 Lessons (28m)
    • 1. Introduction - Interactive dashboard with Excel for you Amazon video direct account

      1:19
    • 2. Step 1

      2:01
    • 3. Step 2

      2:51
    • 4. Step 3

      7:32
    • 5. Step 4

      1:55
    • 6. Step 5

      7:58
    • 7. Step 6

      1:31
    • 8. Step 7

      2:00
    • 9. Conclusion

      1:13

About This Class

In this class you will learn how to create an interactive Business Intelligence Dashboard for your Amazon video direct account.

Amazon video direct have a dashboard, but lets face it, if you are a published on AVD then you really need to see what videos are going well and what videos are not preforming.  Its not easy to establish this with the Amazon dashboard.

Having this business intelligence on hand will allow you make decisions moving forward on what you should produce next and what you should stay clear off.

Transcripts

1. Introduction - Interactive dashboard with Excel for you Amazon video direct account: are you struggling with your Amazon video direct dashboard? Trying to figure out what videos are doing well, in which videos aren't having to go in all the time and search true your videos to see individually how each video is actually performing, changing between minutes streamed on unique streams, changing between the time periods? Well, I have a solution for you. How would you like to be able to view your data like this in an interactive dashboard, where you can just filter by a particular country with the click of your mouse button, where you can just filter by a particular video and see how it's formed month on month? Or where you can feel here by a particular month and see how this month has performed across all of your videos? This is really, really simple to dio on the tools that I'm going to show you how to do it in today. Our excel. You can do it in excel. Truth has been to 13 and you can do it in Excel 2000 and 16 on. It's only going to take you about 20 minutes, so I hope that you will hop into this class and you no longer struggle with trying to establish waffled user doom. Well, I'm watch videos earned 2. Step 1: The first step is to set up some folders on your computer. So I want you to set up a folder on your computer called Amazon or Amazon Foils. I'm would in that folder, you're going to have another folder called CSP. This is where you're gonna dump all your data from Amazon Video Direct. Then in Amazon Video Direct. You need to go to export monthly CSP, and you can export each and every one of the files and you'll see the files dropping down here. Now on. Most PC's thes files are going to drop down into your downloads file, so you'll need to then go to your downloads fire. Once you've downloaded all off the file to you condemned old all off the files CSP files. And once you have all of these files actually downloaded, all you need to do then is take the's from your download. So we go toward download folder and you'll find these in your damned old folder on. You can then coat them from your down holds folder, and you can put them into your Amazon folder that you have just set up in the folder named CSP, so you'll see Now, in my CSP folder, I have 2016 050 or 60708 So I have made to Augustana loaded. I haven't downloaded September on purpose because I want to show you what happens when you down a load. A new file laid around in this course. So now you have all of your files in your folder called Amazon files contained in another folder called CSP. And I will explain the reason for this again in a later part off this class. 3. Step 2: The second step is to open a New Excel workbook on in this workbook. You are going to set up a date table to start with. You will understand the relevance of this later on. So to set up your day table, the first thing we need to do is we need to type in some dates. So we put in some headers first, starting with date demo, going to put in a month and then we're going to put in a year. We're gonna put some formulas in here now in a second. But Amazon video direct started in Maine and not whole of you may have started uploading videos in May, but I'm going to start my dates in May. So the first off the fifth, 2000 and 16 and you can put your date in whatever format that relating to the country that you live in. I know that in Europe, the day goes before the month and in the States, the month goes before the day, so it doesn't really matter once you have your format set up on, then I'm gonna do the 2nd May 2000 and 16 and then what? I'm gonna do is I'm gonna fill this down when you see what I'm feeling it down the date below. You can see how far you're going so you can feel it down as long as you want. And I've gone down this fire as in November 2000 and 17 So I've brought it up for quite a long time. The next thing I'm gonna do is I'm gonna calculate the month from the date. So to do this, you say, equals month. Andi, open bracket put in, select the cell that has the date in it. Close the bracket on hit. Enter on, then you're also going to put up with the year. So you're going to say year, you're going to select the date and you're gonna close the bracket. So this is now for the day to pull data month on, and the year I'm going to then double click to feel that all the way down and that will feel all the way down. True. My dates. Now, if I wanted to make this longer, I can just drag and drag it all the way down. Now it's gone down to April 2000 and 19. The next part off this step is to convert this into a table. So to convert this into a table, you press control anti on. You get this little dialog box, create table on you want to take my table has headers because we set up these headers appear earlier on and then press OK, now in the design ribbon, you want to rename this table and you want to rename a to dig table. So now we have a table named de table. I'm I will come back to this table later on, but we're going to rename the sheet as well to date on. That's the second step complete in creating your interactive dashboard. 4. Step 3: the third step in setting appear Interactive dashboard is to go to power quickly. And if you haven't got power query installed and you don't know how to use power Query I have a class on skill share. Exelby, I para query one on in this class. It's a relatively new class. Eichel true in detail, getting set up with para quarry. So I suggest that you hop into that course on you have a look at how to set or para curry only taking about six minutes to watch that particular part off the lesson. So go to para query and you're going to get data from a file. And this time you're going to import from a folder. I remember we sat open a folder early around that waas within an Amazon folder called CFE, and you need the path for this folder and you can browse for this path. But I actually have it handy, so I'm just going to paste it in and select. Okay, So what happens then is you get this table of doubt and letting you know that there is CSP files The date they were accessed on the name of the files and what you want to do is you want to add it this now What you're doing now is you are getting the diet on your actually transforming the daughter and from this table that we have imported all Waleed is this finery content? We don't need anything else. So if we highlight this column, go to remove columns and then select Remove other columns and we'll see this applied step comes appear removed with her column. So if you make a mistake, you can accept that on. It'll bring back up the step that you did. So remove columns, remove all the columns then what we want to do is we want to extend the data from this. So this little dolar arrow click on this on What this has done now is it has extended the data. So it's brought in all the data from them. See, SV files. But it was a number of files in that folder on each fire has a header. So we need to remove the headers from this particular table of data you see here this is where a new file started. So if we go to title name on entitle name we want you only click this title name and select . Okay, so that has now removed any role that has title names over the removed revote roles that don't actually have a title name. We don't really need the title I d. So I'm going to remove the title i d column. And if you don't have seasons or syriza's, you can remove them to. But I do have some, so I'm going to keep the mean, But I'm going to remove the session I d on. I'm gonna remove the Siri's I D. So I pressed Control to Click Botham, and I'm going to remove these columns, not remove or their columns. Now I have a date. I have a title name. I have a season name. If we're looking at seasons and I have the series name for Syriza's would in seasons, we also then have the offer type on this to offer typed this video prime orders, Amazon video direct, wished adds, and I'm gonna leave both of them in there. What we have after this, then, is we have all the tables of data we've got. The minute streamed in Germany, the minute streamed in Japan the minute streamed in UK the minutes dreamed in the US And then it goes into the unique streams as well for each particular country, we need to own pivot this data. So I'm gonna show you what I mean by, um, pivoting this data. So if we select all of the's millet streams on unique stream columns, we're going to select all of the's on. Then what we're going to do is we're gonna go to Transformed Data. I'm are going to go to one pivot column and instead, now off having the minutes streamed going across the columns, it's now going down the roles relating toothy X to the actual title name. After this, what we need to do is we need to split this particular column on. We need to replace some data would in this column as well. So where we have d e? We're gonna highlight the column on replaced values. So you want to value to find you're gonna put in dash space, D e. And you're going to replace this wit Germany and select Okay, you're then going to do the same for Japan. So dash dot jp and you're going to replace this wish. Japan, You're going to do the same for Dash Space UK Unjust. Replaced this with the UK on your going to do the same again. Us. I'm gonna replace this with USA on. This is important so that we can identify the country's for the actual mapping. But the problem that we have now is is that we have minutes streamed on the country together or we have unique streams on the country together. So what we want to do is separate out the country. To do this, all you need to do is on the home ribbon, Say spirit column and you're gonna split it by a kilometer and you're going to split it by a space and you're going to take the left most species and say, OK, now we have our country actually separated. Let's put some hatters onto this. No. So let's rename this column. So in transform, weaken, say, rename on this, we can say Miller underscore views. This column here we can rename to country, and we can leave the Value column as it is. So here's all of the steps that we have done on If we make a mistake, If you have made a mistake you can expect Continue to expect until you get it right and re do the steps until they're actually correct. So now we have got our data on. We have transformed our data, so we're going to know clothes on and load. Now there's an option close and low, too. And you could created connection. Only you can load it to a table. We're going to load it to a table. We're not only going to create a connection, but if you have huge files, you could be looking at putting this into power. Pivot instead, on which case you would create a connection. Only we're going to create a table so you can actually see the data. I'm going to put it into a new worksheet on. We're going to say load. So now we have a new work chic. It's called she to with our new table of data. And we also have our workbook queries over here so we can see the query that we actually used to get this table a data and we can go in and we can re amend the query from there. So that is the power query end on the last part of this step 5. Step 4: the fourth step is the power of you. Worksheet and Power view is another ad on, so you may need to download and activate the Arden. So if you go to Microsoft, you can easily downloaded. It's available in 2000 and 16 would out the date download on its available in 2000 and 13. But you have to down an older, and then you have to turn it on. So once you've downloaded it to turn it on, go to file and then go to options and in options, go to Adan's On in Adan's Go to Com Adan's Press. Go on in here, you'll need to take power of you. You'll also need to make sure that you have power pivot as well turn done, because power pivot para query on a pair of you all work hand in hand. When you have that on, you can then go to insert on. You'll see this little power of you body on. Once you hit this power view, booking a new sheet is going to appear called your power view sheet, and I aren't going to close the filters, and I'm just going to look at all for the moment and I'm gonna delete this whole table of data outta here cause we don't need it for the moment. So in power view, we have a data at Relationships Field. What we need to do is we need to set up a relationship between the date table on the data that we downloaded. So if we go to new on, if we go to our day table on inner day table, you want to select the date column and in our related table, we which is the C S V table. We also want to select the date column. So now we have created a relationship between them on in RCs V filed the dates that we have . If we have a look at the dates, they are individual dates. But because we set up a relationship, we can now the tie these dates into particular months. 6. Step 5: we now have everything set up. I'm ready to go. So what we can do now is start creating our interactive business intelligence dashboard. Let's drag some data in and let's put the title name into our table off data us and then we have a title name on Let's put a value in here as well. So let's drop the value in now. At the moment, this value is both millets, Andi views added Together, we're gonna change this to an actual matrix. We are also going to change this to a clustered column chart so we can now see that that the value by each particular title where we want to do now is we want to tile this or we want to filter this and we want to filter by either minutes or views. So I'm going to get you to pull minutes and views down into your tire by and you see these new tiles come appear. But I'm gonna change the title type to a tire flow, and I'm gonna have to drop down here to the bottom. Now all of this is contained within the box and I'm gonna make this box bigger and I'm gonna make this chart also a little bit bigger, and I'm going to add a title to this chart. And then I'm going to just make this whole box the same size as my actual page. Not gonna make this a little bit bigger as well, so it's more legible and we can click on any off the particular videos on it highlights that particular video. Now I'm doing this video by video because I have put in title name into the access. If you have a season or a Siri's, you could put these in here instead. But I have title name now. At the moment, I'm looking at the minutes streamed for each particular video. So for 18 awesome fax bed money, I've had 17 tens and minutes streamed between the month off May on the month off August. But I can click the tile here and look at the unique streams instead, and I can see that I've had 950 unique streams off this particular video. Let's go ahead and let make this a little bit Mawr Interactive and add a little bit more detail. I mentioned earlier that the tiles were contained with in this box and you could see this box in between these two lines here on anything that you put in between this box is going to be controlled or filtered by thes particular tiles. So let's take our country and put our country in Andi. Then let's take our value on drop our value in here as well. So now we have broken down our unique streams by country on by value. But now let's have a look at this bite map and we can change this to a map type. I'm gonna make this map a little bit bigger, Andi. I am going to then take the country on. I am going to drop the country down into the color. Now what happens if I select Japan? You will see that arm the graph above were still in unique dreams On the graph above. Only my Japan minute is actually highlighted for the particular video. So we see none off. This particular video has been looked at in Japan. If I wanted to just look at the U. S. A. You can see that the total views is still there. You can still see it, but the amount viewed by the USA is highlighted in north A lot stronger. Now, to clear this filter, you just click in the particular chart in the white area. You can do the same for the minutes streamed. We can just look at the U. S. A. Or we could just look at Germany and you can see a very little viewed by Germany. We could just look at the UK and we can click away there from it to own filter all off these particular areas. So this is the minute streamed or the unique streams since the beginning of time and all the time that we have in, Let's have a look now at how we can look at this in a little bit more detail. Let's put in some time so go back to all on inner day table. We want to pull in the month. We're going to change this month to a matrix so you can see the months we're gonna pull it down to the rose so you could see each months a month. Five. His name on 6 June 17 is July 8th is August, and we can put some values in here because we set up relationships early. Iran's weaken dropped the values and they're now on that we can see our unique streams pair a month for all of the countries. If we select Japan, we can see the unique streams for Japan by month. If we select the U. S. A. The unique streams by the U. S. A. If we select minutes dreamed, changes directly to minutes streamed. What interactive dashboards. Sometimes numbers don't work best on these interactive dashboards. So let's change this to a bar chart, and we change it to a Kloster bar chart, and we make this a little bit bigger. So now we can see the value by month. So what happens if we click July? Will? If we click July, we can see that all off the charts actually move jewels. Two shells July. But the remainder of the charts stay there so you can see that our July minutes are coming in just over 500 which is highlighted, and you can see that it says highlighted their 547 for that particular video. If we click August, we can see on this particular video we had a much greater view off our videos. We had 1000 over 1002 minutes streamed, and we can look at this in unique streams as well. 15 We can see that we very had. We had very little. And in fact, this video wasn't available until the very end off May. This video wasn't available at all. Thes tree videos were also not available, so it's quite easy to see different things that are going on. And then we can change it two minutes. Dreamed aunt to own filter the data. Just click away on the right part on the white part. Off the chart. Let's look at just removing some of the title so I click this chart appear. And if I go to layout untitled, I can remove the title from the chart. The same with this one. I can remove the title from this chart that I'm gonna leave in value by month. On that chart. I am also going to put in some data labels Frankel to my layout, and I put in some data labels on. We can add in the actual numbers for the particular column stacks to make them more visible on in power view. If we go to teams, we can also quite easily change the color off the bar chart on the charts that appear by selecting different teams that are available. We can change our farm size as well because we can't read the writing on that to 75% now That's coming in a little bit better that we can actually read the writing armed, the particular charts so dependent on the name of your particular video, we will see how much space that you have. 7. Step 6: If you're working with a number of syriza's or a number off seasons, you can also change. The charity can look at them in more detail. We're looking at this by title name, and I don't really have very many Syriza's Their seasons, in fact, of only one short one set up, But just to show you, for example, in our access we have title name. What I'm gonna do is I'm gonna drop in, uh, Siri's name, and I'm just gonna move our title name to underneath our series name. It's No, none of these here in this chair to have an actual Siri's. But this does now what I've done by adding two fields to the access. First the Siris on, then the title. If I double click armed the Siri's name, it brings a scene down to the next filter, which is the title name, and then we get to see each title individually. And then when you click on each one of these, the corresponding charts changes well and you can see that most of my views for this were in the UK on America. There wasn't money in Japan or Germany, the same with this particular video here. So if you're working with a Siri's, you can put the data into the access on. You can make it more interactive, so you can drill down further by adding more fields in So you could have the season name in here is well, we drop inthe e season name and then you could drill down and it's going to give you the Siri's name, and you could drill down again on it's going to give you the title name. 8. Step 7: This particular interactive dashboard is evergreen with the way that we pulled data from a file. Let me show you what I mean. Earlier, Iran, I told you that I only put in files. I only downloaded files to the month of August. Now let me put in September. Let's say September's finished on Let me drop in the September file. So it's dropped into that CSB folder that we spoke about earlier on. Now, if we go back to Power Query and we're going to go to our query on, we are going to refresh this data hit Refresh. I'm basically that have now gone in on its now pulled it in if we scroll down our September data. So if we go to our power view sheet on inner power view sheet, we also refresh this table. We can see that everything has now updated to include the month of September and we can see that I've had a very steady growth on views each month. And if I click on September, I can see which particular video in September will is there. But my may results are still there. My June results are still there on the dashboard just automatically updates. The reason update was will be cut was because we got data from a folder when you save this particular file. So I'm going to save this file. As in my Amazon files, I am not going to say this into the CSB folder. Only the CSP files can go in there. And that's why I said set up and Amazon files folder on. Then a folder would in that for CSP on. I'm gonna call this analysis where you can call a dashboard and you can call it whatever you want, but just put it in. The Amazon files not in the CSP folder today have been both saved in a place where they can be easily accessed where they could be easily used on. Like I said, it's evergreen. 9. Conclusion: so there you have it. That's how you build an interactive dashboard used in Para query and power view in Excel for your project project, I would like you to build an interactive dashboard. Andi in the Projects Fields. Drop in, take a screenshot on, drop it into the project sections and let everybody see the dashboard that you have created all off. These fields that you have available can be used would in your chart. You know you can't really do anything wrong. If you do something wrong, you can just restart. You can take the values back, play around with the data that you have play around with the bar chart, the column, charity or the charts that are available, the maps, the tiles that tart, slices the cards and have a look and see what you can actually do. As I mentioned earlier, I have a introductory course available on power. Bi, I hear on Skill Chair on. There's going to be a follow on. Siris, of course, is to that, so keep an eye out for them. If you liked this class, I really hope that you give it the thumbs up because it will help other students find this cast. Thank you by name