The Urban Transport Institute

Given the complexity and the number of overlapping fieldwork activities taking place in any one week of a TUTI continuous survey such as VISTA09 (after the initial ramp-up period), it was essential to automate as many of the procedures as possible for the main survey. To this end, an Admin Program (HotSpot – Household Travel Survey Program for Operational Techniques) was written (in Excel using VBA macros) to facilitate the production of fieldwork materials and the collection of fieldwork data. A screenshot of the main Control Panel worksheet of the HotSpot program, as used in VISTA09, is shown below.

It can be seen that the Control Panel is laid out in a diary format, showing which tasks need to be performed each day of the week, and the order in which they should be performed. The current date is shown under today's name, while the week of the survey (in terms of the Travel Days) is shown at top-right. Next to each task, where appropriate, is the week of the survey pertaining to that task. Some tasks appear more than once (e.g. Delivery Stats) when that task is performed more than once per week. After each task has been performed, a tick-box can be selected to indicate completion of that task for this week. At the start of each week, the “Clear Ticks” button can be clicked to set the program up for the new week.

An overview of the main functions is provided below, by day of week.

Monday

·      Log in SPD Data – this routine imports the SPD Input spreadsheets into which the data from the SPD Control
       Sheets have been entered from the deliveries over the weekend.

·      Delivery Stats – this routine gathers the information from the SPD Input Sheets, and calculates a range of
       statistics related to the SPD phase of the field work, by region and by week of the survey. The main statistic
       being checked at this stage is the “% Personal Delivery”.

·      View DPU Sheet – this routine gathers the information from the SPD Input Sheets, and shows who performed
       the task, the time they spent on the task and the kilometres travelled in the DPU Sheet (our name for a
       productivity sheet).

·      MC Phone List – this routine produces the lists of phone numbers to be used each night to make the
       Motivational Calls (MC), with space to record the outcome of the calls. These lists must be produced by 4pm
       each Monday to allow the Monday night calls to be made.

Tuesday

·      Log in SPP Data – this routine imports the SPP Input spreadsheets into which the data from the SPP Control
       Sheets have been entered each Monday.

·      Pickup Stats – this routine gathers the information from the SPP Input Sheets, and calculates a range of
       statistics related to the SPP phase of the field work, by region and by week of the survey. The main statistics
       being checked at this stage is the “% Personal Collection” and the “% Collected from the Door”.

·      View DPU Sheet – this routine gathers the information from the SPP Input Sheets, and shows who performed
       the task, the time they spent on the task and the kilometres travelled.

·      Speedit Export – given that data entry for the collections made on the weekend will start on Wednesday,
       this routine exports data from HotSpot about each sampled household, for import into the Speedit data
       entry program, to serve as a basis for the data entry process.

Wednesday

·      Log in PCL Data – this routine imports the PCL Input spreadsheets into which the data from the PCL Control
       Sheets have been entered each Tueday. It also imports a file of household coordinates that may have been
       modified during the PCL map-making process.

·      Pre-Contact Stats – this routine gathers the information from the PCL Input Sheets, and calculates a range
       of statistics related to the PCL phase of the field work, by region and by week of the survey.

·      View DPU Sheet – this routine gathers the information from the PCL Input Sheets, and shows who performed
       the task, the time they spent on the task and the kilometres travelled.

·      KPI Stats – this routine combines data from the various field activities and calculates a series of Key
       Performance Indices (KPIs) for the PCL, SPD and SPP activities. These stats are provided to the Field
       Supervisors, so that they can review comparative performance by field staff, and bring any problems to
       the attention of the field staff before they go out into the field again.

Thursday

·      Log in Postals – this routine records the ID numbers on Survey Packs returned in the mail, and codes them
       as received in the database. This includes Postal Returns containing completed questionnaires and Postal
       returns containing blank questionnaires. This prevents these households from being included in any reminder
       activities.

·      Log 1800 Calls – this routine enables the details of all 1800 calls to be logged into the database, to prevent
       these households from being included in any reminder activities and to note their status on the SPD and SPP
       Control Sheets to be prepared later.

·      RC Phone List – this routine produces the lists of phone numbers to be used each Thursday night to make
       the Reminder Calls (RC), with space to record the outcome of the calls.

·      Office Hours Import – this routine imports the latest copy of the Office Hours spreadsheet supplied weekly
       from the Survey Office. Separate OH Sheets are imported for I-view staff (the market research company
       employed by TUTI for fieldwork operations) and TUTI staff (involved in Data Entry operations).

·      View DPU Sheet – the DPU Sheet is viewed one last time to check for errors in staff IDs, hours worked and
       kilometres travelled, before the production of the weekly payrolls.

·      Expenses by Week – this routine enables weekly payrolls to be calculated for all I-view and TUTI and staff.

·      Expenses by Staff – this routine enables a cumulative paysheet to be calculated for each staff member.

