1

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. enter image description here enter image description here

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!

0

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)))
      }
} 

Improved formula


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 :

Formulas Charts

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.