Visualize Data that updates dynamically

Question

TLDR: How can I create a bar chart that updates the x-axis daily? The end goal is to have a chart that shows a side by side comparison of goal completions performance for each source of traffic. Here is a couple of examples of what it looks currently.

I've used the Google Analytics add on for Google Sheets to create a workbook that provides an analysis of our event tracking & goal completions. https://developers.google.com/analytics/solutions/google-analytics-spreadsheet-add-on

I am having trouble getting the daily, weekly, & monthly reports by source to visualize properly. I have been able to figure out the overview reports and that should provide enough context to understand what I'm trying to do with the "by source" reports. https://docs.google.com/spreadsheets/d/1YvdVXbH5biaW2qY4pPi_g1AkNeJ0Dy9rcE2yQzCx2Xc/edit?usp=sharing

I have removed the report configuration tab but this workbook runs daily and compares to previous day, week, & month. Therefore, the source/mediums are constantly updating and I don't know how to visualize them properly because of the dynamic changes that create discrepancies in the source/mediums over time.

Any advice is much appreciated!

Answer 1

Edit : Improved formula.

={
    {'Daily Report'!F12,'Daily Report (previous day)'!F12}
      ;{
          ArrayFormula(iferror(VLOOKUP(A3:A25,'Daily Report'!$A$16:$F$25,6,false))),
          ArrayFormula(iferror(VLOOKUP(A3:A25,'Daily Report (previous day)'!$A$16:$F$25,6,false)))
      }
} 

---

Here's a way to do it :
(I updated your sheet in tab 'Daily Report by Source')

First you need every Source/Medium possible so I combined every entries from Daily report and Daily Report (previous day).

=UNIQUE({'Daily Report (previous day)'!A16:A25;'Daily Report'!A16:A25})

I kill duplicate with =Unique.

Then look for data with a VLOOKUP :

    ={iferror(VLOOKUP(A3,'Daily Report'!$A$16:$F$25,6,false),),
iferror(VLOOKUP(A3,'Daily Report (previous day)'!$A$16:$F$25,6,false),)}

IFERROR is here to replace error message when there's no value with nothing, but you can put whatever you need. {}is just telling Sheets this is an array.

What you can do instead is replacing the formula in B3 with this for an Arrayformula and extra lazyness.

=ArrayFormula(iferror(VLOOKUP(A3:A25,'Daily Report'!$A$16:$F$25,6,false)))

Results :