Marketing.Meltwater

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 table called mwr Meltwater.
   • myBMT (bmt-dwh-uks-app-my.azurewebsites.net)
2. Merging Queries:
   • The query joins the mwr Meltwater table with the hbs PR Report table using the file_name column from both tables. It performs a left outer join, meaning all rows from the mwr Meltwater table are retained, and matching rows from the hbs PR Report table are added.
3. Expanding Merged Data:
   • It expands two columns, Press_release_title and Campaign.lookupValue, from the hbs PR Report table to include them in the result.
4. Second Merge:
   • The query performs another left outer join, this time between the Campaign.lookupValue column from the previous step and the CampaignName column from the Campaigns table. This adds related campaign information to the dataset.
5. Expanding Campaigns Data:
   • The CampaignGuid column from the Campaigns table is expanded and included in the dataset.
6. Adding a Custom Column (Custom1):
   • A new column named Custom1 is added, where every row gets the value 0.
7. Replacing Null Values:
   • The query replaces any null values in the Title column with the string "-1".
8. Adding a Conditional Column (Name):
   • A new column named Name is added, with values based on a conditional statement:
     • If the Title column has the value "-1", the value in Name will be the value from the Opening_Text column.
     • Otherwise, it will take the value from the Title column.
9. Removing Unnecessary Columns:
   • The query removes a large number of columns that are no longer needed, such as URL, Hashtags, Image, Author_Name, and many others. Only essential columns are kept.
10. Unpivoting Data:
    • The table is transformed by unpivoting all columns except Date, Document_ID, Source_Type, Name, Campaign.lookupValue, and CampaignGuid. This means other columns are converted into two columns:
    • Attribute (which contains the original column names) and
    • Value (which contains the corresponding data).
11. Renaming Columns:

• Several columns are renamed for clarity:
  • Source_Type becomes SourcesType
  • Campaign.lookupValue becomes CampaignId
  • Document_ID becomes ObjectID
  • Date becomes Date_Key

12. Adding a Platform Column:

• A new column named Platform is added, with the value "PR Coverage" for all rows.

13. Changing Column Types:

• The Value column is converted to a numeric data type.
• The Date_Key column is converted to a date type.

14. Filtering Rows:

• Finally, the query filters the rows, keeping only those where the CampaignGuid column is not null (i.e., it excludes rows with missing campaign data).

15. Result Output:

• The transformed data is returned as the output of the query.