We Are Here To Help

Follow

Open Payroll Data Schema

Socrata’s Open Payroll allows you to visualize the details of your employees and payroll in an interactive way. The interface presented by Open Payroll is dynamically generated by your data. This document describes the dataset that is required for Open Payroll.

For Open Payroll to work, your dataset must be organized into a flat-file. You can think of this as having all your data on a single tab in Microsoft Excel. The dataset described below has three categories of columns/fields:

  • Required Fields: These fields are required by Open Payroll to function.
  • Recommended Fields: When these fields are provided, the usefulness of Open Payroll will become substantially better for constituents.
  • Optional Fields: These fields are optional as they are useful for users (e.g. analysts) accessing the raw data directly as well and may be used in future enhancements of the Open Payroll product.

Dataset Fields

Recommended Column Name

Type

Value

Notes

year

Required

Number

Used to distinguish entries across multiple years.

transaction_date

Required

Date/Time

The date of the paycheck. For annual data, add years in the format 1/1/[year].

Employee Information

employee_id

Required

Text

This unique identifier is used to associate multiple years of data to the same person, enabling a trend visualization.

name_last

Recommended

Text

Used in searching and show data on a specific individual.

name_first

Recommended

Text

Used in searching and show data on a specific individual.

name_suffix

Recommended

Text

Used in searching and show data on a specific individual.

Position Details

department / division / etc.

Required

Text

Used to aggregate payroll for a given department/division name.

position_title

Required

Text

Used to present a human-readable title for the position.

position_type

Required

Text

Used to aggregate payroll by employment type (ex. Full Time, Part Time, Contract), and to properly represent aggregate statistics (e.g. averages and medians).

service_start_date

Recommended

Date

Calculate length of service since initial employment.

service_end_date

Recommended

Date

Calculate length of service since initial employment.

Actual/YTD Pay Details

pay_total_actual

Required

Number

The actual amount that the position has received in total pay in the paycheck.

pay_base_actual

Recommended

Number

The actual amount that the position has received in base/salary pay in the paycheck.

pay_benefits_actual

Recommended

Number

The actual amount that the position has received in benefits pay in the paycheck.

pay_overtime_actual

Recommended

Number

The actual amount that the position has received in overtime pay in the paycheck.

pay_other_actual

Recommended

Number

The actual amount of other pay (e.g. supplemental pay, bonuses, etc) in the paycheck.

Target Pay Details

projected_total

Optional

Number

The projected total annual earnings for the employee.

Supplementary Information

memorandum_of_understanding

Optional

Text

Show employees connected to a specific MOU.

contract

Optional

Text

Show employees connected to a specific union contract.

bargaining_group

Optional

Text

Show employees connected to a specific bargaining group.

Any other field

Optional

Text

Used for data analysts looking at government specific data.



Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk