Comment on page
Identifying target area for marketing campaign using consumer sentiment data
Consumer sentiment data from CPG merchants' reviews can be used not only to understand how consumers perceive the merchant, but also, when observed with similar data from other adjacent merchants, to understand how consumers perceive entire areas.
In this tutorial, we will be using sentiment data from The Data Appeal Company to identify the areas which we should target for a new marketing campaign of a quality coffee product in Berlin. Specifically we will use a sample of their Main Listing dataset to gather POI location, review volume and footfall data, as well as the Clusters & Topics dataset to analyze sentiment for the topic "coffee".
We will then construct spatial indexes to identify the best areas to launch a campaign based on the review volumes, footfall and coffee sentiment data.
Go to the CARTO login page and use your login credentials (user/password, Google or SSO) and continue the process until you get to your CARTO Workspace.
From the Navigation Menu in the left panel, select Maps. On the top-right corner, select "New map". This should take you to a new instance of a Builder map:
Let's first import the restaurants and cafes in Berlin, which is the relevant subset of the Data Appeal dataset for our tutorial. Navigate to "Add source from:" and select the table
dataappeal_restaurants_and_cafes_berlin_cpg, located under the "demo_tables" folder in the CARTO Data Warehouse connectionWhen the table loads, click on "More options" in the source and "Show data table". Scroll to the right and observe the columns "reviews", "sentiment" and "footfall", which are relevant in our case. Visit the dataset documentation to understand what the variables represent.
Lastly, rename the layer as "Restaurants and cafes".
As we mentioned earlier, we are not so much interested in analyzing a general sentiment value. Rather, we are interested in understanding sentiment when it comes to the topic of coffee. For that, we will import sentiment data for the "coffee" and "kaffee" topics for each POI. This data can be found in the Data Appeal Clusters and Topics dataset, from which we will import a subset filtered only for the relevant topics.Navigate to "Add source from:" and select the table
dataappeal_clusters_and_topics_for_coffee_berlin_cpg, located under the "demo_tables" folder in the CARTO Data Warehouse connection.When the table loads, click on "More options" in the source and "Show data table". Scroll to the right and observe the columns "topic", "polarity" and "opinions_count", which are relevant in our case. Visit the dataset documentation to understand what the variables represent.
Lastly, rename this other layer as "Coffee sentiment data".
We can see that we have polarity and opinions data for each POI, only for the coffee topic. To create a sentiment score for each POI, let's assign a score of
1 to each positive polarity, a score of -1 to each negative polarity, multiply by the value of "opinions_count" and aggregate for each POI.We also combine the resulting table with the "Restaurants and cafes" data, matching the two tables on "poi_id".
Go to "Add source from...", select "Custom query" and run the query below:
WITH polarity AS (
SELECT *,
CASE
WHEN polarity = '+' THEN CAST(opinions_count AS INT64)
WHEN polarity = '-' THEN -CAST(opinions_count AS INT64)
ELSE 0
END
as polarity_score
FROM `carto-demo-data.demo_tables.dataappeal_clusters_and_topics_berlin_cpg`
),
poi_coffee_sentiment AS(
SELECT poi_id, SUM(polarity_score) AS poi_coffee_sentiment
FROM polarity
GROUP BY poi_id
),
pois_with_coffee_sentiment AS (
SELECT poi_coffee_sentiment.*, polarity.geoid, polarity.cluster, polarity.topic, ROW_NUMBER() OVER(PARTITION BY polarity.poi_id ORDER BY polarity.geoid) AS row_num FROM poi_coffee_sentiment
INNER JOIN polarity
ON polarity.poi_id = poi_coffee_sentiment.poi_id
),
final_table AS (
SELECT * FROM pois_with_coffee_sentiment
WHERE row_num = 1
)
SELECT a.*, b.poi_coffee_sentiment, b.cluster
FROM `carto-demo-data.demo_tables.dataappeal_restaurants_and_cafes_berlin_cpg` a
INNER JOIN final_table b
ON a.poi_id = b.poi_id
Save the new source as a new table, by clicking on "Create table from query" in the SQL editor. Save in the CARTO Data Warehouse, under the folder "organization data", "shared". Name as "pois_with_coffee_sentiment_score".
When the operation is finished, rename the source and layer to "POIs with coffee sentiment score", and style the buffer according to the config seen below.
We have also saved it in the address
cartobq.cpg_marketing_sentiment_analysis_map.pois_with_coffee_sentiment_scoreso you can just load this step using a custom query.To move forward, delete all previous sources, leaving only "POIs with coffee sentiment score".
We will attempt to create a spatial index, aggregating the coffee sentiment, reviews volume, and football scores of all POIs across quadbins, to find the best areas to launch this campaign.
First, we will create the spatial index. We will use the QUADBIN_FROMGEOGPOINT method, found in the CARTO Analytics Toolbox.
Go to "Add source from...", select "Custom query" and run the query below.
Make sure you select "quadbin" mode on the top right corner of the SQL editor
with pois as (
SELECT poi_data.*,`carto-un`.carto.QUADBIN_FROMGEOGPOINT(geom, 17) as quadbin
FROM `cartobq.cpg_marketing_sentiment_analysis_map.pois_with_coffee_sentiment_score` poi_data
)
SELECT quadbin,
sum(reviews) as reviews_sum,
sum(poi_coffee_sentiment) as coffee_sentiment_sum,
sum(footfall) as footfall_sum,
count(*) as poi_count
FROM pois
GROUP by quadbin
Save the new source as a new table by clicking on "Create table from query" in the SQL editor.
Save in the CARTO Data Warehouse, under the folder "organization data", "shared". Name it as
market_plan_poi_quadbins. When the operation is finished, rename the source and the layer to "Aggregated POIs", and style the buffer according to the config seen below. We have saved the result of this step in a publicly available table,
cartobq.cpg_marketing_sentiment_analysis_map.market_plan_poi_quadbins, so that you can also select it using a custom query and continue with the tutorial.
Let's create a new custom index score to aggregate footfall, review volumes, and sentiment score in one single value that we can use to compare areas.
Go to "Add source from...", select "Custom query (SQL)" and run the query below:
Make sure you select "quadbin" mode on the top right corner of the SQL editor
WITH quadbin_normalized AS (
SELECT *,
( (reviews_sum - AVG(reviews_sum) OVER () ) /
NULLIF(STDDEV_POP(reviews_sum) OVER (), 0)
) AS reviews_sum_normalized,
( (coffee_sentiment_sum - AVG(coffee_sentiment_sum) OVER () ) /
NULLIF(STDDEV_POP(coffee_sentiment_sum) OVER (), 0)
) AS coffee_sentiment_sum_normalized,
( (footfall_sum - AVG(footfall_sum) OVER () ) /
NULLIF(STDDEV_POP(footfall_sum) OVER (), 0)
) AS footfall_sum_normalized
FROM `cartobq.cpg_marketing_sentiment_analysis_map.market_plan_poi_quadbins`
)
SELECT *, (0.5*coffee_sentiment_sum_normalized + 0.25*footfall_sum_normalized + 0.25*reviews_sum_normalized) as index_score
FROM quadbin_normalized
We normalize all variables and then we create the score by assigning weights to the variables. We assign the most weight (0.5) to the coffee sentiment variables, while we divide the rest of the weight to footfall and reviews volume (0.25 each).
Rename the layer and the source to "Index score" and style as seen below. Hide the "Aggregated POIs" layer.
We can see that there are, as expected, a lot of areas of interest in the center, but also some areas in the suburbs. Let's introduce some widgets to explore further.
Let's create a histogram filter for the index score, one for coffee sentiment, one for footfall, and one for the volume of the reviews. Go to Widgets, and select layer "Index score". Create a widget according to the configuration seen below.
Repeat the process for coffee sentiment, footfall, and reviews volume. Create widgets and configure them as seen below.
Let's also create a tooltip for the index score layer. Navigate to the tooltip tab, and enable tooltips for the "Index score" layer. Create tooltips when the user hovers over the quadbin, enable for the following variables:
- Index score (average)
- Coffee sentiment normalized (sum)
- Footfall normalized (sum)
- Reviews normalized (sum)
We can see that there are some areas in the center but also west and east which should be good candidates for our campaign. But which are the areas with good sentiment?
To visualize that, let's assign coffee sentiment as height criterion in the index layer. We cannot do that in the current viewport, as we also want to visualize individual POIs, so we need to duplicate the map and visualize the index score layer in two different ways.
Click on “More options …” on the “Index score” layer, click on “Duplicate layer”. Style as below. Name as “Index score (coffee sentiment)”
To view both layers in a separate viewport, click on “Switch to dual map view”, and “3D view”.
In the former viewport, hide the “Index score (coffee sentiment)” layer; and in the latter viewport, hide the other two layers.
Let's also create a tooltip for the second index score layer. Navigate to the tooltip tab, and enable tooltips for the “Index score (coffee sentiment)” layer. Replicate the configuration just as we did for the first layer:
- Index score (average)
- Coffee sentiment normalized (sum)
- Footfall normalized (sum)
- Reviews normalized (sum)
We can now see more clearly which are the best areas in Berlin, with the highest index score and coffee sentiment score. Let’s now find the merchants with the highest coffee sentiment score, to validate that they fall within the identified areas, and to explore the areas using Google Street view API.
First create a widget, choosing the “POIs with coffee sentiment score” source. Style as below
We can see that few merchants (around 200) in Berlin stand out for coffee sentiment. For a trade spend allocation exercise, we could have chosen to focus trade spend budget on those merchants.
Let’s now create a tooltip for each merchant, to view the name and sentiment of each merchant. Enable the tooltip for the POIs layer, and choose to configure using HTML code. Paste the code below:
<div class="CDB-Popup">
<dl>
<div>
<dt>Name</dt>
<dd>{{name}}</dd>
</div>
<div>
<dt>Coffee sentiment</dt>
<dd>{{poi_coffee_sentiment}}</dd>
</div>
</dl>
</div>
Another interesting use case which can be enabled by the HTML tooltip configurator is embedding Google Street view along with the name and coffee sentiment score. Paste the code below:
<div class="CDB-Popup">
<dl>
<div>
<dt>Name</dt>
<dd>{{name}}</dd>
</div>
<div>
<dt>Coffee sentiment</dt>
<dd>{{poi_coffee_sentiment}}</dd>
</div>
<div>
<dt>Street view</dt>
<img height="200" src="https://maps.googleapis.com/maps/api/streetview?size=400x400&location={{latitude}},{{longitude}}&fov=90&heading=70&pitch=0&key=yourKey&">
</div>
</dl>
</div>
Remember to replace your API key to enable this service. For more information on how to do this, please follow the following tutorial.
Change the name of the map to “Marketing campaign locations based on sentiment data analysis”
Finally we can make the map public and share the link to anybody in the organization. For that you should go to “Share” on the top right corner and set the map as Public. For more details, see Publishing and sharing maps.
