Analyze and visualize omni-channel insights in Tableau using Query Service

Hello, everybody. In this video, I will walk you through Adobe Experience Platform Query Service and cover how it can help you analyze data and connect to external data visualization tools, such as Tableau. Hello, everybody. Query Service is a powerful SQL-based analytics tool that helps customers unlock profile, audience, activation, journey, and operational insights on top of readily available unified customer data with high concurrency. Query Service also allows you to query and transform omnichannel big data to the right granularity and power-enriched analysis in downstream BI and machine learning platforms for ingestion into the real-time customer profile. CitySignal is a telecom company and is interested in analyzing customer churn behavior. They want to understand the primary reasons and factors that drive customers away from CitySignal. CitySignal wants to use Adobe Experience Platform Query to understand customer churn behavior and leverage the platform to combine multiple data sources to create a unified data set that can be analyzed in Tableau. Here are some of the questions they want to answer. So let’s take a look at how CitySignal can accomplish these goals. Hello, everybody. First thing we need to do is ingest data into Adobe Experience Platform. On the Sources page, I already have a data source created and connected to Amazon S3, a cloud storage system. We can also use other ways to ingest data, such as Azure Blob Storage, Google Cloud Storage, or SFTP, to name a few. We have four data sets for CitySignal customers, which contain data for churn, call center, profile, and websites. Each of these data sources are tied to a schema and a data set. Now, if we go to the Data Sets section, we can see these four data sets created based on the four files we ingested using S3. Next, we’ll go to the Query Service UI and run some basic queries to confirm if the data makes sense. We usually call the stage of the process SQL Explorer. Let’s click on New Query to get a list of total churned customers by month. Click Play and run the query. Once it executes, we can see how many churned customers we have for the last six months. The next query I’ll run will show the monthly bill range and the total count of customers tied to each revenue range. Based on the output, we can see that most customers are paying more than $150 a month. Note that we can run the same query in PostgreSQL, which you can see here. In addition, we can also use the Query Service REST API to programmatically write, execute, and schedule queries. Hello, everybody. The next step is to consolidate these separate data sets into a single data set. We call this step of the process SQL Prep, where we can write a SQL query and output a brand new custom data set which meets your business requirement. Once we write a query, we can either click on the output data set option, or we can do it using a SQL query. In this case, I have written a CTAS or create table as select query to combine these data sets. Please note that I used an underscore for the alias name to populate the output data set. Hello, everybody. Given that I already ran this query earlier, I’ll now go to the data sets tab where we can see the new data set called cs underscore demo underscore output. If you take a quick peek at the columns, we can see that all columns have an underscore in them, which matches the alias we used in the CTAS query. In the last step of the demo, I will integrate the consolidated data set with Tableau. We call this process SQL Interactive or BI tool integration. We need to go to the credentials tab in query service, which has all the necessary authentication information needed to connect to any external platform via pSQL. Hello, everybody. Next, in the Tableau desktop app, we need to enter the pSQL credentials to connect to query service. My Tableau instance is already connected to query service. Now that we’re connected, you can see all the columns we created as part of the CTAS query that we ran in the query service UI. Now we’ll go to the dashboard I created where you can see that from October 2020 to March 2021, CitySignal had almost 50,000 customers with an average monthly spend of $136. We can also see that about 7,000 customers churned during that time, which gives us a churn rate of 14%. If we drill deeper, we can see a breakdown of churned and existing customers by service. We can also look at the churned customers by month in the form of a line graph, followed up by reason of churn, which is going to be very valuable for CitySignal. In this case, we can see that 29% of customers churned because they found a better deal elsewhere. Finally, we have a breakdown of the monthly bill range, how long were customers members before churning, and total churn rate by US West Coast. The dashboard contains a lot of different sheets, which you can see in this Tableau file. All this ties back to the original goals which CitySignal had set up for this analysis. You have just seen how Adobe Experience Platform query service provides customers with a rich set of SQL-based analytic capabilities to provide rich insights on customer data collected from all channels and devices to surface these insights in external visualization tools like Tableau. Hello, thanks for watching.