Marketing.PagePerformance

by

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:
  2. Removing Unnecessary Columns:
    • It removes the columns SourceYear, SourceMonth, and file_name, which are not needed for analysis.
  3. Merging with the hbs External Table:
    • The pagePath column from the hbs pagePerformance table is joined with the Url - Copy column from the hbs External table using a left outer join. This retains all rows from the page performance table, while bringing in any matching rows from the hbs External table.
  4. Expanding the hbs External Table:
    • The columns CampaignGuid, CampaignId, and PageViews from the hbs External table are expanded and added to the dataset.
  5. Filtering Rows:
    • Rows where CampaignGuid is null are filtered out, ensuring only rows with valid campaign data are kept.
  6. Removing Unnecessary Columns (Round 2):
    • The CampaignId column is removed, as it is not needed for further analysis.
  7. Unpivoting Data:
    • The query unpivots all columns except for CampaignGuid, checksum, SourceDate, and pagePath. This means all other columns are transformed into two columns:
      • Attribute (which contains the original column names) and
      • Value (which contains the corresponding data).
  8. Renaming Columns:
    • Several columns are renamed to better reflect their purpose:
      • checksum becomes ObjectID
      • pagePath becomes Name
      • SourceDate becomes Date_Key
  9. Changing Column Types:
    • The Date_Key column is changed to the date data type for proper formatting.
  10. Adding a Custom Column for Platform:
  • A new column named Platform is added, and all rows are assigned the value "Page Performance" to indicate the context of the data.
  1. Adding a Custom Column for Source Type:
  • Another new column called SourcesType is added, with each row receiving the value "www.bmt.org" to indicate the traffic source.
  1. Result Output:
  • The final table, with all transformations and custom columns, is returned as the result of the query.

Appended with

Source Selection:

Removing Unnecessary Columns:

  • It removes the columns SourceYear, SourceMonth, and file_name, which are not required for the analysis.

Merging with the hbs External Table:

  • The pagePath column from the hbs pagePerformanceFormSubmissions table is joined with the Url - Copy column from the hbs External table using a left outer join. This means all rows from the form submissions table are kept, and matching rows from the hbs External table are added where available.

Expanding the hbs External Table:

  • The columns CampaignGuid, CampaignId, and PageViews from the hbs External table are expanded and added to the dataset.

Filtering Rows:

  • Rows where CampaignGuid is null are filtered out to retain only rows with valid campaign data.

Removing Unnecessary Columns (Round 2):

  • The CampaignId, landingPage, and eventName columns are removed, as they are not required for further analysis.

Unpivoting Data:

  • The query unpivots all columns except for pagePath, checksum, SourceDate, and CampaignGuid. This means all other columns are transformed into two columns:
    • Attribute (containing the original column names) and
    • Value (containing the corresponding data).

Renaming Columns:

  • Several columns are renamed to make the dataset more meaningful:
    • pagePath 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.

Adding a Custom Column for Platform:

  • A new column named Platform is added, and each row is given the value "Page Performance" to indicate the context of the data.

Adding a Custom Column for Source Type:

  • Another column called SourcesType is added, with all rows receiving the value "www.bmt.org" to indicate the traffic source.

Result Output:

  • The final processed data is output after all transformations and column additions.

Leave a Comment