How to Manually Reconcile Keap Delta Export Records in Google Sheets
In the world of CRM management, ensuring data accuracy and consistency is paramount. For businesses relying on Keap, understanding how to effectively reconcile “delta” export records in Google Sheets can save countless hours and prevent critical data discrepancies. A delta export represents only the changes since your last full export, making it efficient but requiring a methodical approach for integration. This guide provides a practical, step-by-step process for manually reconciling these vital updates, transforming a potentially complex task into a manageable routine for maintaining a clean and current Keap database.
Step 1: Export Your Keap Delta and Full Record Data
The first critical step in reconciling your Keap data is to acquire the necessary exports. You’ll need two distinct datasets: your most recent “delta” export and a full export of your Keap records. The delta export captures only the changes – new records, updated fields, or deleted entries – since your last full backup or export. Navigate to your Keap account’s data export section, typically found under Admin settings or Reports, and select the option for a “Delta Export” or “Changes Only” export for the relevant contact or company data. Simultaneously, ensure you have a current “Full Export” of all your records. This full export serves as your master source of truth against which the delta changes will be applied. It’s crucial that both exports contain a unique identifier, such as the Keap Contact ID or Company ID, as this will be your primary key for matching records in Google Sheets.
Step 2: Prepare Your Google Sheets Environment
Once you have your Keap exports, open a new Google Sheet. For clarity and organization, it’s highly recommended to create three separate tabs: one for your “Full Master Data,” one for your “Delta Export,” and a third for “Reconciled Data.” Label them clearly to avoid confusion. Before importing, take a moment to review the column headers in your exported CSV files. Ensure they are consistent and recognizable. If you anticipate dealing with very large datasets (tens of thousands of rows or more), consider that Google Sheets has performance limitations. For extremely large sets, a dedicated database or more robust spreadsheet software might be necessary, but for typical Keap exports, Google Sheets is usually sufficient. This organized setup will streamline the reconciliation process and make it easier to track your progress.
Step 3: Import and Standardize Your Delta Export Data
Now, import your delta export CSV file into the “Delta Export” tab of your Google Sheet. Use the “File > Import > Upload” function and choose to “Replace current sheet” to avoid data stacking. After importing, immediately inspect the data for consistency. Pay close attention to data types: ensure dates are formatted as dates, numbers as numbers, and text fields are clean. Remove any irrelevant columns that were part of the export but are not necessary for reconciliation, reducing visual clutter. Most importantly, identify the unique Keap ID column and ensure its integrity. You might need to use basic text functions (`TRIM`, `CLEAN`) to remove leading/trailing spaces or non-printable characters that could interfere with matching in later steps. Standardizing this data now prevents errors and ensures accurate comparisons.
Step 4: Import and Prepare Your Full Master Data
Next, switch to your “Full Master Data” tab and import your full Keap export CSV file. Similar to the delta export, use “File > Import > Upload” and “Replace current sheet.” This sheet now represents the most complete picture of your Keap data *before* applying the delta changes. Perform the same standardization and cleaning steps as you did for the delta export. Verify column headers, check data types, and ensure the unique Keap ID column is consistent and properly formatted. This master sheet will be the foundation for identifying new records and updating existing ones. Having both datasets clean and consistently formatted on their respective tabs is crucial for the formulas you’ll implement in the next steps to work correctly and yield accurate reconciliation results.
Step 5: Identify New and Updated Records Using Lookup Functions
On your “Reconciled Data” tab, you will begin the actual reconciliation. Start by copying all the data from your “Full Master Data” tab into this new tab. Now, you need to identify which records in the “Delta Export” are new and which are updates to existing records in your master data. In a new column in your “Reconciled Data” tab (e.g., Column A), use a `VLOOKUP` or `XLOOKUP` function to check if the Keap ID from your delta export exists in your master data. For example, `=IFERROR(VLOOKUP([Delta_ID], ‘Full Master Data’!A:A, 1, FALSE), “NEW”)`. This formula will return the ID if it exists, or “NEW” if it doesn’t. For updated records, you’ll need to compare specific fields. You can use a combination of `IF` statements and exact `MATCH` functions across relevant columns to flag discrepancies. This step systematically flags every delta record as either a truly new entry or an update to an existing contact or company.
Step 6: Apply Delta Updates and Flag Discrepancies
Once you’ve identified new records, copy them directly from the “Delta Export” tab and append them to the bottom of your “Reconciled Data” tab. For existing records that have updates in the delta export, you’ll need to meticulously apply those changes. Using a combination of `ARRAYFORMULA` with `VLOOKUP` or `INDEX/MATCH` functions, you can automate the update of specific fields. For example, in your “Reconciled Data” tab, for a column like ‘Email Address’, you might use `=IF(ISNUMBER(MATCH([Keap_ID], ‘Delta Export’!A:A, 0)), VLOOKUP([Keap_ID], ‘Delta Export’!A:Z, [Email_Column_Number_in_Delta], FALSE), [Current_Email_Address])`. This formula checks if the ID is in the delta; if so, it pulls the new email, otherwise, it retains the old. Simultaneously, use conditional formatting to highlight cells where the delta value differs from the original master value, drawing your attention to specific changes for review. This dual approach ensures updates are applied while maintaining visibility over every modification.
Step 7: Conduct a Manual Review and Finalize Reconciliation
The final, and arguably most critical, step is to perform a thorough manual review of your “Reconciled Data” tab. While automated formulas handle the bulk of the work, human oversight is essential, particularly for sensitive data fields or complex changes. Pay close attention to records flagged by conditional formatting, as these indicate direct updates from the delta export. Verify that the changes are accurate and intentional. Look for any anomalies, such as unexpected “NEW” records that might be duplicates, or crucial fields that appear to be missing or incorrect. This manual check allows you to catch any edge cases that automated rules might miss, ensuring the highest level of data integrity. Once confident, your “Reconciled Data” tab now represents your updated master Keap dataset, ready for re-import or as the basis for your next full backup.
If you would like to read more, we recommend this article: The Essential Guide to Keap Data Protection for HR & Recruiting: Beyond Manual Recovery
