Build a Free Cold Email Dashboard from the Smartlead API (Sheets + Looker Studio)

The Schema That Makes It Work

campaigns_daily:
  date | workspace | client | campaign_id | campaign
  | prospects_contacted | sends | hard_bounces
  | replies_total | replies_human | replies_positive
  | meetings

replies_log:
  date | campaign_id | prospect_email | intent
  | confidence | summary        (from the classifier)

computed (formulas / Looker):
  bounce_rate     = hard_bounces / sends
  positive_rr     = replies_positive / prospects
  pos_to_meeting  = meetings / replies_positive

• •One row per campaign per day is the grain that answers every question later: trends, week-over-week, per-client rollups, and incident forensics ("what changed on the 14th?").
• •Idempotent writes: the job deletes-then-writes its date's rows so re-runs never duplicate. This one decision prevents 90% of dashboard rot.
• •Client column from day one even if you have one client today. Agencies grow into it; retrofitting is painful.
• •Intent from the classifier upgrades replies_total into the honest ladder. Ship v1 without it, but leave the columns.

The Looker Studio Layer

Connect the Sheet as a data source and build four views: a program overview (all campaigns, the ladder metrics, 30-day trend), a campaign drilldown (daily series for one campaign), a client view (filtered by the client column, shareable as a read-only link, which quietly becomes your client reporting), and an alarm view (any campaign with bounce rate above 3% or positive rate collapsing week over week, per the stop rules). The alarm view is the one you check daily; the rest exist for weekly reviews and clients.