Daily competitor video performance log in Google Sheets

PromptCreate

Build a code workflow that maintains a rolling, day-by-day competitor video performance log in Google Sheets, so my marketing team can chart share-of-voice and benchmark against creators we care about.

Trigger: cron, every morning at 7:00 AM America/New_York.

Inputs the workflow should accept at configure time: the Google Sheets spreadsheet ID, the Config tab name (default "Config"), and the Daily Log tab name (default "Daily Log").

Step 1 — Read the competitor watchlist. Use Google Sheets Get Values to read the Config tab. Assume the first row is a header and each subsequent row is one competitor with at least two columns: a friendly competitor name and a Tubular creator / channel identifier (channel ID, creator ID, or platform handle). Skip empty rows.

Step 2 — Fetch yesterday's videos and metrics from Tubular Labs. For each competitor row, call Tubular Labs Execute GraphQL Query to fetch: every video that creator/channel published in the last 24 hours, the video title, the canonical URL, the platform (YouTube, TikTok, Instagram, Facebook, Twitter, Twitch), the 1-day-post-upload cumulative view count, like count, comment count, and share count (Tubular exposes 1, 2, 3, 7, and 30-day measurement windows — use the 1-day window), and the creator's current total subscribers/followers. Send the API key in the Authorization header with no Bearer prefix. Handle GraphQL errors in the response body, not just HTTP status.

Step 3 — Append rows to the Daily Log. Use Google Sheets Append Values on the Daily Log tab with valueInputOption USER_ENTERED. Append one row per video with these columns in order: Date (the trigger date in YYYY-MM-DD, ET), Competitor (friendly name from Config), Platform, Video Title, Video URL, Views (1-day), Likes (1-day), Comments (1-day), Shares (1-day), Engagement (likes + comments + shares), Follower Count (creator total at run time). If a competitor published nothing in the window, append no rows for them — do not write a blank/placeholder row.

Behavior rules: this is a deterministic pipeline, no judgement calls or LLM steps. If Tubular returns a rate-limit error (HTTP 429 or extensions.code RATE_LIMITED), back off and retry with exponential delay. If a single competitor errors, log it and continue with the rest — never let one bad row block the whole run. The Daily Log tab should keep growing forever; do not clear or rewrite existing rows. Assume the Daily Log header row already exists; if it doesn't, the workflow can create it on the first run.

Output: the Google Sheet is the source of truth. Charts, pivots, and any downstream BI dashboards are built on top of the Daily Log tab in Sheets — the workflow itself just keeps that tab fresh.

What does this prompt do?

Reads your list of competitor channels and creators from a Config tab in Google Sheets, so the watchlist is yours to edit any time.
Pulls every video those competitors published in the last 24 hours from Tubular Labs, plus 1-day view, like, comment, and share counts and the creator's current follower count.
Appends one row per video to a Daily Log tab with date, competitor, platform, title, URL, views, engagement, and followers, ready to chart in Sheets or pipe into a BI tool.
Runs on autopilot every weekday morning so your share-of-voice dashboard is fresh before the team's first meeting.

What do I need to use this?

A Tubular Labs account with API access (you can find your key under Settings, Integrations).
A Google Sheets account and a spreadsheet with a Config tab listing the competitor channels or creator IDs you want to track.
An empty Daily Log tab in the same spreadsheet where new rows will land each morning.

How can I customize it?

Change the morning run time, the cadence (daily vs weekdays only), or the time zone.
Add or remove competitors any time by editing the Config tab, no workflow changes needed.
Pick which metrics to log (views, likes, comments, shares, followers) and the column order in the Daily Log tab.
Swap the 24-hour lookback for a 2-day or 7-day window if you care about longer-tail performance.

FAQs

Do I need to be a developer to use this?

No. Once you connect Tubular Labs and Google Sheets and tell the workflow which spreadsheet to use, it runs on its own every morning. You manage the competitor list by typing into the Config tab.

Which video platforms are covered?

Tubular Labs aggregates social video data across YouTube, Facebook, Instagram, TikTok, Twitter, and Twitch, so any creator or channel in your watchlist that publishes on those platforms is included.

How do I add a new competitor to the watchlist?

Open the Config tab in your Google Sheet and add a row with the channel or creator ID. The next morning's run will pick it up. No workflow edits required.

Can I build a share-of-voice dashboard on top of this?

Yes. The Daily Log tab is structured for charting. You can build pivots and charts directly in Google Sheets, or connect Looker Studio, Tableau, or another BI tool to the same sheet.

What if a competitor did not publish anything yesterday?

Nothing is appended for that competitor on that day. The workflow only writes rows for videos that actually went live in the lookback window, keeping the log clean.

Related templates

Call overdue Xero customers with an AI collections agent

Every weekday at 10am, an AI voice agent phones your most overdue Xero accounts, logs what each customer promised, and reports back to finance in Slack.