Shortcut Across the Data-Verse: Get Your Data to PowerPoint at Warp Speed

Transcript

Hi, welcome to Shortcut Across the Dataverse. Get your data to PowerPoint at warp speed. My name is Jennifer Dungen and I work for Torstar in Toronto, Ontario, and I’ve been with the company for about 18 years, starting as a web developer, moving into QA, and then finally taking over the web analytics. Throughout that time, I’ve worked with Adobe Analytics at every stage of my career. And in my current position, I also manage Google and Marfield Analytics, as well as previously Parsley. In addition to being passionate about analytics, I enjoy reading, playing video games, and DIY projects around the house. I’m also an avid crafter from crocheting and amiguromi to designing original and character-based Halloween costumes. Over the last few years, I’ve had the pleasure of being a five-time Adobe Analytics champion, the co-leader of the Gnome East Analytics User Group, and an Experience League Community Advisor. I encourage you all to join a user group or even multiple. There’s a lot of great content available, and at least the online and hybrid events should be recorded. So if you can’t make it to the live sessions, you can always watch it on YouTube. And if you’re struggling with a problem, have an idea that you think would be great for Adobe to add to the roadmap, or just want to be more active in the analytics community, I highly recommend coming to the Experience League forums. It’s a great way to get help or even just to build your own brand.

To all crew, this is the captain speaking. Today’s mission will take us across the galaxy to complete the delivery of a PowerPoint presentation to the executive sector. I know what you’re thinking. There’s a lot of obstacles and dangers in our way. However, we’re going to be circumventing these perils by making use of a hyperspace portal or wormhole. And if successful, this will open up a consistent and repeatable trade route.

We’ll be reviewing our manifest.

Then we’ll be loading our cargo bay. Next, we’ll ensure that everything is properly organized for the turnover. And if all goes as planned, we’ll complete the delivery. So all hands on deck, let’s go. As data analysts, most of us love working in Adobe’s workspaces. It’s easy to drag and drop, dig into the results and get up to date information. However, there are times when you’re going to get those requests from the executive team to provide them with a PowerPoint slide or slides for the regular weekly, monthly, quarterly, et cetera, stakeholder meetings. And this can be a time vacuum, depending on the complexity of the ask.

It can be tempting to just take screenshots of your workspace and slap them into the slides and ship it off. But if you really care about how the content aligns or not showing the little extras like edit icons and scroll bars, this can mean added time adjusting and modifying each screenshot to make sure everything looks neat and tidy. My example here is a simple one page slide. But if you have to do this for multiple slides and or multiple slides, multiple sites, the work really does start to add up.

Some of you may even remember a feature that Adobe was working on. They had a lab open to get feedback and it would export your visualizations to PowerPoint. It’s currently been taken down, presumably to integrate features based on the results of the lab. There are a few issues with this initial attempt, at least in my book. One of these is that it put one visualization per slide and that it was always going into a blank slide, meaning extra time was required for converting the content to combine visuals and placing them into your organization slide templates, which most of us do have to deal with. And sure, we could do the bare minimum, but generally the executives don’t wanna spend that extra time doing this work. They expect a finished product that they can add to their presentations. Whether you’re using the old legacy or new report builder, or even the CJA report builder, the techniques we’re going to cover today will apply to all versions.

If you haven’t upgraded to the new report builder yet, you can get it from the Microsoft store or app source. Now, some of us, you might be thinking that you’ll have an issue with your corporate security, which most of us has locked out of the Microsoft store, but give it a try anyway. For me, I can’t open the store app, but I was able to open this link and download the report builder directly.

