Recipe for success: Pardot B2BMA columns to rows

What's not there might be important too! Further reading: Pardot B2B Marketing Analytics Implementation Guide · B2B Marketing Analytics Datasets ... Skiptocontent Searchfor: ABOUT WelcometoTheSpot,ablogaboutallthingsPardotandMarketingAutomation.TheSpotForPardotisacommunityblogwherePardotprossharehow-tos,miscellaneousideas,&theoccasionalwarstory. PoweredbySercante:Wehelpcompaniesofallshapesandsizesoptimizetheirlead-to-revenueprocessesontheSalesforceplatform. UPCOMINGEVENTS ParDreamin’2021– NowStreaming Bingeover100Pardot-focusedsessions Can’twaitforthenextParDreamin’event?We’vegotyoucoveredwithParDreamin’sessionreplaysondemand. WATCHON-DEMAND   SubscribetoTheSpot CONTACT[email protected]530.307.0534 LatestPosts NewPardotFeature:CustomComponentsforEmailContent TheUltimatePardotAdminGuidetoWebTrackingCookies ParDreamin’2021DemoJamRecap:ProductstoExtendPardot HowtoEmpowerHigherEdMarketingTeamswithPardot ParDreamiesBestInShowPardotAwardWinner:Destined ParDreamiesSocialImpactPardotAwardWinner:Lexipol ParDreamiesGrassrootsInnovationPardotAwardWinner:BrandEdHoldings NonprofitsonSalesforce:HowtoLeveragePardotforMarketingandMore ParDreamiesExtendingPardotAwardWinner:LexisNexisCanada PardotandSalesforceMarketingReporting:A101Guide ParDreamiesPersonalizationPerfectionPardotAwardWinner:Restaurant365 Announcingthe2021ParDreamiesPardotAwardWinners GetOnBoardwiththeNewPardotandSlackIntegration(Beta) HowtobeSuccessfulwiththePardotDrag-and-DropLandingPageBuilder 5WaystoStandardizeYourPardotBusinessUnits MarketingHocusPocus:6PardotHorrorStoriestoAvoid ImplementingPardotExternalActivitiesNativelyinSalesforce PreservingProspectEmailPreferencesAftertheNewPardotMailabilityUpgrade HowtoImplementPardotExternalActivitieswithZapier ParDreamin’2021:ThePardotWorld’sBigVirtualGathering PardotExternalActivity:Whatitisandhowtouseit CelebratingtheSuccessofPardotPros EverythingPardotAdminsShouldKnowAboutApplePrivacyUpdates UsingthePardotIntegrationfromZoomAppMarketplace HowToAutomateSalesforceCampaignNamingConventions Dreamforce2021HacksforBusyB2BMarketers HowtoImportProspectsIntoPardotWhileEnsuringCleanData AreyouReadyforthePardotProspectMailabilityUpgrade? CreatingaPardotSpamIdentificationProcesswithProspectUpdater NudgefromSalesforceWinter‘22:SwitchtoPardotLightning HowtoAuditDynamicContentandVariableTagUsagewiththePardotAPI FourbigSalesforceWinter‘22Pardothighlights BeyondDynamicContent:HowtoCreateREALContentPersonalizationinPardot WorkConfidentlyinPardotthroughQualityAssuranceTesting PardotProtectedCampaignMemberStatusesSolution 5StepstoPassthePardotSpecialistCertificationExam Recipeforsuccess:PardotB2BMAcolumnstorows AnnouncingTheSpotforPardotJobs UseAccountsasCampaignMembersinPardot Superchargesales‘speedtolead’withcleanPardotdata 3SoftSkillsThatMakeAPardotAdminAwesome WatchtheDataFlowintoPardotwithZapier Sercante’sGuidetoBecominganExceptionalPardotAdmin HowtoaddressPardotissuesrelatedtotheSalesforceoutage OneForm,TwoPardotOrgs–Part2 ContentPersonalizationUsingDynamicContent,PardotHML,andPardotSnippets OneForm,TwoPardotOrgs–Part1 Top8NewFeaturesfromSalesforce’sSummer‘21ReleaseNotes PlanningforyourPardotBusinessUnitImplementation SercanteReceivesSITrailblazerFundFromSalesforceVentures PardotMarketingDataSharing UpgradingtotheSalesforce-PardotV2Connector GettingaListofPardotBusinessUnitsForYourApp–Programmatically Can’tSignInToPardot:SalesforceSSONowRequired Duck,Duck,Goose:HowtoplaywithPardotSnippets! 21ResourcestoLevel-UpYourPardotEmailsIn2021 PardotAPIandGettingReadywithSalesforceSSOUsersPart3A:ConnectingtoPardotAPIfromAPEX PardotAPIandGettingReadywithSalesforceSSOUsersPart3B:ConnectingtoPardotAPIfromCode SinglePardotForm,MultipleCompletionActions PardotAPIandGettingReadywithSalesforceSSOUsersPart2–WordPressPardotPluginEdition PardotGoodiesfromSalesforceIdeaExchange CommonPardotSalesforceConnectorErrors&HowtoFixThem ConsiderationsforIntegratingPardot+Sitecore ParDreamin’2021Recap ParDreamin’Day4Recap ParDreamin’Day3Recap ParDreamin’Day2Recap TheOfficialParDreamin’GuideForIT,DevelopersandSalesforceAdmins TheOfficialParDreamin’GuideFor“Accidental”PardotAdmins TheOfficialParDreamin’GuideforAdvancedPardotAdmins TheOfficialParDreamin’GuideForNon-Profit&HigherEdorganizations AnnouncingParDreamin’2020 PardotAPIandGettingReadywithSalesforceSSOUsers ProvetheImpactofConversationalMarketingwithCampaignInfluence HowToMapLookupFieldsinPardot ClosetheLooponEvents:SplashandPardotIntegration CampaignInfluenceStarterPack:TheEasyButtontoProveResultsfromPardot First-PartyCookiesinPardot:FAQsAbouttheNewBeta APardotAdminFAQ:“Whydomyemailsbreakwhenforwarded?” WhattheNewTerminus+PardotIntegrationMeansforYourABMStrategy CreativeWaystoPersonalizeYourMarketingwithPardotAdvancedDynamicContent Q&A:EverythingYouNeedtoKnowaboutPardotEngagementStudio WhatSuccessfulMarketersKnowAboutSalesforceAuto-Association HowtoGetSalestoActuallyUseSalesforceEngage 15ResourcesToMakeTheMostOfYourLandingPages:TheUltimateListforPardotAdmins WhySalesforceisDiscontinuingPardot-OnlyUsers&MovingtoSSO TheThreeL’sinPardot:Lists,Location,andLeadAssignment HowtoTestinPardotSandboxes,TrainingOrgs…andEvenProduction ThePardotSandbox:9Need-to-KnowsAboutthisCriticalFeature LearnHowCollegesandUniversitiesareAdaptingtoDigitalChange–MarTechforHigherEd:ASercanteSeminar ScoringCategoriesandHML:ThePeanutButterandChocolateMoment The10BestPardotIdeasOntheIdeaExchange WhentoUseaPardotListEmailvs.EmailTemplate(andWhattoDoifYouBuildtheWrongOne) 4WaystoEncourage&AutomateAddingContactRolesinSalesforce(NewPostonTheDrip!) ImpactedbyCOVID-19?ApplyforthePardotBootcampScholarship PivotingPost-COVID:6DigitalStrategiesforHigherEducation PushingPardot:HowtouseDynamicContentWithMoreThan25Options HowPardotAdminsCanFINALLYBurytheHatchetwithSales HowPardotAdminsShouldbeThinkingAboutCoronavirus 4TipsforAvoidingEmailFatigueinPardot ChangeyourdatacolumnstorowsinPardotB2BMarketingAnalytics/TableauCRM Hasthiseverhappenedtoyou?YouwishyoucouldchangeyourPardotB2BMAdatafromcolumnstorows. YouhaveallthePardotB2BMarketingAnalyticsdatayouwantinasinglecolumn,butyouneedtocreateB2BMAreportsthatshowthedataasrows. ForExample: Yourdatalookslikethis. Butyouwantthedatatolooklikethis. Soyoucanmakevisualslikethis! Andyouwanttodothiswithoutusingacomplicatedqueryonthefrontendofyourdashboardthatcouldlimittheabilitytofilterandsortthedata… Therearemanywaystoaccomplishthis.ButoneofthesimplestandmostmanageablemethodsisthroughadatarecipeusingtheAppend(UNION)andtransformfilters.Thisapproachallowsyoutotweakyourrulesanddatawithoutcodeonthefrontendsoyoucankeepyourdashboardcreationoptionsflexible. ChangingPardotB2BMADataColumnstoRows Step1:Figureoutwhatfieldsandfilterstoadd. Thefirststepistoidentifytherules. Whatdetermineseachtype?Whichfieldsareneededforthedatavisual?Whatfiltersdoyouwanttoinclude? Inourexample,weknowtheMQL(MarketingQualifiedstage)hasbeenreachedifthe“Date__Qualified”dateisgreaterthan1971-01-01’.Eachstagehasitsownrules. Tocreateourdataset,weneedtodeterminewhatfieldsandfiltertoadd: MQLStatus(rulesfields)andMQLDateSQLStatus(rulesfields)andSQLDateSALStatus(rulesfields)andSALDate Filters:Country,Accountname Whencreatingyourdataset,besuretodocumentwhatfieldsareneeded.Keepthefieldlistassmallaspossible(youcanalwaysaddmorefieldslaterifneeded).Lessismoreforperformanceanduseabilityinthiscase. Step2:Choosethefieldsyouneed. Openyourdatarecipeandselectthedataset.Chooseonlythefieldsyouneed.Thiswillkeepthedatasetmanageable.Youshouldpickallthefieldsneededtocreatetherulesandthefieldsyouwillusetofilter.Documenteverythingyouselectasyouwillberepeatingthisprocessforeachrowtype. Step3:Attachatransformtothedataset. Inthisnextstep,you’regoingtoattachatransformtothedataset.Selectthe‘customformulaoption.’ Inourexample,anywherethequalifieddataisafter‘1971-01-01’weflagthetypeasMQL,otherwiseweleaveitblank. Step4:Addyourruleandcreateanewfield. Usethe“CASE”functiontoaddyourruleandcreateanewfield.Thiswillcreateanewfield(BusinessStage)thatwillindicate‘MQL’wherethequalifieddataisvalid.SetthefieldtypeastextandMakesuretorenamethefield CodeSample: case whenDate_QualifiedMQL__c>’1971-01-01′then‘MQL’else‘’end Step5:Attachanothertransform. Attachanothertransformtopopulatethecorrectdateusingthecustomformula.Weareessentiallycreatingadatefieldthatwillbethe“main”dateforourdatasetandallowtheaggregateonthedashboard. CodeSample: casewhenDate_QualifiedMQL__c>’1971-01-01′thenDate_QualifiedMQL__celseDate_QualifiedMQL__cend Step6:Addafilter. Addafiltertowhittleyourdatasetdowntojustthe“MQL”data. Nowyourdatasetlookslikethiswith“SQL”asaFunnelStatusandtheSQLDateastheFunnelDate.  Step7:Repeatsteps1through6. Repeatsteps1through6tocreatethenextrowtype.Foreach,makesureyourCASEstatementreflectsyourrules. Step8:Appendthedataflows. AppendthetwodataflowstogetherwiththeAppendconnector.Eachdatasetmusthaveidenticalrowstoconnectthemasone(unioned)dataset. Nowyourdatasetlookslikethis.YourSALsandMQLshavegonefromcolumnstorows! Foreachremainingvariable,repeatsteps1-7withcorrecttransformationrulesandaddappendtothedatasetasyougo. Step9:Createanoutputobject. Onceyouhaveaddedallyourtypesinfiltered“sub”datasets,createanoutputobject. Onceyourunthedataflow,youwillbeabletousetheFunnelStageandFunnelDatetodosummarieswithoutcomplexcode.IftheruleschangeforcreatingtheFunnelGroups,changetheTransformsintherecipeoraddnewones. Nowyoucanuseyournewdatasettocreatevisualsbydateandtypeorcreateasummarytable.  FurtherconsiderationsforreformattingPardotB2BMAdata Hereareafewthingstokeepinmindasyou’regettingstarted. Youcanusejoinstoaddotherdatasetstoyourrecipe–notetheyhavetoexistforall“streams”Verifyalldesiredvariableshavebeenaccountedfor(inourexampleafteryoufilterSQL,SALandMQLarethererowsthatarenotbroughtinthatyoumightneedinyouranalysis?)Youcanbringinasmanycolumnsasyouneedforfiltering.Sinceweareturningeach“column’intoitsownrow,eachnewcolumncancontainthesamedataithadbefore,makingallfiltersintheoriginaldatasetavailable.Makesureyoudoarealitycheckonthedataincluding:ExpectedrowcountsExpectedamounttotalsMissingvaluesNotethatwhenyoucreaterowsfromcolumns,youneedtodecideifyouwantALLdataconvertedorjustthedatathatmeetstherulesyouset.Ifindoubt,createanadditionaldatastreamthatfiltersoutallrecordsthatdon’tmeetanyofthecriteriaandmakesuretoreviewthose.What’snottheremightbeimportanttoo! Furtherreading: PardotB2BMarketingAnalyticsImplementationGuideB2BMarketingAnalyticsDatasetsPardotMarketingDataSharing Sharethis:ClicktoshareonTwitter(Opensinnewwindow)ClicktoshareonFacebook(Opensinnewwindow)ClicktoshareonLinkedIn(Opensinnewwindow) Publishedby DominiqueBeaudin DominiquewasborninPointeClaire,Quebec,Canada,butshespentmostofherchildhoodintheTorontoarea.Aftergraduateschool,shemovedtoWisconsintostaywithherparentsbeforemovingtoWesternCanada.lHowever,sheendedupmeetingherhusbandandstayinginWIinsteadforseveraldecadesbeforemovingback.Shehasthreesonsandenjoysrunninglongdistances,workingoutandlearningnewthings.Shehasapassionformetricsandhasspentmostofhercareerworkingwithvarioussortsofdataandanalytics(mostlyaroundMicrosoftprojects).SheisexcitedtopivotthoseskillstohelpSercanteclientsreachtheirfullpotential. ViewallpostsbyDominiqueBeaudin LeaveaReply Cancelreply Postnavigation PreviousPreviouspost:AnnouncingTheSpotforPardotJobsNextNextpost:5StepstoPassthePardotSpecialistCertificationExam WriteaComment... Email(Required) Name(Required) Website   LoadingComments... Comment ×