Release 51

by

Additions

Marketing

New [Marketing] DataMart is designed to support Customer Acquisition and Campaign Management efforts. It provides a centralised, standardised structure for analysing marketing activities, optimising campaign performance, and understanding customer engagement across multiple channels.

12 standardised views. This structure allows for seamless integration and analysis of marketing performance across platforms (e.g., Advertising, Traffic, Email Campaigns etc), enabling a unified view of campaign events and interactions with detailed attribution and engagement insights.

  • Data Transformation:
    • Attributes are unpivoted into key-value pairs (Attribute and Value), ensuring consistency across data marts.
    • Fields are standardised (e.g., Date_Key, CampaignGuid) for easy UNION and integration.
  • Platform and Source: All data marts include a SourceType and Platform for clear categorisation of campaign types and data origins.
  • Future Proofing: A Custom1 field is included across marts, allowing for flexible expansion as new metrics emerge.

The DataMart and Views support the Global Marketing Report

Marketing.Campaign_details_plus

The Marketing_Campaign_Details_Plus view provides an enriched and detailed representation of campaign metadata, serving as a foundational dataset for analysing and managing marketing campaigns. This view is designed to offer a structured, high-level summary of campaign attributes, ensuring clarity and consistency for reporting and decision-making.

Alterations

Customer.Orderline

There was an issue with the OBJECT_SEQ that prevented it from functioning correctly for the intended join operation.

Project.Details

Improvements to the quality of [Project Name] are being achieved by leveraging additional context and details from [Project Description]. This process extracts the most relevant segment of the description that corresponds to the project name, ensuring consistency and enhanced accuracy in data representation.

IIF(
    CHARINDEX(src.[NAME], src.[DESCRIPTION]) > 0,
    SUBSTRING(
        src.[DESCRIPTION],
        CHARINDEX(src.[NAME], src.[DESCRIPTION]),
        IIF(
            CHARINDEX('|', src.[DESCRIPTION]) > 0,
            CHARINDEX('|', src.[DESCRIPTION]) - CHARINDEX(src.[NAME], src.[DESCRIPTION]),
            255
        )
    ),
    src.[NAME]
) AS PROJECT_NAME

Canadian Projects (CAD)

Improvements to Cost Values based on revised mapping provided, USD to follow

Notes

1. ⚠️ DIFFERENT: customer_orderline_202411.sql
2. 🔴 MISSING IN PRD CONTAINER: marketing_Advertising.sql
3. 🔴 MISSING IN PRD CONTAINER: marketing_campaignTraffic.sql
4. 🔴 MISSING IN PRD CONTAINER: marketing_campaignTrafficFormSubmissions.sql
5. 🔴 MISSING IN PRD CONTAINER: marketing_campaign_details_plus.sql
6. 🔴 MISSING IN PRD CONTAINER: marketing_EmailCampaigns.sql
7. 🔴 MISSING IN PRD CONTAINER: marketing_Events.sql
8. 🔴 MISSING IN PRD CONTAINER: marketing_Forms.sql
9. 🔴 MISSING IN PRD CONTAINER: marketing_HubSpotLandingPages.sql
10. 🔴 MISSING IN PRD CONTAINER: marketing_Meltwater.sql
11. 🔴 MISSING IN PRD CONTAINER: marketing_pagePerformance.sql
12. 🔴 MISSING IN PRD CONTAINER: marketing_pagePerformanceFormSubmissions.sql
13. 🔴 MISSING IN PRD CONTAINER: marketing_SocialMedia.sql
14. 🔴 MISSING IN PRD CONTAINER: marketing_ThoughtLeadership.sql
15. ⚠️ DIFFERENT: project_cost_CAD.sql
16. ⚠️ DIFFERENT: project_details.sql
17. ⚠️ DIFFERENT: project_details_CAD.sql
18. ⚠️ DIFFERENT: project_details_plus.sql

Leave a Comment