Emplifi

Overview

Emplifi is a unified social media marketing platform that helps clients drive growth by understanding their audience, creating content, analyzing engagement, and improving cross-channel care.

Two Circles' Emplifi integration strives to synchronize the social media data from Emplifi into Two Circles' data warehouse product (DWA) in an accurate, stable manner without affecting business operations. This integration provides post metrics and profile metrics data from the following social media platforms: Facebook, Instagram, Twitter, Snapchat, TikTok, YouTube and LinkedIn.

Setup

Below details are required to setup the connection with Emplifi API

• TEAM: Please provide Client ID and Client Secret for your Emplifi account.
  Follow the below steps to get the credentials
  1. Login to the Emplifi account
  2. Locate the client ID and client secret that is specific to your Emplifi account. You can also contact Support team at support@emplifi.io if any questions. 
  client ID and Client secret will look like - ODU3Mewfrefdbtgdfsflsfks2NzgyNjU0N19lZDEzNjhiYzYzMDgxYjE1NjU5ZjIwNTZlNWNjN2RkZQ== and ee9da23dw23er9bd775a8b437w3er43
  3. Provide the list of social media platforms' names to Two Circles Customer success team that you want to ingest post metrics and profiles metrics to DWA.
• Success: Save credentials in KORE Secret, create and prioritize a work item in the DWA backlog.
• Two Circles DWA Team: Notify the TEAM the integration has been configured, provide the user guide and a walk-through of the integration.

Entity Relationship Diagram

Data Dictionary

The data dictionary provides information on the list of tables and respective fields' maximum character length allowed, data type and definition of each field.

Data Load and refresh Frequency

A 12 months worth of historical data is loaded at the initial setup of the integration. Two Circles do offer a historical data load of 6 months, 12 months, 18 months and 24 months of data into DWA depending on the business needs of the Clients. Once the historical data load is complete, we do an incremental data load where data for past 30 days will be deleted and reloaded to the tables and full wipe and replace of data every day on applicable DWA tables via Emplifi REST API.

Table Description

This section provides definition, load frequency and key fields of each table in the Schema.

profiles
This table provides information on all the social media profiles that exist for a organization. A full pull is performed on the table every day where all the profiles will be deleted and reloaded from the API. Primary Key is id and other keys fields include:

profile_labels
This table provides profile labels information that are associated to each each social media profile. A full pull is performed on the table every day where all the profiles' lables will be deleted and reloaded from the API Key fields includes id(pk), name and profile_id(PK). A full pull is performed on the table every day.

facebook_profiles_basic_metrics
This table gives fan metrics data quantified based on each Facebook profile. Key information includes profile_id(PK), date(PK), fans_lifetime and fans_change . A full incremental load takes place every day where past 30 days of data is reloaded into the table.

facebook_profiles_insight_metrics
This table gives insights metrics for fans, reaches, views and impressions per each facebook profile. Key information includes profile_id(PK), date(PK), insights_fan_adds, insights_fan_removes, insights_fans_lifetime, insights_impressions, insights_reactions, insights_video_views, insights_views etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.

facebook_posts_basic_metrics
This table provides information on basic metrics for posts in a Facebook profile. Key fields are id(PK), snapshot_date(PK), profile_id(PK), comments, interactions, reactions, media_type, shares, video_shared etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.

facebook_posts_insight_metrics
This table provides information on insights metrics for posts in a Facebook profile. Key fields are id(PK), snapshot_date(PK), profile_id(PK), insights_interactions, insights_post_clicks, insights_reach, insights_reactions, insights_video_views etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.

facebook_posts_labels
This table provides information on the labels for posts in each profile. Key fields are post_id(PK), profile_id(PK), post_labels_id etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.

facebook_posts_attachments
This table gives information on the attachments that exist for a post in each Facebook profile. Key fields includes post_id(PK), profile_id(PK), attachments_title, attachments_title etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.

