Marketing.CampaignTraffic

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 the hbs campaignTraffic table.
• myBMT (bmt-dwh-uks-app-my.azurewebsites.net)

Adding a Custom Column (Custom1):

• A new column named Custom1 is added to the table, and each row is assigned a value of 0.

Removing Unnecessary Columns:

• The query removes the columns SourceYear, SourceMonth, and file_name, as they are not needed for the analysis.

Merging with the Campaigns Table:

• The table is joined with the Campaigns table, using the sessionCampaignName column from the hbs campaignTraffic table and the CampaignName column from the Campaigns table. This is a left outer join, meaning all rows from the hbs campaignTraffic table are kept, and matching rows from the Campaigns table are added.

Expanding the Campaigns Table:

• The CampaignGuid column from the Campaigns table is expanded and added to the table.

Filtering Rows:

• The query filters out rows where CampaignGuid is null, keeping only rows with valid campaign data.

Renaming Columns:

• Several columns are renamed to make the dataset clearer:
  • sessionCampaignName becomes Name
  • SourceDate becomes Date_Key
  • checksum becomes ObjectID

Changing Column Types:

• The Date_Key column is changed to the date data type for proper formatting.

Unpivoting Data:

• The query unpivots all columns except for CampaignGuid, ObjectID, Name, and Date_Key. This means all other columns are transformed into two columns:
  • Attribute (which contains the original column names) and
  • Value (which contains the corresponding data).

Adding a Custom Column for Platform:

• A new column named Platform is added, and all rows are assigned the value "Campaign Traffic" to indicate the data’s context.

Adding a Custom Column for Source Type:

• Another new column named SourcesType is added, and each row is assigned the value "www.bmt.org" to indicate the traffic source.

Result Output:

• The final table, with all the transformations and custom columns added, is returned as the result of the query.

Appended with

This query is an additional step, appending another dataset (hbs campaignTrafficFormSubmissions) to the previous campaign traffic data. Let’s break this down and explain the process.

Power Query Process in Plain English:

1. Source Selection:
   • The query begins by loading data from the hbs campaignTrafficFormSubmissions table.
   • myBMT (bmt-dwh-uks-app-my.azurewebsites.net)
2. Removing Unnecessary Columns:
   • It removes the columns SourceYear, SourceMonth, and file_name, which are not required for the analysis.
3. Merging with the Campaigns Table:
   • The sessionCampaignName column from the hbs campaignTrafficFormSubmissions table is joined with the CampaignName column from the Campaigns table through a left outer join. This means all rows from the form submissions table are retained, and matching data from the Campaigns table is brought in.
4. Expanding Campaigns Data:
   • The CampaignGuid column from the Campaigns table is expanded and added to the dataset.
5. Filtering Rows:
   • Rows where CampaignGuid is null are filtered out, so only rows with valid campaign data remain.
6. Renaming Columns:
   • Several columns are renamed to align with the dataset:
     • sessionCampaignName becomes Name
     • SourceDate becomes Date_Key
     • checksum becomes ObjectID
     • eventName becomes Attribute
     • eventCount becomes Value
7. Changing Column Types:
   • The Date_Key column is changed to the date data type for proper formatting.
8. Adding a Custom Column for Platform:
   • A new column called Platform is added to the dataset, with all rows given the value "Campaign Traffic" to indicate the data’s context.
9. Adding a Custom Column for Source Type:
   • Another custom column named SourcesType is added with the value "www.bmt.org" to indicate the traffic source.
10. Result Output:
    • The final processed data is output, ready to be appended to the previous campaignTraffic dataset.