Skip to content

brycewang-stanford/Auto-Empirical-Research-Skills / skills/18-jusi-aalto-stata-accounting-research

stata-accounting-research

Unverified

STATA code pattern library for empirical archival accounting research. Provides tested syntax from 126 peer-reviewed JAR (Journal of Accounting Research) replication files (2017-2025). Use when the user asks procedural questions like "How do I implement [method]?" or "Show me code for [technique]" — including: entropy balancing, propensity score matching (PSM), difference-in-differences (DiD), regression discontinuity (RDD), instrumental variables (IV), event studies (CAR/BHAR), survival analysis, Fama-MacBeth regressions, bootstrap, quantile regression, reghdfe/xtreg/areg, clustering standard errors, fixed effects, esttab/outreg2 table formatting, winsorization, leads/lags. Users can specify their variables (e.g., treatment, outcomes, controls) and receive adapted syntax. NOTE: This skill provides code patterns from published papers, not research design advice.

FileKindSize
README-original.mdmarkdown2.9 KB
SKILL.mdskill md4.8 KB
references/JAR_55_bbcl.doother59.1 KB
references/JAR_55_chaplinksy-hanley-moon.doother46.3 KB
references/JAR_55_clm.doother95.1 KB
references/JAR_55_elw.doother58.0 KB
references/JAR_55_hlsw.doother104.1 KB
references/JAR_55_kks.doother365.5 KB
references/JAR_55_lzz.doother25.5 KB
references/JAR_55_madsen.doother307.5 KB
references/JAR_55_mr.doother41.5 KB
references/JAR_56_al.doother75.3 KB
references/JAR_56_bch.doother13.5 KB
references/JAR_56_blv.doother24.6 KB
references/JAR_56_csmw.doother27.6 KB
references/JAR_56_en.doother21.2 KB
references/JAR_56_granja.doother107.9 KB
references/JAR_56_htw.doother18.6 KB
references/JAR_56_kmt.doother5.6 KB
references/JAR_56_ksvw.doother8.0 KB
references/JAR_56_llz.doother3.1 KB
references/JAR_56_ls.doother147.5 KB
references/JAR_56_lv2018.doother123.7 KB
references/JAR_57_bht.doother24.4 KB
references/JAR_57_bl.doother22.6 KB
references/JAR_57_bw.doother263.2 KB
references/JAR_57_cct.doother347.1 KB
references/JAR_57_cgw.doother195.8 KB
references/JAR_57_hcp.doother38.0 KB
references/JAR_57_honigsberg.doother99.5 KB
references/JAR_57_lehmann.doother112.8 KB
references/JAR_57_lm.doother85.5 KB
references/JAR_58_bsz.doother36.9 KB
references/JAR_58_ckm.doother52.3 KB
references/JAR_58_eyring.doother58.1 KB
references/JAR_58_gj.doother187.5 KB
references/JAR_58_gsz.doother121.6 KB
references/JAR_58_htyz.doother30.9 KB
references/JAR_58_jansen.doother99.5 KB
references/JAR_58_joshi.doother43.0 KB
references/JAR_58_kvy.doother19.5 KB
references/JAR_58_msz.doother35.0 KB
references/JAR_58_rauter.doother433.2 KB
references/JAR_58_wy.doother64.5 KB
references/JAR_58_zz.doother125.5 KB
references/JAR_59_breuer.doother826.9 KB
references/JAR_59_ctv.doother383.7 KB
references/JAR_59_dess.doother78.4 KB
references/JAR_59_hmo.doother351.6 KB
references/JAR_60_abkp.doother129.9 KB
references/JAR_60_alv.doother35.4 KB
references/JAR_60_at.doother38.5 KB
references/JAR_60_barrios.doother119.6 KB
references/JAR_60_bbin.doother72.9 KB
references/JAR_60_bdem.doother11.6 KB
references/JAR_60_bl.doother43.5 KB
references/JAR_60_bmsw.doother7.7 KB
references/JAR_60_cc.doother20.1 KB
references/JAR_60_cpss.doother15.4 KB
references/JAR_60_cstv.doother175.3 KB
references/JAR_60_dg.doother19.9 KB
references/JAR_60_duguay.doother8.0 KB
references/JAR_60_fhl.doother71.3 KB
references/JAR_60_gls.doother37.0 KB
references/JAR_60_hmryz.doother28.6 KB
references/JAR_60_hst.doother18.3 KB
references/JAR_60_kt.doother10.4 KB
references/JAR_60_mliu.doother28.3 KB
references/JAR_60_nrwx.doother14.6 KB
references/JAR_60_ptwy.doother26.6 KB
references/JAR_60_skim.doother14.7 KB
references/JAR_61_bcdr.doother524.9 KB
references/JAR_61_bkr.doother51.6 KB
references/JAR_61_bls.doother79.2 KB
references/JAR_61_bo.doother29.7 KB
references/JAR_61_ccm.doother48.7 KB
references/JAR_61_cdss.doother3.9 KB
references/JAR_61_cfhll.doother47.1 KB
references/JAR_61_cfmp.doother12.7 KB
references/JAR_61_ckor.doother77.5 KB
references/JAR_61_cllw.doother11.2 KB
references/JAR_61_clm.doother11.0 KB
references/JAR_61_deller.doother368.9 KB
references/JAR_61_dlrtw.doother30.6 KB
references/JAR_61_dlz.doother13.6 KB
references/JAR_61_drs.doother15.8 KB
references/JAR_61_ds.doother197.5 KB
references/JAR_61_glt.doother1.9 KB
references/JAR_61_gyz.doother7.5 KB
references/JAR_61_ll.doother89.3 KB
references/JAR_61_lszz.doother15.9 KB
references/JAR_61_os.doother128.9 KB
references/JAR_61_zureich.doother22.2 KB
references/JAR_62_1_bdsy_banerjee_etal.doother6.6 KB
references/JAR_62_1_bhs_bouwens_etal.doother59.7 KB
references/JAR_62_1_gsz_gu_sun_zhou.doother46.6 KB
references/JAR_62_2_dgzz_defranco_etal.doother1.8 KB
references/JAR_62_2_gmp_grewal_etal.doother37.2 KB
references/JAR_62_2_xyz_xin_etal.doother25.3 KB
references/JAR_62_3_bht_bloomfield_etal.doother16.0 KB
references/JAR_62_3_ccz_cheynel_etal.doother23.3 KB
references/JAR_62_3_kim.doother2.6 KB
references/JAR_62_3_rr_raghunandan_ruchti.doother15.5 KB
references/JAR_62_4_bd_breuer_dehaan.doother15.3 KB
references/JAR_62_4_cc_cadman_carrizosa.doother20.1 KB
references/JAR_62_4_kmsv_kohler_etal.doother91.5 KB
references/JAR_62_4_mps_marra_etal.doother68.0 KB
references/JAR_62_4_raghunandan.doother50.0 KB
references/JAR_62_4_stv_sran_etal.doother41.1 KB
references/JAR_62_4_xu.doother49.4 KB
references/JAR_62_5_aov_abraham_etal.doother418.9 KB
references/JAR_62_5_gibbons.doother80.1 KB
references/JAR_62_5_jiang.doother114.3 KB
references/JAR_62_5_kstz_krueger_etal.doother26.7 KB
references/JAR_62_5_llz_li_lou_zhang.doother6.2 KB
references/JAR_62_5_lrst_lins_etal.doother66.2 KB
references/JAR_62_5_lswy_lin_etal.doother85.2 KB
references/JAR_62_5_lw_li_wang.doother16.6 KB
references/JAR_63_1_blmv_bastiaansen_etal.doother18.5 KB
references/JAR_63_1_cvw_chen_etal.doother61.2 KB
references/JAR_63_1_fgr_frankel_etal.doother30.3 KB
references/JAR_63_1_hrsx_huang_etal.doother35.5 KB
references/JAR_63_1_jia.doother12.0 KB
references/JAR_63_2_bcrw_bakke_etal.doother271.3 KB
references/JAR_63_2_dlr_desimone_etal.doother83.5 KB
references/JAR_63_2_mqtz_mao_etal.doother94.2 KB
references/JAR_63_2_npstv_nessa_etal.doother7.0 KB
references/JAR_63_3_breuer.doother51.4 KB
references/REFERENCES.mdmarkdown30.7 KB

references/JAR_56_ls.do

View on GitHub
* ********************************************************************************
* ********************************************************************************
* Clean Poster Data
* ********************************************************************************
* ********************************************************************************
* author: Shelley Li
* date: January 20, 2017
* purpose: Generate A List of Posters Used for Customer Evaluation & Analysis
* ********************************************************************************
* ********************************************************************************

* Steps
	* 1. Join Monthly Collections to Generate Master List of Posters 
	* 2. Create Treatment Indicator 
	* 3. Generate a clean list of posters for customer evaluation
* ********************************************************************************

***************************************************************************************
* preliminaries
***************************************************************************************

* start log
	cd "T:\Data Prep and Analyses\B. Data Preparation\01. Customer Panel Data\programs\logs"
	local c_date = c(current_date)
	local c_time = c(current_time)
	local c_time_date = "`c_date'"+"_" +"`c_time'"
	local time_string = subinstr("`c_time_date'", ":", "_", .)
	local time_string = subinstr("`time_string'", " ", "_", .)
	log using log_`time_string', text

* change into output folder
	cd "T:\Data Prep and Analyses\B. Data Preparation\01. Customer Panel Data\output"

***************************************************************************************
* 1. Join Monthly Collections to Generate Master List of Posters
***************************************************************************************

	//-----------------------------------
	// July
	//-----------------------------------

		* import 
			import excel using "T:\Data Prep and Analyses\B. Data Preparation\01. Customer Panel Data\input\PosterRegistry_WIP.xlsx", sheet("July") clear first

		* rename variables
			rename colors colors2
			rename L colors3

		* generate month and post indicator
			gen month=7
			gen post=0

		* save
			save july, replace

	//-----------------------------------
	// August
	//-----------------------------------

		*import 
			import excel using "T:\Data Prep and Analyses\B. Data Preparation\01. Customer Panel Data\input\PosterRegistry_WIP.xlsx", sheet("August") clear first
		
		* rename variables
			rename colors colors2
			rename L colors3

		* generate month and post indicator
			gen month=8
			gen post=0
		
		* save
			save august, replace

	//-----------------------------------
	// October
	//-----------------------------------

		* import
			import excel using "T:\Data Prep and Analyses\B. Data Preparation\01. Customer Panel Data\input\PosterRegistry_WIP.xlsx", sheet("October") clear first

		* rename variables
			rename colors colors2
			rename L colors3

		* generate month and post indicator
			gen month=10
			gen post=1

		*save 
			save october, replace

	//-----------------------------------
	// December
	//-----------------------------------

		* import
			import excel using "T:\Data Prep and Analyses\B. Data Preparation\01. Customer Panel Data\input\PosterRegistry_WIP.xlsx", sheet("December") clear first

		* rename variables
			rename colors colors2
			rename L colors3

		* generate month and post indicator
			gen month=12
			gen post=1

		* save
			save december, replace

	//-----------------------------------
	// Append Months 
	//-----------------------------------

		* append
			append using july, force
			append using august, force
			append using october, force

		* rename variables
			foreach v of varlist _all {
			      capture rename `v' `=lower("`v'")'
			   }

		*save and clear
			save posterregistry, replace
			clear

***************************************************************************************
* 2. Create Treatment Indicator
***************************************************************************************

	* import
		import excel using "T:\Data Prep and Analyses\B. Data Preparation\01. Customer Panel Data\input\tr_indicator.xlsx", clear first

	* lowercase variables
		replace shop=lower(shop)

	* save
		save tr_indicator, replace
		clear

********************************************************************************
* 3. Generate a clean list of posters for customer evaluation
********************************************************************************

	* use poster registry data 
		use posterregistry, clear

	//-----------------------------------
	// Fix store names
	//-----------------------------------

		* lower case variables
			replace shop=lower(shop)
			replace brand=lower(brand)

		*fix some store name and brand related errors and typos
			*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
			*redacted
			*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

		*see which observations' shop or brand is missing
			gen missing_shopbrand=1 if shop=="" | brand==""
			replace missing_shopbrand=0 if missing_shopbrand==.
			keep if missing_shopbrand==0

		*fix more shop name related errors
			*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
			*redacted
			*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

	//-----------------------------------
	// Merge in treatment indicators
	//-----------------------------------

		* merge treatment indicator
			merge m:1 shop using tr_indicator
			keep if _m==3

		*drop the shops not in the experiment
			drop if tr==. 

	//-----------------------------------
	// Fix brand names
	//-----------------------------------

		*fix brand name related errors & typos
			*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
			*redacted
			*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

		*drop the brands (usually with no or few promoters) not included in the experiment
			*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
			*redacted
			*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

		*Only Keep the Store-brands that Appeared in Both Experimental Periods****
			sort shop brand post
			by shop brand: egen n=count(post)
			by shop brand post: egen n_post=count(post)
			gen sp=n-n_post
			drop if sp==0
			drop n n_p sp _m

	* save poster 
		save "T:\Data Prep and Analyses\C. Master Dataset\input\poster_clean731", replace

	* export to excel
		export excel poster_clean731, firstrow(variables) replace


***************************************************************************************
	* end log
		log close
* ********************************************************************************
* ********************************************************************************
* Prepare Customer Ratings
* ********************************************************************************
* ********************************************************************************
* author: Tatiana Sandino
* date: March 11, 2017
* purpose: Generate A List of Posters Used for Customer Evaluation & Analysis
* ********************************************************************************
* ********************************************************************************
*Description: 

*This program prepares data on creativity ratings at the store-brand-poster level.
*Its inputs, outputs, and the index with the steps followed are described below:

*Inputs (under "T:\Data Prep and Analyses\B. Data Preparation\01. Customer Panel Data\input"):
	*1) CustomerPanel.dta - Data from the Customer Panel app, prepared in Excel 
	*	(see app_combined.xlsx)
	*2) poster_clean731_Final_Stata.dta- Poster data (handcoded by Shelley, Kyle and Tatiana)
	*   (see poster_clean731_Final.xls)

*Outputs (under "T:\Data Prep and Analyses\B. Data Preparation\01. Customer Panel Data\output"):
	*Intermediate Outputs
	*Poster_AppRatings.dta- Stata dataset after processing customer panel app inputs
	
	*Final Output
	*CustomerPanel_Dataset- Stata dataset ready for customer panel analysis

	   /*Contains 731 observations and the following variables:	 

		variable name   type    format  label	
			
		poster          str5    %5s	 
		posterfilename  strL    %51s	 
		Store           str4    %4s	    Store code
		Brand           str11   %11s	 
		Poster_author   str22   %22s	Name of the author of the poster
		Poster_authorID int     %8.0g	Employee ID of the author of the poster
		Poster_clear    byte    %8.0g	Dummy indicating if poster picture was clear
		Poster_2colors  byte    %8.0g	Dummy indicating poster has 2 or more colors
		Poster_3colors  byte    %8.0g	Dummy indicating poster has 3 or more colors
		Poster_picture  byte    %8.0g	Dummy indicating poster has a picture or image in it
		Poster_multiple byte    %8.0g	Dummy indicating poster picture includes multiple posters
		Poster_alldups  str17   %17s	Variable identifying group for posters that look the same
		Poster_month    byte    %8.0g	Variable indicating month when the poster was collected
		post            byte    %8.0g	 
		Treatment       byte    %8.0g	Variable indicating that the psoter was produced in a treatment store
		
		Attractive_ra~t float   %9.0g	Average ratings 1 (least attractive) to 5 (most attractive)
		Attractive_ra~d float   %9.0g	Average ratings normalized for each rater as follows:(rating_bucket-meanbucket)
		Attractive_pc~s float   %9.0g	% attractiveness raters that could not read at least one language in the poster
		Attractive_p~25 float   %9.0g	% attractiveness raters with age<25 years
		Attractive_p~ee float   %9.0g	% attractiveness raters with a university degree
		Attractive_p~le float   %9.0g	% attractiveness raters that were female
		Useful_rating~t float   %9.0g	Average ratings 1 (least useful) to 5 (most useful)
		Useful_rating~d float   %9.0g	Average ratings normalized for each rater as follows:(rating_bucket-meanbucket)
		Useful_pctrat~s float   %9.0g	% usefulness raters that could not read at least one language in the poster
		Useful_pctra~25 float   %9.0g	% usefulness raters with age<25 years
		Useful_pctra~ee float   %9.0g	% usefulness raters with a university degree
		Useful_pctra~le float   %9.0g	% usefulness raters that were female

		M_Attractive_~t float   %9.0g	XXXX Trial- Average ratings 1 (least attractive) to 5 (most attractive)
		M_Attractive_~d float   %9.0g	XXXX Trial- Average ratings normalized for each rater as follows: (rating_bucket-meanbucket)
		M_Attractive_~s float   %9.0g	XXXX Trial- % attractiveness raters that could not read at least one language in the poster
		M_Attractive~25 float   %9.0g	XXXX Trial-% attractiveness raters with age<25 years
		M_Attractive~ee float   %9.0g	XXXX Trial- % attractiveness raters with a university degree
		M_Attractive~le float   %9.0g	XXXX Trial- % attractiveness raters that were female
		M_Useful_rati~t float   %9.0g	XXXX Trial- Average ratings 1 (least useful) to 5 (most useful)
		M_Useful_rati~d float   %9.0g	XXXX Trial- Average ratings normalized for each rater as follows:(rating_bucket-meanbucket)
		M_Useful_pctr~s float   %9.0g	XXXX Trial- % usefulness raters that could not read at least one language in the poster
		M_Useful_pct~25 float   %9.0g	XXXX Trial- % usefulness raters with age<25 years
		M_Useful_pct~ee float   %9.0g	XXXX Trial- % usefulness raters with a university degree
		M_Useful_pct~le float   %9.0g	XXXX Trial- % usefulness raters that were female
		
		A_Attractive_~t float   %9.0g	YYYY Trial- Average ratings 1 (least attractive) to 5 (most attractive)
		A_Attractive_~d float   %9.0g	YYYY Trial- Average ratings normalized for each rater as follows:(rating_bucket-meanbucket)
		A_Attractive_~s float   %9.0g	YYYY Trial- % attractiveness raters that could not read at least one language in the poster
		A_Attractive~25 float   %9.0g	YYYY Trial- % attractiveness raters with age<25 years
		A_Attractive~ee float   %9.0g	YYYY Trial- % attractiveness raters with a university degree
		A_Attractive~le float   %9.0g	YYYY Trial- % attractiveness raters that were female
		A_Useful_rati~t float   %9.0g	YYYY Trial- Average ratings 1 (least useful) to 5 (most useful)
		A_Useful_rati~d float   %9.0g	YYYY Trial- Average ratings normalized for each rater as follows:(rating_bucket-meanbucket)
		A_Useful_pctr~s float   %9.0g	YYYY Trial- % usefulness raters that could not read at least one language in the poster
		A_Useful_pct~25 float   %9.0g	YYYY Trial- % usefulness raters with age<25 years
		A_Useful_pct~ee float   %9.0g	YYYY Trial- % usefulness raters with a university degree
		A_Useful_pct~le float   %9.0g	YYYY Trial- % usefulness raters that were female*/ 
		
