Weekly media coverage desk with share of voice tracking

Build me a media coverage desk that our comms and marketing team opens every week to see who is writing about us and about our competitors. It is a browsable surface, not a scheduled job: a person opens it, picks a date window, and works through the coverage.

The watchlist lives in Google Sheets, not in the app. On load, a handler reads a Watchlist tab with Google Sheets Get Values. Each row is a brand name plus a label saying whether it is ours or a competitor. Everything the desk searches for comes from that tab, so the team can add or drop a brand by editing the spreadsheet without touching the app.

The main view is a coverage grid built from Diffbot Knowledge Graph DQL Search against the article index, one row per article showing headline, publication, date, which watchlist brand it belongs to, and sentiment. A date window picker sits at the top of the page and drives every query and every view on it. Important Diffbot detail: DQL Search paginates with from and size, and size maxes out at 50 per request, so the handler must page through results until it has the whole window rather than assuming one call returns everything. The Knowledge Graph endpoint is kg.diffbot.com/kg/v3/dql and auth is a token query parameter, not a bearer header. Article records carry a sentiment score already, so sentiment does not need a separate service.

Give the window three views over the same underlying coverage. A coverage timeline showing article volume over the window, broken out by brand so a spike is attributable. A share of voice breakdown measuring each brand's share of total article count in the window. And a ranking of the publications covering the category most, so the team can see which outlets actually drive the conversation and who they should be pitching.

Clicking any article opens a reading pane that pulls the clean full text through Diffbot Extract Article, showing title, author, date, sentiment and body text so nobody has to fight a paywall or a wall of ads to see what was actually said. The pane should also carry a Log to sheet action that appends that article's row to a History tab with Google Sheets Append Values, so coverage accumulates over time rather than resetting each time the window moves.

Put a Draft the weekly recap button on the page that kicks off a background agent. The agent takes the current window's coverage, groups it into themes, notes where a competitor is winning the narrative, then creates a new document with Google Docs Create Document and writes the recap into it with Google Docs Batch Update Document. When the doc is ready it posts the link into the comms channel with Slack Send a Message. The finished recap also gets listed inside the app in a Recaps view, with its date window and a link to the doc, so the team can reread past weeks instead of hunting through Slack history.

Two rules to bake in. First, let the user mark any row as ours, competitor, or irrelevant, and persist that classification. Rows marked irrelevant stay hidden on later loads so the same false matches do not keep reappearing, with some way to review and restore hidden rows. The ours versus competitor labelling should feed the share of voice view rather than being decorative.

Second, filter out syndicated press release wire duplicates. One announcement picked up by twenty outlets must not distort share of voice. Detect near-identical headlines and body text clustered on the same day around one announcement, collapse them into a single canonical entry with a pickup count, and count that announcement once in share of voice and in the timeline. Let the user expand the entry to see which outlets carried it, since the pickup list is genuinely useful to a comms team even though it should not inflate the numbers.

Audience is a comms and marketing team opening this weekly, so favour a dense, scannable grid over a sparse dashboard, and make the date window the one control everything else hangs off.