Agency

Lead inquiry
     ↓
Discovery call
     ↓
Proposal + SOW
     ↓
agency_clients record created
     ↓
agency_projects created per engagement
     ↓
agency_deliverables tracked
     ↓
Monthly retainer or project completion

-- Compare client results to our internal metrics
SELECT 
  'Client' as type,
  ac.company_name,
  AVG(ad.conversion_rate) as avg_conversion
FROM agency_clients ac
JOIN agency_deliverables ad ON ad.client_id = ac.id
GROUP BY ac.id

UNION ALL

SELECT 
  'Internal',
  pv.name,
  AVG(ppa.conversion_rate)
FROM publisher_verticals pv
JOIN publisher_posts pp ON pp.vertical_id = pv.id
JOIN publisher_post_analytics ppa ON ppa.post_id = pp.id
GROUP BY pv.id;