*Index

*1. Clean up and generate variables from Customer Panel app data
	*1.1 Create assignment variables consistently. We already have Assign_Dimension
	*	 specifying "attractive/useful", now we create Assign_Income specifying "high/low"
	*1.2 Create indicator for latest rating per trial/income/dimension/poster
	*1.3 Relabel rating variable, make it from 1 to 5 (instead of 0 to 4)
		*and define normalized rating
	*1.4 Keep only latest rating per income/dimension/poster for XXXX's and YYYY's trials
	*1.5 Define poster variable as a string
	*1.6 Count number of customer raters
	*1.7 Drop unnecessary variables
	*1.8 Save File as_Posters_AppRatings file 
	
*2. Prepare poster_clean handcoded data 
	*2.1. Rename variables
	*2.2 Label variables
	
*3. Merge Poster_Clean data with Posters_AppRatings 
	
*4. Identify cases where there was a poster-rater language mismatch
	
*5. Run interrater reliability analyses using intraclass correlations
	*5.1 Evaluate interrater reliability for attractiveness
	*5.2 Evaluate interrater reliability for useful
		
*6. Create one line per poster including ratings and rater characteristics for 
	*each trial-dimension-income using reshape function.
	*6.1 Create Trial_Dimension_Income variable based on which I plan to expand  
	*    the data(similar to "dadmom" in example above)
	*6.2 Widen the data expanding the following variables based on each 
		*Trial_Dimension_Income: rating_bucket rating_normalized rater_age 
		*rater_education rater_gender langmismatch
	
*7. Estimate correlations within and across trials

		*7.1 Examine correlation across attractiveness ratings:
		*7.2 Examine correlation across usefulness ratings:

*8. Fill in ratings for posters that were not rated (since a duplicate was rated)
	*8.1 Fill in numeric missing values
	*8.2 Fill in string missing values

*9. Create summary measures for ratings and rater characteristics per trial
	*	- Aggregate values for each dimension in each trial, 
	*	- Aggregate values for each dimension across trials

	*9.1 Aggregate variables for attractiveness
		*9.1.1 Across both trials
		*9.1.2 XXXX's Trial
		*9.1.3 YYYY's Trial

	*9.2 Aggregate variables for usefulness
		*9.2.1 Across both trials
		*9.2.2 XXXX's Trial
		*9.2.3 YYYY's Trial

*10. Delete input variables
*11. Save CustomerPanel Dataset

* ********************************************************************************

***************************************************************************************
* preliminaries
***************************************************************************************
clear

cd "T:\Data Prep and Analyses\B. Data Preparation\01. Customer Panel Data\output"

set more off
set memory 1g
set matsize 5000

* start log
	cd "T:\Data Prep and Analyses\B. Data Preparation\01. Customer Panel Data\programs\logs"
	local c_date = c(current_date)
	local c_time = c(current_time)
	local c_time_date = "`c_date'"+"_" +"`c_time'"
	local time_string = subinstr("`c_time_date'", ":", "_", .)
	local time_string = subinstr("`time_string'", " ", "_", .)
	log using log_`time_string', text

*******************************************************************************************

use "T:\Data Prep and Analyses\B. Data Preparation\01. Customer Panel Data\input\CustomerPanel.dta", clear

*******************************************************************************************
*1. Clean up and generate variables from Customer Panel app data
*******************************************************************************************

	//-----------------------------------
	*1.1 Create assignment variables consistently. We already have Assign_Dimension
	*	 specifying "attractive/useful", now we create Assign_Income specifying "high/low"
	//-----------------------------------
	
		gen Assign_Income = "high" if(Assign_High_Income==1 & Assign_Low_Income==0)
		replace Assign_Income = "low" if(Assign_High_Income==0 & Assign_Low_Income==1)
		
		drop Assign_High_Income Assign_Low_Income
		
		*Label assignment variables
		label variable Assign_Income "Categorical variable indicating whether the rating was done by a 'high' or 'low' income customer"
		label variable Assign_Dimension "Categorical variable indicating whether the poster was rated based on the 'attractive' or 'useful' criterion"

	//-----------------------------------
	*1.2 Create indicator for latest rating per trial/income/dimension/poster
	//-----------------------------------
		gsort trial_name poster Assign_Income Assign_Dimension -time
		by trial_name poster Assign_Income Assign_Dimension: gen Lasttofirst_rating=_n
		gen Latest_rating=(Lasttofirst_rating==1)

		*Label latest rating
		label variable Latest_rating "Dummy indicating the rating was the last received for a given poster-trial-income-dimension condition"
	
	//-----------------------------------
	*1.3 Relabel rating variable, make it from 1 to 5 (instead of 0 to 4)
		*and define normalized rating
	//-----------------------------------
	
		rename bucket rating_bucket 
		
		replace rating_bucket=rating_bucket+1

		bysort trial_name time rater_id: egen meanbucket=mean(rating_bucket)
		bysort trial_name time rater_id: egen sdbucket=sd(rating_bucket)
		bysort trial_name time rater_id: egen countbucket=count(rating_bucket)
		drop countbucket
		
		gen rating_normalized=(rating_bucket-meanbucket)/sdbucket
		drop meanbucket sdbucket
	
	//-----------------------------------
	*1.4 Keep only latest rating per income/dimension/poster for XXXX's and YYYY's trials
	//-----------------------------------
	
		keep if(trial_name=="Trial XXXX" | trial_name=="2017-02-12 Trial YYYY")
		keep if Latest_rating==1
		drop time Lasttofirst_rating Latest_rating
		
	//-----------------------------------
	*1.5 Define poster variable as a string
	//-----------------------------------
		tostring poster, format(%05.0f) replace

	//-----------------------------------
	*1.6 Count number of customer raters
	//-----------------------------------
			sort rater_id
			by rater_id: gen r=_n==1 
			*# raters:
			count if r==1 
			*keep it sorted by trial_name
			sort trial_name
	
	//-----------------------------------
	*1.7 Drop unnecessary variables
	//-----------------------------------
		drop proctor_name rater_id rating_location rater_income r
	
	//-----------------------------------
	*1.8 Save File as_Posters_AppRatings file 
	//-----------------------------------
	save "Poster_AppRatings", replace

	clear
	
*******************************************************************************************
*2. Prepare poster_clean handcoded data 
*******************************************************************************************
	
	use "T:\Data Prep and Analyses\B. Data Preparation\01. Customer Panel Data\input\poster_clean731_Final_Stata.dta", clear
	
	//-----------------------------------
	*2.1. Rename variables
	//-----------------------------------
		rename shop Store
		rename brand Brand
		rename name Poster_author
		rename empid Poster_authorID
		rename clear Poster_clear
		rename hindi Poster_hindi
		rename english Poster_english
		rename colors2 Poster_2colors
		rename colors3 Poster_3colors
		rename imagepicture Poster_picture
		rename multiple Poster_multiple
		rename alldups Poster_alldups
		rename tr Treatment
		rename month Poster_month

	//-----------------------------------
	*2.2 Label variables
	//-----------------------------------
		label variable Store "Store code"
		label variable Poster_author "Name of the author of the poster"
		label variable Poster_authorID "Employee ID of the author of the poster"
		label variable Poster_clear "Dummy indicating if poster picture was clear"
		label variable Poster_hindi "Dummy indicating if poster has Hindi characters"
		label variable Poster_english "Dummy indicating if poster has English characters"
		label variable Poster_2colors "Dummy indicating poster has 2 or more colors"
		label variable Poster_3colors "Dummy indicating poster has 3 or more colors"
		label variable Poster_picture "Dummy indicating poster has a picture or image in it"
		label variable Poster_multiple "Dummy indicating poster picture includes multiple posters"
		label variable Poster_alldups "Variable identifying group for posters that look the same"
		label variable Treatment "Variable indicating that the psoter was produced in a treatment store"
		label variable Poster_month "Variable indicating month when the poster was collected"
		
*******************************************************************************************
*3. Merge Poster_Clean data with Posters_AppRatings 
*******************************************************************************************
	
	merge 1:m poster using  "Poster_AppRatings"
		
*******************************************************************************************
*4. Identify cases where there was a poster-rater language mismatch
*******************************************************************************************
	
		gen lang_mismatch=(Poster_hindi==1 & rater_readhindi==0 | Poster_english==1 & rater_readenglish==0)
		drop Poster_hindi rater_readhindi Poster_english rater_readenglish

*******************************************************************************************
*5. Run interrater reliability analyses using intraclass correlations
*******************************************************************************************

	gen 	ratertype="HIMa" if (trial_name=="Trial XXXX" & Assign_Income=="high")
	replace ratertype="LIMa" if (trial_name=="Trial XXXX" & Assign_Income=="low")
	replace	ratertype="HIAr" if (trial_name=="2017-02-12 Trial YYYY" & Assign_Income=="high")
	replace ratertype="LIAr" if (trial_name=="2017-02-12 Trial YYYY" & Assign_Income=="low")
	
	save temp1, replace
	
	//-----------------------------------
	*5.1 Evaluate interrater reliability for attractiveness
	//-----------------------------------
		
		*5.1.1 Agreement on attractiveness across all rater types of both trials 
				*Agreement among High Income-XXXX, Low Income-XXXX, 
				*High Income-YYYY, and Low Income-YYYY
				drop if(Assign_Dimension=="useful")
				icc rating_bucket poster ratertype, cons
		
		*5.1.2 Agreement on attractiveness across all XXXX raters
				*Agreement between High Income-XXXX& Low Income-XXXX
				drop if(trial_name=="2017-02-12 Trial YYYY")
				icc rating_bucket poster ratertype, cons
		
		*5.1.3 Agreement on attractiveness across all YYYY raters 
				*Agreement between High Income-YYYY and Low Income-YYYY
				clear
				use temp1
				drop if(Assign_Dimension=="useful" | trial_name=="Trial XXXX")
				icc rating_bucket poster ratertype, cons
	
	//-----------------------------------
	*5.2 Evaluate interrater reliability for useful
	//-----------------------------------
	
		*5.2.1 Agreement on usefulness across all rater types of both trials 
				clear
				use temp1
				drop if(Assign_Dimension=="attractive")
				icc rating_bucket poster ratertype, cons
		
		*5.2.2 Agreement on usefulness across all XXXX raters
				*Agreement between High Income-XXXX& Low Income-XXXX
				drop if(trial_name=="2017-02-12 Trial YYYY")
				icc rating_bucket poster ratertype, cons
		
		*5.2.3 Agreement on usefulness across all YYYY raters 
				*Agreement between High Income-YYYY and Low Income-YYYY
				clear
				use temp1
				drop if(Assign_Dimension=="attractive" | trial_name=="Trial XXXX")
				icc rating_bucket poster ratertype, cons

*******************************************************************************************
*6. Create one line per poster including ratings and rater characteristics for 
	*each trial-dimension-income using reshape function.
*******************************************************************************************

	*Bring in  temp1 dataset saved in step 5 before running interrater reliability analyses
		
		clear
		use temp1
		*Drop ratertype from interrater reliability analyses
		drop ratertype

	//-----------------------------------
	*6.1 Create Trial_Dimension_Income variable
	//-----------------------------------

		gen Trial_Dimension_Income="_Ma_At_HI" if(trial_name=="Trial XXXX" & Assign_Dimension=="attractive" & Assign_Income=="high")
		replace Trial_Dimension_Income="_Ma_At_LI" if(trial_name=="Trial XXXX" & Assign_Dimension=="attractive" & Assign_Income=="low")
		replace Trial_Dimension_Income="_Ma_Us_HI" if(trial_name=="Trial XXXX" & Assign_Dimension=="useful" & Assign_Income=="high")
		replace Trial_Dimension_Income="_Ma_Us_LI" if(trial_name=="Trial XXXX" & Assign_Dimension=="useful" & Assign_Income=="low")

		replace Trial_Dimension_Income="_Ar_At_HI" if(trial_name=="2017-02-12 Trial YYYY" & Assign_Dimension=="attractive" & Assign_Income=="high")
		replace Trial_Dimension_Income="_Ar_At_LI" if(trial_name=="2017-02-12 Trial YYYY" & Assign_Dimension=="attractive" & Assign_Income=="low")
		replace Trial_Dimension_Income="_Ar_Us_HI" if(trial_name=="2017-02-12 Trial YYYY" & Assign_Dimension=="useful" & Assign_Income=="high")
		replace Trial_Dimension_Income="_Ar_Us_LI" if(trial_name=="2017-02-12 Trial YYYY" & Assign_Dimension=="useful" & Assign_Income=="low")

		replace Trial_Dimension_Income="_Unrated_dup" if (rating_bucket==.)
		drop trial_name Assign_Dimension Assign_Income _merge

	//-----------------------------------
	*6.2 Widen the data expanding the following variables based on each 
		*Trial_Dimension_Income: rating_bucket rating_normalized rater_age 
		*rater_education rater_gender langmismatch
	//-----------------------------------
	
	reshape wide rating_bucket rating_normalized rater_age rater_education rater_gender lang_mismatch, i(poster) j(Trial_Dimension_Income) string
	drop  *_Unrated_dup

*******************************************************************************************
*7. Estimate correlations within and across trials
*******************************************************************************************

		//-----------------------------------
		*7.1 Examine correlation across attractiveness ratings:
		//-----------------------------------
			pwcorr rating_bucket_Ma_At_HI rating_bucket_Ma_At_LI rating_bucket_Ar_At_HI rating_bucket_Ar_At_LI
	
		//-----------------------------------
		*7.2 Examine correlation across usefulness ratings:
		//-----------------------------------	
			pwcorr rating_bucket_Ma_Us_HI rating_bucket_Ma_Us_LI rating_bucket_Ar_Us_HI rating_bucket_Ar_Us_LI
			
*******************************************************************************************
*8. Fill in ratings for posters that were not rated (since a duplicate was rated)
*******************************************************************************************

	//-----------------------------------
	*8.1 Fill in numeric missing values
	//-----------------------------------
		foreach x in rating_bucket rating_normalized lang_mismatch {
			foreach y in _Ma_At_HI _Ma_At_LI _Ma_Us_HI _Ma_Us_LI _Ar_At_HI _Ar_At_LI _Ar_Us_HI _Ar_Us_LI {
				sort Poster_alldups `x'`y'
				replace `x'`y' = `x'`y'[_n-1] if (Poster_alldups==Poster_alldups[_n-1] & `x'`y'==.)
			}
		}
		
	//-----------------------------------
	*8.2 Fill in string missing values
	//-----------------------------------
		foreach x in rater_age rater_education rater_gender {
			foreach y in _Ma_At_HI _Ma_At_LI _Ma_Us_HI _Ma_Us_LI _Ar_At_HI _Ar_At_LI _Ar_Us_HI _Ar_Us_LI {
				gsort Poster_alldups -`x'`y'
				replace `x'`y' = `x'`y'[_n-1] if (Poster_alldups==Poster_alldups[_n-1] & (`x'`y'=="."|`x'`y'==""))
			}
		}
		
		drop Poster_alldups

