Topic 1, Adventure Works
Case study
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all questions included on this exam in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other questions in this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to make changes before you move to the next section of the exam. After you begin a new section, you cannot return to this section.
To start the case study
To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore the content of the case study before you answer the questions. Clicking these buttons displays information such as business requirements, existing environment, and problem statements. When you are ready to answer a question, click the Question button to return to the question.
General Overview
AdventureWorks Cycles is a bicycle retailer with a few locations in the Midwest region. The AdventureWorks Cycles business model supports both in store purchases as well as online orders. In addition to offering a wide variety of bicycles, the company sells clothing, performance nutrition supplements, bicycle parts as well as bicycle fitting and repair services. The customer base varies from professional cyclists, individual leisure riders to families. The business experienced unprecedented growth of over 2000% during the pandemic bringing a total number of customers to 10,000. The company decided to invest in Microsoft Dynamics 365 Customer Insights and Dynamics 365 Sales App to unify customer data and improve sales.
Blue Yonder Airlines created a new division named BY Excursions (BYE). BYE operates site-seeing tours arid other excursions in vacation destinations across North America.
BYE is the product of the acquisition of three regional tour and excursion companies.
* Each of these regional operations have updated their branding.
* The three operations continue to maintain separate CRM systems for sales and customer service in their respective terntones (East West and Central).
* Each regional operator offers different types of tours and excursions. For example, the East region currently offers boat tours, but the Central region does not.
BYE uses a single instance of Dynamics 365 Marketing for marketing automation, and tt recently implemented Dynamics 365 Customer Insights as its CDP. Dynamics 365 Marketing and Customer Insights are not currently connected.
A corporate data lake is built on Azure Data Lake Gen 2. This data lake has two containers named ‘imports- and “exports. – The BYE data science team uses Azure Machine Learning Studio to build predictive models.
An Azure Data Factory pipeline produces flat-fit
le extracts of all customers, events, and survey response data from every regional CRM system, and copies them to the corporate data lake imports container daily. These extracts are produced in pipe-delimited text format and each type of extract file has the same schema. The pipeline runs at 1 a.m. US Eastern Standard Time (UTC -5). Each regional system extract has the same format.
Customer data is represented as records in the Customer extract file. This file contains customer address and contact information. Because customers can make purchases in more than one region, the same customer may be represented in multiple regional extract files.
Regional extracts may also contain multiple records corresponding to the same individual customer.
Each record in a single regional extract will have a unique customer number value. The customer number value is not guaranteed to be unique across all regional extracts. Each customer file contains fewer than 50,000 records.
Customer event ticket purchases are represented as records in the event tickets extract file. This file contains a unique ID value for every ticket purchase along with details such as event type, event date, purchase date and ticket cost Each customer is also sent a customer satisfaction survey after the conclusion of every event. The survey includes numeric rating of 1 -5 (1 being le sati d and 5 being most satisfied) and a free-text response field for the customers to share their impressions of the event.
The schema for the three files in each extract is shown below:
Your team is responsible for the configuration of Customer insights. You are an Administrator in the environment.
Your team has configured a separate data source corresponding to the customer file extracts from each regional operation. Each data source uses a Microsoft Power Query text file connector to ingest the extract files. The Microsoft Enhanced Addresses enrichment has been enabled for each regional data source. The enrichment name for each region is the region name (East. West or Central) followed by “addresses.” Your team has also configured a fourth data source using Power Query to create consolidated files containing all event tickets and survey response records. These two new files have the same layout as the regional files.
Customer email addresses are included in these files because customer numbers are not unique across regions.
Your team has defined a Unified Customer Profile based on the three different customer extract files. The East and West regional customer extracts have deduplication rules configured with the default merge preferences and the following centric:
1. Exact matches on LastName. HomeAddress, City and PostalCode.
2. Exact matches on FirstName, LastName, EmailAddress and normalized PhoneNumber.
The Central regional customer extract does not have any deduplication rules configured.
Your team has configured matching to start with the East region customer extract as the primary entity, then the Central region, then the West region. Each regional entity is configured to include all records, and subsequent entities match the previous entity using the following conditions:
1. Exact matches on LastName and normalized PhoneNumber.
2. Exact matches on normalized Phonernumber, EmailAddress and Country.
Your team has added activities based on the event ticket and survey response extracts. Survey responses have been semantical^ mapped to the Feedback activity type, and event tickets have been semantically mapped to the SalesOrder activity type.
Your team has not created any measures or segments.
Your team has configured an export of the ConflationMatchPairs entity and attached it to a connection to the corporate data lake “exports” container.
In addition to the Enhanced Addresses enrichment mentioned previously, your team has configured a custom import enrichment that reads a file from a third-party vendor’s SFTP server.
BYE uses Dynamics 365 Marketing s outbound marketing campaigns to send two types of commercial emails:
1, Post-purchase follow-up email – This email is sent the day after a customer purchases an event ticket. It contains generic information about upcoming events for the region. BYE leadership wants to replace the generic content with a personalized set of recommended event types the customer might want to consider. There are no restrictions on the types of events that can be recommended.
2. Post-event email – This email is sent the day after an event concludes, and it includes a link to the customer survey.
1. A significant number of BYE customers are duplicated in the regional file extracts. Manual review of a random sampling of duplicates returned the following findings:
a. Duplicate records frequently have different values for street address such as inconsistencies in spelling and abbreviations for common words like “street” “avenue.” “drive.’ etc.
b. Duplicate records typically share the same values for last name, email address, city, state or province, postal code, and country.
c Duplicate records typically do not share the same values for first name.
2. An automated process in the East regional CRM system recently updated the state or province component of several thousand customer’s addresses with incorrect values. All other components of these customer’s addresses are still presumed correct.
3. An automated process in the Central regional CRM system recently deleted the first name values for several hundred customers.
The data science team has built inference pipelines in three different workspaces. Your user account has the following access:
* Owner access to BYAEAML01
* Reader access to BYAEAML02
* User Access Administrator access to BYAEAML03
The table below shows the published pipelines in each workspace.
The marketing team requests your team plan for two updates to BYE’s Customer Insights instance:
1. Stop using the Enhanced Addresses enrichment and start using the Azure Maps enrichment.
2. Start sending an export of customer data to the same data broker that currently provides the custom enrichment. This would use the same SFTP server as the existing enrichment.
AdventureWorks website page views for authenticated ecommerce customers and cycling club members are stored in a CDM folder named WEBSTATS that is stored in an Azure Data Lake Gen 2 container named WEBDATA119 in the Central US region This folder contains an entity named PageViews that includes the following fields: emoitaddress, pagers. visit date, productcatvgory.
AdventureWorks uses Dynamics 365 Sates as its sales CRM application, and its data is stored in Microsoft Dataverse.
Cycling club event logistics and member attendance are managed in an Azure SQL database. Attendance is tracked in a single table named EventAttendec that includes the following fields: emailaddress, eventuate, eventaddress, eventcity, eventstate. eventpostalcode. lastupdated (timestamp).
The GubMember entity contains records of existing customers, as well as individuals who have not made a purchase from AdventureWorks. There are approximately 500.000 records in this entity, and there are approximately 5 million records in the EventAttendee table.
AdventureWorks currently has a single Dynamics 365 Customer Insights environment. This environment is named ADWProd, and it is configured as a B-to-C production environment in the West USA region.
The following data sources have been configured:
1. To ingest the LoyottyMember. EcommerceCustomer and ClubMember entities descended in the Data Sources section of this case study.
2. For Dynamics 365 Sales contact records using a Dataverse managed data lake connector.
3. For the EventAttendee table using a Power Query connection.
Profile unification has been configured to perform map, match, and merge for non-primary key attributes with identical semantic types from the LoyottyMember, EcommerceCustomer and GubMember entities. Creation dates and timestamps were intentionally left unmapped. Some additional attributes were accidentally excluded from the merge process. These attributes are listed in in the Pain Points section of this case study.
The search and filter index has been configured to include FutlName, City, State and PhoneNumber as search fields. The Share field is enabled for fileting based on frequency and will show up to 10 options.
All members of the AdventureWorks marketing team have been granted access to the ADWProd environment.
The AdventureWorks leadership team has identified several issues that need to be addressed to support current growth and ensure customer senior
1. Attributes for first name and postal code were included in the mapping process, but they were accidentally excluded from the merge process.
2. Multiple copies of printed catalogs are being sent to members of the same household resulting in wasted printing and mailing costs.
3. A segment export to Dynamics 365 Sales has been configured, but the sales team reports the associated marketing lists has zero members.
4. Ingesting the EwntAttendee table takes significantly longer than ingesting any other data source, and it causes significant delays for completion of scheduled refreshes.
1. Only one copy of a catalog should be sent per household. AdventureWorks defines a household as all customers who share the same last name, street address, city and state.
2. Sales users are expected to call customers who are included in the loyalty system but do not have a populated email address in the loyalty system.
3. All marketing emails must include the customers first name in the subject field, if it is available in the source data.
4. Marketing team members must be able to search for customer profiles in Microsoft Dynamics 365 Customer Insights by full name, email address, phone number, city, state, and postal code.
5. All members of the marketing team who can access a production Dynamics 365 Customer Insights environment should also have access to its corresponding testing environment.
6. Data exports and scheduled refreshes are not allowed in test environments.
Data Source
AdventureWorks Cycles uses Customer Insights to connect to data from three different sources to generate a unified customer record. The data ingestion has been done for the initial data load. There are three data sources containing customer profile data loaded to a dedicated storage account and container in the Azure Data Lake:
Loyalty data source: This data source contains customer profile information from in-store purchases.
– loyalty.member.csv: srcid (primary key), firstname, lastname, middlename, fullname, addressstreet, loyalty_email, city, zipcode, state, homephone, datecreated, timestamp Ecommerce Data source: This data source contains customer profile information from online purchases.
– ecom.member.csv: ecid (primary key), firstname, last name, fullname, email, homephone, streetaddress, city, zip, state, datecreated, timestamp Cycling Clubs Data Source: This data source contains customer profile information for members of Cycling clubs.
– cclubcust.csv: ccid (primary key) firstname, lastname, full_name, email, main phone, streetaddress1, city, zip_code, state, datecreated, datecreated, timestamp The Loyalty data source contains the largest and most trusted dataset. It is considered the Primary Source followed by Ecommerce and Cycling Clubs Data Sources.
All three data sources share common customer demographics. Map, Match, and Merge (M3) rules within audience insights are applied accordingly to generate a unified customer record.
Additionally, there are three data sources that contain customer cellphone numbers for Loyalty, Ecommerce, and Cycling Club data sources that have been loaded to the Azure Data Lake but have not been ingested into audience insights.
cellPhone_loyaly.csv: srcid (primary key), cellphone
cellPhone_ec.csv: ecid (primary key), cellphone
cellPhone_cc.csv: ccid (primary key), cellphone
Pain Points
The AdventureWorks Cycles leadership team identified several pain points that need to be addressed immediately to support current growth and ensure customer satisfaction.
Lack of strategy for refreshing the customer data in the audience insights. There is a considerable effort needed to build pipelines to flow the incremental data updates into the Azure Data Lake so it can be ingested and processed in audience insights.
Customer Service reps cannot search for customers efficiently in audience insights which affects the customer satisfaction. Also, they do not have valid cell phone numbers for customers since it is not part of the profile.
The Sales team uses the Dynamics 365 Sales app but are not able to use segments generated in audience insights to generate marketing lists.
Marketing campaigns often sound redundant and inefficient as the same messaging is being sent to multiple members of the same household.
The Marketing team cannot create fully personalized communications due to missing Full Name in the unified customer record.
The test team is complaining that they do not have a dedicated UAT environment where they can test features before they are deployed to production.
Project Goals
Create a strategy to implement incremental data refresh in prod audience insights that reads data from Azure Data Lake Gen 2. In parallel configure incremental refresh in one of the non-production audience insights where all the data sources are available, loaded from Azure SQL database, through Power Query to audience insights instance. This will allow some testing of the incremental refresh functionality to be completed while the long-term strategy is being finalized.
Implement necessary changes to address the remaining pain points identified during the Leadership Team meeting.
Detailed Requirements
Pain Points
Configuring incremental refreshes for all customer data profiles as follows:
– Incremental data refresh should be configured for member tables only
– Timestamp data and time field should be used by the system to check when the record was last updated
– All three tables should be refreshed every two days
Adding additional data sources and search fields to audience insights
– Ingesting Cell phone data- the requirement is to keep the name of the date sources aligned with the design document. See section 1 for more details.
– Furthermore, to get a quick snapshot of the quality of data, data profiling should be enabled for the phone fields only
– The following fields from the unified customer record should be added to index: Last Name, Full Name, Email, Cell Phone, Street Address, DOB Ability to use segments from the audience insights to generate marketing lists
– The Sales team needs to generate a marketing campaign based on segment of customers who have a Loyalty email. (loyalty.email) Ability to group customer profiles into a household cluster for purpose of generating targeted marketing communication
– A household cluster is defined as customers who share Last Name, Street Address, City, Zip Code and State Adding Full Name field to the unified customer record
– Full Name is a merged field with the following merging policy
a. loyalty.member.fullname
b. ecom.member.fullname
c. cclubcust.csv.full_name
Creating a sandbox environment that mirrors the current development environment
– Create a sandbox environment called UAT1 and copy configurations from env. “DEV1”.
a. Note: there is also an existing environment called “Dev” and it is not configured correctly and should not be copied
