Google Analytics v3 API - SyncWith

Try the Google Analytics v3 API right now. Export your data to Google Sheets or CSV, read documentation and access tutorials. SyncWithSupportedAPIsTutorialsSupportPricingLoginConnectGoogleAnalyticstoGoogleSheetsConnectingtotheGoogleAnalyticsV3APItoGoogleSheetsviaSyncWithInthisreferencewe’lllookathowtosetupaGoogleAnalyticsv3(UniversalAnalytics)APIconnection.We’lluseSyncWithtogetyourGADataintoGoogleSheets.PleaseseeourGoogleAnalyticsGuidetoseeafulllistingofallourGAconnections,templates,tutorialandspecificusecases.InSyncWithifyousearchforgoogleanalyticsyou’llgetafewresults,includingourGAv4connectorandvariousGoogleAnalyticstemplates.Don’tHaveSyncWith?SyncWithisaGoogleSheetsAddOnSyncWithallowsyoutomovedatafromanyAPIintoGoogleSheetsLearnmoreaboutwhyMarketersloveSyncWithCheckoutourGoogleWorkspaceListingandseewhywe’rethehighestratedAPIaddonwithover100,000installs.InstalltodayandgetyourdataintoSheetsinminutes.ChooseaReportingTimeFrameYoucanchoosefromawiderangeofdefaultreportingtimeframesaswellasdynamicones.Somemarketersandagenciesmightreportonthelast21weeksofdata.Youmayormaynotwanttoseethecurrentdayinthereportingperiod.Allarepossiblewiththedaterangepicker.Popularoptionsinclude:Last30daysLast90daysLastMonthMonthtoDateLast5MonthsChoosingYourDimensionsThereareaLOTofdimensionsavailablefortheGoogleAnalyticssowe’lltakealookatsomeofthemorepopularones.Onewaytothinkaboutdimensionsiseverytimeyouaddoneyoucreatemorerows.Forexampleifyouhave30daysand20countries.Ifyourequestdateyou’llget30rows,ifyourequestcountryyou’llget20rowsifyourequestcountryanddateyou’llget600rows(aslongastheirisdataforeverydayforeverycountry).Forthisreasonaddingjustafewdimensionscanreallyexplodethenumberofrowsreturned.PopularGoogleGAv3DimensionsNameDescriptionDateUsefulfortrackingtrendsovertimeCampaignUsefulfortrackingyourvariousmarketingpaidandunpaidmarketingcampaigns,setviatheutm_campaignvariableSourceThisreferstothespecificsitewithinagivenchannel,forexampleifyouhavemedium=socialtwosourceofsocialtrafficmightbeTikTokorInstagram,setviatheutm_sourceMediumThisreferstothetypeofchannel,egsocial,search,direct,email,etc.Forexampleyoumighthavemedium=searchfortrafficfrombing,googleandduckduckgoFullReferrerThisshowsthedomainofthereferringsite,examplevaluescouldinclude: youtube facebook.com/ reddit.com/ google yahooCountryNameofthecountrytheuserisfrom,examplevaluescouldinclude: UnitedStates India Canada Brazil UnitedKingdom Japan France GermanyLandingPageThisbreaksthingsoutbythefirstpagetheuserstartedtheirsessionon,thisisveryusefulintermsofunderstandingthepagesthatareattractinguserstoyoursiteforinstance /-therootofyourwebsite /support-yoursupportpage /pricing-yourpricingpagePageUnlikelandingpagethisislookingatallpages,soifyouhadLandingPageandPageasdimensionsyou’dseeallthepagesbuttheywouldbebrokenoutbyLandingPage,thisletsyouaskthequestionwhensomeonelandson/pricingwheredoelsedotheygoonmysitePageTitlePageisSimplythetitleofthepages.WhilePageshowsthepath,PageTitleshowsthetitleyou’vespecifiedforexample /pricing-AcmeServicesPricingPlansISOWeekoftheYearWhilethedatecanbeusefulgettingbackstatsonaweeklybasiscanoftenbemorehelpfulifyouwanttochartthemout,especiallyifyouwanttolimitthenumberofrowsbeingreturned.UsingtheISOweekalsohelpsnormalizethedataincaseyouwanttocomparedaterangestootherplatforms.CampaignYoucanusecampaignorutm_campaigntotrackspecificpromotionssuchas: -instagrambiolink -specificfacebookadcampaigns ChoosingYourMetricsMetricscanbethoughtofasthedataforeveryrow.Usingourexampleaboveifyouhavecountryanddateasdimensionsyoustillneedtospecifywhatyouwanttoknowaboutthem,forexampleshowmemypageviewsandsessionsbydatebycountry.GoogleAnalyticsprovidesaLOTofdimensionssomeofthemorepopularoneswithdescriptionsareprovidedbelow.PopularPageInsightMetricsMetricDescriptionSessionsSessionsmeasurethenumberofvisitstoyourwebsitePageviewsPageviewmeasurethetotalnumbertimesapagewasviewed,includingmultipleviewswithinthesamesession.BouncesBouncesmeasurewhenauserleftthesiteonthepagetheylandedon(egtheydidn’tviewanyotherpages).Forasitethatdeliversallthevalueauserneedsinthefirstpageviewthiswon’tindicateanythingnegative.Ifauserisexpectedtoviewmultiplepageseg,viewmultipleproduct,gotoacheckoutcartandthencompletepaymentaabounceisn’tgreat.BounceRateThisisthenumberofbouncesdividedbysessions.Aslongasyou’renotaggregatingandsummarizingdatainyourgooglesheetyoucanusebouncerate.Butifyou’reaggregatingdataviapivottablesyou’llneedtocomputeyourownbouncerateontheaggregates.AverageSessionDurationTheaveragelengthofauserssessionSessionsDurationSessionsDurationisthetotalmeasurabletimeusersspentontheirentiresessions,thisisusefulifyou’reusingapivottableanddoingrollupsandyouneedtocalculateyourownaveragetimepersession.GoalxStartsThiswillshowthenumberoftimesagivengoalfiredashavingstarted.GoalxCompletionsYoucansetupmanygoalsinGoogleAnalyticswhichwillhavethenamesGoal1,Goal2....Goaln.Thiswillshowthenumberoftimesagivengoalfiredascompleted.UniquePageviewsTheunique(meaningonlyonepageviewperpersonmaycount)numberoftimesthepagewasviewedPages/SessionTheaveragenumberofpagestheusergroupviewsinasession.TimeonPageTimeonpageisthetotalmeasurabletimeusersspentonthepage,thisisusefulifyou’reusingapivottableanddoingrollupsandyouneedtocalculateyourownaveragetimeonpage.LimitsonMetricsRequestedItshouldalsobenotedthatifyouchoosemorethan10metircsGooglewillthrougha400error: SortingBySpecificMetricsorDimensionsYoucanchoosemultiplemetricsanddimensionstosortbyforexample:SortfirstbyDateAscendingSortsecondbySessionsDescending SettingaRowLimitIfyouwanttolimittosaythefirst20,000rowsyoucansetarowlimitSettingtheUpdateModeSyncWithsupports3updatemodes:ReplacecontentsofsheetAppendrowstoendofsheetUpdateoraddrowsasneededThefirsttwooptionsaresimpleinnature,thelastoptiontoupdateoraddrowsasneededismoreadvanced.ReplaceContentsofSheetPREFERREDMODEEverytimeyourefreshorSyncWithrefreshesbasedonyourpredefinedschedule,allthecellswillbedeletedandthenewdatawillbeinserted.Anycomputationalrowsthatyou’veaddedwillnotgetdeletedaslongastheyareoutsideoftherangebeingupdated.Ifyou’veinsertedacomputationalrowinbetweenotherdatarowsitwillgetoverwrittenFormattingyouapplywillstayintact,egifyouspecifyapercentage,number,datethatformattingforthecolumnwillpersistThismodeisidealforwhenyoudon’twanttocollectorstorepreviousdata.Forexampleyoujustwantthelast30daysofdata.AppendRowstoEndofSheetNOTCOMMONLYUSEDORRECOMMENDEDEverytimeyourefreshorSyncWithrefreshesbasedonyourpredefinedscheduleSyncWithwillappendthenewrowstothelastlinewithdataTheentiresheetwillnotbere-sortedbasedonanysortyouhavebuttheappendeddatawillbesortedandappendedaccordingtothesortyouspecified.ItwillNOTpreventduplicaterowsfrombeingcreatedItwillNOTupdateexistingrowsIfyouwanttoonlyappendnewrowsorproperlyupdateexistingrowsthenyouneedtouseUpdateorAddRowsasNeededThisisnotapopularupdatingmodeUpdateorAddRowsasNeededUSEDforusecaseswhereyouneedtocollecthistoricaldataThismodeusesthedateasakeytodetermineifarowisunique.Iftherowisunique(wedon’thavedatafromthatdateyet)thenitwilladdanewrowIftherowisnotuniqueitwillupdatethemetricsifthey’vechanged,egyoupulledapartialdaythedatawasupdatedthemostrecentpullwillupdatethemetricsforthedayswherethedatachanged.Thisupdatemodeisagreatoptionifyouwanttocollecthistoricalpagedata,bydoingmanysuccessful90dayperiodpullsofyourdataacrossolderdaterangesyoucancollectallyourhistoricaldata,thenchangetheconnectortojustgetthelast7daysdataonadailybasis.SchedulingYourGoogleAnalyticsDatatoGoogleSheetsManualRefreshManualRefreshIsthedefaultschedulingforaconnection.Datawillnotbeupdateduntilyouactivelyrefreshit.Inordertorefreshthedatayoumusteither:RefreshyourdatafromintheSyncWithaddonsidebarviatherefreshbutton-allconnectionscanbemanuallyrefreshedRefreshviatheAdd-onmenu,allowingyoutorefreshallconnectionssetup,orallconnection(s)pushingdatatothecurrentactivesheetRefreshviathecheckbox(locatedintheheaderabovethedatatable)ScheduledRefreshesThereare3optionsforscheduledrefreshes:Automaticallyrefreshdailywillrefreshthedataeverydayatthesametime.Thetimeissetbasedonwhentheconnectionwaslastupdated.Ifyouwishtosettherefreshtohappenataspecifictimeyouwouldneedtoopentheconnectionandsaveitatthattime.AutomaticallyrefreshhourlywillrefreshthedataeveryhourlyAutomaticallyrefreshevery5minuteswillrefreshthedataevery5minutes.Thisisnottypicallyusedforgoogleanalytics-unlessyou’relookingindetailatthecurrentday.Amaximumof100connectionscanuse5minuterefresh.SampleConnectionsSavingHistoricGoogleAnalyticsDataBeforeSunsetYou’veprobablyheardoftheGoogleAnalyticssunsetdatesthatareupcoming.GoogleannouncedthatinJuly2023UniversalAnalytics(GAv3)willbesunsetted.ThisarticlewilldealwiththeverysimplepropositionofgettingyourdataoutofGAandintoGooglesheetsforhistoricpurposes.Thebestapproachtostoringyourhistoricdataisgoingtobebasedontheinformationyou’llcareabouthistorically,youaremorelikelytogolookbackatbroadertrendsthannarrowerones.Wecanjustselect10dimensionsand10metricsandaskfor10yearsofdatabecausewe’llrunintoGoogleSheetscelllimitation(mostrecentlyupdatedto10millioncells).Asanexampleletssaywejustwantmedium,dateand10metricsforthelast10years:365days*10years=3650daysLetssay200differentsources10Metrics3650x200x10=7.3Mcellsofdatawhichisbumpinguptothe10Mlimit(andalsomakingaspreadsheetthatwillberatherslow.StrategiesforReducingRowsReduceGranularityofDateDoyoureallyneedtoseedailydataover10years?Whynotuseweeklygranularity.SelectISOWeekofISOYearandyou’llgettheweekandtheyearinasinglecellthose7.3Mcellsbecome~1Mcellsa7Xreduction.WecangoevenfurtheranduseMonthofYear-whichhasthemonthandyearcombined-togetanevenbiggerreduction-aremonthlytrendssufficientforyourhistoricanalysisReduceGranularityofaSetTherearealotofcountriesinthisworld,howmanyareimportanttoyouwhenlookingback?Youmighthavesometrafficfrom200countriesandyoucanalwaysdownloadtrafficbyyearforallcountries,butyoumightonlywanttrafficbyweekforthetop25countriesChangeGranularityforDifferentTimeFramesMaybethelastyearyouwantdailydataMaybethelast5yearsyouwantweeklydataMaybethelast20yearsyouwantmonthlydataDon’tlimityourselftoasingledataset.AnalysisFirstApproachStartfirstwiththethingsyouknowyoulookatWhatdatagoesintoyourweekly,monthly,annualplanningWhatdatadoyougobackandlookattounderstandtrendsWhatisimportanttoyourbusinessGoalsyoutrackTrafficsourcesReferralpartnersandtheirspecificUTMsExample1:TrafficSourceandLandingPageThiswillhelpyoulookatlongtermtrendsfortrafficsourceandthepagesthatattractedthattrafficWe’vechosenAlltimesowearegatheringasmuchdataaspossibleHerearethedimensionswe’reincluding:MonthofYear-ThiswillreturnYYYYMMwhichmeanswe’reonlyusingupasinglecelltocapturethedateandwe’reonlyusing12rowstocaptureanentireyearofdataLandingPage-ThiswillreturnthepagetheuserlandedonwhichisgreatforunderstandingthecontentthatdrovevisitsSource-Sourcewillletusandgobackandaskthequestions-howhasourtrafficfromreddittrendedovertime,whatwereourtoppagesinyearxforreddit,etcMedium-WillhelpuslookatbigtrendsonthetypesofsourcesegemailsearchdirectHerearethemetricswe’reincludingSessions-tounderstandhowmanypeoplewenttoourwebsitefromagivensourceortoagivenlandingpageBounceRate&Bounces-We’reincludingboth,ifyouneedtoincludejustoneuseBouncesasyoucancalculatebounceratefromBounces/Sessions.Ifyou’redoingaggregates-egwhatwasmybounceratefromredditbyyearyouhavetouseBouncesasyoucan’tsum/averagebouncerate.Avg.SessionDuration&SessionDuration-Similarlytobouncerateandbounceswe’reincludingbothsessiondurationandavg.sessionduration.Ifyouwanttoknowtheaveragesessiondurationfromgooglesearchbyyearyou’llneedtocalculateit.Avg.SessionDuration=SessionDuration/SessionsGoalCompletions1-3-We’vefoundthatmostpeoplearegoodatsettingupeventsandgoalcompletionsfromthoseevents.Theseareheresoyoucanseelongtermtrendsonyourtop3websitegoals,egnewslettersignups,casestudydownloadsorpricinginquiries-whateverwasimportantforyoursite.