Data assets

Data assets are a collection of CSV files and SQL templates that add support for common data sets and use cases, such as customer attributes, email and SMS opt-in status, predicted gender, calendars, and lookups for countries, states, provinces. Most data assets are available to retail and non-retail use cases.

Customer attributes

The Customer Attributes table contains a series of columns that identify attributes about individuals, such as if that individual can be contacted, if a marketable email address, physical address, or phone number is available, if they are an employee, reseller, or if the individual represents a test account, along with identifying each individuals’s revenue relationship with a brand.

The Customer Attributes table has a small number of specific requirements. A series of recommended and optional configurations are also available. You may enable any of the optional configurations for your tenant. In some cases, an optional configuration has its own set of specific requirements.

Email engagement

Email engagement captures interactions with the emails that your brand has sent to your customers. There are three sets of data that you can make available in your tenant:

Engagement attributes

The Email Engagement Attributes table contains many of the same fields as the Email Engagement Summary table, except for the addition of the Amperity ID field. Whereas the Email Engagement Summary table is unique by email and brand (if available), the Email Engagement Attributes table is unique by the Amperity ID and email for each brand combination.

Note

In the Email Engagement Attributes table, each Amperity ID should only have one email address, per brand.

Tip

The Email Engagement Attributes table pulls the email engagement data, for each Amperity ID, from the Email Engagement Summary table using the email associated with it in the Merged Customers table.

Apply email-attributes/ semantic tags to data sources, and then add the Email Engagement Attributes table to your customer 360 database using a built-in SQL template. Some customization of this SQL template is necessary to align the behavior of this table to the data that exists in your tenant.

Engagement summary

The Email Engagement Summary table contains a summary of email event statistics, such as counts for opens and clicks, the first open, and the most recent click, unique by email address.

The Email Engagement Summary table is generated as Stitch output after applying email summary semantic tags (when available) alongside email events semantic tags to data sources that contain email engagement data, such as opens, clicks, opt-in and opt-out preferences, or conversions.

Unified email events

The Unified Email Events table contains individual email event information, such as sends, opens, clicks, opt-in and opt-out preferences, bounces, and conversions. This table is added as Stitch output when email event semantic tags are applied to data sources.

The Unified Email Events table is generated as Stitch output after applying email events semantic tags to data sources that contain email engagement data, such as opens, clicks, opt-in and opt-out preferences, or conversions.

Fiscal calendars

A fiscal calendar is a yearly accounting period that aligns the weeks and months in a calendar year with holidays and a brand’s marketing goals to align the business for an entire calendar year. A common fiscal calendar used by brands is the 4-5-4 fiscal calendar.

You may use one of the following data assets to define a fiscal calendar or you may provide to Amperity a file that contains your brand’s complete fiscal calendar:

NRF calendar

The NRF calendars data asset contains a 4-5-4 fiscal calendar that spans from 2016-2024 and divides each year into months based on a 4 weeks - 5 weeks - 4 weeks format. Each month has the same number of Saturdays and Sundays, which ensures like days can be compared.

A 4-5-4 calendar divides years into months using a 4 weeks - 5 weeks - 4 weeks pattern. Each week starts on a Sunday and ends on a Saturday. Each quarter has the same number of days. A 4-5-4 calendar can be useful for comparing like days for sales reporting purposes.

To add the NRF calendar data asset

Add the NRF calendar data asset to your tenant by pulling the file that is available from Amperity Data Assets, which is the name of an Amazon S3 bucket that can be made available to your tenant. Follow the steps for adding a data source and feed. Click Browse and select the “NRF_Calendar.csv” file from the Amperity Data Assets Amazon S3 bucket.

Note

If Amperity data assets credentials are not available on your tenant, make a request to Amperity Support to enable Amperity data assets for your tenant.

Add a passthrough table to your customer 360 database named NRF Calendar, and then run your customer 360 database to build the NRF Calendar table.

Extended fiscal calendar

Important

Ask your Amperity representative for help building a customized fiscal calendar for your tenant that includes holidays and marketing windows that are important for your brand.

You can download an extended fiscal calendar (CSV file) that runs from 2016 to 2030 and uses a 4-5-4 pattern. This fiscal calendar includes the following holidays:

  • New Year’s Day

  • Martin Luther King Day

  • Valentine’s Day

  • President’s Day

  • St. Patrick’s Day

  • Memorial Day weekend

  • Canada Day

  • Independence Day

  • Juneteenth

  • Labor Day weekend

  • Halloween

  • Thanksgiving weekend

  • Black Friday

  • Cyber Monday

  • Christmas

  • Boxing Day

Use this CSV file to explore what a fiscal calendar looks like. Use a text editor to add additional holidays, extend for additional years, and so on. You may upload this CSV file to Amperity directly using the Feed Editor, and then apply semantic tags for fiscal calendars.

Loyalty programs

A loyalty program helps your brand build positive experiences with your customers in the form of rewards, referral programs, early access, incentives, customer community, more direct ways to provide feedback, and more.

Your highest-value customers are often members of your brand’s loyalty program. Use the loyalty data assets to analyze the performance of your loyalty program and look for ways to improve the experiences your customers have with your brand.