Unlike the legacy plugin, the new report builder is compatible with Mac computers, but you still have to use the Microsoft store because it is an Excel plugin and Excel is a Microsoft tool. One final point that I should cover before we begin, if you wanna use report builder, you still need to make sure your account has been granted permissions to use it. Even if you’re a system admin, your account may not have the permissions applied. So just make sure you to confirm this in the admin console. So the first thing we’re going to do is to replicate our workspace report. And if you’re starting from scratch, then I do recommend building out a workspace so that you can compare the results and ensure that you’re pulling the values you expect. How you organize your Excel document is ultimately up to you. I’m going to show you my technique and explain why I do it this way, showing you some tips and tricks that I employ to make my life easier. First, I start by setting up multiple tabs, date config, raw data, and final report. For more complex reports, such as handling multiple sites or pulling in a lot of visualizations, I’ll create multiple raw data and final report tabs to suit my needs. Date config is something I always do. I use Excel’s date formulas to create all my from and to ranges.

And I’m sure a lot of you watching this are saying, but why? Adobe already provides these ranges out of the box. Well, there are two reasons. The first is so that if I need to rerun the report for a different set of ranges, I can do so quickly and easily without needing to modify all my report builder requests.

I simply type in the range that I want to use and use a simple if statement to use override value if it exists or the formula generated date range if there’s no override.

The second reason I’ll get to shortly, so stay tuned. Now, I don’t want to go into the weeds of Excel date formulas, but there’s a lot of resources available to help you build these out to suit your needs. So you shouldn’t have any issues modifying this part of your report.

Next, the raw data tab. This can look however you need it to look. I usually create a mix of report builder data requests and some calculations such as percent change and calculations to create the directional indicators.

I can then use Excel’s conditional formatting to color code my up and down change arrows.

Now, I can of course make these calculations on the final report tab, but sometimes I find it easier to keep the math all in one place.

You’ll notice that I’m using arrows directly on my formulas because these are actual ASCII characters and they work perfectly as is. You can also try using Excel’s icon sets, but I sometimes find those to be a little bit finicky. Let’s take a closer look at some of our report builder requests. My examples are using the new report builder. This is pretty easy to follow. I set the cell where I want my raw data to start flowing, then I set my date ranges using the final from and to date calculations.

Now, I build out my table, selecting the dimensions and metrics that I need. You’ll notice that with the new report builder, we can see a preview of what we’re building in real time, showing both the layout and how much space it’s going to take. So if we need to make adjustments as needed while we’re building it. Drag and drop the elements into the row, column and value blocks. Here, you’ll see that when I drag a metric into the value, the column is automatically populated with the metric headers.

I’ll do a final check to make sure that the rows and columns are laid out as expected and that they’re sorted accordingly. Next, I’ll click finish, allowing the request to connect to Adobe and replace the placeholders with real values. A final step would be to compare these values to your workspace report to ensure the data matches.

Everything looks good. So now let’s look at a slightly more complex example.

Here, I want to use segments to split my traffic by platform. You might be able to simply use the mobile device type dimension. However, since mobile device type applies to both web and app, and I want those separated, I’m going to take a different approach. First, I’ve pre-created a table with a row for each platform. Now, I’ll create a new request for Desktop Web. I don’t need any dimensions here, so I’m going to go straight to adding my metrics and then I’ll add my Desktop Web segment right into the segment block. Keep in mind that report builder segments are applied to the entire request. So I’ll have to create a separate request for each row of my table. Now that Desktop Web is complete, I’ll create a new request for Mobile Web. After I select the new data block location, everything looks good right now, since I need the exact same date range as my previous request. Unfortunately, at this time, there seems to be a slight bug with report builder. As soon as you click Next, an error about invalid dates will appear. You’ll just have to reselect the cells to fix it. It’s annoying, but easy enough to work around. Now, just build out the request the same way as before with the metrics in the same order and choosing the correct Mobile Web segment. In the preview, you’ll notice that the request is taking up two rows, one for the header and one for the data, which doesn’t exactly line up with the labels. And while you could build this out with the extra space allotted, I like to clean up and simplify my requests by hiding the unnecessary repetition.

Simply access the options menu, enter the three dots on the header object, and choose to hide them. This will update the preview, showing you that the request will now only return the data and nothing else. When you do this, making sure that you have the metrics ordered the same way for all requests is crucial, since you’re relying on the headers from the first request in the series.

