Only email, phone or original_membership_no can be used as the UID for migrating a guest.
Extra care must be taken if any changes to the files are made in Excel since Excel will change the data to the incorrect format for columns such as original_membership_no and birthday (see notes on correct format for these columns below).
Unescaped quotation marks in the data will cause the script to crash, so the data should be scrubbed for that. An example of an unescaped quotation mark is a quotation mark appearing like this in a column: 78701".
Another data issue that will cause the script to crash is having a number with the letter e in it on its own in a cell with no other text or spaces (such as in the phone or address_line1 column), e.g. 4345e5939. The script will read this as a number with an exponent and crash.
Required columns
name - The name the client wishes to call its Punchh loyalty program
email - Guest email address. Must be a valid email address - if the email is invalid, the user will not be created and the API will return an error. Please see examples of valid and invalid email addresses here.
original_points - The guest's current points (not their lifetime points). Should not be higher than the point threshold to earn a reward (i.e. if the program is 100 pts = Free Sandwich, this value should not be more than 100. If it is higher than 100 the guest will receive a free offer (sandwich in this example) for every 100 points).
Optional columns (if no data is provided in these columns, they must be removed from the file)
phone - Guest’s phone number. Must be only 10 digits. Cannot have any special characters like parentheses or dashes. Should only be included if Phone is the UID for migration. If used as the UID for migration, this value should be unique for each record.
original_membership_no - Saving/re-saving the file in Excel will change the cell format for this column to the wrong format and cause the incorrect membership numbers to be added for guests in the file. Doing so will cause migration to fail for any affected user trying to migrate using their original_membership_no.
If used as the UID (unique identifier) for migration, this value should be unique for each record. Membership number of the guest as per the incumbent loyalty program. This is typically a physical loyalty card number. Should only be included if this is the UID for migration.
first_name - Guest’s first name
last_name - Guest’s last name
address_line1 - Guest’s address
city - Guest’s city
state - Guest’s state
zip_code - Guest’s zip code
birthday - Must be in YYYY-MM-DD format and cannot be less than 13 years or greater than 200 years in the past from the date when the script is run.
Excel will change the birthday date format to the wrong format if the file is saved/re-saved in Excel. This will cause an error and the guest will not be created as a result.
registration_date - The date the guest registered for the previous loyalty program. Must be in ISO 8601 date format YYYY-MM-DDTHH:mm:SSZ (e.g. 1985-11-25T00:00:00Z).
preferred_location - The Punchh location_id of the guest’s favorite location
initial_points - Only used when the loyalty program has membership tiers. If tiers are new to the loyalty program, this field is not needed.
The initial_points column can be used to award guests the points needed to place them to the appropriate tiers (e.g. if the tiers are 0, 150, and 500, this value should only be 150, to place the guest into 2nd tier, or 500 to place them in the highest tier).
migrated_rewards - This is the dollar value of any rewards that the guest has in the current loyalty program. NOTE: Only used in Points Based loyalty programs.
redeemable_id1 - The Punchh redeemable_id for the first redeemable the client wishes to give to the migration user
rewards_count1 - The quantity of redeemable_id1 the client wishes to give to the migration user
redeemable_id2 - The Punchh redeemable_id for the second redeemable the client wishes to give to the migration user
rewards_count2 - The quantity of redeemable_id2 the client wishes to give to the migration user
redeemable_id3 - The Punchh redeemable_id for the third redeemable the client wishes to give to the migration user
rewards_count3 - The quantity of redeemable_id3 the client wishes to give to the migration user
redeemable_id4 - The Punchh redeemable_id for the fourth redeemable the client wishes to give to the migration user
rewards_count4 - The quantity of redeemable_id4 the client wishes to give to the migration use
Note: If additional redeemables need to be given to migration users, follow the above format (e.g. redeemable_id5 and rewards_count5, redeemable_id6 and rewards_count6 and so on.
marketing_email_subscription - Specifies whether the guest wishes to opt in or out of receiving marketing emails. Set to TRUE for opting in and FALSE for opting out of marketing emails.
challenge_campaign_id1 - The Punchh challenge_campaign_id of the first challenge campaign to be added to the migration user’s profile
progress_count1 - The number of steps the guest has completed towards completing challenge_campaign_id1
challenge_campaign_id2 - The Punchh challenge_campaign_id of the second challenge campaign to be added to the migration user’s profile
progress_count2 - The number of steps the guest has completed towards completing challenge_campaign_id2
Note: If additional challenge campaigns need to be added to migration user profiles, follow the above format (e.g. challenge_campaign_id3 and progress_count3, challenge_campaign_id4 and progress_count4 and so on.
File format should be with the same column name:
(Please note many of the columns are optional and can be added with the mandatory column fields)
| name | phone | original_membership_no | first_name | last_name | address_line1 | city | state | zip_code | birthday | registration_date | preferred_location | initial_points | original_points | migrated_rewards | redeemable_id1 | rewards_count1 | redeemable_id2 | rewards_count2 | redeemable_id3 | rewards_count3 | redeemable_id4 | rewards_count4 | marketing_email_subscription | challenge_campaign_id1 | progress_count1 | challenge_campaign_id2 | progress_count2 |
Disclaimer:
In an effort to supply information as quickly as possible, this article has been published prior to a formal technical review, and is subject to factual, grammatical, and various structural errors. Data may be incomplete, misordered, or incorrect.
This additional disclaimer will be removed upon formal review of this article. The standard Punchh Inc. KB Disclaimer still applies, and can be found at: https://support.punchh.com/hc/en-us/articles/360040100273-Punchh-Inc-Knowledge-Base-Disclaimer
If further assistance is required, submit a ticket to Punchh Support. (For help submitting a ticket, click here)