Project Overview
We’re a music promotions agency seeking a comprehensive Airtable-based CRM to manage a wide array of information, including contact management (journalists, playlist curators, DJs, clients, etc.), pitch tracking, wins & coverage reporting, Chartmetric data ingestion with fuzzy matching for automated reporting, campaign management, automations, and error logging. The goal is to have a centralized, scalable, and user-friendly system that serves as the home base for much of our campaign workflow.
Key Objectives
- Unified Data Management: Consolidate all tastemaker contacts, pitch records/internal reporting system, external client-facing reports, and campaign asset management into a single Airtable ecosystem.
- External Reporting Data Integration: Automatically import and match Chartmetric data (playlists, radio spins, performance metrics) to existing records using fuzzy matching and scripting. Import and match Google News alerts or watch RSS feeds for press placements. Import and match Inflyte reports for DJ promo placements. Import and match tastemaker feedback from email to the correct pitch record/internal reporting system and external client-facing report. This could involve watching a specific folder that catches pitch responses or using mailhooks.
- Comprehensive Campaign Tracking: Monitor campaign stages from new client acquisition through final report sent, track revenue, and roll up campaign results (pitches, wins, coverage) into easily digestible reports.
- High-Level Reporting & Dashboards: Provide interfaces or dashboards for quick overviews, detailed performance analysis, and user-friendly navigation.
- Error Handling & Logging: Implement robust automations with logging to handle potential issues (API rate limits, unmatched records, etc.).
Detailed Scope
Core Table Thoughts
- Tastemaker Contacts table will track journalists, DJs, playlist curators, and radio programmers. It is unclear if placements (playlists, radio stations, publications) tied to tastemakers should live in this table or their own.
- Clients table will include fields such as name, primary genre, Spotify URL/URI, billing information, last campaign release date (automatically updated), and notes.
- Genres table will serve as a central reference for music genres.
- Pitches table will track outreach efforts, linking to contacts and artists. Fields will include pitch date, status (sent, replied, accepted, etc.), follow-up date, and notes.
- Wins table will log all successful placements such as playlist additions, press articles, radio plays, DJ promo downloads/feedback, and social mentions. This will link to artists, pitches, and placements, with fields like win type, date, source (Chartmetric, manual entry), streams/plays, URL, and match status.
- Campaigns table will track status, start/end dates, fee, and rollups for pitches and wins.
- External Reporting Data Import table will act as a staging area for imported Chartmetric and other external data before automated matching. Fields will include Chartmetric URIs, cleaned URIs, artist/playlist names, dates, etc.
- Automation Logs table will maintain timestamped records of any errors or special events, including rate-limit handling and unmatched items.
Advanced Automations & Logic
- Last Contact Date Automation: Automatically updates a contact’s last contact date whenever a new pitch is created. Integration with Mixmax or Gmail may be required. If necessary, this could be achieved using mailhooks and BCC.
- Chartmetric Data Integration: This could be done via manual CSV import, script-based, or an external tool approach (Zapier/Make/middleware) to import Chartmetric CSV/API data. Fuzzy matching will match artists and playlists by URIs or partial string similarities. Wins will be automatically created if a match is found, linking to the correct playlist and artist. Error logging will capture unmatched data or rate-limit errors in the automation logs table. A proof-of-concept will be conducted to compare Airtable scripting with external integration platforms (Zapier/Make) to determine the most efficient and reliable integration method.
- Campaign Rollups: Summarize pitch counts, wins, and coverage for each campaign, potentially incorporating budget vs. actuals and ROI calculations.
- Error Handling & Retry Logic: Chartmetric imports should handle 429 (rate-limit) responses with exponential backoff if using a script. Errors should be logged in the automation logs table, capturing the record ID and error message.
Reporting & Dashboards
- Interface Designer: Interactive dashboards will be created for campaign overview (pitches, wins, coverage), contact engagement (last contacted, relationship status), and artist performance (wins, streams/plays from Chartmetric). Filters should allow for date range, campaign, or contact type.
- Custom Views & Charts: Grid and calendar views will help with pitches/follow-up scheduling. Bar or pie charts will visualize distribution of wins, contact verticals, or campaign status.
Documentation & Training
- In-Platform Descriptions: Field-level descriptions will be provided for clarity.
- User Guide: Step-by-step documentation will be created, covering setup, tables, automations, and FAQs.
- Video Tutorials: Optional short Loom-style videos may be produced to demonstrate key workflows such as contact entry, pitch creation, and campaign dashboards.
Deliverables
- A fully functional Airtable base will be provided, including all tables, relationships, fields, formulas, and dashboards/interfaces working as described.
- An error logging mechanism will be implemented to capture and handle automation/script errors.
- Documentation and training resources will include a written document or short videos explaining the system’s structure, usage, and troubleshooting.
Timeline
- Phase 1 - Core Setup: Establish base structure, main tables, fields, and basic relationships. Validate with sample data.
- Phase 2 - Advanced Automations & Integrations: Execute proof-of-concept comparing Airtable scripting vs. Zapier/Make. Finalize Chartmetric import, fuzzy matching, and error logging. Build out follow-up reminders and other automations.
- Phase 3 - Reporting & Dashboards: Create Interface Designer dashboards, charts, and summary views. Fine-tune metrics and user experience.
- Phase 4 - Documentation & Training: Finalize documentation and record tutorial videos. Testing and refinement will take place as needed.
