I have keyword data that automatically populates into a google sheet tab named 'Keyword Data' segmented by Campaign, ad group, match type and day. Not every row has a conversions. In another tab there is keyword data with similar columns but only for keywords that converted, called 'Conversion Data'. This tab also has Campaign, Ad Group, Match Type and Day segments as well as the additional Conversion type name.
It is set up this way due to the limitations of the export from Google Ads.
I created a tab called 'Full Data Set' where I would like to have the keyword data exactly as is on the 'Keyword Data' tab with the addition of data that shows if that keyword had conversions on that on that day, & what conversions they were. Essentially breaking down that 'all conversions' row in the 'Full Data Set' tab.
The end goal is to use this 'Full Data Set' tab to create a pivot table where I can look at cost per conversions for different conversions, ad groups, campaigns and so on. I don't need help with that part, just the formula. Just trying to give context.
To start, In that 'Full Data Set' tab, I created arrays to bring in all the data from the 'Keywords Data' Sheet. enter image description here
Then I made a helper sheet to list the unique values from the 'Conversion Action' column in the 'Conversion Data' tab. I then transposed these as rows back in the 'Full Data Set' as columns. This is the list of conversion names that actually occured and the # of times in each week
then it's transposed to the 'Full Data Set' tab
Then, I tried to make a VLOOKUP array that looks like this: enter image description here
Breaking down the idea behind the madness: =ArrayFormula(VLOOKUP($B2:$B&$C2:$C&$D2:$D&$H2:$H&J$1, 'Conversion Data'!$H$3:$I, 1, FALSE)) =ArrayFormula(VLOOKUP('Ad Group'&'Search Keyword'&'Search Keyword Match Type'&'Week'&'Conversion Action', Same Unique Identifier From Conversion Data Tab, 1, FALSE))
Basically I was making a unique identifier using the Ad Group, Search Keyword, Match Type, Week, and Conversion Name [which is the column header] in the 'Final Data Set' tab and searching the 'Conversion Data' tab for the same ID. Then trying to pull the corresponding number from the All Conv. row in the 'Conversion Data' tab.
This didn't work and I'm stuck.
Here's the sheet: https://docs.google.com/spreadsheets/d/1UXjTsBFvHy_8i9p7fcSNVcUhpks8j9-i7cnT1l3yzvU/edit?usp=sharing I would love some help, thank you so much for reading this! Let me know if you have questions.
Slightly tweaking your existing formula at Cell_P2 in Full Data Set tab:
=arrayFormula(ifna(vlookup($B2:$B&$C2:$C&$D2:$D&$H2:$H&P$1, choosecols('Conversion Data'!$H$3:$I,2,1),2,)))