Client, a leading brand from a top healthcare and medical products company, wants to establish an optimum marketing mix model and calculate the ROI for the marketing activities.
Lack of connections between multiple datasets
- One of the most common problems in marketing mix projects is having multiple of datasets without any direct linkage between them. In this project, there was no unique identifier (e.g., physician ID) in use consistently across datasets. This meant that we had create a bridge between multiple datasets using name matching (for both physicians and accounts) on multiple string variables, with a strong need for manual review of matches. This matching was required across multiple pairs of datasets.
- Furthermore, none of the datasets were in a common format and had to be pre-processed, cleaned and transformed using certain business rules to be ready for usage.
Volume discount program
- The client employed a Volume Discount Program based upon order quantity and not average historical usage. Hence, it made perfect sense for clinics to order quantities in bulk to enjoy the discount, even if their overall usage pattern wasn’t changing. We needed to account for this as a timing issue while doing the modeling.
- Furthermore, discount buckets were uniform across the country; there was no variation in discount offered to produce data to actually measure the impact of volume discount program
- One of the key issues was to identify accounts which are getting the commercial (VDP) discount and which “non-commercial” accounts enjoyed reduced pricing under a Federal support program. There was no single database which distinctly identified which accounts are commercial or non-commercial. (Non-commercial accounts were excluded from Volume Discount Program, but could purchase product at flat discount based on Federal subsidization.)
Different level of granularity of each channel
As expected in a multi-channel study, one of the key issues was different level of granularity of each data element in model:
- Sales – Account, Local ID, Ship-to ID
- Calls – Physician
- Volume Discount Program – Order level
- National / Print / Search / Digital Display – DMA
- Local TV – for selected DMAs
- Contract status – Physician or account address
Dual objective: Overall brand strategic and tactical guidance
Objective was not only to find ROI for each channel and optimize promotional expenditure, but also measure how specific channels performed in specific time intervals, in the presence of external factors like negative media publicity, sales force re-alignment etc.
The team leveraged the Axtria MarketingIQ™ platform to arrive at a solution for the client.
- Due to lack of common merge variable, Axtria deployed a “fuzzy matching” approach using Generalized Edit Distance to matching string variables. To make the matching more robust, Axtria developed basic pre-cleaning mechanisms like “Avenue” and “Av” to “AVE” to ensure that all strings have similar transformations applied before matching. This technique was followed across all datasets.
- To counter the bulk ordering and timing issue, Axtria developed rolling 6 months of unit average usage and used this as key dependent variable in model.
- Also, Axtria approach was to calculate “effective free units” for each account to determine the impact of these units on sales for the account. This approach allowed optimization of this channel which would have been quite cumbersome otherwise.
- To handle non-commercial accounts, the first step was to identify such accounts (not a trivial task). Axtria brainstormed with core client team and developed series of business rules to identify such accounts.
- Once these accounts were identified, we calculated “effective free units” using non-commercial discount rate on all orders made by such accounts. This avoided over-estimation of impact of discount in commercial accounts
- We assembled data after multiple discussions with core group and developed multiple business rules to individually treat each dataset and then assembled a master database for modeling.
- One of the key discussions was level of aggregation of dataset for modeling and we explored multiple options: Physician – month, Account / LID / SID – month, DMA – month, Annual models, Multi-stage model to leverage maximum variation of channel promotion across cross-sectional data.
- Axtria developed a multivariate regression model with following promotional variables:
- We introduced multiple cross-channel terms (like print x TV) in the model to estimate presence of interaction effects and their magnitude. Also, to test for the impact of changes in sales force structure during the analysis time window, we modeled pre- and post-change detailing impact to see whether there was any measurable difference in quality of detailing before and after re-alignment.
- We found the sales force, volume discount program, National print and National TV all to have significant observable impact on brand units. Some channels (e.g., print) were found to be effective only in specific market segments. Model suggested no significant impact for local TV.
- Several scenarios for resource allocation were run at total budget variations of -20% to +20% from historical. One significant finding was that optimization of spend across channels yielded as much revenue impact (at 10% lower total spending) than the historical, non-optimized budget.