Analyze, Visualize, and Take Action on Social Data with the Social Analytics Block by Rivery

Taylor McGrath, Head of Customer Solutions at Rivery

May 6, 2020

In today’s marketing landscape, social channels are no longer just tools for messaging. They offer a trove of insights into valuable metrics like user behavior, audience engagement, and purchase intent. To fully leverage these insights, however, marketers need solutions that can manage the vast amounts of social data while also illuminating key insights.

Unfortunately, many marketing teams still rely on excel spreadsheets or self-built API connections to extract and analyze social media data. These approaches can’t readily handle the volume of data needed to generate competitive insights. And even when social data is housed in a cloud data warehouse, the increased volume of social data doesn’t directly result in easy to visualize, timely insights.

That’s why Rivery and Looker have teamed up to provide marketers with a plug-and-play solution for social media data analysis.

The Social Analytics Block combines the Rivery ELT platform with Looker to enable marketers and social media managers to track and analyze critical metrics across key social media channels. With this Looker Block, users can harness dashboards and visualizations built to catalyze breakthroughs in social performance to uncover social insights hidden within their cloud data warehouses.

Capture Insights Across Social Channels

The Social Analytics Block is powered by Rivery’s pre-built data ingestion pipelines for Facebook, Instagram, and Twitter. The initial data feeds include:

• Facebook - Posts: Post-level insights data from Facebook Social.
• Facebook - Pages: Page-level insights data from Facebook Social.
• Instagram - Posts: Post-level insights data from Instagram Social.
• Instagram - Pages: Page-level insights data from Instagram Social.
• Twitter - Tweet Details: Tweet metadata and user account insights from Twitter Ads (organic tweets).
• Twitter - Tweet Activity: Statistic report from Twitter Ads (organic tweets).

Rather than pre-hashed reports and aggregated totals, Rivery captures the granular data marketers need to craft precision-guided campaigns, careful marketing strategies, and data-driven messaging.

Step 1: Orchestrate and Transform the Data

In order to prepare the extracted data for social media analysis and visualization in Looker, Rivery first orchestrates and transforms the data into its required format. The extracted social data passes through an orchestration pipeline that applies business logic and transformations to the data stream. This process combines all the social data from Facebook, Instagram, and Twitter into a single database table.

Here’s how the data workflow is executed:

1) Ingest data from Facebook, Instagram, and Twitter with data pipelines

Pre-built data connectors allow teams to avoid making API connectors from scratch.

2) Perform transformations with a logical orchestration to combine data from all social channels

Rivery’s Logic Rivers automate this entire process.

3) A single table is created in the data warehouse, featuring the insights across all social platforms

The data is now ready for use in Looker.

4) Define dimensions and measures in LookML.

measure: page_impressions_current_30 {
    type: sum_distinct
    view_label: "Time Comparison Fields"
    description: "Number of impressions per page for the last 30 days. Facebook and Instagram only."
    sql_distinct_key: ${account_id}|| ${date_date} ;;
    sql: ${page_impressions};;
    filters: {
        field: date_date
        value: "30 days"
    }
}

measure: page_impressions_previous_30 {
    type: sum_distinct
    view_label: "Time Comparison Fields"
    description: "Number of impressions per page for the previous 30 days. Facebook and Instagram only."
    sql_distinct_key: ${account_id}|| ${date_date} ;;
    sql: ${page_impressions};;
    filters: {
        field: date_date
        value: "60 days ago for 30 days"
    }
}

measure: page_impressions_percent_change {
    type: number
    view_label: "Time Comparison Fields"
    description: "Percent change between impression count for the last 30 days compared to the previous 30 days. Facebook and Instagram only."
    value_format_name: percent_2
    sql: (${page_impressions_current_30}-${page_impressions_previous_30})/ NULLIF(${page_impressions_previous_30},0) ;;
}

Step 2: Visualize and Analyze Across Social Platforms

After transforming the data into the correct format, users can leverage pre-built dashboards in Looker to visualize their data, compare social metrics side-by-side, and track page and post engagement across social media platforms. A few of these analyses include:

Social Analytics Overview

Analyze key engagement metric trends and growth across all social channels.

All-Channel Post Performance

Evaluate post-level engagement metrics across all social channels.

Tweet Performance

Track tweet performance across dynamic interval buckets.

Take Your Social Media Marketing Analysis to the Next Level

Learn more about how to maximize your social media engagement and performance during our upcoming webinar and try out the Social Analytics by Rivery Block to take your social media analysis to the next level.