PetPlan Pet Insurance Sarikka Raman PetPlan Pet Insurance is a pet - - PowerPoint PPT Presentation

PetPlan Pet Insurance

Sarikka Raman

PetPlan Pet Insurance is a pet insurance company that specializes in coverage for cats and dogs. PetPlan Pet Insurance offsets the high costs of pet health care by offering health insurance policies that include coverage for illnesses, injuries, veterinary exam fees, and cancer treatments.

About

PetPlan Pet Insurance

Interaction with Client

Current system & problems

After consulting with the company:

• Delay between trial period and renewal

period

• Currently only offer coverage to cats &

dogs

• Unorganized claims and treatments

○ Need to implement a limit on the days between treatment and claim

Database Design

• Policyholder Loyalty Form

○ Referrals, Comments & Concerns ○ Customer Rewards

• Pet Claim Form

○ Reimbursement for costs incurred by treatment ○ Only applicable to one pet ○ Multiple treatments

• Free Trial Form

○ Continuity of the trial period to the coverage period

• Pet Medical History Form

○ Necessary to make valid quote & decide policy plan

Forms and Relevance

Policyholder Loyalty (PolicyholderLoyaltyForm ID, Date, Policyholder ID, Credit Card ID) Policyholder (Policyholder ID, First Name, Last Name, Phone #, Email, Address, City ID, SS#) Credit Card (Credit Card ID, CC#, Expiration Date, Security Code, Bank) Pet (Pet ID, Pet Name, Category ID, Breed, Sex, DOB, Age) Policyholder’s Pets with Coverage (Policyholder’s Pets with Coverage ID, PolicyholderLoyaltyForm ID, Pet ID, Policy Number) Policyholder Comments and Concerns Form (PolicyholderLoyaltyForm ID, Policyholder Comments and Concerns ID, Date, Comments) City (City ID, State, ZIP) Category (Category ID, Description) Pet Claim (Pet Claim Form ID, Policy #, Date, Policyholder ID, Pet ID, Total Reimbursement Amount) Pet -Treatment (Pet Claim Form ID, Treatment Form ID) Treatment (Treatment Form ID, Date, Hospital/Clinic ID, Description/Diagnoses, Cost) Hospital/Clinic (Hospital/Clinic ID, Hospital/Clinic, Hospital/Clinic Phone #, Hospital/Clinic Address, City ID) Free Trial (FreeTrialForm ID, Trial Code, Policy #, Policyholder ID, Pet ID, Date) Promo (Trial Code, Source) Pet Medical History (PetMedID, Date, PolicyholderID, PetID) Previous Vet (PetMedID, Hospital/ClinicID, Date, Condition Description) Breed (Breed ID, Description) Policy (Policy #, Name) Policyholder Referral Form (Policyholder Referral ID, PolicyholderLoyaltyForm ID, Date Referred, # of pets)

Class Diagram

Example Queries

Query 1

List the policyholder IDs

• f those that took

between 1 and 80 days to file a claim for pet treatment.

Query 2

What is the average age

• f a pet with coverage?

Query 3

What is the sum of all costs for dog shots?

Query 4

Which customers had claims more than $250?

Query 1: List the average number of days it took for each pet’s

policyholder to file a claim for treatment in ascending order. (Display PolicyholderID, Pet ID, PetName, Days Between Treatment and Claim)

• Expression: # of days between treatment and claim →

([PetClaim.Date]-[Treatment.Date])

• Pet Plan Pet Insurance can regulate and restrict the days between filed claims or offer

deductions/promotions for those that do so in a specific time period.

QbE: Output:

PolicyholderID Pet Name Pet ID Average Days Between Treatment and Claim 5 Sprinkles 5 2 9 Reese 11 3 4 Cloud 4 36 8 Gogo 8 65 10 Rex 12 83 3 Spot 3 97 1 Susie 1 131.66667 6 Doc 6 138 Field: PolicyholderID Pet Name Pet ID Average Days Between Treatment and Claim: [PetClaim.Date]- [Treatment.Date] Table: Policyholder Pet Form Pet Form Total: Group By Group By Group By Avg Sort: Ascending Show: X X X X Criteria >1 Or:

Query 2: What is the average age of a pet with coverage?

• Pet Plan Pet Insurance can use this information to adjust its marketing scheme to

attract people with younger pets for a greater LTV of each pet with coverage.

AvgAge 5.28

Field Age PetClaimForm ID Table PetForm Pet Claim Total Avg Where Sort Show X Criteria

QbE: Output:

Query 3: What is the sum of all costs for dog shots?

Field: Description/ Diagnosis Cost

Description

Table: Treatment Treatment Category Total: Group By Group By Group By Sort: Show: X X X Criteria Like “*shot*” “dogs” Field Cost Table Individual costs dog shots- Anthony Total Sum Sort Show X Criteria

QbE:

SumOfCost $2230

Output:

Query 4: What customers had claims more than $250?

• Pet Plan Pet Insurance can use this information help see which animals/customers are

high risk and can assess how to move forward whether it be put them in a high risk category or raise their rates

Field LastName Cost Table Policyholder Treatment Total Group By Where Sort Show X X Criteria >250

Last Name Cost DelRuiz $345 Mack $340 Mack $345 Ong $350 Ramon $345 Turner $340

QbE: Output:

Conclusion

• Easily add new categories of animals to

system ○ Expand beyond cats & dogs with ease

• Reduce the waiting time between trial

and renewal periods ○ Smooth transitions between coverage

• Offer rewards and receive feedback
• Implement restrictions on the time

between treatment and claim

Solutions