Data Processing & Reporting Checklist for Agency & Consulting

Build a living catalog that lists platforms, endpoints, credentials, and human owners for each client. Use a shared sheet or Notion database and require an entry for every new source before onboarding work begins.

Document required columns, types, and nullability using JSON Schema or Pydantic models so changes are caught early. Share schemas with clients and vendors to prevent breaking changes when exports shift.

Convert all timestamps to UTC and store client local time as a separate field to avoid reporting drift. Maintain per-client fiscal calendars and week starts so rollups are consistent across dashboards and PDFs.

Use Supermetrics, Funnel.io, Fivetran, or simple Python scripts to fetch only new or changed data using sync tokens and updated_at fields. Configure backoff, retry, and windowed pulls for platforms with strict rate limits.

Adopt a predictable pattern like client_channel_yearmonth_descriptor.csv and mirror that structure in Google Drive or SharePoint. Include versioning conventions so account managers can trace which file fed a report.

Create separate datasets per client in BigQuery, Snowflake, or Redshift to simplify cost attribution and access control. Mirror the same isolation in dev and prod to keep testing safe and reproducible.

Create standardized dbt models for spend, clicks, sessions, and conversions, and use macros for reusable logic like UTM parsing and channel mapping. Tag models by client and deliverable so pipelines can run selectively.

Maintain mapping tables that reconcile Google Ads, Meta, LinkedIn, and TikTok naming to a canonical schema. Review and update mappings monthly to reflect new campaigns and naming rules agreed with the client.

Normalize emails and phone numbers, then apply probabilistic matching with libraries like Splink or deterministic joins with hashed keys. Record match rates and unresolved proportions to inform client discussions on attribution quality.

Store historical rates from sources like the European Central Bank or Open Exchange Rates and convert at transaction time. Persist the applied rate and source in the fact table for later audit and reconciliation.

Use MaxMind for IP-to-geo or a vendor like Clearbit for firmographic data, but strip or hash PII before external calls as required by the client DPA. Cache enrichment results per client to reduce cost and API variability.

Use PDFPlumber, Camelot, or Tabula to parse tables, and Tesseract OCR for scans, then validate column counts and totals before load. Store raw PDFs with a processed flag and a manifest so every figure is traceable.

Track max event date per table and compare to expected latency by source. Send Slack or email alerts via webhooks when thresholds are breached so client deliverables are not sent with stale data.

Test for uniqueness, not null, ranges, and referential integrity on key models like spend and opportunity facts. Parameterize thresholds per client to account for different scale and distribution patterns.

Compute moving averages and z-scores for CPC, CPA, ROAS, and lead rates, then flag anomalies beyond configured sigma. Log exceptions with campaign and platform context to speed investigation by account managers.

Compare daily and monthly totals between API pulls and transformed tables, accounting for timezones and currency conversions. Maintain a reconciliation dashboard that highlights gaps and known issues by client.

Publish dbt docs or OpenLineage metadata so analysts and clients can trace a KPI to its sources and transformations. Require a short business definition and owner for each derived metric.

Document how to run incremental loads with safe windows and when to force a full refresh after schema changes. Include steps to pause BI extracts, backfill, validate totals, and re-enable schedules to avoid double counting.

Create Looker Studio, Tableau, or Power BI templates that reference a client_id parameter and hide non-applicable charts. Lock visual styles and KPI placements so deliverables feel consistent across accounts.

Use Jinja or Nunjucks templates to produce weekly and monthly commentary that pulls top movements, spend efficiency, and funnel drop-offs. Embed links to specific dashboard tabs for quick verification by the client.

Store metric definitions in YAML and sync them to your BI and testing layers so one change updates everywhere. Record effective dates and SOW references to avoid contract disputes over metric interpretations.

Automate email and Slack sends with audience-specific versions and attach PDFs for executives who avoid dashboards. Include last refresh time, caveats, and a link to the changelog to build trust.

Use service accounts and RLS policies in Tableau, Power BI, or Looker to ensure only assigned account managers and client users see their data. Audit permissions monthly and after staff changes to prevent leakage.

Record every change to definitions, data sources, and visualizations with a reason and expected impact on KPIs. Include this summary in monthly business reviews so stakeholders understand fluctuations due to methodology shifts.

Use Airflow, Dagster, or Prefect with separate DAGs per client or workspace to isolate failures. Define retries, SLAs, and resource pools so a heavy client run does not starve others.

Use GitHub Actions or GitLab CI to run dbt builds and tests on pull requests before merge. Require reviews from a data lead or account lead for changes that affect client-facing KPIs.

Store API keys, dataset names, calendars, and KPI thresholds in per-client YAML or JSON configs that are injected at runtime. Avoid copy-pasting code by using the same job definition across clients with different configs.

Use BigQuery INFORMATION_SCHEMA or Snowflake Account Usage to attribute spend by dataset and tag. Set budgets and alerts, then optimize high-cost models with clustering, partitioning, or materialization changes.

Store credentials in GCP Secret Manager, AWS Secrets Manager, or Vault and inject them at runtime rather than in code. Rotate platform tokens quarterly and after staffing changes to reduce risk.

Apply lifecycle rules in S3 or GCS for raw and processed data with clear retention periods aligned to contracts and regulations. Document and rehearse deletion procedures for GDPR or CCPA requests so response times are fast.