You can use the same methodology to split the traffic up more, such as looking at your apps by phones and tablets, or maybe iOS and Android, or using this technique for other types of breakdowns. One of the best things about using this method is that you have more control over how the data is ordered. In Workspace, you can sort alphabetically by your segment names, or by ascending or descending order of one of your metrics. But with this, because you would have exact control over what row the data loads on, you can align it exactly as you need. I’ve made use of this to keep my data for web together, followed by my mobile app data.

As we’re entering hyperspace, let’s head to the observation lounge and look at some other cool features of Report Builder.

We have to be cognizant of metrics that are naturally stacking within our datasets, particularly metrics like unique visitors and visits. If we need to see both granular breakdowns of the data and aggregated totals, we need to make multiple requests to fulfill those needs. We can’t just use Excel to add up the rows of data.

While metrics like page views are fine, unique visitors and visits will be over-counted. While this does mean a little bit of extra work setting up our Report Builder reports, this is really the only way to properly replicate the totals that Workspace shows naturally. And if you’re trying to replicate a multi-tier breakdown, you’ll have to make totals for each level. There’s also additional filtering options available at both the dimension and metric levels. This does allow for more granular control to be applied that correlates specifically to the elements within the request. This includes simple filtering, such as determining the number of rows based on most popular logic, with the similar filtering options as is available in Workspace. The other option is to curate a specific list of items from your dimension, either by adding them one by one or using a pre-list determined from cells somewhere in your Excel doc.

One thing that the new Report Builder doesn’t yet seem to support is saving a curated list. In my own work, I have to deal with data from about 30 websites. And in order to ensure that my multiple requests work together and with my final visualizations, I have to make sure each site loads in a specific order. I used to use a save list to determine that order. Now I just use a predefined list in my configuration tab. Like dimensions, metrics also have filtering options available. This is presented as segments. So unlike the segment block in the main request builder, where the segment was applied to the entire request, you can apply a segment to a specific metric. This is akin to stacking a segment within your metrics in Workspace.

However, be careful, as currently there is no visual indicator that the metric is filtered in the report builder. And since these headers are part of the request, even if you change that text manually to show that this particular metric is constrained by a segment, the text will be wiped out the next time the data is refreshed. However, you could add labels to the row or columns outside of the data block to ensure you know what’s happening.

Now that we’ve collected all the data we need into Excel, the next step is to create our final results tab, graphing all the data into our final report slide or slides. And you’ll notice that I have a mix of graphs, styled tabular data, and conditional formatted characters. This is the most finicky part of the process, but luckily, once it’s done, you shouldn’t have to deal with it again.

The first thing you want to do is to force the cell background color to a solid, like white, but this will depend on your template. You may even want to match it to your PowerPoint slide. This will make sure that the Excel grid lines aren’t visible except where you actually need table grid lines.

You’ll notice that in my Excel tab, everything is quite large and exaggerated. This is to be expected, and the final sizing will all depend on how everything looks in PowerPoint. I’m sure that statement’s a bit confusing upfront, starting with my title, you’ll notice that this isn’t just a hard coded value. Remember back when I was talking about the benefits of using Excel date formulas over Adobe’s presets, and I mentioned a second reason that I was going to get to later? Well, this is it.

Using Excel’s formulas, I can concatenate the variable dates into my title so that I don’t have to update this manually every time. This uses the concat function along with the text function to convert the numerical data string into the readable date formats I want to use. Since my example is a weekly report, I’m going to use the from and to dates for the span that covers last week.

Now, let’s start by adding this to my PowerPoint template. First, you’ll notice that I have the cells merged to ensure that the text fits fully within the selected cells. This is important because when we bring this to PowerPoint, the overflow won’t be maintained. The real magic here is how we’re going to paste the content into PowerPoint.

You’ll copy the cells and graphs from Excel as normal, but if you just use a standard paste in PowerPoint, this won’t work as intended.

You need to access the paste menu and select paste special.

This will bring up a dialogue with lots of different options. Here, we want to make sure that we select paste link and choose the Microsoft Excel workspace object. This is going to create an object that updates in real time any changes that are made in Excel. Next, you can align the object where you want it in your slide.

