Pull from Cross Country Computer

Cross Country Computer specializes in strategic solutions for companies that are seeking to evolve their marketing methods around acquiring, retaining, and developing valuable customers.

SFTP is a common way to make data sources available to Amperity. For example: files are made available from a SFTP site and are frequently refreshed. Use a courier to connect to the SFTP site and pull the refreshed files to Amperity on a regular basis.

Note

When using SFTP as a data source, a sample file is often required to set up the initial feed and courier processes.

Important

Files provided by Cross Country Computer are text files with fixed-width fields. These text files may (at first) appear to be a CSV file, but are not a delimited file format. This is the standard output and format of files provided by Cross Country Computer. You may request from Cross Country Computer to convert the text file format into a delimited CSV file, but Cross Country Computer may charge your brand a fee to reformat the file.

Because the frequency at which your brand updates data from Cross Country Computer is likely to be very low, Amperity recommends to use a custom domain table to apply formatting to the individual fixed-width fields within the text file provided by Cross Country Computer, after which you can apply semantic tags.

This topic describes the steps that are required to pull customer records to Amperity from Cross Country Computer:

  1. Get details

  2. Add data source

Get details

The Cross Country Computer data source requires the following configuration details:

Detail one.

The hostname for the SFTP site from which data will be pulled. For example: sftp.crosscountrycomputer.com.

Detail one.

The username and passphrase.

Detail one.

The host public key (if encryption is configured).

Detail one.

A private key when the “private-key” credential type is selected.

Detail one.

The public key to use for PGP encryption.

Detail one.

The remote folder from which Amperity will pull data.

Important

A “/” (forward slash) must be used as the first character for this value. For example: “/folder/name”.

Caution

Cross Country Computer provides files in text format (“some-file.txt”). You must use a custom domain table to define each field in the text file using a combination of the TRIM() and SUBSTR() functions to define the character length for each field in the file, after which you may apply semantic tags.

Add data source and feed

Add a data source that pulls data from an Cross Country Computer bucket for each file that you want to pull to Amperity.

Browse the Cross Country Computer bucket to select a file, and then review the settings for that file. Define the feed schema, and then activate the feed. Run the courier manually, and then review the data that is added to the domain table that is associated with the feed.

To add a data source for any SFTP site

Step 1.

Open the Sources page to configure Cross Country Computer.

Click the Add courier button to open the Add courier dialog box.

Add

Select Cross Country Computer. Do one of the following:

  1. Click the row in which Cross Country Computer is located. Sources are listed alphabetically.

  2. Search for Cross Country Computer. Start typing “cro”. The list will filter to show only matching sources.

Step 2.

Credentials allow Amperity to connect to Cross Country Computer and must exist before a courier can be configured to pull data from Cross Country Computer. Select an existing credential from the Credential drop-down, and then click Continue.

Tip

A courier that has credentials that are configured correctly will show a “Connection successful” status, similar to:

Add
Step 3.

Select the file that will be pulled to Amperity, either directly (by going into the SFTP site and selecting it) or by providing a filename pattern.

Add

Click Browse to open the File browser. Select the file that will be pulled to Amperity, and then click Accept.

Use a filename pattern to define files that will be loaded on a recurring basis, but will have small changes to the filename over time, such as having a datestamp appended to the filename.

Note

For a new feed, this file is also used as the sample file that is used to define the schema. For an existing feed, this file must match the schema that has already been defined.

Add

Use the PGP credential setting to specify the credentials to use for an encrypted file.

Add
Step 4.

Review the file.

Add

The contents of the file may be viewable in their raw format. You may need to adjust the value for the Header Row setting to “0”.

Important

Files provided to Amperity from Cross Country Computer are in a raw text format. Amperity may show formatting details. Use a custom domain table to define a schema for the contents of the text file, after which you may apply semantic tags.

Step 5.

A feed defines the schema for a file that is loaded to Amperity, after which that data is ready for use with downstream workflows within Amperity.

Use a new feed and the truncate and load option

To use a new feed, choose the Create new feed option, select an existing source from the Source drop-down or type the name of a new data source, and then enter the name of the feed.

Add

Use the Truncate and load option. This will delete all rows in the associated domain table prior to loading data each time the courier is run.

Step 5.

Some data sources contain fixed-width fields. Use a combination of the TRIM() and SUBSTR() functions within a custom domain table to define the length of each field in the file.

For example:

SELECT
  TRIM(SUBSTR(col_1, 2, 35)) AS NAME_LINE1,
  TRIM(SUBSTR(col_1, 416, 20)) AS FIRST_NAME,
  TRIM(SUBSTR(col_1, 436, 15)) AS MID_NAME,
  TRIM(SUBSTR(col_1, 451, 20)) AS LAST_NAME,
  TRIM(SUBSTR(col_1, 37, 35)) AS ADDR_LINE1,
  TRIM(SUBSTR(col_1, 109, 35)) AS ADDR_LINE2,
  TRIM(SUBSTR(col_1, 72, 30)) AS CITY,
  TRIM(SUBSTR(col_1, 102, 2)) AS STATE,
  TRIM(SUBSTR(col_1, 104, 5)) AS ZIP,
  TRIM(SUBSTR(col_1, 280, 10)) AS PHONE,
  TRIM(SUBSTR(col_1, 621, 60)) AS EMAIL_ADDRESS,
  TRIM(SUBSTR(col_1, 1, 1)) AS GENDER
FROM custom-domain-table-name

Note

The file provided by Cross Country Computer will contain fields that are unique to your workflow; the SQL that is required to apply formatting to each of the fixed-width fields will vary.

