Using mathematics to solve problems with real-world data excites me.

Background

Founded in 2016, Quality Health is a medical insurance company serving thousands of customers throughout the United States. They offer four different health insurance plans: bronze, silver, gold, and platinum, each with different premiums and claim coverage rates.

In 2019, the company launched 57 ads spanning twelve marketing campaigns, covering a wide range of topics such as wellness tips, affordability of plans, and preventative care. This project is a detailed analysis of the performance of each campaign, its impact on signups, and subsequent patient claims. It also provides strategic recommendations on how to allocate marketing budgets across future campaigns, guided by three primary objectives:

1. Raising awareness of Quality Health's brand
2. Increasing the number of customer signups
3. Identifying which campaigns attract high-value customers

Dashboards

Two interactive Tableau dashboards were developed to deliver insights into the company's key metrics. These dashboards help the marketing team understand and evaluate the performance of the various marketing campaign categories to date and allocate their marketing budget for the year.

Marketing and Signup Dashboard

Claims Dashboard

Definitions

1. #CoverageMatters
2. #HealthyLiving
3. #InsureYourHealth
4. Affordable Plans
5. Benefit Updates
6. Compare Health Coverage
7. Family Coverage Plan
8. Golden Years Security
9. Health For All
10. Preventive Care News
11. Summer Wellness Tips
12. Tailored Health Plans

• Impressions: The number of people who saw an ad for a marketing campaign
• Clicks: The number of impressions who clicked on an ad after viewing it
• Signups: The number of clicks who signed up for a health plan after viewing and clicking on an ad

North Star Metrics

• Marketing Metrics
  • Click-Through Rate (CTR): The percentage of impressions who clicked on the ad
    • CTR = (Total Clicks) / (Total Impressions) * 100
  • Cost Per Click (CPC): The average dollar amount spent obtaining one click
    • CPC = (Total Cost) / (Total Clicks)
  • Cost Per Impression (CPI): The average dollar amount spent on obtaining one impression
    • CPI = (Total Cost) / (Total Impressions)
• Signup Metrics
  • Signup Rate: The percentage of clicks who signed up for a health plan
    • Signup Rate = (Count of Signups) / (Total Clicks) * 100
  • Cost Per Signup: The average dollar amount spent on obtaining one customer signup
    • Cost Per Signup = (Total Cost) / (Count of Signups)
• Claims Metrics
  • Average Claim: The average cost per claim submitted
    • Average Claim = (Total Claim Amounts) / (Count of Claims)
  • Claim Count: The number of claims submitted

Summary of Insights

Marketing Metrics

Tailored Health Plans had the highest number of impressions (1.36 million) and the third lowest CPI ($0.0037), while Health for All had the lowest impressions (0.12 million) and the highest CPI ($0.0255). Despite Tailored Health Plans leading in impressions, the campaign had the second-lowest CTR at 6.7%.

Summer Wellness Tips had the cheapest CPC at $0.02. However, this is similar to other campaigns; all but one campaign had a CPC between \$0.03 and \$0.05.

Golden Years Security's CPC was the most expensive CPC ($0.48) and an extreme outlier. Additionally, it received few impressions (0.35 million) and had the lowest CTR (1.7%), making it one of the worst-performing campaigns overall.

Despite Health for All having the lowest impressions and one of the more expensive CPCs ($0.05), the campaign had an astonishingly high CTR (36.1%), nearly twice as high as the next best campaign, Benefit Updates (22.2%), and about 4x higher than average. These results are robust, considering that most campaigns had a 7-17% CTR.

Signup Metrics

The #HealthyLiving campaign had the highest number of clicks (127k), while Golden Years Security struggled and only received about 6k. Golden Years Security also had the highest cost per signup ($124.05) and one of the lowest signup rates (0.4%), which continues the pattern of cost inefficiency noticed earlier. In contrast, Health for All had the highest signup rate (8.2%) and the lowest cost per signup ($0.57).

Similarly, #CoverageMatters, Compare Health Coverage, and #HealthyLiving are among the better campaigns in terms of cost efficiency. They had above-average signup rates ranging from 2.9% for #HealthyLiving to 4.8% for #CoverageMatters and a low cost per signup ranging from \$0.65 for #CoverageMatters to \$1.25 for #HealthyLiving.

On the other end of the spectrum, Summer Wellness Tips, Affordable Plans, Benefit Updates, and Family Coverage Plan had meager signup rates ranging from 0% for Family Coverage Plan to 0.2% for Summer Wellness Tips and the cost per signup was high.

