Weekly CallRail Channel ROI Report in Slack and Google Sheets

Build me a weekly marketing channel ROI report for CallRail. Trigger it on a cron schedule every Monday at 8am ET, covering the previous 7 days of call activity. CallRail does not need polling here, just a scheduled run.

Step 1. Aggregate call counts. Call CallRail Summarize Call Data twice for the last 7 days: once grouped by source and once grouped by campaign. Use these aggregates as the channel-level totals.

Step 2. Identify quality. Call CallRail List All Calls for the last 7 days requesting attribution fields (source, campaign, keyword, duration, lead_status, value, tracking_phone_number, customer_name). Page through results so no calls are missed. From this list, compute per source and per campaign: total calls, qualified calls (default definition: duration > 60 seconds AND lead_status = good_lead, but make the threshold easy for the user to edit), total call value when present, and the top 3 keywords by qualified call count.

Step 3. Compare to prior week. Run the same Summarize Call Data calls for the 7 days before that and compute week-over-week change per source and per campaign. Flag any channel whose qualified calls fell by more than 25 percent week-over-week.

Step 4. Write a short narrative report. Rank channels by qualified call volume. Always reference channels by source name and campaign name, never by internal IDs. The report should have clear sections: a one-line headline (which channel won), Top channels (ranked list with counts and WoW change), Top 3 keywords driving calls, Channels to watch (anything flagged as dropping sharply), and a one-sentence recommendation. Keep numbers grouped into sections, not a wall of figures.

Step 5. Post to Slack. Use Slack Send a Message to post the report to a channel the user picks during setup. Format with Slack markdown: bold section headers, bullet lists for rankings, and a small footer with the date range covered.

Step 6. Log to Google Sheets. Use Google Sheets Append Values to append one row per channel to a tracking sheet the user picks. Columns: week_start_date, week_end_date, channel_type (source or campaign), channel_name, total_calls, qualified_calls, top_keyword, total_value, wow_qualified_change_pct, flagged. This builds a trend log the marketing team can chart over time.

Setup inputs to collect from the user: CallRail account ID, Slack channel to post in, Google Sheet ID and tab name to append to, qualified call definition (duration threshold in seconds, lead_status value), and the drop threshold for the watch list. Default qualified = 60 seconds + good_lead, default drop threshold = 25 percent.

Tone: write the Slack message like a marketing analyst briefing the team. Concise, scannable, no jargon, no raw IDs. If a week has zero calls, post a short note saying so instead of an empty report.

Additional information

What does this prompt do?

Ranks your marketing sources and campaigns each week by how many qualified phone leads they drove, using last week's CallRail call activity.
Calls out the top keywords driving calls and flags any channel that dropped sharply versus the prior week.
Posts a clean, section-by-section summary to the Slack channel of your choice every Monday morning.
Appends one trend row per channel to a Google Sheets tracker so week-over-week performance accumulates over time.

What do I need to use this?

A CallRail account with tracking installed on the campaigns you want to measure.
A Slack workspace and the channel you want the weekly report posted in.
A Google Sheet you want trend rows appended to (one tab is fine).
A rough definition of a qualified call for your team, such as calls longer than 60 seconds marked as a good lead.

How can I customize it?

Change the run time. Monday at 8am ET is the default, but any weekday and timezone works.
Adjust what counts as a qualified call (minimum duration, lead status, minimum call value).
Pick a different Slack channel, or post to a private channel only marketing leadership can see.
Swap the Google Sheet, or add extra columns like cost per qualified call if you track spend separately.

Frequently asked questions

Does this replace my CallRail dashboard?

No. It pulls the same call data you already see in CallRail and turns it into a short, written ranking that lands in Slack each Monday. Your team gets the takeaway without anyone logging in to build a view.

How does it decide which channel is winning?

It ranks each source and campaign by qualified call volume using the definition you provide, such as calls over 60 seconds marked as a good lead. You can change that bar at any time.

What gets written to Google Sheets?

One row per channel per week with the source or campaign name, total calls, qualified calls, top keyword, and the week-over-week change. Over time the sheet becomes a clean trend log you can chart however you like.

Can I get this in a private channel for leadership only?

Yes. You pick the Slack channel when you set it up, so you can route the report to a private marketing channel, a direct message, or a public team channel.

Will it call out channels that suddenly tanked?

Yes. The report flags any source or campaign that dropped sharply versus the prior week so you can investigate before the trend gets worse.