Marketing.ThoughtLeadership

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

1. Source Selection:
   • The query starts by loading data from the hbs Thought_Leadership table.
   • myBMT (bmt-dwh-uks-app-my.azurewebsites.net)
2. Removing Unnecessary Columns:
   • The query removes a set of columns that are not needed for the analysis. These columns include Campaign.isSecretFieldValue, Color_Tag, Compliance_Asset_Id, Publication:_ID, Content_Type, Modified, Created, Date_end, and Campaign.lookupId.
3. Renaming Columns:
   • Several columns are renamed to make their names more meaningful:
     • Date_live becomes Date_Key
     • Piece_title becomes Name
     • Campaign:_CampaignId becomes Campaign Id
     • Campaign.lookupValue becomes Campaign Name
4. Changing Column Types:
   • The Date_Key column is changed to the datetime data type, ensuring correct formatting for date values.
5. Unpivoting Data:
   • The table is unpivoted by transforming all columns except Name, ID, Campaign Name, Campaign Id, and Date_Key. This means all other columns are turned into two columns:
     • Attribute (which contains the original column names) and
     • Value (which contains the corresponding data).
6. Adding a Platform Column:
   • A new column called Platform is added to every row, and its value is set to "Thought Leadership" to classify the data.
7. Duplicating the Campaign Name Column:
   • The Campaign Name column is duplicated to create a new column called Campaign Name - Copy.
8. Splitting the Campaign Name Column:
   • The duplicated Campaign Name column is split into three separate columns using the delimiter "| ". This results in three new columns: Campaign Name.1, Campaign Name.2, and Campaign Name.3.
9. Renaming Columns:
   • Several columns are renamed:
     • Campaign Id becomes CampaignGuid
     • Campaign Name - Copy becomes Campaign Name
     • Campaign Name.1 becomes SourcesType
     • ID becomes ObjectID
10. Removing Additional Unnecessary Columns:

• The query removes the columns Campaign Name.2, Campaign Name.3, and the duplicated Campaign Name column as they are no longer needed.

11. Result Output:

• The transformed data, with the unnecessary columns removed, is returned as the final output of the query.