You can, of course, resize the object here, but I do recommend against that. I make all sizing updates in Excel so that I can ensure that my content is sized by one another. This is particularly visible if you have multiple graphs and text objects, as the font sizes of the axes and legends scale when resized in PowerPoint, but they stay consistent if they’re resized back in Excel. You can test that your live link is working by making a change in Excel. You should see the object in PowerPoint update immediately. You can see here that I’m now adjusting the text and size of my title in Excel and seeing it reflect in PowerPoint.

I’ll continue to adjust all my elements this way until I reach my desired results.

And if you don’t see the updates, particularly in the case of graphs, you may have to force the links to update. Go to File, Info, and choose Edit Links to Files.

This will open a dialogue showing all of your linked objects. You can force these to update one at a time.

Unfortunately, there’s no bulk update, so you’ll have to do this for each item individually. But in most cases, if you open PowerPoint after all your Excel updates, you shouldn’t have to do this. This is more relevant when building your template.

Continue adding all your objects. These can be individual or groups of cells and all of your chart elements. Paste all of these using the special linked objects method. You’ll probably have to go back and forth between PowerPoint and Excel a few times until you have everything sized and aligned to your satisfaction. But once you’re done, you now have your finished live link template.

Before we have mission sign off, there are a few housekeeping details we have to address. First, live link PowerPoint are relative to the saved directories. So it’s unlikely that other people in your organization will have access to the Excel. Even if they do, they may not have report builder permissions to update the content themselves.

Now, you might be tempted to build all of this in a shared directory, but there are reasons not to do this because the second point is that everyone who opens the PowerPoint is going to be presented with this lovely warning message, which isn’t ideal and can scare people who are less technically inclined. Also, anyone who clicks to update links, who doesn’t have access to the Excel document, will basically wipe all the data. I keep my actual live link file only for myself. My process is open Excel, log into report builder and run all the requests and save it. Then I open my PowerPoint live link file and confirm it to update the links. Now, I save the current version as well as do a second save as. I have a reports folder for all the dated reports. I save this file as a copy into that folder with an appropriate date in the file name.

Now, in the PowerPoint info, I go into the edit links and I break all the links to Excel. This will lock the content to the current static version of the data.

I save the dated version again and then I distribute that static version of the report to the executive team. It’s a good idea to keep a backup of your live link file just in case you accidentally overwrite the live link version with your static version. It’s a lot easier to restore what you lost.

Using this method, I can decrease the cognitive load on you and your team from a repetitive copy and pasting as well as reduce manual mistakes. It’s hard to pinpoint the exact time that this will save. I can say that one of our weekly reports used to take my colleague most of Monday to build manually. And with this process, it was reduced to about 20 minutes. And most of that time was just waiting for the report builder request to update, which can happen in the background while you’re doing other work. Now, within a matter of minutes, we can produce standard PowerPoint slides, following our organization’s color schemes and template designs, no more fiddling with sizing and alignment, just a few clicks and we’re done. Thank you for your time. Any questions? A big thanks to you, Jennifer. As someone who lives in PowerPoint personally, this session was seriously so useful. Let’s open up that Q&A. We’ve seen some questions coming in, but would love to get some more. So pop your questions in the chat now. But we will start with this first one.

Will the report builder continue to import data over time? If you have set up your finish date, well, sorry, let me, if you have set up your finish date is past the day you’re working on it, i.e. create report today with a finish date of August 30th. All right, well, I’ve never actually tried it. I imagine it would work something similar to how Workspace does, where it will just come in with zeros for any date that’s in the future and there’s no data that exists. However, if you are running this manually and you have a date that’s, again, aligned to today or up to yesterday, the data is always gonna come in anyway, because you’re gonna be running it over and over and over again, either on a schedule or manually. So I don’t see if there’s any real need to put a future date, but I suppose, I mean, you could. I’ve never tried it. I’d give it a try. I mean, hey, I’d love to hear your experiences with it because we’re a community and we share from each other’s knowledge and we share crazy ideas. And I mean, I love crazy ideas. So try it, let me know how it works. I love it. Just let’s try it, let’s do it. Why not? Let’s see what happens. I love that. Okay, here’s another one. This is great. I agree. What prompted you to think that this could be done? Ah, yeah, well, there is a backstory and like everything, it comes from a point of great pain.