*******************************************************************************************
*9. Create summary measures for ratings and rater characteristics per trial
*******************************************************************************************
	*	- Aggregate values for each dimension in each trial, 
	*	- Aggregate values for each dimension across trials
		
	*Notice we need the labels for categorical variables, which are as follows:
	*	Education: 	phd, ma, ba, 12 or 10
	*				phd- Doctorate (PhD/DBA)
	*				ma-	Masters (MA/MSc/MTech)
	*				ba- Bachelors (BA/BSc/BTech)
	*				12-	12th
	*				10- 10th or less
	*	Gender: male, female
	*	Age: 18_to_24, 25_to_39, 40_to_60 (there was a 60 Plus but nobody answered that) 

	//-----------------------------------
	*9.1 Aggregate across both trials
	//-----------------------------------
		*9.1.1 Aggregate variables for attractiveness
			egen Attractive_rating_bucket = rowmean(rating_bucket_Ma_At_HI rating_bucket_Ma_At_LI rating_bucket_Ar_At_HI rating_bucket_Ar_At_LI)

			egen Attractive_rating_normalized = rowmean(rating_normalized_Ma_At_HI rating_normalized_Ma_At_LI rating_normalized_Ar_At_HI rating_normalized_Ar_At_LI)

			egen Attractive_pctraters_langmiss = rowmean(lang_mismatch_Ma_At_HI lang_mismatch_Ma_At_LI lang_mismatch_Ar_At_HI lang_mismatch_Ar_At_LI)
				
			gen Attractive_pctraters_under25 = 0
				foreach v in rater_age_Ma_At_HI rater_age_Ma_At_LI rater_age_Ar_At_HI rater_age_Ar_At_LI{
					replace Attractive_pctraters_under25 = Attractive_pctraters_under25 + 0.25 if(`v'=="18_to_24")
				}
			gen Attractive_pctraters_udegree = 0
				foreach v in rater_education_Ma_At_HI rater_education_Ma_At_LI rater_education_Ar_At_HI rater_education_Ar_At_LI{
					replace Attractive_pctraters_udegree = Attractive_pctraters_udegree + 0.25 if(`v'=="ba" | `v'=="ma" | `v'=="phd")
				}			

			gen Attractive_pctraters_female = 0
				foreach v in rater_gender_Ma_At_HI rater_gender_Ma_At_LI rater_gender_Ar_At_HI rater_gender_Ar_At_LI{
					replace Attractive_pctraters_female = Attractive_pctraters_female + 0.25 if(`v'=="female")
				}
				
			label variable Attractive_rating_bucket "Average ratings 1 (least attractive) to 5 (most attractive)"
			label variable Attractive_rating_normalized "Average ratings normalized for each rater as follows: (rating_bucket-meanbucket)/sdbucket"
			label variable Attractive_pctraters_langmiss "% attractiveness raters that could not read at least one language in the poster"
			label variable Attractive_pctraters_under25 "% attractiveness raters with age<25 years"
			label variable Attractive_pctraters_udegree "% attractiveness raters with a university degree"
			label variable Attractive_pctraters_female "% attractiveness raters that were female"

		*9.1.2 Aggregate variables for usefulness
	
			egen Useful_rating_bucket = rowmean(rating_bucket_Ma_Us_HI rating_bucket_Ma_Us_LI rating_bucket_Ar_Us_HI rating_bucket_Ar_Us_LI)

			egen Useful_rating_normalized = rowmean(rating_normalized_Ma_Us_HI rating_normalized_Ma_Us_LI rating_normalized_Ar_Us_HI rating_normalized_Ar_Us_LI)

			egen Useful_pctraters_langmiss = rowmean(lang_mismatch_Ma_Us_HI lang_mismatch_Ma_Us_LI lang_mismatch_Ar_Us_HI lang_mismatch_Ar_Us_LI)

			gen Useful_pctraters_under25 = 0
				foreach v in rater_age_Ma_Us_HI rater_age_Ma_Us_LI rater_age_Ar_Us_HI rater_age_Ar_Us_LI{
					replace Useful_pctraters_under25 = Useful_pctraters_under25 + 0.25 if(`v'=="18_to_24")
				}
			gen Useful_pctraters_udegree = 0
				foreach v in rater_education_Ma_Us_HI rater_education_Ma_Us_LI rater_education_Ar_Us_HI rater_education_Ar_Us_LI{
					replace Useful_pctraters_udegree = Useful_pctraters_udegree + 0.25 if(`v'=="ba" | `v'=="ma" | `v'=="phd")
				}			

			gen Useful_pctraters_female = 0
				foreach v in rater_gender_Ma_Us_HI rater_gender_Ma_Us_LI rater_gender_Ar_Us_HI rater_gender_Ar_Us_LI{
					replace Useful_pctraters_female = Useful_pctraters_female + 0.25 if(`v'=="female")
				}

			label variable Useful_rating_bucket "Average ratings 1 (least useful) to 5 (most useful)"
			label variable Useful_rating_normalized "Average ratings normalized for each rater as follows: (rating_bucket-meanbucket)/sdbucket"
			label variable Useful_pctraters_langmiss "% usefulness raters that could not read at least one language in the poster"
			label variable Useful_pctraters_under25 "% usefulness raters with age<25 years"
			label variable Useful_pctraters_udegree "% usefulness raters with a university degree"
			label variable Useful_pctraters_female "% usefulness raters that were female"
		
	//-----------------------------------
	*9.2 Aggregate variables only in XXXX's Trial
	//-----------------------------------
	
		*9.2.1 Aggregate variables for attractiveness in XXXX's trial

			egen M_Attractive_rating_bucket = rowmean(rating_bucket_Ma_At_HI rating_bucket_Ma_At_LI)

			egen M_Attractive_rating_normalized = rowmean(rating_normalized_Ma_At_HI rating_normalized_Ma_At_LI )

			egen M_Attractive_pctraters_langmiss = rowmean(lang_mismatch_Ma_At_HI lang_mismatch_Ma_At_LI )
				
			gen M_Attractive_pctraters_under25 = 0
				foreach v in rater_age_Ma_At_HI rater_age_Ma_At_LI {
					replace M_Attractive_pctraters_under25 = M_Attractive_pctraters_under25 + 0.5 if(`v'=="18_to_24")
				}
			gen M_Attractive_pctraters_udegree = 0
				foreach v in rater_education_Ma_At_HI rater_education_Ma_At_LI {
					replace M_Attractive_pctraters_udegree = M_Attractive_pctraters_udegree + 0.5 if(`v'=="ba" | `v'=="ma" | `v'=="phd")
				}			

			gen M_Attractive_pctraters_female = 0
				foreach v in rater_gender_Ma_At_HI rater_gender_Ma_At_LI {
					replace M_Attractive_pctraters_female = M_Attractive_pctraters_female + 0.5 if(`v'=="female")
				}

			label variable M_Attractive_rating_bucket "XXXX Trial- Average ratings 1 (least attractive) to 5 (most attractive)"
			label variable M_Attractive_rating_normalized "XXXX Trial- Average ratings normalized for each rater as follows: (rating_bucket-meanbucket)/sdbucket"
			label variable M_Attractive_pctraters_langmiss "XXXX Trial- % attractiveness raters that could not read at least one language in the poster"
			label variable M_Attractive_pctraters_under25 "XXXX Trial-% attractiveness raters with age<25 years"
			label variable M_Attractive_pctraters_udegree "XXXX Trial- % attractiveness raters with a university degree"
			label variable M_Attractive_pctraters_female "XXXX Trial- % attractiveness raters that were female"
		
		*9.2.2 Aggregate variables for usefulness in XXXX's Trial
		
			egen M_Useful_rating_bucket = rowmean(rating_bucket_Ma_Us_HI rating_bucket_Ma_Us_LI)

			egen M_Useful_rating_normalized = rowmean(rating_normalized_Ma_Us_HI rating_normalized_Ma_Us_LI )

			egen M_Useful_pctraters_langmiss = rowmean(lang_mismatch_Ma_Us_HI lang_mismatch_Ma_Us_LI )
				
			gen M_Useful_pctraters_under25 = 0
				foreach v in rater_age_Ma_Us_HI rater_age_Ma_Us_LI {
					replace M_Useful_pctraters_under25 = M_Useful_pctraters_under25 + 0.5 if(`v'=="18_to_24")
				}
			gen M_Useful_pctraters_udegree = 0
				foreach v in rater_education_Ma_Us_HI rater_education_Ma_Us_LI {
					replace M_Useful_pctraters_udegree = M_Useful_pctraters_udegree + 0.5 if(`v'=="ba" | `v'=="ma" | `v'=="phd")
				}			

			gen M_Useful_pctraters_female = 0
				foreach v in rater_gender_Ma_Us_HI rater_gender_Ma_Us_LI {
					replace M_Useful_pctraters_female = M_Useful_pctraters_female + 0.5 if(`v'=="female")
				}
				
			label variable M_Useful_rating_bucket "XXXX Trial- Average ratings 1 (least useful) to 5 (most useful)"
			label variable M_Useful_rating_normalized "XXXX Trial- Average ratings normalized for each rater as follows: (rating_bucket-meanbucket)/sdbucket"
			label variable M_Useful_pctraters_langmiss "XXXX Trial- % usefulness raters that could not read at least one language in the poster"
			label variable M_Useful_pctraters_under25 "XXXX Trial- % usefulness raters with age<25 years"
			label variable M_Useful_pctraters_udegree "XXXX Trial- % usefulness raters with a university degree"
			label variable M_Useful_pctraters_female "XXXX Trial- % usefulness raters that were female"
		
	//-----------------------------------
	*9.3 Aggregate variables only in YYYY's Trial
	//-----------------------------------

		*9.3.1 Aggregate variables for attractiveness in YYYY's Trial
		
			egen A_Attractive_rating_bucket = rowmean(rating_bucket_Ar_At_HI rating_bucket_Ar_At_LI)

			egen A_Attractive_rating_normalized = rowmean(rating_normalized_Ar_At_HI rating_normalized_Ar_At_LI )

			egen A_Attractive_pctraters_langmiss = rowmean(lang_mismatch_Ar_At_HI lang_mismatch_Ar_At_LI )

			gen A_Attractive_pctraters_under25 = 0
				foreach v in rater_age_Ar_At_HI rater_age_Ar_At_LI {
					replace A_Attractive_pctraters_under25 = A_Attractive_pctraters_under25 + 0.5 if(`v'=="18_to_24")
				}
			gen A_Attractive_pctraters_udegree = 0
				foreach v in rater_education_Ar_At_HI rater_education_Ar_At_LI {
					replace A_Attractive_pctraters_udegree = A_Attractive_pctraters_udegree + 0.5 if(`v'=="ba" | `v'=="ma" | `v'=="phd")
				}			

			gen A_Attractive_pctraters_female = 0
				foreach v in rater_gender_Ar_At_HI rater_gender_Ar_At_LI {
					replace A_Attractive_pctraters_female = A_Attractive_pctraters_female + 0.5 if(`v'=="female")
				}
				
			label variable A_Attractive_rating_bucket "YYYY Trial- Average ratings 1 (least attractive) to 5 (most attractive)"
			label variable A_Attractive_rating_normalized "YYYY Trial- Average ratings normalized for each rater as follows: (rating_bucket-meanbucket)/sdbucket"
			label variable A_Attractive_pctraters_langmiss "YYYY Trial- % attractiveness raters that could not read at least one language in the poster"
			label variable A_Attractive_pctraters_under25 "YYYY Trial- % attractiveness raters with age<25 years"
			label variable A_Attractive_pctraters_udegree "YYYY Trial- % attractiveness raters with a university degree"
			label variable A_Attractive_pctraters_female "YYYY Trial- % attractiveness raters that were female"
	
		*9.3.2 Aggregate variables for usefulness in YYYY's Trial
		
			egen A_Useful_rating_bucket = rowmean(rating_bucket_Ar_Us_HI rating_bucket_Ar_Us_LI)

			egen A_Useful_rating_normalized = rowmean(rating_normalized_Ar_Us_HI rating_normalized_Ar_Us_LI )

			egen A_Useful_pctraters_langmiss = rowmean(lang_mismatch_Ar_Us_HI lang_mismatch_Ar_Us_LI )

			gen A_Useful_pctraters_under25 = 0
				foreach v in rater_age_Ar_Us_HI rater_age_Ar_Us_LI {
					replace A_Useful_pctraters_under25 = A_Useful_pctraters_under25 + 0.5 if(`v'=="18_to_24")
				}

			gen A_Useful_pctraters_udegree = 0
				foreach v in rater_education_Ar_Us_HI rater_education_Ar_Us_LI {
					replace A_Useful_pctraters_udegree = A_Useful_pctraters_udegree + 0.5 if(`v'=="ba" | `v'=="ma" | `v'=="phd")
				}			

			gen A_Useful_pctraters_female = 0
				foreach v in rater_gender_Ar_Us_HI rater_gender_Ar_Us_LI {
					replace A_Useful_pctraters_female = A_Useful_pctraters_female + 0.5 if(`v'=="female")
				}
				
			label variable A_Useful_rating_bucket "YYYY Trial- Average ratings 1 (least useful) to 5 (most useful)"
			label variable A_Useful_rating_normalized "YYYY Trial- Average ratings normalized for each rater as follows: (rating_bucket-meanbucket)/sdbucket"
			label variable A_Useful_pctraters_langmiss "YYYY Trial- % usefulness raters that could not read at least one language in the poster"
			label variable A_Useful_pctraters_under25 "YYYY Trial- % usefulness raters with age<25 years"
			label variable A_Useful_pctraters_udegree "YYYY Trial- % usefulness raters with a university degree"
			label variable A_Useful_pctraters_female "YYYY Trial- % usefulness raters that were female"

*******************************************************************************************
*10. Delete input variables
*******************************************************************************************
	drop rating_bucket_* rating_normalized_* rater_age_* rater_education_* rater_gender_* lang_mismatch_*

*******************************************************************************************
*11. Save CustomerPanel Dataset
*******************************************************************************************
	save "CustomerPanel_Dataset", replace

*******************************************************************************************
log close
* ********************************************************************************
* ********************************************************************************
* Attendance
* ********************************************************************************
* ********************************************************************************
* author: Kyle Thomas
* modified by: Tatiana Sandino 
* date: February 14, 2017
* purpose: compute weekly attendance promoter data
* ********************************************************************************
* Inputs: B. Data Preparation/02. Attendance Data/input
* Outputs: B. Data Preparation/02. Attendance Data/output
* Steps:
* 1. Read and Merge Data
* 2. Generate weekly unique promoters on store-brand level
* ********************************************************************************

***************************************************************************************
* preliminaries
***************************************************************************************

	* start log
		cd "T:\Data Prep and Analyses\B. Data Preparation\02. Attendance Data\programs\logs"
		local c_date = c(current_date)
		local c_time = c(current_time)
		local c_time_date = "`c_date'"+"_" +"`c_time'"
		local time_string = subinstr("`c_time_date'", ":", "_", .)
		local time_string = subinstr("`time_string'", " ", "_", .)
		log using log_`time_string', text

	* change into working directory
		cd "T:\Data Prep and Analyses\B. Data Preparation\02. Attendance Data\output"

* ********************************************************************************
* 1. Read and Merge Data
* ********************************************************************************

	* local for each sheet
		local months jan-2017 dec-2016 nov-2016 oct-2016 sep-2016 aug-2016 jul-2016 jun-2016 may-2016

	* import 
		foreach i in `months'{
			import excel "T:\Data Prep and Analyses\B. Data Preparation\02. Attendance Data\input\attendance_work.xlsx", sheet("`i'") firstrow clear
			save `i', replace
		}
		
	* combine data
		qui foreach x in `months'{
			append using `x', force
		}

* ********************************************************************************
* 2. Generate Weekly Measures
* ********************************************************************************

	*format date
		gen week = wofd(date)
		format week %tw

	* fix brands 
		replace brand = lower(brand)
		**fix brand name related typos
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		*redacted
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

	* redefine shop for promoters that were misidentified (confirmed with MPR)
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		*redacted
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		
	* drop locations not in experiment
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		*redacted
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

	/*Adjustments to exclude one-time promoter visits to stores 
	for induction or training, and to fix misspellings leading to
	double-counting promoters (TATIANA CHANGE)*/
	
		* before counting names, fix misspelled names (when caught) and 
		* make all names lower case 
		
			* fix misspelled names 
				*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
				*redacted
				*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
			
			* make all names lower case
				replace name=lower(name)
			
		* exclude promoters that did not last more than 7 days at the company
			bysort brand name: egen GM_totaldays=nvals(date)
			drop if GM_totaldays<=7
			*2,483 out of 111,171 observations were excluded
			
		* exclude promoters visiting a shop only occassionally
			gen month=mofd(date)
			format month %tm

			bysort shop brand month name: egen visitshopmonth=nvals(date)
			bysort shop brand name: egen maxvisitshopmonth=max(visitshopmonth)
			bysort shop brand name: egen visitshoptotal=nvals(date)
			
			gen pctvisitshop=visitshoptotal/GM_totaldays
			
			drop if (visitshopmonth<=2)
		
	/*End of adjustments to exclude exceptional promoter visits to stores*/
	
	* generate number of unique promoters per shop-brand-week
		bysort shop week brand : egen n_promoters = nvals(name)

		drop GM_totaldays month visitshopmonth maxvisitshopmonth visitshoptotal pctvisitshop
		
	* generate count to be summed
		gen count = 1

	* drop duplicate day entries
		duplicates drop shop brand name date, force

	save "T:\Data Prep and Analyses\B. Data Preparation\03. Surveys\output\attendance_name", replace

	* save data 
		save attendance_name, replace

	* generate count
		collapse (sum) count (mean) n_promoters, by(shop week brand)

	* generate attendance variable
		gen attendance = count/n_promoters

	*replace to zero if  brand = corporate brand
		replace brand = "corporate brand" if brand == "corporate"
		replace count = 0 if brand == "corporate brand"
		replace n_promoters = 0 if brand == "corporate brand"
		replace attendance = 0 if brand == "corporate brand"

	* save data
		save "T:\Data Prep and Analyses\C. Master Dataset\input\attendance_count", replace

***************************************************************************************
	* end log
		log close


* ********************************************************************************
* ********************************************************************************
* Prepare Pre-experimental Survey Data
* ********************************************************************************
* ********************************************************************************
* author: Shelley Li
* date: February 1, 2017
* purpose: Prepare Pre-experimental Survey Data
* ********************************************************************************
* Inputs: B. Data Preparation/03. Surveys/input
* Outputs: B. Data Preparation/03. Surveys/output
* Steps:
* 1. Import and clean data
* ********************************************************************************

***************************************************************************************
* preliminaries
***************************************************************************************

	* start log
		cd "T:\Data Prep and Analyses\B. Data Preparation\03. Surveys\programs\logs"
		local c_date = c(current_date)
		local c_time = c(current_time)
		local c_time_date = "`c_date'"+"_" +"`c_time'"
		local time_string = subinstr("`c_time_date'", ":", "_", .)
		local time_string = subinstr("`time_string'", " ", "_", .)
		log using log_`time_string', text

	* change into working directory
		cd "T:\Data Prep and Analyses\B. Data Preparation\03. Surveys\output"

* ********************************************************************************
* 1. Import and clean data
* ********************************************************************************
	
	****import and clean the missing demographic information from the pre-experimental surveys
		import excel using "T:\Data Prep and Analyses\B. Data Preparation\03. Surveys\input\PreSurveys_missinginfo.xlsx", clear first
	
	* lowercase vars
		replace shop=lower(shop)
		replace shopname=lower(shopname)
		replace brand=lower(brand)

	* rename stores to make store names consistent across data files
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		*redacted
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	
	* make "brand" consisent with poster data file
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		*redacted
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

	*code the variables the same way as in the variable definitions for the proposed analyses	
		gen pre_gender=1 if gender=="FEMALE"
		replace pre_gender=0 if gender=="MALE"
		drop gender
		rename pre_gender gender
		
		gen edu=1 if education=="M.A." | education=="M.B.A." | education=="M.B.A. MARKETING" ///
		| education=="M.SC."  | education=="MASTER" | education=="MASTERS"
		
		replace edu=3 if education=="12th" | education=="PERSUING (B.A.)" | education=="PERSUING (BACHELOR)" ///
		| education=="PURSUING (B.A.)" | education=="PURSUING (BACHELOR)"
		
		replace edu=4 if education=="10th"
		
		replace edu=2 if education!="" & edu==.
		
		rename tenure_company_coded tenure_survey
		
		sort shop brand
		
		keep shop brand tenure_survey gender edu age 
		
		duplicates tag shop brand, gen (dup)
		tab dup
		br if dup==1
		
		foreach var in tenure_survey gender edu age{
		by shop brand: egen ave`var'=mean(`var') 
		replace `var'=ave`var'
		drop ave`var'
		}
		
		duplicates drop shop brand, force
		drop dup
		
		save PreSurveys_missinginfo, replace
	
	****import quantitiative assessment data from pre-experimental surveys
		import excel using "T:\Data Prep and Analyses\B. Data Preparation\03. Surveys\input\PreSurveys.xlsx", clear first

	*	drop missing
		drop if missing(Name)==1

		gen str6 PreQ_= string(Q)
		gen one=_n
		sort Name one
		by Name: replace PreQ_="1.10" if PreQ_[_n-1]=="1.9" & PreQ_[_n+1]=="1.11"
		drop one Region Designation Mobile CoreValue Q
	
	* rename variables 
		rename StoreCode shop
		rename Brand brand
		rename Name employee_name
		rename ANS ANS_

	* lowercase vars
		replace shop=lower(shop)
		replace brand=lower(brand)

	* rename stores to make store names consistent across data files
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		*redacted
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	
	* make "brand" consisent with poster data file
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		*redacted
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

	* group ids
		egen employeeid=group(employee_name TLName)
		*this is because employee name cannot uniquely identify an employee
		egen qid=group(PreQ_)
		**tab PreQ_ qid can tell you the qid that corresponds to the PreQ_ # 
		**note that qid=2 when PreQ_==1.10 (rather than when PreQ==1.2) - but I will clean this up in the end with Q #s similar to the post-survey questions
	
	* duplicates drop 
		duplicates tag employeeid qid, gen (dup)
		tab dup
		***no dup
		drop dup
	
	* reshape data
		sort employeeid qid ANS_
		reshape wide PreQ_ ANS_, i(employeeid) j(qid)
	
	* keep relevant data
		keep ANS_1 ANS_7 ANS_11 ANS_3 ANS_14 ANS_18 ANS_20 ANS_21 ANS_24 ANS_26 ANS_27 ANS_30 ANS_31 shop brand employee_name TLName
	
	* rename variables
		rename ANS_1 engagement1
		rename ANS_7 engagement2
		rename ANS_11 engagement3
		rename ANS_3 engagement4
		rename ANS_14 quality1
		rename ANS_18 quality2
		rename ANS_20 quality3
		rename ANS_21 motivation1
		rename ANS_24 motivation2
		rename ANS_26 motivation3
		rename ANS_27 ability1
		rename ANS_30 ability3
		rename ANS_31 ability4
	
		sort shop brand

	*investigate and process duplicated shop-brands
		duplicates tag shop brand, gen (dup)
		tab dup
		br if dup==1
		
		foreach var in engagement1 engagement4 engagement2 engagement3 quality1 quality2 quality3 motivation1 motivation2 motivation3 ability1 ability3 ability4 {
			by shop brand: egen ave`var'=mean(`var') 
			replace `var'=ave`var'
			drop ave`var'
		}
		
		duplicates drop shop brand, force
		drop dup
		
	*merge with the demographic information
		merge 1:1 shop brand using PreSurveys_missinginfo

	*all matched
		drop _m employee_name TLName
		
		gen post=0
		order shop brand post
		
		* save data
		save "T:\Data Prep and Analyses\C. Master Dataset\input\presurvey", replace
	
***************************************************************************************
	* end log
		log close

	
* ********************************************************************************
* ********************************************************************************
* Prepare Post-experimental Survey Data
* ********************************************************************************
* ********************************************************************************
* author: Shelley Li
* date: February 1, 2017
* purpose: Prepare Pre-experimental Survey Data
* ********************************************************************************
* Inputs: B. Data Preparation/03. Surveys/input
* Outputs: B. Data Preparation/03. Surveys/output
* Steps:
* 1. Import and clean data
* ********************************************************************************

***************************************************************************************
* preliminaries
***************************************************************************************

	* start log
		cd "T:\Data Prep and Analyses\B. Data Preparation\03. Surveys\programs\logs"
		local c_date = c(current_date)
		local c_time = c(current_time)
		local c_time_date = "`c_date'"+"_" +"`c_time'"
		local time_string = subinstr("`c_time_date'", ":", "_", .)
		local time_string = subinstr("`time_string'", " ", "_", .)
		log using log_`time_string', text

	*change into working directory
		cd "T:\Data Prep and Analyses\B. Data Preparation\03. Surveys\output"

* ********************************************************************************
* 1. Imort and clean data
* ********************************************************************************

	//-----------------------------------
	// Import the list that links survey ID for employees with employees' shop, brand, and employee ID
	//-----------------------------------

		* import data
			import excel using "T:\Data Prep and Analyses\B. Data Preparation\03. Surveys\input\PostSurveys_list.xlsx", sheet ("Eid") clear first

		* sort by id
			sort Eid

		* lower case shops and brands
			replace shop=lower(shop)
			replace brand=lower(brand)

		* make "brand" consisent with poster data file
			*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
			*redacted
			*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

		* save
			save eid, replace

	//-----------------------------------
	// Import the list that links survey ID for TLs with TL's shop - in case that the survey ID for employees is missing in the responses
	//-----------------------------------

		* import 
			import excel using "T:\Data Prep and Analyses\B. Data Preparation\03. Surveys\input\PostSurveys_list.xlsx", sheet ("TLid") clear first
			
		* cleanup data
			sort Tlid
			rename Tlid TLid
			replace shop=lower(shop)
			replace brand=lower(brand)
			keep TLid shop
			rename shop TLshop

		*save
			save tlid, replace

	*-------------------------------------------------
	* import tenure and gender information on shop XXXX
	*-------------------------------------------------
	
	import excel using "T:\Data Prep and Analyses\B. Data Preparation\03. Surveys\input\PostSurveys_XXXX.xlsx", clear first

	sort shop brand
	
	save postsurvey_scbl, replace
	
	*************************************************************************************
	****Prepare Post-experimental Survey Data*******************************************
	*************************************************************************************

	import excel using "T:\Data Prep and Analyses\B. Data Preparation\03. Surveys\input\PostSurveys.xlsx", clear first

	***drop responses that are missing all identifying information or all valid answers
	drop if missing(Q17_1) & missing(Q17_2)==1 & missing(Q3)==1 & missing(Q4)==1
	drop if missing(Q12_1)==1 & missing(Q12_2)==1 & missing(Q12_3)==1 & missing(Q12_4)==1 & missing(Q13_1)==1 ///
	& missing(Q13_2)==1 & missing(Q13_3)==1 & missing(Q14_1)==1 & missing(Q14_2)==1 & missing(Q14_3)==1 ///
	& missing(Q15_1)==1 & missing(Q15_2)==1 & missing(Q15_3)==1 & missing(Q15_4)==1

	rename Q17_1 TLid
	rename Q17_2 Eid

	**identify the employees for whom there are more than one set of responses
	duplicates tag Eid if Eid!=., gen (dup)
	tab dup
	*br if dup==1
	sort Eid RecordedDate

	**eliminate duplicates case by case (based on the quality of the answers and when it was answered)
	drop if Eid==32 & Q5==""
	drop if Eid==71 & Q4==""
	drop if Eid==74 & Q4==""
	drop if Eid==182 & Q4==""
	drop if Eid==208 & Q4==""
	replace Q5="YYYY" if Eid==208
	drop if Eid==256 & Q6_2=="0"
	drop dup

	**merge in the shop, brand, and employee ID of the employees
	sort Eid
	merge m:1 Eid using eid
	drop if _m==2
	drop _m

	**merge in the shop of the TL (which would be the shop of the employee in the case of missing info on employee's shop)
	sort TLid 
	merge m:1 TLid using tlid

	**fill in the missing brand and shop information for the employees
	*br if Eid==.
	replace brand= Q5 if brand==""
	replace brand=lower(brand)
	replace shop=TLshop if shop==""


	**fill in manually the missing employee IDs from company records
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		*redacted
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	
	**drop variables that we do not see use for the future analysis
	drop _m EmployeeName_ID TLshop StartDate EndDate Status IPAddress Progress Durationinseconds Finished RecordedDate ResponseId RecipientLastName RecipientFirstName RecipientEmail ///
		 ExternalReference LocationLatitude LocationLongitude DistributionChannel TLid Eid

	**rename variables so that we can tell the post-experimental responses from the pre-experimental ones once we merge them into a final dataset

	rename Q6_1 post_startmo
	rename Q6_2 post_startyr
	**the above two use textual information

	rename Q7 gender

	*code the variables the same way as in the variable definitions for the proposed analyses	
	replace gender=gender-1

	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	*redacted
	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

	rename Q8 age
	**in years
	rename Q10 edu
	**1= masters; 2=bachelors; 3=12th; 4=10th
	drop Q11 
	**Question on experience working for a mobile phone company; 
	**not in pre-experimental survey - so deleted here. 

	rename Q12_1 engagement1
	rename Q12_2 engagement2
	rename Q12_3 engagement3
	rename Q12_4 engagement4

	rename Q13_1 quality1
	rename Q13_2 quality2
	rename Q13_3 quality3

	rename Q14_1 motivation1
	rename Q14_2 motivation2
	rename Q14_3 motivation3

	rename Q15_1 ability1
	drop Q15_2 
	**Q15_2 only in post_experimental survey
	rename Q15_3 ability3
	rename Q15_4 ability4

	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	*redacted
	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

	rename tenure_coded tenure_survey
	
	**Add 3 more observations for Store XYYY (late responses)
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		*redacted
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	
	drop Q3 Q4 Q5 post_startmo post_startyr EmployeeID
	**NO missing stores or brands and 7 missing EmployeeIDs
	
	sort shop brand
	duplicates tag shop brand, gen (dup)
	br if dup!=0
	
	foreach var in tenure_survey gender edu age engagement1 engagement2 engagement3 engagement4 ///
	quality1 quality2 quality3 motivation1 motivation2 motivation3 ability1 ability3 ability4{
		by shop brand: egen ave`var'=mean(`var') 
		replace `var'=ave`var'
		drop ave`var'
		}
	
	duplicates drop shop brand, force
	drop dup
	
	gen post=1
	order shop brand post
	
	save "T:\Data Prep and Analyses\C. Master Dataset\input\PostSurvey", replace

***************************************************************************************
	* end log
		log close

 
* ********************************************************************************
* ********************************************************************************
* Sales Data
* ********************************************************************************
* ********************************************************************************
* author: Kyle Thomas
* reviewed by: Tatiana Sandino and Shelley Li
* date: January 26, 2017
* purpose: take individual store sales data and generate store-brand-week
* ********************************************************************************
* ********************************************************************************
* Inputs: B. Data Preparation\04. Sales Data\input
* Outputs: B. Data Preparation\04. Sales Data\output
* Steps
	* 1. Import and combine data
	* 2. Generate Weekly Measures
* ********************************************************************************

***************************************************************************************
* preliminaries
***************************************************************************************

	* start log
		cd "T:\Data Prep and Analyses\B. Data Preparation\04. Sales Data\programs\logs"
		local c_date = c(current_date)
		local c_time = c(current_time)
		local c_time_date = "`c_date'"+"_" +"`c_time'"
		local time_string = subinstr("`c_time_date'", ":", "_", .)
		local time_string = subinstr("`time_string'", " ", "_", .)
		log using log_`time_string', text

	* change into working directory
		cd "T:\Data Prep and Analyses\B. Data Preparation\04. Sales Data\output"

* ********************************************************************************
* 1. Import and combine data
* ********************************************************************************

* set up local to contain all store abbreviations
	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	*redacted (local store contains store names)
	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

	* use for loop to import each file
		qui foreach x in `stores'{
			* import
				cd "T:\Data Prep and Analyses\B. Data Preparation\04. Sales Data\input"
				import delimited `x'_sales.csv, clear
				cd "T:\Data Prep and Analyses\B. Data Preparation\04. Sales Data\output"
			* drop unnecessary data
				cap drop invoiceno
				cap drop itemcolour
				cap drop cc
				cap drop imei
				cap drop imei2mobile
				cap drop isbajaj
				cap drop customername
				cap drop customerphone
				cap drop shopcomments
				cap drop datecreated
			* rename data
				rename salesamtincltax sales_tax_amt
				rename amountexclvat sales_notax_amt
			* convert strings to numbers
				foreach z in sales_tax_amt sales_notax_amt totalprofit{
					replace `z'=subinstr(`z',",","",.)
					destring `z', replace
				}
			* save and clear
				save `x'_sales.dta, replace
				clear
		}
		
	* combine data
		qui foreach x in `stores'{
			append using `x'_sales, force
		}

