Data Analysis Methodology and Conventions

Data Analysis Methodology and Conventions IMS Consulting Group Training January 31 2012

What this course is about We will teach you how to make dynamic analyses with simple user interfaces to automate analyses • Simple user interface to select countries, drugs, therapy areas etc. • Titles and labels update automatically • Metrics are calculated instantly from the raw PADDS MIDAS extract • Charts are updated automatically IMS Consulting Group Data Analysis Training - January 17 2012

What this course is about More importantly we will teach you good practices to perform fast, accurate and insightful analysis of MIDAS sales data 5 Data Analysis Steps 1. Understand your therapy areas and countries 2. Extracting data from PADDS 3. Processing the data in Excel 4. Making dynamic tables 5. Checking the analysis IMS Consulting Group Data Analysis Training - January 17 2012

Agenda IMS Consulting Group Data Analysis Training - January 17 2012

Introductions • Please say: • Your name • Your office • What you want to learn today IMS Consulting Group Data Analysis Training - January 17 2012

Training objectives We want to enable our staff to perform common analyses quickly and accurately • Improve the speed and efficiency of data analysis, allowing consultants to spend more time on value-added activities • Improve accuracy and reduce the need for rework • Introduce the good habits of documentation and structure to help reuse, update or revise analyses at a later date • Teach a set of standard approaches, so that work can be understood and reviewed more easily by others • Give confidence to the attendees that they have the skills to be resourced across the full range of IMSCG projects IMS Consulting Group Data Analysis Training - January 17 2012

What you will learn This course is designed to teach practical skills and good practices for sales data analysis • A 5-step framework for undertaking MIDAS data analysis • Things to look out for to understand sales data for therapy areas, drug classes and countries • A recommended approach to pulling and processing MIDAS data • New techniques: • Use of functions to create dynamic tables • How to replicate tables for multiple countries / products • Some simple user interface tools (forms) • Standards for colours and formats that will improve consistency and readability across tools and projects • Good practices to work cleanly, quickly and accurately IMS Consulting Group Data Analysis Training - January 17 2012

Data analysis steps Today’s training is structured around 5 steps that are followed in almost all good MIDAS data analyses 5 Data Analysis Steps 1. Understand your therapy areas and countries 2. Extracting data from PADDS 3. Processing the data in Excel 4. Making dynamic tables 5. Checking the analysis IMS Consulting Group Data Analysis Training - January 17 2012

Training exercises During the course of today, we will follow a set of exercises to extract data from PADDS and create analyses and user interface PADDS Completed Analysis With User Interface IMS Consulting Group Data Analysis Training - January 17 2012

Project example The techniques demonstrated today can be used to create more sophisticated client deliverables IMS Consulting Group Data Analysis Training - January 17 2012

Project example More complex models still use the same principles IMS Consulting Group Data Analysis Training - January 17 2012

Course overview Understanding your therapy area and countries of interest is vital to minimise errors later on in the process 1. Understand your therapy areas and countries 2. Extracting data from PADDS 3. Processing the data in Excel 4. Making dynamic tables 5. Checking the analysis • Learning objectives: • How to use MIDAS and other resources to understand the therapy area and countries of interest so that you are equipped to pull the data correctly from PADDS • Key activities • Review and discuss key questions to ask about the data and the resources used to answer them • Quiz based exercise using MIDAS and other resources IMS Consulting Group Data Analysis Training - January 17 2012

Course overview With good knowledge of the therapy area and countries of interest the data can be correctly pulled from PADDS 1. Understand your therapy areas and countries 2. Extracting data from PADDS 3. Processing the data in Excel 4. Making dynamic tables 5. Checking the analysis • Learning objectives: • Best practice for pulling data from PADDS • Key activities • Review and discuss top tips for pulling data from PADDS IMS Consulting Group Data Analysis Training - January 17 2012

Course overview Following the extract from PADDS, the data must be processed before it can be analysed 1. Understand your therapy areas and countries 2. Extracting data from PADDS 3. Processing the data in Excel 4. Making dynamic tables 5. Checking the analysis • Learning objectives: • Good practice for processing MIDAS data in Excel • Formatting conventions to improve consistency across projects • Advice for how to work ‘cleanly’ and in a traceable way • Key activities • Review and discuss top tips and examples of best practice for processing data • Practical Excel exercise to process a raw MIDAS extract IMS Consulting Group Data Analysis Training - January 17 2012

Course overview Dynamic tables are a highly efficient and accurate way to analyse the processed data 1. Understand your therapy areas and countries 2. Extracting data from PADDS 3. Processing the data in Excel 4. Making dynamic tables 5. Checking the analysis • Learning objectives: • Practical techniques to create dynamic tables in Excel • Understanding of various different applications for dynamic tables and their power to save time and improve accuracy • Simple user interface techniques • Key activities • Review and discuss top tips and examples of best practice for developing dynamic tables data • 6 practical Excel exercises of increasing difficulty building dynamic tables and accompanying charts IMS Consulting Group Data Analysis Training - January 17 2012

