Spartan Race Facebook posts - Microsoft Excel Tutorial

- [Instructor] All right, our next case study is a fun one and it's actually looking at Spartan Race Facebook data. So in this case we've got 393 rows of data, that actually shows Spartan Race Facebook posts from August through October 2016. Now we have some really interesting dimensions to work with here. We've got the page name, which is Spartan Race. The date of the posts. A copy of the posts. A link to the actual post itself. What type of post it was, whether it was a photo, a video, a status or an event. The hour that the post was posted, and the time of day. So afternoon, evening, morning, et cetera. And in terms of metrics, you've got your standard social media engagement metrics. Like shares, reactions, likes, and comments. So we'll cover a few different concepts in this case study. We'll do a quick review of show values as with percent of column. We'll go into calculated fields and then do some demos with slightly more advanced calculations using count columns in the raw data. So let's go ahead into Excel and dive right in. Alrighty, in the pivot table case study workbook, go ahead and find the Spartan Race Facebook Post tab. And with any of these metrics or cells selected, just press control A to grab all of that data. We're going to insert a pivot table on a new worksheet and let's name it Spartan Pivot. And I'm going to right click and just change that tab color to a nice light green. And now before we dive in, let's just pretend that we are the marketing analysts for Spartan Race and we've been tasked to better understand Spartan Race's Facebook performance and posting strategy. Now if that's the case, the metrics that I really care about in terms of KPIs here, are these engagement metrics like shares, reactions, likes, and comments. And since we have some really nice interesting dimensions to work with, what I'd like to know is how do those engagement metrics or engagement rates differ by post copy or post type, or when Spartan Race is posting certain things. So hopefully I can use pivot tables and pivot charts to extract some interesting insights out of this data and use those to help Spartan Race optimize its Facebook strategy. So with that, let's start pretty high level and simply use a count of summarization to get a sense of how Spartan Race's posts tend to be distributed in different ways. So I'm looking for a count. I can pull in any of these dimensions here. Let's use post copy, for instance. And when I drag it into values, since it's not a numerical measure, it will default to count of, which simply counts the rows. So 393, those are the total number of rows in my data set, which is exactly what I want. Keep in mind that I could pull in anything here. Date of posts, page, and I'd get the same count of metric because we're just counting rows here. Very simple. And now what I can do with this count is then pull in other fields into my row labels to break that count down. So let's start with post type. Event, link, photo status, or video. And what this tells me is the number of times each type of post appeared in our data set. So we can sort these to descending by the count. And see that video posts were the most common. There were 195 video specific posts in this sample. Followed by photo at 156, and then really not many link, event, or status posts at all. And one thing that we can do that's very simple, just to visualize this trend a little bit better is to add a pivot chart. Throw something like a part or donut in here as well, just to visualize these numbers that we're looking at in our table. And I'm just going to get rid of these field buttons. And I'm going to right click series, I want to make the hole a little bit smaller. Totally a personal preference, so up to you. Now let's change the title of this number of posts. Since remember, the count is just counting rows and rows represent individual posts. So essentially what we're looking at here are the number of posts that fall into each bucket of our row levels. So you can see here that video counts for just about half of the posts and photo makes up a large percentage. And then there really aren't many of these other post types in here. And now that I have this donut chart in place, I can just swap in different dimensions without having to rebuild my chart. So for instance, let's pull post type out and pull time of day in. And again, sort these descending by the count. When we do, we can see that more than half of the posts were posted in the evening, followed by late night and an afternoon. And really, not many posts in the morning at all. You can even drill down deeper than this and you can take time of day out and actually do hour of post. And sort those descending by the count. And here you can see that two, 3pm, kind of early afternoon is a pretty common time to post. And then I know it's a little bit tough to read with all of these labels, actually wouldn't recommend using donuts or pies when you have this many cuts for your data. But just to illustrate what I mean, you've got very low volume times at two, three, four, 7am, which makes perfect sense. So now that we know, kind of, the distribution of posts, we know when Spartan Race tends to post most often, and what types of posts tend to be most common. Now as an analyst, the next level of insight that I need to get to is how the engagement rates actually break down according to these different dimensions. So I really want to start with post types, so let's go ahead and pull our post data here and get rid of our donut chart. And we can just pull post type back into our row labels, kind of where we started. And now since we want to start looking at actual engagement, let's pull in some of those fields. Pull shares, reactions, likes, and comments. You'll notice that shares defaulted to a count, that's likely because there is at least one blank row in the column. So as long as I'm aware of it, it's not a big deal. We can just change the summarization mode to a sum. And then reactions, likes, and comments all evaluate to sum, so those are all set. Now let's just go ahead and change the number format to add a thousand separator in there. Really just to make it a bit more readable. You don't actually have to do this. But I kind of like to keep everything consistent and polished as much as I possibly can. So there we go. Now this is interesting. I've got kind of these four component metrics, but I really want one metric to give me total engagements. And since likes are a subset of reactions, there are really only three components here that I care about. Shares, the reactions, and the comments. So let's go ahead into tools, fields items and sets. Create a calculated field called total engagements. And the formula is as simple as taking the shares plus the reactions plus the comments. Whoops. Plus the comments. And there we go, we're good to go. So there's total engagements. And right off the bat we can see that photos, our second line here, tend to generate a ton of total engagements. Specifically, a lot of reactions and likes. Whereas videos also generate a lot of total engagements. They drive more shares and comments in general. So some interesting trends starting to emerge here already. But one thing to remember is that this is pure volume that we're looking at. We're looking at sums and it's aggregated across posts so it's not really a fair comparison without factoring in the actual number of posts that fall into each category, which we're capturing here in column B. So for example, because link posts only drove 9,000 total engagements, that doesn't necessarily mean they're lower performers than photos or videos because there are only 31 posts that fell into that category compared to 156 for photo and 195 for video. So what would be a more appropriate, more apples to apples measure of performance or relative engagement is engagements per post. So what that means is that we need a counting column that we can use in our calculated fields formula. Since we can't take our total engagement calculation and divide by the count of posts copy, that would be exactly what we'd want to do, but unfortunately we're limited only to using the sum of a given field. So we can't divide by the count of anything else. So we practice this a few times, should be kind of becoming second nature by now. So we can jump back into our raw data. Just insert a new column here. We'll call it number of posts. Since our data is at the post level. Set it equal to one. Fly it down, just make sure it went all the way to the bottom. Sweet. Head back to our pivot. And refresh. Now one thing to call out, you'll see that our total engagement column got all busted up. It's got that name error now. Sometimes this happens, sometimes it doesn't. When it does happen, it just means that it's thrown a reference error after you've refreshed the pivot. So we can go into our total engagement formula and see that it lost the reference to those fields. Probably because those columns shifted as I added my new number of posts column in the raw data. So not a big deal. We can just redefine this field. Just like we had before, as shares, plus reactions plus comments. So just a heads up, sometimes that happens, other times it doesn't. I'm not sure why it only happens sometimes, but it's an easy enough fix to make. So now that we have our number of posts column in here we can compare that against count of posts copied. And confirm that the sum does give us the same values as the count. So this sum field is the one that we can use in our calculation. And pull up count of post copy out. And now let's head back into fields, items, and sets. We're going to create a new calculated field called engagements per post. And that's simply going to equal that total engagement field that we had calculated, divided by that new column we just created, called number of posts. And press okay. Let's format this to a number. We don't really need any decimal points here. And now we have engagements per post. So that factors in the number of posts and it gives us the average amount of engagement for each of these buckets. So now when we sort, by that new column, engagements per post. Now we have kind of a more accurate, more realistic measure relative engagement. So for photo posts, the average post generates 945 engagements. Video, only 734. And then we actually do see the same kind of low volume posts, also under perform in terms of engagements per post. So that's an interesting finding, you know, and an insight that I might keep in mind. Which makes a lot of sense and it also helps to validate why we're seeing so much attention given to photo and video posts, because as we can see here, tend to generate a considerably higher amount of engagement than other post types. So now we can kind of continue this analysis by maybe pulling post type out and time of day in. And sorting time of day by engagements per post. Now we see that morning only had about 16 posts in the sample that were posted in the morning. But among those 16 engagements per post was really, really strong. 909, which is higher than the average for afternoon, late night, or evening. If we want to drill even deeper we can take post copy, drag that in as a secondary row label. Change our design report layout to outline. And now we can see which actual posts drove such strong performance. So these are the 16 posts that fell into that morning bucket. And if we sort these at the post level, descending by engagements per post, now we can see the right. I can get rid of my field list just to make it a little bit more visible. So now we're essentially ranking our posts by engagements per post, which at this level is exactly the same as total engagements. And we can se that this one post, which is a Spartan transformation post really outperformed against the others. Captured 2500 total engagements, which really helped to lift the overall average at the time of day level. So really interesting insight once you drill down to the actual post level. And then you could even go back into your field list. You could pull time of day out, so now we're looking at all of the individual posts in our sample, sorted by engagements per post. Just to see what kind of floats up to the very top of our list. So we've got this post copy that says we're not 100% positive that he's performing full pushup. Blah, blah, blah. If we actually bring in link as well, we can actually copy that link and paste it into a browser to see what post it was. In this case it was a polar bear doing burpees. So there you have it. That's our kickoff to the Spartan Race Facebook data case study. A lot of really interesting way to dig in to this data and we really only started to scratch the surface here. So go ahead, explore this data, play with it. Try the homework exercises and give me a shout if you have any questions.