* ********************************************************************************
* 2. Generate Weekly Measures
* ********************************************************************************

	* total sales for store week; total sales for each brand for each week for each store
	* primary brands and "other brand" category

	* -----------------------------------
	* Create Date
	* -----------------------------------

	* generate unique id for sorting
		gen id = _n

	* format date
		gen x = substr(invoicedatetime,1,10)
		gen x2 = date(x,"MDY")
		format x2 %td
		drop x invoicedatetime
		rename x2 sales_date

	* generate week
		gen week = wofd(sales_date)
		format week %tw

	* rename location to shop
		rename location shop

	* -----------------------------------
	* Flag Brands
	* -----------------------------------

	* fix brand variable
		rename group2 brands
		replace brands = lower(brands)

	* fix brand name related errors
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		*redacted
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

	* flag main brands
		gen main_brands = 0

		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		*redacted
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		foreach i in XXX{
			replace main_brands = 1 if brands == "`i'"
		}

	* flag other brands
		gen other_brands = 0
		replace other_brands = 1 if main_brands == 0
		replace brands = "other" if other_brands==1

	* -----------------------------------
	* Related to the brand "Home Credit"
	* -----------------------------------

	* fix variable
		replace homecreditcharges = subinstr(homecreditcharges,",","",.)
		replace homecreditcharges = "0" if homecreditcharges==""
		destring homecreditcharges, replace

	* get shop-week totals
		gen hcsale = sales_tax_amt if ishomecredit=="yes"
		bysort shop week : egen sales_hc = total(hcsale)
		gen profit_hc = 0

	* -----------------------------------
	* Generate Sales for Each Brand
	* -----------------------------------

	* generate sales for each main brand
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		*redacted: brand names removed
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		foreach i in XXX{

			* initiate blank sales vars
			gen totalprofit_`i' = 0
			gen sales_tax_amt_`i' = 0

			* replace blanks with values
			replace totalprofit_`i' = totalprofit if brands == "`i'"
			replace sales_tax_amt_`i' = sales_tax_amt if brands == "`i'"

			*create weekly sales
			bysort shop week : egen profit_`i' = total(totalprofit_`i')
			bysort shop week : egen sales_`i' = total(sales_tax_amt_`i')

			*drop individual sales
			drop totalprofit_`i'
			drop sales_tax_amt_`i'

		}	

	* -----------------------------------
	* Create Total Sales for Store-Week (no home credit numbers are included)
	* -----------------------------------

	bysort shop week : egen profit_all = total(totalprofit)
	bysort shop week : egen sales_all = total(sales_tax_amt)

	* -----------------------------------
	* Number of Sales-Days for Store-Week
	* -----------------------------------

	bysort shop week : egen days = nvals(sales_date)

	* -----------------------------------
	* clean data
	* -----------------------------------
		duplicates drop shop week, force
		drop group1 brands itemname incentive totalprofit homecreditcharges sales_tax_amt budgetused sales_notax_amt taxrate vatamt discount profit salesamtdifference normaldpinclvat finalnetdpinclvat id sales_date main_brands other_brands

	* -----------------------------------
	* create sales and profit
	* -----------------------------------
	
		gen id = _n
		reshape long sales profit, i(id) j(brand) string
		replace brand = subinstr(brand,"_","",.)
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		*redacted
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		rename sales sales_tax
		drop id
		
	* save sales data
		save "T:\Data Prep and Analyses\C. Master Dataset\input\store_week_brand_sales", replace

***************************************************************************************
	* end log
		log close

 



* ********************************************************************************
* ********************************************************************************
* Store Characteristics
* ********************************************************************************
* ********************************************************************************
* author: Kyle Thomas
* reviewed by: Shelley Li and Tatiana Sandino
* date: February 16, 2017
* purpose: calculate distance between stores and between stores and head office
* ********************************************************************************
* ********************************************************************************
* Inputs: B. Data Preparation\05. Store Characteristics\input
* Outputs: B. Data Preparation\05. Store Characteristics\output
* Steps
	* 1. Make dataset of store latitude and longitude
	* 2. Get store distances
	* 3. Make final dataset
	* 4. Add store characteristics
***************************************************************************************
***************************************************************************************

