This article covers master data management in EFS Panel, including creation, editing, recoding, and organization of master data variables. It also explains how to use the master data codebook and track changes through master data logs.

nformation

Master data contains basic characteristics of panelists as specified by the panel administrator. It is stored independently from projects and is permanently available for evaluation purposes or creating subpanels (“groups”).

• Maximum Limit: Up to 2,800 master data per installation can be created. The actual limit depends on factors like type, size, content, and number of characteristics.
• Text Variables: Use no more than 300 text-type master data and select the smallest possible size range, as they require more storage space.
• Performance Impact: High numbers of master data can slow down panel performance. Export and delete redundant variables to maintain efficiency.
• Consistency and Changes: Only one change process can be executed at a time. If another process is running, EFS will prompt you to repeat the action later.

Master Data Overview

In the People → Master data menu, you can see an overview of all existing master data. The table contains the following information for each master data variable:

• Variable: Variable name.
• Title: Name of the master data. Clicking the title opens the entry form to edit the master data item.
• Variable type:
  • Standard: Manually created or generated by import.
  • Recoded: Generated by recoding.
• Data type: Type of data saved in the master data item.
• Characteristics: Number of characteristics (answer categories) for “Integer” type. Hover over the question mark icon to see a list in a popup window.
• Created: Creation date.
• Last change: Date of the last change.
• Category: Categories to sort and manage master data.
• Survey allocations: Number of survey allocations. Click the number to see a list of allocated surveys.
• SINC allocations (for panels with Social Insight Connect): Number of SINC allocations. Click the number to see a list of allocated language versions.

Exporting Overview Content: Available in Excel and CSV formats.

Display Options

• Sort Order: Default order can be changed by clicking column headers.
• View Function: Display or hide table columns as needed.
• Search Functions: Simple and extended search available. Extended search allows searching by variable name or title, specific data type, or specific master data category.

Actions

Actions can be applied to individual, multiple, or all master data:

• Add to category: Assign master data to a specific category.
• Delete: Initiate the removal process for master data.

To apply an action, tick the “Actions” checkbox for the relevant master data, select the desired action from the drop-down list, and confirm with Execute. Alternatively, use “Mark all master data on this page” or “Mark all master data found” options.

Creating New Master Data

Creating New Master Data

There are several ways to create new master data:

1. Manually: Explained below.
2. By Mass Import: Suitable for creating multiple new entries, especially when a list exists (e.g., in MS Excel).
3. Automatically: Occurs when allocating project variables and master data variables.

Creating Master Data Manually:

Clicking on the Create master data button will open the entry form that allows you to create new master data.

1. Enter Variable Details:
   • Variable name: Must start with m_ or md_ and not exceed 20 characters. If not specified, the system assigns a name automatically.
   • Title: Enter the title. For panels with a registration form or large panels, a nomenclature is recommended for clarity.
2. Select Settings:
   • Master data type and size range.
   • Appropriate master data category.
3. Confirm:
   • Click on Create to save the new master data.

Creating Answer Categories for “Integer” Type Master Data

For “Integer” type master data, follow these additional steps to create answer categories:

1. Create Answer Categories:
   • After saving, the section for creating answer categories appears.
   • Enter the first answer category (e.g., code = 1, answer category = “yes”) in the “New” line and click Save.
   • Enter the second answer category (e.g., code = 2, answer category = “no”) and click Save.
2. Check and Modify:
   • Check the result in the Master data codebook. The variable (e.g., titled “Smoker”) will display with the different answer categories and codes.
   • To modify or expand answer categories later, open the edit dialog of the master data item, then click Edit answer categories to access modifiable input fields.

Tips

• For a large number of answer categories or repeated use across multiple questions, use the Mass-import answer categories function.

Sorting Master Data by Categories

Managing Master Data Categories

• Open Manage Categories Dialog: Click on the Manage categories button in the master data overview.
• Overview Table: Lists all existing categories with their IDs, titles, descriptions, and number of assigned master data. The “Default” category cannot be deleted, and all master data are assigned to it by default.
• Create New Categories: Click on the Create category button to open the dialog for creating new categories. Enter the category title and description.
• Edit Existing Categories: Use the icons in the “Actions” column to edit or delete categories.

Assigning Master Data to Categories

• Via Overview: Use the “Assign category” action in the master data overview to assign several master data at once.
• Via Editing Dialog: Change the assignment of a specific master data via its editing dialog.

Quick Changes to Master Data Labels

Purpose: Labeling helps internal orientation and appears in the codebook and export functions.

Quick Change Function:

