Marketing.LandingPages

by

Purpose

The purpose of these queries is to streamline the integration and transformation of marketing and campaign data from multiple sources into a unified format. These Power Query scripts address various types of campaign-related data—ranging from email campaigns, social media posts, and advertising, to events, page performance, and form submissions.

Process

Source Selection: The query starts by loading data from a table called hbs Landing_Pages Monthly.

myBMT (bmt-dwh-uks-app-my.azurewebsites.net)

Merging Queries: It merges the hbs Landing_Pages Monthly table with another table, hbs Landing_Pages Content, by matching the breakdown column in the first table with the analytics_page_id column in the second table. This is a left outer join, meaning all rows from the first table are kept, and matching rows from the second table are added.

myBMT (bmt-dwh-uks-app-my.azurewebsites.net)

Adding a Calculated Column: A new column named pageBouncePercent is created by multiplying the values in the pageBounceRate column by 100, turning the rate into a percentage.

Expanding Merged Data: The query expands the hbs Landing_Pages Content table, adding two columns, name and campaign, to the results.

Filtering Rows: It filters the rows to keep only those where the campaign column is not null (i.e., it removes rows where the campaign value is missing).

Unpivoting Data: The query transforms the table by unpivoting all columns except breakdown, name, campaign, and list_date. This means that all other columns are turned into two columns: Attribute (which contains the original column names) and Value (which contains the corresponding data).

Renaming Columns: Several columns are renamed for clarity:

  • breakdown becomes Id
  • list_date becomes Date_Key
  • campaign becomes CampaignGuid
  • name becomes Name

Changing Column Types: The Date_Key column is explicitly set to the date type to ensure correct formatting.

Adding a Custom Column for Source Type: A new column called SourcesType is added with the value “Direct traffic” for all rows.

Adding Another Custom Column for Platform: A column called Platform is added with the value “HubSpot Landing Pages” for all rows.

Result Output: Finally, the processed table with all the transformations is output as the result.

Leave a Comment