BallotReady to Redshift/BigQuery Data Formats
Bluelink supports 2 data formats for syncing BallotReady data to a data warehouse like Redshift or BigQuery.
Format 1 is the raw data as it comes from BallotReady. There is one row for each BallotReady event. A BallotReady event is triggered by a user’s action, such as clicking a button or submitting a form on the BallotReady website.
Format 2 is where data is pre-processed into three tables: People, IDs, and Events.
Note: Field names will be snake_case in Redshift and camelCase in BigQuery to follow common conventions for each database.
Format 1: Raw event data
| Field | Description | Sample Value |
| ballotId | Unique ID assigned by BallotReady for a Ballot lookup | 6870051 |
| sessionId | Unique ID assigned by BallotReady for the user session | f2f47548-b5c1-4f96-98d9-779cb5bd24a5 |
| properties | This field may include IDs from Targetsmart or properties that further define the event | {"voter_id": “12323606”, "voterbase_id": "AA-19786660", "accepts_digital_signature":true} |
| createdAtUtc | Timestamp of the event | 2020-08-17T15:09:43.513439 |
| url | The event took place on this page | https:// www.ballotready.org/ec/pledge |
| browserName | Browser name | chrome |
| browserOs | Browser information | Mac OS |
| browserVersion | Browser information | 84.0.4147 |
| initialUrl | First BallotReady URL the user visited | https://app.civicengine.com?utm_source=facebook |
| initialReferrer | URL the user was referred from, if any | facebook.com |
| utmSource | UTM data | |
| utmMedium | UTM data | cpc |
| utmCampaign | UTM data | nationalregistrationday |
| utmTerm | UTM data | vote+registration |
| utmContent | UTM data | flag |
| screenWidth | Width in px | 545 |
| screenHeight | Height in px | 768 |
| viewportWidth | Width in px | 2049 |
| viewportHeight | Height in px | 985 |
Format 2: Three tables
The second format is where we pre-process the data into 3 tables: People, IDs, and Events.
Person data
| Field | Description | Sample value |
| personId | Unique ID for the person assigned by Bluelink. | 773985ac-a492-4a4e-8147-9a92a89adf6f |
| ballotId | Unique ID assigned by BallotReady | 6870051 |
| firstName | First name | Anna |
| lastName | Last name | Smith |
| addressLine1 | address | 411 N 114th St |
| addressLine2 | address | Omaha, NE 68154 |
| city | city | Omaha |
| state | state | NE |
| zip | zipcode | 68154 |
| phone | phone number | 14105678989 |
| anna@gmail.com |
Event data
| Field | Description | Sample Value |
| id | Unique event ID assigned by Bluelink | 97c451ab-cc7f-492d-b74d-f09bae90304a |
| personId | Which person the event was for. Maps to personId field in the person table | 773985ac-a492-4a4e-8147-9a92a89adf6f |
| ballotId | Unique ID assigned by BallotReady for a Ballot lookup | 6870051 |
| sessionId | Unique ID assigned by BallotReady for the user session | f2f47548-b5c1-4f96-98d9-779cb5bd24a5 |
| properties | This field may include IDs from Targetsmart or properties that further define the event | {"voter_id": “12323606”, "voterbase_id": "AA-19786660", "accepts_digital_signature":true} |
| createdAtUtc | Timestamp of the event | 2020-08-17T15:09:43.513439 |
| url | The event took place on this page | https:// www.ballotready.org/ec/pledge |
| browserName | Browser name | chrome |
| browserOs | Browser information | Mac OS |
| browserVersion | Browser information | 84.0.4147 |
| initialUrl | First BallotReady URL the user visited | https://app.civicengine.com?utm_source=facebook |
| initialReferrer | URL the user was referred from, if any | facebook.com |
| utmSource | UTM data | |
| utmMedium | UTM data | cpc |
| utmCampaign | UTM data | nationalregistrationday |
| utmTerm | UTM data | vote+registration |
| utmContent | UTM data | flag |
| screenWidth | Width in px | 545 |
| screenHeight | Height in px | 768 |
| viewportWidth | Width in px | 2049 |
| viewportHeight | Height in px | 985 |
External IDs
| Field | Description | Sample value |
| personId | Unique ID for the person assigned by Bluelink. Maps to personId field in the Person table | 773985ac-a492-4a4e-8147-9a92a89adf6f |
| source | The source of the ID | ballotready:session_id |
| identifier | The ID | 1238929 |