SharePoint Media Database - Media Outlets list
SharePoint Media Database - Journalists list
PowerApps Media Database mobile app
Adding new Journalist in the PowerApp
Media Outlets list in the PowerApp
Filling in the publication's author in Press Clipping
Pregenerated ACME PR Report example
Bonus calculation module with target-based salary bonuses
Relations PowerBI Report - Essentials Tab
Relations PowerBI Report - Media Details Drillthrough Page
Relations PowerBI Report - Publications Summary Filtered View
URL Shortener Tool (browser version)
URL Shortener Outlook Compose Add-in
Social Media Updater Tool in action
One of the most basic tools for PR specialists is an address book with journalists' contact details. Working in a PR agency, we've noticed that beyond simple contact information, there was a good deal more data being gathered not only about journalists, but also the media outlets themselves. That information was often left unorganized and spread out across different locations or project files. A single media outlet or person would be represented in multiple entities, with outdated or incomplete information. Overly repetitive processes were left unchecked. And finally, a lot of potentially valuable insights were never made because all these chunks of data were kept separate with no relation to each other, making analytics extremely tedious.
Given our agency's technology focus and background in IT knowledge, we decided to design and create a custom system based on Office 365 to:
- Centralize media and data on journalists to keep it up to date and consistent
- Integrate features of external tools we've used to simplify our daily routine
- Automate the most repetitive processes like creating reports
- Create reliable analytical tools for a 360-degree view on data from every major process to evaluate results and discover opportunities
- Establish a mobile-friendly, fully cloud-based foundation to expand and constantly add value
what it does
The whole system combines a number of subsystems and tools:
Media Database (Sharepoint)
Hosts heavily customized Contacts list for Journalist and Media Outlets. The media list contains basic information like name, type, website or publisher, as well as internal rank (influence level), social media links and appropriate statistics (i.e. views for online media, audience for TV).
These lists serve as a single source of media and journalists entities throughout the rest of the system. Having a great two-way synchronization of the Journalists list with Outlook is key for press release distribution and convenient access.
A modern news section of the site will be utilized to communicate about updates to the system internally.
Disclaimer: The Journalists list contains fields used for distribution categories and email greeting personalization. These are used by a 3rd party Outlook add-on to filter recipients and personalize the distribution of press releases (that add-on is not a part of this submission).
The “Media database” PowerApp is the primary mean of accessing the lists from mobile devices. It enables us to view, filter and modify records as well as add new media and journalists quickly and conveniently, thanks to its smart purpose-built forms.
For convenient access in a desktop environment, the PowerApp is also embedded in a simple Outlook add-in.
Press Clipping files
Publications about a Client or project—a fundamental focus of PR work—are tracked in Excel workbooks called „Press Clipping”, sitting in SharePoint-hosted Client folders. These files are a crucial data source for analytics.
Our template simplifies the filling in and processing of the clippings thanks to advanced nested formulas, default fields and drop-down lists. Using PowerQuery, the files always have up-to-date lists of Media and Journalists from SharePoint for auto-fill. After pasting a link to an online publication or a Social Media post, the formulas automatically derive the Media name, type and rank.
Although the design is standardized, it still allows individual adjustments like Client-specific ranks or publication categories to be made.
Based on tracked publications, these workbooks also contain automatically generated periodic Reports and even a Bonus calculation module for performance-based bonuses.
Custom URL Shortener
Almost every press release or media alert contains a link to a press pack with pictures and other multimedia assets. Using OneDrive for Business as a repository, the sharing links you get are pretty long.
We've created a custom URL Shortener that works within the agency's domain (in our case, http://sarota.pl) and has many of the benefits of premium 3rd party shortening tools, like click history or link editing. A full website version offers full functionality, and we've also produced a light version of it as an Outlook Compose add-in that works perfectly within the e-mail authoring context.
The URL Shortener is secured by Azure Active Directory authentication for single sign-on and user-level permissions.
Social Media Updater
In Public Relations, having a great overview of the Social Media landscape is an absolute must. Using a combination of Microsoft Graph and Facebook, Twitter and YouTube APIs, we've created a simple and powerful tool that quickly fetches key metrics of the Media outlets' profiles on each platform and writes them directly to our SharePoint Media list.
These numbers are then used to determine the influence of channels, within the PowerBI report or in Press Clippings.
Specifically for Facebook pages, we also collect the page ID to enable Media detection inside Press Clippings from a complicated post link like this: https://www.facebook.com/permalink.php?story_fbid=847927648710356&id=122798287867108.
The updater app is also secured by Azure Active Directory authentication.
As SharePoint Alerts are difficult to customize, we decided to use Microsoft Flow for custom notification emails after a new Media or Journalist is added to the database. Using a shared Exchange mailbox allowed us to customize the look of the sender account. Random messages of praise and emojis are included!
A more sophisticated Flow is used to trigger a fetch of social media metrics for a newly added Media Outlet – complete with access token retrieval for authentication.
The data from the Media Database SharePoint Lists and Press Clippings all come together in a relational data model designed in PowerBI.
Our 11-page PowerBI Report is always up to date (thanks to scheduled refreshes). It provides a holistic look at all the data from every project hooked up to the model and ties together publications with media outlets' and journalists' details, enabling rich insights.
The results are grouped into tabs like: Agency Essentials, Summaries of Media, Journalists, Publications, and Social Media. Drillthrough pages for Media Outlet, Journalist and Client serve as detailed profiles, providing a great look at the relationship status and results on an individual level.
In a few clicks, users can see
- filtered publications from specific media types,
- whether or not they have enough high-quality publications in A-rank media,
- a comparison of performance in different quarters
- and which media outlet or specific person they should forge a closer relationship with.
How we built it
SharePoint Online – Hosts the Media and Journalists lists and Press Clipping Excel files. Modern Media list visually enhanced using JSON Column Formatter.
Outlook (desktop) – Synchronized SharePoint Journalist lists.
Outlook Add-ins (desktop, online) – Compose Add-In: URL Shortener (SSO). Read Add-in: Media Database PowerApp embed.
Microsoft Flow – Custom email notifications. Authenticated Flow triggering social media details fetch.
Excel – Press Clipping files: advanced, nested formulas, drop-down lists, data validation, tables.
Power Query – Press Clipping files: fetching data from SharePoint lists. Power BI Report: data clean-up and preparation, merging of press clippings.
Power BI – Relational data model. Report with quick measures, drillthrough pages, the latest visuals. Mobile-ready views.
PowerApps – Utilizing SharePoint connector. Browsing and editing Media & Journalists lists. Smart edit forms.
Laravel Framework (PHP) – Building Custom URL Shortener & Social Media Updater.
Guzzle – PHP HTTP Client used in Social Media Updater.
Microsoft Graph (beta) – reading & writing SharePoint Media Database list items.
Facebook, Twitter & YouTube APIs – for fetching page id, fan, follower and subscriber counts.
Azure Active Directory – Securing access to URL Shortener & Social Media Updater.
Semantic UI Frontend framework – styling the URL Shortener & Social Media Updater.
Challenges we ran into
We came up against three main challenges. First, we needed to synchronize the non-standard fields between Outlook and SharePoint (finding documentation on that is no easy task). Second, we needed to ensure data consistency in Press Clippings for reliable analytics. Finally, some of the PowerApps SharePoint connector limitations were also problematic.
We also had to take into account potential differences between projects which could influence our system's design—for example, client-specific columns in Excel, the contents of the automated reports and bonus targets.
Due to the nature of the project, preparing the system for this submission was also demanding. To avoid leaking any sensitive information, we opted to hook it up to fake generated data.
Accomplishments we're proud of
- We created a huge agency-wide PowerBI report with insights and visualizations that had not previously been possible. It's always up to date and simple to use.
- Our own URL Shortener with click history and editing, and AAD single sign-on and Add-in access.
- A simple and powerful social media data updater triggered by an authenticated Flow.
- Reliable auto-filling of media details to greatly shorten the clipping and reporting processes.
- Our own mobile PowerApp with smart forms.
Quickly adapting relevant new features and updates rolling out in O365 over the last couple of months felt good (PowerBI drillthrough and visuals, PowerApps & Flow improvements, SharePoint JSON Column Formatter).
What we've learned
The entire process of creating the system taught us a great deal. Some of the most significant and prospective topics include:
- Power Query to import data for processing in Analytics
- Advanced formulas in Excel
- Use of Microsoft Graph with SharePoint
- Authentication with Azure Active Directory
- Applying Microsoft Flow for e-mail customization, and working on variables and JSON
- Using the JSON column formatter in SharePoint
What's next for the project
We have a number of ideas for updating our system and providing solutions for specific PR activities. These include:
- For moderators: accepting new contacts in the database from actionable e-mail thanks to custom Flow (waiting for support for the SharePoint's Approval Status field)
- Dynamic drop-down list of journalists based on the Media name in Press Clipping files
- Importing publication clippings from 3rd party services (Media Monitoring providers)
- Custom add-ins in Excel to allow adding a new media outlet or a journalist straight from Press Clippings
- Integrating the contact database with delivery companies' address books
- Measuring the open rate of press release e-mails
- Using Power BI API to add dynamic, filtered charts about the author of an e-mail and his or her Media outlet inside an Outlook Add-in
- Standardizing event workbooks for reliable data on attendance and helpful contact details