AI Data Transformation for Square Payment activity to NetSuite Bank Rec Import

Lots of creative and technical folks are showcasing really amazing examples for how AI can be used to create really amazing outputs.  And that’s great!  But I find that the average business leader is still having a hard time figuring out practical applications of AI tools to build legitimate points of efficiency beyond some written copy.  To that end, I’ve been creating a series of small posts to highlight some small, practical ways that we’ve leveraged AI for ourselves and our clients to earn some incremental wins in the AI space.  To be a success the tool doesn’t have to be revolutionary.  But if we’re able to incorporate AI and automation as a habit in an organization, those savings of a couple hours here and a couple hours there, over time, add up to organization wide productivity gains.

This example comes from an internal use case we built for ourselves.  

We have a lot of excess office space as a result of our hiring shifting from Portland-only to anywhere in the US through Covid.  Partly to help bring some energy to the office, and partly to get more involved in the community, we started a co-working space in half our office, targeted at startups and solopreneurs who needed a comfortable place to work for a low amount of money (we call it Upstart Collective).  Recurring payments for our Upstart members are managed through Stripe, as are a number of other one-off charges that happen in that business.  We use NetSuite as our ERP, and so we needed to find a way to get all that data into NetSuite so we could do our normal accounting and reporting functions.  

Stripe provides a handy export of data in a CSV file format, but there’s quite a bit of massaging that’s required to tease out the actual transactions to be created from that file.  Example:   One of the files has a column to delineate Credits and Debits, and the Amount is always listed as a positive number; dollar amounts are listed with the dollar sign, but NetSuite only accepts the CSV import with decimal numbers (no currency characters), etc.  Ultimately this means that every column of the file needs some data transformation to happen to support NetSuite's Bank Reconciliation CSV data import template.    

Historically we’ve taken one of two different approaches in these situations:  

• Have a user manually transform the data into the new format.  This can definitely work, but it’s time consuming every time it has to be done, and it increases the likelihood of user error creeping in.  Given the size of this particular file, that process would likely be about 2 hours each month.  
• Have a developer build an automation script in NetSuite (ie: a scheduled script that picks up the uploaded CSV File and automates the transformation).  This is a one-time investment of effort and cost (probably 12 hours of developer time).  We’re fortunate to have developers on staff who can do this, but it’s still a re-allocation of their valuable time that takes away from revenue generating capacity.   
•  

We wanted to see if we could automate this by using a ChatGPT Project.  Sneak peek:  It was a great success!  

As always with an AI Initiative, we started by gathering all of the relevant data:  

• NetSuite CSV Template for Bank Reconciliation Imports:  NetSuite has a defined (and rigid) structure that’s required to import this data.  This becomes our keystone of required data inputs.  
• Stripe Export File:  This file is the superset of available information we have to use in populating our NetSuite template.  

The first big task was to understand and map the Stripe Export File data to our NetSuite template needs.  In this process we started by evaluating each column in the NetSuite template, and determining how we would get that information from the Stripe export file.  A part of this understanding was to document how we would have to change the data from the Stripe export file to meet the standards of the NetSuite import template.  Each of these evaluations became the rules that we would apply in our ChatGPT project.  

Our next step was to build out the ChatGPT project.  In keeping with the “easy, practical application” spirit of things, we’re using the first tier of paid ChatGPT account, and the Project you can create through the user interface on the web.  It increases the amount of memory allocated to evaluation, and helps keep continuity and consistency better than happens within the regular chat.  It also lets us pair a normal prompt with (1) stored instructions, and (2) uploaded files.  All together, it presents a handy way to create saved, repeatable automation tools for a user. 

For the uploaded Files, the only one we provided was the NetSuite CSV import template for Bank Reconciliation data.  This is our keystone for our OUTPUT, so it was a really important set of data to be able to consistently reference.  

In the Instructions section, we stored all of our mapping rules to transform the data from the Stipe export file into the NetSuite import template.  

Here are the shared instructions we created:   