• Open Quick Change Labels Dialog: Click the Quick change labels button in the People → Master data menu.
• Entry Form: Shows all created master data. Each line contains the master data variable (unchangeable) and the title serving as the label. Change the labels as desired.
• Submit Changes: Click the Submit button to save changes.

Information: Variables cannot be deleted or added. Removed lines will be ignored.

Editing Labels Externally in MS Excel:

• Choose Separator: Click on in the “Choose a separator” row to separate columns by a tab character.
• Copy Content to Excel: Highlight and copy the content (Ctrl-C) to MS Excel.
• Edit Labels in Excel: Edit the labels in Excel, then copy the changed data back to the window.
• Submit Changes: Confirm by clicking on Submit.

Mass Import of Master Data

You can create master data and related answer categories via mass import or by exporting, editing, and reimporting existing master data. This is useful if you have a list in Excel.

Deleting data via mass import is not allowed due to the risk of accidental data loss. To delete master data or answer categories, use the user interface.

Formatting import data

1. Formatting master data

The following format should be used:

item;master data variable;title;data type;order;category ID

Example:

item;md_001;Birthday;int;2;5;3

This sample master data item is used to save the date of birth. The variable is “md_001”, and it is labeled “Birthday”. The data type is “integer”, i.e. a whole number. The field has 2 digits. The order of the master data is such that date of birth is in position 5 and belongs to the category with the ID number 3.

Field	Meaning	Example
Information to be created	Indicates whether the element is a master data variable (item) or an answer category (r_cat).	item -> master data item
Master data variable	Indicates the master data item:The format for manually created master data is “md_”.The format for master data created by mass import is “m_number”.	m_001
Title	The label.	Birthday
Data type	Data type and size in the database.	int
Order	The position in the order of master data.	5
Category ID	The master data category.	3

2. Formatting answer categories

The following format should be used:

r_cat;master data variable;code;title;

Example:

r_cat;md_001;Birthday;0;Please select

This sample answer category belongs to the variable “md_001”, in which the birthday is saved. The answer category has the code “0” and is labeled “Please select”, i.e. it is the defaulted field in a drop-down list that prompts the panelist to set the correct date.

Field	Meaning	Example
r_cat	Indicates whether the element is a master data variable (item) or an answer category (r_cat).	rcat -> answer category
Master data variable	Indicates to which master data item an answer category belongs.	md_001
Code	The code of the answer category.You can use negative numbers, however these cannot contain blank spaces. E.g., “-77” can be imported “- 77 not”.	0
Title	The label.	Birthday

Performing the import

1. Open the People → Master data menu.
2. This will open the overview of the master data. Instead of manually creating new master data, click on the Mass-import master data button.
3. Select the desired separator.
4. Usually, default master data and answer categories will already be available. Add the new master data and answer categories in the correct format. If you have created an Excel table, transfer the content, but not the column labels, using copy & paste.
5. Confirm by clicking on Import.
6. At the beginning of the import operation, entries are checked for the following errors:
   • Correct naming according to the “m_number” convention.
   • Matching type and size information.
   • No existing master data variable with the same name.
   • Each row must have exactly five columns and no empty row at the end.
7. If the data is error-free, the master data will be created. Otherwise, an error message will result.

Editing and exporting master data by means of the mass import function

Master data and answer categories that have already been created will be output in the input field and can then be edited. External editing is also possible:

1. Copy the content of the input field into Excel.
2. Should Excel copy the data into a cell instead of formatting it correctly, select Data → Text to Columns. Choose the data type “Delimited”, and confirm by clicking on Next.
3. Choose the separator, and confirm by clicking on Next again.
4. Check the display, and then confirm by clicking on Finish.

Changing the Order of the Master Data

You can specify the order in which master data should normally be displayed in the admin area. To do so, click on the Change order button in the People → Master data menu.

• You can define the order yourself. To do so, enter the desired numbering in the “Order” field, and confirm by clicking on Save (see the following figure).
• You can sort the master data in a panel or master data survey by means of the associated variables. To do so, click on the Sort by project questionnaire order button. Select the desired project and then confirm by clicking on Submit.

Deleting Master Data

You can remove master data by activating the corresponding “Delete” checkbox in the master data overview and confirming with Save. Please note that already recorded information and information stored in these master data are irrevocably deleted and are no longer available for future searches in the panel, group forming, or evaluations.

This can also damage functions that access master data such as recodings.