·      Speedit Extractions – each week, the Data Entry office sends a list of household IDs for those households for
       whom data entry is complete, and whose data has been transferred from the working files to the archived files.

·      Response Stats – this routine combines data from the Pickup Stats worksheet with information about postal
       returns, sample loss and data entry room losses, to calculate response rates by week and region.

·      Progress Report – this routine combines data from the various Admin Stats worksheets to produce an overall
       Progress Report by week and region. This provides the basis for weekly reports to the client and other parties.
       It is also provided to the field office supervisors, to provide feedback to field staff about overall survey
       performance.

·      SPD Mailing Labels – this routine produces the mailing labels to be used on the Survey Packs to be delivered
       to households

·      SPD Control Sheets – this routine produces the SPD Control Sheets that are used by fieldwork staff in the field.
       It also produces SPD Input spreadsheets that are later used for the entry of data recorded on the Control Sheets

·      SPD Map Data – this routine produces a list of household addresses and geocodes which are used to produce
       the maps to be used by fieldwork staff in the field to locate the sampled households. The actual maps are
       produced outside of HotSpot (using a program written in MapBasic for MapInfo), but a checkbox is provided
       to note when the SPD Maps have been produced and sent to the field offices.

Friday

·      Log in RC Outcomes – this routine imports the RC Input spreadsheets into which the data from the RC Phone
       List have been entered on Thursday night.

·      RM Labels – this routine produces the mailing labels to be used on the Reminder Letter envelopes, which are
       prepared and mailed each Friday.

·      PCL Mailing Labels – this routine produces the mailing labels to be used on the Pre-Contact Letter (PCL)
       envelopes.

·      PCL Control Sheets – this routine produces the PCL Control Sheets that are used by fieldwork staff in the field.
       It also produces PCL Input spreadsheets that are later used for the entry of data recorded on the Control Sheets.

·      PCL Map Data – this routine produces a list of household addresses and geocodes which are used to produce
       the maps to be used by fieldwork staff in the field to locate the sampled households.

·      BACKUP – this routine makes a backup copy of the volatile data currently in the HotSpot databases (the data
       is also retained in the HotSpot program itself) and labels it with the date of the backup.

Saturday

·      Log in MC Outcomes – this routine imports the MC Input spreadsheets into which the data from the MC
       Phone Lists have been entered during the week.

·      Delivery Stats – the Delivery Stats sheet is checked again, to note the percentage of households receiving
       Motivational Calls (to ensure no blank MC Input Sheets have been accidentally imported).

·      SPP Control Sheets – this routine produces the SPP Control Sheets that are used by fieldwork staff in the field.
       It also produces SPP Input spreadsheets that are later used for the entry of data recorded on the Control Sheets.
       No new SPP maps are produced; the maps produced for the SPD phase are re-used because the household
       numbers and locations will not have changed.

Sunday

·      There are no specific tasks scheduled for HotSpot on Sundays.

OTHERS (these are tasks not scheduled for any particular day of the week)

·      SETUP – this routine enables HotSpot to be customised for any particular survey of the same type as VISTA09.
       It records file names and pathnames, colour schemes, length of survey, number of regions, starting dates of
       key activities, number of households on PCL and SPD lists, and payrates for different types of field staff.

·      View Sample – this routine allows the user to quickly view the full sample (56 addresses per region per week)
       and the final sample (42 addresses per region per week), simply by entering the region and week number.

·      Field Staff List – this routine enables details of all field staff to be entered, to be used in various other routines.

·      PCL Expenses – this routine gathers the information about field staff hours and kilometres travelled from the
       PCL Input Sheets, and calculates the costs of running the PCL phase of the field work, by region and by week
       of the survey

·      SPD Expenses – this routine gathers the information about field staff hours and kilometres travelled from the
       SPD Input Sheets, and calculates the costs of running the SPD phase of the field work, by region and by week
       of the survey

·      SPP Expenses – this routine gathers the information about field staff hours and kilometres travelled from the
       SPP Input Sheets, and calculates the costs of running the SPP phase of the field work, by region and by week
       of the survey

·      Other Expenses – this routine gathers data from the imported Office Hours spreadsheets, and calculates the
       costs of running the Survey Offices and other expenses not recorded on the PCL, SPD and SPP input sheets

·      Expense Report – this routine combines data from the various Financial Function worksheets to produce an
       overall Expense Report by task, week and region. This report is used to track project expenses, and to compare
       against budgeted expenses.

·      RESTORE – this routine restores a copy of a backup file previously created by the HotSpot program. The specific
       file to be restored can be specified by the user by selecting the date of the Backup.

·      SAVE – this button is just a quick way of saving the current version of HotSpot, including any changes that have
       been made in the underlying databases.

·      Clear Ticks – at the end of each week, all of the tasks should have been ticked to indicate that they have
       been completed. To start a new week, this button can be clicked to remove the ticks from all the boxes on
       the screen.

  [Home]                                                                                                                                                          [Top]