***************************************************************************************
* preliminaries
***************************************************************************************

	* start log
		cd "T:\Data Prep and Analyses\B. Data Preparation\05. Store Characteristics\programs\logs"
		local c_date = c(current_date)
		local c_time = c(current_time)
		local c_time_date = "`c_date'"+"_" +"`c_time'"
		local time_string = subinstr("`c_time_date'", ":", "_", .)
		local time_string = subinstr("`time_string'", " ", "_", .)
		log using log_`time_string', text

	* change into working directory
		cd "T:\Data Prep and Analyses\B. Data Preparation\05. Store Characteristics\output"

*********************************************************************
*********************************************************************
* 1. Make dataset of store latitude and longitude
*********************************************************************
*********************************************************************

	*----------------------------------------
	* get new addresses
	*----------------------------------------

	* import latitude and longitude data
		import excel "T:\Data Prep and Analyses\B. Data Preparation\05. Store Characteristics\input\mpr_coordinates_new.xlsx", sheet("detail") firstrow clear

	* drop blank coordinates 
		drop if lon==.

	* keep relevant variables
		keep stata_store lat lon

	* save new data 
		save mpr_coordinates_new, replace

	*----------------------------------------
	* get old addresses
	*----------------------------------------

	* import old data
		use "T:\Data Prep and Analyses\B. Data Preparation\05. Store Characteristics\input\_mpr_coordinates_combined", clear

	* keep relevant data
		keep stata_store lat lon

	* append new addresses
		append using mpr_coordinates_new

	* drop stores not in experiment
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		*redacted
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

	* drop duplicates
		duplicates drop stata_store, force

	* make head office entry last in list
		replace stata_store = "Zmpr_HEAD_OFFICE" if stata_store=="mpr_HEAD_OFFICE"
		sort stata_store
		replace stata_store = "mpr_HEAD_OFFICE" if stata_store=="Zmpr_HEAD_OFFICE"

	* save
		save new_mpr_coor, replace

*********************************************************************
*********************************************************************
* 2. Get store distances
*********************************************************************
*********************************************************************

	*initiate an incremental variable at one
		local x 1

	*establish store names that can be used in creating unique variable identifiers and unique file names
	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	*redacted: removed store names
	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	foreach val in XXXX{

		*use original dataset
			use new_mpr_coor, clear

		*generate id
			gen id = _n

		*create starting location by concatenating latitude and longitude
			gen lat_start = lat 
			gen lon_start = lon

		*set ending location to be the same as the starting location of whichever site we are on
			gen lat_end=lat_start if id==`x'
			gen lon_end=lon_start if id==`x'

		*these next steps fill lat_lon_end with the same end location coordinates,
			gsort -lat_end
			replace lat_end=lat_end[_n-1] if missing(lat_end)

			gsort -lon_end
			replace lon_end=lon_end[_n-1] if missing(lon_end)
		
			sort id

		*the distance calculation function
			geodist lat_start lon_start lat_end lon_end, gen(dist) miles

		*prep data for merge by giving each calculation a unqiue name in accordance with whatever store we are currently on
			rename dist dist_`val'

		*save with unique name in accordance with whatever store we are currently on and clear
			save mpr_coordinates_`val', replace
			clear

		*increment the x counter by 1, e.g., on the third time through this will increment x to 4
			local x `++x'
	}

*********************************************************************
*********************************************************************
* 3. Make final dataset
*********************************************************************
*********************************************************************

	*----------------------------------------
	* combine store data
	*----------------------------------------

		use mpr_coordinates_XXX, clear

		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		*redacted: removed store names
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

		foreach val in XXX {

			*merge the datasets
				merge 1:1 id using mpr_coordinates_`val'.dta

			*drop the merge indicator to prevent error
				drop _merge
		}

		*save final results
			save mpr_coordinates_combined_new, replace

	*----------------------------------------
	* reshape data
	*----------------------------------------

		drop lat lon id lat_start lon_start lat_end lon_end 

		gen id = _n

		reshape long dist_, i(id) j(end_store) string

	*----------------------------------------
	* clean data
	*----------------------------------------

		*rename and order
			rename stata_store start_store
			drop id 
			rename dist_ distance
			order start_store end_store distance

		*flag if distance is a head office to distance
			gen ho_dist = 1 if end_store=="mpr_HEAD_OFFICE"

		* drop if distance is greater than 1 mile and it is not a head office distance
			drop if distance>1 & ho_dist!=1
		
		* drop if distance is distance to itself
			drop if distance==0

		* drop if store is head office
			drop if start_store=="mpr_HEAD_OFFICE"

		* compute head office distance; take min by store for collapse
			replace ho_dist = ho_dist*distance
			bysort start_store : egen hodist = min(ho_dist)

		* intialize counter for stores
			gen close_stores = 1

		* drop the head office rows
			bysort start_store : egen nclose = nvals(end_store)
			drop if end_store == "mpr_HEAD_OFFICE" & nclose!=1
			replace close_stores = 0 if end_store=="mpr_HEAD_OFFICE"

		* collapse to get number of close stores
			collapse (sum) close_stores (min) hodist, by(start_store)

		* rename variable
			rename start_store shop

		* save
			save store_distances, replace

*********************************************************************
*********************************************************************
* 4. Add store characteristics
*********************************************************************
*********************************************************************

	* import store characteristics
		import excel "T:\Data Prep and Analyses\B. Data Preparation\05. Store Characteristics\input\store_characteristics.xlsx", sheet("details") firstrow clear

	*merge in distances
		merge 1:1 shop using store_distances
		drop _m

	* save
		save "T:\Data Prep and Analyses\C. Master Dataset\input\store_characteristics", replace

***************************************************************************************
	* end log
		log close




* ********************************************************************************
* ********************************************************************************
* Clean Poster System Activity Data*
* ********************************************************************************
* ********************************************************************************
* author: Shelley Li
* date: January 26, 2017
* purpose: Clean Poster System Activity Data*
* ********************************************************************************
* ********************************************************************************
* Inputs: B. Data Preparation\06. Poster System Data\input
* Outputs: B. Data Preparation\06. Poster System Data\output
* Steps
	* 1. Import and clean
***************************************************************************************
***************************************************************************************

***************************************************************************************
* preliminaries
***************************************************************************************

	* start log
	  cd "T:\Data Prep and Analyses\B. Data Preparation\06. Poster System Data\programs\logs"
	  local c_date = c(current_date)
	  local c_time = c(current_time)
	  local c_time_date = "`c_date'"+"_" +"`c_time'"
	  local time_string = subinstr("`c_time_date'", ":", "_", .)
	  local time_string = subinstr("`time_string'", " ", "_", .)
	  log using log_`time_string', text

	* change into output folder
	  cd "T:\Data Prep and Analyses\B. Data Preparation\06. Poster System Data\output"

***************************************************************************************
* 1. Import and clean
***************************************************************************************

	import excel using "T:\Data Prep and Analyses\B. Data Preparation\06. Poster System Data\input\EmployeeList_01302017.xlsx", clear first

	keep user_name store_code employee_ID brand employee_internal_ID
	rename user_name account_name
	sort employee_internal_ID

	save employeeaccounts, replace


	import excel using "T:\Data Prep and Analyses\B. Data Preparation\06. Poster System Data\input\ActivityLog_01232017.xlsx", clear first

	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	*redacted: private information, e.g. usernames, etc. 
	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

	gen month=month(timestamp)
	gen day=day(timestamp)
	gen year=year(timestamp)

	merge m:1 employee_internal_ID using employeeaccounts
	drop if _m==2
	drop _m

	rename store_code shop
	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	*redacted: private information, e.g. store names, etc.
	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	
	drop if shop==""

	replace shop=lower(shop)
	replace brand=lower(brand)
	
	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	*redacted: private information, e.g. store names and brand names
	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

	save activitylog, replace

	**generate store-level frequency of access variable
	tab1 shop brand
	gen one=1
	sort shop
	collapse (sum) one, by(shop)
	rename one accessfreq
	sort shop

	save shop_accessfreq, replace
	
	**generate store-level frequency of active access
	use activitylog, clear
	drop if action=="Leave" | action=="Logout"
	gen one=1
	sort shop
	collapse (sum) one, by(shop)
	rename one accessfreq_active
	sort shop
	
	save shop_activeaccessfreq, replace
	
	**generate store-level frequency of access using only "log ins"
	use activitylog, clear
	keep if action=="Login"
	gen one=1
	sort shop
	collapse (sum) one, by(shop)
	rename one accessfreq_login
	sort shop
	
	save shop_loginaccessfreq, replace

***************************************************************************************
  log close
* ********************************************************************************
* ********************************************************************************
* Merge Store Data
* ********************************************************************************
* ********************************************************************************
* author: Kyle Thomas, Shelley Li
* date: February 16, 2017
* modified: Tatiana Sandino 
* date: 3/18/2017
* modified: Shelley Li
* date: 3/19/2017
* purpose: merge store characteristics, sales data, survey, and attendance data
* ********************************************************************
* ********************************************************************************
* Inputs: C. Master Dataset\input
* Outputs: C. Master Dataset\output
* Steps
	* 1. Import and clean sales data
	* 2. Merge in other datasets
	* 3. Construct new measures and clean  
* **************************************************************************************

***************************************************************************************
* preliminaries
***************************************************************************************

	* start log
	  cd "T:\Data Prep and Analyses\C. Master Dataset\programs\logs"
	  local c_date = c(current_date)
	  local c_time = c(current_time)
	  local c_time_date = "`c_date'"+"_" +"`c_time'"
	  local time_string = subinstr("`c_time_date'", ":", "_", .)
	  local time_string = subinstr("`time_string'", " ", "_", .)
	  log using log_`time_string', text

	* change into output folder
	  cd "T:\Data Prep and Analyses\C. Master Dataset\input"

***************************************************************************************
* 1. Import and clean sales data
***************************************************************************************

	* use sales data
		use store_week_brand_sales, clear

	* drop dates before experiment started
		drop if week < tw(2016w18)

		drop if brand=="all" 
		drop if brand=="other"

	* merge in attendance data 
		merge 1:1 shop brand week using attendance_count
		drop if _m==2
		drop _m 

		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		*redacted
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		
	* merge in store characteristics
		merge m:1 shop using store_characteristics
		drop _m
		
	* set sales and attendance as missing during renovation or after closing (*TATIANA CHANGE)
	
		* sales missing during renovation or during closing week
		replace sales_tax=. if (week>=wofd(closed) & week<=wofd(reopen) & closed!=. & reopen!=.)
		replace sales_tax=. if (week>=wofd(closed) & closed!=. & reopen==.)

		* attendance missing during renovation or during closing week
		replace attendance=. if (week>=wofd(closed) & week<=wofd(reopen) & closed!=. & reopen!=.)
		replace attendance=. if (week>=wofd(closed) & closed!=. & reopen==.)

	* define or redefine three store characteristics variables 
				
		* market type (to include store characteristics according to the managing director)
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		*redacted: private information related to store names
		*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		
		* renovation (defined as dummy=1 after a renovation during the period, zero otherwise)
		replace renovation=0 if(renovation==.)
		replace renovation=0 if(week<wofd(closed) & closed!=. & reopen!=.)
		
		*monthbeforeclosing
		replace permanent_closed=0 if(permanent_closed==.)
		gen monthbeforeclosing=permanent_closed
		replace monthbeforeclosing=0 if(week<(wofd(closed)-4) & closed!=.)

	* generate post indicator
		**Sep 1, 2016 is the last day of 2016w35; 
		**Sep 1 to Sep 5 is the first system update. 
		gen post=1 if week>=tw(2016w36)
		replace post=0 if week<tw(2016w36)
	
	*Other data preparations originally done under Proposed Analyses program 
			* check the sales number 
				sum sales,d
				drop if sales_tax<=0
				winsor sales_tax, gen (sales_w) p(0.01)
				
			* set maximum attendance=7 but leave another variable, attendanceUC, unconstrained
				gen attendanceUC=attendance
				replace attendance=7 if(attendance>7 & attendance!=.)
	
			**Only Keep the Store-brands that Appeared in Both Pre- and Post- Periods
				sort shop brand post
				by shop brand: egen n=count(post)
				by shop brand post: egen n_post=count(post)
				by shop brand post: egen msales=mean(sales_tax)
				by shop brand post: egen mattendance=mean(attendance)
				by shop brand post: egen mattendanceUC=mean(attendanceUC)
				gen sp=n-n_post
				drop if sp==0
				drop n n_post sp 
					
			**generate pre-period average sales and attendance data
				gen lmsales=log(1+msales)
				sort shop brand post
				by shop brand: replace lmsales=lmsales[1]
				by shop brand: replace mattendance=mattendance[1]
				by shop brand: replace mattendanceUC=mattendanceUC[1]

			***we should determine which weeks in November to drop
			***Dec 1 is the last day of w48, Nov 3 is the last day of w44.
				gen nov=1 if week>tw(2016w44) & week <=tw(2016w48)
				replace nov=0 if nov==.
				
			**drop the weeks after Jan 28, 2017
				drop if week>=tw(2017w5) 

			**generate more regression variables
				gen lsales=log(1+sales_tax)
				gen trxpost=tr*post
				gen store_age= end_age/365
	
			*make brand names consistent across files
				*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
				*redacted
				*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
			
			* refinement of sales days -t here are a few cases where days>7 
			* this is because Stata defines w52 as a longer week
			* - In these cases, redefine sales days as 7
				replace days=7 if(days>7 & week==tw(2016w52))

***************************************************************************************
* 2. Merge in other datasets
***************************************************************************************			

	* merge in survey data
		replace shop=lower(shop)
		merge m:1 shop brand post using "T:\Data Prep and Analyses\C. Master Dataset\output\master_survey.dta"
		drop if _m==2
		drop _m
		
	*merge in the median tenure data to fill the missing values in tenure
		sort brand post
		merge m:1 brand post using "T:\Data Prep and Analyses\C. Master Dataset\output\median_tenuregender_brand.dta"
		drop if _m==2
		drop _m med_tenureBG ave_gender
		
		sort post
		merge m:1 post using "T:\Data Prep and Analyses\C. Master Dataset\output\median_tenuregender.dta"
		drop if _m==2

***************************************************************************************
* 3. Construct new measures and clean
***************************************************************************************
	
	*construct tenure measures using median brand-period value and median period value to fill the missing values
		gen tenure_bg_brand=tenure_bestguess
		replace tenure_bg_brand=med_tenureBG_brand if tenure_bestguess==.
		
		gen tenure_bg_period=tenure_bestguess
		replace tenure_bg_period=med_tenureBG if tenure_bestguess==.
		
	*construct gender measures using average brand-period value and average period value to fill the missing values
		gen gender_fmissing_brand=gender
		replace gender_fmissing_brand=ave_gender_brand if gender==.
		
		gen gender_fmissing=gender
		replace gender_fmissing=ave_gender if gender==.
		
		drop med_tenureBG_brand med_tenureBG ave_gender_brand ave_gender
		
		* clean data
		replace count = 0 if count == .
		rename count promoter_days
		replace n_promoter=0 if n_promoter==.
		
		**drop those with 0 promoters (indicating that this brand didn't exist at this store)
				drop if n_promoter==0

		sort shop week brand
		order shop week brand sales_tax days promoter_days
		drop open start_date end_date _m 
		destring sqft, replace
		destring start_age, replace
		destring end_age, replace
		destring hodist, replace
		destring close_stores, replace
		drop if tr==.
		gen motivation= (motivation1+motivation2+motivation3)/3
		gen ability= (ability1+ability3+ability4)/3


	**label variables
		label variable sales_tax "Sales w VAT Tax"
		label variable promoter_days "Promoter_days"
		label variable days "Sales Days"
		label var lsales "Sales"
		label var tr "Info Sharing"
		label var post "Post"
		label var trxpost "Info Sharing x Post"
		label var n_promoters "# of promoters for the brand at the store"
		label var sqft "store physical size"
		label var store_age "store age"
		label var days "sales days"
		label var close_stores "# of nearby stores"
		label var hodist "distance to head office"
		label var tenure_survey "Tenure_Survey"
		label var tenure_bestguess "Tenure_BestGuess"
		label var tenure_bg_period "Tenure fill w/ median period"
		label var tenure_bg_brand "Tenure fill w/ median brand"
		label var TL_changed "TL Change"
		
		label var gender "Gender"
		label var gender_fmissing "Gender fill w/ mean period"
		label var gender_fmissing_brand "Gender fill w/ mean brand"
		label var lmsales "Pre-Intervention Sales"
		label var mattendance "Pre-Intervention Attendance"
		label var age "Age" 
		label var edu  "Education"
		label var renovation "Renovation"
		label var permanent_closed "Permanent Close"
		label var monthbeforeclosing "Month Before Closing"
		
	* save data
		cd ../output
		save store_data, replace
		save "T:\Data Prep and Analyses\D. Analyses\05. Graphs\inputs\store_data", replace

***************************************************************************************
  log close

* ********************************************************************************
* ********************************************************************************
* Create the Master Survey Dataset
* ********************************************************************************
* ********************************************************************************
* author: Shelley Li
* date: March 5, 2017
* purpose: create master survey dataset
* ********************************************************************
* ********************************************************************************
* Inputs: C. Master Dataset\input
* Outputs: C. Master Dataset\output
* Steps
	* 1. Import treatment indicator
	* 2. Import "best guesses" of the tenure measures
	* 3. Import data on TL change
	* 4. Append the Two Survey Datasets
* **************************************************************************************

***************************************************************************************
* preliminaries
***************************************************************************************

	* start log
	  cd "T:\Data Prep and Analyses\C. Master Dataset\programs\logs"
	  local c_date = c(current_date)
	  local c_time = c(current_time)
	  local c_time_date = "`c_date'"+"_" +"`c_time'"
	  local time_string = subinstr("`c_time_date'", ":", "_", .)
	  local time_string = subinstr("`time_string'", " ", "_", .)
	  log using log_`time_string', text

	* change into output folder
	  cd "T:\Data Prep and Analyses\C. Master Dataset\input"

***************************************************************************************
* 1. Import treatment indicator
***************************************************************************************

	import excel using "T:\Data Prep and Analyses\C. Master Dataset\input\tr_indicator.xlsx", clear first
	replace shop=lower(shop)
	save tr_indicator.dta, replace
	
***************************************************************************************
* 2. Import "best guesses" of the tenure measures
***************************************************************************************

	*import best guesses for the pre-experimental survey
	
	import excel using "T:\Data Prep and Analyses\B. Data Preparation\03. Surveys\input\tenure_bestguess.xlsx", sheet ("bestguess_pre") first clear
	
	sort shop brand
	
	drop EmployeeName
	
	foreach var in tenure_survey tenure_QB tenure_bestguess {
	by shop brand: egen ave`var'=mean(`var')
	replace `var'=ave`var'
	drop ave`var'
	}
	
	duplicates drop shop brand, force
	
	gen post=0
	
	save bestguess_pre, replace
	
	*import best guesses for the post-experimental survey
	
	import excel using "T:\Data Prep and Analyses\B. Data Preparation\03. Surveys\input\tenure_bestguess.xlsx", sheet ("bestguess_post") first clear
	
	sort shop brand
	
	drop EmployeeName_ID EmployeeID
	
	foreach var in tenure_survey tenure_QB tenure_bestguess {
	by shop brand: egen ave`var'=mean(`var')
	replace `var'=ave`var'
	drop ave`var'
	}
	
	duplicates drop shop brand, force
	
	gen post=1
	
	save bestguess_post, replace
	
	*append the "best guesses" files
	append using bestguess_pre
	
	sort shop brand post
	
	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	*redacted: private information on store names
	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	
	save bestguess, replace
	
	***COMMENT: WE DID NOT USE THE "BESTGUESS" DATA IN OUR FINAL REGRESSION
	
***************************************************************************************
* 3. Import data on manager (team leader) change
***************************************************************************************
	
	import excel using "T:\Data Prep and Analyses\B. Data Preparation\03. Surveys\input\tl_change.xlsx", clear first
	
	replace shop=lower(shop)
	sort shop
	save tl_change, replace
	
***************************************************************************************
* 4. Append the Two Survey Datasets
***************************************************************************************

	use "T:\Data Prep and Analyses\C. Master Dataset\input\presurvey.dta", clear
	append using "T:\Data Prep and Analyses\C. Master Dataset\input\PostSurvey.dta"
	
	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	*redacted: include private information, e.g. store names
	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	drop tenure_survey
	
	**merge with "best guesses" of tenure var
	sort shop brand post
	merge 1:1 shop brand post using bestguess
	drop _m
	
	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	*redacted: include private information, e.g. store names
	*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	**merge with treatment indicator
	merge m:1 shop using tr_indicator
	drop if _m==2
	drop _m
	drop if tr==.
	
	**merge with data on TL changes
	sort shop
	merge m:1 shop using tl_change
	drop if _m==2
	drop _m

	* save data
	save master_survey, replace
	
	**GENERATE MEDIAN VALUES FOR TENURE_BESTGUESS TO FILL IN MISSING VALUES ON TENURE LATER
	keep shop brand post tenure_bestguess gender
	
	sort brand post
	by brand post: egen med_tenureBG_brand=median(tenure_bestguess)
	by brand post: egen ave_gender_brand=mean(gender)
	
	sort post
	by post: egen med_tenureBG=median(tenure_bestguess)
	by post: egen ave_gender=mean(gender)
	
	drop tenure_bestguess gender
	
	duplicates drop brand post, force
	save median_tenuregender_brand, replace 
	
	drop med_tenureBG_brand ave_gender_brand
	duplicates drop post, force
	save median_tenuregender, replace 
	
***************************************************************************************
	* end log
		log close
* ********************************************************************************
* ********************************************************************************
* Generate Datasets for Poster Analyses
* ********************************************************************************
* ********************************************************************************
* author: Shelley Li
* date: March 13, 2017
* purpose: Generate Datasets for Poster Analyses
* ********************************************************************
* ********************************************************************************
* Inputs: C. Master Dataset\input
* Outputs: C. Master Dataset\output
* Steps
	*1. Generate the dataset for the analyses that use poster data as DVs
	*2. Generate the Dataset for "Poster data as explanatory variables" Analyses
* **************************************************************************************

***************************************************************************************
* preliminaries
***************************************************************************************

	* start log
		cd "T:\Data Prep and Analyses\C. Master Dataset\programs\logs"
		local c_date = c(current_date)
		local c_time = c(current_time)
		local c_time_date = "`c_date'"+"_" +"`c_time'"
		local time_string = subinstr("`c_time_date'", ":", "_", .)
		local time_string = subinstr("`time_string'", " ", "_", .)
		log using log_`time_string', text

	* change into working directory
		cd "T:\Data Prep and Analyses\C. Master Dataset\output"
		
****************************************************************************************
*1. Generate the dataset for the analyses that use poster data as DVs
****************************************************************************************
	
	*----------------------------------------------------------------------------------------
	*1.1 Prepare store data to be later merged with poster data for "poster data as DV" analyses
	*-----------------------------------------------------------------------------------------
		
		*load sales data
		use "T:\Data Prep and Analyses\C. Master Dataset\output\store_data.dta", clear
				
		sort shop week brand
		by shop week: gen n_brand=_N

		*keep the four weeks of data around each poster collection		
		gen Poster_month=7 if week>=tw(2016w26) & week<=tw(2016w29)
		replace Poster_month=8 if week>=tw(2016w31) & week<=tw(2016w34)
		replace Poster_month=10 if week>=tw(2016w37) & week<=tw(2016w40)
		replace Poster_month=12 if week>=tw(2016w49) & week<=tw(2016w52)
		drop if Poster_month==.
		
		*make the brand names consistent for later merging 
		*XXXXXXXXXXXXXXXXX
		*redacted
		*XXXXXXXXXXXXXXXXX
			
		keep shop brand n_brand Poster_month tenure_survey tenure_QB tenure_bestguess tenure_bg_brand tenure_bg_period TL_changed age gender gender_fmissing_brand gender_fmissing edu days n_promoters sqft store_age close_stores hodist market
		
		*generate the average of four weeks of data
		sort shop brand Poster_month
		foreach var in n_brand tenure_survey tenure_QB tenure_bestguess tenure_bg_brand tenure_bg_period TL_changed age gender gender_fmissing_brand gender_fmissing edu days n_promoters sqft store_age close_stores hodist {
		by shop brand Poster_month: egen average_`var'=mean(`var')
		replace `var'=average_`var'
		drop average_`var'
		}

		*keep unique shop-brand-poster collection observations
		duplicates drop shop brand Poster_month, force
		*save the temporary file for later merging
		save temp1, replace
		
		*----------------------------------------------------------------
		*1.2 prepare the survey data to be merged with the poster data 
		*----------------------------------------------------------------
		
		use "T:\Data Prep and Analyses\C. Master Dataset\output\master_survey.dta"
		drop tenure_survey age gender edu tr_notes tr tenure_QB tenure_bestguess pre_period_TL post_period_TL TL_changed
		sort shop brand post

		*save the temp file for later merging
		save temp2, replace
		
		*---------------------------------------------------------------------------------
		*1.3 merge the above sales file and survey data with the poster data
		*---------------------------------------------------------------------------------
		*load the poster data
		use "T:\Data Prep and Analyses\B. Data Preparation\01. Customer Panel Data\output\CustomerPanel_Dataset.dta", clear
		*rename var names for consistency across files 
		rename Store shop
		rename Brand brand
		rename Treatment tr
		
		*merge with store characteristics from the sales file "temp1"
		sort shop brand Poster_month
		merge m:1 shop brand Poster_month using temp1
		drop if _m==2
		drop _m
		
		*merge with survey data from temp2
		merge m:1 shop brand post using temp2
		drop if _m==2
		drop _m

		gen trxpost=tr*post
		
		**label variables
		label var tr "Info Sharing"
		label var post "Post"
		label var trxpost "Info Sharing x Post"
		label var n_promoters "# of promoters for the brand at the store"
		label var sqft "store physical size"
		label var store_age "store age"
		label var days "sales days"
		label var close_stores "# of nearby stores"
		label var hodist "distance to head office"
		label var tenure_survey "Tenure_Survey"
		label var tenure_bestguess "Tenure_BestGuess"
		label var TL_changed "TL Change"
		label var gender "Gender"
		label var age "Age" 
		label var edu "Education"
		
		*save the dataset
		save PosterData_DV, replace
		
**********************************************************************************************************
*2. Generate the Dataset for "Poster data as explanatory variables" Analyses
**********************************************************************************************************
		
	*--------------------------------------------------------------
	*2.1 Prepare poster data to be later merged with sales data
	*--------------------------------------------------------------
		*load poster data
		use "T:\Data Prep and Analyses\B. Data Preparation\01. Customer Panel Data\output\CustomerPanel_Dataset.dta", clear
		*rename for consistency across files
		rename Store shop
		rename Brand brand
				
		*collapse to the shop-brand-Poster_month level
		drop poster posterfilename Poster_author Poster_authorID Treatment
		order shop brand Poster_month
		sort shop brand Poster_month
		
		collapse (mean)Poster_clear-A_Useful_pctraters_female, by (shop brand Poster_month) 
				
		**Generate an overall quality score that incorporates both attractiveness and usefulness for table 7
		gen quality_rating=(Attractive_rating_bucket+Useful_rating_bucket)/2
		gen quality_rating_norm=(Attractive_rating_normalized+Useful_rating_normalized)/2
		gen A_quality_rating=(A_Attractive_rating_bucket+A_Useful_rating_bucket)/2
		gen A_quality_rating_norm=(A_Attractive_rating_normalized+A_Useful_rating_normalized)/2
		gen M_quality_rating=(M_Attractive_rating_bucket+M_Useful_rating_bucket)/2
		gen M_quality_rating_norm=(M_Attractive_rating_normalized+M_Useful_rating_normalized)/2
		
		**generate period-average, pre-period value, indicators on whether the pre-period value is above or below (equal to) the sample median
		foreach var in Attractive_rating_bucket Attractive_rating_normalized Useful_rating_bucket Useful_rating_normalized ///
						M_Attractive_rating_bucket M_Attractive_rating_normalized M_Useful_rating_bucket M_Useful_rating_normalized ///
						A_Attractive_rating_bucket A_Attractive_rating_normalized A_Useful_rating_bucket A_Useful_rating_normalized ///
						quality_rating quality_rating_norm A_quality_rating A_quality_rating_norm M_quality_rating M_quality_rating_norm{
		sort shop brand post
		by shop brand post: egen m`var'=mean(`var')
		by shop brand: gen p`var'=m`var'[1]
		sort post
		by post: egen md`var'=median(p`var')
		gen pm`var'=md`var'[1]
		gen am`var'=1 if p`var'>pm`var'
		replace am`var'=0 if p`var'<=pm`var'
		drop md`var' pm`var'
		}
		
		sort shop brand Poster_month
		*save shop-brand-collection level data for later merging
		save shop_brand_postermonth, replace
		
		**for later merge with the sales dataset (but only use the pre- and post-period averages of the poster data)
		sort shop brand post
		*keep unique shop-brand-experimental period observations
		duplicates drop shop brand post, force
		*save shop-brand-experimentalperiod level data for later merging
		save poster_shop_brand_post, replace
		
		**for later merge with the main sales dataset to import the indicators on whether pre-period value is above or below sample median
		keep shop brand am*
		duplicates drop shop brand, force
		save poster_preindicator, replace
		
	*----------------------------------------------------------------------------------
	*2.2. Merge the poster data with sales data (Merge with each collection of posters)
	*----------------------------------------------------------------------------------
		
		use "T:\Data Prep and Analyses\C. Master Dataset\output\store_data.dta", clear
		
		**only keep the 2 weeks leading up to each collection and the weeks after each collection (until 2 weeks leading up to the next collection)
		gen Poster_month=7 if week>=tw(2016w28) & week<=tw(2016w32)
		replace Poster_month=8 if week>=tw(2016w33) & week<=tw(2016w38)
		replace Poster_month=10 if week>=tw(2016w39) & week<=tw(2016w50)
		replace Poster_month=12 if week>=tw(2016w51) & week<=tw(2017w5)
		drop if Poster_month==.
			
		*merge with poster data at the shop-brand-collection level
		sort shop brand Poster_month
		merge m:1 shop brand Poster_month using shop_brand_postermonth
		drop if _m==2
		gen submit=1 if _m==3
		replace submit=0 if _m==1
		drop _m
				
		*save the sales dataset that uses each collection of the poster data as the explanatory variables
		save Sales_PosterData_EV, replace
	
	*--------------------------------------------------------------------------------------------------------------
	*2.3 Merge the poster data with sales data (with the average poster quality for the pre-and post-periods)
	*--------------------------------------------------------------------------------------------------------------
		*load data
		use "T:\Data Prep and Analyses\C. Master Dataset\output\store_data.dta", clear

		*merge with poster data at the shop-brand-experimentalperiod level
		sort shop brand post
		merge m:1 shop brand post using poster_shop_brand_post
		drop if _m==2
		drop _m
		
		drop am* Poster_month- M_quality_rating_norm
		*save the sales dataset that uses the pre- and post-period averages of the poster data as the explanatory variables
		save Sales_PosterData_EV_Average, replace
		
