Designed and Built a Dashboard to Measure the Marketing Campaign Effectiveness

Client was a technology giant who follows a business model which relied on partners to drive the sales of their products. They conducted quarterly marketing programs which provided funds to partners for their marketing activities. The funds were budgeted for partners during campaign launch and paid at end of campaign period based on the proofs submitted for various marketing activities conducted.

Business Challenge:

• The client wanted a consolidated report for the Proof of Execution received from various partners participating in the campaign.

• They also wanted a dashboard which illustrates the effectiveness of the campaign at various levels (such as overall versus by geo, across various marketing activities).

• The dashboard should also give insights into the various metrics such as investment, ROI, impact on sales etc.

TurnB Approach:

• Extracted the individual proof excel files and audited them to ensure that they followed the standard format.

• Created a master file by consolidating all individual files using an algorithm developed in Python.

• Checked and filled missing values based on partner profile and available elsewhere.

• Identified and corrected discrepancies observed in the fields where pre-determined inputs were specified.

• Devised logics for standardizing results of each metric to the required format and validated the same.

• Flagged outliers in the results provided and removed them from analysis.

• Formulated metrics that accurately depicted performance of the campaign.

• Modelled the metrics along with yearly comparisons for illustrating the campaign performance versus previous campaign.

• Designed a model to compare the various outputs versus Worldwide/Geo level benchmarks to provide insights on the performance for each partner.

• Designed the dashboard in an intuitive way that will enable marketing managers to snip and use the view for their leadership-level presentations.

• Incorporated a separate slicer for further deep dive into area-level insights.

• Provided a visual to depict the partner-metric level results that helped the client view activities and results for each partner in a single snapshot.

Implications:

• The client was able to get a complete snapshot of campaign performance in a single snip.

• The yearly changes in marketing metrics helped client to get a picture of the changing market dynamics.

• It enabled them to efficiently allocate funds to partners for subsequent campaigns, based on the results observed.

• The marketing managers were able to provide recommendations on marketing activities to the partners for next campaign based on results of this campaign.

• Comparison of business growth for partners in campaign versus non-participating partners bring forth the knowledge on impact of campaign for partner business.