• Therefore, a list will be output when deleting master data, in which you can see whether the master data are still being used. This covers normal recordings and lookup recordings, filters in surveys, grouping filters, update rules, and panel settings. In the column “Usage” you can see which function has access to the master data. The traffic-light icon in the column “Status” shows, whether another conjunction exists (red traffic light icon).
• The check does not cover formal recodings.

Tracking Changes of Master Data

Master data administration includes a logfile which keeps track of changes made to the master data variables. This allows you, for example, to easily identify and track any changes made by other users.

To view the change history of your master data, open the People → Master data → Master data logs menu. The overview table lists all changes along with their respective dates, the variables concerned, the responsible user, and the types of operation performed.

Via the Detail view icon, you can access the details of a particular change.

Recoding Master Data

You can recode existing master data to generate more compact variables. For example, recode “Year of birth” into “Age group” with characteristics like “Teens,” “Adults,” or “Seniors.” These new variables can be used for creating groups by applying master data filters.

Exporting Master Data:

• Recoded variables are included in the data record during export like any other master data.

Methods of Recoding:

1. Rearranging Characteristics: Modify the characteristics of a master data variable.
2. Calculated Recoding: Based on values from one or several master data variables.
3. Table-Based Recoding: Uses a lookup table with all possible values and their corresponding destination values.

Important Notes:

• Many or complex recodings can negatively impact panel performance.
• Automated recoding with every master data change is server-intensive. Limit to 20 recodings per installation and use grouped recodings triggered via an update rule during low traffic periods.
• Regularly check if existing recodings are necessary.

Rearranging the Characteristics of Master Data

Recoding is often used to rearrange the characteristics of a master data item. Follow these steps:

1. Defining the Mapping
   • Determine the characteristics of your new recoding variable.
   • Define which characteristics of the source variable should be allocated to each characteristic of the recoding variable.
2. Creating a Recodes Definition
   • Go to People → Master data → Recoding of master data.
   • Click on the Create recoding variable button.
   • Enter the title for the recodes definition.
   • Select the source master data variable.
   • Choose a master data category for the new recoding variable.
   • Decide if the recoding should be triggered automatically. For example, allocate the recodes definition to a group and trigger it weekly.
   • Optionally, assign the recodes definition to a recoding group and specify the recoding order.
   • Click Create to confirm.
   • A new master data variable is created for storing the recoding results.
3. Defining Target Categories
   • Click on the title of the new recodes definition (e.g., “Age group”).
   • On the Recodes definition tab, change the title, activate automatic recoding, and modify the recoding order.
   • Go to the 1. Define target categories tab.
   • Enter the target categories (e.g., “Teens”, “Adults”, “Senior citizens”), including their sort numbers and codes. Confirm each entry by clicking Save.
4. Specifying the Target Categories
   • Go to the 2. Specify assignments tab.
   • All characteristics of the source variable will be displayed. Tick the checkboxes for characteristics that fit the target categories.
   • Example: Select years of birth from 1910 to 1944 for “Senior citizens,” 1945 to 1985 for “Adults,” and 1986 to 2004 for “Teens.”
   • Click Save to confirm.
5. Executing the Recoding
   • Go to the 3. Run recoding process tab.
   • The result display will show how many records were affected.

Calculating Recodings with Formulas

In many cases, you can use a mathematic formula to calculate the recoding, using the values of one or several master data as starting point for the calculation.

Recodings are frequently used to rearrange the characteristics of a master data item.

This requires the following steps:

• Defining the formula.
• Creating a new recoding.

1. Defining the formula

Define the formula which describes your recoding. The following rules apply:

• Use wildcards #md_xxx# resp. #m_xxx# to insert master data.
• The operators + – / * and ( and ) can be used.
• It is possible to perform date calculations. For details, please see the explanations for grouping filters in Chapter “Creating and Editing Groups”.

Note: only the functions named below can be used and are supported in future versions of EFS. No other functions can be used.