instagram_profiles_basic_metrics
This table gives Followers metrics data quantified based on each Instagram profile. Key information includes profile_id(PK), date(PK), follwers_change and followers_lifetime. A full incremental load takes place every day where past 30 days of data is reloaded into the table.
instagram_profiles_insight_metrics
This table gives insights metrics on followers, reaches and impressions per each Instagram profile. Key information includes profile_id(PK), date(PK), insights_followers, insights_impressions, insights_reach etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.
instagram_posts_attachments
This table gives information on the attachments that exist for a post in each Instagram profile. Key fields includes post_id(PK), profile_id(PK), attachments_title, attachments_title etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.
instagram_posts_basic_metrics
This table provides information on basic metrics for posts in a Instagram profile. Key fields are id(PK), snapshot_date(PK), profile_id(PK), comments, interactions, reactions, media_type, shares, video_shared etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.
instagram_posts_insight_metrics
This table provides information on insights metrics for posts in a Instagram profile. Key fields are id(PK), snapshot_date(PK), profile_id(PK), insights_interactions, insights_post_clicks, insights_reach, insights_reactions, insights_video_views etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.
instagram_posts_labels
This table provides information on the labels for posts in each Instagram profile. Key fields are post_id(PK), profile_id(PK), post_labels_id etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.
linkedin_profiles_basic_metrics
This table gives Followers metrics data quantified based on each LinkedIn profile. Key information includes profile_id(PK), date(PK), follwers_change and followers_lifetime. A full incremental load takes place every day where past 30 days of data is reloaded into the table.
linkedin_profiles_insight_metrics
This table gives insights metrics on engagements, reaches and impressions per each LinkedIn profile. Key information includes profile_id(PK), date(PK), insights_engagements, insights_impressions, insights_reach etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.
linkedin_posts_basic_metrics
This table provides information on basic metrics for posts in a LinkedIn profile. Key fields are id(PK), snapshot_date(PK), profile_id(PK), comments, interactions, reactions, media_type, shares, video_shared etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.
linkedin_posts_insight_metrics
This table provides information on insights metrics for posts in a LinkedIn profile. Key fields are id(PK), snapshot_date(PK), profile_id(PK), insights_interactions, insights_post_clicks, insights_reach, insights_reactions, insights_video_views etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.
linkedin_posts_labels
This table provides information on the labels for posts in each LinkedIn profile. Key fields are post_id(PK), profile_id(PK), post_labels_id etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.
linkedin_posts_attachments
This table gives information on the attachments that exist for a post in each LinkedIn profile. Key fields includes post_id(PK), profile_id(PK), attachments_title, attachments_title etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.

snapchat_posts_basic_metrics
This table provides information on basic metrics for posts in a Snapchat profile. Key fields are id(PK), snapshot_date(PK), profile_id(PK), comments, interactions, reactions, media_type, shares, video_shared etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.
snapchat_posts_insight_metrics
This table provides information on insights metrics for posts in a Snapchat profile. Key fields are id(PK), snapshot_date(PK), profile_id(PK), insights_interactions, insights_post_clicks, insights_reach, insights_reactions, insights_video_views etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.
snapchat_posts_labels
This table provides information on the labels for posts in each Snapchat profile. Key fields are post_id(PK), profile_id(PK), post_labels_id etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.
snapchat_posts_attachments
This table gives information on the attachments that exist for a post in each Snapchat profile. Key fields includes post_id(PK), profile_id(PK), attachments_title, attachments_title etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.

tiktok_profiles_insight_metrics
This table gives insights metrics on followers, reaches and impressions per each TikTok profile. Key information includes profile_id(PK), date(PK), insights_followers, insights_impressions, insights_reach etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.
tiktok_posts_basic_metrics
This table provides information on basic metrics for posts in a TikTok profile. Key fields are id(PK), snapshot_date(PK), profile_id(PK), comments, interactions, reactions, media_type, shares, video_shared etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.
tiktok_posts_insight_metrics
This table provides information on insights metrics for posts in a TikTok profile. Key fields are id(PK), snapshot_date(PK), profile_id(PK), insights_interactions, insights_post_clicks, insights_reach, insights_reactions, insights_video_views etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.
tiktok_posts_labels
This table provides information on the labels for posts in each TikTok profile. Key fields are post_id(PK), profile_id(PK), post_labels_id etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.
tiktok_posts_attachments
This table gives information on the attachments that exist for a post in each TikTok profile. Key fields includes post_id(PK), profile_id(PK), attachments_title, attachments_title etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.

twitter_profiles_basic_metrics
This table gives Followers metrics data quantified based on each Twitter profile. Key information includes profile_id(PK), date(PK), follwers_change, ff_ratio, listed_change, followers_lifetime etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.
twitter_posts_basic_metrics
This table provides information on basic metrics for posts in a Twitter profile. Key fields are id(PK), profile_id(PK), origin and author_profile_id. A full incremental load takes place every day where past 30 days of data is reloaded into the table.
twitter_posts_labels
This table provides information on the labels for posts in each Twitter profile. Key fields are post_id(PK), profile_id(PK), post_labels_id etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.