Loyalty events

The Unified Loyalty Events table contains a row for loyalty program events, unique by Amperity ID and event datetime.

Apply loyalty events semantic tags to data sources, and then add the Unified Loyalty Events table to your customer 360 database as a passthrough table.

Loyalty profiles

The Unified Loyalty table contains a row for every customer who belongs to your loyalty program, unique by combination of Amperity ID and loyalty ID.

Apply loyalty profiles semantic tags to data sources, and then add the Unified Loyalty table to your customer 360 database as a passthrough table, after which you can analyze retention rates and customer value for your loyalty program. For example:

Reference data assets

The following file-based data assets are available:

Enable file-based data assets

Amperity makes CSV files available to your tenant from an Amazon S3 bucket. Create a support ticket and request to enable the “Amperity data assets Amazon S3 bucket” for your tenant. Use any of these options:

After the Amperity data assets Amazon S3 bucket is enabled, use a courier to pull individual data assets from that location to your tenant using an Amazon S3 data source.

Address standardization

Address standardization is a data asset that contains a list of address variations for state and street names within the United States. Use this data asset to support address-based householding workflows.

The address standardization data asset is available from an Amazon S3 bucket named Amperity Data Assets. You may make a request to Amperity Support to enable file-based data assets, after which you can use the Amazon S3 data source to load the “address_standardization_conversion.csv” file from the “/householding” directory in that bucket.

To add the address standardization data asset

Add the address standardization data asset to your tenant by following the steps for adding a data source and feed from an Amazon S3 bucket. Click Browse and select the “address_standardization_conversion.csv” file from the Amperity Data Assets Amazon S3 bucket, which is located in the “householding” directory in the bucket.

Use all three fields – before, convert, and type as the primary key.

Add a passthrough table to your customer 360 database named LookupTables AddressStandardization, and then run your customer 360 database to build the LookupTables AddressStandardization table.

Important

The LookupTables AddressStandardization table is used within the Merged Households SQL template in a series of LEFT JOIN operations that are used to standardize addresses. For example:

LEFT JOIN (
  SELECT
    UPPER(before) AS before
    ,UPPER(convert) AS converted
  FROM LookupTables_AddressStandardization
  WHERE type = 'STREET'
) AS a7clean ON (a7clean.before = core.a7)

You can name this table anything else, such as Address Standardization USA. If you use the Merged Households SQL template, you will need to update the LEFT JOIN sections within that template to contain the updated table name.

Gender name ratios

Use the gender name ratios data asset to use gender prediction to apply personalization strategies to your marketing campaigns.

The gender_name_ratios.csv file contains a list of baby names from the past ~130 years, along with their associated gender.

The source of the data in the gender_name_ratios.csv file is from United States Social Security Administration records for popularity and frequency of baby names. These records were used to generate the gender_name_ratios.csv file, which is similar to:

given_name,predicted_gender,gender_name_ratio,male_count,female_count
EMILIA,F,7178.6,5,35893
THERESE,F,7025.0,5,35125
AILEEN,F,6969.8,5,34849
...
LINDSEY,F,20.2,7710,156111
MORRISON,M,20.2,1496,74
ROLLA,M,20.1,1306,65

The most important column is gender_name_ratio, which describes what proportion of given_name is associated with one gender versus the other.

Note

Only names with a ratio greater than 20 are included. This ensures that any prediction has a ~95% chance of being accurate based on the given name.

Only names with at least 1000 male or female examples were included. This filters out very uncommon names.

To add the gender name ratios data asset

Add the gender name ratios data asset to your tenant by pulling the file that is available from Amperity Data Assets, which is the name of an Amazon S3 bucket that can be made available to your tenant. Follow the steps for adding a data source and feed. Click Browse and select the “gender_name_ratios.csv” file from the Amperity Data Assets Amazon S3 bucket.

Use given_name as the primary key.

Note

If Amperity data assets credentials are not available on your tenant, make a request to Amperity Support to enable Amperity data assets for your tenant.

You can add predicted gender to your customer 360 database in two ways, depending on how your brand wants to use predicted gender to build segments:

  1. Extend the Customer 360 and/or Merged Customers tables to include predicted gender (recommended).

  2. Add predicted gender values to your customer 360 database as a standalone table.

Extend the Merged_Customers table (recommended)

Note

The steps are the same for both the Customer 360 and Merged Customers tables.

Edit the Merged Customers table and extend the table for predicted gender.

Use a common table expression (CTE) to pull data from the domain table that contains predicted gender data (“Predictions_Gender”):

predict_gender AS (
  SELECT
    mc.amperity_id
    ,CASE
      WHEN UPPER(ratios.predicted_gender) = 'M' THEN 'Male'
      WHEN UPPER(ratios.predicted_gender) = 'F' THEN 'Female'
      ELSE ratios.predicted_gender
    END AS predicted_gender
  FROM Merged_Customers AS mc
  LEFT JOIN Predictions_Gender AS ratios
  ON UPPER(
    COALESCE(mc.given_name, SPLIT(mc.full_name,' ')[0])
  ) = ratios.given_name
),

