Combining Multiple Data Sources to Tell the Whole Story
Thank you everyone for joining today. I’m very happy to see all the other presenters at the Analytic Summit. It’s definitely exciting to hear what every else is doing in the industry, and all the awesome case studies. Hopefully you’re going to enjoy today’s session about combining multiple data sources. I’m going to talk about a couple case studies. One that’s a little bit more thorough and the last one I’m going to walk you through some actual practical ways to combine these multiple data sources. Again, thanks for joining, and let’s get started here.
This is an Indian parable. It’s an illustration of the six blind men and an elephant. Essentially, they were given the task to go to this big elephant, and identify what this thing is, or what this beast is. I’m trying to use this illustration to describe what really happens in the real world when it comes to data.
As you can see here, these blind men, each of them have their own take on what this thing is. One of them says it’s a big snake. Another says it’s a little furry mouse. Another, a tree stump. At the end of the day, so many organizations are like these blind men. They’re blind because data lives in different silos, different groups who don’t speak to one another. In fact, some groups don’t even have access to a lot of the data. Without seeing the whole picture, without opening our eyes and seeing what’s in front of us, we’re never going to see that we have a beautiful, majestic, elephant in our village. I’ve got a couple examples that I mentioned earlier, that I’m going to go through to help drive this point. Here we go.
The first one is about National Chocolate Day. It’s probably the best holiday in the USA, after Christmas.
We partnered up with a super store called “Piggly Wiggly”. Now all of this is fictional, we’ve changed the data too. But essentially, these are insights that we worked on. Piggly Wiggly just launched a brand new mobile app. The mobile app is intended to draw people to use it so that they can redeem coupons, and hopefully they can generate higher customer loyalty. They decide, “Well, we’re going to have a coupon for free chocolate cake during the National Chocolate Cake Day. What better way to celebrate that holiday than with free chocolate cake?”
They decided to launch this campaign. What we’re going to do is analysis the performance of the free chocolate cake campaign. We’re going to have to bring in multiple data sources. We’ve got marketing, we’ve got clickstream data, and we’ve got transactional data. And we’re going to use a couple tools to bring that data together.
With this campaign, the business has asked that we answer a couple key questions. First they want to know how well did this campaign drive app downloads? The second is: how well did the campaign engage users with the app? And last: how well did the campaign drive retention?
Let’s dive into the data a little bit. But before we do, I want to create our data model. We’re bringing in multiple data sources, so we want to make sure that we bring all this data together. Facebook, we got our download data, we’re getting engagement data from Adobe, transactional data from Redshift. We’re going to join Facebook and Adobe Analytics data using the campaign ID, and we’re going to join Adobe Analytics and Redshift using the user ID. This is all data we’re collecting that’s readily available.
Let’s look at the data now. The first question that we had was how well did this campaign drive app downloads? We can see here that we plastered Facebook with all these beautiful ads, awesome chocolate cakes making me hungry already. We want to know, “Did this actually work?” Let’s look at the results. We saw a significant increase in downloads. Over 285 percent increase over the previous period. In addition, there was a new daily download record. We’ve hit the most downloads for our Piggly Wiggly app than we’ve ever had before.
Awesome news and the marketing is ecstatic, right? They’re super happy, they’re celebrating now. They did a fantastic job. They launched the Facebook ads, and we’ve got a tremendous amount of downloads.
Let’s go to the next question we had. How well did the campaign engage users? Now we’re going to be looking at some Adobe Analytics data. We notice that we have an increase of 18 percent at the registration rate. The user folks that have downloaded the app, and gone through the registration process so they can actually see this coupon for the free cake. Next we had the 41 percent increase in the coupon view rate. This is the number of folks that are viewing a coupon for the first time after they have registered.
Again, this looks like it’s going really well. And last, let’s look at coupon redemptions. Significant, off the chart, 259 percent increase in coupon redemptions. People are going crazy for the chocolate cake. I can’t blame them, chocolate cake is amazing. So far, we’re looking at marketing and engagement data, and this campaign is looking fantastic.
We’re having another party at the headquarters. The product team is super happy. The marketing team is having their party. The product team is also having a party because their app is performing really, really well.
Now let’s see: what does the data actually tell us about retention? We want to see, we invested all this money to drive these customers to download the app. How long are these customers going to stay with us? The blue line here is our control, and the red line is the campaign. We can see that the retention cohort is down 33 percent. Here, we’re looking at anyone that anything remotely engaging in the app, just even opening the app and returning. They don’t even need to look at a coupon. We’re seeing that this cohort is 33 percent lower than the control. This isn’t going too well here for this campaign right now. Let’s see what other data we have.
Now we’re going to bring in the purchasers cohort. Again, the blue line is the control, the red line is the campaign. We’re seeing that the campaign is performing 58 percent lower than the control. Here we’re looking at users that have purchased during the campaign period, and have come back and purchased again. By the looks of it, we’re not doing too well when it comes to driving retention.
The store managers at Piggly Wiggly are a little bit confused, “We drove so many new customers to download the app. We sold all of our cakes, but these guys aren’t coming back. What’s going on here?”
Let’s look at the story. Facebook did amazing. All those Facebook ads with a 285 percent increase in downloads. All of our engagement metrics are off the charts. But our retention metrics are down compared to the control. This is sort of a mixed bag. Depending on which side of the organization you work in, you can pick and choose which story you want to run with. But at the end of the day, if we look at this whole picture, we can see that there’s some really good stuff that’s going on with this campaign, and some things that are either bad, or expected, or some things that we need to take account if we’re going to run a similar campaign in the future.
Let’s look at some key takeaways here for this campaign. One thing is we know that this free chocolate cake coupon campaign has achieved a record number of downloads. It’s hard to beat free chocolate cake. It’s a little surprise that we hit that record number. Second thing that we realize is free stuff will definitely drive significant engagement within the app. As long as we’re giving out free food, or free stuff to our customers, they’re definitely going to come back to use the app, especially if it’s required to use the app.
Third thing is freebie customers will drive engagement in sales. But they’re not going to be as valuable as other segments of your customers where they organically come back, or they find some other type of value that Piggly Wiggly has. And last, if we really want to drive customer loyalty here, we may need to do something more than just these one-off coupons. They definitely with driving short-term goals. But at the end of the day, we may need a larger, more comprehensive program that can generate high retention and loyalty.
I performed not a lot of analysis on this campaign here, but there’s definitely some other things that we can look into. Some other questions that we can answer are, what was the costs to acquire these new customers? Second, how does the customer lifetime value for customer acquired does this campaign compare to the control? And last, at what point do we expect these customers to become ROI positive? We’ve now spent a significant amount of money to drive these downloads, we’ve given away tons of free chocolate cake. Is this campaign ever going to become ROI positive?
These are some key questions we should probably look into if we’re going to dive deeper into this campaign. This is a really good example of how we can look at the whole story. You guys can see we have multiple data sources. Each one can really tell a good story, or spin it in a way that you want.
I want to go into my second case study now. And with this, I’m going to actually show you guys who you guys can combine some of this data yourself. It’s going to be a little bit easier than you may have expected.
I want to analyze the performance of paid search campaigns. I want to bring in a couple different sources, I’ve got a couple new ones this time. I’ve got Google AdWords, Adobe Analytics, and Salesforce. Again, I’m going to use the Cognetik Cloud Connector and Tableau.
Before I get started, let’s look at the data model here. The good thing is I’m tracking campaigning across all these data sources, looking at campaigns, or engagement, or transactional data, we’re going to be able to join this pretty nicely. And what I’m trying to understand here at the end of the day, is for the SAS software that I’m selling, how many of these free trials are actually converting to paid customers?
Let’s pull the data together. Let’s get out of PowerPoint here.
Demo Screen 1:
Now, I’ve already prebuilt some of these data connectors, but I do want to go through a few examples really quickly with you guys so you can see how easy this is to do within Tableau.
I’m going to create a web data connector within Tableau. I’m going to type “Tableau.cognetic.com”. Here, I’m going to sign in. For this first data connecter, I want to create, bring in my Google AdWords data. I’ll select my credentials. I have a couple options, different types of reports that I can bring in. I want to bring in the campaign performance report. I’m going to select the Cognetik AdWords account. Because I ran multiple campaigns over this time, I want to run my query across all these campaigns.
Let’s pull in some dimensions here. The dimension we’re going to use campaign. Let’s look at some really important metrics. Impressions, we’ll look at clicks, and I want to bring in my cost area. In terms of granularity, I have a couple options here. But I just want it graded at the top level. In terms of calendar, we’ll go standard. Now the date range, I do want a custom date range. This campaign did take place a while back, so I’m going to use a custom date range here. Back in March. I’ll set my row limit a little higher. And we’re going to name our data extract here.
Before we create the data extract within Tableau, I’m going to hit preview. You can see that the data came back really quickly. And this data looks really good. I see my campaigns here, I’ve got my impressions, clicks, and costs. We’ll do a submit, it should only take a few seconds here to create this data extract. Then once it comes back, we can use it to start blending and trending this data together.
Awesome, so my data came through. We go here to my worksheet. I do want to show you my Adobe Analytics query. I’ll sign in really quick. You can see I’ve created. I’ve selected Adobe Analytics as my provider, I’ve got my credentials and report suite set up. I’m bringing campaigning again because that’s what we’re going to adjoin the data. I got my metrics, granularity, and all the other settings look perfect, awesome. I’m just going to close this because I’ve already pulled this data.
Let’s bring this data together now. I’m going to bring in my Adobe Analytics data. Campaign, we’ll do visits, we’ll do bounce rates. I want to see how many folks from this campaign are actually converting to free trials. We put this in the table here. This goes pretty quickly. Now I’m going to go to my Google AdWords data. For this to work here, I’m going to make sure I have defined my relationships between this data. As I mentioned before, we’re using campaign name. So it looks like this is already set. Adobe Analytics to AdWords campaign. I’m going to do the same thing for the Salesforce data. Awesome, so it’s all set in place. I want to bring in impressions here. As you can see, I dropped in impressions, and it’s already dropping it in place with that campaign.
Next, I want to bring in my click through rates. And then I want to do costs per trial. So now I have my marketing and my engagement data, and I can start to see some insights here with this data. But, I now need to bring in my transactional data. I really want to see which one is performing best at converting to paid customers. I’ve got this dataset up and voila, we’ve just now easily blended these three data sources within Tableau. Super easy, we can now see a picture across all these data sources.
Let’s go back into PowerPoint because I’ve got it a little easier to read in here. Let’s see what kind of insights we got. First question, which campaign performed best at signing up free trials? Cloud API was a close second at 6.45 percent conversion rate. But Tableau API performed the best at 7 and a half.
Now we’re looking at AdWords data. We want to see which campaign had the lowest cost per trial. We see Tableau API campaign wins again. The Display Tableau API campaign really came in close here too at 26 dollars.
Now we’re looking at marketing engagement data, I want to add one more data point here, the transactional data.
Which one performed best at converting free trials to paid customers? Wow, I got three data sources here, awesome insights. I see Display Tableau API is doing the best at converting those customers. We can see that there are some wins with a couple of these campaigns. Tableau API performs really well across two. And the Display Tableau API performs really well across one data point. One being KPI.
This may all be good, or some may be good. Depending really on the goals that you’re trying to achieve here. But, at the end of the day, the whole point is, we want to be able to look at the whole picture. And now we do. If we’re just the marketing team looking at AdWords and trying to optimize for cost, then Tableau is going to win. But, at the end of the day, we get to close these customers so they can start paying us. So the marketing team should be looking at the data as well.
Alright, we went through couple case studies here. I want to have some takeaways for this presentation. First thing, it’s important to identify. When you’re starting your analysis, or diving deep into your data, it’s important to map out all your data sources. Want to be able to tell a whole story. You don’t want to be like the six blind men, you want to be able to see all the data sources that you’re going to need to tell that story.
Second, you want to create a data model. Combine the data, join it, and you can really use any BI tool. In my example, I used Tableau. You can use Micro strategies, and you can even use Excel. At the end of the day, you really just want to bring that data together. Combine it, so you can see those awesome insights.
Third, dig deep into the data. It’s important that you analyze the entire journey that the customer takes so that you can see these insights. By journey, I mean every single touchpoint that the customer has with your company. Even with AdWords. They are already engaging with just an impression of your ad. You’ve already collected some data on these customers. We want to take it from impression to click to purchase. You can even take it to multi-channel, get data on the user when they come in-store to redeem a coupon for that free cake.
And last, take action. When you discover these awesome insights, it’s important that you take it back to your team, take it back to your executives. Evangelize this data and take some action so they can optimize your business and optimize your marketing campaigns.
Everyone, it was a pleasure going through this talk with you guys about combining multiple data sources. Hopefully you can see it’s fairly easy now to tell the whole story. You can definitely reach out to me if you have any questions. This is my contact info. Definitely had a great time going through this content with you guys. Please reach out, again. Thank you to ObservePoint for having me present at the Analytics Summit 2017.