So many, many years ago, one of my juniors was doing a weekly report for our manager and unbeknownst to me, she was literally spending most of her Monday doing a report every week. And I mean, like every week and it was taking most of her time. And when I discovered this, I’m like, there’s gotta be an easier way. Like you can’t be spending most of your day trying to do a report every week. So essentially I said, well, I know that we can do stuff with Report Builder. Either way, if we can get the data at least automated in some fashion, it’s gonna save time. And then I started doing some research, like how can I do connectivity? How can I make this even better to try and take out all that manual work? And again, just through Googling and trying to figure out what could be done, I came across this live linking technique and I’m like, okay, that’s really interesting. Put it into a trial, seemed to work. So we ended up building out the entire report and essentially got ourselves down from spending most of a day to about half an hour. So definitely an improvement.

This is so cool. I love it. I love like the fact that you identified a problem and it was like, there’s gotta be something easier and better to do. And I think a lot of times, you know, we either just like, okay, like let’s just keep doing the same thing we’ve been doing, but you kind of like pause and was like, we gotta find a solution. And then you kept going until you found that solution. I think that’s incredible. Thank you. Okay.

Just to confirm, you would need to do this for every slide in a presentation, correct? That’s a good question.

Yes, yeah. So every slide that has that interconnected link, you would definitely have to do this. Of course, you know, you may have slides that aren’t being driven by the Adobe data. They could be static slides or other types of slides, slides that are getting manual updates, such as, you know, the comments, the commentary on what this data means. Those kinds of slides technically don’t have to be done. You could just go into the slide, modify the static content and be done. But yes, essentially every slide that is getting Adobe data has to have that live linking technique to ensure that you have that connectivity. So I’m not sure if that you were looking at the Adobe side or just any other side, because again, this data, these slides could be just copied the static versions into your executives bigger slide, something that they’ve got content from sales or content from marketing or like other stuff that isn’t being driven by the data. Yeah, no, I think that makes a lot of sense. But that’s helpful that you kind of explained that for folks.

Okay, here’s another one. Can you put in the all Excel elements, I think let me rephrase it. Can you put all of the Excel elements at once or do you have to do one at a time? You can absolutely do all in one depending on your layout. So keep in mind that with the elements and how Excel works with the very static grid, if you can’t get things to line up properly or things look a little janky, splitting them up is obviously going to make a big difference. But I’ve got tables of data, which is a set table and that I copy as an entire set of cells. And then each of my graphs, I choose those as individual graphs. But if you can build out your entire Excel in a format that you want, absolutely, you could probably grab the entire thing. Maybe not the graphs, graphs are kind of a separate element in Excel on its own, but give it a try. I mean, like I said before, experiment, make it your own. Do what works for you that makes your life the easiest.

Well said, well said. We’ve got a follow-up question.

So follow up to the question around needing to do this for every slide.

They said, would you need a new Excel workbook for each slide or could you keep it all in one workbook but different tabs? I’d love to kind of get your thoughts on that.

I have like the sample that I did was very, very simple, but I do have reports that have many different slides. I use one workbook. I just create multiple different raw data slide or tabs in my Excel workbook for each of the different elements that I’m doing. Even for my final results, I’ll have different tabs just to try and make things manageable. For instance, I work for a news organization. We have 30 different websites. We don’t typically break it out by each website, but we do break it out by website groups. So we’ve got kind of like our core website that’s kind of like our flagship that gets its own treatment. Then we’ve got our newspapers that publish on a daily basis and those get grouped together separately in their own tabs. And then we’ve got our community newspapers, which again get grouped. So break it out however you need it, but absolutely one workbook does everything, no problems at all.

Awesome, good to hear.

