Weekly CallRail marketing channel ROI report to Slack

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.