***************************************************************************************
* end log
log close
	***************************************************************************************
	*****Purpose: Generate Analyses Tables Listed in the Original Proposal *******
	*****Created by: Shelley Li
	*****Date: Feb 14, 2017
	*****Modifications by: Tatiana Sandino
	*****Date: Mar 19, 2017
	*****      Sept 13, 2017- Verified if we had more than one poster per shop-brand-month
	*****                     in the creativity regressions
	***************************************************************************************

	* start log
		cd "T:\Data Prep and Analyses\D. Analyses\02. Main Analysis\programs\logs"
		local c_date = c(current_date)
		local c_time = c(current_time)
		local c_time_date = "`c_date'"+"_" +"`c_time'"
		local time_string = subinstr("`c_time_date'", ":", "_", .)
		local time_string = subinstr("`time_string'", " ", "_", .)
		log using log_`time_string', text
		
	* change into working directory
		cd "T:\Data Prep and Analyses\D. Analyses\02. Main Analysis\output"

	set more off
	
	*Choose tenure from the choices: tenure_survey tenure_bg_period tenure_bg_brand 
	local tenure="tenure_survey"

	*Choose gender from the choices: gender gender_fmissing gender_fmissing_brand
	local gender="gender"
	
	*Choose whether to exclude november or not: 
		*to exclude it choose exclnov="if nov==0" and exclnovinlist= "if nov==0 &"
		*to NOT exclude it choose exclnov="" and exclnovinlist= "if"
	local exclnov="if nov==0"
	local exclnovinlist= "if nov==0 &"
	
	****************************************************************************************
	******ANALYSES THAT USE THE MASTER SALES-ATTENDANCE DATASET store_data.dta**************
	******i.e. TABLES 1, 3, 6, 7, 10, TABLE 5 COLUMNS 1&2************************************
	****************************************************************************************
	
	* load the master file for sales-attendance-store characteristics
	use "T:\Data Prep and Analyses\C. Master Dataset\output\store_data.dta", clear
	
	merge m:1 shop using "T:\Data Prep and Analyses\B. Data Preparation\06. Poster System Data\output\shop_activeaccessfreq.dta"
	drop if _m==2
	drop _m
	
	merge m:1 shop using "T:\Data Prep and Analyses\B. Data Preparation\06. Poster System Data\output\shop_accessfreq.dta"
	drop if _m==2
	drop _m
	
	gen engagement= engagement1+engagement4+engagement2+engagement3
	gen quality= quality1+quality2+quality3
	
	
	*Keep the sales-analysis sample the same as the attendance-analysis sample
	keep if mattendance!=.
	
	qui: xi: reg lsales tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales i.brand `exclnov', vce (cluster shop)
	keep if e(sample)
	
	estpost sum lsales attendance tr post n_promoters sqft store_age days close_stores hodist tenure_survey gender lmsales mattendance accessfreq_active
	esttab,replace label cells("count mean sd min max")
	
	pwcorr lsales attendance tr post n_promoters sqft store_age days close_stores hodist tenure_survey gender lmsales mattendance accessfreq_active, sig
	
	
	*get descriptive stats for accessfreq
	summarize accessfreq accessfreq_active, detail
	tabulate accessfreq 
	
	pwcorr accessfreq accessfreq_active sales_tax attendance engagement quality, sig
	
	*--------------------------------------------------------------------------
	*Descriptive Statistics of Main Financial/Attendance Variables- Spot Errors
	*(NEW TATIANA CHANGES)
	*--------------------------------------------------------------------------
	*Summarize DV
	summarize sales_tax lsales attendance attendanceUC motivation ability, detail
	histogram lsales
	set more off
	graph save Graph "Histogram LnSales (Excludes sales less than or equal to 0).gph", replace
	histogram attendance
	set more off
	graph save Graph "Histogram Attendance (Setting attendance greater than 7 to 7).gph", replace
	histogram attendanceUC
	graph save Graph "Histogram Attendance Unconstrained.gph", replace


	* sales have been already restricted to be > 0 in "merge.do"
	
	*Summarize EV
	summarize n_promoters sqft store_age days close_stores hodist tenure_survey tenure_bestguess tenure_bg_period tenure_bg_brand gender gender_fmissing_brand gender_fmissing TL_changed lmsales mattendance age edu renovation monthbeforeclosing close_stores, detail
	tabulate market, missing
	
	***RED FLAGS AND RESOLUTIONS: 
	*n_promoters: large numbers, going up to 19- these had to be trainings, not promoters working at the stores
		*this was fixed to exclude promoters that showed up at a store that was not their home store
		*see explanations on attendance.do
	*days: we have some sales days equal to 9- weeks cannot have more than 7 days
		*clarified by Shelley and Kyle, this is just based on Stata convention (accomodating the last week of the year)
		*we cap days at 7 in the program merge.do
	*attendance: we had some attending more than 7 days a week. This is partly 		
		* but not entirely explained by the week 52 defined by Stata
		* We cap attendance at 7 in program merge.do
	*sqft: one of the stores appeared to be 15525 sqft big
		*there was a typo with DGJF which should have read 155.25 
		*this was fixed on the excel spreadsheet used as input
				
	label var `tenure' "Tenure" 
	label var `gender' "Gender"

	*----------------------------------------------
	*Table 1 - Financial Performance and Engagement
	*----------------------------------------------
	
	eststo clear
	
	*Financial performance
	qui: eststo: xi: reg lsales tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales i.brand `exclnov', vce (cluster shop)
	*Attendance
	qui: eststo: xi: tobit attendance tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' mattendance i.brand `exclnov', vce (cluster shop) ul(7)
		
	*show formatted table results directly in the command window
	esttab, replace b(4) r2 label ///
    keep(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales mattendance) ///
    order(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales mattendance) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0fc %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Sales" "Attendance")

	*export formatted table results into a csv file that can be opened and editted in Excel
	esttab using table1_sales-attendance.csv, replace b(4) r2 label ///
    keep(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales mattendance) ///
    order(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales mattendance) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Sales" "Attendance")
    eststo clear

	*--------------------------------------------------------------------------------------
	*Table 2 - Financial Performance and Attendance: Split by Post_Early & Post_Late
	*--------------------------------------------------------------------------------------

	gen Post_Early=1 if week>=tw(2016w36) & week<=tw(2016w44)
	replace Post_Early=0 if Post_Early==.
	gen Post_Late=1 if week>=tw(2016w45) 
	replace Post_Late=0 if Post_Late==.
	gen trxPost_Early=tr*Post_Early
	gen trxPost_Late=tr*Post_Late

	eststo clear

	*Excluding Nov
	qui: eststo: xi: reg lsales tr Post_Early Post_Late trxPost_Early trxPost_Late n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales i.brand `exclnov', vce (cluster shop)
	qui: eststo: xi: tobit attendance tr Post_Early Post_Late trxPost_Early trxPost_Late n_promoters sqft store_age days close_stores hodist `tenure' `gender' mattendance i.brand `exclnov', vce (cluster shop) ul(7)

	esttab, replace b(4) r2 label ///
    keep(tr Post_Early Post_Late trxPost_Early trxPost_Late n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales mattendance) ///
    order(tr Post_Early Post_Late trxPost_Early trxPost_Late n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales mattendance) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0fc %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Sales" "Attendance")

	esttab using table2_EarlyLate.csv, replace b(4) r2 label ///
    keep(tr Post_Early Post_Late trxPost_Early trxPost_Late n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales mattendance) ///
    order(tr Post_Early Post_Late trxPost_Early trxPost_Late n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales mattendance) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Sales" "Attendance")

    eststo clear	
set more off
	*-------------------------------------------------------------------------------------------------
	*Table 3 (Prev Table 9) - Financial Performance and Attendance: Contingent on Frequency of Access
	*--------------------------------------------------------------------------------------------------
	**Proposed Analysis
	replace accessfreq=0 if tr==0
	replace accessfreq_active=0 if tr==0 
	replace accessfreq=0 if tr==1 & post==0
	replace accessfreq_active=0 if tr==1 & post==0
	
	gen trxpostxaccessfreq=tr*post*accessfreq
	gen trxpostxactiveaccess=tr*post*accessfreq_active
	
	label var trxpostxaccessfreq "Info Sharing x Access to System x Post"
	label var trxpostxactiveaccess "Info Sharing x Active Access to System x Post"

	summarize accessfreq_active if(tr==1 & post==1 & lsales!=. & n_promoters!=.	& sqft!=. & store_age!=. & days!=. & close_stores!=. & hodist!=. & `tenure'!=. & `gender'!=.), detail 
		*Shows what stats I can save
		return list	
		gen mean_activeaccess=r(mean)
		gen med_activeaccess=r(p50)
		gen topQ_activeaccess=r(p75)
	
	bysort shop: egen shop_activeaccess=max(accessfreq_active)
	
	gen trHIaccess=(tr==1 & shop_activeaccess>med_activeaccess)
	gen trLOaccess=(tr==1 & shop_activeaccess<=med_activeaccess)
	gen trHIaccessxpost=trHIaccess*post
	gen trLOaccessxpost=trLOaccess*post
	
	label var trHIaccess "High Access"
	label var trLOaccess "Low Access"
	label var trHIaccessxpost "High Access x Post"
	label var trLOaccessxpost "Low Access x Post"
	
	eststo clear
	*qui: eststo: xi: reg lsales tr post trxpost trxpostxaccessfreq n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales i.brand `exclnov', vce (cluster shop)
	qui: eststo: xi: reg lsales tr post trxpost trxpostxactiveaccess n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales i.brand `exclnov', vce (cluster shop)
	*qui: eststo: xi: tobit attendance tr post trxpost trxpostxaccessfreq n_promoters sqft store_age days close_stores hodist `tenure' `gender' mattendance i.brand `exclnov', vce (cluster shop) ul(7)
	qui: eststo: xi: tobit attendance tr post trxpost trxpostxactiveaccess n_promoters sqft store_age days close_stores hodist `tenure' `gender' mattendance i.brand `exclnov', vce (cluster shop) ul(7)
	
	esttab, replace b(4) r2 label ///
    keep(tr post trxpost trxpostxaccessfreq trxpostxactiveaccess n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales mattendance) ///
    order(tr post trxpost trxpostxaccessfreq trxpostxactiveaccess n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales mattendance) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Sales" "Attendance")

	esttab using table3_prev9_access_fin-att.csv, replace b(4) r2 label ///
    keep(tr post trxpost trxpostxactiveaccess n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales mattendance) ///
    order(tr post trxpost trxpostxactiveaccess n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales mattendance) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Sales" "Attendance")
	
	**Fixed effects

	eststo clear
	qui: eststo: xi: reg lsales post trxpost trxpostxactiveaccess i.shop i.brand `exclnov', vce (cluster shop)
	qui: eststo: xi: tobit attendance post trxpost trxpostxactiveaccess i.shop i.brand `exclnov', vce (cluster shop) ul(7)
	
	esttab, replace b(4) r2 label ///
    keep(post trxpost trxpostxactiveaccess) ///
    order(post trxpost trxpostxactiveaccess) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Sales" "Attendance")

	esttab using table3__FEaccess_fin-att.csv, replace b(4) r2 label ///
    keep(post trxpost trxpostxactiveaccess) ///
    order(post trxpost trxpostxactiveaccess) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Sales" "Attendance")

	eststo clear
	
	**Include in a single analysis above and below or equal to median 
	qui: eststo: xi: reg lsales trHIaccess trLOaccess post trHIaccessxpost trLOaccessxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales i.brand `exclnov', vce (cluster shop)
	qui: eststo: xi: tobit attendance trHIaccess trLOaccess post trHIaccessxpost trLOaccessxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' mattendance i.brand `exclnov', vce (cluster shop) ul(7)

	esttab, replace b(4) r2 label ///
    keep(post trHIaccess trLOaccess trHIaccessxpost trLOaccessxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales mattendance) ///
    order(post trHIaccess trLOaccess trHIaccessxpost trLOaccessxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales mattendance) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0fc %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Sales" "Attendance")

	esttab using table3_prev9_hiaccess_fin-att.csv, replace b(4) r2 label ///
    keep(post trHIaccess trLOaccess trHIaccessxpost trLOaccessxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales mattendance) ///
    order(post trHIaccess trLOaccess trHIaccessxpost trLOaccessxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales mattendance) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Sales" "Attendance")

    eststo clear	
	
	*---------------------------------------------------------------------------------
	*Table 4 (Prev Table 10)- Financial Performance: Robustness Check with Store Fixed Effects
	*---------------------------------------------------------------------------------
	
	**NOTE THAT N_PROMOTER ENDS UP NOT BEING ESTIMATED IN THESE REGRESISONS
	**SUGGESTING THAT THE FEW VARIATIONS IN THIS VARIBLE ARE PERHAPS ENTIRELY CONTROLLED VIA BRAND AND SHOP FIXED EFFECS
	
		
	eststo clear

	qui: eststo: xi: reg lsales post trxpost i.brand i.shop `exclnov', vce (cluster shop)
	qui: eststo: xi: tobit attendance post trxpost i.brand i.shop `exclnov', vce (cluster shop) ul(7)
	
	esttab, replace b(4) r2 label ///
    keep(post trxpost ) ///
    order(post trxpost ) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0fc %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Sales" "Attendance")

	esttab using table4_prev10_storeFE_exclcontrols.csv, replace b(4) r2 label ///
    keep(post trxpost ) ///
    order(post trxpost ) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Sales" "Attendance")
	
	eststo clear
	*------------------------------------------------------------------
	*Table 5 (Prev Table 6-8) - Financial Performance: Subsample Analysis
	*------------------------------------------------------------------
	
	**the median number of closeby stores is 2 - SEE the store characteristics data and do files
	**Use the main specification - i.e. excluding the November data
	
	
	sort shop brand
	merge m:1 shop brand using "T:\Data Prep and Analyses\C. Master Dataset\output\poster_preindicator.dta"
	drop if _m==2
	drop _m
	
		
	eststo clear
	*6.1 More vs Fewer Nearby Stores - Original Survey Measures
	qui: eststo: xi: reg lsales tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales i.brand `exclnovinlist' close_stores>2, vce (cluster shop)
	qui: eststo: xi: reg lsales tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales i.brand `exclnovinlist' close_stores<=2, vce (cluster shop)
	*6.2 High vs Low Creative Talent - Original Survey Measures
	qui: eststo: xi: reg lsales tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales i.brand `exclnovinlist' amquality_rating==1, vce (cluster shop)
	qui: eststo: xi: reg lsales tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales i.brand `exclnovinlist' amquality_rating==0, vce (cluster shop)
	*6.3 Mainstream vs Divergent markets
	qui: eststo: xi: reg lsales tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales i.brand `exclnovinlist' market=="mainstream", vce (cluster shop)
	qui: eststo: xi: reg lsales tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales i.brand `exclnovinlist' market=="divergent", vce (cluster shop)

	esttab, replace b(4) r2 label ///
    keep(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales) ///
    order(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0fc %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("More Nearby Stores" "Fewer Nearby Stores" "High Creative Talent" "Low Creative Talent" "Mainstream" "Divergent")

	esttab using table5_fin_subsamples.csv, replace b(4) r2 label ///
    keep(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales) ///
    order(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' lmsales) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("More Nearby Stores" "Fewer Nearby Stores" "High Creative Talent" "Low Creative Talent" "Mainstream" "Divergent")

	eststo clear

	
	*FIXED EFFECTS VERSION
	eststo clear
	*6.1 More vs Fewer Nearby Stores - Original Survey Measures
	qui: eststo: xi: reg lsales tr post trxpost  i.shop i.brand `exclnovinlist' close_stores>2, vce (cluster shop)
	qui: eststo: xi: reg lsales tr post trxpost  i.shop i.brand `exclnovinlist' close_stores<=2, vce (cluster shop)
	*6.2 High vs Low Creative Talent - Original Survey Measures
	qui: eststo: xi: reg lsales tr post trxpost  i.shop i.brand `exclnovinlist' amquality_rating==1, vce (cluster shop)
	qui: eststo: xi: reg lsales tr post trxpost  i.shop i.brand `exclnovinlist' amquality_rating==0, vce (cluster shop)
	*6.3 Mainstream vs Divergent markets
	qui: eststo: xi: reg lsales tr post trxpost  i.shop i.brand `exclnovinlist' market=="mainstream", vce (cluster shop)
	qui: eststo: xi: reg lsales tr post trxpost  i.shop i.brand `exclnovinlist' market=="divergent", vce (cluster shop)

	esttab, replace b(4) r2 label ///
    keep(tr post trxpost ) ///
    order(tr post trxpost ) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0fc %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("More Nearby Stores" "Fewer Nearby Stores" "High Creative Talent" "Low Creative Talent" "Mainstream" "Divergent")

	esttab using table5_FEfin_subsamples.csv, replace b(4) r2 label ///
    keep(tr post trxpost ) ///
    order(tr post trxpost ) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("More Nearby Stores" "Fewer Nearby Stores" "High Creative Talent" "Low Creative Talent" "Mainstream" "Divergent")

	eststo clear
	
	*------------------------------------------------------------------
	* Table 8 (Similar to Previous Table 6-8) - Attendance: Subsample Analysis
	*------------------------------------------------------------------
	
	*Exploratory analyses>> do this partition using attendance
	eststo clear

	*6.1 More vs Fewer Nearby Stores - Original Survey Measures
	qui: eststo: xi: tobit attendance tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' mattendance i.brand `exclnovinlist' close_stores>2, vce (cluster shop) ul(7)
	qui: eststo: xi: tobit attendance tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' mattendance i.brand `exclnovinlist' close_stores<=2, vce (cluster shop) ul(7)
	*6.2 High vs Low Creative Talent - Original Survey Measures
	qui: eststo: xi: tobit attendance tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' mattendance i.brand `exclnovinlist' amquality_rating==1, vce (cluster shop) ul(7)
	qui: eststo: xi: tobit attendance tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' mattendance i.brand `exclnovinlist' amquality_rating==0, vce (cluster shop) ul(7)
	*6.3 Mainstream vs Divergent markets
	qui: eststo: xi: tobit attendance tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' mattendance i.brand `exclnovinlist' market=="mainstream", vce (cluster shop) ul(7)
	qui: eststo: xi: tobit attendance tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' mattendance i.brand `exclnovinlist' market=="divergent", vce (cluster shop) ul(7)

	*show formatted table results directly in the command window
	esttab, replace b(4) r2 label ///
    keep(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' mattendance) ///
    order(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' mattendance) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0fc %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("More Nearby Stores" "Fewer Nearby Stores" "High Creative Talent" "Low Creative Talent" "Mainstream" "Divergent")
	
	esttab using table8_attend_subsamples.csv, replace b(4) r2 label ///
    keep(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' mattendance) ///
    order(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' mattendance) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("More Nearby Stores" "Fewer Nearby Stores" "High Creative Talent" "LowCreativeTalent" "Mainstream" "Divergent")

	eststo clear
	
	
	*FIXED EFFECTS VERSION
	eststo clear

	*6.1 More vs Fewer Nearby Stores - Original Survey Measures
	qui: eststo: xi: tobit attendance tr post trxpost  i.shop i.brand `exclnovinlist' close_stores>2, vce (cluster shop) ul(7)
	qui: eststo: xi: tobit attendance tr post trxpost  i.shop i.brand `exclnovinlist' close_stores<=2, vce (cluster shop) ul(7)
	*6.2 High vs Low Creative Talent - Original Survey Measures
	qui: eststo: xi: tobit attendance tr post trxpost  i.shop i.brand `exclnovinlist' amquality_rating==1, vce (cluster shop) ul(7)
	qui: eststo: xi: tobit attendance tr post trxpost  i.shop i.brand `exclnovinlist' amquality_rating==0, vce (cluster shop) ul(7)
	*6.3 Mainstream vs Divergent markets
	qui: eststo: xi: tobit attendance tr post trxpost  i.shop i.brand `exclnovinlist' market=="mainstream", vce (cluster shop) ul(7)
	qui: eststo: xi: tobit attendance tr post trxpost  i.shop i.brand `exclnovinlist' market=="divergent", vce (cluster shop) ul(7)

	*show formatted table results directly in the command window
	esttab, replace b(4) r2 label ///
    keep(tr post trxpost ) ///
    order(tr post trxpost ) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0fc %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("More Nearby Stores" "Fewer Nearby Stores" "High Creative Talent" "Low Creative Talent" "Mainstream" "Divergent")
	
	esttab using table8_FEattend_subsamples.csv, replace b(4) r2 label ///
    keep(tr post trxpost ) ///
    order(tr post trxpost ) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("More Nearby Stores" "Fewer Nearby Stores" "High Creative Talent" "LowCreativeTalent" "Mainstream" "Divergent")

	eststo clear
	
	****************************************************************************************
	******ANALYSES THAT USE THE POSTER VARIABLES AS DVs "PosterData_DV.dta"****************
	******i.e. TABLES 2 & 5 COLUMNS 3&4 ****************************************************
	****************************************************************************************
	
	*--------------------------------------------
	*Table 1 - Quality of Creative Work
	*--------------------------------------------
	
	*load the poster data as DV dataset
	use "T:\Data Prep and Analyses\C. Master Dataset\output\PosterData_DV.dta", clear	
		*TATIANA: THIS DATASET COMES FROM 03. Prepare Poster Datasets.do 
		*		  AND ADDS STORE MEASURES TO THE POSTER RATINGS, USING 
		*		  AVERAGES BASED ON THE 4 WEEKS SURROUNDING THE POSTER
		*		  COLLECTION.	
	merge m:1 shop using "T:\Data Prep and Analyses\B. Data Preparation\06. Poster System Data\output\shop_activeaccessfreq.dta"
	drop if _m==2
	drop _m
	merge m:1 shop using "T:\Data Prep and Analyses\B. Data Preparation\06. Poster System Data\output\shop_accessfreq.dta"
	drop if _m==2
	drop _m
				
	*local tenure="tenure_survey"
	*local gender="gender"

	gen engagement= engagement1+engagement4+engagement2+engagement3
	gen quality= quality1+quality2+quality3
	
	pwcorr accessfreq accessfreq_active engagement quality Useful_rating_bucket Attractive_rating_bucket, sig
	**as you can see, access frequency is positively and significantly correlated with survey_based engagement measures, with survey-based quality measures, 
	**and with poster quality measures from customer panels.
	**survey-based engagement measure is also positively and significantly correlated with poster quality measures
	
	bysort Poster_month tr: sum Useful_rating_bucket
	bysort Poster_month tr:sum Attractive_rating_bucket
	**not so much for the novelty measure
	
	bysort Poster_month: egen ave_value=mean(Useful_rating_bucket)
	bysort Poster_month: egen ave_novelty=mean(Attractive_rating_bucket)
	bysort Poster_month: egen sd_value=sd(Useful_rating_bucket)
	bysort Poster_month: egen sd_novelty=sd(Attractive_rating_bucket)
	gen value_fromMean=abs(Useful_rating_bucket-ave_value)
	gen value_SDfromMean=abs(Useful_rating_bucket-ave_value)/sd_value
	gen novelty_fromMean=abs(Attractive_rating_bucket-ave_novelty)
	gen novelty_SDfromMean=abs(Attractive_rating_bucket-ave_novelty)/sd_novelty
			
	*--------------------------------------
	*Analyses using the AVERAGE measures
	*--------------------------------------
	
	**generate pre-period average novelty and value data
	sort shop brand post
	by shop brand post: egen mAttractive=mean(Attractive_rating_bucket)
	by shop brand post: egen mAttractiveNorm=mean(Attractive_rating_normalized)
	by shop brand post: egen mUseful=mean(Useful_rating_bucket)
	by shop brand post: egen mUsefulNorm=mean(Useful_rating_normalized)
	
	by shop brand: gen pre_Attractive=mAttractive[1]
	by shop brand: gen pre_AttractiveNorm=mAttractiveNorm[1]
	by shop brand: gen pre_Useful=mUseful[1]
	by shop brand: gen pre_UsefulNorm=mUsefulNorm[1]
	
	**label vars
	label var pre_Attractive "Pre-Intervention Novelty of Creative Work"
	label var pre_Useful "Pre-Intervention Value of Creative Work"
	label var Poster_clear "Poster Image is Clear"
	label var Poster_multiple "Containing Multiple Poster Images"
	label var Useful_pctraters_langmiss "Value_Language Mismatch"
	label var Attractive_pctraters_langmiss "Novelty_Language Mismatch"
	
	
	qui: xi: reg Useful_rating_bucket tr post trxpost n_promoters sqft store_age days close_stores hodist tenure_survey gender pre_Useful i.brand, vce (cluster shop)
	keep if e(sample)
	
	sum Useful_rating_bucket Attractive_rating_bucket pre_Useful pre_Attractive
	
	pwcorr Useful_rating_bucket Attractive_rating_bucket tr post n_promoters sqft store_age days close_stores hodist tenure_survey gender pre_Useful pre_Attractive accessfreq_active, sig

		
	*-------------------------------	
	* Table 1- Creativity Measures
	*-------------------------------
		
	eststo clear
	
	**Specified as proposed 
	qui: eststo: xi: reg Useful_rating_bucket tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful i.brand, vce (cluster shop)
	qui: eststo: xi: reg Attractive_rating_bucket tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Attractive i.brand, vce (cluster shop)
	
	esttab, replace b(4) r2 label ///
    keep(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful pre_Attractive) ///
    order(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful pre_Attractive) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0fc %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Value" "Novelty")
	
	esttab using table1_poster.csv, replace b(4) r2 label ///
    keep(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful pre_Attractive) ///
    order(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful pre_Attractive) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Value" "Novelty")
	
	eststo clear
		
		*Check whether there is more than one poster per shop-brand-month in these regressions
		xi: reg Attractive_rating_bucket tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Attractive i.brand, vce(cluster shop)
		predict attres, residual
		gen attresdummy=(attres!=.)
		bysort Poster_month shop brand attresdummy: gen poscount=_n
		count if poscount==1 & attresdummy==1
		count if poscount==2 & attresdummy==1
		count if poscount==3 & attresdummy==1
		count if poscount==4 & attresdummy==1
		count if poscount==5 & attresdummy==1 
		br Poster_month shop brand poscount attres Attractive_rating_bucket Useful_rating_bucket
		*ANSWER: YES, 64 observations correspond to cases where there was more than one poster per shop-brand-month;*/
	
	*-----------------------------------------------------------------
	* Table 3- Exploratory similar to Table 9: Contingent on Frequency of Access
	*-----------------------------------------------------------------
	**Proposed Analysis
	replace accessfreq=0 if tr==0
	replace accessfreq_active=0 if tr==0 
	replace accessfreq=0 if tr==1 & post==0
	replace accessfreq_active=0 if tr==1 & post==0
	
	gen trxpostxaccessfreq=tr*post*accessfreq
	gen trxpostxactiveaccess=tr*post*accessfreq_active
	
	label var trxpostxaccessfreq "Info Sharing x Access x Post"
	label var trxpostxactiveaccess "Info Sharing x Active Access x Post"

	summarize accessfreq_active if(tr==1 & post==1 & Useful_rating_bucket!=. & n_promoters!=. & sqft!=. & store_age!=. & days!=. & close_stores!=. & hodist!=. & `tenure'!=. & `gender'!=.), detail 
		*Shows what stats I can save
		return list	
		gen mean_activeaccess=r(mean)
		gen med_activeaccess=r(p50)
		gen topQ_activeaccess=r(p75)
	
	bysort shop: egen shop_activeaccess=max(accessfreq_active)

	gen trHIaccess=(tr==1 & shop_activeaccess>med_activeaccess)
	gen trLOaccess=(tr==1 & shop_activeaccess<=med_activeaccess)
	gen trHIaccessxpost=trHIaccess*post
	gen trLOaccessxpost=trLOaccess*post
	
	label var trHIaccess "High Access"
	label var trLOaccess "Low Access"
	label var trHIaccessxpost "High Access x Post"
	label var trLOaccessxpost "Low Access x Post"

	*Quality of creative work-

	eststo clear
	*qui: eststo: xi: reg Useful_rating_bucket tr post trxpost trxpostxaccessfreq n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful i.brand, vce (cluster shop)
	qui: eststo: xi: reg Useful_rating_bucket tr post trxpost trxpostxactiveaccess n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful i.brand, vce (cluster shop)
	*qui: eststo: xi: reg Attractive_rating_bucket tr post trxpost trxpostxaccessfreq n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Attractive i.brand, vce (cluster shop) 
	qui: eststo: xi: reg Attractive_rating_bucket tr post trxpost trxpostxactiveaccess n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Attractive i.brand, vce (cluster shop) 

	esttab, replace b(4) r2 label ///
    keep(tr post trxpost trxpostxaccessfreq trxpostxactiveaccess n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful pre_Attractive) ///
    order(tr post trxpost trxpostxaccessfreq trxpostxactiveaccess n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful pre_Attractive) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0fc %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Value" "Novelty")

	esttab using table3_prev9_creative.csv, replace b(4) r2 label ///
    keep(tr post trxpost trxpostxaccessfreq trxpostxactiveaccess n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful pre_Attractive) ///
    order(tr post trxpost trxpostxaccessfreq trxpostxactiveaccess n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful pre_Attractive) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Value" "Novelty")
	eststo clear

	*Fixed Effects- Quality of creative work-

	eststo clear
	qui: eststo: xi: reg Useful_rating_bucket post trxpost trxpostxactiveaccess  i.shop i.brand, vce (cluster shop)
	qui: eststo: xi: reg Attractive_rating_bucket post trxpost trxpostxactiveaccess  i.shop i.brand, vce (cluster shop) 

	esttab, replace b(4) r2 label ///
    keep(post trxpost trxpostxaccessfreq trxpostxactiveaccess ) ///
    order(post trxpost trxpostxaccessfreq trxpostxactiveaccess ) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0fc %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Value" "Novelty")

	esttab using table3_FEaccess_creative.csv, replace b(4) r2 label ///
    keep(tr post trxpost trxpostxaccessfreq trxpostxactiveaccess ) ///
    order(tr post trxpost trxpostxaccessfreq trxpostxactiveaccess ) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Value" "Novelty")
	eststo clear
		
 	**Include a single analysis above and below or equal to the top quartile

	eststo clear
	
	qui: eststo: xi: reg Useful_rating_bucket trHIaccess trLOaccess post trHIaccessxpost trLOaccessxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful i.brand, vce (cluster shop)
	qui: eststo: xi: reg Attractive_rating_bucket trHIaccess trLOaccess post trHIaccessxpost trLOaccessxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Attractive i.brand, vce (cluster shop)
	
	esttab, replace b(4) r2 label ///
    keep(tr post trxpost trHIaccess trLOaccess trHIaccessxpost trLOaccessxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful pre_Attractive) ///
    order(tr post trxpost trHIaccess trLOaccess trHIaccessxpost trLOaccessxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful pre_Attractive) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0fc %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Useful" "Attractive")

	esttab using table3_freqaccess_creative.csv, replace b(4) r2 label ///
    keep(post trHIaccess trLOaccess trHIaccessxpost trLOaccessxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful pre_Attractive) ///
    order(post trHIaccess trLOaccess trHIaccessxpost trLOaccessxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful pre_Attractive) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Useful" "Attractive")

	eststo clear

	*------------------------------------------------------------
	* Table 4-Exploratory analyses similar to Table 10
	*------------------------------------------------------------
	eststo clear

	*Shop and brand fixed effects
	*Including controls
	qui: eststo: xi: reg Useful_rating_bucket post trxpost n_promoters days `tenure' `gender' i.brand i.shop, vce (cluster shop)
	qui: eststo: xi: reg Attractive_rating_bucket post trxpost n_promoters days `tenure' `gender' i.brand i.shop, vce (cluster shop)
	
	esttab, replace b(4) r2 label ///
    keep(post trxpost n_promoters days `tenure' `gender') ///
    order(post trxpost n_promoters days `tenure' `gender') nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0fc %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Value" "Novelty")

	esttab using table4_storeFE_creative.csv, replace b(4) r2 label ///
    keep(tr post trxpost n_promoters days `tenure' `gender') ///
    order(tr post trxpost n_promoters days `tenure' `gender') nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Value" "Novelty")

	eststo clear

	*Shop and brand fixed effects
	*Excluding controls
	qui: eststo: xi: reg Useful_rating_bucket post trxpost i.brand i.shop if n_promoters!=. & days!=. & `tenure'!=. & `gender'!=., vce (cluster shop)
	qui: eststo: xi: reg Attractive_rating_bucket post trxpost i.brand i.shop if n_promoters!=. & days!=. & `tenure'!=. & `gender'!=., vce (cluster shop)
	
	esttab, replace b(4) r2 label ///
    keep(post trxpost ) ///
    order(post trxpost ) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0fc %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Value" "Novelty")

	esttab using table4_storeFE_creative_exclcontrols.csv, replace b(4) r2 label ///
    keep(tr post trxpost ) ///
    order(tr post trxpost ) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("Value" "Novelty")

	eststo clear
		
	*------------------------------------------------------------------
	* Exploratory similar to Table 6-8 - Subsamples
	*------------------------------------------------------------------
	**the median number of closeby stores is 2 - SEE the store characteristics data and do files
	**Use the main specification - i.e. excluding the November data
	
	sort shop brand
	merge m:1 shop brand using "T:\Data Prep and Analyses\C. Master Dataset\output\poster_preindicator.dta"
	drop if _m==2
	drop _m

	eststo clear
	
	**Useful- More vs Fewer Nearby Stores
	qui: eststo: xi: reg Useful_rating_bucket tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful i.brand if close_stores>2, vce (cluster shop)
	qui: eststo: xi: reg Useful_rating_bucket tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful i.brand if close_stores<=2, vce (cluster shop)
	**Useful- High vs Low ex-ante Creative Talent
	qui: eststo: xi: reg Useful_rating_bucket tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful i.brand if amquality_rating==1, vce (cluster shop) 
	qui: eststo: xi: reg Useful_rating_bucket tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful i.brand if amquality_rating==0, vce (cluster shop) 
	*Useful- Mainstream vs Divergent
	qui: eststo: xi: reg  Useful_rating_bucket tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful i.brand if market=="mainstream", vce (cluster shop)
	qui: eststo: xi: reg  Useful_rating_bucket tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful i.brand if market=="divergent", vce (cluster shop)
		
	esttab, replace b(4) r2 label ///
    keep(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful) ///
    order(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' gender pre_Useful) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0fc %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("More Nearby Stores" "Fewer Nearby Stores" "High Creativity" "Low Creativity" "Mainstream" "Divergent")
	
	esttab using table6_useful_subsamples.csv, replace b(4) r2 label ///
    keep(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful) ///
    order(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Useful) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("More Nearby Stores" "Fewer Nearby Stores" "High Creativity" "Low Creativity" "Mainstream" "Divergent")
	
	eststo clear
	
	**Attractive- More vs Fewer Nearby Stores
	qui: eststo: xi: reg Attractive_rating_bucket tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Attractive i.brand if close_stores>2, vce (cluster shop)
	qui: eststo: xi: reg Attractive_rating_bucket tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Attractive i.brand if close_stores<=2, vce (cluster shop)
	**Attractive- High vs Low ex-ante Creative Talent
	qui: eststo: xi: reg Attractive_rating_bucket tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Attractive i.brand if amquality_rating==1, vce (cluster shop) 
	qui: eststo: xi: reg Attractive_rating_bucket tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Attractive i.brand if amquality_rating==0, vce (cluster shop) 
	*Attractive- Mainstream vs Divergent
	qui: eststo: xi: reg  Attractive_rating_bucket tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Attractive i.brand if market=="mainstream", vce (cluster shop)
	qui: eststo: xi: reg  Attractive_rating_bucket tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Attractive i.brand if market=="divergent", vce (cluster shop)
	
	esttab, replace b(4) r2 label ///
    keep(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Attractive) ///
    order(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Attractive) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("More Nearby Stores" "Fewer Nearby Stores" "High Creativity" "Low Creativity" "Mainstream" "Divergent")
	
	esttab using table7_attractive_subsamples.csv, replace b(4) r2 label ///
    keep(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Attractive) ///
    order(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' `gender' pre_Attractive) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("More Nearby Stores" "Fewer Nearby Stores" "High Creativity" "Low Creativity" "Mainstream" "Divergent")
	
	eststo clear
		
	*FIXED EFFECTS VERSIONS
	
	eststo clear
	
	**Useful- More vs Fewer Nearby Stores
	qui: eststo: xi: reg Useful_rating_bucket tr post trxpost  i.shop i.brand if close_stores>2, vce (cluster shop)
	qui: eststo: xi: reg Useful_rating_bucket tr post trxpost  i.shop i.brand if close_stores<=2, vce (cluster shop)
	**Useful- High vs Low ex-ante Creative Talent
	qui: eststo: xi: reg Useful_rating_bucket tr post trxpost  i.shop i.brand if amquality_rating==1, vce (cluster shop) 
	qui: eststo: xi: reg Useful_rating_bucket tr post trxpost  i.shop i.brand if amquality_rating==0, vce (cluster shop) 
	*Useful- Mainstream vs Divergent
	qui: eststo: xi: reg  Useful_rating_bucket tr post trxpost  i.shop i.brand if market=="mainstream", vce (cluster shop)
	qui: eststo: xi: reg  Useful_rating_bucket tr post trxpost  i.shop i.brand if market=="divergent", vce (cluster shop)
		
	esttab, replace b(4) r2 label ///
    keep(tr post trxpost ) ///
    order(tr post trxpost n_promoters sqft store_age days close_stores hodist `tenure' gender ) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0fc %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("More Nearby Stores" "Fewer Nearby Stores" "High Creativity" "Low Creativity" "Mainstream" "Divergent")
	
	esttab using table6_FEuseful_subsamples.csv, replace b(4) r2 label ///
    keep(tr post trxpost ) ///
    order(tr post trxpost ) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("More Nearby Stores" "Fewer Nearby Stores" "High Creativity" "Low Creativity" "Mainstream" "Divergent")
	
	eststo clear
	
	**Attractive- More vs Fewer Nearby Stores
	qui: eststo: xi: reg Attractive_rating_bucket tr post trxpost  i.shop i.brand if close_stores>2, vce (cluster shop)
	qui: eststo: xi: reg Attractive_rating_bucket tr post trxpost  i.shop i.brand if close_stores<=2, vce (cluster shop)
	**Attractive- High vs Low ex-ante Creative Talent
	qui: eststo: xi: reg Attractive_rating_bucket tr post trxpost  i.shop i.brand if amquality_rating==1, vce (cluster shop) 
	qui: eststo: xi: reg Attractive_rating_bucket tr post trxpost  i.shop i.brand if amquality_rating==0, vce (cluster shop) 
	*Attractive- Mainstream vs Divergent
	qui: eststo: xi: reg  Attractive_rating_bucket tr post trxpost  i.shop i.brand if market=="mainstream", vce (cluster shop)
	qui: eststo: xi: reg  Attractive_rating_bucket tr post trxpost  i.shop i.brand if market=="divergent", vce (cluster shop)
	
	esttab, replace b(4) r2 label ///
    keep(tr post trxpost ) ///
    order(tr post trxpost ) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("More Nearby Stores" "Fewer Nearby Stores" "High Creativity" "Low Creativity" "Mainstream" "Divergent")
	
	esttab using table7_FEattractive_subsamples.csv, replace b(4) r2 label ///
    keep(tr post trxpost ) ///
    order(tr post trxpost ) nogap star(* 0.10 ** .05 *** 0.01) ///
    stats(N r2, fmt(%9.0f %9.4f) labels("Observations" "Adj. Rsq")) nonotes mtitle("More Nearby Stores" "Fewer Nearby Stores" "High Creativity" "Low Creativity" "Mainstream" "Divergent")
	
	eststo clear


	
	***************************************************************************************
	* end log
		log close

	
	
brainstormingobra/superpowersYou MUST use this before any creative work - creating features, building components, adding functionality, modifying behavior, or planning anything new, in software or out of it (a talk, a business, a renovation).Stars: 323kUpdated Claude CodeCodexVerifiedInstall
brand-guidelinesanthropics/skillsApplies Anthropic's official brand colors and typography to any sort of artifact that may benefit from having Anthropic's look-and-feel. Use it when brand colors or style guidelines, visual formatting, or company design standards apply.Stars: 198kUpdated Claude CodeCodexVerifiedInstall
frontend-designanthropics/skillsGuidance for distinctive, intentional visual design when building new UI or reshaping an existing one. Helps with aesthetic direction, typography, and making choices that don't read as templated defaults.Stars: 198kUpdated Claude CodeCodexVerifiedInstall
mcp-builderanthropics/skillsGuide for creating high-quality MCP (Model Context Protocol) servers that enable LLMs to interact with external services through well-designed tools. Use when building MCP servers to integrate external APIs or services, whether in Python (FastMCP) or Node/TypeScript (MCP SDK).Stars: 198kUpdated Claude CodeCodexVerifiedInstall