Purpose
This set of Power Query transformations has been designed to consolidate data across multiple digital marketing channels—such as social media, email campaigns, events, and page performance—into a unified master dataset.
The goal is to automate and streamline the integration of data from disparate systems like HubSpot, Meltwater, and internal campaign databases, ensuring consistency and accuracy. These queries unpivot, clean, and merge data, making it ready for advanced analytics and reporting. Each view (whether it’s campaign traffic, social media, or advertising) contributes to a broader understanding of marketing performance, enabling effective reporting and actionable insights.
Each query is designed to achieve the following objectives:
- Data Consolidation: Pull data from different tables and systems (such as HubSpot, Meltwater, and other internal platforms) and merge them based on common identifiers like campaign names, GUIDs, or file names.
- Data Transformation: Standardise data by renaming columns, un-pivoting data for normalisation, and applying calculated fields to ensure consistent structure across multiple datasets.
- Data Cleansing: Remove irrelevant or redundant columns and filter out incomplete or erroneous records to ensure high-quality and meaningful data.
- Campaign Performance Tracking: Append additional data such as campaign traffic, page performance, and form submissions to provide a holistic view of how various campaigns perform across different platforms.
- Platform-Specific Insights: Each query highlights specific metrics related to the source of the campaign, whether it’s social media, email, or advertising, helping to track performance by platform.
These queries serve as a powerful toolset for developers and analysts, enabling them to process large volumes of data efficiently and gain actionable insights for optimising marketing efforts across various channels.