Interestingly, Family Coverage Plan received 1.11 million impressions but had no clicks or signups. This could indicate a possible data integrity issue and should be investigated.

Signups Over Time

When COVID began at the start of 2020, signups resulting from many campaign categories rapidly increased. The most successful were #CoverageMatters, #HealthyLiving, HealthForAll, and Compare Health Coverage, which experienced roughly 3-5x the number of signups from the previous month.

Compare Health Coverage was the only campaign to experience continued growth throughout the rest of the year, and signups spiked again in early 2021. Additionally, the campaign captured the highest number of signups for most months in 2021 during a time while other campaigns' signups stagnated or tapered off.

In general, signups for campaigns are now at or below pre-pandemic levels. Despite the record spikes early on, #HealthyLiving has returned to roughly the same number of signups in 2023 as before COVID, while #CoverageMatters and HealthForAll are now at all-time lows.

Historically, #HealthyLiving, HealthForAll, and #CoverageMatters are the top three campaigns in terms of signups, typically contributing ~70%. Despite record lows, the proportion of signups in recent months is about the same as always, and these campaigns still constitute about 70% of signups. In close 4th and 5th place are CompareHealthCoverage and TailoredHealthPlans, which bring in ~15% and ~10% of signups, respectively.

Claims Metrics

Claims counts for these four campaigns rose at the pandemic's start in early 2020 and plateaued in mid-2020. From there, counts remained consistent, except for Compare Health Coverage. This campaign continued to have more claims filed until peaking in May 2022 at $0.14 million, which was roughly twice as costly as the next campaign. Claim counts dropped sharply in the following months, and now all four campaigns see approximately the same number of claims as each other.

Interestingly, Compare Health Coverage has the least claims filed, but the average claim amount was quite high ($410.44), indicating that these customers tend to file much more expensive claims.

Note: there are 132 claims totaling $0.02 million where no campaign category is listed, which could indicate another data quality issue where some campaigns aren't accurately capturing customer signups.

Recommendations

• Increase Budget Allocation for High Performing Campaigns or Campaigns that Attract High Value Customers:
  • Health For All was one of the best-performing campaigns, and its ads are highly effective at generating clicks and signups. The campaign had the lowest cost per signup ($0.57), the highest CTR (36.1%), and the highest signup rate (8.2%). Unfortunately, those who don't see its ads won't sign up, and the campaign suffered from low impressions (0.12 million).
  • The marketing team should consider increasing the budget for this campaign. Run more ads and carefully select channels likely to reach larger audiences.
• Increase Budget Allocation for Campaigns that Attract High Value Customers:
  • #HealthyLiving and #CoverageMatters comprise 43% of claims made, and average claim amounts are less expensive. Consequently, customers who sign up from these campaigns are less costly and highly desirable.
  • The marketing team should consider increasing these campaigns' budgets to attract more of these customers.
• Leverage High Impressions, Clicks, and Signup Rates:
  • Tailored Health Plans' large number of impressions (1.36 million) is valuable. Although the campaign had a lower CTR (6.7%) and signup rate (1.2%), it was still effective at moving large numbers of customers through the marketing funnel because so many people viewed these ads.
  • #HealthyLiving and #CoverageMatters have high signup rates and effectively convert customers to a purchase. #HealthyLiving had the second-highest impressions, the most clicks, and the fourth-highest signup rate. #CoverageMatters had the third-highest signup rate.
    • While #CompareHealthCoverage had the second-highest signup rate, it also had the highest average claim amounts, and its customers are costly. Preference should be given to #HealthyLiving and #CoverageMatters, which had much more reasonable average claim amounts.
  • The marketing team should study campaigns with high CTRs and adapt any findings to campaigns with high signup rates. Small increases in their CTR could lead to many new signups.
• Adjust Strategy for or Divert Funds from Underperforming Campaigns:
  • Golden Years Security was incredibly cost-inefficient. The campaign had a CPC of roughly 12x above average, a cost per signup of 60x above average, the lowest CTR, and a low signup rate.
  • The marketing team should consult with business development to examine whether this market segment is desirable. If it's worth targeting, future campaign strategy needs to adjust to increase user engagement. Otherwise, funds for these ads can be diverted to higher-performing campaigns.

Dataset Structure

• Customers
  • Table grain: customer_id
  • 16,638 unique customers
• Claims
  • Table grain: claim_id
  • 49,998 unique claim ids
  • All claims filed between 2019 - 2023
• Campaigns
  • Table grain: campaign_id
  • 57 unique campaigns
  • 12 campaign categories