HW 7 - Segmentation AnalysisThe tables in the attached file show a cross-tabulation of frequent -flier mileage categories (high, med, low) by Age-category (younger, older). A travel offer was made to 5000 people in the analytic sample•Table 1 shows the counts of the 5000 people in the analytic sample.•Table 2 shows the counts as a percentage of the 5000 people in the analytic sample•Table 3 shows the number of orders placed for the travel offer.•Table 4 shows the response rate for the travel offerHW:1.Create a new table showing indexes of observed response rates, relative to the overall rate. Then based on the index table you created, create 3 segments labeled as follows (if nec., refer to textbook chapter 7, and slides from week 3, for a refresher on Indexes)2.Segments: Yes (index >= 120), Maybe (index between 100-120), No (index <=100)3.If the entire customer file is 100,000 customers, how many customers are there in each of the “Yes”, “Maybe”, and “No” categories?4.What might be the observed response rate for the “Yes” segment that you want to market to?5.Past marketing efforts (before this new segmentation) showed an overall response rate of 5.24%. If new offers are to be sent and targeted to the “Yes” segment, what is the expected response rate for the “Yes” segment? And what is the response rate index?6. How many orders to the travel offer would you expect? Attached excel is the file of cross-tabulation of frequent -flier mileage categories. Attached slides(week8) is the related contents weekly slides. Please see the slides first and then do the chart for the highest accurate.

Part 2 peer video: https://www.youtube.com/watch?v=y6ZlpCAPQpg&feature=youtu.be This exercise allows you to examine how the quality of hospital care is evaluated through data obtained from electronic health records.  This exercise is discussed in several health administration courses, including courses on quality improvement, informatics, or statistics. In practice, the need to analyze these types of data arises in improvement teams, where managers, clinicians and sometimes patients work together to systematically improve healthcare processes. You can find the solution here and here. Part 1: In this assignment, you are asked to examine if prophylactic antibiotics are discontinued within 24 hours after the end of surgery.  Instead of providing you with the data, we ask that you download the data on the web so that you learn more about the source of the data and the peculiar organization of the data.  Hospital Compare provides information on many quality indicators.  In this assignment, you are asked to focus on the failure to stop the use of antibiotics post-surgery. The Hospital Compare web site provides the following rationale for why this measure of quality is helpful:  "A goal of prophylaxis with antibiotics is to provide benefit to the patient with as little risk as possible. It is important to maintain therapeutic serum and tissue levels throughout the operation. Intra-operative re-dosing may be needed for long operations. However, the administration of antibiotics for more than a few hours after the incision is closed offers no additional benefit to the surgical patient. Prolonged administration does increase the risk of clostridium difficile infection and the development of antimicrobial-resistant pathogens."  To complete this assignment complete the following tasks: a.Download Hospital Compare data for the years starting from 2015 till now.Merge the file "HQI_HOSP_TimelyEffectiveCare" across all databases for different years. b.Select measure ID "SCIP_INF_3"; this measure refers to prophylactic antibiotic use. Select to work with data from "Southeast Alabama Medical Center"; this is provider ID "010001". c.Calculatethe rate of overuse of antibiotics.In these data, the field "Score" refers to the number of patients with overuse of antibiotics.The field "Sample" refers to all selected surgical patients with no evidence of prior infection. The ratio of Score to Sample provides the rate of overuse of antibiotics. d.Construct a control chart showing the rate of overuse of prophylactic antibiotics over time in Southeast Alabama Medical Center.Download the needed data using the following databases (if new data is available, please use them) You can find the solution here Click for more options. The student peer video shows that data across the years us merged using SQL. You can do the same merger without using SQL. Open the CSV files (you can ignore access files for old years.) Filter the data by the measure you are interested in Copy the sheet with the relevant filtered data from each CSV file into a single sheet within the new Excel file. In a sheet titles "ALL" inside the Excel file, use cell reference to bring the data in different sheets to the "ALL" sheet. Do your analysis on this sheet.  Part 2: Nursing home and hospital administrators implement many initiatives to reduce falls.This problem shows how these initiatives can be measured and evaluated.  Prepare a risk-adjusted control chart fordata Click for more options  on falls in a nursing home Please use the following peer video as a reference for this assignment.You can find the solution here Click for more options. Part 3: The following data were obtained on post-surgical infection rates.Are we having more infections than expected from the patients' conditions? You can find the solution here Click for more options.    Week Risk of Infection for   each Patient Number Infected   1 0.9 0.8 0.7 0.8 0.9 0.85 6   2 0.7 0.8 0.7 0.6 0.8 0.9 5   3 0.8 0.95 0.92 0.87 4   4 0.5 0.6 0.66 0.67 3   5 0.3 0.4 0.5 0.4 0.5 0.34 2   6 0.3 0.4 0.5 1 Part 4: Download Hospital Compare data.In these files, the denominator indicates the number of patients.Whether the hospital had above average payments are reported in different fields: [Compared to National], [Category], and [Payment Category] Select data that meet the following conditions: a.Select the "PAYM_30_AMI" measure b.Select hospitals that in the denominator do not have "Not Available". Construct a control chart showing the probability of change over time. To assist, we have downloaded some of the necessary files.You can add to these additional more recent files from the Hospital Compare site.Since the data on AMI payments does not change in the files, you can save time and read only the following files into a new database:    File Database Field Start End   HQI_HOSP_AMI_Payment   HOSArchive_20150122 Compared to National 2010 2013   HQI_HOSP_Payment HOSArchive_20150716 Category 2011 2014   HQI_HOSP_PaymentAndValueOfCare HOSArchive_20161110 Payment Category 2012 2015   Include other data for more recent years after 2016 Some of you do not have Microsoft Access. Early files in Hospital Compare used Microsoft Access and later files used CSV (a file format that opens with Excel.) To remedy this situation, I am replacing Microsoft Access files with Excel Files. If you have to look at only AMI quality measures you need to use the filter feature within Excel.  Use these files:  HQI_HOSP_PaymentAndValueofCare20161110 HQI_HOSP_AMI_Payment20150122 HQI_HOSP_Payment20150716