Pivot tables Pivot tables are an alternative way to create dynamic tables and are often a fast and effective analysis tool due to time constraints pivot tables are not covered in this course Advantages of pivot tables: • Often very quick to set up • Eliminates some kinds of errors • Automatically eliminates rows for non-existant data* Disadvantages of pivot tables: • Not appropriate for client deliverables as hard to format attractively • Not as flexible as setting up a table from scratch • Charting forces a PivotChart (unless do a workaround) • Long timeframes can be a pain to set up (e.g. 84 months of data) *depending on settings IMS Consulting Group Data Analysis Training - January 17 2012

Course overview Once the analysis is complete the it is crucial to check you work for error 1. Understand your therapy areas and countries 2. Extracting data from PADDS 3. Processing the data in Excel 4. Making dynamic tables 5. Checking the analysis • Learning objectives: • Gain awareness of the different sorts of error that can occur • Lean various approaches for preventing, spotting and correcting the different sorts of error • Key activities • Review and discuss top tips and examples of best practice for error handling • Exercises for spotting and correcting various errors IMS Consulting Group Data Analysis Training - January 17 2012

ADHD Overview There are three different types of ADHD, depending on which symptoms are strongest in the individual • It is hard for the individual to organize or finish a task, to pay attention to details, or to follow instructions or conversations. • The person is easily distracted or forgets details of daily routines Predominantly Inattentive Type • The person fidgets and talks a lot. It is hard to sit still for long (e.g., for a meal or while doing homework). Smaller children may run, jump or climb constantly. • The individual feels restless and has trouble with impulsivity. Someone who is impulsive may interrupt others a lot, grab things from people, or speak at inappropriate times. • It is hard for the person to wait their turn or listen to directions. A person with impulsiveness may have more accidents and injuries than others. Predominantly Hyperactive-Impulsive Type Combined Type • Symptoms of the above two types are equally present in the person. Source: CDC ADHD Facts IMS Consulting Group Data Analysis Training - January 17 2012

ADHD Overview ADHD is one of the most common neurobehavioral disorders of childhood Percent of Youth 4-17 ever Diagnosed with Attention-Deficit/Hyperactivity Disorder: National Survey of Children's Health, 2007 • ADHD is usually first diagnosed in childhood and often lasts into adulthood • In Europe the prevalence is lower than the US which may be explained by lower awareness and use of different diagnostic tools Source: CDC State-based Prevalence Data of ADHD Diagnosis IMS Consulting Group Data Analysis Training - January 17 2012

ADHD Overview Treatment for ADHD is a combination of medical and behavioural therapy Commonly Prescribed ADHD Medications IMS Consulting Group Data Analysis Training - January 17 2012

1. Understanding your therapy area / countries of interest Answering these questions will make sure that you pull the data correctly from PADDS Key therapy area questions Key country questions Purpose of the analysis PADDS Pull & Export • Have you included all the attributes that you need (e.g. Molecule List, ATC123 Corporation, Manufacturer, Country, Product, Int Product, Int Strength, Strength, Pack, NFC123, etc.)? • Within the attributes, have you selected all of the molecules, countries, forms etc. that you need? • Have you included all of the measurements that you need (e.g. Sales, LC Sales, Standard Units, etc.)? • Have you selected the correct time units (e.g. QTRs, Months, Years, MATs, etc.) and timeframe? IMS Consulting Group Data Analysis Training - January 17 2012

1. Understanding your therapy area / countries of interest Unless you've worked in the therapy area before, read up on it before diving in IMS Consulting Group Data Analysis Training - January 17 2012

1. Understanding your therapy area / countries of interest Some useful resources • Disease Primers can often be found on Xchange IMS Consulting Group Data Analysis Training - January 17 2012

1. Understanding your therapy area / countries of interest Some useful resources IMS Consulting Group Data Analysis Training - January 17 2012

1. Understanding your therapy area / countries of interest With a couple of PADDS pulls you can learn a lot IMS Consulting Group Data Analysis Training - January 17 2012

1. Understanding your therapy area / countries of interest Make sure you are familiar with the data coverage in your countries of interest IMS Consulting Group Data Analysis Training - January 17 2012

Exercise 1 To familiarise yourself with the therapy area and ensure that your PADDS extraction is appropriate, carry out the following exercise Exercise 1 • Consider Brazil, Italy, UK, US only (all panels) • Using PADDS, answer the following questions: • What drugs (molecules) are available in ATC N6B0 in each country? • IF YOU HAVE TIME: which of these should be used in our drug class definition for ADHD? (search the internet – not MIDAS for this task) • What formulations are available in each country? • What products have generic versions in each country? • How many strengths exist for oral methylphenidate in the USA? • How do the strengths relate to the formulation? IMS Consulting Group Data Analysis Training - January 17 2012

2. Pulling data and exporting from PADDS Some top tips for pulling and exporting data from PADDS IMS Consulting Group Data Analysis Training - January 17 2012

