Streamlining Transaction Matching in Large Datasets

• Analytics & Modeling - Big Data Analytics
• Analytics & Modeling - Data-as-a-Service

• Equipment & Machinery
• Finance & Insurance

• Sales & Marketing

• Machine to Machine Payments

The challenge was to match credit and debit transactions in a single dataset made up of 24 million records. The dataset did not include any identifier that might match debit transactions (sales), and credit transactions (returns). Each debit transaction with a corresponding credit transaction had to be removed from the data, and isolated into a separate stream of records without that single identifier already mentioned missing. The legacy process was time-consuming and prone to errors, involving filtering for high count values, and finding their exact credit value. This process was performed manually, copying and pasting the debit row with sales and the credit row into a separate excel spreadsheet, and removing the “matched” rows from the larger dataset.

The customer in this case study is an accounting and financial industry professional who deals with large transactional data on a regular basis. The customer's role involves managing and analyzing transactional data, which includes information recorded from transactions such as purchases, returns, invoices, payments, credits, debits, trades, dividends, asset sales, contracts, interest, payroll, lending, reservations, signups, subscriptions, donations, and more. The customer was tasked with the challenge of matching credit and debit transactions in a single dataset made up of 24 million records, a task that was time-consuming and prone to errors due to the lack of a single identifier in the dataset.

The solution involved unioning all of the 30 excel workbooks with transactional data into a single .yxdb file (Alteryx Database File). A RecordID field was added to number each record. The data was then split between sales (debits) and returns (credits) using a Filter tool isolating records with a sales value less than $0. The absolute value of the negative sales value of the credit transactions was taken and a Join tool was used to join the debit transactions with the absolute value of the credit transactions. To avoid duplicating data in instances where there were two debit transactions with the same sales value, but only one corresponding credit transaction, a Multi-Row Formula tool was used to add a RecordID field grouped by the sales value in both the debit transaction stream, and credit transaction stream. The Join tool was then used again, with the configuration changed to join on the sales value, but also on the amount of times that the specific sales value actually exists in the data.

• The solution provided a significant improvement in the efficiency and accuracy of the transaction matching process. It eliminated the need for manual matching of transactions, which was time-consuming and prone to errors. The solution also allowed for the analysis of the entire dataset of 24 million rows of data, rather than just a piece of it, improving the completeness of the task. Furthermore, the solution was able to handle the large dataset in a single file that could be opened on a basic corporate machine, making it more accessible and easier to manage. The solution also reduced the risk of human error, such as forgetting to delete a transaction from one dataset or accidentally pasting a transaction multiple times in a separate dataset. Overall, the solution significantly streamlined the transaction matching process, making it faster, more accurate, and more efficient.

• The workflow ran in less than 6 minutes with only 8GB of memory.
• The solution was able to handle 24 million records of data in a single file.
• The solution eliminated the need for manual matching of transactions, saving significant time and reducing the risk of errors.