Building Digital Marketing Dashboard Using Python, Docker, Airflow in Google Cloud (Part-2)

This blog series is posted by WeCloudData’s Data Science Immersive Bootcamp student Bob Huang (Linkedin)

Continuing from the first half of the digital marketing blog post, This is Part 2 that mainly focusing on the data analysis business insights of different social platforms

1. 1. Email: There are lots of information in our emails. We can write codes to filter out useful emails, to parse email contents, to perform natural language processing (NLP), and to send emails. Here are some use cases:
      • Filter phone numbers, email addresses in emails to get contacts of potential clients.
      • Send batch emails to promote products.
      • Filter out keywords in inquiry emails for targeted promotions.
      • Sentiment analysis on feedback emails.

Use IMAP (Internet Message Access Protocol) (https://en.wikipedia.org/wiki/Internet_Message_Access_Protocol) to query emails. Sample codes refer to this blog post (http://beneathdata.com/how-to/email-behavior-analysis/). For query format, refer to rfc3501.

This code will filter out the email contents and headers that have subject “Courses” from seven days ago till now. Using PEEK, the emails are not marked as seen, so that the users that using this email account won’t miss any unread emails.

2. 2. Eventbrite/Meetup: A company might host hundreds of events in the past to promote their service or products. For example, a job training institute might use Eventbrite to create workshops such as Python training. We can use Eventbrite SDK (https://developers.facebook.com/docs/marketing-apis/) or Meetup API (https://www.meetup.com/meetup_api/) to get the following information: Event name, event attendance count, attendee emails, attendee employment status, event locations, event time, event sales (if paid), etc. Some use cases:
      • Check attendance percentage of subsequent events or repetitive promotion events to determine the values of re-hosting.
      • Gather frequent attendees by emails; these people might be potential clients.
      • Compare attendance of the same event in different locations to determine where to host future events.
      • Rehosting popular workshops.
      • Compare the effectiveness of different event hosting platforms (Eventbrite and Meetup)
      • Customer churn analysis
      • Get Events from competitors to compare.
      • Relate event data to ads data to analyze the effectiveness of advertisement.

Since the raw data cannot be directly used by Superset, we use views to do data aggregation and simple arithmetic operation, sample:

This query calculates the event attendance ratio.

The advantage of using view:

2. 2. • View is a logical table that doesn’t store data.
      • Read-only.
      • Automatic update when base table is updated.

3. 3. Google Ads/Facebook Ads: Google Ads is an online advertising platform developed by Google, where advertisers pay to display brief advertisements, service offerings, product listings, video content and generate mobile application installs within the Google ad network to web users.The AdWords API (https://developers.google.com/adwords/api/docs/guides/start) allows apps to interact directly with the Google Ads platform, vastly increasing the efficiency of managing large or complex Google Ads accounts and campaigns. Facebook Ads is an advertising service provided by Facebook. With micro-targeting features, Facebook Ads allows you to reach your exact target audience based on demographics, location, interests, and even behaviors. Facebook provides official Marketing API (https://developers.facebook.com/docs/marketing-apis/) SDKs. This is the easiest way to develop on Marketing API. The SDKs are open-source, and provide support for authentication, core object models, tests, and stability on top of the HTTP-based API.

Use cases:

3. 3. • Campaign Ad management
      • Ad traffic estimation and click through behavior, combined with Google Analytics data
      • Pick best strategies to do advertisement to reduce cost.

Google Ads campaign data hierarchy:

Some useful information about Facebook Campaign Insight (https://developers.facebook.com/docs/marketing-api/reference/ad-campaign-group/insights):

4. 4. Twitter: We can use Tweepy API (http://docs.tweepy.org/en/v3.5.0/api.html) to collect relevant tweets to analyze. Use cases include:
      • Collect Retweets, hashtags that related to the company to do sentiment analysis.
      • Analyze retweet timestamp to find out the best time and best topic to tweet.
      • Schedule tweet posting time

Sentiment analysis pipeline major steps:

4. 4. • Load tweets
      • Tokenize text to extract individual words or sentences
      • Stem words / Lemmatize words
      • Remove stopwords
      • Train a model

5. Linkedin The LinkedIn API (https://developer.linkedin.com/), also known as the REST API (https://searchmicroservices.techtarget.com/definition/RESTful-API) is the heart of all programmatic interactions with LinkedIn. All other methods of interacting, such as the JavaScript as well as the Mobile SDKs, are simply wrappers around the REST API to provide an added level of convenience for developers. Some use cases include:
   • Get a company’s profile
   • Get a company’s updates
   • Get comments for a specific company update
   • Get a company’s followers, by segment
   • Add comment as a company
   • Get historical follower statistics about a company
   • Get historical status update statistics about a company

6. MailChimp: MailChimp (https://mailchimp.com/) is a marketing automation platform and an email marketing service, mostly for sending out marketing emails. MailChimp provides RESTful API to do tasks such as:
   • Get response statistics of email recipients of company campaigns.
   • Get collections of Google Ads Status.
   • Send batch emails at a scheduled time.
   • Get subscribed or unsubscribed member list to compare with the email address list got from Eventbrite API, Gmail, etc.

7. Instagram: Instagram is a photo and video-sharing social networking service The Instagram API (https://www.instagram.com/developer/) Platform can be used to build non-automated, authentic, high-quality apps and services that:
   • Help individuals share their contents with 3rd party apps.
   • Help brands and advertisers understand, manage their audience and media rights.
   • Help broadcasters and publishers discover content, get digital rights to media, and share media with proper attribution.

   Some use cases:

   • Get user comments on event photos.
   • Get information about tagged objects.
   • Get a list of recent media objects from a given location with location coordinate.

8. 8. Google App Script: Google Apps Script (https://developers.google.com/apps-script/) is a scripting language for light-weight application development in the G Suite platform. It is based on JavaScript 1.6 with some portions of 1.7 and 1.8 and provides subset of ECMAScript 5 API. App Script makes it easy to create and publish add-ons in an online store for Google Sheets, Docs, Slides, and Forms.

Some use cases:

8. 8. • Ingest Google Form survey data to Google Cloud SQL (GO PAPERLESS!)
      • Invite people to join events and automatically add to Google Calendar.
      • List upcoming events in Google Calendar.
      • Merge a template email with content.

The following is the Google App Script editor:

9. 9. Google Analytics: Google Analytics (https://analytics.google.com/analytics/web/) is a freemium web analytics service offered by Google that tracks and reports website traffic. The Google Analytics Reporting API v4 (https://developers.google.com/analytics/devguides/reporting/core/v4/) is the newest programmatic method to access report data in Google Analytics. With the Google Analytics Reporting API, you can:
      • Build custom dashboards to display Google Analytics data.
      • Automate complex reporting tasks to save time.
      • Integrate your Google Analytics data with other business applications.

Some specific use cases:

9. 9. • Hook up google analytics to the company website and get website bouncing rate, average session duration trend to find out the popularity of the websites.
      • Get information about where visitors are rerouted in, which includes: Google search, external links, etc.
      • Get visitor geographic data and demographic data. (i.e., If the company is based in Toronto, get the number of visitors from Toronto comparing to other regions)

Usage example:

10. Web Scraping: We also provide web scraping service, mainly using Python, some packages:

11. Other social media: There are more social media sites that provide API or SDK support. As long as it is legal and doable, we can provide technical support.

FUTURE DEVELOPMENT

1. Consider building Docker container to host Dash-Plotly application. Graphs are more flexible comparing to Superset.
2. Current project only include data acquisition and analysis automation, we can also consider automating actions (e.g. send promotion emails based on the collected data statistic automatically)
3. Collaborate with financial team. (Marketing + Finance -> Collaborate decisions)

Set up machine learning model such as NLP analysis based on the collected data.

To find out more about the courses our students have taken to complete these projects and what you can learn from WeCloudData, click here to see our upcoming course schedule.