Update the list of columns in the Merged Customers table to include predicted gender and combined gender:

,pg.predicted_gender
,COALESCE(mc.gender,pg.predicted_gender) AS combined_gender

Note

The combined gender column uses the value from the gender column in the Merged Customers table when a value exists, and then uses the value from the predicted_gender column if the gender column in the Merged Customers table is empty.

Use a LEFT JOIN to join the values from the common table expression to the Merged Customers table:

LEFT JOIN predict_gender pg ON pg.amperity_id = mc.amperity_id

Add a table for predicted gender (optional)

Your brand’s use cases for predicted gender may prefer using a standalone table.

  1. Add a passthrough table to your customer 360 database named Gender Name Ratios.

  2. Add a SQL table to your customer 360 database named Predicted Gender.

  3. Choose SQL as the build mode, and then use SQL similar to:

    WITH ratios AS (
  SELECT *
  FROM Gender_Name_Ratios
  WHERE gender_name_ratio >= 100
)

SELECT
  mc.amperity_id
  ,ratios.predicted_gender
FROM Merged_Customers AS mc
LEFT JOIN Gender_Name_Ratios AS ratios
ON UPPER(
  COALESCE(mc.given_name, SPLIT(mc.full_name,' ')[0])
) = ratios.given_name

    where “100” represents a 99% accuracy threshold. Increase or decrease this value as necessary.

    Tip

    This table will be unique by Amperity ID and may be made available to the Segment Editor for use with campaigns.

Lookup tables

The following lookup tables are available from Amperity data assets:

Lookup: Countries

The country lookup data asset provides a set of standardized names and codes for all countries, along with categories for regions (Americas, Asia, Africa, etc.), sub-regions (Latin America and the Caribbean, Sub-Saharan Africa, etc.), and region codes.

To add the country lookups data asset

Add the country lookups data asset to your tenant by pulling the file that is available from Amperity Data Assets, which is the name of an Amazon S3 bucket that can be made available to your tenant. Follow the steps for adding a data source and feed. Click Browse and select the “lookup_country.csv” file from the Amperity Data Assets Amazon S3 bucket.

Use country_name and country_code as the primary key.

Note

If Amperity data assets credentials are not available on your tenant, make a request to Amperity Support to enable Amperity data assets for your tenant.

Add a passthrough table to your customer 360 database named Lookup Countries, and then run your customer 360 database to build the Lookup Countries table.

Lookup: United States

The country lookup data asset provides a set of standardized names and codes for the United States, including states and territories, along with United States Postal Service (USPS) and International Organization for Standardization (ISO) codes.

To add the United States lookups data asset

Add the United States lookup data asset to your tenant by pulling the file that is available from Amperity Data Assets, which is the name of an Amazon S3 bucket that can be made available to your tenant. Follow the steps for adding a data source and feed. Click Browse and select the “lookup_us_state_territory.csv” file from the Amperity Data Assets Amazon S3 bucket.

Use a combination of US_State_Territory_Name, USPS_Code, and ISO_Code_2 as the primary key.

Note

If Amperity data assets credentials are not available on your tenant, make a request to Amperity Support to enable Amperity data assets for your tenant.

Add a passthrough table to your customer 360 database named Lookup USA, and then run your customer 360 database to build the Lookup USA table.

Lookup: Zip codes

The zip codes lookup data asset provides a list of zip codes that:

  • Identifies if a zip code is standard, a post office box, or unique

  • Associates the primary city for each zip code

  • The state, county, and time zone in which a zip code is located

  • The phone number area codes associated with a zip code

  • The number of households and population counts within each zip code, including demographics

  • Geolocation data for each zip code

To add the zip codes lookups data asset

Add the zip codes lookups data asset to your tenant by pulling the file that is available from Amperity Data Assets, which is the name of an Amazon S3 bucket that can be made available to your tenant. Follow the steps for adding a data source and feed. Click Browse and select the “zip_code_database_small_business.csv” file from the Amperity Data Assets Amazon S3 bucket.

Use zip as the primary key.

Note

If Amperity data assets credentials are not available on your tenant, make a request to Amperity Support to enable Amperity data assets for your tenant.

Add a passthrough table to your customer 360 database named Lookup Zip Codes, and then run your customer 360 database to build the Lookup Zip Codes table.

Normalization: United States

The normalization for United States data asset provides a list of long and abbreviated names for all 50 states within the United States.

To add the zip codes lookups data asset

Add the normalization for United States data asset to your tenant by pulling the file that is available from Amperity Data Assets, which is the name of an Amazon S3 bucket that can be made available to your tenant. Follow the steps for adding a data source and feed. Click Browse and select the “US_State_Normalization.csv” file from the Amperity Data Assets Amazon S3 bucket.

Use US_State_Abbrev and US_State_Long as the primary key.

Note

If Amperity data assets credentials are not available on your tenant, make a request to Amperity Support to enable Amperity data assets for your tenant.

Add a passthrough table to your customer 360 database named Normalization United States, and then run your customer 360 database to build the Normalization United States table.