Workflow Automation: Unlocking Marketing Data
Project Type: Quick Wins
A multi-billion dollar telecommunications company needed to automate inefficient data collection processes from various 3rd-party data sources. Managing hundreds of excel files can be taxing on both the data analysts and the data storage system within a company.
Many of our client's platforms contain data related to marketing success or customer service interactions. In order to track data historically and create outside reporting in Power BI, the previous method required extensive report building, data exporting, and Excel file management.
AMEND was able to automate this process by connecting directly to the data sources' APIs and creating a pipeline to routinely pull data and store it in an Azure Database.
Overview
-
Company Overview
-
-
- Industry: Telecommunications
- Publicly-traded
- $1.2 Billion Revenue
-
-
-
Assessing the Landscape
- Manually exporting and transforming data within multiple new Excel files daily
- Lacked control of a lot of their data unless willing to pay for platforms to build the custom reports ($$$)
- Dozens of platforms tracked different metrics which were not able to be tied together cohesively
-
Identifying Opportunity
- Daily manual process (Excel)
- Desire for reporting outside of the platforms native offering (Power BI)
- Ability to connect to API and automate processes
Tools & Programs:
Platforms Integrated:
Results
(Within a two week sprint)
$150,000
Est. Time Savings Gain
Integration
Complete Data Centralization for Marketing & Customer Service Platforms
Access
Historical data and new metrics easily accessible by stakeholders.
The Solution
Microsoft Power Automate (MS Flow)
To automate Freshdesk (Customer Service Ticketing system) we created a custom API connector and used Microsoft Flow to build a routine workflow that pulled all customer interactions for the day.
The data was then piped into an Azure database for consumption by the Marketing and Analytics team for reporting.
Building an Automated Workflow (SSIS Package)
Liveperson, a customer chat support system, reports metrics on customer engagement and the response metrics of the customer support team. The system required manual exporting at least once a day, which resulted in lost productivity for the client's analytics team.
We created an SSIS package (automated workflow) and SQL Agent Job to automate pulling data directly from the API and pulled into new tables in SQL Server for consumption by the Marketing and Analytics teams for reporting.
The AMEND Assessment Process
Setting the Stage
Defined key deliverables and data points with internal stakeholders, reviewed the API documentation for each platform to understand what data was available, and researched methods and requirements to connect to the API.
Workflow & Data Storage (Azure)
Built data connections & developed data tables within the data warehouse.
Data Mapping, Review, & Schedule Job
-
- Mapped the API response to Microsoft Azure, reviewed with stakeholders to ensure all desired data points were captured, verified data accuracy, created a scheduled routine to run without a manual trigger.
