Campaign Discrepancy Report
Project Goal and Stakeholders
During monthly campaigns, a recurring task that the team needed to conduct was to check for data discrepancies that occur between order data extracts Platform Seller Center Accounts (e.g. Lazada and Shopee) and the data that’s ingested into our Azure database. At the time, there was already an existing campaign tool that was set up using VBA Macros that helped monitor discrepancies in the total Order Count and total Gross Merchandise Value (GMV).
While this was already a big help to them that enabled them to check all active stores in shop checking for discrepancies, a significant amount of time was still spent on discrepancy investigations. Identifying the Order IDs that were causing discrepancies still took a lot of manual effort. Although we were also collecting information on what the total GMV discrepancies were, it wasn’t clear what was necessarily causing them. I was assigned to take on introducing these changes to the campaign tool, and as a result, I needed to learn how to do this through VBA Macros.
Approach and Methodology
CONSULTATION
At the time this project was assigned to me, I was fortunate enough to still be working with the individual who developed the initial version of the campaign tool. I made sure to invest time at the beginning to have multiple consultations on how the tool is utilized from a user experience standpoint, how the VBA code enabled it to operate, and to identify what were the gaps in the tool that would be relevant to include in the updated version.
REPORT DESIGN AND CODING
I then spent time designing how the report would look like, as we essentially wanted the tool to automatically fill the newly created report with order details from both the platform and our azure extracts. What helped in this step of the process was taking the time to map out explicitly what a discrepancy investigation would look like when done manually, such as identifying what data needed to be copied and pasted into the main report, determining what transformations needed to occur and what formulas I needed to execute.
Sample of the Discrepancy Report that we have been able to automate through the project
Through this step, I was also able to identify categories for the different discrepancies I was able to observe in my initial manual investigations. I needed to ensure that when these discrepancies were added up, it would help explain why there were GMV discrepancies whether or not the total order count when comparing both sources of data was consistent.
Additionally, I also wanted to make sure that from a user experience standpoint, I didn’t introduce anything new or complicated that would make the process of shop checking more complicated. This meant that I needed to determine at which part of the tool’s existing VBA logic would I be able to add additional sections.
We wanted to generate the discrepancy report without necessarily adding extra steps on the end of the user. As a result, it became necessary to map out at what point I could add in the process of generating the report.
Once I had all of the information I needed, I then proceeded to convert the entire discrepancy investigation process line per line into VBA code. This was coded into the tool through an additional module, which you can review below:
TEAM ONBOARDING
Since I was introducing a new update to the tool, I spent some time to onboard the rest of my team how to read and interpret the added fields and reports. I also tried testing out the tool with them to gain any feedback they may have about the process and respond to questions they may have about it.
One of the significant insights we uncovered through the use of the discrepancy report was that it was possible that multiple orders could contribute to our GMV discrepancy observed across multiple stores.
Project Outcome
Overall, the project proved to be a significant help as it drastically improved the way we were able to conduct our shop investigations. The automated report was now able to account for 100% of all GMV discrepancies observed between our platforms and Azure data. What used to take around 30 minutes of manual work was now reduced to less than a minute due to the automation.
As a result of changes made to the campaign tool, escalations on discrepancies have now materialized into actionable steps that can be communicated to our stakeholders for improvement, whether this was our development team, or even to platforms as we had complete visibility over what drives discrepancy when comparing platform data and database data.