0

Link to sample sheet - https://docs.google.com/spreadsheets/d/1nKQXHwVO8KjsOy5qvjzh-s-YYRUYlAoH-3aXYbwcKsA/edit?usp=sharing

I have a report that's downloaded from the google ads console which looks like this:

enter image description here

I get a report from the client which attributes leads to google ads campaigns like this:

enter image description here

I want to combine both these tables together to make them look like this:

enter image description here

If you look at the data in the first to tables, you'll see that the report the client shares with me has leads attributed to 24th March. This date is not present in the report that I got out of the google ads console. Using a sumifs formula will not capture all the leads that I get from the client's report.

Essentially, to get the data from both tables in the output table, I need to do a full outer join.

I'm not sure how to do that in google sheets. Any help you can give me would be greatly appreciated!

BigBen
  • 46,229
  • 7
  • 24
  • 40
Abhay
  • 827
  • 9
  • 34

1 Answers1

3

I made a new tab in your sample called MK.Help.

I then put this formula in cell L3:

=ARRAYFORMULA(QUERY({A3:G;{H4:I,IFERROR(ROW(H4:H)/{0,0,0,0}),J4:J}},"select Col1,Col2,SUM(Col3),SUM(Col4),SUM(Col5),SUM(Col6),SUM(Col7) where Col1 is not null group by Col1,Col2 label SUM(Col3)'Impressions',SUM(Col4)'Clicks',SUM(Col5)'Cost',SUM(Col6)'Leads',SUM(Col7)'Offline Leads'"))

That do what you're hoping?

MattKing
  • 7,373
  • 8
  • 13
  • Your solution works for the sample sheet but I have no idea how it works so I'm unable to use it on my actual data. Could you explain how `{H4:I,IFERROR(ROW(H4:H)/{0,0,0,0}),J4:J}` part works? It would help me figure out how to join my tables. Thanks for your help! – Abhay May 22 '20 at 09:59
  • Hey there, Glad it's close to working. I added a tab on your sample sheet called MK.Demo that might help explain what that portion of the formula does. it's essentially just creating an array of 4 columns worth of blanks between what's in H4:I and J4:J. – MattKing May 23 '20 at 14:57