2. Pulling data and exporting from PADDS • Pull “everything” at once • Be sure to remove “all others” for global sales estimates IMS Consulting Group Data Analysis Training - January 17 2012

2. Pulling data and exporting from PADDS • Switch Layout to Absolute • Pull “Local Currency” Euro/USD for a constant exchange rate IMS Consulting Group Data Analysis Training - January 17 2012

2. Pulling data and exporting from PADDS • To change the layout default to absolute, select Layouts in the Report menus and then select “IMS Standard (absolute)” • You have to change this in each database – monthly, quarterly and medical IMS Consulting Group Data Analysis Training - January 17 2012

2. Pulling data and exporting from PADDS • Always export as “Flat (only lowest level)” IMS Consulting Group Data Analysis Training - January 17 2012

2. Pulling data and exporting from PADDS The client has provided us with the following definition with which to investigate the ADHD market • Your European client has defined your ADHD molecule market of interest as: • Amphetamine & dexamfetamine • Methylphenidate • Dexamfetamine • Atomoxetine • We have extracted 12 years of quarterly Standard Units and Sales (Mfn) Local Currency* • The following attributes were used to define the rows: • Country combined • Mol list • Mol count • Intprd • Corp • NFC123 • Gx Product Class, • Int strength • The client would like to compare the following countries: • Italy • UK • USA • Brazil *”Local Currency” means at constant exchange rate IMS Consulting Group Data Analysis Training - January 17 2012

3. Processing the data in Excel Important tips for data processing IMS Consulting Group Data Analysis Training - January 17 2012

3. Processing the data in Excel Sticking to certain formatting standards will allow others to understand your work faster IMS Consulting Group Data Analysis Training - January 17 2012

3. Processing the data in Excel It is vital to maintain the integrity of your original data in case you need to quickly update it at a later stage ×  • Insert any new columns to the right or left • Do not insert columns into the middle of your data IMS Consulting Group Data Analysis Training - January 17 2012

3. Processing the data in Excel Simple relationships between the sheets improves readability and auditing Simple relationships among sheets Overly complex relationships among sheets Data Sheet(s) Analysis Sheet(s) Data Sheet(s) Analysis Sheet(s)   Lists & Selections Sheet • This structure is bad because tracing calculations has you jumping back and forth between sheets which makes the task much more complex • Elements such as selections and lists are harder to find because they don’t have a ‘home’ • It is good to have lists and selections on a separate sheet to make them easier to find and maintain good structure for the workbook IMS Consulting Group Data Analysis Training - January 17 2012

3. Processing the data in Excel Create calculated variables and “key” variables in new clearly-identified columns next to the raw MIDAS data Data sheet Output sheet • “Key” variables are useful for “rolling-up” data into custom groups and form the building blocks for the dynamic tables • Create key variables in your data sheet and then use formulas such as SUMIF to group the data together • In this example, all the sales for a particular molecule-form have been summed IMS Consulting Group Data Analysis Training - January 17 2012

3. Processing the data in Excel Creating custom groupings and “key” variables requires a good knowledge of different text functions IMS Consulting Group Data Analysis Training - January 17 2012

3. Processing the data in Excel The IF function returns one value if its logical expression evaluates to TRUE and another value if it evaluates to FALSE • IF(logicalExpression, valueIfTrue, valueIfFalse) • LogicalExpression can be any valid Excel expression that evaluates to TRUE or FALSE (e.g. A1 > 0). • ValueIfTrue is the value that is returned if logicalExpression evaluates TRUE. • ValueIfFalse is the value that is returned if logicalExpression evaluates FALSE. • IF statements may be nested up to seven layers deep. For example, the following expression returns ‘Yes’ if A is greater than zero and less than 10 but not equal to 5. • IF(A1 > 0, IF(A1 < 10, IF(A1 = 5, “No”, “Yes”), “No”), “No”) IMS Consulting Group Data Analysis Training - January 17 2012

3. Processing the data in Excel Calculating days of therapy is a common part of processing the data • Days of therapy (DOT), also called treatment days (Tx days), is an important and commonly used volume measure • It is critical to use when the therapy area has multiple forms and/or dosing regimens, since 1 standard unit is not always comparable between products • Days of therapy = Volume (in SUs) / average (or defined) daily dose (in SUs per day) IMS Consulting Group Data Analysis Training - January 17 2012

Exercise 2 Now that you have extracted your data, there are various processing tasks to be completed before it will be ready to analyse Exercise 2a • Open file “DA Training Exercise 2a Complete Data Tables v_.xls” • Instructions are in the Excel file • A completed example is in the Excel file for Exercise 2b Exercise 2b • Open file “DA Training Data Sources ADHD Sales.XLS” • Open file “DA Training Data Sources ADHD AVDD.XLS” • Open file “DA Training Exercise 2b Extend Data Tables v_.xls” • Instructions are in the Excel file • A completed example will be given to you IMS Consulting Group Data Analysis Training - January 17 2012

Data Analysis Methodology and Conventions