Weekly share-of-voice tracker in Google Sheets

PromptCreate workflow

Build me a deterministic code workflow that logs a weekly share-of-voice snapshot for my brand and competitors into a Google Sheet so my marketing team has a long-running historical record.

Trigger: cron, every Monday at 6:00am in the workflow's local timezone.

Inputs I will configure: my Brandwatch project, the name of the existing query group in that project that holds my brand and competitors (for example "Brand vs Competitors"), the Google Sheet spreadsheet ID, and the tab name to append rows to.

Steps the workflow should run:

1. Compute the date range for the trailing 7 days. End date = the Sunday just past (the workflow runs Monday morning). Start date = 6 days before that. Track the end date as the week-ending date for the report.

2. Call Brandwatch List Query Groups for my project to find the configured query group by name and resolve its query group ID and the list of member queries (each with a name and ID, which I want to use as the brand name).

3. Call Brandwatch Get Chart Data with dimensions queryGroups and days, scoped to that query group ID and the 7-day range, to get mention volume per brand per day. Aggregate per brand to get total mentions for the week.

4. Call Brandwatch Get Chart Data again with dimensions queryGroups and sentiment, same query group and same range, to get the breakdown of positive, neutral, and negative mentions per brand.

5. For each brand in the query group, compute: mentions (total volume for the week), share-of-voice percent (this brand's mentions divided by the sum of mentions across all brands in the group, times 100), positive percent (positive mentions divided by total mentions, times 100), negative percent (negative mentions divided by total mentions, times 100), and net sentiment (positive percent minus negative percent). Round all percentages to one decimal place. If total mentions for a brand is zero, set the percentages to 0 instead of dividing by zero.

6. Call Google Sheets Append Values once with one row per brand, using USER_ENTERED so the date and numbers are stored natively. Columns in this order: week_ending_date, brand, mentions, share_of_voice_pct, positive_pct, negative_pct, net_sentiment.

No reasoning or LLM steps are needed; the math is fixed and the structure of the row is fixed. Keep the implementation as a small set of deterministic code nodes.

Error handling: if the named query group cannot be found, fail loudly with a clear message rather than appending a partial row. If Brandwatch returns 429, retry with exponential backoff. If the sheet append fails, the run should fail so I can re-run it without losing a week.

Output of the run: the appended rows and a short text summary listing each brand's mentions and share-of-voice percent for the week, so I can confirm the run looked right when I scan the run history.

Additional information

What does this prompt do?

Pulls last week's mention counts and sentiment for your brand and competitors from your social listening account.
Calculates each brand's share of voice, positive percentage, and net sentiment for the trailing seven days.
Appends one row per brand into a Google Sheet so you build a clean, long-running competitive history.
Runs every Monday at 6am without anyone having to remember to refresh a dashboard.

What do I need to use this?

A Brandwatch login that can read your competitive query group.
A Google account that can edit the spreadsheet you want to log results into.
The query group in Brandwatch that already contains your brand and competitors.
The Google Sheet you want the weekly numbers appended to.

How can I customize it?

Change the day or time the workflow runs, for example Friday afternoon for an end-of-week snapshot.
Point it at a different competitive set by swapping the query group name.
Add or remove columns in the sheet, like reach, engagement, or week number, to match how your team reports.

Frequently asked questions

Do I need to build the brand and competitor list every week?

No. You set up the Brand vs Competitors query group once in your social listening account, and the workflow reads it every Monday.

What does share of voice mean in this report?

It is each brand's mentions divided by the total mentions across every brand in the group for the past seven days, shown as a percentage.

Will old rows get overwritten?

No. The workflow appends new rows below your existing data, so you keep a full historical record week over week.

Can I send this to Slack or email instead of a spreadsheet?

You can, but the value of this prompt is the long-running record. Most teams keep the sheet as the source of truth and add a Slack summary as a separate workflow.

What if a brand had zero mentions last week?

It still gets a row with zero mentions and zero share of voice, so your trend lines stay clean.

Stop rebuilding your competitive dashboard every Monday.

Connect your social listening account and Google Sheets once, and Geni logs a clean weekly snapshot for you every Monday at 6am.

Explore more prompts Create workflow