Sales and Marketing know the value of monthly reporting better than probably anyone in the organization. Regular reports let these departments tweak every aspect of their messaging based on what worked with their audience before, as well as share how successful their team has been at any time.
The same is true for Internal Comms. Yet, many internal communicators don’t regularly report on their comms, relying instead on annual surveys or other means of purely qualitative measurement.
When done right, monthly reporting on internal comms will help you answer important questions like:
- What internal links are getting clicked (or getting ignored)?
- How many employees are using mobile devices to access content?
- Is the employee social network useful?
- Are we sending too many emails to everyone?
- How is engagement trending over the year?
A monthly report on your department’s work may be all that you need to prove your impact in the organization. In this post we’re going to show you how to start reporting simply, without starting from scratch.
We’ve created a set of smart excel and PowerPoint templates that you can download right now and start reporting across your internal channels.
Our excel template covers email, intranet and social data inside the organization, and automatically updates our PowerPoint companion so you can easily share the data. Read on to learn how to use it to it’s full potential.
The monthly internal comms report in PowerPoint
Once you’ve downloaded and linked our templates, entered your data and refreshed the charts, you’ll have a ready-to-share PowerPoint presentation.
How to import your internal comms data into the excel template
When you first open our excel templates, you’ll notice some instructions, a legend, and five additional sheets at the bottom:
- Email Data
- Intranet Data
- Social Data
- Top Content
Don’t worry if these channel do not directly reflect what you use at your org, or what you’re able to get data on, as you can customize these if necessary.
This data can automatically update our linked PowerPoint presentation template. For this to happen, we need to first link the files. Once you have the report saved to the directory you want to store it in:
- Open the powerpoint template
- Make sure macros are enabled
- Navigate to View > Macros
- Click Macros and run UpdateLinks
- A dialog will open and where you can select the Excel file in directory you chose above to finish linking these documents
The monthly summary and dashboard
The summary sheet compiles data from the remaining sheets, and provides a dashboard for the month:
There’s no need to enter numbers here but your data in the remaining sheets will populate the graphs here to show you the most important metrics for each channel as well as a summary of Engagements by Channel and top content.
Working with your Internal Email Data
Email data is the first sheet in the excel template we can enter data in. We’ve formatted the sheet so that green cells indicate areas to enter your data, and grey/orange cells automatically perform calculations:
You can export these monthly metrics from most email platforms, and if you’re using Bananatag you can simply export the All Email Stats Report and paste the results right inside the green cells.
Once you’ve entered your data, you’ll see the graphs in Excel update. We will also need to update the corresponding graphs in the Powerpoint presentation with the new data.
To update the PowerPoint charts:
- Select the chart
- Click the Design Tab
- Click refresh data
We’ve included two charts here, one for total opens and clicks, and one which graphs the open and click rates for each month. Together these charts are useful for explaining how actions on email fluctuate over the month and what portion of the audience is engaging with email, respectively.
Working with your Intranet Data
The next tab in the Excel template is for your intranet data. Depending on your intranet vendor, the metrics provided will differ slightly, but most should provide at least the basic traffic metrics on this sheet for each month.
Also related: A free Internal Comms dashboard for Google Analytics
Basic Intranet Metrics can be updated by looking at a traffic sources report for your intranet. These metrics track how many visits each internal channel drove to the intranet that month.
Advanced Intranet Metrics are available using Google Analytics and can be used for reporting on the intranet’s usage and visitor behavior (i.e. desktop vs. mobile, visit duration, and the average number of pages viewed per session).
The primary view of the intranet that we’ve provided is intranet visits by source, as referenced in the PowerPoint presentation:
This single view allows you to see overall traffic to your intranet along with which channels brought employees to the intranet each month, allowing you to report on the effectiveness of those channels.
Reporting on Internal Social Networks
In the social data sheet you’ll find cells to record top-level metrics for any internal social network:
Similar to intranet data we have two sections, basic and advanced. You only need to enter data in top section and this is where you record Likes, Shares and Comments on your social network.
Note that as with the other sheets, the graphs are automatically updated in Excel and can be updated in PowerPoint as outlined above.
For any organization using an internal social network, we find the data in this sheet very valuable since social metrics show another angle of engagement, beyond passive interactions with comms.
Taking this idea further, we also know that different actions on social are worth more than others in terms of actual engagement.
Related: A weighted social score for your internal social content
Using the advanced Weighted Social Metrics section, you can assign weights to each social activity (for example, a like may be work 5x a visit, and a comment may be worth 2x a like). Note that only the like weight, share weight and comment weight cells need to be updated for this to happen.
We’ve included both basic and advanced charts in our Powerpoint report to let you choose the most appropriate view for your organization:
Reporting on your top internal content
Last but not least, the final sheet in our Excel template contains a view of top content for each month. This sheet can be duplicated for each month.
Using the same data sources we looked at for the email and intranet data tabs, here you can list the top viewed intranet articles and top clicked email links:
We’ve also added a column for notes on how each link was shared to help you track the various tactics you may use to share content.
Slides 16 and 17 in our PowerPoint template represent the data from this sheet in two formats for: a list view and a graph linked to the Excel template:
Let us know how you’re using this template in the comments below and on Twitter! We also welcome your feedback on this resource as well as any suggestions for expanding on the templates.