Here’s a question. Can you use the report builder scheduler with this? So I’ve never done this because again, you still need to do like open up the PowerPoint to get the links updated. However, that’s not to say you can’t use the scheduler. If you don’t wanna go in and run the report manually, you could absolutely schedule it to come into your inbox. Now you do have to be very careful because the links in PowerPoint link to a very specific folder and naming convention. So as long as you overwrite the file on your computer with the exact same name and the exact same folder structure, the scheduler should be absolutely fine to use. Awesome, okay. I was a little confused about the date configs tab. Can you explain a bit more about what you’re doing there? All right, yeah, so again, because I didn’t wanna take up a lot of time doing the Excel dates within the presentation, essentially I have all of my date formulas. So the things that I don’t wanna have to rewrite all the time that are basically a reflection of based on today’s date, this many weeks back, that many days before, et cetera. But of course, periodically, you are going to have to rerun the report.

And so essentially, I just make a way for me to create an override date where I can just manually put in a date range. And then I just simply say, okay, if I’ve got an override, use the override. If I don’t have an override, use my formula. So I don’t have to actually overwrite the formula in order to have it update all my requests. So it’s basically just a way of kind of making it a little bit simpler without destroying your date formulas.

That makes sense, yeah.

Okay, we’ve got, how large can things get before it gets too big to manage? I mean, this is a good question. Have you reached that point of chaos that things are too big that you cannot manage? Well, let’s see. I think my biggest report has about, its heyday probably had about 260 requests in it. And it was still doable. So that’s still manageable.

It does get harder to manage a big long list of requests. So I guess what’s manageable is relative to your level of management skills. And in fact, going back to kind of that previous question, asking if it has to be in the same workspace or a workbook, technically, if you want to break it into smaller chunks of workbook A, workbook B, workbook C, workbook D, that all drive one PowerPoint presentation, you can absolutely do that to alleviate your own organizational skills. I’d say find what works for you and what works efficiently for how things run.

That makes sense.

I just wanted to read this one. It’s not a question, but someone said, this is incredible in all caps. Didn’t know this existed. I also before seeing this did not know this existed. So I’m learning a ton of stuff from all of you. This is really cool. We’ve just got a few more minutes here. I’d love to kind of get your final thoughts, Jen, if that’s okay, of like how this experience has been.

Some of the takeaways in terms of like what did, what came from this whole project, what are some of the results, some of the feedback, other things, would just love to kind of get your last thoughts here. All right. Well, again, every time you try something new, you’re gonna build a knowledge base in your own head of what’s possible, what’s not possible, that drives future innovation. Some things are gonna fail, and that’s when you go back to the drawing board. You go, okay, how can I make this better? Or can I make it work? And other things, when it does work, all of a sudden you start finding new use cases for it. Oh, marketing team is getting a report every week that they’re trying to pull into Excel. Well, maybe I’ll build that out and automate it for them, make it easier for them, save them some time.

So honestly, everything that I do, whether it’s a failure or a success, I always add that to my repertoire of just things that I can learn from, whether it’s something that I use in the future or something that I pass on to somebody else to inspire them to try something new. And of course, I always love to hear from those people, so hopefully I’ll see many of you on Experience League, posting your new ways of doing this that will inspire me on how to do something even better. So it’s a community I love to share, I love to hear feedback, and I love to try and build something new with everyone. So it’s great all around. I love that. Yeah, no, you’ve got a lot of friends in the comments here. You’ll have to go back and read a lot of thank yous. A lot of this is incredible. Thank you for sharing. I’ll leave wise words from our friend, Jeff Bloomer, who mentioned, pain is the mother of invention, which I think just captures this all so perfectly. You are the inventor that came from the pain of seeing something and you made something possible, which is amazing.

Jen, you’re the best. This has been so fun chatting with you. Thank you for taking your time to share all of this with us. It was an incredible session and a really fun Q&A, so I appreciate your time. Thank you for having me, and I’m so glad that people responded again, it was a blast to do, as you could probably tell from my insane slides.

The slides were amazing, I will say. I love the theme spot on, so you’re the best. All right, thank you, Jen.