______________________________________________
Project Objectives:
The primary objective of this project was to implement an Automated Email Campaign to enhance stakeholder communication and increase Honda Dealer participation in meeting Battery Electric Vehicle (BEV) readiness requirements. The project aimed to provide clear, timely updates on milestone achievements within the Honda EV Charger Program, ensuring that dealers had adequate information to progress toward EV Allocation Eligibility.
Project Overview:
Jeremy Bosse led the initiative by defining the project scope and desired outcomes, focusing on boosting Honda Dealer participation and improving access to program information. A formal stakeholder analysis was conducted to identify key stakeholders, including Assistant Zone Managers, District Sales Managers, and Honda Dealers, and to assess how the project would impact them.
A gap analysis of the existing communication environment revealed that dealers lacked easy access to program documents and did not receive frequent or effective updates to encourage participation. To address these challenges, an automated system was designed to deliver personalized email communications, ensuring that stakeholders were well-informed at each stage of the program.
Deliverables:
- Automated Email Campaign – Sent customized emails to dealers based on their progress in the Honda EV Charger Program.
- CRM-Like Functionality – Designed the campaign to imitate a marketing CRM system, providing tailored messaging to each dealership.
- Integrated Communication System – Leveraged Microsoft Power Automate to manage communications across multiple Microsoft applications, eliminating the need for additional software.
- Program Documentation & Contact Information – Provided easy access to key documents and support channels within email communications.
- Stakeholder Communication Management – Maintained an up-to-date email log and utilized Microsoft Teams and video chat for internal communications.
Challenges
- Budget Constraints: The project had a budget of $0.00, requiring the use of existing resources without additional expenses.
- Technical Configuration: Setting up the Power Automate flow to function without errors required overcoming issues related to conditional operations, infinite loops, and duplicate email sends.
- Ensuring Stakeholder Engagement: Effective messaging strategies were needed to ensure dealers engaged with the communications and took action on EV readiness milestones.
Outcome:
The project officially launched in April 2023 after the approval of customized email verbiage and successful development and testing of the Power Automate flow. Since the launch, dealership participation has been actively monitored and continued until the EV Charger Program’s initial requirements deadline.
Success was measured by the increase in dealer participation and the frequency of communications about EV Charger Infrastructure requirements. The project is estimated to achieve 94-95% Zone EV Allocation Eligibility by the October deadline.
By effectively managing resources and leveraging automation technology, the project was successfully delivered within budget and significantly improved program participation and stakeholder communication.
______________________________________________
Power Automate Flow
With Power Automate, Jeremy Bosse achieved an automated process seamlessly integrating Microsoft Outlook and Microsoft Excel. Power Automate is a versatile cloud-based service that allows users to automate workflows and processes without extensive coding. Below is a step by step description of the mechanism of the Automated Workflow:
Trigger Event - Monitor Changes in Excel File (1):
- The flow begins by monitoring changes in an Excel file stored in a OneDrive folder.
Condition for Activation: Check Last Modified Time (2):
- A condition is set to check if the file’s last modified time is "greater than or equal to" a specified time. This ensures that the flow is triggered only when a genuine modification occurs.
List Rows from Excel Table (3):
- After the trigger condition is met, the flow lists rows present in a table within the modified Excel file.
Process Output for Customized Email Trigger (4):
- The output from the "List rows present in a table" action is used to determine the specific condition for sending a customized email.
Define Condition for Email Trigger (5):
- The email is triggered based on specific values from the data, which are aligned with program milestones.
Repeat for Each Milestone (6):
- This process is repeated for each milestone, ensuring that email notifications are sent at the appropriate times throughout the program.
Outcome: Automated Workflow
- The flow successfully automates a complex sequence of actions involving Excel, OneDrive, and Outlook, streamlining communication and process management.
"The flow begins by monitoring changes in an Excel file stored in a OneDrive folder, pulling metadata based on a specified time condition. This condition, set as "greater than or equal to" the file's last modified time, ensures that the flow is activated only when the file is genuinely modified."
"Subsequently, the flow lists rows present in a table within the Excel file, and the output is used to determine when to send a customized email. The email is triggered by specific values, defined within the condition, aligning with program milestones."
______________________________________________
AI Prompt Breakdown
Introduction and Context:
"I have an excel file named 'Charging Report' on OneDrive that has a sheet that houses data, and another sheet with a table named 'Tracker' that pulls data from the sheet that houses data using VLOOKUP formulas."
- This provided essential background information, clarifying that the Power Automate flow involves Excel, OneDrive, and VLOOKUP formulas. Understanding this context allowed ChatGPT to consider the dependencies and data relationships when forming a solution.
Structure of the Table and Data Flow:
"The table named 'Tracker' has rows with primary keys and fields that match the cell values from the sheet that houses the data. The table named 'Tracker' includes a field for email addresses."
- This explained how data is structured within the file, including primary keys and email fields, which is critical for ensuring that conditions within Power Automate correctly reference and process the intended data.
Description of the Power Automate Flow:
"The Power Automate flow begins with the trigger 'When a file is modified', then the following action is 'List rows present in a table', then the following action is 'Apply to each' that selects the dynamic content 'value' as an output from the 'List rows present in a table' step."
- This outlined the logical sequence of steps in the existing workflow. It helped ChatGPT understand how the automation is structured and pinpoint where changes need to be made to refine its behavior.
Condition for Email Triggers:
"I added a condition to the 'Apply to each' step that selects a dynamic content output from a field in the 'List rows present in a table' step and set the condition to 'is equal to' to match the row value which is also equal to the field name."
- This part described the logic behind the email trigger, which is fundamental to understanding what drives the notifications and where inefficiencies might arise.
Issue Description and Desired Outcome:
"I want the Power Automate Flow to only trigger when the modification is a save point, not all modifications, to prevent the flow from sending more than one email per modification."
- This explicitly stated the problem: unnecessary triggers caused by minor modifications. This guided ChatGPT's solution toward methods that ensure the flow only executes when a significant change (such as a save event) occurs.
This prompt provided a clear, structured problem statement, enabling ChatGPT to recommend solutions like comparing modified timestamps, using trigger conditions, and refining conditions within the "Apply to each" step to prevent unnecessary email notifications.
______________________________________________
Program Milestones
Not Started (Milestone 1):
- Dealers have not started planning or scheduling infrastructure installation activities
Construction or Renovation (Milestone 2):
- Dealers are currently undergoing facility renovations (delaying program participation).
Construction or Renovation Utility Delays (Milestone 3):
- Delays occurring regarding the current facility renovations, delaying program participation.
Site Walk Scheduled (Milestone 4):
- Dealers have scheduled for a contractor to conduct a site-walk for future infrastructure installation.
Site Walk Completed (Milestone 5):
- Dealers have completed their site walks with a verified contractor.
EV Chargers Ordered (Milestone 6):
- Dealers have ordered infrastructure equipment including wall chargers and lifts.
Permitting (Milestone 7):
- Dealers have obtained the necessary permits to install EV infrastructure.
Install in Progress (Milestone 8):
- Dealers are undergoing facility updates to accommodate EV product line allocation and staging.
Install Complete (Milestone 9):
- EV charger infrastructure have been successfully installed and are readily available.
2024 Requirements Met (Milestone 10):
- All requirements for the EV Charger infrastructure have been met.
EV Chargers Online (Milestone 11):
- Chargers are plugged in and are hooked up to a power supply.
______________________________________________
Key Results
Dealers voiced pre-launch concerns about EV Allocation Eligibility. The graph reveals most were in "Not Started" status initially. Post-launch, significant progress occurred, notably in Zone 4, inspiring Zone 3 to adopt the campaign for their dealers' advancement.
This Chart shows a count of Zone 4 Dealers against each milestone within the EV Charger Infrastructure Program at the beginning of the program.
This Chart shows a count of Zone 4 Dealers against each milestone within the EV Charger Infrastructure Program at the August priority deadline.
This Chart shows a percentage share of Zone 4 Dealers against each milestone within the EV Charger Infrastructure Program at the beginning of the program.
This Chart shows a percentage share of Zone 4 Dealers against each milestone within the EV Charger Infrastructure Program at the August priority deadline.
