Omnichannel Marketing Communications
Omnichannel Marketing Communications, Data Dive Assignment, Spring
2021 Contents Assignment……………………………………………………………………………………………2 Assignment in brief…………………………………………………………………………………………………………………..2 Due Dates & Checklist……………………………………………………………………………………………………………….2 Report …………………………………………………………………………………………………………………………………….2 Top-line Summary:………………………………………………………………………………………………………………..2 Main Q&A…………………………………………………………………………………………………………………………….3 The Data ……………………………………………………………………………………………….4 Data Background………………………………………………………………………………………………………………………4 Variable Definitions…………………………………………………………………………………………………………………..4 Structure of the Excel Document………………………………………………………………………………………………..5 Quirks in the data……………………………………………………………………………………………………………………..5 Examples of Creatives from the Web ………………………………………………………………………………………….6 Grading…………………………………………………………………………………………………7 Excel Workshop ……………………………………………………………………………………..8 About this workshop…………………………………………………………………………………………………………………8 Sorting data……………………………………………………………………………………………………………………………..8 Computing numeric day of the week using the WEEKDAY Function………………………………………………10 VLOOKUP Function: Computing written day of the week …………………………………………………………….11 VLOOKUP Practice with age……………………………………………………………………………………………………..14 PIVOT Tables: Example 1 (gender x conversions)………………………………………………………………………..15 PIVOT Tables: Example 2 (Number of touch points before conversion) …………………………………………16 PIVOT Tables: Example 3 (Nesting)……………………………………………………………………………………………18 Basic IF statements…………………………………………………………………………………………………………………19 Nested IF statements: Example 1 (weekend or not?)…………………………………………………………………..19 Advanced Nested IF statements: Example 2 (morning, afternoon, or evening?)……………………………..20 All-Around Advanced: Nested IF statements + filtering + VLOOKUP đ …………………………………………..21 There must be an easier way?? ………………………………………………………………………………………………..23 A note on descriptive statistics…………………………………………………………………………………………………24 2 Calculating descriptive statistics in Excel……………………………………………………………………………………24 Plotting the distribution…………………………………………………………………………………………………………..24 What descriptive(s) are âbestâ?………………………………………………………………………………………………..25 Graphing the distribution ………………………………………………………………………………………………………..27 Calculating percentiles…………………………………………………………………………………………………………….30 Bonus: adding cumulative percent line to your graph …………………………………………………………………34 Excel resource if you get stuck………………………………………………………………………………………………….37 Assignment Assignment in brief Your task is to explore data from a display campaign run in the Irish market, then write a brief report consisting of 3 questions and a topline summary. You may work with a buddy of your choosing or alone. Due Dates & Checklist March 8 (Monday) by 4:59 pm â Upload your report & excel document here 1 : Checklist â Report is a word doc â Report has no cover page â jump right in! â First line of report has student ID(s) and names (NOT in a header, they are difficult to copy when they are in there) â Report is named using the following convention: studentD1_studentID2.docx, e.g., 123456_342344.docx (If you did not work with a partner, you’ll just have 1 number in your file name). â Excel file is named using the same convention as the word doc â All word counts are adhered to Report The report consists of 2 parts: a topline summary and a Q&A. The report is meant to be brief. Pay careful attention to word counts and use the space wisely. Top-line Summary: The topline summary is in bullet-point form. You are to write <1 sentence to 2 sentences on the following: 1 If you are working with a buddy, only one person needs to upload the report. 3 1. In future campaigns, how many impressions should we âspendâ on each individual person2 if we assume that the client is on a budget and billed on a cost per mill basis? 3 (<1-2 sentences) 2. Make three more recommendations for optimizing future display campaigns (<1-2 sentences each) a. b. c. 3. Your favorite takeaway from this assignment (1-2 sentences) Main Q&A Note: your answers should be supported with the dataset, in class material, and readings. Cite all sources using either APA or Harvard Style (https://libguides.ucd.ie/academicintegrity/harvardstyle) 1. Explain the logic behind your recommended impressions/person(s). Remember that the client is billed on an impression basis and is on a budget, so you will have to decide what your strategy should be. For example, should we try to reach a lot of people at the sacrifice of repetition, or should we try to reach a smaller number of people more times? Whatever your strategy, be sure to provide actual numerical recommendations (e.g., 5 impressions (also known as âtouch pointsâ) per person) and support your answer with evidence from the data (e.g., tables, figures, et cetera) and learnings from class/readings/online, where appropriate. (max 400 words) 2. Explain the logic behind your three additional recommendations for optimizing future display campaigns as noted in the topline summary. Support your answer with evidence from the data (e.g., tables, figures, et cetera) and learnings from class, where appropriate. (max 600 words) 3. The final question ignores the dataset and asks you to do some research on display campaigns more generally. Based on your own research, what are the characteristics of a stellar brand building display campaign (part a) and a stellar activation display campaign (part b). Be sure to support your answer with learnings from class/readings/online where appropriate (max 700 words). a. Stellar brand building: b. Stellar activation: 2 If you have different recommendations for different types of people, please specify that in your answer 3 Cost per mill = cost per 1,000 impressions 4 The Data Data Background This is confidential, white-labeled data from Quantcast. The data is from a display campaign run for an Irish bank selling mortgages. A major goal of the campaign was to get people to request more information or a meeting using an online callback form. Hence, in this dataset, a âconversionâ occurs when someone fills out the callback form. The dataset contains information on how many ads were served to each individual (n=235), what gender and age the individuals are (estimated), when and where the ads were served, what targeting was employed (retargeting, prospecting), and what kind of conversion occurred (click, view â explained below under âTargeting Typeâ). The campaign was run on a cost per mill basis. That is, advertisers were charged for every 1,000 impression. They were not charged based on clicks. Variable Definitions The dataset contains 14 unique variables across several tabs. pUnit Number: There are 235 individuals in this dataset. pUnit number identifies each individual by their cookie (numbered simply 1 â 238 (a few people were removed, hence the total n is 235)). Event_date: The date and time at which an ad exposure occurred. Campaign: The campaign associated with this data (i.e., mortgages). Targeting type: There are four levels here a. Prospecting: at the time of exposure, the individual had not visited the bankâs mortgage landing pages in the recent past. b. Retargeting: at the time of exposure, the individual had visited the bankâs mortgage landing pages in the recent past. c. Click Conversion: the individual clicked on the ad and filled out the callback form d. View Conversion: the individual did not click on the ad, but later went to the site and filled out the callback form. Event_device: Whether the ad was served on a desktop, mobile device, tablet, app, et cetera. Event_category: There are four levels here 5 a. First_touch: the first time an individual is exposed to an ad from this display campaign b. Middle_touch: the ad exposure(s) that occur between the first and last touch c. Last_touch: the final ad served before the âconversionâ ad (i.e., the 2nd to last ad) d. Conversion: the final ad served before conversion Event_domain: The website where the ad was shown Creativesize: The dimensions of the ad (see âexamples of creatives from the webâ below for examples of the sizes). Gender: The estimated gender of the individual Age_group: The estimated age of the individual First Touch Date (see tab Mortgages TTC): The date of the first touch First Touch TTC (see tab Mortgages TTC): The time in days from first touch to conversion Last Touch Date (see tab Mortgages TTC): The date of the last touch Last Touch TTC (see tab Mortgages TTC): The time in days from last touch to conversion Structure of the Excel Document The Excel document contains 3 tabs: 1. Mortgages â Raw 2. Mortgages â TTC 3. Data Dive Practice The first tab contains the raw data; all remaining tabs are derived from this tab. The second tab (Mortgages TTC) contains âtime to convertâ (TTC) data. It is the same data as the ârawâ tab, but only the âconversionâ line is shown. The final tab (Data Dive Practice) is a truncated version of the raw data that weâll use for in-class practice. Quirks in the data You may notice that some people have no âfirst touch.â There are several explanations for this: 1. The âfirst touchâ happened outside the date range of this dataset 2. The âfirst touchâ and âconversionâ happened so close together that the system thought it more appropriate to label the âfirst touchâ as a âlast touch.â 6 3. Conversion happened immediately. While you may want to look at TTC (time to convert) and make recommendations, keep in mind the limitations of your claims â you will be missing data from a significant chunk of people who donât have a âfirst touch.â What impact will that have on your recommendations, if any? Top papers will note the limitations and how those limitations might change their answers, if they choose to explore TTC. Examples of Creatives from the Web Note â we do not know which Irish bank ran this campaign. However, I thought it might be helpful to show some examples of mortgage-related display ads with their dimensions. 300*250 160*600 728*90 7 Grading UCD defines the following four grade brackets as follows: D = adequate, C = good, B = great, and A = excellent. Grades lower than D- indicate a failing grade. Below, Iâll give you some more context regarding what each bracket constitutes Grade UCD Criteria Specifics A range Excellent ⢠Many high-quality insights (of which many are non-obvious, i.e, not low hanging fruit) ⢠Compelling recommendations supported not just by the data in the dataset, but also by my lectures/assigned readings and other relevant outside readings. ⢠Makes me think (on several occasions), âI wish Iâd thought of that!â or âI wish Iâd found that reading!â or âI wish Iâd noticed that!â etc. ⢠Does not fall victim to spurious correlations or overstating insights based on the data B range Great ⢠Many high-quality insights (though not as nuanced or non-obvious as the A-range) ⢠Compelling recommendations that are well-supported, but not at the A-level ⢠Rarely, if ever, falls victim to spurious correlations or overstating insights based on the data C range Good ⢠Good insights, but many fall under the âlow hanging fruitâ category (i.e., are easy to identify) ⢠Recommendations are logically supported but could be fleshed out more/researched more. ⢠Some instances of opinion rather than actual support ⢠May overstate insights of the data, may miss important insights D range Adequate ⢠Nearly all insights fall under the âlow hanging fruitâ category ⢠Recommendations may be based on opinion ⢠May overstate insights of the data ⢠May have some mistakes in data analysis Below D- Not passing ⢠Incorrect insights ⢠Poor support, based mostly on opinion ⢠Substantial mistakes in data analysis ⢠Thin on data, research, and writing 8 Excel Workshop About this workshop This workshop is meant to cater to multiple levels of Excel. If some of the tasks are too easy for you, skip ahead! And donât be shy about raising your hand Sorting data Go to tab âdata dive practiceâ â all examples will be based on this tab. The cookie data (pUnit Number) are out of order. Letâs fix this. 1. Select all data, not just the cookie columnâotherwise, the rest of the data become dissociated. A quick way to select all the data without dragging your mouse around: a. Click on Cell A1 (not into the cell, just click so it is selected) b. Hold down CTRL SHIFT (Command Shift on Mac) c. While holding down CTRL SHIFT, hit your right arrow key. d. While still holding down CTRL Shift, hit your down arrow key. 2. Click on âcustom sort.â The sort function is located here. Always opt for âcustom sort.â Otherwise, Excel will choose a sorting column and method for you, which may not be the one you want. 9 3. Click on âmy data has headersâ if youâve selected the heading row like I have above. Sort by pUnit_Number. 4. The data should now look like this. Note that the event categories (first touch, middle touch, et cetera) are out of order. Letâs fix this while holding cookie order constant. Select all data, go back to custom sort, then click âadd levelâ and select âevent_date.â This will tell Excel to first sort by cookie, then sort by cookie number then sort by event_date. 10 5. Check to make sure your heading is still in the first row. If not, something went awry when you sorted. You should hit CTRL Z (undo) and try again. Most likely, you did not tick the box âmy data has headers.â Computing numeric day of the week using the WEEKDAY Function It may be useful to know which day of the week certain events occur on. Excel makes this easy with the WEEKDAY function. More information on this function here. 1. Insert a new column C and name it âweekday numericâ 2. Enter the following formula: =WEEKDAY(B2). This function returns a number that represents day of the week according to the following convention: Numeric Written 1 Sunday 2 Monday 3 Tuesday 4 Wednesday 5 Thursday 6 Friday 7 Saturday You may notice that you donât get a number at all, but rather, a weirdly formatted date. Thatâs because Excel is confused about what the cellâs formatting should be. Change formatting from âcustomâ to ânumber.â Click on the â00sâ button to remove any decimals. See red boxes below. 3. Drag the formula all the way down, or hover your mouse in the bottom right of the cell until a black cross appears, then double click. 11 VLOOKUP Function: Computing written day of the week VLOOKUP allows you to look up and return data that matches an index you specify. For example, our variable âweekdaysâ is in the form of 1,2,3,4,5,6,7. Iâd prefer to have this information in string form, that is written out as âMonday,â âTuesday,â et cetera. With VLOOKUP, I can ask Excel to look in the table below and return the written word that corresponds to the numeric day of the week. Below, Iâll show you how. Numeric Written 1 Sunday 2 Monday 3 Tuesday 4 Wednesday 5 Thursday 6 Friday 7 Saturday While there are other ways to solve this conundrum, Iâm introducing VLOOKUP because it is a crucial one for you to know. VLOOKIP is the function I use more than any other in excel. It is also a function that I saw tested via an assignment for a job application. Itâs worth the effort to learn it, even though itâs fussy and can be frustrating. So letâs go: 1. Create a new column D and name it âWeekday.â 2. Add a new tab called âweekday.â 3. In the new tab âweekday,â enter the following legend 4. âNow return to the tab Data Dive Practice.â In cell D, weâll enter the following formula: =VLOOKUP(value,table,col_index,[range_lookup]) where value= The value youâd like to look up 12 table=The range of data youâd like to look in col_index=The column of the value you would like to return. [range_lookup]= TRUE is an approximate match and FALSE is exact match. Use FALSE. Help I donât get this function! Help, Iâm always getting errors with VLOOKUP ARRRHG! 23 Things You Should Know about VLOOKUP VLOOKUP can be a fussy function. Iâve linked a nice troubleshooting guide above. Hereâs what the formula in column D will look like: =VLOOKUP(C2,Weekday!$A$1:$B$8,2,FALSE) Letâs break this down. C2â this is the value Iâd like to look up. We are going to drag this formula down, and because there are no â$â, the formula will update as we drag (e.g., C3, C4, C5, et cetera) Weekday!$A$1:$B$8âthe first piece Weekdays! tells excel weâre going to a new tab. $A$1:$B$7 is the array of data that has the values Iâm indexing on (i.e., the numeric day of the week) and the value I would like to be returned if excel finds a match. The â$â locks the array. This means that when I drag the formula down (or sideways), the array Iâve selected remains the same. This is critical so that when I drag the formula down, the reference array stays the same. There is a $ in front of the letter to lock the columns, and a $ in front of the number to lock the rows. It is possible to lock just one, but donât worry about that right now. 2 â This tells excel that I when it finds a match between the two arrays, I want the value in column 2 (i.e., Column B), returned. Excel always counts the left most column in the array as â1â. So even if your table starts in column C, this formula treats it as a 1 and column D as 2, et cetera. FALSE â This tells excel to return an exact match. — 13 Conceptually, hereâs what is happening. Excel is going to look for â4â wherever we tell it to (in this case, the weekdays tab) Excel looks in this table for a 4 and finds it! STEP 1 (C2) STEP 2 Weekday!$A$1:$B$8 STEP 3 2,FALSE Excel returns whatever value is in column 2 (which we specified in the formula with a â2â) The FALSE tells Excel to only return a value if there is an exact match versus a partial match (e.g., â4â not â44â or â14â et cetera) âColumn 1â âColumn 2â 14 The result: 5. Now drag the formula down to populate all cells. VLOOKUP Practice with age Letâs try another VLOOKUP example, this time with age. I find it confusing to have âageâ written out. Letâs change it to numeric. age_twentyfive_thirtyfour 25_34 age_fortyfive_fiftyfour 45_54 age_fiftyfive_sixtyfour 55_64 age_thirtyfive_fortyfour 35_44 age_eighteen_twentyfour 18_24 age_sixtyfive_plus 65 See if you can get the following results. If not, raise your hand and Iâll come help. 15 PIVOT Tables: Example 1 (gender x conversions) Pivot tables allow us to create easy cross-tabs on the data. First, select all data as you do when you sort. 1. Go to the âinsertâ tab and select âpivot tableâ 2. Select ânew worksheetâ as the destination for the Pivot table then press OK. Name this new tab âPivot 1.â 3. You should see a box called âPivot Tables Fields.â If at any point this box disappears, you can get it back by clicking âanalyzeâ then âfield listâ 16 4. Letâs build a simple table of demographics x conversion. a. Drag âgenderâ into ârowsâ b. Drag âgenderâ into âvaluesâ c. Drag âevent_categoryâ into filters Youâll get a table like the one below. This shows the number of events that occurred for females and males (e.g., first touch, last touch, conversion, et cetera). Now, we only want to look at conversions to see if one gender converts more often. d. Since we only want to look at conversions, click on âevent_category (All) and select âconversion.â Your table should look like this one: PIVOT Tables: Example 2 (Number of touch points before conversion) Iâd like to know how many impressions (i.e., ads or âtouch pointsâ) people saw before they converted. 1. Insert a new pivot table, name the tab âPivot 2â 2. Drag âpUnitâ into rows, âevent_categoryâ into filters, and âpUnitâ into values. Your table should look like this: 17 Note that Excel defaults to summing the variable pUnit.4 That doesnât make sense for what weâre interested in knowing. We want to know the number of touchpoints before conversion. So we need to do two things. 3. We need to change the âsumâ to âcount.â Click on pUnit in the Values box, then âValue Field Settings.â Change âsumâ to âcountâ 4 Any time you enter a numeric variable into the Values box, Excel will default to summing that variable. Oftentimes, you DO NOT WANT THE SUM. Often, youâll want an average or a count. Be sure to keep a close eye on this issue when you are doing your pivot tables. 18 PIVOT Tables: Example 3 (Nesting) Suppose you would like to know whether males and females react differently to ad size. To begin examining this, we could create a pivot table that nests creative size under gender: To build this table you will: 1. Select all data in tab âdata dive practiceâ and create a pivot table in a new tab 2. Label this new tab âPivot 3â 3. Drag âgenderâ into rows 4. Drag âcreativesizeâ into rows below gender 5. Drag âevent categoryâ into âvaluesâ 6. Drag âevent categoryâ into âfiltersâ 7. Filter event_category by âconversionsâ 19 Basic IF statements First, I want to show you how a basic IF statement works. =IF (logical_test, [value_if_true], [value_if_false]) where logical_test = Some statement that can be true or false (e.g., 10>2?) value_if_true=The value to return when true value_if_false=The value to return if false Help I donât get this function! Letâs do a simple one. I want to compute a new column that is âyesâ if the ad was shown on daft.ie and a 0 if not. In column N, youâll write the following function: =IF(I2=”daft.ie”,”yes”,0) Note that any text is in quotations, while numbers are not. Youâre results should look like this: I realize this column isnât super useful with âyesâ and 0. But I wanted to make the point that string variables (i.e., non-numeric data) always needs to be in quotes. ALWAYS. Nested IF statements: Example 1 (weekend or not?) While Vlookup is my favorite function, nested IF statements are a close second. A nested function is when there is a function wrapped in a function. Letâs do a simple one. I want to create a new variable that is coded âWeekendâ if the event date occurs on a weekend and âWeekdayâ if it does not. To do this, we will nest the âORâ function. In column O, write the following function: 20 =IF(OR(D2=”Saturday”,D2=”Sunday”),”Weekend”,”Weekday”) The âorâ statement is nested in the logical test portion of the IF statement. Your results should look like this: Advanced Nested IF statements: Example 2 (morning, afternoon, or evening?) I would like to create a variable that specifies whether an ad or conversion happened in the morning, in the afternoon, or at night. 1. Create a new column called âTimeâ 2. Use the âhourâ function to return the hour of the day from âevent_dateâ =HOUR(B2) 3. Create a new column called âTime of Dayâ 4. Determine your cutoffs for morning, afternoon, and night. Iâll propose the following: a. Morning: 6 â 11 b. Afternoon: 12 â 4 c. Evening: 5 + 5. Time for a nested if function! =IF(AND(P2>=6,P2<=11),”Morning”,IF(AND(P2>=12,P2<=16),”Afternoon”,”Evening”)) The function first asks whether P2 is greater than or equal to 6 and less than 11. If so, then the function should return the word âMorning.â If not, Excel should execute a second IF statement that asks whether P2 is greater than or equal to 12 and less than or equal to 16. If so, Excel should return the word âAfternoon.â Otherwise, Excel should return the word âEvening.â 21 All-Around Advanced: Nested IF statements + filtering + VLOOKUP đ I would like to create a new variable that denotes whether a cookie was exposed to retargeting at any point before conversion. Here is one way to do this. 1. Put a filter on the data. 2. Filter Column F (Targeting Type) to contain only âRetargeting.â 3. Copy and paste the pUnit column into a new tab. This will give us a list of cookies that were at some point retargeted. Call this tab âretargetingâ 4. In column B, type âyesâ for each cookie. We are going to use this data as part of a VLookup Function 22 5. Return to the tab âdata dive practiceâ and unfilter the data. Name column R âContained Retargeting?â 6. In column R, I want to denote for each conversion whether the cookie was retargeted at any point. This means that I only want data to appear in the green shaded rows (where a conversion happened). To do this, we are going to nest a VLookup function inside an IF statement. It will look like this: =IF(H2=”conversion”,VLOOKUP(A2,Retargeting!$A$2:$B$17,2,FALSE),””) This formula says that if event_category = âconversion,â then go to the Retargeting tab and look up the corresponding cookie, then return whatever is in the 2nd column (in this case, the âyesâ). If event_category does not = âconversionâ, return nothing (i.e., ââ). Your data should look like this: Now, if you are like me and #N/A makes you feel anxious and unhappy, itâs easy to deal with this. The #N/A is occurring because Excel didnât find anything in the tab âretargetingâ when it looked for the cookie. In other words, the cookie wasnât there. Why not? Because remember â only cookies that had retargeting are listed in the retargeting tab. This means that any of the #N/Aâs conceptually mean âno retargeting. Wouldnât it be nice if they said that? Enter, the IFERROR statement (another favorite). =IFERROR (value, value_if_error) where Value â The value or formula you would like to check for an error (e.g., #N/A) Value_if_error â The value you would like Excel to return instead of the error 7. So, letâs amend our formula as follows (new stuff in red): =IFERROR(IF(H2=”conversion”,VLOOKUP(A2,Retargeting!$A$2:$B$17,2,FALSE),””),”No”) 23 Now, our data look like this â hurray! There must be an easier way?? There are SO many ways to do the same thing in Excel. For example, the last exercise (above) could have been done simpler if we had just used the data in the tab âMortgages â Conversions,â which only contains conversion data. So while the exercises above may not be the simplest way to do things, they give you practice at fundamental skills so that you can build your own formulas and answer your own questions. 24 A note on descriptive statistics In order to make your recommendations, you will need to calculate some descriptive statistics for variables of your choosing (e.g., time to convert). These are statistics that summarize the distribution of the data, for example: Mean: the average of the data Median: the point at which 50% of the data is above and 50% of the data is below Mode: the data point which appears the most often Max: the highest number in the data set Min: the lowest number in the data set If you want more information about this, go here. Calculating descriptive statistics in Excel Letâs calculate some descriptive statistics for Last Touch TTC. For this, we will use the real data (not the practice data). Go to tab âMortgages TTC.â Recreate column P in your own dataset. Plotting the distribution You may find it helpful to plot the distribution of data 25 The formulas5 will be as follows. Donât copy and paste them from here. Build them yourself. Mean =AVERAGE(N2:N243) Median =MEDIAN(N2:N243) Mode =MODE(N2:N243) Min =MIN(N2:N243) Max =MAX(N2:N243) You should get the following answers Descriptive Mean 4.086758 Median 2 Mode 0 Min 0 Max 25 What descriptive(s) are âbestâ? This is an important question. Youâre probably most familiar with the mean. And in many cases, the mean, median, and mode are nearly identical/identical. When, you ask? When data are normally distributed, which looks like this. When data are normally distributed, the mean, median, and mode are all the same number. 5 Typo found here and was corrected. Old version stated N9:N243. Should be N2:N2243 26 However, the mean isnât always the best descriptive for making decisions. Consider the following, for example: Look familiar? Itâs a chart from Byron Sharp shown in week 1. On the X axis, we have the number of cokes bought per year. On the Y axis, we have the number of people buying that number of cokes per year. The data is heavily skewed. It does not conform to a normal distribution. Hence, mean, median, and mode will be different. Itâs easy to see here that the modal response is 0. That is, most people buy 0 Cokes per year. The bulk of the data is between 0-3 Cokes per year. For skewed data sets, the mean can be quite misleading. If we were to take the mean of this data, it would be around 30. But to focus on the mean here misses the point that most people only buy a few Cokes per year. So the âtypicalâ person isnât buying 30 Cokes/year even if 30 is mathematically the average. Make sense? If it doesnât, please raise your hand and Iâll come over. Which descriptive stats would be more useful? Certainly the mode (which is 0). The mode tells us what number is most common. The median would also be helpful, but I would really like to see the data in percentiles. (If you donât know about percentiles, read about them here). Next, Iâll show you how to graph the distribution and calculate the percentiles. 27 Graphing the distribution Even though I have the descriptive statistics for last touch time to convert, I would like to see the distribution. To do this, weâll do the following (note, this tutorial shows less steps because I assume you have completed the first tutorial). Select column containing Last Touch TTC and insert a pivot table (see earlier tutorial if you are having trouble doing this) Drag Last Touch TTC into ârowsâ and âvalues.â Change from sum to count. You should see this: 28 Here is how we can interpret this: The number of days from the last touch to conversion. Some people converted in 0 days, some people converted after 1 day, et cetera all the way up to 25 days (that matches our âmaxâ calculation from before) How many people converted for each span of days. For example, 73 people converted in 0 days. 33 took 1 day to convert. 23 took 2 days to convert, et cetera. 29 What I would really like to see is a distribution. To do this, copy and paste the data in a blank cell. Make sure it pastes as raw data, not as a pivot table (it just makes it easier to work with). Highlight the data and go to insert, then select scatterplot 30 Here it is in all its glory. Now you can do fun cosmetic things to it, if you wish. See some tips here. Calculating percentiles It would be nice to know what percentage of people converted in 0 days, 1 day, 2 days, et cetera. How can we do this? Itâs easy. Letâs first just calculate some percents. Type in the following function below. Donât forget to put in the $ because we are going to drag this formula down. Can you figure out where they need to be added in the below formula? 0 10 20 30 40 50 60 70 80 0 5 10 15 20 25 30 Count of Last Touch TTC 31 Drag the formula down. Last Touch TTC Count of Last Touch TTC Percentages 0 73 33% 1 33 15% 2 23 11% 3 17 8% 4 5 2% 5 6 3% 6 9 4% 7 6 3% 8 4 2% 9 7 3% 10 11 5% 11 3 1% 12 2 1% 13 2 1% 14 4 2% 15 1 0% 17 1 0% 18 4 2% 19 2 1% 20 2 1% 21 1 0% 22 1 0% 25 2 1% So this tells us that 33% of people converted in 0 days since the last touch, 15% converted in 1 day from the last touch, et cetera. Change calculation to percent 32 To calculate the cumulative percentage (i.e., percentiles), copy the first percentage (33%) into the top row. Then, create the following formula. You will then drag this down. 33 Your results should look like this: Last Touch TTC Count of Last Touch TTC Percents Cum. Percent 0 73 33% 33% 1 33 15% 48% 2 23 11% 59% 3 17 8% 67% 4 5 2% 69% 5 6 3% 72% 6 9 4% 76% 7 6 3% 79% 8 4 2% 80% 9 7 3% 84% 10 11 5% 89% 11 3 1% 90% 12 2 1% 91% 13 2 1% 92% 14 4 2% 94% 15 1 0% 94% 17 1 0% 95% 18 4 2% 96% 19 2 1% 97% 20 2 1% 98% 21 1 0% 99% 22 1 0% 99% 25 2 1% 100% So, this tells me that by 8 days after the last touch point, 80% of people in the dataset converted. Of course, from an interpretation standpoint, we have missing data to worry about. But my point here is to show you how to do the calculations. 34 Bonus: adding cumulative percent line to your graph It can be nice to visualize where you start getting a drop of in conversions. Here is how you graph the cumulative percent (i.e., percentiles) on your first graph. Click on the chart you made previously. Go to âchart designâ and then to âselect data.â You will get this screen. Click on âAddâ (see red box). Specify the following (you can select the data with your mouse and Excel will handle writing out the references (e.g., Sheet!…..) 35 Almost done, but youâll notice that the cumulative percent is basically flat. This is because the Y axis goes from 0 to 80, but our percents go from 0 to 1. We can fix this by adding a second axis. Click on the orange dots and right click, then select âformat data series.â 36 Youâll then see this tab open. Click on âsecondary axisâ Itâs almost perfect, but I would like the Y axis to go to 100%, not 120%. 0% 20% 40% 60% 80% 100% 120% 0 10 20 30 40 50 60 70 80 0 5 10 15 20 25 30 37 Right click on the righthand axis and select âformat axis.â You can then specify the max Y value here. Can we make the graph prettier? Yes! Play around or Google for tips. But now you have some basic chart building skills, if you didnât have them already. Excel resource if you get stuck If you want to learn how to do something in excel, just Google âhow do IâŚin excel.â I do this all the time and I have years of excel experience. My favorite excel resource is Excel Jet. Iâve gotten to the point now where if I have an excel question, I just Google â{what I want to do here} excel jetâ. Nine times out of ten, Excel Jet pulls through for me. I see that they have YouTube videos, but I find their written answers quicker to understand (they include the code too). My least favorite resource is support.office.com, so donât worry if you find their explanations confusing.
Need assignment help for this question?
If you need assistance with writing your essay, we are ready to help you!
OUR PROCESS
Order
Payment
Writing
Delivery
Why Choose Us: Cost-efficiency, Plagiarism free, Money Back Guarantee, On-time Delivery, Total ĐĄonfidentiality, 24/7 Support, 100% originality