Skip to main content

Relo Metrics

Ajaykumar Guttlapalli
  • Updated

Overview

Relo Metrics is an AI-powered sponsorship analytics intelligence platform that enables brands to track and optimize their sponsorship investments and powers teams and leagues with the insights they need to retain and grow revenue. The company tracks sponsor exposure across live broadcasts, social media, and streaming platforms, delivering actionable insights via a fast, single solution platform.

KORE’s Relo Metrics integration strives to synchronize data from the Relo Metrics API into KORE’s data warehouse product (KORE DWA) in an accurate, stable manner without affecting business operations. The focus of this integration is the sponsorship analytics data.

This is a mono-directional integration in that KORE will only ingest data from the source, KORE will not push data back into the vendor’s system.

Setup

This section describes each step required to implement this integration from start to finish, including the owner of the task and a link to how they can complete it.

  • Client TEAM: Notify KORE of your intention to enable this integration
  • KORE: Send TEAM user guide and instructions on how to setup the data access for KORE
  • Client TEAM : Provide Kore with the below mention information required for source connectivity
    • username
    • password
    • API consumer key
    • relometrics_client_id
  • KORE: Save credentials in KORE Secret, create and prioritize a work item in the DWA backlog
  • DWA Developer: Setup integration and confirm data is loaded correctly
  • KORE: Notify the TEAM the integration has been configured, provide the user guide and a walk through of the integration

ERD and data dictionary

(This information is provided on a best-effort basis without guarantees. For clarity, most column names are omitted from this ERD.)

 

 

 

 

Table Description:

rights_holders:

This table returns the list of rightsholders the client has access to. Key information includes id, name, manager details, logo details, sponsors, leagueid, league name, sportid, gametypeid, streams and accounts.

The data in this table is updated fully once a day. You can check the warehouse status report on Tableau to view the exact time the update happens each day.

sponsors:

This table returns the list of Sponsors client has access to. Key information includes id, name, active sponsors, rightholderplacements, logos, analysisactive, clientremoved and valuation restrictions.

The data in this table is updated fully once a day. You can check the warehouse status report on Tableau to view the exact time the update happens each day.

account_ownership_type

This table returns the list of different account ownership types. Key information includes is and name.

The data in this table is updated fully once a day. You can check the warehouse status report on Tableau to view the exact time the update happens each day.

media_types:

This table returns the list of different media types like video, image, text etc. Key information includes id and name of media type.

The data in this table is updated fully once a day. You can check the warehouse status report on Tableau to view the exact time the update happens each day.

social_platforms:

This table returns the list of different social media platforms where sponsorship data can be analyzed. Key information includes id, social media names, cpm, cpv, cpe and cpve.

The data in this table is updated fully once a day. You can check the warehouse status report on Tableau to view the exact time the update happens each day.

social_posts

This table returns the granular data about individual posts. Key information includes id, social media account url, account details, media value, impressions, engagements, collectionids, hashtags, duration, views details etc.

The data in this table is updated incrementally once a day. You can check the warehouse status report on Tableau to view the exact time the update happens each day.

media_value_reports

This table returns all available data points exposures, impressions, engagements, duration, full media value, sponsor media value, post count, average mvp. Key information includes

The data in this table is updated incrementally once a day. You can check the warehouse status report on Tableau to view the exact time the update happens each day.

broadcast_exposures

This table returns broadcasts and associated exposure details. Key information includes exposures, impressions details, views details, engagements details, media value details, rightholders, sponsor, accountownership, social platforms, media and collection details.

The data in this table is updated incrementally once a day. You can check the warehouse status report on Tableau to view the exact time the update happens each day.

audience_insights

This table returns the details of the audience from where the sponsorship data can be ana;yzed.Key information includes audience_insights_id, platformid, username, followercount, updatedat, profileurl, demographictypename, segmentname, lastupdated and value.

The data in this table is updated fully once a day. You can check the warehouse status report on Tableau to view the exact time the update happens each day.

Example queries

Report on media data for particular sponsor

  1. Start with the table sponsors
  2. Logic to get the media data for particular sponsor
SELECT 
s.id,
s.name,
sad.rightsholder_name,
sad.mediatype_name,
sad.placement_name,
COUNT(sad.postid)
FROM relometrics.sponsors s
LEFT JOIN relometrics.sponsor_annotations_details sad 
ON s.id = sad.sponsor_id 
WHERE s.id = 1371
GROUP BY s.id, s.name, sad.rightsholder_name, sad.mediatype_name, sad.placement_name 
ORDER BY sad.mediatype_name, sad.placement_name ;

Media value report in a particular date range

  1. Start with the table media_value_reports
  2. Logic to get the Media value report for a date range
SELECT *
FROM relometrics.media_value_reports mvr 
WHERE mvr."date" BETWEEN GETDATE()-7 and GETDATE()
ORDER BY sponsorid, rightsholderplacementid ;