Reporting Automation

Freesat is a collective venture between the BBC, ITV, Channel 4 and Channel 5. They have been in operation for over 16 years, delivering the best of linear TV and On Demand content over satellite to over 2 million homes. 

They wanted to automate their marketing reports as the number of reports produced was growing and the process was very manual. Each one required pulling data from diverse platforms such as Google Analytics, Facebook Ads, Braze, Moveable Ink and others.

Challenges:

  1. Manual Data Collection:

    • Staff spend hours collecting data from various platforms for weekly and monthly reports.

    • High risk of errors during manual data entry.

  2. Delayed Insights:

    • Reporting processes were slow, delaying actionable insights.

  3. Disjointed Data Sources:

    • No centralised location for consolidating data from marketing tools.

  4. Lack of Automation Knowledge:

    • The company lacked expertise in using tools like Zapier and Google Cloud Platform to improve processes.

Solution: Create a Centralised Database of all Customer and Campaign Data

1. Workflow Design

  • Zapier Integration Setup: Configured Zaps (Zapier workflows) for each data source. 

  • Platform Connections:

  • Connected Google Analytics, Facebook Ads, and HubSpot to Zapier using pre-built integrations.

  • Moveable Ink isn’t currently supported by Zapier so webhooks had to be used for connection.

  • Linked Zapier to Google BigQuery for centralised data storage.

2. Automated Data Collection

Zap Configuration:

  • Trigger: Data changes (e.g., new campaign metrics in Facebook Ads, updated analytics in Google Analytics).

  • Action: Data pushed to a specific BigQuery Table

  • Real-Time Updates: Set up Zaps to trigger hourly or daily updates, ensuring reports were always current.

3. Data Cleaning and Transformation

  • Added preprocessing steps to clean and transform raw data into a report-ready format directly in BigQuery:

    • Standardized date formats and campaign names.

    • Applied simple calculations (e.g., ROI, cost per click) via automated formulas.

4. Dashboard Creation

Linked the cleaned data in Google Sheets to a Looker Studio dashboard.

  1. Created dynamic visualizations for:

  • Campaign performance (e.g., impressions, CTR, and conversions).

  • ROI comparisons across platforms.

  • Monthly trends and KPIs.

5. Training and Handover

  • Provided training sessions for the agency’s team on using and maintaining Zapier workflows.

  • Created documentation detailing workflows and troubleshooting steps.

Results:

Time Savings:

An estimated 15 hours per week of manpower was saved across the team.

Error Reduction:

Eliminated manual data entry errors therefore improving report accuracy.

Faster Insights:

Enabled real-time reporting, allowing for quicker decision-making.

Improved Scalability:

The automated workflows could be easily adapted to onboard new clients and platforms.