Step 6.

Use the feed editor to do all of the following:

  • Set the primary key

  • Choose the field that best presents when the data in the table was last updated; if there is not an obvious choice, use the “Generate an updated field” option.

  • For each field in the incoming data, validate the field name and semantic tag columns in the feed. Make any necessary adjustments.

  • For tables that contain customer records, enable the “Make available to Stitch” to ensure the values in this data source are used for identity resolution.

When finished, click Activate.

Step 7.

Find the courier related to the feed that was just activated, and then run it manually.

On the Sources page, under Couriers, find the courier you want to run and then select Run from the actions menu.

Add

Select a date from the calendar picker that is before today, but after the date on which the file was added to the Cross Country Computer bucket.

Add

Leave the load options in the Run courier dialog box unselected, and then click Run.

After the courier has run successfully, inspect the domain table that contains the data that was loaded to Amperity. After you have verified that the data is correct, you may do any of the following:

  • If the data contains customer records, edit the feed and make that data available to Stitch.

  • If the data should be loaded to Amperity on a regular basis, add the courier to a courier group that runs on the desired schedule.

  • If the data will be a foundation for custom domain tables, use Spark SQL to build out that customization.

Workflow actions

A workflow will occasionally show an error that describes what prevented a workflow from completing successfully. These first appear as alerts in the notifications pane. The alert describes the error, and then links to the Workflows tab.

Open the Workflows page to review a list of workflow actions, choose an action to resolve the workflow error, and then follow the steps that are shown.

Step one.

You may receive a notifications error for a configured Cross Country Computer data source. This appears as an alert in the notifications pane on the Destinations tab.

Review a notifications error.

If you receive a notification error, review the details, and then click the View Workflow link to open this notification error in the Workflows page.

Step two.

On the Workflows page, review the individual steps to determine which step(s) have errors that require your attention, and then click Show Resolutions to review the list of workflow actions that were generated for this error.

The workflow tab, showing a workflow with errors.
Step three.

A list of individual workflow actions are shown. Review the list to identify which action you should take.

Choose a workflow action from the list of actions.

Some workflow actions are common across workflows and will often be available, such as retrying a specific task within a workflow or restarting a workflow. These types of actions can often resolve an error.

In certain cases, actions are specific and are shown when certain conditions exist in your tenant. These types of actions typically must be resolved and may require steps that must be done upstream or downstream from your Amperity workflow.

Amperity provides a series of workflow actions that can help resolve specific issues that may arise with Cross Country Computer, including:

Step four.

Select a workflow action from the list of actions, and then review the steps for resolving that error.

Choose a workflow action from the list of actions.

After you have completed the steps in the workflow action, click Continue to rerun the workflow.

Bad archive

Sometimes the contents of an archive are corrupted and cannot be loaded to Amperity.

To resolve this error, do the following.

  1. Upload a new file to Amperity.

  2. After the file to the workflow action, and then click Resolve to retry this workflow.

Invalid credentials

The credentials that are defined in Amperity are invalid.

To resolve this error, verify that the credentials required by this workflow are valid.

  1. Open the Credentials page.

  2. Review the details for the credentials used with this workflow. Update the credentials for Cross Country Computer if required.

  3. Return to the workflow action, and then click Resolve to retry this workflow.

Missing file

An archive that does not contain a file that is expected to be within an archive will return a workflow error; Amperity will be unable to complete the workflow until the issue is resolved.

To resolve this error, do the following.

  1. Add the required file to the archive.

    or

    Update the configuration for the courier that is attempting to load the missing file to not require that file.

  2. After the file is added to the archive or removed from the courier configuration, click Resolve to retry this workflow.

PGP error

A workflow action is created when a file cannot be decrypted using the provided PGP key.

To resolve this error, verify the PGP key.

  1. Open the Sources page.

  2. Review the details for the PGP key.

    If the PGP key is correct, verify that the file that is associated with this workflow error was encrypted using the correct PGP key. If necessary, upload a new file.

  3. Return to the workflow action, and then click Resolve to retry this workflow.

SFTP site unavailable

The SFTP site at sftp.crosscountrycomputer.com is unavailable. This may be due to any of the following issues:

  • A configuration error within Amperity.

  • The SFTP server is unavailable or unresponsive.

  • The SFTP server does not have enough storage space.

  • Too many connection attempts were made to the SFTP server.

Note

Amperity will make multiple attempts to connect to an SFTP server before showing this workflow error.

To resolve this error, do any of the following.

  1. Verify the configuration for sftp.crosscountrycomputer.com.

  2. Verify that the SFTP server is available and responsive.

  3. Verify the amount of storage space that is available on the SFTP server.

  4. Decrease the number of couriers in Amperity that are configured to connect to sftp.crosscountrycomputer.com.

  5. Return to the workflow action, and then click Resolve to retry this workflow.

Unable to decompress archive

An archive that cannot be decompressed will return a workflow error; Amperity will be unable to complete the workflow until the issue is resolved.

This issue may be shown when the name of the archive doesn’t match the name of the configured archive or when Amperity is attempting to decompress a file (and not an archive). In some cases, the contents of the archive file may be the reason why Amperity is unable to decompress the archive.

To resolve this error, do the following.

  1. Verify the configuration for the archive, and then verify the contents of the archive.

    Update the configuration, if neccessary. For example, when Amperity is attempting to decompress a file, update the configuration to specify a file and not an archive.

    In some cases, re-loading the archive to the location from which Amperity is attempting to pull the archive is necessary.

  2. Return to the workflow action, and then click Resolve to retry this workflow.