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 About insurance policies that include coverage for illnesses, injuries, veterinary exam fees, and PetPlan Pet Insurance cancer treatments.

Interaction with After consulting with the company: Client Delay between trial period and renewal ● period Currently only offer coverage to cats & ● Current system & problems dogs Unorganized claims and treatments ● ○ Need to implement a limit on the days between treatment and claim

● Policyholder Loyalty Form ○ Referrals, Comments & Concerns Customer Rewards ○ ● Pet Claim Form Reimbursement for costs incurred ○ by treatment ○ Only applicable to one pet Multiple treatments ○ Database Design ● Free Trial Form Continuity of the trial period to the ○ Forms and Relevance coverage period ● Pet Medical History Form Necessary to make valid quote & ○ decide policy plan

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 Query 2 Query 3 Query 4 What is the average age What is the sum of all Which customers had List the policyholder IDs of those that took of a pet with coverage? costs for dog shots? claims more than $250? between 1 and 80 days to file a claim for pet treatment.

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

Query 2: What is the average age of a pet with coverage? QbE: Output: Field Age PetClaimForm ID Table PetForm Pet Claim AvgAge Total Avg Where 5.28 Sort Show X Criteria ● 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.

Query 3: What is the sum of all costs for dog shots? QbE: Output: Field: Description/ Cost Description Field Cost SumOfCost Diagnosis Table Individual costs dog shots- Anthony Table: Treatment Treatment Category $2230 Total: Group By Group By Group By Total Sum Sort: Sort Show: X X X Show X Criteria Like “*shot*” “dogs” Criteria

Query 4: What customers had claims more than $250? Output: QbE: Field LastName Cost Last Name Cost Table Policyholder Treatment DelRuiz $345 Total Group By Where Mack $340 Sort Show X X Mack $345 Criteria >250 Ong $350 Ramon $345 Turner $340 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

● Easily add new categories of animals to system ○ Expand beyond cats & dogs with ease ● Reduce the waiting time between trial and renewal periods Conclusion ○ Smooth transitions between coverage Offer rewards and receive feedback ● ● Implement restrictions on the time Solutions between treatment and claim