Marketing.Advertising

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 Advertising table.
   • myBMT (bmt-dwh-uks-app-my.azurewebsites.net)
2. Renaming the Date Column:
   • The column Date_live is renamed to Date_Key for consistency with other datasets.
3. Changing Data Type:
   • The Date_Key column is converted to the date data type to enable easy date-based analysis.
4. Removing Unnecessary Columns:
   • Columns such as Created_By information (e.g., id, title, email), secret campaign fields, and ad specification data are removed as they are not needed for analysis.
5. Duplicating and Splitting Campaign Name:
   • A copy of the Campaign.lookupValue column is created as Campaign Name - Copy. This allows further manipulation of the campaign name.
   • The Campaign.lookupValue is split into multiple columns (Campaign Name.1, Campaign Name.2, Campaign Name.3) using the delimiter ” | “.
6. Adding Custom Columns:
   • A custom column Custom1 is added with a default value of 0.
   • Another column Name is created, combining the values of Publication.lookupValue, Advertising_campaign_name, and the month name of Date_Key. This helps form a meaningful concatenation for the name of each advertising campaign.
7. Renaming Columns:
   • The Advertising_campaign_name column is renamed to SourcesType, indicating the source of the campaign.
8. Further Removal of Columns:
   • Several columns that are not required for analysis, such as Modified, Date_end, and Campaign.lookupId, are removed.
9. Merging with Campaigns Table:
   • The advertising data is merged with the Campaigns table using Campaign Name as the key. The CampaignGuid is pulled from the Campaigns table to enable campaign-based reporting.
10. Expanding and Unpivoting Columns:

• The merged table is expanded to include the CampaignGuid.
• Columns such as SourcesType, Name, Campaign Name, and other campaign-related columns are unpivoted to create two columns:
  • Attribute (which stores the original column names), and
  • Value (which stores the respective data).

11. Adding Platform Information:

• A new column Platform is added, setting its value as “Advertising” for all rows, identifying that the data pertains to advertising campaigns.

12. Removing Unnecessary Columns:

• Finally, redundant columns such as Campaign Name.2, Campaign Name.3, Publication.lookupValue, and other variations of Campaign Name are removed.

13. Result Output:

• The resulting table is output after all transformations are applied.