We're going to upload a CSV file, and I'd like to have the data from that file made into a CSV file using the Bank Rec Import Template csv file as the template.  Please make sure the resulting CSV file includes all of the columns that are present in the Bank Rec Import Template csv file, even if no data is mapped to those columns.  

Column Mapping:  

Column F (Arrival Date (UTC)) from the uploaded file should be mapped to Column A (Date (MM/DD/YYYY)) in the Bank Rec Import Template csv file.  We should use the date format MM/DD/YYYY for this column.  

Column T (Destination Name) from the uploaded file should be mapped to Column B (Payer/Payee Name) in the Bank Rec Import Template csv file.  

Column A (id) from the uploaded file should be mapped to Column C (Transaction Id) in the Bank Rec Import Template csv file.  

Column L (Description) and Column R (Trace ID) from the uploaded file should be concatenated with a "-" character as the delimiter, and the resulting value should be mapped to Column F (Memo) in the Bank Rec Import Template csv file.  

* Example of how to populate Column F (Memo) in the Bank Rec Import Template csv file:  If Column L (Description) from the uploaded file has the value "Grasshopper" and column R (Trace ID) from the uploaded file has the value "910000154534345" we would make a concatenated value of "Grasshopper-910000154534345" and use that value to populate Column F (Memo) in the Bank Rec Import Template csv file.  

In the file that is created using the Bank Rec Import Template csv file, Column D (Transaction Type) should be populated using these rule:  

• It should say "Credit" if the amount in Column B of the uploaded file is negative
• It should say "Debit" if the amount in Column B of the uploaded file is positive.  

Column B (Amount) from the uploaded file should map to Column E (Amount) in the Bank Rec Import Template csv file.  

• ADDITIONAL NOTE FOR COLUMN B:  The format for this column should be a decimal number, with no dollar signs or commas.  
• ADDITIONAL NOTE FOR COLUMN B:  We should reverse the sign on the values in this field.  If it is a negative value, make it positive.  If it is a positive value, make it negative. 

The output file should be a CSV file.  Please note:  There are three columns in the Bank Rec Import Template CSV file that will never have data, but still need to be included in columns G, H, and I.  

Column G:  Header should be "NS Internal Customer Id"

Column H:  Header should be "NS Customer Name"

Column I:  Header should be "Invoice Number(s)"

Here are a few things to note that we found were important to generate consistent results:  

• We used a consistent format of Column (name), starting with the “From” file and ending with the “To” file.  This helped ensure the mappings always went the right direction. 
• We try to avoid having more that one instruction in a sentence/bullet point.   
• We found that it was helpful to provide an example output in one of the field mappings as a benchmark for guidance.  
• We had to reiterate twice that the resulting file should include all columns from the NetSuite Template in the uploaded files.  We even had to call out the specific columns because they were frequently skipped in the output file if there were no values.  

The final step was to come up with a consistent prompt for when we run this project.  We landed on something simple and consistent that reinforced the instructions:  

Using the instructions and template file below, could you please create a CSV file using the data from this payouts 3.15.csv file? 

The end result is that this solution allows us to download the file from Stipe, upload the file to our Project, and ask it to process the file using this prompt.  The Project evaluates the file through the lens of the instructions, and outputs a downloadable CSV file that conforms to the format instructions.  A process that would have taken a couple hours per month, or maybe 12 hours of a developer’s time, was completed in about 4 hours and runs as consistently as if it were done via code. Each month it takes the user about 3 minutes to complete the entire process. 

Beyond just the efficiency side of things, there are a couple of really valuable aspects to this solution:  

• It was 100% created by business users, not developers.  This approach empowers the business users to create their own automations, rather than depending on technical resources for creation.  
• It’s flexible to maintain.  If NetSuite changes their Import requirements, or if Stripe changes their export details, changes to this transformation project can be done by updating text instructions, rather than updating code.  Similarly, as business needs evolve, the same business users can make those updates to evolve the project.