CPQ Data Import – VLOOKUP Solution

Hey folks. I’d like to preface this by saying that there are other tools and strategies for doing CPQ data imports.

1. Use a tool like DBAmp or Prodly.
2. Use External IDs on everything so moving data is just doing export then import without doing any spreadsheet work.

With that, we found something cool today. You may already have found this and improved your manual data migration lives. This article might be a long one so strap in. We are going to show two things here. First, we’ll show a manual strategy for migrating your CPQ data from one org to another. The second thing is a magic google sheets thing that makes this data import go faster and provide a facility to have a template sheet you can set up for your org to do migrations with.

Manual Strategy for Migrating your CPQ Data

An example process for importing Products, Features, and Product Options is as follows:

1. Export Products, Features, and Product Options from original org (All fields).
2. Put these three exports into a google sheet as tabs.
3. Blank out the ID field in each tab for all records.
4. Import Products into destination org.
5. Set the IDs column in your spreadsheet.
6. Import Features into destination org.
7. Set the IDs column in your spreadsheet.
8. Import Product Options into destination org.

You can do the above with google sheets and your favorite CSV import tool such as dataloader or dataloader.io. We will start the screenshots after step 5 where you can see my spreadsheet after I’ve exported the data and imported products and set the IDs for them after import. This is the Product2 export. Notice the ID column is filled in using the success CSV file. You can just select the whole ID column and copy / paste it into your spreadsheet.

Then we have the features that haven’t been imported yet. Notice the ID field isn’t filled in. But the ID for the configured sku is already filled in! How? It’s a formula in the cell for each feature row. You can see there is =VLOOKUP(C2, ‘Product Master’!1:8, 2, false) as the value of the selected cell. This looks up the ID in the range of rows 1 – 8 from the Product Master tab using cell C2 as a reference. We are looking up by product name! The 2 is saying we want the value from the second column (which is the ID) and the false means the specified range not sorted (which means it searches the whole range. We always use false here.).

Pretty great eh? As you can see we have similar lookups in the Product Options tab, which also hasn’t been imported yet. But wait! What is that Key column doing there? This is a way to look up the feature ID from the Product Options tab. In the Features tab, we need to set a key value we can use to look up an the ID of the feature for the feature in each product option. You can see this is the lookup that is selected below so you can see the similar lookup function and the feature key being used. F2 is the feature key in the Product Options tab.

So as you import, fill in the IDs in all of your tabs as you go. These are just example objects for CPQ. This strategy can be used for any related objects. Kapeesh?

So now is the time for the fun bit. Traditionally, we have this formula that we put in the cells for something like Configured Sku. So the ID automatically populates when we fill in the IDs of the Products. So we have to do a bit of spreadsheet copy / paste magic to get the correct formulas into all of the rows. If you just put one formula into a cell and then try and use the + on the bottom right of the cell and drag down, you’re going to get the correct values for the first parameter in the vlookup but it will also increment the range in the second parameter and the number in the third parameter. NOT GOOD. So we would make a column for incrementing the first parameter, copy that to a text editor, then find / replace things to inject the other parts of the formula, and finally copy / paste the whole list of new formulas into the Configured Sku column in the spreadsheet. What a nightmare. Takes forever.

Magic Google Sheets Thing

Here’s the solution. Pause and look at this for a second. Where is the formula? What is the formula saying?

We put the formula in the HEADER ROW!!! WHAAAAAAT…. Yep. Apparently you can specify a list of things in a cell with the { and }. So the first thing is the text you want to show in the cell. Then a semicolon to separate things. Then you see the formula. It’s the formula you had in the cell but inside a ARRAYFORMULA function. This means apply this formula to the range that is in the formula. We have the same VLOOKUP that we did with one change. The C2 is now C2:C. This means the C column but start at C2. Which is the whole range of C without the header column. When you make this the formula for the header of the column, you’ll get an error.

The error is because there is data in the rows of this column. All you have to do is delete the data from the whole column and it will work. This is scary. Because you’re deleting all of those rows full of formulas you spent hours creating. But have no fear. Just delete them. This means you can set up your spreadsheet and just paste in data from your exports without having to put formulas in all the rows for all the IDs!! You can do this with any column where you have a repeating formula all the way down the column.

Also! You got to the end of this article. As always, here’s a cookie. Link to the spreadsheet example above is here! (Psst. The formula you’re looking for is in Column B of the Features tab. Happy spreadsheeting!