End-To-End BI Solution For Education Marketing Agency

Technology Used

SQL Server, SQL Server Integration Service, SQL Server Analysis Service & Excel PowerPivot, SQL Server Reporting Service, SharePoint and Excel Services, Performance Point Server, Excel Services

Customer Profile

One of the pioneers in education marketing space, specializing in running marketing campaigns for schools, career colleges and universities and help them enrolling more students.

Customer Situation

  • Has been running paid search marketing campaigns for their clients in popular search engines like Google, Bing, and Yahoo etc.
  • Had difficulty in consolidating the effectiveness of those campaigns and not able to fine tune their future campaigns based on the results of the campaigns run already

Congruent's Solution

  • Designed and developed an end-to-end BI solution, addressing the customer’s pain points using the Microsoft BI stack. It made sense to leverage the Microsoft BI stack since the customer had invested already on SQL Server, SharePoint and MS Office productivity suite
  • Developed a SSIS package that downloaded the raw data using the RESTful web services of the search providers. The package integrated the data from various providers like Google and Bing, cleansed, de-duplicated, and loaded the data temporarily onto staging tables and subsequently to the data warehouse
  • Designed the data warehouse using dimensional modeling techniques. Built multi-dimensional cubes that serve as the data source for SSRS static reports, excel power pivots and SharePoint dashboards that displayed scorecards and KPIs. KPIs included ad impressions, clicks, conversion rate, average cost per click etc.


  • Customer was able to report back effectively to their clients on the results of the campaigns and get classified information in the form of KPIs and scorecards that helped them re-strategize their ad placements and refine the targeted keywords
  • Excel based self-service analytics engine helped the customer’s business analysts to conduct various ‘what-if’ scenarios to make informed decisions on ad spend per search provider, relevance of the ad impressions for the given keywords and conversion patterns
  • Customer was able to offer a result oriented business model to their clients which they felt reduced the risks on their part. This risk reduction helped the customer to gain a few new clients too

Know more about our Data Analytics Consulting Services