IM 310 Week 5 Data Models for Business Analytics Example

Will This Plan Renew? Using a Veterinary Group's New Data Models for Business Analytics

[Student Name]

University of Phoenix

IM/310: Data Analytics & Modeling

Week 5 Assignment

[Instructor Name]

[Date]

Lone Star Pet Health, its clinics, data and results are composites written for a model paper.

Over four weeks, this course has followed Lone Star Pet Health from scattered systems to an integrated warehouse. Week 1 mapped six systems and designed a layered architecture. Week 2 built conceptual and logical models with shared definitions and a common service code list. Week 3 normalized wellness plan data. Week 4 designed a star schema for visits and plans with conformed dimensions. This final paper uses that foundation for business analytics.

Kinds of Analytics

Chen et al. (2012) described how business intelligence grew from warehouse reporting on structured data into the analysis of web, mobile and sensor data, and emphasized that analytics depends on data management foundations as well as analytical methods. Descriptive analytics summarizes what happened, through reports and dashboards. Predictive work forecasts likely outcomes with statistical and machine learning models. Prescriptive analytics recommends actions, often by combining predictions with business rules or optimization. Lone Star needs all three, starting with the first.

A Descriptive Dashboard

The owners' original question from Week 1, where dental care earns the most per visit, can now be answered the morning after any day. A dashboard built on the visit fact table shows revenue, visits, revenue per visit and dental revenue per dental visit by clinic and month. It shows that dental revenue per visit ranges from $310 to $520 across clinics. The difference is driven mainly by whether clinics perform extractions during cleanings or refer them, a finding that led the owners to review referral practices.

A Predictive Model

Wellness plans renew annually, and about 31 percent did not renew last year. The group wants to know which plans are at risk so front desk staff can contact those clients before renewal. Shmueli and Koppius (2011) distinguish predictive models, built to forecast new cases accurately, from explanatory models, built to test causes, and note that predictive models should be judged on how well they predict data not used to build them.

A logistic regression model was built from 14 months of plan history using the warehouse's plan fact table and conformed patient and client dimensions. Predictors included visits in the past six months, share of included services used, species, age group, number of pets the client has on plans and distance from home to clinic. The model was built on plans that came up for renewal in the first ten months and tested on the last four. In the test period, the fifth of plans with the highest risk scores contained about 60 percent of the plans that did not renew.

The strongest predictor was the share of included services used: clients who had used less than a third of their plan's services by month nine were much more likely to let it lapse, apparently because they felt they had not received value.

The plans most likely to lapse were the ones whose owners had forgotten what they had paid for.

A Segmentation

Clustering clients on visit frequency, spending, services used and number of pets produced five segments: multi-pet households with steady visits, single-pet wellness-focused clients, sick-visit-only clients, high-spending senior-pet owners and infrequent clients. Managers use the segments to tailor reminders and offers, for example promoting dental plans to senior-pet owners and puppy packages to new households.

Checking the Dashboard's Numbers

Before the owners relied on the dashboard, its figures were reconciled with the accounting system for three months. Revenue totals matched within 0.3 percent for every clinic, with the small differences traced to refunds recorded on different dates. This check matters: a dashboard that disagrees with the books, even slightly, quickly loses managers' trust, and the warehouse's value depends on that trust.

Using the Segments Carefully

Segments are a convenient summary, but they can be misused. A client labeled "infrequent" may simply have a young, healthy pet. Managers are encouraged to use segments for planning reminders and offers, not to judge or rank clients, and segments will be refreshed quarterly as visit patterns change.

How the Design Made This Possible

Each analysis depends on earlier design choices. The common service code list from Week 2 makes service usage comparable across clinics, which the renewal model relies on. Group-wide client identifiers allow multi-pet households to be recognized across clinics. The fine grain of the visit fact table allows any summary the dashboard needs. Conformed dimensions let the plan and visit fact tables be analyzed together (Kimball & Ross, 2013). Without these choices, the same analyses would require the four days of manual spreadsheet work described in Week 1, and would still be unreliable.

From Prediction to Action

The renewal model supports a prescriptive rule: at month nine, front desk staff call clients on high-risk plans who have used less than half their included services, offering to book the remaining visits. A test at four clinics will compare renewal rates with four similar clinics that do not call yet, so the effect of the calls can be measured.

Governance and Privacy

Predictions about individual clients are sensitive. Only clinic managers and front desk leads will see risk scores, and only for their own clinics. Scores will never be shown to clients or used to change prices. The model will be retrained every six months and checked for accuracy against the latest renewals, and the operations director, as data owner, approves any new use.

Limits

The model was built on 14 months of data, including the period when several clinics joined; patterns may shift. Segments are descriptive and may not predict future behavior. The dashboard depends entirely on the nightly integration, which must be watched closely.

Conclusion

Lone Star's investment in architecture, shared definitions, normalized operational tables and a dimensional warehouse now pays off in analytics: a dashboard answering the owners' questions, a renewal model tested on new data and a client segmentation. Each depends on design choices made earlier in the course. With governance and testing in place, the group can use its data to keep more clients and grow its services.

References

Chen, H., Chiang, R. H. L., & Storey, V. C. (2012). Business intelligence and analytics: From big data to big impact. MIS Quarterly, 36(4), 1165-1188. https://doi.org/10.2307/41703503

Kimball, R., & Ross, M. (2013). The data warehouse toolkit: The definitive guide to dimensional modeling (3rd ed.). Wiley.

Shmueli, G., & Koppius, O. R. (2011). Predictive analytics in information systems research. MIS Quarterly, 35(3), 553-572. https://doi.org/10.2307/23042796