My productivity hack: Magic Mind | The World’s First Productivity Drink Use my
code MREXCEL20 for 20% off #magicmind
Today, we take a look at analyzing YouTube stats year over year. Along the way, you will see:
Unpivoting KPI columns in Excel Power Query
Creating a pivot table in Excel
Using the SEQUENCE function
XLOOKUP in Excel
Charting in Zebra BI for Office
#productivitytips #productivityhacks #productivityatwork
#adhdmanagement #mondaymotivation #lifehack #inspiration #lifetips
Table of Contents
(0:00) Welcome
(0:10) Magic Mind Challenge
(0:35) Szilvia, Rob, and Matcha
(0:51) Magic Mind Ingredients
(1:49) How does it taste
(2:35) Analyze YouTube Stats
(3:10) Power Query
(3:40) Unpivot KPI columns
(4:10) Pivot Table
(4:55) SEQUENCE function
(5:40) XLOOKUP function
(6:22) Adjust formula by dragging
(6:41) Slicer Shape
(7:09) Pivot Table Lock Column Widths
(7:23) Hide pivot table by grouping
(7:57) Comments
(8:45) Alt+Enter for 2-line comment
(9:16) Zebra BI Charts for Office
(9:33) 4 Charts
(9:55) Explaining the chart
(10:13) Views up 130%
(10:35) Using Chart Comments
(10:54) Gypsy Stock Arrows
(11:21) Impact YouTube Shorts
(11:57) Love Zebra BI
(12:12) Magic Mind Coupon
(12:27) 14 Days Challenge
(12:48) Play us out, Nancy!
code MREXCEL20 for 20% off #magicmind
Today, we take a look at analyzing YouTube stats year over year. Along the way, you will see:
Unpivoting KPI columns in Excel Power Query
Creating a pivot table in Excel
Using the SEQUENCE function
XLOOKUP in Excel
Charting in Zebra BI for Office
#productivitytips #productivityhacks #productivityatwork
#adhdmanagement #mondaymotivation #lifehack #inspiration #lifetips
Table of Contents
(0:00) Welcome
(0:10) Magic Mind Challenge
(0:35) Szilvia, Rob, and Matcha
(0:51) Magic Mind Ingredients
(1:49) How does it taste
(2:35) Analyze YouTube Stats
(3:10) Power Query
(3:40) Unpivot KPI columns
(4:10) Pivot Table
(4:55) SEQUENCE function
(5:40) XLOOKUP function
(6:22) Adjust formula by dragging
(6:41) Slicer Shape
(7:09) Pivot Table Lock Column Widths
(7:23) Hide pivot table by grouping
(7:57) Comments
(8:45) Alt+Enter for 2-line comment
(9:16) Zebra BI Charts for Office
(9:33) 4 Charts
(9:55) Explaining the chart
(10:13) Views up 130%
(10:35) Using Chart Comments
(10:54) Gypsy Stock Arrows
(11:21) Impact YouTube Shorts
(11:57) Love Zebra BI
(12:12) Magic Mind Coupon
(12:27) 14 Days Challenge
(12:48) Play us out, Nancy!
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2526, a year-over-year change using Zebra BI.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
A strange email showed up about six weeks ago.
It's from a company that makes this energy drink called Magic Mind.
And they said, “Hey, it's designed specifically for people in the creative work industry”.
And they said, "If athletes have Gatorade, creators now have Creatorade".
I'm like, Oh, that sounds interesting. They said, "Do you want to try some?" And when I read the ingredients, the number one ingredient is a green tea called matcha.
I just had a flashback to when we were working on the book, MrExcel XL.
Szilvia was building her section of Excel-themed ****tails.
Rob Collie, for the PowerPivotini, the original version was going to have green matcha tea.
And I wasn't a fan back then.
Magic Mind is very open about what the ingredients are on their website.
They have a number of peer-reviewed studies about all of these and how they help to boost energy, reduce stress, reduce anxiety.
The person who created Magic Mind was an entrepreneur who was running a company.
He went to the doctor and the doctor said, "You have to cut out the caffeine".
And so he found that matcha and these ingredients was a great way to eliminate the caffeine, but still get the energy boost.
Reading their website, magicmind.co, talks about how with caffeine there's that post caffeine crash two to three hours afterwards and that the matcha will last longer.
These ingredients are nootropics.
Some people use them to improve memory, mental alertness, and concentration.
So it's an interesting concept, but the big question for me was how does it taste?
I was intrigued but skeptical.
They offered to send me a month's supply.
I said yes, then I said no.
I actually, by the time I said no, it was already on its way.
So I said, Okay, well let's try it.
All right, first thing, how does it taste?
That's the big question.
So it's this little energy shot every morning.
I keep it refrigerated.
I don't have breakfast till about halfway through my day, so I just shoot this down.
It doesn't taste that bad.
There's almost a little bit of sweetness in it.
And they say that after 15 minutes you'll start to experience this flow state.
I don't know.
And then it kind of builds after three or four days.
All right, so it's been 35 days that I've been taking Magic Mind and I'm trying to figure out some quantitative way I could measure this.
They say that it's a product that's great for creators in the creative economy.
So I went out to YouTube and I downloaded statistics from YouTube over the last 35 days.
And then I just coincidentally happened to have the exact same report from a year ago.
So we have September 20th going up for five weeks.
This is kind of how the data looks like when it comes down from YouTube.
It's not exactly how I want it.
So the first thing I'm going to do to analyze this is run it through Power Query.
My goal is to take these KPIs out here that are currently going across and to make them go down.
So we're going to use, on the Data tab, From Table or Range.
Okay, so let's see.
We have four columns over here on the left that I don't need.
I want to remove those columns.
Year and day, I'm going to keep.
This and this, I'm going to right click and remove the columns.
And then here are the five KPI columns that I need to go down the page.
So I select these two columns, and on the Transform tab I'm going to say Unpivot The Other Columns.
Beautiful.
I always call this column attribute.
I'm going to double-click and rename that to be KPI, like that.
And good, we can just Home, Close and Load.
Beautiful.
All right, now back in Excel.
I'm going to do a pivot table.
New worksheet is fine.
All right, good.
So we're going to put days down the left hand side, I have to drag them to the rows area since they're numeric.
Years I'm going to drag to the columns area.
So it's going across.
The value in the values area, and then KPI up in the filters area.
And so the idea here is that we can choose one of these KPIs and the report will show us last year and this year.
I don't need a grand total here, so I'll right click and remove the grand total.
Right click and remove the grand total.
All right, perfect.
Now, you see there were some days in those 35 days where I didn't do any videos at all.
So let's build a table that shows every single day, that way we have no gaps.
So I'll ask for the sequence of 35, that'll get me the 35 days.
And then the date, then we're going to use =DATE( of 2022, 9, comma 19.
This is day zero essentially.
I should be able to just do plus F5#.
Good.
Oh, that's funny - It formatted the date correctly on the first one, but not the others.
That's not a surprise, really.
The formatting never works in the rest of the cells of the array.
All right, so there is our list of dates.
I'll put date up here.
And then we want to show two things.
We want to show current year, CY, and prior year like that.
All right, so it's going to be an XLOOKUP.
=XLOOKUP( we're looking up this day number.
Now press F4, one, two, three times to lock it down to column F.
We're looking in this range right here.
I'll press F4.
And then for current year, we're going to use this range, the 2022 range, press F4.
If not found, we're going to put a zero.
Thanks, Rico, for that awesome idea.
And double click and copy that down.
All right, now when I copy this over to prior year, I'm going to have to adjust that formula a little bit.
You see that we're currently grabbing 2022.
I just grab the purple range and drag it over to the right to point to 2021.
Double click and copy that down.
All right, so now we have, let's see, a pivot table, and then a nice table that's filling in all the dates.
Go back to the pivot table, and I'm going to add a slicer.
Insert a slicer based on KPI.
Click okay.
I'm going to try and make this long and narrow.
So we'll go five columns on the slicer.
And kind of resize it like that.
See if we can get it to fit up at the top.
All right, awesome.
So now we can update the report like that.
You see that the pivot table is changing its width.
If we go into PivotTable Options and “Autofit Column Width On Update”, turn that off.
Now, it should just, yeah.
Oh, here's another cool trick.
This is from Tine, when I downloaded the Zebra BI templates, Tine does this a lot.
And I'm actually using a faster way to do this from Geert Delmulle.
I want to group these into columns.
So it's Alt+Shift+Right.
It asks, Group into rows or columns?
I'll say columns.
And that creates this beautiful collapsible thing that we can just very quickly hide the pivot table.
So thanks to Geert and to Tine for that awesome trick.
All right, so I've added a column here for comments.
I mean, these are manually added by me.
Just to kind of note what's happening.
We had a significant event this year with Hurricane Ian arriving on the 29th.
But I remember that I had a seminar that I was supposed to do for El Paso the day before.
And the winds were just howling so much, I had to postpone that.
So it was really the 28th and 29th were hurricane days here.
But it's not just the days of the hurricane, it's getting everything secured the days before, bringing things in, tying things down.
So we lost the two days before, two days of the hurricane, and then after the hurricane, you got to take everything back and put everything back the way it was.
So there's kind of six days there that were an issue.
And notice this comment here, I actually used Alt+Enter to create a two line comment.
Although I've turned off Wrap Text.
The current year was Hurricane Ian.
And in the previous year it was this horrendously long 11 minute video doing minute by minute stock arrows.
And then down here on the 13th day was a video where I was having a friendly argument with some of my MVP friends about the Subtotal command and why I love subtotals and why do they want to get rid of them?
Okay, so we're going to analyze this data.
Control shift down, control shift right, control backspace.
On the insert tab, we'll go to my add-ins and this add-in that I talked about in yesterday's video, Zebra BI Charts for Office.
Now remember, there's four different charts that we get here.
You can use the little arrows there to see the four charts.
But I like this first chart.
I'm glad they made this one.
The folks at Zebra BI made this one be the result.
We just kind of cover up our data there.
So now I got this great little dashboard where I can look at the five KPIs.
And the way that the chart works is, so last year in the 35 days of 2021, there were 11 videos produced.
And this year there were 26 videos produced, which is an astounding 136.4% increase.
We can look at things that aren't necessarily in my control.
It's actually in *your* control.
The number of views were up 130.4% and the number of comments up 44%.
The number of likes, again, that's kind of on you, but also on me.
If I create better content, I might get more likes.
Up 71%.
The one that really is interesting to me here is video length.
And this is where these comments that I put in are kind of helpful.
I got off to a great start and I was ahead.
But then we had these two days of hurricane prep.
The hurricane almost arrives.
And on the day that the hurricane arrives, last year, I had done a very long video for someone named Gypsy doing minute by minute stock arrows.
And it was like an 11 minute video.
So I fell behind there during the hurricane.
And then on the last day, In Defense of Subtotals video put me way behind.
And so you can see, I had a great start for the month, fell behind, and then slowly, slowly, slowly worked my way back up.
And at the end of the 35 days came up 22.7%.
How can it be that the videos were up 136% and the length was only up 22%?
Well, what's changed massively from last year, 2021 to 2022, this year YouTube is really pushing Shorts.
And they're encouraging small channels like my channel to start creating short videos.
So short videos that are less than 60 seconds, you can do several of them in a very short time.
I love this Zebra BI add-in.
Gives you great ways to visualize the data very easily and even being able to put in the comments so that way a year from now I can remember what was happening.
Those are great features.
All right, so there you go.
The Magic Mind Energy drink.
Check it out.
If you want to try it yourself, I'll put the link down in the YouTube description.
They even gave us a 20% off coupon to give it a try.
At least in my case, things seem to be up after I'm using it.
Since Magic Mind helps unleash creativity, they're doing a 14 Days of Magic challenge.
Between now and November 30th, 2022, post a creative Excel spreadsheet image to Instagram.
Tag me @MrExcelPics and #14DaysofMagic.
One winner will receive a three month subscription to Magic Mind, courtesy of Magicmind.co.
Well, hey, I want to thank you for stopping by.
Thanks to the Magic Mind folks, and to Zebra BI.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
A strange email showed up about six weeks ago.
It's from a company that makes this energy drink called Magic Mind.
And they said, “Hey, it's designed specifically for people in the creative work industry”.
And they said, "If athletes have Gatorade, creators now have Creatorade".
I'm like, Oh, that sounds interesting. They said, "Do you want to try some?" And when I read the ingredients, the number one ingredient is a green tea called matcha.
I just had a flashback to when we were working on the book, MrExcel XL.
Szilvia was building her section of Excel-themed ****tails.
Rob Collie, for the PowerPivotini, the original version was going to have green matcha tea.
And I wasn't a fan back then.
Magic Mind is very open about what the ingredients are on their website.
They have a number of peer-reviewed studies about all of these and how they help to boost energy, reduce stress, reduce anxiety.
The person who created Magic Mind was an entrepreneur who was running a company.
He went to the doctor and the doctor said, "You have to cut out the caffeine".
And so he found that matcha and these ingredients was a great way to eliminate the caffeine, but still get the energy boost.
Reading their website, magicmind.co, talks about how with caffeine there's that post caffeine crash two to three hours afterwards and that the matcha will last longer.
These ingredients are nootropics.
Some people use them to improve memory, mental alertness, and concentration.
So it's an interesting concept, but the big question for me was how does it taste?
I was intrigued but skeptical.
They offered to send me a month's supply.
I said yes, then I said no.
I actually, by the time I said no, it was already on its way.
So I said, Okay, well let's try it.
All right, first thing, how does it taste?
That's the big question.
So it's this little energy shot every morning.
I keep it refrigerated.
I don't have breakfast till about halfway through my day, so I just shoot this down.
It doesn't taste that bad.
There's almost a little bit of sweetness in it.
And they say that after 15 minutes you'll start to experience this flow state.
I don't know.
And then it kind of builds after three or four days.
All right, so it's been 35 days that I've been taking Magic Mind and I'm trying to figure out some quantitative way I could measure this.
They say that it's a product that's great for creators in the creative economy.
So I went out to YouTube and I downloaded statistics from YouTube over the last 35 days.
And then I just coincidentally happened to have the exact same report from a year ago.
So we have September 20th going up for five weeks.
This is kind of how the data looks like when it comes down from YouTube.
It's not exactly how I want it.
So the first thing I'm going to do to analyze this is run it through Power Query.
My goal is to take these KPIs out here that are currently going across and to make them go down.
So we're going to use, on the Data tab, From Table or Range.
Okay, so let's see.
We have four columns over here on the left that I don't need.
I want to remove those columns.
Year and day, I'm going to keep.
This and this, I'm going to right click and remove the columns.
And then here are the five KPI columns that I need to go down the page.
So I select these two columns, and on the Transform tab I'm going to say Unpivot The Other Columns.
Beautiful.
I always call this column attribute.
I'm going to double-click and rename that to be KPI, like that.
And good, we can just Home, Close and Load.
Beautiful.
All right, now back in Excel.
I'm going to do a pivot table.
New worksheet is fine.
All right, good.
So we're going to put days down the left hand side, I have to drag them to the rows area since they're numeric.
Years I'm going to drag to the columns area.
So it's going across.
The value in the values area, and then KPI up in the filters area.
And so the idea here is that we can choose one of these KPIs and the report will show us last year and this year.
I don't need a grand total here, so I'll right click and remove the grand total.
Right click and remove the grand total.
All right, perfect.
Now, you see there were some days in those 35 days where I didn't do any videos at all.
So let's build a table that shows every single day, that way we have no gaps.
So I'll ask for the sequence of 35, that'll get me the 35 days.
And then the date, then we're going to use =DATE( of 2022, 9, comma 19.
This is day zero essentially.
I should be able to just do plus F5#.
Good.
Oh, that's funny - It formatted the date correctly on the first one, but not the others.
That's not a surprise, really.
The formatting never works in the rest of the cells of the array.
All right, so there is our list of dates.
I'll put date up here.
And then we want to show two things.
We want to show current year, CY, and prior year like that.
All right, so it's going to be an XLOOKUP.
=XLOOKUP( we're looking up this day number.
Now press F4, one, two, three times to lock it down to column F.
We're looking in this range right here.
I'll press F4.
And then for current year, we're going to use this range, the 2022 range, press F4.
If not found, we're going to put a zero.
Thanks, Rico, for that awesome idea.
And double click and copy that down.
All right, now when I copy this over to prior year, I'm going to have to adjust that formula a little bit.
You see that we're currently grabbing 2022.
I just grab the purple range and drag it over to the right to point to 2021.
Double click and copy that down.
All right, so now we have, let's see, a pivot table, and then a nice table that's filling in all the dates.
Go back to the pivot table, and I'm going to add a slicer.
Insert a slicer based on KPI.
Click okay.
I'm going to try and make this long and narrow.
So we'll go five columns on the slicer.
And kind of resize it like that.
See if we can get it to fit up at the top.
All right, awesome.
So now we can update the report like that.
You see that the pivot table is changing its width.
If we go into PivotTable Options and “Autofit Column Width On Update”, turn that off.
Now, it should just, yeah.
Oh, here's another cool trick.
This is from Tine, when I downloaded the Zebra BI templates, Tine does this a lot.
And I'm actually using a faster way to do this from Geert Delmulle.
I want to group these into columns.
So it's Alt+Shift+Right.
It asks, Group into rows or columns?
I'll say columns.
And that creates this beautiful collapsible thing that we can just very quickly hide the pivot table.
So thanks to Geert and to Tine for that awesome trick.
All right, so I've added a column here for comments.
I mean, these are manually added by me.
Just to kind of note what's happening.
We had a significant event this year with Hurricane Ian arriving on the 29th.
But I remember that I had a seminar that I was supposed to do for El Paso the day before.
And the winds were just howling so much, I had to postpone that.
So it was really the 28th and 29th were hurricane days here.
But it's not just the days of the hurricane, it's getting everything secured the days before, bringing things in, tying things down.
So we lost the two days before, two days of the hurricane, and then after the hurricane, you got to take everything back and put everything back the way it was.
So there's kind of six days there that were an issue.
And notice this comment here, I actually used Alt+Enter to create a two line comment.
Although I've turned off Wrap Text.
The current year was Hurricane Ian.
And in the previous year it was this horrendously long 11 minute video doing minute by minute stock arrows.
And then down here on the 13th day was a video where I was having a friendly argument with some of my MVP friends about the Subtotal command and why I love subtotals and why do they want to get rid of them?
Okay, so we're going to analyze this data.
Control shift down, control shift right, control backspace.
On the insert tab, we'll go to my add-ins and this add-in that I talked about in yesterday's video, Zebra BI Charts for Office.
Now remember, there's four different charts that we get here.
You can use the little arrows there to see the four charts.
But I like this first chart.
I'm glad they made this one.
The folks at Zebra BI made this one be the result.
We just kind of cover up our data there.
So now I got this great little dashboard where I can look at the five KPIs.
And the way that the chart works is, so last year in the 35 days of 2021, there were 11 videos produced.
And this year there were 26 videos produced, which is an astounding 136.4% increase.
We can look at things that aren't necessarily in my control.
It's actually in *your* control.
The number of views were up 130.4% and the number of comments up 44%.
The number of likes, again, that's kind of on you, but also on me.
If I create better content, I might get more likes.
Up 71%.
The one that really is interesting to me here is video length.
And this is where these comments that I put in are kind of helpful.
I got off to a great start and I was ahead.
But then we had these two days of hurricane prep.
The hurricane almost arrives.
And on the day that the hurricane arrives, last year, I had done a very long video for someone named Gypsy doing minute by minute stock arrows.
And it was like an 11 minute video.
So I fell behind there during the hurricane.
And then on the last day, In Defense of Subtotals video put me way behind.
And so you can see, I had a great start for the month, fell behind, and then slowly, slowly, slowly worked my way back up.
And at the end of the 35 days came up 22.7%.
How can it be that the videos were up 136% and the length was only up 22%?
Well, what's changed massively from last year, 2021 to 2022, this year YouTube is really pushing Shorts.
And they're encouraging small channels like my channel to start creating short videos.
So short videos that are less than 60 seconds, you can do several of them in a very short time.
I love this Zebra BI add-in.
Gives you great ways to visualize the data very easily and even being able to put in the comments so that way a year from now I can remember what was happening.
Those are great features.
All right, so there you go.
The Magic Mind Energy drink.
Check it out.
If you want to try it yourself, I'll put the link down in the YouTube description.
They even gave us a 20% off coupon to give it a try.
At least in my case, things seem to be up after I'm using it.
Since Magic Mind helps unleash creativity, they're doing a 14 Days of Magic challenge.
Between now and November 30th, 2022, post a creative Excel spreadsheet image to Instagram.
Tag me @MrExcelPics and #14DaysofMagic.
One winner will receive a three month subscription to Magic Mind, courtesy of Magicmind.co.
Well, hey, I want to thank you for stopping by.
Thanks to the Magic Mind folks, and to Zebra BI.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
