Accessing and Querying TargetSmart Data

How can I access TargetSmart data in PAD?

If you already have an existing TargetSmart contract, inform your TargetSmart representative that you’re requesting for Community Tech Alliance to receive authorized recipient access to provide your voter files through PAD.

If you do not have an existing TargetSmart contract, contact TargetSmart to get started. Community Tech Alliance only facilitates providing access to TargetSmart data for users of PAD.

Once TargetSmart has been notified or you’ve finalized your contract, inform CTA of the contract and the details of the files you’ve purchased at help@techallies.org. CTA will provide a dataset in your project called "TargetSmart" containing views with the data you’ve purchased within a week’s time.

If you run into any issues, send us an email at help@techallies.org.

How can I find my TargetSmart data in PAD?

PAD users with TargetSmart access can find their data in the TargetSmart dataset. 

For the following sections in this document, the "voter_base" view (one view of several that you may have in your dataset) will be used for examples. 

[project_name].targetsmart.voter_base -- for all of the monthly installations
[project_name].targetsmart.voter_base_latest -- for only the latest month's file

In this and throughout the following sections, you’ll find example queries for PAD users to try running. These example queries have been generalized and use placeholders such as [project_name]. When querying in PAD, replace the placeholder text with the appropriate indicated information.

Try querying the following to see 10 "voterbase_ids" in your "voter_base" view:

SELECT voterbase_id 
FROM [project_name].targetsmart.voter_base
WHERE ingestion_date = '2022-05-17'
LIMIT 10

Can I segment TargetSmart data by release month?

Yes! CTA receives TargetSmart data for ingestion in monthly installations and appends current files to the older installations by default. CTA records the installation date with a field called "tsmart_release_date" in your "voter_base" view.

If you’re looking for an installation of a particular month, the following sample query will help you filter the data. This example specifically filters for January 2023. 

SELECT *
FROM `[project_name].targetsmart.voter_base`
WHERE ingestion_date = '2023-01-01'

The "ingestion_date" field defaults to the first of the month but includes the entire month's worth of voter file release data from TargetSmart. So, if you'd like to find a specific month, modify the query to reflect your project, year, and month you'd like to access!  

The 'tsmart_release_date' field records the actual date that Targetsmart data was received by CTA. We cluster our monthly on partitions on this field, so it can be used to further drill down queries for a month where there were multiple installs released by Targetsmart.

For example:

SELECT voterbase_id 
FROM [project_name].targetsmart.voter_base
WHERE ingestion_date = '2022-05-01'
AND tsmart_release_date = '2022-05-17'
LIMIT 10

To learn more about clustering, check out the documentation here.

How Can I Optimize My Queries?

Due to the large amount of data contained in the "targetsmart" views, we highly recommend your queries always include one or both of the following columns in the "WHERE" statement of your query:

• ingestion_date
• tsmart_release_date

These are the partition and cluster data fields CTA uses for the storage of the data. 

CTA created a view to automatically partition on the "ingestion_date" and "tsmart_release_date" for the latest installation acquired from TargetSmart. Using this "voter_base_latest" over "voter_base" will increase processing speed and reduce the cost of a query.

[project_name].targetsmart.voter_base_latest 

Otherwise, below is a base query that you can use to retrieve data for a specific month you want to work with using the "voter_base" view.

/* Retrieves voterbase_ids in Alabama for the file released in May 2022 */ 
SELECT voterbase_id
FROM [project_name].targetsmart.voter_base 
WHERE ingestion_date = '2022-07-01' -- replace with the voterfile month desired
AND vb_vf_source_state = 'AL' -- replace with abbreviation of state desired

To retrieve all the current Targetsmart release dates and their related ingested_date, query the following for a helpful reference to optimize your queries:

SELECT DISTINCT tsmart_release_date
, ingestion_date
FROM [project_name].targetsmart.voter_base 
WHERE tsmart_release_date >= '2022-05-01'
ORDER BY 1,2

TargetSmart data can contain upwards of 300 columns. It’s best to always specify the columns you want to retrieve instead of using "select *" as it may compute longer and more expensively if you do so. You can find BigQuery best practices on querying here.

When does CTA update TargetSmart data?

TargetSmart national files are provided to Community Tech Alliance by TargetSmart once a month. There is no consistent date of the month that CTA receives these files from TargetSmart at the moment.

Once a new file is received, the file is ingested by CTA and is made available to partners by being appended to their project’s respective view(s) within 5 business days. 

Can I segment TargetSmart data by state file?

Yes! Your "voter_base" view may contain different states. 

The example below shows voterbase_ids filtered to a particular state using "vb_vf_source_state":

SELECT voterbase_id 
FROM [project_name].targetsmart.voter_base
WHERE vb_vf_source_state = 'AL'
AND tsmart_release_date = '2022-05-17' 

Is there a data dictionary for the TargetSmart dataset?

Metadata found in the schema for the view provides a description of each TargetSmart data field. See the image below of an example "voter_base" view.

Looking for a query or example but couldn’t find it? CTA is always looking to update tips, tricks, and examples to help PAD users maximize their use of their data. Send us suggestions at help@techallies.org.