Marketing.Events

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 Events table.
   • myBMT (bmt-dwh-uks-app-my.azurewebsites.net)
2. Adding Date Column:
   • A new column, Date_Key, is added, which takes the value from the Start_date field. This makes it easier to analyze data by event start dates.
3. Removing Unnecessary Columns:
   • Several irrelevant or sensitive columns are removed, such as fields related to campaign secrecy, user details (e.g., emails, job titles), and metadata (e.g., modified date, created date, labels). This step ensures that only the essential information is retained for analysis.
4. Changing Data Type:
   • The Date_Key column is transformed to the date data type for consistency and easy date-based analysis.
5. Unpivoting Columns:
   • All columns except the ID, Date_Key, Event.lookupValue, Campaign.lookupValue, and Campaign:_CampaignId columns are unpivoted. This step converts the table into a long format, where each attribute is represented in a single column called Attribute, and its corresponding value is placed in the Value column.
6. Adding Platform Information:
   • A new column Platform is added to label all the rows with the value “Events”, which helps to distinguish this data from other platforms when appending with other datasets.
7. Adding Source Information:
   • Another column SourcesType is added with the value “Events”, marking this data source as event-related.
8. Splitting Campaign Name by Delimiter:
   • The Campaign.lookupValue column is split into three parts (Campaign_Core_activity.1, Campaign_Core_activity.2, Campaign_Core_activity.3) based on the delimiter ” | “. This step allows you to handle campaigns with multiple parts or names.
9. Removing Unnecessary Split Columns:
   • After splitting, the columns Campaign_Core_activity.1, Campaign_Core_activity.2, and Campaign_Core_activity.3 are removed as they are not needed further.
10. Renaming Columns:

• The column ID is renamed to ObjectID, Event.lookupValue is renamed to Name, and Campaign:_CampaignId is renamed to CampaignGuid for consistency and clarity in the final dataset.

11. Result Output:

• The transformed table is returned after all the necessary transformations are applied.