Function		Explanation	Example
case	CASEWHEN expr1 THEN value1ELSE value2END	Returns the THEN value for which the condition applies.	CASEWHEN md_country = ‘Germany’ THEN 1WHEN md_country = ‘Austria’ THEN 2ELSE 99END
ceil	CEIL(x)	Returns the smallest integer value not less than X.	CEIL(1.23); → 2
concat	CONCAT(str1,str2,…)	Returns the string that results from concatenating the arguments.	CONCAT(‘My’, ‘S’, ‘QL’);→ ‘MySQL’
date_format	DATE_FORMAT(date,format)	Formats the date value according to the format string.	DATE_FORMAT (‘1997-10-04 22:23:00’, ‘%W %M %Y’);→ ‘Saturday October 1997’
datediff	DATEDIFF(expr,expr2)	Returns expr1 – expr2 expressed as a value in days from one date to the other.	DATEDIFF(‘1997-12-31 23:59:59’, ‘1997-12-30’);→ 1
day	DAY(date)	Returns the day of the month for date, in the range 1 to 31.	DAYOFMONTH(‘1998-02-03’);→ 3
floor	FLOOR(X)	Returns the largest integer value not greater than X.	FLOOR(1.23);→ 1
from_unixtime	FROM_UNIXTIME (unix_timestamp)FROM_UNIXTIME (unix_timestamp, format)	Returns a representation of the unix_timestamp argument as a value in ‘YYYY-MM-DD HH:MM:SS’ or ‘YYYYMMDDHHMMSS’ format.If format is given, the result is formatted according to the format string, which is used the same way as listed in the entry for the DATE_FORMAT() function.	FROM_UNIXTIME(875996580);→ ‘1997-10-04 22:23:00’
greatest	GREATEST(value1, value2,…)	With two or more arguments, returns the largest (maximumvalued) argument.	GREATEST(2,0);→ 2
if	IF(expr1,expr2, expr3)	If expr1 TRUE (expr1<> 0 and expr1 <> NULL), than IF() returns expr2; otherwise it returns expr3.	IF(1>2,2,3);→ 3
instr	INSTR(str,substr)	Returns the position of the first occurrence of substring substr in string str.	INSTR(‘foobarbar’, ‘bar’);→ 4
least	LEAST(value1,value 2,…)	With two or more arguments, returns the smallest (minimumvalued) argument.	LEAST(2,0);→ 0
left	LEFT(str,len)	Returns the leftmost len characters from the string str.	LEFT(‘foobarbar’, 5);→ ‘fooba’
length	LENGTH(str)	Returns the length of the string str, measured in number of characters.	LENGTH(‘text’);→ 4
lower	LOWER(str)	Returns the string str with all characters changed to lowercase according to the current character set mapping.	LOWER(‘QUADRATICALLY’);→ ‘quadratically’
month	MONTH(date)	Returns the month for date, in the range 1 to 12.	MONTH(‘1998-02-03’);→ 2
now	NOW()	Returns the current date and time as a value in ‘YYYY-MM-DD HH:MM:SS’ or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.	NOW();→ ‘1997-12-15 23:50:26’
right	RIGHT(str,len)	Returns the rightmost len characters from the string str.	RIGHT(‘foobarbar’, 4);→ ‘rbar’
to_days	TO_DAYS(date)	For a given date, it returns the number of days passed since year 0 until this date.	TO_DAYS(950501);→ 728779
trim	TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM(remstr FROM] str)	Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.	TRIM(‘ bar ‘);→ ‘bar’
upper	UPPER(str)	Returns the string str with all characters changed to uppercase according to the current character set mapping.	UPPER(‘Hej’);→  ‘HEJ’
unix_timestamp	UNIX_TIMESTAMP()UNIX_TIMESTAMP(date)	If called with no argument, returns a Unix timestamp(seconds since ‘1970-01-01 00:00:00’ UTC) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since ‘1970-01-01 00:00:00’ UTC.	UNIX_TIMESTAMP(‘1997-10-04 22:23:00’);→ 875996580
year	YEAR(date)	Returns the year for date, in the range 1000 to 9999, or 0 for the “zero” date.	YEAR(’98-02-03′);→ 1998

2. Creating a recoding

• In the People → Master data → Recoding of master data menu, click on the Create recoding variable (formula) button.
• Enter the title of the recodes definition.
• Enter the formula.
• Specify whether automatic recoding should be applied. In the example shown above, the panelists’ age is calculated. In this case, automatic triggering does not make sense: instead, you could e.g. allocate the recoding to a recoding group and trigger it only once a week.
• Optionally, you can assign the recodes definition to a recoding group.
• Optionally, you can specify the recoding order.
• Confirm by clicking on Submit.
• A new master data variable is created: it will be used to store the results of the recoding process. The newly created recodes definition appears in the overview table.
• To execute the recoding process directly, follow the instructions in this chapter. Alternatively, you can either execute the recoding process in self-defined intervals, or use automated recoding.

Using Allocation Tables to Define Recodings

A third recoding method uses allocation tables (lookups) containing source and destination values, like recoding zip codes by state.