youtube_profiles_basic_metrics
This table gives viewers and subscribers metrics data quantified based on each YoutTube profile. Key information includes profile_id(PK), date(PK), views_change, interaction_change, subscribers_lifetime, subscribers_change, views_lifetime etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.
youtube_posts_basic_metrics
This table provides information on basic metrics for posts in a YoutTube profile. Key fields are id(PK), snapshot_date(PK), profile_id(PK), comments, interactions, reactions, media_type, shares, video_shared etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table.
youtube_posts_labels
This table provides information on the labels for posts in each YoutTube profile. Key fields are post_id(PK), profile_id(PK), post_labels_id etc., A full incremental load takes place every day where past 30 days of data is reloaded into the table. 

Mapping of API endpoints to DWA Tables

The below table outlines the list of Emplifi API endpoints that are utilized to ingest data into the DWA tables in Emplifi_v2 schema.

Sample Queries

The below queries gives the performance metrics(total comments, reactions, shares etc.,) of Facebook posts by media type(Photo, video etc.,) for the most recent day.

select distinct
	   fpbm.media_type,
       sum(fpbm.comments) as comments,
       Sum(fpbm.comments_sentiment_positive) as positive_comments,
	   SUM(fpbm.comments_sentiment_neutral) as neutral_comments,
       SUM(fpbm.comments_sentiment_negative) as negative_comments,
       SUM(fpbm.reactions) total_reactions,
	   SUM(fpbm.reactions_by_type_anger) total_angeries,
	   SUM(fpbm.reactions_by_type_haha) total_hahas,
	   SUM(fpbm.reactions_by_type_like) total_likes,
	   SUM(fpbm.reactions_by_type_love) total_loves,
	   SUM(fpbm.reactions_by_type_sorry) total_sorries,
	   SUM(fpbm.reactions_by_type_wow) total_wows,
	   SUM(fpbm.shares) total_shares,
	   SUM(fpim.insights_impressions) total_impressions,
	   SUM(fpim.insights_post_clicks) total_clicks,
	   SUM(fpim.insights_video_views_unique) tptal_unique_video_views
  from facebook_posts_basic_metrics fpbm
       left join facebook_posts_insight_metrics fpim
       on fpim.id = fpbm.id
       where fpbm.snapshot_date = CURRENT_DATE - INTERVAL '1 day' 
       group by fpbm.media_type 
       order by fpbm.media_type;

This query gives Facebook profiles' Fans metrics(total fans, fans churned, fans added and removed) for the past 30 days period of time.

select
	fpbm2.profile_id,
	fpbm2.fans_lifetime as fans,
	Sum(fpbm2.fans_change) as fans_change,
	SUM(fpim2.insights_fan_adds_unique) as fans_added,
	SUM(fpim2.insights_fan_removes_unique) as fans_remvoed
from facebook_profiles_basic_metrics fpbm2
       left join facebook_profiles_insight_metrics fpim2
       on fpim2.profile_id = fpbm2.profile_id
       where fpbm2.date = CURRENT_DATE - INTERVAL '30 day'
       group by fpbm2.profile_id,fpbm2.fans_lifetime order by fans_lifetime desc;

This query gives YouTube performance metrics(likes, video views,dislikes, subscribers etc.,) based on each Video posted by the organization.

select distinct
	ypbm.id,
	ypbm.video_views,
	ypbm.likes,
	ypbm.comments, 
	ypbm.dislikes,
	ypbm.video_view_time,
	SUM(ypbm2.subscribers_lifetime) as subscribers,
	SUM(ypbm2.subscribers_change) as subscribers_changed
from youtube_posts_basic_metrics ypbm 
left join youtube_profiles_basic_metrics ypbm2 
    on ypbm2.profile_id = ypbm.profile_id
    where ypbm2.date = CURRENT_DATE - INTERVAL '1 day'
    group by 
      ypbm.id,
      ypbm.video_views, 
      ypbm.likes,
	  ypbm.comments, 
	  ypbm.dislikes,
	  ypbm.video_view_time 
    order by ypbm.video_views desc;