Social media analytics are hard. That's one of the biggest problems with social media. Anything that makes them easier is awesome.
Anyone that has spent professional, quality time with Radian6, Sysomos, Crimson Hexagon, or any other social analytics platform knows how difficult it can be to manage the large volumes of data they quickly produce. Dealing with this social media data can become overwhelming, especially if you have your own company design standards or reports you need to produce rather than screenshotting or exporting the pre-canned reports they provide.
Microsoft Excel is commonly used to manage the raw social metrics data these tools export. I've spent over 7 years managing social media analytics and creating social applications for dozens of clients and Excel has been a critical tool for that work. One of the reasons that Excel is so powerful is because of its support for VBA macros which allow you to create your own functions and customize how your data is handled. This capability is critical when it comes to social media metrics and analysis because macros can be used to manipulate data in any way you like.
In this series of posts, I'll give several ways to use Excel macros to improve your social media reporting workflows, giving you more time for community management (your favorite, I know!)
But first things first: you need a place you can store these macros so you can use them in your daily social analytics activities.
In this post, I'll show you how to create a Personal Macro Workbook in Excel for Windows. Why do you need one? You could simply use the Macro Recorder or VBA Editor in Excel to create one-off macros but it's much more manageable to store them in a spreadsheet known as a Personal Macro Workbook ("PMW" for short.) This allows you to easily share your macros between any open Excel workbook on which the PMW is stored. This is much easier than copying macros between every workbook in which you'd like to use them. You can also create an Excel AddIn so that you can easily share it between machines and other people. I'll show you how in a future post.
I'm going to focus on Microsoft Excel for Windows in this post because that's what most people use - if you need instructions for the Mac, check this link.
Once you've opened your PMW you can see the code Excel added to it when you created your first macro. Here's how:
Where does your PMW live on your system? Typically it's automatically saved in your Windows user profile in the following location:
C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART
Feel free to verify it. Go ahead, I'll wait.
This file will open every time you open Excel and its contents will be shared across all open Workbooks. Note that only one copy is editable. Excel will complain if you try to open it multiple times and make all but one copy read-only if you try.
It's technically not possible to share this file between computers but in a future post I'll explain how to get around that. You can easily share it with others via email - that is if your email server doesn't block a macro-enabled spreadsheet attachment like the plague it could be. SharePoint is another option. You can copy macros between workbooks easily using the Visual Basic Editor but you shouldn't need to do that except in rare circumstances.
Since the PMW will open every time you open Excel it may get in your way. If this is the case simply re-hide it using the same steps we covered in Step Three above; however, choose "Hide" instead of "Unhide." You can always change the PMW's visibility when you need to edit the macros or data it contains.
In creating your own Personal Macro Workbook, you've taken the first step toward creating a powerful social media toolkit that you can share between all the spreadsheets you come across. In my next post, I'll give you some basic macros to make your life easier when dealing with exported data from Radian6, one of the most popular social media monitoring solutions on the market today.
Microsoft has a great tutorial on how to create a PMW that you can refer to for more information.
If you have any questions don't hesitate to ask! Like our Thought Labs Facebook Page or ask me whatever you like on Twitter at @cappypopp.