1. Creating the Allocation List (Lookup)
   • Lookups are lists with source and destination codes for recoding.
   • Create and edit lookups in People → Master data → Recoding of master data → Lookups.
   • Format: Two columns; left for source values (unique), right for destination codes (not unique). Use numeric values ≥ 0 or strings (e.g., AU, AT). Save in CSV or XLS format.
   • Creating a Lookup:
     • Go to People → Master data → Recoding of master data → Lookups.
     • Click on Create lookup.
     • Enter data manually or import CSV/XLS data.
2. Using Lookups for Recoding
   • Go to Master data recodes menu.
   • Click Create recoding variable (lookup).
   • Enter the title, select the lookup table, and source variable.
   • Specify if recoding should be automatic.
   • Optionally, assign to a recoding group and specify the order.
   • Click Create. A new master data variable is created for storing recoding results.
   • Executing Recoding:
     • Follow the instructions in this chapter to execute the recoding process now, at self-defined intervals, or automatically.

Specifying the Order of Recoding Processes

To specify the order when recodings depend on other recodings:

• Click on the Change order button.
• Specify the sequence in the “Recoding order” field.
• Confirm with Save.

Executing Recodings Manually

You can trigger recodings manually, individually or as a block:

• Individual Recoding: Locate it in the overview table and click the Run recoding procedure icon.
• All Recodings: Check the overview table to ensure the recoding order is correct. Click Execute all recodings to trigger the process.

Configuring a Regular, Effective Execution of Recodings

Regular synchronization of data is often crucial, while automatic recoding with every change isn’t necessary. Manual recoding can be ineffective and error-prone. By combining recoding groups with update rules, you can optimize the timing and scope of recoding.

Creating a Recoding Group:

1. Go to People → Master data → Recoding of master data → Recoding groups.
2. Click Add group.
3. Enter a name and description, then confirm with Save.

Assign Recoding to a Group:

1. Open an existing recoding.
2. Select the appropriate group in the “Recoding group” drop-down list.
3. Click Save to confirm.

Executing Recodings for a Group Manually:

1. Go to People → Master data → Recoding of master data → Recoding groups.
2. Click the Execute recoding in group icon for the desired group. All recodings assigned to this group will be executed.

Configuring Automated Execution for a Recoding Group:

1. Go to Groups → Update rules.
2. When creating or editing a rule, select the action “Execute recodings from recoding group” and save.
3. Select the desired recoding group in the “Value” field.
4. Set the schedule for automatic execution, then save. All recodings of the selected group will follow this schedule.

Configuring Automated Execution of Recodings

EFS Panel can automatically execute recodings in a preset order under specific circumstances, such as:

• Candidate self-registration via the registration form.
• Panelist data edits using the re-registration form.
• Imported or updated panelist data.
• When a panelist’s master data is saved in the admin area.

Activating Automatic Recoding:

1. Activate the “Auto recode” function when creating a new recoding or by editing an existing recoding (click the Edit icon in the recoding table).

Important:

• When using “Auto recode,” be cautious when deleting old recodings or adding new ones to avoid disrupting the recoding order.

Master Data Codebook

The master data codebook provides an overview of the panel’s master data and their encodings. It is used, for example, for the definition of filter conditions or for evaluation. You will find the codebook in the People → Master data → Master data codebook. It contains the variable name and label, data type, variable type, answer code and label (if available) as well as the master data category for each master data item.

Above the list, you will find various features which allow you to restrict, to reorder or to extend the list:

• Restrict to category: This option allows you to restrict the list to a single master data category.
• Sort by column: With this option you can order the list by category label, variable name, variable label, or variable type.
• Show recoding information: Master data of “recoded” type are displayed along with recoding details such as the recoding formula.
• Hide answer categories: The columns “Answer code” and “Answer label” are hidden, and the list of master data is simplified.

Via the Export button you can download the codebook in its current configuration.

Master Data Logs

Master data administration includes a logfile that keeps track of changes made to the master data variables. This allows you, for example, to easily identify and track any changes made by other users. To view the change history of your master data, open the People → Master data → Master data logs menu. The overview table lists all changes along with their respective dates, the variables concerned, the responsible user, and the types of operation performed.

Via the Detail view icon, you can access the details of a particular change.

FAQ

What is the maximum number of master data that can be created?

Up to 2,800 master data per installation can be created, but the actual maximum number may be lower depending on various factors such as type, size, and content of the master data.

How can I improve the performance of my panel with many master data variables?

To improve performance, regularly review and delete redundant variables, use recoding groups for efficient updates, and limit the use of text variables to no more than 300.

Can I automatically execute recodings?

Yes, you can configure automated execution of recodings by activating the “Auto recode” function when creating or editing a recoding.

Tags: Master Data, EFS Panel, Data Management, Recoding, Codebook, Data Logs